目录

1. 表连接查询

1.1 什么是多表查询

1.1.1多表查询的分类：

 1.2笛卡尔积现象

1.2.1什么的笛卡尔积：

1.2.2 如何清除笛卡尔积现象的影响

1.3内连接

1.3.1隐式内连接

 1.3.2显式内连接

 1.3.3总结内连接查询步骤

1.4左外连接

 1.5右外连接

 2.子查询

2.1什么是子查询

2.2子查询结果的三种情况：

 2.3子查询的结果是一个值的时候

2.3.1案例：查询工资最高的员工是谁？

2.3.2查询工资小于平均工资的员工有哪些？

2.4子查询结果是多行单列的时候

2.4.1查询工资大于 5000 的员工，来自于哪些部门的名字

2.4.2查询开发板与财务部所有的员工信息

2.5子查询的结果是多行多列

2.5.1查询出 2011 年以后入职的员工信息，包括部门名称

2.6子查询小结

3.事务

3.1事务的应用场景说明

3.2手动提交事务

3.2.1手动提交事务的 SQL 语句

 3.2.2手动提交事务使用过程：

 3.2.3模拟张三给李四转钱500元(成功)

3.2.4案例演示 2：事务回滚

3.3自动提交事务

 3.3.1案例演示 3：自动提交事务

 3.3.2取消自动提交

 3.4事务原理

3.4.1原理图：

3.4.2 事务的步骤：

3.5回滚点

3.5.1什么是回滚点

3.5.2回滚点的操作语句

3.5.3具体操作：

3.6事务的隔离级别

3.6.1事务的四大特性ACID

3.6.2事务的隔离级别

3.6.3MySQL 数据库有四种隔离级别

3.6.4MySQL事务隔离级别相关命令

3.6.5脏读的演示

3.6.6窗口重复读的演示

3.6.7幻读的演示

4.DCL(Data Control Language)

4.1创建用户

4.1.1语法：

4.1.2关键字说明

4.1.3具体操作

4.2给用户授权

4.2.1语法

4.2.2关键字说明

4.2.3具体操作

4.3撤销授权

4.3.1语法：

4.3.2具体操作

4.4查看权限

4.4.1SHOW GRANTS FOR '用户名'@'主机名'；

4.4.2具体操作：

4.5删除用户

4.5.1语法

4.5.2具体操作

4.6修改管理员密码

4.6.1语法

4.6.2具体操作

 4.7修改普通用户密码

4.7.1语法

6.7.2具体操作

---

1. 表连接查询

1.1 什么是多表查询

数据准备

# 创建部门表
create table dept(
id int primary key auto_increment, 
name varchar(20)
)
 
insert into dept (name) values ('开发部'),('市场部'),('财务部'); # 创建员工表
create table emp (
id int primary key auto_increment, 
name varchar(10),
gender char(1),		-- 性别
salary double,	-- 工资
join_date date,	-- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)
 
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008- 08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

多表查询的作用：

比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。

如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

1.1.1多表查询的分类：

 1.2笛卡尔积现象

1.2.1什么的笛卡尔积：

-- 需求：查询所有的员工和所有的部门
select * from emp,dept;

结果分析：

1.2.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 设置过滤条件 Column 'id' in where clause is ambiguous 
select * from emp,dept where id=5;
 
select * from emp,dept where emp.`dept_id` = dept.`id`;
 
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

1.3内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键。

1.3.1隐式内连接

隐式内连接：看不到JOIN关键字，条件使用WHERE指定

SELECT 字段名 FROM 左表 ，右表 WHERE 条件

select * from emp,dept where emp.`dept_id` = dept.`id`;

 1.3.2显式内连接

显示内连接：使用INNER JOIN ... ON语句，可以省略INNER

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接。

1.确定要查询哪些表

select * from emp inner join dept；

2.确定表连接条件，员工.dept_id = 部门表.id 的数据才是有效的

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

 3.确定查询条件，外面查询的是唐僧的信息，员工表.name = "唐僧"

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

4.确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

 

5.我们发现写表名有点长，可以给表取别名，显示的字段名也可以使用别名

select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

 1.3.3总结内连接查询步骤

1. 确定查询哪些表
2. 确定表连接的条件
3. 确定查询的条件
4. 确定查询的字段

1.4左外连接

