插入数据:INSERT
INSERT [INTO] table_name (列1, 列2,...) VALUES (值1, 值2,....),()....;
创建一个表users,有一下属性:
mysql> CREATE TABLE users(
-> int SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
-> sec BOOLEAN
-> );
插入两组数据,形成一下表
mysql> INSERT users VALUES (NULL,'Tom' ,'123',25,1);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
+----+----------+----------+-----+------+
除了用NULL,增加主键的自动编号,还可以用DEFAULT,数字除了直接用还可以用算数表达式,默认约束也可以用DEFAULT 表示:
mysql> INSERT users VALUES (DEFAULT,'John' ,'456',25,1);
mysql> INSERT users VALUES (NULL,'John' ,'456',3*7-5,1);
还可以插入两条属性:有一个有意思的东西,我试验的,一并分享出来:
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
+----+----------+----------+-----+------+
/*
经调查,现有四条数据。然后用NULL,插入第五条,接着直接在下一条直接id赋值5:
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(5,'ASD','234',34,1);
ERROR 1062 (23000): Duplicate entry '5' for key 1
/*
结果一定是报错的,因为5已经存在了,但是我又试了一下NULL,和6
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(6,'ASD','234',34,1);
ERROR 1062 (23000): Duplicate entry '6' for key 1
/*
还是报错,又试了一下NULL,和8
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(8,'ASD','234',34,1);
Query OK, 2 rows affected (0.07 sec)
/*
虽然成功了,但是现在表中的id有什么呢?5~8有哪些呢?结果先不公布,为了验证我的猜想,我做了以下试验
*/
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
| 5 | John | 456 | 25 | 1 |
| 7 | John | 456 | 25 | 1 |
| 8 | ASD | 234 | 34 | 1 |
| 9 | John | 456 | 16 | 1 |
| 10 | John | 456 | 25 | 1 |
| 11 | ASD | 234 | 34 | 1 |
+----+----------+----------+-----+------+
/*
目前没有6,没有12,我们来测试一下这两个数据
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(6,'ASD','234',34,1);
Query OK, 2 rows affected (0.09 sec)
/*
成功了,然后是一下NULL(就是13)和12,结果一定是失败的对吧,因为12在上一条语句已经插入了,
那么紧接着在插入一个且仅一个NULL,那么,这个NULL是多少呢???
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(6,'ASD','234',34,1);
Query OK, 2 rows affected (0.09 sec)
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(12,'ASD','234',34,1);
ERROR 1062 (23000): Duplicate entry '12' for key 1
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
| 5 | John | 456 | 25 | 1 |
| 6 | ASD | 234 | 34 | 1 |
| 7 | John | 456 | 25 | 1 |
| 8 | ASD | 234 | 34 | 1 |
| 9 | John | 456 | 16 | 1 |
| 10 | John | 456 | 25 | 1 |
| 11 | ASD | 234 | 34 | 1 |
| 12 | John | 456 | 25 | 1 |
| 14 | John | 456 | 25 | 1 |
+----+----------+----------+-----+------+
13 rows in set (0.00 sec)
/*
OK,结果是NULL=14,那么结论出来了,当一条语句用NULL插入失败的时候,值不会真的插入进去,但是,位置却留了出来,那么这里就有一个问题,什么时候能再次插入13呢,答案就是:直接用13代替NULL,证据如下:以15为例。
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(15,'ASD','234',34,1);
ERROR 1062 (23000): Duplicate entry '15' for key 1
mysql> INSERT users VALUES (15,'ASD','234',34,1);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
| 5 | John | 456 | 25 | 1 |
| 6 | ASD | 234 | 34 | 1 |
| 7 | John | 456 | 25 | 1 |
| 8 | ASD | 234 | 34 | 1 |
| 9 | John | 456 | 16 | 1 |
| 10 | John | 456 | 25 | 1 |
| 11 | ASD | 234 | 34 | 1 |
| 12 | John | 456 | 25 | 1 |
| 14 | John | 456 | 25 | 1 |
| 15 | ASD | 234 | 34 | 1 |
+----+----------+----------+-----+------+
14 rows in set (0.00 sec)
/*
但是用NULL,就插不进去,前面演示过,为了说明问题,再次演示:
*/
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1),(16,'ASD','234',34,1);
ERROR 1062 (23000): Duplicate entry '16' for key 1
mysql> INSERT users VALUES (NULL,'John' ,'456',25,1);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM USERS;
+----+----------+----------+-----+------+
| id | username | password | age | sec |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 25 | 1 |
| 2 | John | 456 | 25 | 1 |
| 3 | John | 456 | 25 | 1 |
| 4 | John | 456 | 16 | 1 |
| 5 | John | 456 | 25 | 1 |
| 6 | ASD | 234 | 34 | 1 |
| 7 | John | 456 | 25 | 1 |
| 8 | ASD | 234 | 34 | 1 |
| 9 | John | 456 | 16 | 1 |
| 10 | John | 456 | 25 | 1 |
| 11 | ASD | 234 | 34 | 1 |
| 12 | John | 456 | 25 | 1 |
| 14 | John | 456 | 25 | 1 |
| 15 | ASD | 234 | 34 | 1 |
| 17 | John | 456 | 25 | 1 |
+----+----------+----------+-----+------+
15 rows in set (0.01 sec)
/*
没有16,只有17,结论就是。。。。搞得我好乱,想不出结论了,看过的大牛,帮我弄个结论吧,在此谢过了!!
*/