SQL语句-INSERT语句

Insert语句

Insert语句三种写法:

mysql> desc students;
+-------+-------------+------+-----+---------+-------+
| Field     | Type  | Null  | Key    | Default | Extra |
+-------+-------------+------+-----+---------+-------+ 
|sid        |int(11)    |YES        |   |NULL   |   |
|sname  |varchar(20)    |YES    |   |NULL   |   |
+-------+-------------+------+-----+---------+-------+


Insert into students values(1,’aaa’);
Insert into students set sid=2,sname=‘bbb’;
Insert into students select * from students_bak;

mysql> select * from students;
+------+-------+
|sid |sname|
+------+-------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
+------+-------+

• 其中insert...values和insert...set两种语句都是将指定的数据插入到现成的表中,而insert...select语句是将另外表中数据查出来并插入 到现成的表中
• Partition子句代表可以将数据插入到指定的表分区中
• Tbl_name代表将数据插入到的目标表
• Col_name代表要插入指定数据的目标表列，如果是多列则用逗号 隔开，如果目标表中的某些列没有在Insert语句中指定，则这些列 会插入默认值，当然可以使用default显视指定插入默认值
• Values中除了可以指定确定的数值之外，还可以使用表达式expr

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);         -- 正确
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);         -- 错误

mysql> insert into students(sid,sname) values(4,'ddd');
mysql> insert into students(sid) values(5);
mysql> insert into students(sname) values('eee');
mysql> insert into students value(2*3,'fff');
mysql> select * from students;
+------+-------+
|sid        |sname|
+------+-------+
| 1     |aaa    |
| 2     |bbb    |
| 3         | ccc   |
| 4     |ddd    |
| 5         | NULL |
|NULL   |eee    |
| 6     |fff    |
+------+-------+

• Insert...values语句不光可以插入一条数据，也可以插入多条数据

  INSERT INTO tbl_name(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
  Insert into students values(7,’abc’),(8,’bcd’);

• Insert...values和insert...select语句的执行结果如下
• Records: 100 Duplicates: 0 Warnings: 0
• Records代表此语句操作了多少行数据，但不一定是多少行被插入的数据，因为如果存 在相同的行数据且违反了某个唯一性，则duplicates会显示非0数值，warning代表语句执 行过程中的一些警告信息
• low_priority关键词代表如果有其他链接正在读取目标表数据，则此insert语句需要等待读取完成
• low_priority和high_priority关键词仅在MyISAM, MEMORY, and MERGE三种存储引擎下才生效

• Ignore关键词代表insert语句如果违反主键和唯一键的约束条件，则不报错而只产生警告信息，违反的行被丢弃，而不是整个语句回退;在数据类型转换
  有问题时如果有ignore则只产生警告信息,而不是语句回退

CREATE TABLE `students` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(20) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
UNIQUE KEY `idx_st_sid` (`sid`),
KEY `idx_st_union` (`sname`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin
mysql> select * from students;
+------+-------+--------+
|sid|sname|gender|
+------+-------+--------+
|1|abc| 1|
|2|abc| 1|
|3|abc| 1|
mysql> insert into students values(1,'bbb',0);
ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'
mysql> insert ignore into students values(1,'bbb',0);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level     | Code | Message                    |
+---------+------+------------------------------------------+
| Warning   | 1062 | Duplicate entry '1' for key 'idx_st_sid'   |

mysql> insert ignore into students values(1,'aa',0),(5,'bb',1); 
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 2 Duplicates: 1 Warnings: 1
mysql> select * from students3; 
+------+-------+--------+
|sid |sname|gender|
+------+-------+--------+ 
|1|a|0| 
|2|b|0| 
|4|c|1|
mysql> insert ignore into students select * from students3;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 2 Warnings: 2

Insert...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语句的结果 存放在临时表中，再插入到目标表中(注意执行顺序)

insert into students select * from students;

Insert delayed语句详解

在5.6.6版本之前，用来表示此插入语句当碰到其他链接正在使用 目标表时就等待，直到目标表没被用时再插入数据
在5.7版本时，delayed关键词就不再支持，但语句执行时不会报 错，只会产生一个警告信息，后续版本会去掉此关键词

insert delayed into students select * from students; 
Query OK, 18 rows affected, 1 warning (0.00 sec)
Records: 18 Duplicates: 0 Warnings: 1

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;

On duplicate key update子句后面可以跟多个修改，用逗号隔开

上述例子中如果b字段也有唯一性约束，则与此语句的执行结果 相同，但一般应该避免出现对应多条的情况

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

Insert on duplicate key update语句详解

mysql> create table students2(sid int primary key,sname varchar(20),sex int);
Insert into students2 values(1,’aaa’,1);            -- 插入成功
Insert into students2 values(1,’bbb’,0);            -- 插入失败
mysql> insert into students2 values(1,'bbb',0);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into students2 values(1,‘bbb’,0) on duplicate key update sname=‘bbb’;
+-----+-------+------+
| sid   | sname     | sex | 
+-----+-------+------+ 
| 1 |bbb        | 1 |
insert into students2 values(1,‘ccc’,0) on duplicate key update sname=‘ccc’,sex=0;      -- 执行成功

将如下数据插入到dept表中

1,’computer science’ ; 2,’education’; 4,’accounting’
根据create table ... like语句创建teacher_backup表，并插入如下数 据:
1,’susan’,1; 2,’ruth’,4; 3,’vivian’,4
将teacher_backup表的数据通过insert...select语句插入到teacher表中