左外连接：使用LEFT OUTER JOIN ... ON,OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部'); select * from dept;
 
-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
 
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;

 1.5右外连接

右外连接:使用 RIGHT OUTER JOIN ... ON ,OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL

可以理解为：在内连接的基础上保证右表的数据全部显示

-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null); 
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
 
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

 2.子查询

2.1什么是子查询

-- 需求：查询开发部中有哪些员工
select * from emp;
 
-- 通过两条语句查询
select id from dept where name='开发部' ; 
select * from emp where dept_id = 1;
-- 使用子查询
select * from emp where dept_id = (select id from dept where name='市场部');

 子查询的概念：

1. 一个查询的结果做为另一个查询的条件
2. 有查询的嵌套，内部的查询称为子查询
3. 子查询要使用括号

2.2子查询结果的三种情况：

1.子查询的结果是单行单列

2.子查询的结果是多行单列

3.子查询的结果是多行多列

 2.3子查询的结果是一个值的时候

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

SELECT 查询字段 FROM 表 WHERE 字段=(子查询)；

2.3.1案例：查询工资最高的员工是谁？

-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);

2.3.2查询工资小于平均工资的员工有哪些？

-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

2.4子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN(子查询)；

2.4.1查询工资大于 5000 的员工，来自于哪些部门的名字

-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字	Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
 
select name from dept where id in (select dept_id from emp where salary > 5000);

2.4.2查询开发板与财务部所有的员工信息

-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));

2.5子查询的结果是多行多列

子查询结果只要是多列，肯定在FROM后面作为表

SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件；

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

2.5.1查询出 2011 年以后入职的员工信息，包括部门名称

-- 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
 
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
 
-- 也可以使用表连接;
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

2.6子查询小结

1. 子查询结果只要是单列，则在 WHERE 后面作为条件
2. 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

3.事务

3.1事务的应用场景说明

什么是事务：

在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。

转账的操作：

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT, 
NAME VARCHAR(10),
balance DOUBLE
);
 
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

张三账号-500

李四账号+500

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

3.2手动提交事务

MYSQL 中可以有两种方式进行事务的操作：

1. 手动提交事务
2. 自动提交事务

3.2.1手动提交事务的 SQL 语句

 3.2.2手动提交事务使用过程：

 3.2.3模拟张三给李四转钱500元(成功)

目前数据库数据如下：

 

1. 使用 DOS 控制台进入 MySQL
2. 执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
3. 使用 SQLYog 查看数据库：发现数据并没有改变
4. 在控制台执行 commit 提交事务：
5. 使用 SQLYog 查看数据库：发现数据改变

3.2.4案例演示 2：事务回滚

模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：

1. 在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
2. 使用 SQLYog 查看数据库：发现数据并没有改变
3. 在控制台执行 rollback 回滚事务：
4. 使用 SQLYog 查看数据库：发现数据没有改变

总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL

语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

3.3自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务

 3.3.1案例演示 3：自动提交事务

1. 将金额重置为 1000
2. 更新其中某一个账户
3. 使用 SQLYog 查看数据库：发现数据已经改变

 3.3.2取消自动提交

查看MySQL是否开启自动提交事务

@@表示全局变量，1表示开启，0表示关闭

取消自动提交业务

 执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变

在控制台执行 commit 提交任务

 3.4事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

3.4.1原理图：

3.4.2 事务的步骤：

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
2. 开启事务以后，所有的操作都会先写入到临时日志文件中
3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回
4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

3.5回滚点

3.5.1什么是回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

3.5.2回滚点的操作语句

3.5.3具体操作：

1. 将数据还原到 1000
2. 开启事务
3. 让张三账号减 3 次钱，每次 10 块
4. 设置回滚点：savepoint three_times;
5. 让张三账号减 4 次钱，每次 10 块
6. 回到回滚点：rollback to three_times;
7. 分析执行过程
8. 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

3.6事务的隔离级别

3.6.1事务的四大特性ACID

3.6.2事务的隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

3.6.3MySQL 数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

隔离级别越高，性能越差，安全性越高

3.6.4MySQL事务隔离级别相关命令

查询全局事务隔离级别

查询隔离级别 select @@tx_iolation;

设置事务隔离级别，需要推出MySQL再重新登录才能看到隔离级别的变化

设置隔离级别 set global transaction isolation level 级别字符串；

3.6.5脏读的演示

