SQL基础语法—insert语句
1 insert语句
insert语句⽤于插⼊数据到表中，其基本语法有以下三种：
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
SELECT ...
[ON DUPLICATE KEY UPDATE assignment_list]
其中 values和 set两种语句都是将指定的数据插⼊到现成的表中，⽽select语句是将另外表中数据查出来并插⼊到现成的表中
partiion⼦句代表可以将数据插⼊到指定的表分区中
table_name代表将数据插⼊到的⽬标表
col_name代表要插⼊指定数据的⽬标表列，如果是多列则⽤逗号隔开，如果⽬标表中的某些列没有在insert语句中指定，则这些列会插⼊默认值，当然可以使⽤default显式指定插⼊默认值
values中除了可以指定确定的数值之外，开可以使⽤表达式expr
insert into tbl_name (col1,col2) values(15,col1*2) ##正确
insert into tbl_name (col1,col2) values(col1*2,15) ##错误
案例演⽰：
## 修改sid字段，添加auto_increment属性
mysql> alter table students modify sid int auto_increment;
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
## 修改gender字段，添加默认值0
mysql> alter table students modify gender int default 0;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
## 向students表中插⼊⼀条数据
mysql> insert into students(sname) values("bbb");
Query OK, 1 row affected (0.04 sec)
mysql> insert into students(sid,sname) values(5,"ccc");
Query OK, 1 row affected (0.02 sec)
mysql> insert into students(sname) values("ddd");
Query OK, 1 row affected (0.07 sec)
mysql> insert into students(sid,sname) values(4,"dd");
Query OK, 1 row affected (0.10 sec)
mysql> insert into students values(3*3,'fff',1);
Query OK, 1 row affected (0.09 sec)
mysql> select * from students;
+-----+--------+--------+
| sid | sname | gender |
+-----+--------+--------+
| 1 | viktor | 1 |
| 2 | aaa | NULL |
| 3 | bbb | 0 |
| 4 | dd | 0 |
| 5 | ccc | 0 |
| 6 | ddd | 0 |
| 9 | fff | 1 |
+-----+--------+--------+
7 rows in set (0.00 sec)
insert into tbl_name(a,b,c) values(1,2,3)(4,5,6)
insert into students values(7,'abc'),(8,'bcd')
low_priority关键词代表如果有其他链接正在读取⽬标表数据，则此insert语句需要等待读取完成
low_priority和high_priority关键词仅在MylSAM，MEMORY， MERGE三种存储引擎下才⽣效
ignore关键词代表insert语句如果违反主键和伪意见的约束条件，则不报错⽽只产⽣警告信息，违反的⾏被丢弃，⽽不是整个语句回退；
在数据类型转换有问题时如果有ignore则只产⽣警告信息，⽽不是语句回退
mysql> select * from students;
+-----+--------+--------+
| sid | sname | gender |
+-----+--------+--------+
| 1 | viktor | 1 |
| 2 | aaa | NULL |
| 3 | bbb | 0 |
| 4 | dd | 0 |
| 5 | ccc | 0 |
| 6 | ddd | 0 |
| 9 | fff | 1 |
+-----+--------+--------+
7 rows in set (0.00 sec)
mysql> insert ignore into students values(1,'aa',1),(7,'cc',0);
Query OK, 1 row affected, 1 warning (0.10 sec)
Records: 2 Duplicates: 1 Warnings: 1
select语句
⽤于从另外的表中查出记录并插⼊到⽬标表中
insert into tbl_temp2(fld_id)
select tbl_temp1.fld_order_id
from tbl_temp1 where tbl_temp1.fld_order_id > 100;
当⽬标表和select语句中的表相同时，则会先将select语句的结果存放在临时表中，再插⼊到⽬标表中（注意执⾏顺序）
mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | NO | | NULL | |
| sname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc students;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | UNI | NULL | |
| gender | int(11) | YES | | 0 | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
## concat⽤来合并两个字段
mysql> insert into student2(sid,sname) select sid,concat(sid,sname) from students where sid=1;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
## 当两个表中的字段不匹配时，插⼊会出现错误
mysql> insert into student2 select * from students;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
##选定两个字段进⾏插⼊
mysql> insert into student2 select sid,sname from students;
Query OK, 8 rows affected (0.07 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from student2;
+-----+---------+
| sid | sname |
+-----+---------+
| 1 | 1viktor |
| 2 | aaa |
| 3 | bbb |
| 7 | cc |
| 5 | ccc |
| 4 | dd |
| 6 | ddd |
| 9 | fff |
| 1 | viktor |
+-----+---------+
9 rows in set (0.00 sec)
3 insert on duplicate key update语句
当insert语句中使⽤on duplicate key update⼦句时，如果碰到当前插⼊的数据违反主键或唯⼀键的唯⼀性约束，则insert会转变成update语句修改对应的已经存在表中的这条数据。⽐如，如果a字段有唯⼀性约束且已经含有1这样的记录，则以下两条语句的执⾏结果相同。
insert into table(a,b,c) values(1,2,3)
on duplicate key update c=c+1
update table set c=c+1 where a=1;
简⽽⾔之，该语句可以解决插⼊的数据与表之间的冲突。insert语句字段顺序
案例演⽰：
mysql> insert into student2(sid,sname) select sid,sname from students where sid<3;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student2;
+-----+--------+
| sid | sname |
+-----+--------+
| 1 | viktor |
| 2 | aaa |
+-----+--------+
2 rows in set (0.00 sec)
## 创建sname字段的unique索引
mysql> create unique index idx_1 on student2(sname);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | NO | | NULL | |
| sname | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into student2(sid,sname) select sid,sname from students where sid=1;
ERROR 1062 (23000): Duplicate entry 'viktor' for key 'idx_1'
mysql> insert into student2(sid,sname) select sid,sname from students where sid=1 on duplicate key update sname='dabric';
Query OK, 2 rows affected (0.01 sec)
Records: 1 Duplicates: 1 Warnings: 0
mysql> select * from student2;
+-----+--------+
| sid | sname |
+-----+--------+
| 1 | dabric |
| 2 | aaa |
+-----+--------+
2 rows in set (0.00 sec)
mysql> insert into student values(1,'bb',0) on duplicate key update sname='b'sex=1;
4 练习
将如下数据插⼊到dept表中
1,'computer science'; 2,'education'; 4,'accounting'
根据like语句创建teacher_backup表，并插⼊如下数据：1,'susan'; 1; 2,'ruth'; 4; 3,'vivian', 4
将teacher_backup表的数据通过select语句插⼊到teacher表中
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论