【每日一学】轻松入门MySQL DML:自信地驾驭数据操作!

学习总目标

本次学习目标

第13章 DML

13.1 添加语句

（1）添加一条记录到某个表中

insert into 表名称 values(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应

mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid | int(11) | YES | | NULL | |
| tname | varchar(5) | YES | | NULL | |
| salary | double | YES | | NULL | |
| weight | double | YES | | NULL | |
| birthday | date | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| blood | enum('A','B','AB','O') | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

insert into teacher values(1,'张三',15000,120.5,'1990-5-1','男','O','13789586859');

insert into teacher values(2,'李四',15000,'1990-5-1','男','O','13789586859'); #缺体重weight的值
ERROR 1136 (21S01): Column（列） count（数量） doesn't match（不匹配） value（值） count（数量) at row 1

（2）添加一条记录到某个表中

insert into 表名称 (字段列表) values(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应

insert into teacher(tid,tname,salary,phone) values(3,'王五',16000,'15789546586');

（3）添加多条记录到某个表中

insert into 表名称 values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应

insert into 表名称 (字段列表) values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应

insert into teacher (tid,tname,salary,phone)
values(4,'赵六',16000,'15789546586'),
(5,'汪飞',18000,'15789548886'),
(6,'天琪',19000,'15909546586');

（4）示例演示

#演示基本的，简单的DML语句
#基于tempdb数据库演示
create database tempdb;
use tempdb;
#创建teacher表
create table teacher(
id int,
name varchar(20),
gender enum('m','f'),
birthday date,
salary double,
tel varchar(11)
);
#查看teacher表结构
mysql> desc teacher;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | double | YES | | NULL | |
| tel | char(18) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
#添加数据
#（1）第一种情况，给所有字段赋值
insert into 表名称 values(值列表);
#这种情况要求(值列表)的每一个值的类型、顺序与表结构一一对应
#表中有几个字段，(值列表)必须有几个值，不能多也不能少
#值如果是字符串或日期类型，需要加单引号
#例如：添加一条记录到teacher表
insert into teacher values
(1,'张三','m','1998-7-8',15000.0,'18256953685');
#例如：添加一条记录到teacher表
insert into teacher values
(2,'李四','f','1998-7-8',15000.0); #少了电话号码
mysql> insert into teacher values
-> (2,'李四','f','1998-7-8',15000.0);
ERROR 1136 (21S01): Column count doesn't match value count at row 1'
#(值列表)中值的数量和表结构中column列的数量不一致。
#例如：添加一条记录到teacher表
insert into teacher values
(2,'李四','f','北京宏福苑',15000.0,'18256953685'); #把生日写称为地址
mysql> insert into teacher values
-> (2,'李四','f','北京宏福苑',15000.0,'18256953685');
ERROR 1292 (22007): Incorrect date value: '北京宏福苑' for column 'birthday' at row 1
#日期格式不对
#（2）第二种情况，给部分字段赋值
insert into 表名称 (部分字段列表) values(值列表);
#此时(值列表)中的值的数量、格式、顺序与(部分字段列表)对应即可
#例如：添加一条记录到teacher表，只给id和name字段赋值
insert into teacher (id,name) values (2,'李四');
mysql> select * from teacher;
+------+------+--------+------------+--------+-------------+
| id | name | gender | birthday | salary | tel |
+------+------+--------+------------+--------+-------------+
| 1 | 张三 | m | 1998-07-08 | 15000 | 18256953685 |
| 2 | 李四 | NULL | NULL | NULL | NULL |
+------+------+--------+------------+--------+-------------+
2 rows in set (0.00 sec)
#没有赋值的字段都是默认值，此时默认值是NULL
#这种情况，当某个字段设置了“非空NOT NULL”约束，又没有提前指定“默认值”，
#那么在添加时没有赋值的话，会报错。明天演示非空约束。
#（3）一次添加多条记录
insert into 表名称 values(值列表1),(值列表2)...;
insert into 表名称 (部分字段列表) values(值列表),(值列表2)...;
#上面一个insert语句有几个(值列表)就表示添加几行记录。
#每一个值列表直接使用逗号分隔
#添加多条记录到teacher表
insert into teacher (id,name) values
(3,'王五'),
(4,'宋鑫'),
(5,'赵志浩'),
(6,'杨业行'),
(7,'牛钰琪');

#查看数据
mysql> select * from teacher;
+------+--------+--------+------------+--------+-------------+
| id | name | gender | birthday | salary | tel |
+------+--------+--------+------------+--------+-------------+
| 1 | 张三 | m | 1998-07-08 | 15000 | 18256953685 |
| 2 | 李四 | NULL | NULL | NULL | NULL |
| 3 | 王五 | NULL | NULL | NULL | NULL |
| 4 | 宋鑫 | NULL | NULL | NULL | NULL |
| 5 | 赵志浩 | NULL | NULL | NULL | NULL |
| 6 | 杨业行 | NULL | NULL | NULL | NULL |
| 7 | 牛钰琪 | NULL | NULL | NULL | NULL |
+------+--------+--------+------------+--------+-------------+
7 rows in set (0.00 sec)

13.2 修改语句

修改所有行

update 表名称 set 字段名 = 值, 字段名 = 值; #给所有行修改

#修改所有人的薪资，都涨了1000
update teacher set salary = salary + 1000 ;

修改部分行

update 表名称 set 字段名 = 值, 字段名 = 值 where 条件; #给满足条件的行修改

#修改天琪的薪资降低5000
update teacher set salary = salary-5000 where tname = '天琪';

13.3 删除

删除部分行的数据

delete from 表名称 where 条件;

delete from teacher where tname = '天琪';

删除整张表的数据，但表结构留下

delete from 表名称;

delete from teacher;

截断表，清空表中的数据，只有表结构

truncate 表名称;

truncate teacher;

truncate表和delete表的区别：

delete是一条一条删除记录的。如果在事务中，事务提交之前支持回滚。（后面会讲事务）

truncate是把整个表drop，新建一张，效率更高。就算在事务中，也无法回滚。

#同学问：是否可以删除salary字段的值，字段留着，值删掉
#可以实现，但是不是用delete，用update
#同学问：是否可以删除salary字段，连同字段和这个字段的数据都是删除
#可以实现，但是不是用delete，用alter table 表名称 drop column 字段名;
#同学问：只删除某个单元格的值
#可以实现，但是不是用delete，用update

13.4 UPDATE中嵌套子查询

#子查询也可以嵌套在update语句中
#（1）修改“t_employee”表中部门编号（did）和
#“测试部”部门编号（did）相同的员工薪资为原来薪资的1.5倍。
UPDATE t_employee
SET salary = salary * 1.5
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#（2）修改“t_employee”表中did为NULL的员工信息，
#将他们的did值修改为“测试部”的部门编号。
#子查询select did from t_department where dname = '测试部'
#这种子查询必须是单个值，否则无法赋值
UPDATE t_employee
SET did = (SELECT did FROM t_department WHERE dname = '测试部')
WHERE did IS NULL;
#（3）修改“t_employee”表中“李冰冰”的薪资值等于“孙红梅”的薪资值。
#这里使用子查询先在“t_employee”表中查询出“孙红梅”的薪资。
#select salary from t_employee where ename = '孙红梅';
UPDATE t_employee
SET salary = (SELECT salary FROM t_employee WHERE ename = '孙红梅')
WHERE ename = '李冰冰';
#You can't specify target table 't_employee' for update in FROM clause'
UPDATE t_employee
SET salary = (SELECT salary FROM(SELECT salary FROM t_employee WHERE ename = '孙红梅')temp)
WHERE ename = '李冰冰';
#当update的表和子查询的表是同一个表时，需要将子查询的结果用临时表的方式表示
#即再套一层子查询，使得update和最外层的子查询不是同一张表
#（4）修改“t_employee”表“李冰冰”的薪资与她所在部门的平均薪资一样。
#子查询，查询李冰冰的部门编号
#select did from t_employee where ename = '李冰冰';
#子查询第二层，查询李冰冰所在部门的平均薪资
#select avg(salary) from t_employee where did = (select did from t_employee where ename = '李冰冰');
#子查询第三层，把第二层的子查询结果当成临时表再查一下结果
#目的使得和外层的update不是同一张表
SELECT pingjun FROM (SELECT AVG(salary) pingjun FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰') temp)
#update更新
UPDATE t_employee
SET salary =
(SELECT pingjun FROM
(SELECT AVG(salary) pingjun FROM t_employee WHERE did =
(SELECT did FROM t_employee WHERE ename = '李冰冰') ) temp)
WHERE ename = '李冰冰';

13.5 DELETE中嵌套子查询

#delete语句中也可以嵌套子查询
#（1）从“t_employee”表中删除“测试部”的员工记录。
DELETE FROM t_employee
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');
#（2）从“t_employee”表中删除和“李冰冰”同一个部门的员工记录。
#子查询 “李冰冰”的部门编号
#select did from t_employee where ename = '李冰冰';
DELETE FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰');
#You can't specify target table 't_employee' for update in FROM clause'
#删除和子查询是同一张表
DELETE FROM t_employee WHERE did = (SELECT did FROM (SELECT did FROM t_employee WHERE ename = '李冰冰')temp);

13.6 使用子查询复制表结构和数据

#演示通过子查询复制表，
#（1）复制表结构
#（2）复制一条或多条记录
#（3）同时复制表结构和记录
#仅仅是复制表结构，可以用create语句
CREATE TABLE department LIKE t_department;
#使用INSERT语句+子查询，复制数据，此时INSERT不用写values
INSERT INTO department (SELECT * FROM t_department WHERE did<=3);
#同时复制表结构+数据
CREATE TABLE d_department AS (SELECT * FROM t_department);
#如果select后面是部分字段，复制的新表就只有这一部分字段

第15章 事务

15.1 事务的特点

1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

2、事务的ACID属性：

（1）原子性（Automicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

/*
原子性：
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
成功：张三账号变为500，李四变为1500，
失败：张三账号是1000，李四还是1000.
#一致性
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
要么他俩的余额不变， 还是1000，总和就是2000
要么他俩的余额有变化，张三500，李四1500，总和仍然是2000
错误：
张三500，李四1000，总和是1500，结果不对
张三1000，李四1500，总和是2500，结果不对
#隔离性
例如：张三要给李四转500，
王五要给李四转500，
张三转账是否成功，和王五是否转账成功无关。


#持久性：
例如：张三要给李四转500，一旦成功提交，就转账成功，撤不回来了。
*/

15.2 事务的开启、提交、回滚

MySQL默认情况下是自动提交事务。 每一条语句都是一个独立的事务，一旦成功就提交了。一条语句失败，单独一条语句不生效，其他语句是生效的。

15.2.1 手动提交模式

#开启手动提交事务模式
set autocommit = false; 或 set autocommit = 0;
上面语句执行之后，它之后的所有sql，都需要手动提交才能生效，直到恢复自动提交模式。
#恢复自动提交模式
set autocommit = true; 或 set autocommit = 1;

例如：

SET autocommit = FALSE;#设置当前连接为手动提交模式
UPDATE t_employee SET salary = 15000
WHERE ename = '孙红雷';
COMMIT;#提交

例如：

SET autocommit = FALSE;#设置当前连接为手动提交模式
UPDATE t_employee SET salary = 15000
WHERE ename = '孙红雷';
#后面没有提交，直接关了连接，那么这句修改没有生效

15.2.2 自动提交模式下开启事务

/*
也可以在自动提交模式下，开启一个事务。
(1)start transaction;
....
(3)commit; 或 rollback;
在(1)和(3)之间的语句是属于手动提交模式，其他的仍然是自动提交模式。
*/
START TRANSACTION; #开始事务
UPDATE t_employee SET salary = 0
WHERE ename = '李冰冰';
#下面没有写commit;那么上面这句update语句没有正式生效。
commit;#提交
START TRANSACTION;
DELETE FROM t_employee;
ROLLBACK; #回滚

15.2.3 DDL语句不支持事务

#说明：DDL不支持事务
#DDL：create,drop,alter等创建库、创建表、删除库、删除表、修改库、修改表结构等这些语句不支持事务。
#换句话只对insert,update,delete语句支持事务。
TRUNCATE 表名称; 清空整个表的数据，不支持事务。 把表drop掉，新建一张表。
START TRANSACTION;
TRUNCATE t_employee;
ROLLBACK; #回滚 无效

15.3 事务的隔离级别

/*
mysql支持四个隔离级别：
read-uncommitted：会出现脏读、不可重复读、幻读
read-committed：可以避免脏读，会出现不可重复读、幻读
repeatable-read：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个行。
serializable：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个表。
修改隔离级别：
set transaction_isolation='隔离级别';
#mysql8之前 transaction_isolation变量名是 tx_isolation
查看隔离级别：
select @@transaction_isolation;
*/

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

• 脏读：一个事务读取了另一个事务未提交数据；
• 不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
• 幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。

数据库提供的 4 种事务隔离级别：

• Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
• Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。