将数据进行恢复：UPDATE account SET balance = 1000；

1.打开A窗口登录MySQL，设置全局的隔离级别为最低

mysql -uroot -proot
set global transaction isolation level read uncommitted;

 2.打开B窗口，AB窗口都开启事务

use day23;
start transaction;

 3.A窗口更新两个人的账户数据，为提交

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

 4.B窗口查询账户

select * from account;

 5.A窗口回滚

rollback;

 

6. B窗口查询账户，钱没了

 脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升

将数据进行恢复：

UPDATE account SET balance = 1000;

1.在A窗口设置全局的隔离级别为read committed

set global transaction isolation level read committed;

B 窗口退出 MySQL，B 窗口再进入 MySQL

 AB窗口提示开启事务

2.A更新两个人的账户，未提交

update account set balance=balance-500 where id=1; 
update account set balance=balance+500 where id=2;

 3.B窗口查询账户

 A 窗口 commit 提交事务

4.B窗口查看账户

 结论：read committed 的方式可以避免脏读的发生

3.6.6窗口重复读的演示

将数据进行恢复：
 

UPDATE account SET balance = 1000;

1.开启A窗口

set global transaction isolation level read committed;

 2.开启B窗口，在B窗口开启事务

start transaction; 
select * from account;

 3.在A窗口开启事务，并更新数据

start transaction;
update account set balance=balance+500 where id=1; 
commit;

4.B窗口查询

select * from account;

 两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：

将全局的隔离级别进行提升为：repeatable read

将数据进行恢复：

UPDATE account SET balance = 1000;

1.A窗口设置隔离级别为：repeatable read

set global transaction isolation level repeatable read;

 2.B窗口推出MySQL，B窗口再进入MySQL

start transaction; 
select * from account;

3.A窗口更新数据

start transaction;
update account set balance=balance+500 where id=1; 
commit;

4.B窗口查询

select * from account;

结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

3.6.7幻读的演示

在 MySQL 中无法看到幻读的效果。

但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：

结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读

4.DCL(Data Control Language)

1. DDL： create / ater / drop
2. DML：insert /update/delete
3. DQL ：select /show
4. DCL ：grant /revoke

我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

4.1创建用户

4.1.1语法：

CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码"；

4.1.2关键字说明

4.1.3具体操作

创建 user1 用户，只能在 ocalhost 这个服务器登录 mysq 服务器，密码为 123

create user 'user1'@'localhost' identified by '123';

创建 user2 用户可以再任何电脑上登录MySQL服务器，密码为 123

create user 'user2'@'%' identified by '123';

注意：创建的用户名都在mysql数据库中的user表中可以查看到，密码经过了加密

4.2给用户授权

创建用户之后，没有什么权限！

4.2.1语法

GRANT 权限 1，权限2 ... ON 数据库名.表名 TO "用户名"@主机名；

4.2.2关键字说明

4.2.3具体操作

给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询

grant create,alter,insert,update,select on test.* to 'user1'@'localhost';

注意：用户名和主机名要与上面创建的相同，要加单引号

给 user2 用户分配所有权限，对所有数据库的所有表

grant all on *.* to 'user2'@'%';

4.3撤销授权

4.3.1语法：

REVOKE 权限1，权限2 ... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost';'用户名'@'主机名'

4.3.2具体操作

撤销 user1 用户对 test 数据库所有表的操作的权限

revoke all on test.* from 'user1'@'localhost';

• 注：用户名和主机名要与创建时相同，各自要加上单引号

4.4查看权限

4.4.1SHOW GRANTS FOR '用户名'@'主机名'；

4.4.2具体操作：

查看user1用户的权限

• 注：usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。

4.5删除用户

4.5.1语法

DROP USER'用户名'@'主机名';

4.5.2具体操作

删除user2

drop user 'user2'@'%';

4.6修改管理员密码

4.6.1语法

mysqladmin -uroot -p password 新密码

• 注意：需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。

4.6.2具体操作

1. 将 root 管理员的新密码改成 123456
2. 要求输入旧密码
3. 使用新密码登录

 4.7修改普通用户密码

4.7.1语法

set password for '用户名'@'主机名' = password("新密码")；

• 注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。

6.7.2具体操作

1. 将'user1'@'localhost'的密码改成'666666'
2. 使用新密码登录，老密码登录不了