MySQL笔记-多表查询

本文标签 : 多表查询  事务四大特性  并发事务问题  事务隔离级别

一、多表查询

1.多表关系

• 概述:

• 一对多(多对一): 

• 多对多:   

演示:

-- -------------------------------- 多表关系 演示 ---------------------------------------------

-- 多对多 ----------------
create table student(
    id int auto_increment primary key ***ment '主键ID',
    name varchar(10) ***ment '姓名',
    no varchar(10) ***ment '学号'
) ***ment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');


create table course(
    id int auto_increment primary key ***ment '主键ID',
    name varchar(10) ***ment '课程名称'
) ***ment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');


create table student_course(
    id int auto_increment ***ment '主键' primary key,
    studentid int not null ***ment '学生ID',
    courseid  int not null ***ment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)***ment '学生课程中间表';

insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

student_course : 

student :

course :

 

关系视图 : 

 

•  一对一 :

演示: 

-- --------------------------------- 一对一 ---------------------------
create table tb_user(
    id int auto_increment primary key ***ment '主键ID',
    name varchar(10) ***ment '姓名',
    age int ***ment '年龄',
    gender char(1) ***ment '1: 男 , 2: 女',
    phone char(11) ***ment '手机号'
) ***ment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key ***ment '主键ID',
    degree varchar(20) ***ment '学历',
    major varchar(50) ***ment '专业',
    primaryschool varchar(50) ***ment '小学',
    middleschool varchar(50) ***ment '中学',
    university varchar(50) ***ment '大学',
    userid int unique ***ment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) ***ment '用户教育信息表';


insert into tb_user(id, name, age, gender, phone) values
        (null,'黄渤',45,'1','18800001111'),
        (null,'冰冰',35,'2','18800002222'),
        (null,'码云',55,'1','18800008888'),
        (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
        (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
        (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
        (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

tb_user :

tb_user_edu :

2.多表查询概念

-- ------------------------------------> 多表查询 <--------------------------------------------
-- 准备数据
create table dept(
    id   int auto_increment ***ment 'ID' primary key,
    name varchar(50) not null ***ment '部门名称'
)***ment '部门表';

create table emp(
    id  int auto_increment ***ment 'ID' primary key,
    name varchar(50) not null ***ment '姓名',
    age  int ***ment '年龄',
    job varchar(20) ***ment '职位',
    salary int ***ment '薪资',
    entrydate date ***ment '入职时间',
    managerid int ***ment '直属领导ID',
    dept_id int ***ment '部门ID'
)***ment '员工表';

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),

            (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
            (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
            (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),

            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),

            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),

            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

查询两张表的数据     直接查询的结果:  可以看到有大量的无效的数据

要想从 emp 表和 dept 表中查询数据 ,要先对无效的笛卡尔积进行消除 :

-- 多表查询 -- 笛卡尔积
select * from emp , dept where emp.dept_id = dept.id;

清除后 : 

  

 3.多表查询的分类

 4.内连接

演示 :  

-- 内连接演示
-- 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 ... (inner 可以省略)
-- 表结构: 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;

结果 : 

 5.外连接

演示 : 

-- 外连接演示
-- 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;

结果 : 

6.自连接

演示 :

自连接可以将自身看成两张表 a 和 b ,a 表 和 b 表 的连接条件是 

    a表的 managerid = b表的 id

演示 : 

-- 自连接
-- 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 outer join emp b on a.managerid = b.id;

结果 : 

 7.联合查询

演示 : 

-- union all , union
-- 1. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

-- 直接将查询的两个结果合并
select * from emp where salary < 5000
union all
select * from emp where age > 50;

-- 去除重复数据 可以删去关键字 all
select * from emp where salary < 5000
union
select * from emp where age > 50;

注意 : 联合查询的多张表的列数必须保持一致 ,字段类型也要保持一致.

结果 :

 8.子查询  

1.标量子查询

演示 : 

-- -------------------------------------- 子查询 ------------------------

-- 标量子查询
-- 1. 查询 "销售部" 的所有员工信息
-- 第一步. 查询 "销售部" 部门ID
select id from dept where name = '销售部';

-- 第二步. 查询部门 id 为 4 查询员工信息
select * from emp where dept_id = 4;

-- 第三步. 合并
select * from emp where dept_id = (select id from dept where name = '销售部');


-- 2. 查询在 "方东白" 入职之后的员工信息
-- 第一步. 查询 方东白 的入职日期
select entrydate from emp where name= '方东白';

-- 第二步. 查询他之后的员工信息
select * from emp where entrydate > '2009-02-12';

-- 第三步. 合并
select * from emp where entrydate > (select entrydate from emp where name= '方东白');

结果 :

2.列子查询

演示 : 

-- 列子查询
-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息
-- a. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

-- b. 根据部门ID, 查询员工信息 ( in: 在指定范围内查找 )
select * from emp where dept_id in (2,4);

-- c. 合并
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 dept where name = '财务部');

-- b. 比 财务部 所有人工资都高的员工信息 (高于财务部最高工资的人, all : 查询返回的列表都需要满足条件)
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));


-- 3. 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
select id from dept where name = '研发部';

select salary from emp where dept_id = (select id from dept where name = '研发部');

-- b. 比研发部其中任意一人工资高的员工信息 ( any/some : 查询满足其中的任意一个条件)
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

结果 :

3.行子查询

演示 : 

