0

So I tried to experiment with the INSERT INTO code. I tried using the statement,

INSERT INTO cats (name, age)
VALUES ( 8, '3');

just to try it out and see what happens. It turns out it's still able to store both values. Under name, was 8, and under age was 3. I also tried to insert the name, Momo, without any quotes but it did not work. How come the number 8, worked without the ' ' and Momo didn't'? Also, how come when I use ' ' for the integer, 3, it still took that value as age?

Last part of the question that I just want to clarify. I tried inserting 'Mimi' in the age value and the table showed 0. I just assumed that meant a null value because 'Mimi' isn't an integer. Is that correct? If so, then why did '3' work?

For more clarification, my cats table looks like this

mysql> SHOW COLUMNS FROM cats;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

I'm a true beginner so I'm sorry for the very simple question

timmyen
  • 121
  • 2
  • 7

2 Answers2

1

In SQL, string constants need to be surrounded by single quotes. Otherwise, they are interpreted as an identifier -- presumably a column name in most contexts, but it could also be a variable name.

A values() statement has no way to reference columns, so Momo will generate an error that the identifier is not recognized. Note that double quotes are an exception in some databases, including MySQL. They can surround either identifiers or string values.

In a numeric context, such as when inserting a value into a numeric column, SQL converts strings to numbers. In most cases, the SQL will generate an error, if the string does not convert cleanly to a number.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think the assumption you are making is that '8' is a string, and 8 is an integer.

Making this assumption is usually a good one, in most programming languages. The SQL language however is more lenient. It already knows what the column type is, so it can figure out how to store the value and what you meant.

So in a lot of cases 8 and '8' and "8" are identical.

Evert
  • 93,428
  • 18
  • 118
  • 189