今日内容--Mysql多表和事务
- 多表查询
- 事务
- DCL
一. 多表查询(重点)
1. 概述
思路:
1.解释什么是多表查询
2.演示笛卡尔积,引出一个简单的多表查询,分析数据冗余需要消除无用的数据
3.多表查询有哪些?
# 创建部门表
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),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);
-
查询多个表的数据称为多表查询
-
演示笛卡尔积
- 有两个集合A,B.取这两个集合所有的排列组合情况. - 要完成多表查询,需要消除无用的数据.
-
多表查询的分类
- 内连接查询
- 外连接查询
- 子查询
2. 内连接
思路:
1.演示where条件去除笛卡尔积中错误的数据
2.查询部分字段的数据
3.别名简化sql
4.sql语句规范化介绍
5.介绍显示内连接的语法
2.1 隐式内连接
- 语法
select 字段列表 from 表1 , 表2 where 主外键关联条件;
- 示例
-- 单表查询
SELECT * FROM EMP;
SELECT * FROM dept;
-- 多表查询 笛卡尔积
SELECT * FROM EMP,dept;
-- 隐式内连接:使用where条件 去消除 错误的数据
SELECT * FROM dept ,emp WHERE dept.id=emp.dept_id
-- 查询部分字段:员工的姓名,工资,部门名称
SELECT emp.name ,emp.salary,dept.name FROM dept ,emp WHERE dept.id=emp.dept_id
-- 使用别名简化
SELECT e.name ,e.salary,d.name FROM dept d ,emp e WHERE d.id=e.dept_id
- 代码规范化
-- 规范sql
select
e.name, -- 员工姓名
e.salary, -- 员工工资
d.name -- 部门名称
from
dept d, -- 部门表
emp e -- 员工表
where
d.id=e.dept_id; -- 主外键关联条件
2.2 显示内连接
- 语法
select 字段列表 from 表1 [inner] join 表2 on 主外键关联条件; -- []表示 inner 可以省略
- 示例
-- 显示内连接,查询两个表的所有字段
select * from emp e inner join dept d on d.id=e.dept_id;
- 注意
1.隐式内连接是mysql的方言版
2.显示内连接才是sql的标准版
3.在mysql数据库中,两者原理相同,只是写法上有区别。
* , 换成 inner join
* where 换成了 on
3. 外连接
思路:
1.演示左外和右外连接
2.对比两者的区别
3.1 左外连接
- 语法
select 字段列表 from 表1 left [outer] join 表2 where 主外键关联条件; -- []表示 outer 可以省略
- 示例
-- 查询出左表所有的数据 以及 满足条件的(和右表匹配的)数据
select * from emp e left join dept d on e.dept_id=d.id;
3.2 右外连接
- 语法
select 字段列表 from 表1 right [outer] join 表2 where 主外键关联条件; -- []表示 outer 可以省略
- 示例
-- 查询出右表所有的数据 以及 满足条件的(和左表匹配的)数据
select * from emp e right join dept d on e.dept_id=d.id;
-
模拟实现全外连接(补充)
1.合并两个列数和列类型相同的的结果集: union all(不会去重) 和 union (会去重) 2.合并结果集模拟全外连接(mysql并不支持全外连接)
- 实例1
-- 将部门表和员工表的id和name两列结果集合并 SELECT id ,NAME FROM emp UNION SELECT id ,NAME FROM dept;
- 示例2
(select * from emp e left join dept d on e.dept_id=d.id) -- 左外连接 union (select * from emp e right join dept d on e.dept_id=d.id); -- 右外连接
-- 演示笛卡尔积
SELECT * FROM dept d,emp e;
-- 内连接查询 隐试--方言
SELECT * FROM dept d,emp e WHERE d.`id`=e.`dept_id`;
-- 内连接查询 显试--标准
SELECT * FROM dept d JOIN emp e ON d.`id`=e.`dept_id`;
-- 外连接 左外连接
SELECT * FROM dept d LEFT JOIN emp e ON d.`id`=e.`dept_id`;
-- 外连接 右外连接
SELECT * FROM dept d RIGHT JOIN emp e ON d.`id`=e.`dept_id`;
-- 模拟实现全外连接 ,并去重(去掉all即可)
SELECT * FROM dept d LEFT JOIN emp e ON d.`id`=e.`dept_id`
UNION
SELECT * FROM dept d RIGHT JOIN emp e ON d.`id`=e.`dept_id`;
-- 纵向合并结果集(列数相同,列类型相同) 去重(去掉all即可)
SELECT id,NAME FROM dept UNION ALL SELECT dept_id,NAME FROM emp;
SELECT id,NAME FROM dept UNION ALL SELECT id,NAME FROM emp;
内连接
外连接 左外连接
外连接 右外连接
模拟实现全外连接 ,并去重(去掉all即可)
纵向合并结果集(列数相同,列类型相同) 去重(去掉all即可)
4. 子查询
4.1 概述
思路:
1.解释什么是子查询?
2.演示案例--查询工资最高的员工信息
- 查询中嵌套查询–称为子查询
-- 查询工资最高的员工信息
-- 拆分需求:1.查询最高工资是多少
select max(salary) from emp; -- 结果 8000
-- 2.查询对应的员工信息
select * from emp where salary=8000;
-- 合并连个sql 实现动态查询
select * from emp where salary=(select max(salary) from emp);
4.2 子查询的四种情况
-
根据子查询的结果集分为四种情况:
- 单行单列
-- 需求:查询工资小于平均工资的员工信息 -- 1.先查平均工资 select avg(salary) from emp;-- 结果5000 -- 2.再查小于5000的员工信息 select * from emp where salary < 5000; -- 结果集为的单行单列 -- 合并两个sql ,实现动态查询 select * from emp where salary < (select avg(salary) from emp); -- 总结:使用运算符: > < >= <=
- 多行单列
-- 需求:查询'信息部'和'开发部'的所有员工信息 -- 1.查询'信息部'和'开发部'的部门编号 select id from dept where name in ('信息部','开发部'); -- 结果集为的多行单列 -- 2.查询对应的员工 select * from emp where dept_id in (1,2); -- 合并,实现动态查询 select * from emp where dept_id in (select id from dept where name in ('信息部','开发部')); -- 总结:使用 in
- 多行多列
- 示例1
-- 需求:查询工资大于平均工资的的员工的信息和部门信息 -- 第一种实现:内连接 select * from emp e ,dept d where e.dept_id=d.id and e.salary >(select avg(salary) from emp); -- 第二种实现:子查询 -- 先查工资大于平均工资的员工 select * from emp where salary >(select avg(salary) from emp); -- 再把上面的结果集作为一个虚拟表和部门表进行内连接 select * from dept d , (select * from emp where salary >(select avg(salary) from emp)) t where d.id=t.dept_id ;
- 示例2
-- 查询 入职时间 大于 2011-11-11 的员工信息和部门信息 -- 1.先查询 入职时间 大于 2011-11-11 的员工信息 2.在和部门表去结合 (内连接方言版) select * from emp where join_date > '2011-11-11'; select * from (select * from emp where join_date > '2011-11-11' ) e,dept where e.dept_id=dept.id; -- 2.先将emp和dept使用内连接结合 2.再去进行条件筛选 大于 2011-11-11 select * from emp,dept where emp.dept_id=dept.id and emp.join_date > '2011-11-11';
- 单行多列
-- 单行多列 in 理解为一个对象(少用) -- 需求案例:找出工资和部门与白骨精相同的人 -- 1.先查询白骨精的工资和部门编号 select salary ,dept_id from emp where name='白骨精'; -- 2.再使用子查询 in SELECT * FROM emp WHERE (salary,dept_id) IN (5000,3); -- 不支持的 select * from emp where (salary ,dept_id) in (select salary ,dept_id from emp where name='白骨精');
5. 多表查询练习
-
准备工作
-- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); -- 添加4个部门 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'学工部','上海'), (30,'销售部','广州'), (40,'财务部','深圳'); -- 职务表,职务名称,职务描述 CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) ); -- 添加4个职务 INSERT INTO job (id, jname, description) VALUES (1, '董事长', '管理整个公司,接单'), (2, '经理', '管理部门员工'), (3, '销售员', '向客人推销产品'), (4, '文员', '使用办公软件'); -- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id ename VARCHAR(50), -- 员工姓名 job_id INT, -- 职务id mgr INT , -- 上级领导 joindate DATE, -- 入职日期 salary DECIMAL(7,2), -- 工资 bonus DECIMAL(7,2), -- 奖金 dept_id INT, -- 所在部门编号 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) ); -- 添加员工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10); -- 工资等级表 CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 级别 losalary INT, -- 最低工资 hisalary INT -- 最高工资 ); -- 添加5个工资等级 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);
-
练习
分析思路: 1.所查的字段属于哪些表? 2.使用什么技术去查?查看架构设计图 -- 内、外连接,子查询 3.分析表连接需要满足的条件
题目:
-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述 -- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置 -- 3.查询员工姓名,工资,工资等级 -- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级 -- 5.查询出部门编号、部门名称、部门位置、部门人数 -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
答案:
-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述 SELECT e.id,-- 员工编号 e.ename,-- 员工姓名 e.salary,-- 工资 j.jname,-- 职务名称 j.description -- 职务描述 FROM emp e,job j WHERE e.job_id=j.id;
-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置 SELECT e.id,-- 员工编号 e.ename,-- 员工姓名 e.salary,-- 工资 j.jname, -- 职务名称 j.description,-- 职务描述 d.dname,-- 部门名称 d.loc -- 部门位置 FROM emp e,-- 员工表 job j,-- 职务表 dept d -- 部门表 WHERE e.dept_id=d.id -- 条件1 AND e.job_id=j.id; -- 条件2
-- 3.查询员工姓名,工资,工资等级 SELECT e.ename,-- 员工姓名 e.salary,-- 工资 s.* -- 工资等级 FROM emp e,-- 员工表 salarygrade s -- 工资表 WHERE e.salary BETWEEN s.losalary AND s.hisalary ; -- 条件
-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级 SELECT e.ename,-- 员工姓名 e.salary,-- 工资 j.jname,-- 职务名称 j.description,-- 职务描述 d.dname,-- 部门名称 d.loc,-- 部门位置 s.grade -- 工资等级 FROM emp e,-- 员工表 job j,-- 职务表 dept d,-- 部门表 salarygrade s -- 工资表 WHERE e.dept_id=d.id -- 条件1 员工表和部门表关联 AND e.job_id=j.id -- 条件2 员工表和职务表关联 AND e.salary BETWEEN s.losalary AND s.hisalary;-- 条件3 员工表和工资表关联
-- 5.查询出部门编号、部门名称、部门位置、部门人数 -- 第一步:对员工表分组查询,统计出部门人数 SELECT e.dept_id,-- 部门编号 COUNT(id) -- 人数 FROM emp e -- 员工表 GROUP BY e.dept_id -- 根据部门编号分组 -- 第二步:把第一步的结果集作为一个虚拟表,关联部门表进行子查询 SELECT d.*,-- 部门表所有字段 e.total -- 部门人数 FROM dept d,-- 部门表 (SELECT dept_id ,COUNT(id) total FROM emp GROUP BY dept_id) e WHERE d.id=e.dept_id; -- 条件
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 SELECT e1.ename,-- 员工的姓名 e1.mgr,-- 员工的上级编号 e2.id,-- 直接上级的编号 e2.ename -- 直接上级的姓名 FROM emp e1,-- 员工表 自己和自己进行内连接 emp e2 -- 员工表 WHERE e1.mgr=e2.id; -- 关联条件
二.事务(了解)
- 概念
事务是把多个操作捆绑成一个整体,事务中的若干个操作要么全部执行成功,要么全部撤回。
- 事务操作
1.开启事务 start transaction;
2.回滚事务 rollback;
3.提交事务 commit;
1.基本演示
思路:
1.介绍课程目标:
理解事务的概念 事务的四大特征 了解事务的隔离级别
2.阐述事务的概念
如果一个包含多个步骤的业务被事务管理,该业务的多个操作要么全部成功,要么全部失败。
3.举例--张三给李四转账
-- 创建账户表
create table account(
id int primary key auto_increment,
name varchar(20),
balance double
);
insert into account values (null,'曹操',1000), (null,'刘备',1000);
-- 1.拆分转账步骤:
-- 曹操的账户金额减少500
update account set balance =balance-500 where name ='曹操';
-- 刘备的账户金额增加500
update account set balance =balance+500 where name ='刘备';
-- 2.还原数据,在两个操作中间加点异常
update account set balance =1000;
-- 曹操的账户金额减少500
update account set balance =balance-500 where name ='曹操';
-- 加个异常
aaa
-- 刘备的账户金额增加500
update account set balance =balance+500 where name ='刘备';
-- 3.结果曹操的金额减少500,刘备的金额没有发生变化 -- 再次还原数据
update account set balance =1000;
-- 添加事务管理
start transaction;
-- 曹操的账户金额减少500
update account set balance =balance-500 where name ='曹操';
-- 中间加个异常
aaa
-- 刘备的账户金额增加500
update account set balance =balance+500 where name ='刘备';
-- 新开启一个本地连接窗口,对比两个窗口数据,发现新窗口数据不变,说明事务在开启后,没有提交,或没有回滚,数据只是在窗口暂时发生了变化,并没有对数据库作永久的更改。此时采取回滚操作
rollback;
-- 再次观察数据,发现数据已经还原
-- 4.开启事务,不加异常,最后提交事务。
commit;
2. 默认自动提交&手动提交
思路:
1.介绍mysql是自动提交事务的,为每一条dml语句自动添加一个事务管理。
执行每一个dml语句前,会自动开启一个事务,执行完成后,如果执行成功就自动提交事务,否则自动回滚。
2.可以手动把多个dml语句绑定在一个事务内,即上面的转账案例。
3.查看mysql默认自动提交事务
4.修改为手动提交事务
select @@autocommit;-- 查看自动提交
set @@autocommit=0; -- 1表示自动提交 0表示手动提交
- 示例:演示一下手动提交
-- 一. 查询提交方式,mysql默认是1,表示自动提交
SELECT @@autocommit; -- 1
-- 修改曹操的金额为500
UPDATE account SET balance=500 WHERE id=1;
-- 发现在新开的连接窗口,数据也发生了变化,不需要手动提交。
-- 二. 设置为手动提交
SET @@autocommit=0;
SELECT @@autocommit; -- 0
-- 修改曹操的金额为300
UPDATE account SET balance=300 WHERE id=1;
-- 发现在新开的连接窗口,数据没有发生变化。
COMMIT;-- 手动提交之后,新开的连接窗口数据才发生变化。
3. 事务四大特征
1.原子性:是不可能分割的最小单位,把一个事务包含的多个操作看成一个整体,缩成一个原子,这些个操作要么全部成功,要么全部失败。
2.持久性:当事务提交或回滚后,数据库会持久化的保存数据
3.隔离性:多个事务之间,相互独立
4.一致性:事务操作前后,数据总量不变。
4. 事务隔离级别介绍
思路:
1.概念:多个事务之间是相互独立的。但如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别,就可以解决这些问题。
2.存在的问题:
脏读:一个事务,读取到另一个事务中没有提交的数据
不可重复读(虚读):在同一个事务中,两次读到的数据不一样
幻读:一个事务操作数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
3.隔离级别:
3.1 read uncommitted :读未提交 --存在: 脏读,虚读,幻读
3.2 read committed :读已提交 -- 存在: 虚读,幻读 (Oracle 默认)
3.3 repeatable read :可重复读 -- 存在: 幻读 (MySQL 默认)
3.4 serializable:串行化,锁表 -- 不存在任何问题
4.注意:
隔离级别从小到大安全性越来越高,但是效率越来越低
-- 查询数据库隔离级别
select @@tx_isolation;
-- 设置数据库隔离级别
set global transaction isolation level 级别字符串;
5. 事务–隔离级别演示
1.设置事务隔离级别为 read uncommitted
2.重新开启两个连接窗口
3.两个窗口都开启事务,开始执行转账,先不提交
4.发现在另一个窗口开启事务后,可以查询出数据已经发生改变,此为脏读;并在同一个事务中,两次读的数据不一样,此为虚读。
1.设置事务隔离级别为 read committed
2.重新开启两个连接窗口
3.两个窗口都开启事务,开始执行转账,先不提交
4.发现在另一个窗口开启事务后,不能查询出数据已经发生改变
5.在第一个窗口去提交事务
6.发现在另一个窗口可以查询出数据发生改变了,此为虚读。因为两次查询的数据不一样。
1.设置事务隔离级别为 repeatable read
2.重新开启两个连接窗口
3.两个窗口都开启事务,开始执行转账,先不提交
4.发现在另一个窗口开启事务后,不能查询出数据已经发生改变
5.在第一个窗口去提交事务
6.发现在另一个窗口依然不能查询出数据发生改变,
7.在第二个窗口也提交事务,然后再查询,才发现数据发生改变
1.设置事务隔离级别为 serializable -- 相当于锁表
2.重新开启两个连接窗口
3.两个窗口都开启事务,开始执行转账,先不提交
4.发现在另一个窗口开启事务后,不能查询出表数据,线程处于暂停状态,账户表被锁住了
5.在第一个窗口去提交事务,此时另一个窗口立即显示出表数据
三.DCL学习
data control language (了解)
1. 管理用户
1.1 增删查
1.查看mysql数据库中的user表 -- % 通配符 的含义
2创建和删除一个用户
-- 创建一个用户 zs,密码 666
create user '用户名'@'主机名' identified by '密码';
-- 删除一个用户
drop user '用户名'@'主机名';
1.2 修改密码
-
修改密码的两种方式
-- 使用dml update user set password=password('新密码') where user='用户名'; FLUSH PRIVILEGES; -- 刷新权限 -- 使用dcl SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
-
忘记root用户的密码怎么办?
1. cmd -- > net stop mysql 停止mysql服务 * 需要管理员运行该cmd 2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables 3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功 4. use mysql; 5. update user set password = password('你的新密码') where user = 'root'; 6. 关闭两个窗口 7. 打开任务管理器,手动结束mysqld.exe 的进程 8. 启动mysql服务 9. 使用新密码登录。
2. 管理权限
-
1. 查询权限: -- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; -- 例如: SHOW GRANTS FOR 'lisi'@'%'; SHOW GRANTS FOR 'root'@'%'; 2. 授予权限: -- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 例如:给张三用户授予所有权限,在任意数据库任意表上 GRANT ALL ON *.* TO 'zhangsan'@'localhost'; 3. 撤销权限: -- 撤销权限: revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; -- 例如:撤销李四对数据库db3的修改权限 REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
总结概括
1.多表
内连接
隐式内连接 select * from 表1,表2 where 主外键关联条件;
显示内连接 select * from 表1 inner join 表2 on 主外键关联条件;
外连接
左外 select * from 表1 left join 表2 on 主外键关联条件;
右外 select * from 表1 right join 表2 on 主外键关联条件;
子查询
单行单列 > < = 作为where后的条件
多行单列 in 作为where后的条件
多行多列 from 后面作为虚拟表
单行多列 通常看成是一个对象 where (id,name) in (select id,name from 表)
2.事务
操作:开启 回滚 提交
四大特性:原子性 持久性 隔离性 一致性
多个事务操作同一批数据存在问题:脏读 不可重复读 幻读
设置隔离级别解决以上的问题:
read uncommitted;
read committed;
repeatable read;
serializable;