-- 行子查询
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
-- a. 查询 "张无忌" 的薪资及直属领导
select salary,managerid from emp where name = '张无忌';

-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

结果 : 

4.表子查询

演示 :

-- 表子查询
-- 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;

结果 :

 9.多表查询案例练习

演示 :

-- ---------------------------------------> 多表查询案例 <----------------------------------
create table salgrade(
    grade int,
    losal int,
    hisal int
) ***ment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

-- 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 join dept d on e.dept_id = d.id where age < 30;


-- 3. 查询 拥有 员工的部门ID、部门名称 (人事部没有员工)
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id  distinct关键字去重
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 d.id = e.dept_id where age > 40;


-- 5. 查询所有员工的工资等级
-- 表: emp , salgrade
-- 连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.* , s.grade , s.losal , s.hisal  from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- between...and 写法
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. 查询指定部门平均薪资  1
select avg(e1.salary) from emp e1 where dept_id = 1;
select avg(e1.salary) from emp e1 where dept_id = 2;

-- b. 查询低于本部门平均工资的员工信息
select *, (select avg(e1.salary) from emp e1 where dept_id = e2.dept_id) '平均'
from emp e2
where salary < (select avg(e1.salary) from emp e1 where 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.事务简介

 2.事务操作 

操作方法 1 :

操作方法 2 :

 提交事务执行使用 ***mit ,如果执行过程中发生错误, 则 返回初始值 rollback.

 演示 :

-- ---------------------------- 事务操作 ----------------------------
-- 数据准备
create table a***ount(
    id int auto_increment primary key ***ment '主键ID',
    name varchar(10) ***ment '姓名',
    money int ***ment '余额'
) ***ment '账户表';
insert into a***ount(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);

-- 恢复数据
update a***ount set money = 2000 where name = '张三' or name = '李四';



select @@auto***mit; -- 查询当前事务执行方式, 查询结果 0 为手动提交 , 1 为自动提交

set @@auto***mit = 0; -- 设置为手动提交

-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from a***ount where name = '张三';

-- 2. 将张三账户余额-1000
update a***ount set money = money - 1000 where name = '张三';

程序执行报错 ... -- 这里程序执行错误,前面两条语句执行,下面语句不会执行

-- 3. 将李四账户余额+1000
update a***ount set money = money + 1000 where name = '李四';

-- 事务执行成功,没有错误则 提交事务 手动提交
***mit;

-- 回滚事务 如果执行过程出错,则执行回滚操作,返回初始值
rollback ;



-- 方式二
-- 转账操作 (张三给李四转账1000)
start transaction ;  -- 开启事务

-- 1. 查询张三账户余额
select * from a***ount where name = '张三';

-- 2. 将张三账户余额-1000
update a***ount set money = money - 1000 where name = '张三';

程序执行报错 ... -- 这里程序执行错误,前面两条语句执行,下面语句不会执行

-- 3. 将李四账户余额+1000
update a***ount set money = money + 1000 where name = '李四';


-- 事务执行成功,没有错误则 提交事务 手动提交
***mit;

-- 回滚事务 如果执行过程出错,则执行回滚操作,返回初始值
rollback;

 3.事务四大特性

•  原子性 : 要么事务执行成功要么就执行失败,进行回滚操作再进行下一次提交事务.
•  一致性 : 比如例子中的转账操作,事务提交无论成功或失败,张三和李四账户的余额加起来是一个恒定的值,不会发生增加或减少.
• 比如两个事务 A 和 B ,两个事务在操作过程中彼此之间不会影响之间的执行,两个事务是在独立的环境下运行.
• 事务不管是操作成功提交了还是操作失败回滚了,它对数据库当中的数据改变是永久的,数据库当中的数据最终是存储在磁盘当中,所有数据就会永久的保留下来.

4.并发事务引发的问题

并发事务引起的问题 :多个并发执行事务在操作同一个数据库/表所引起的问题.

• 脏读(没有执行完的事务 A 存储到磁盘后被事务 B 读取,则为脏读 ) : MySQL的隔离级别默认可以重复读取,也就是说未提交的数据根本读不到,这里只是告诉我们会有这种情况发生,而数据库隔离级别不会不会产生这种情况.
•  不可重复读 : 事务 A 第一次执行查询一个 id=1 的语句后,再令 事务 B 更新了刚刚查询的语句 id=1 并且提交 ,如果 事务 A 再执行相同的查询 id=1 我们发现,此时查询出来的数据和第一次查询出来的数据不一样,这个现象就称为不可重复读.
• 幻读(解决了不可重复读后引发出来的) : 

 5.事务隔离级别,解决事务并发问题

read un***mintted : (会出现脏读) 

如果相反设置为 set session transaction isolation level read ***mitted;后再次执行上面的操作,如果不进行 ***mit; 提交则数据不会发生改变.

read ***mintted : (会出现不可重复提交问题)

如果设置为 set session transaction isolation level repeatable read; 后再次 ***mit 提交,不可重复读问题就会被解决.

 repeatable read : (会出现幻读问题)

 要想解决幻读问题,则要设置事务的隔离级别为 set session transaction isolation level serializable;

 serializable (可以规避所有的并发事务问题,但是它的性能是最差的) : A 事务 ***mit 提交后, B 事务才能进行操作. 

注意 : 事务的隔离级别越高, 数据越安全, 但是性能越低 .

总结

多表查询 :

事务 :

码文不易,三联支持一下呗

如有不足,还望指出.