mysql 参数inserue_【MYSQL】insert into

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, ... ]