insert into table-name values()
mysql>desc ns;+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| uname | varchar(20) | NO | | NULL | |
| udate | date | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00sec)
mysql>drop table ns;
Query OK,0 rows affected (0.00sec)
mysql>insert into ns values(null,'alex',now());
Query OK,1 row affected, 1 warning (0.05sec)
mysql> select *from ns;+-----+-------+------------+
| uid | uname | udate |
+-----+-------+------------+
| 1 | alex | 2016-11-08 |
+-----+-------+------------+
1 row in set (0.00 sec)
插入部分列
mysql>insert into ns values(null,'alex');
ERROR1136 (21S01): Column count doesn't match value count at row 1mysql>insert into ns(uid,uname) values(null,'alex');
Query OK,1 row affected (0.00sec)
mysql> select *from ns;+-----+-------+------------+
| uid | uname | udate |
+-----+-------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
+-----+-------+------------+
2 rows in set (0.00 sec)
插入非法null
mysql>insert into ns values(null,null,now());
ERROR1048 (23000): Column 'uname' cannot be null
插入两行
mysql> insert into ns values(null,'jat',19871117),(null,'jet',14581122);
Query OK,2 rows affected (0.00sec)
Records:2 Duplicates: 0 Warnings: 0mysql> select *from ns;+-----+-------+------------+
| uid | uname | udate |
+-----+-------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
| 3 | jat | 1987-11-17 |
| 4 | jet | 1458-11-22 |
+-----+-------+------------+
4 rows in set (0.00 sec)
再次插入部分列
mysql>insert into ns(uname) values('james');
Query OK,1 row affected (0.00sec)
mysql> select *from ns;+-----+-------+------------+
| uid | uname | udate |
+-----+-------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
| 3 | jat | 1987-11-17 |
| 4 | jet | 1458-11-22 |
| 5 | james | NULL |
+-----+-------+------------+
5 rows in set (0.00 sec)
使用set 插入数据
mysql> insert into ns set uname='lecake';
Query OK,1 row affected (0.00sec)
mysql> select *from ns;+-----+--------+------------+
| uid | uname | udate |
+-----+--------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
| 3 | jat | 1987-11-17 |
| 4 | jet | 1458-11-22 |
| 5 | james | NULL |
| 6 | lecake | NULL |
+-----+--------+------------+
6 rows in set (0.00 sec)
column 列顺序与表不一致
mysql> insert into ns(uname,udate,uid) values ('Kyrie',20160711,null)->;
Query OK,1 row affected (0.00sec)
mysql> select *from ns;+-----+--------+------------+
| uid | uname | udate |
+-----+--------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
| 3 | jat | 1987-11-17 |
| 4 | jet | 1458-11-22 |
| 5 | james | NULL |
| 6 | lecake | NULL |
| 7 | Kyrie | 2016-07-11 |
+-----+--------+------------+
7 rows in set (0.00 sec)
使用INSERT…SELECT语句插入从其他表选择的行
查询不能包含一个ORDER BY子句,而且INSERT语句的目的表不能出现在SELECT查询部分的FROM子句.
mysql> select *from ns2;+-----+--------+-----------+------------+
| uid | uname | uemail | udate |
+-----+--------+-----------+------------+
| 1 | config | con@1.com | 1987-11-17 |
| 2 | enable | en@1.com | 1458-11-22 |
+-----+--------+-----------+------------+
2 rows in set (0.00sec)
mysql>select uname,udate from ns2;+--------+------------+
| uname | udate |
+--------+------------+
| config | 1987-11-17 |
| enable | 1458-11-22 |
+--------+------------+
2 rows in set (0.00sec)
mysql>insert into ns(uname,udate) select uname,udate from ns2;
Query OK,2 rows affected (0.05sec)
Records:2 Duplicates: 0 Warnings: 0mysql> select *from ns;+-----+--------+------------+
| uid | uname | udate |
+-----+--------+------------+
| 1 | alex | 2016-11-08 |
| 2 | alex | NULL |
| 3 | jat | 1987-11-17 |
| 4 | jet | 1458-11-22 |
| 5 | james | NULL |
| 6 | lecake | NULL |
| 7 | Kyrie | 2016-07-11 |
| 8 | config | 1987-11-17 |
| 9 | enable | 1458-11-22 |
+-----+--------+------------+
9 rows in set (0.00sec)
mysql>
INSERT 语法
INSERT [LOW_PRIORITY | DELAYED |HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr| DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:
INSERT [LOW_PRIORITY| DELAYED |HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:
INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]