老男孩MySQLDBA6期-2019年01月19日-第2天-08MySQL Insert

MySQL Insert

Insert语句用于插入数据到表中，其基本语法有以下三种：

Insert语句三种写法的常规用法：

desc students;

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

select * from students;

其中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); ##错误

insert into students(sid,sname) values(4,'ddd');
insert into students(sid) values(5);
insert into students(sname) values('eee');
insert into students value(2*3,'fff');

select * from students;

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

select * from students;

/*ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'*/
insert into students values(1,'bbb',0);

/*Query OK*/
insert ignore into students values(1,'bbb',0);

/*有warning*/
show warnings;

/*Query OK，有1个warning*/
insert ignore into students values(1,'aa',0),(5,'bb',1);

select * from students3;

/*Query OK，有2个warning*/
insert ignore into students select * from students3;

用于从另外的表中查出记录并插入到目标表中

　　• 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;

在 5.6.6 版本之前，用来表示此插入语句当碰到其他链接正在使用目标表时就等待，直到目标表没被用时再插入数据

在 5.7 版本时，delayed 关键词就不再支持，但语句执行时不会报错，只会产生一个警告信息，后续版本会去掉此关键词

## Query OK, 有1个warning
insert delayed into students select * from students;　　

当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;

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); 

##执行成功
insert into students2 values(1,‘bbb’,0) on duplicate key update sname=‘bbb’;

##执行成功
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表中