目录
2.4.1查询工资大于 5000 的员工，来自于哪些部门的名字
2.5.1查询出 2011 年以后入职的员工信息，包括部门名称
4.4.1SHOW GRANTS FOR '用户名'@'主机名'；
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.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='市场部');子查询的概念：
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套，内部的查询称为子查询
- 子查询要使用括号
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子查询小结
- 子查询结果只要是单列，则在 WHERE 后面作为条件
- 子查询结果只要是多列，则在 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 中可以有两种方式进行事务的操作：
- 手动提交事务
- 自动提交事务
3.2.1手动提交事务的 SQL 语句
3.2.2手动提交事务使用过程：
- 执行成功的情况： 开启事务 à 执行多条 SQL 语句 à 成功提交事务
- 执行失败的情况： 开启事务 à 执行多条 SQL 语句 à 事务的回滚
3.2.3模拟张三给李四转钱500元(成功)
目前数据库数据如下：
- 使用 DOS 控制台进入 MySQL
- 执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
- 使用 SQLYog 查看数据库：发现数据并没有改变
- 在控制台执行 commit 提交事务：
- 使用 SQLYog 查看数据库：发现数据改变
3.2.4案例演示 2：事务回滚
模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：
- 在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
- 使用 SQLYog 查看数据库：发现数据并没有改变
- 在控制台执行 rollback 回滚事务：
- 使用 SQLYog 查看数据库：发现数据没有改变
总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL
语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
3.3自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务
3.3.1案例演示 3：自动提交事务
- 将金额重置为 1000
- 更新其中某一个账户
- 使用 SQLYog 查看数据库：发现数据已经改变
3.3.2取消自动提交
查看MySQL是否开启自动提交事务
@@表示全局变量，1表示开启，0表示关闭
取消自动提交业务
执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变
在控制台执行 commit 提交任务
3.4事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)
3.4.1原理图：
3.4.2 事务的步骤：
- 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
- 开启事务以后，所有的操作都会先写入到临时日志文件中
- 所有的查询操作从表中查询，但会经过日志文件加工后才返回
- 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。
3.5回滚点
3.5.1什么是回滚点
在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。
3.5.2回滚点的操作语句
| 回滚点的操作语句 语句 | |
| 设置回滚点 | savepoint 名字 |
| 回到回滚点 | rollback to 名字 |
3.5.3具体操作：
- 将数据还原到 1000
- 开启事务
- 让张三账号减 3 次钱，每次 10 块
- 设置回滚点：savepoint three_times;
- 让张三账号减 4 次钱，每次 10 块
- 回到回滚点：rollback to three_times;
- 分析执行过程
- 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。
3.6事务的隔离级别
3.6.1事务的四大特性ACID
| 事务特性 含义 | |
| 原子性（Atomicity） | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。 |
| 一致性（Consistency） | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2 个人的 总金额是 2000，转账后 2 个人总金额也是 2000 |
| 隔离性（Isolation） | 事务与事务之间不应该相互影响，执行时保持隔离的状态。 |
| 持久性（Durability） | 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。 |
3.6.2事务的隔离级别
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：
| 并发访问的问题 含义 | |
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复读 | 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这 是事务 update 时引发的问题 |
| 幻读 | 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是 insert 或 delete 时引发的问题 |
3.6.3MySQL 数据库有四种隔离级别
上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。
| 级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别 | ||||||
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
| 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle 和 SQL Server |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL
|
| 4 | 串行化 | serializable | 否 | 否 | 否 | |
隔离级别越高，性能越差，安全性越高
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)
- DDL： create / ater / drop
- DML：insert /update/delete
- DQL ：select /show
- DCL ：grant /revoke
我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。
4.1创建用户
4.1.1语法：
CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码"；
4.1.2关键字说明
| 关键字 说明 | |
| '用户名' | 将创建的用户名 |
| '主机名' | 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以 从任意远程主机登陆，可以使用通配符% |
| '密码' | 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器 |
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关键字说明
| 关键字 说明 | |
| GRANT…ON…TO | 授权关键字 |
| 权限 | 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授 予所有的权限则使用 ALL |
| 数据库名.表名 | 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作 权限则可用*表示，如*.* |
| '用户名'@'主机名' | 给哪个用户授权，注：有 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';'用户名'@'主机名'
| 关键字 说明 | |
| REVOKE…ON…FROM | 撤销授权的关键字 |
| 权限 | 用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权 限则使用 ALL |
| 数据库名.表名 | 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表 示，如*.* |
| '用户名'@'主机名' | 给哪个用户撤销 |
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具体操作
- 将 root 管理员的新密码改成 123456
- 要求输入旧密码
- 使用新密码登录
4.7修改普通用户密码
4.7.1语法
set password for '用户名'@'主机名' = password("新密码")；
- 注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。
6.7.2具体操作
- 将'user1'@'localhost'的密码改成'666666'
- 使用新密码登录，老密码登录不了
650
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
