第五章、多表查询
多表关系
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多(多对一)
案例:部门 与 员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键
多对多
案例:学生 与 课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
案例:用户 与 用户详情的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
多表查询概述
概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
多表查询分类
连接查询
内连接:相当于查询A、B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询
连接查询-内连接
内连接查询语法
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件... ;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件... ;
内连接查询的是两张表交集的部分
内连接演示
1. 查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)
-- 表结构:emp dept
-- 连接条件:emp.dept_id= dept.id
select emp.name , dept.name from emp,dept where emp.dept_id = dept.id ;
select e.name , d.name from emp e , dept d where e.dept_id = d.id;
2. 查询每一个员工的姓名 及关联的部门的名称(显式内连接实现) --- INNER JOIN ... ON ...
-- 表结构:emp,dept
-- 连接条件:emp.dept_id= dept.id
select e.name , d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name , d.name from emp e join dept d on e.dept_id = d.id;
连接查询-外连接
外连接查询语法
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER]JOIN 表2 ON 条件… ;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN表2 ON 条件… ;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
外连接演示
1.查询emp表的所有数据, 和对应的部门信息(左外连接)
-- 表结构:emp , dept
-- 连接条件:emp.dept_id= dept.id
select e.* , d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.* , d.name from emp e left join dept d on e.dept_id = d.id;
2.查询dept表的所有数据, 和对应的员工信息(右外连接)
select d.* , e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.* , e.* from dept d left outer join emp e on e.dept_id = d.id;
连接查询-自连接
自连接查询语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件... ;
自连接查询,可以是内连接查询,也可以是外连接查询。
自连接演示
1. 查询员工及其所属领导的名字
-- 表结构:emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
2.查询所有员工 emp及其领导的名字 emp 如果员工没有领导, 也需要查询出来
-- 表结构:emp a , emp b
select a.name '员工' , b.name '领导' from emp a left join emp b on a.managerid = b.id;
联合查询-union , union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ... ;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
联合查询演示
1. 将新资低于 5000的员工,和 年龄大于 50岁的员工全部查询出来.
select * from emp where salary < 5000
union all
select * from emp where age > 50;
select * from emp where salary < 5000
union
select * from emp where age > 50;
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/ DELETE/SELECT的任何一个。
根据子查询结果不同,分为:
- 标量子查询 (子查询结果为单个值)
- 列子查询 (子查询结果为一列)
- 行子查询 (子查询结果为一行)
- 表子查询 (子查询结果为多行多列)
根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询。
常用的操作符:= <> > >= <
标量子查询演示
1. 查询 "销售部" 的所有员工信息
-- a. 查询 “销售部” 部门ID
select id from dept where name = '销售部';
-- b.根据销售部部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
2. 查询在 "方东白"入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
-- b.查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN、NOT IN、ANY 、SOME、ALL
操作 描述
IN 在指定的集合范围之内,多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中,有任意一个满足即可
SOME 与ANY等同,使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足
列子查询演示
1. 查询“销售部” 和“市场部”的所有员工信息
-- a. 查询“销售部”和“市场部”的部门ID
select id from dept where name = '销售部' or name= '市场部';
-- b.根据部门ID,查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
2. 查询比财务部 所有人工资都高的员工信息
-- a. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from deptwhere name = '财务部');
-- b.比财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
3. 查询比研发部其中任意一人工资高的员工信息
-- a.查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
行子查询
子查询返回的结果是一行(可以是多列) 这种子查询称为行子查询。
常用的操作符:=、<>、IN、NOT IN
行子查询演示
查询与“张无忌”的薪资及直属领导相同的员工信息
-- a. 查询“张无忌”的薪资及直属领导
select salary , managerid from emp where name = '张无忌';
-- b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary , managerid from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
表子查询演示
1. 查询与“鹿杖客”,“宋远桥”的职位和薪资相同的员工信息
-- a. 查询“鹿杖客”,“宋远桥”的职位和薪资
select job , salary from emp where name = '鹿杖客' or name = '宋远桥';
-- b. 查询与“鹿杖客”,“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job , salary) in ( select job , salary from emp where name = '鹿杖客' or name = '宋远桥');
2. 查询入职日期是“2006-01-01”之后的员工信息,及其部门信息
-- a. 入职日期是“2006-01-01”之后的员工信息
select * from emp where entrydate >'2006-01-01';
-- b. 查询这部分员工,对应的部门信息;
select e.* , d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
多表查询案例
1. 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
-- 表:emp , dept
-- 连接条件:emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;
2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
-- 表:emp , dept
-- 连接条件:emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
3.查询拥有员工的部门ID、部门名称
-- 表: emp , dept
-- 连接条件:emp.dept_id = dept.id
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;
4.查询所有年龄大于40岁的员工, 及其归属的部门名称;如果员工没有分配部门,也需要展示出来
-- 表:emp,dept
-- 连接条件:emp.dept_id = dept.id
-- 外连接
select e.* , d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
5.查询所有员工的工资等级
-- 表:emp,salgrade
-- 连接条件:emp.salary >= salgrade.losal and emp.salary
select e.* , s.grade , s.losal , s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary
select e.* , s.grade , s.losal , s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;
6. 查询“研发部”所有员工的信息及 工资等级
-- 表:emp,salgrade, dept
-- 连接条件:emp.salary between salgrade.losal and salgrade.hisal emp.dept_id = dept.id
-- 查询条件:dept.name = '研发部'
select e.* , s.grade
from emp e ,
dept d ,
salgrade s
where e.dept_id = d.id
and (e.salary between s.losal and s.hisal )
and d.name = '研发部';
7.查询“研发部”员工的平均工资
-- 表:emp dept
-- 连接条件:emp.dept_id= dept.id
select avg(e.salary) from emp e , dept d where e.dept_id = d.id and d.name = '研发部';
8. 查询工资比”灭绝”高的员工信息。
-- a. 查询“灭绝”的薪资
select salary from emp where name = '灭绝';
-- b.查询比她工资高的员工数据
select * from emp where salary > ( select salary from emp where name = '灭绝');
9.查询比平均薪资高的员工信息
-- a.查询员工的平均薪资
select avg(salary) from emp;
-- b.查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );
10. 查询低于本部门平均工资的员工信息
-- a.查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id =1;
select avg(e1.salary) from emp e1 where e1.dept_id=2;
-- b.查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
11. 查询所有的部门信息,并统计部门的员工人数
select d.id , d.name , ( select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
select count(*) from emp where dept_id = 1;
12. 查询所有学生的选课情况,展示出学生名称, 学号,课程名称
-- 表:student , course , student_course
-- 连接条件:student.id = student_course.studentid , course.id = student_course.courseid
select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
总结
1.多表关系
一对多:在多的一方设置外键,关联一的一方的主键
多对多:建立中间表,中间表包含两个外键,关联两张表的主键
一对一:用于表结构拆分,在其中任何一方设置外键(UNIQUE),关联另一方的主键
2. 多表查询
内连接
隐式:SELECT … FROM 表A , 表B WHERE 条件...…
显式:SELECT .… FROM 表A INNER JOIN 表B ON 条件......
外连接
左外:SELECT ... FROM 表A LEFT JOIN 表B ON 条件..….
右外:SELECT ...FROM表A RIGHT JOIN 表B ON 条件..….
自连接:SELECT ... FROM 表A 别名1 , 表A 别名2 WHERE 条件......
子查询:标量子查询、列子查询、行子查询、表子查询
第六章、事务
事务简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
事务操作
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0;
提交事务
COMMIT ;
回滚事务
ROLLBACK ;
开启事务
START TRANSACTION 或 BEGIN ;
提交事务
COMMIT ;
回滚事务
ROLLBACK ;
事务四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
问题 描述
脏读 一个事务读到另外一个事务还没有提交的数据。
不可重复读 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了”幻影”。
事务隔离级别
隔离级别 赃读 不可重复读 幻读
Read uncommitted 读未提交 √ √ √
Read committed 读已提交 × √ √
Repeatable Read(默认) 可重复读 × × √
Serializable 串行化 × × ×
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITIED | REPEATABLE READ | SERIALIZABLE }
(SESSION表示只对当前会话有效,GLOBAL表示对整个MySQL实例有效。)
注意:事务隔离级别越高,数据越安全,但是性能越低。
总结
1.事务简介
事务是一组操作的集合,这组操作,要么全部执行成功,要么全部执行失败。
2.事务操作
START TRANSACTION ;-- 开启事务
COMMIT / ROLLBACK; --提交/回滚事务
3. 事务四大特性
原子性(Atomicity)、一致性(Consistency)、隔离性(lsolation)、持久性(Durability)
4.并发事务问题
赃读、不可重复读、幻读
5.事务隔离级别
READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE