基础篇04——多表查询

多表关系

一对多

多对多

多对多是通过中间表实现的

-- 创建学生表
create table student
(
    id   int auto_increment primary key comment 'ID',
    name varchar(10) comment '姓名',
    no   varchar(3) comment '学号'
) comment '学生表';
 
insert into student
values (null, '黛绮丝', '001'),
       (null, '谢逊', '002'),
       (null, '小明', '003'),
       (null, '小红', '004');
 
-- 创建课程表
create table course
(
    id   int auto_increment primary key comment 'ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
 
insert into course
values (null, '语文'),
       (null, '数学'),
       (null, '英语');
 
-- 创建中间表，维护学生表和课程表之间的关系
create table student_course
(
    id        int auto_increment primary key comment 'ID',
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';
 
insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 3);

一对一 

-- 创建用户表
create table tb_user
(
    id     int auto_increment primary key comment 'ID',
    name   varchar(10) comment '姓名',
    age    tinyint unsigned comment '年龄',
    gender char(1) comment '性别',
    phone  char(11) comment '手机号'
) comment '用户基本信息表';
 
-- 创建用户教育信息表
create table tb_user_edu
(
    id            int auto_increment primary key comment 'ID',
    degree        varchar(20) comment '学历',
    major         varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool  varchar(50) comment '中学',
    university    varchar(50) comment '大学',
    userid        int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用户教育信息表';
 
insert into tb_user(id, name, age, gender, phone)
values (null, '小明', 16, '1', '10000000001'),
       (null, '小花', 13, '2', '10000000002'),
       (null, '小华', 15, '1', '10000000003'),
       (null, '小红', 14, '2', '10000000004');
 
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null, '本科', '舞蹈', 'XXX小学', 'XXX中学', 'XXX大学', 1),
       (null, '大专', '会计', 'YYY小学', 'YYY中学', 'YYY大学', 2),
       (null, '硕士', '英语', 'AAA小学', 'AAA中学', 'AAA大学', 3),
       (null, '博士', '临床医学', 'BBB小学', 'BBB中学', 'BBB大学', 4);

多表查询概述

多表查询指的是从多张表中查询数据

用以下例子举例

笛卡尔积

当直接查询两张表时（即执行命令：select * from emp,  dept;），查询的结果就是笛卡尔积，查询结果数为两张表的数据量相乘，我们需要消除无效的笛卡尔积，如下

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

 多表查询分类

内连接

-- 隐式内连接实现：查询每一个员工姓名及其所属部门名称
select emp.name, dept.name
from emp,
     dept
where dept_id = dept.id;
 
-- 显式内连接实现：查询每一个员工姓名及其所属部门名称
-- 可以给表起别名来简化代码，但是起别名之后不能再用表原来的名称
-- inner关键字可以省略
select e.name, d.name
from emp e
         inner join dept d on e.dept_id = d.id;

外连接

左外连接和右外连接可以相互转换，即左外连接可以用右外连接替代，右外连接也可以用左外连接替代，只需要更改关键字left、right和两个表的位置即可，这里就不展示了。 

-- 查询emp表的所有数据，和对应的部门信息（左外连接）
-- outer 关键字可以省略
-- from 后跟着的表为左表，join后跟着的表为右表
-- 两个表的顺序不一样查询结果也不一样
-- 左外连接表示展示左表的所有数据，以及左表中每一条数据对应的右表数据，如果右表没有数据则显示为空
-- 右外连接则反过来，表示展示右表的所有数据，以及右表中每一条数据对应的左表数据，如果左表没有数据则显示为空
-- 自己运行一下代码就可以明白
select e.*, d.name  -- e.* 表示查询emp表中的所有数据
from emp e
         left outer join dept d
                         on e.dept_id = d.id;
 
-- 查询所有的部门信息，和对应的emp表数据（右外连接（
select d.*, e.*
from emp e
         right outer join dept d on d.id = e.dept_id;

自连接

自连接的外连接查询可以是左外也可以是右外。

-- 查询所有员工的名字及其所属领导的名字
-- 显式内连接实现
select e1.name as '员工姓名', e2.name as '领导姓名'
from emp e1
         join emp e2 on e1.managerid = e2.id;
 
-- 隐式内连接实现
select e1.name '员工姓名', e2.name '领导姓名'
from emp e1,
     emp e2
where e1.managerid = e2.id;
 
-- 查询所有员工的名字及其所属领导的名字，如果员工没有领导也要查询出来
-- 这里用外连接实现，用的是左外连接
select e1.name '员工姓名', e2.name '领导姓名'
from emp e1
         left join emp e2 on e1.managerid = e2.id;

联合查询union

-- 将薪资低于5000的员工和年龄大于40的员工全部查询出来
-- 即员工满足两个条件之一就需要被查询出来
-- 有 all 关键字查询结果不去重，没有 all 则会将查询结果去重
-- 只有 select 和 from 之间的字段列表的列数和类型一致时才可以用联合查询
select *
from emp
where salary < 5000
union all
select *
from emp
where age > 40;

子查询

子查询一般都用小括号括起来，可以放在 select、from、where 这几个位置（具体看后面案例）

标量子查询

-- 查询研发部的所有员工信息
select *
from emp
where dept_id = (select id from dept where dept.name = '研发部');

列子查询

-- 查询销售部和市场部的所有员工信息
select *
from emp
where dept_id in (select dept.id
                  from dept
                  where dept.name in ('销售部', '市场部'));
 
 
 
-- 查询比财务部 所有人 工资都高的员工信息
-- 查询财务部的id：select id from dept where dept.name = '财务部'
-- 先查出财务部员工最高的工资
-- select max(salary) from emp where dept_id = (select id from dept where dept.name = '研发部');
-- 实现方式1：
select *
from emp
where salary > (select max(salary)
                from emp
                where dept_id = (select id
                                 from dept
                                 where dept.name = '财务部'));
 
-- 实现方式2：(all)
select *
from emp
where salary > all (select salary
                    from emp
                    where dept_id = (select id
                                     from dept
                                     where dept.name = '财务部'));
 
 
-- 查询比研发部 任意一人 工资高的员工信息(any/some)
select *
from emp
where salary > any (select salary
                    from emp
                    where dept_id = (select id
                                     from dept
                                     where dept.name = '研发部'));

行子查询

-- 查询与“张无忌”薪资及直属领导相同的员工信息
-- 先查出张无忌的薪资和指数领导id
# select salary, managerid from emp where name='张无忌';
# 假设查出结果为(10000, 1)
# (salary, managerid) = ( select ...) => salary=10000 and managerid=1
select *
from emp
where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

-- 查询与 小明，小红 的职位和薪资相同的员工信息
# 1、查询小明，小红 的职位和薪资
select job, salary
from emp
where name in ('小明' '小红');
select *
from emp
where (job, salary) in (select job, salary
                        from emp
                        where name in ('小明' '小红'));
 
-- 查询入职日期是'2018-01-01'之后的员工信息及其部门信息
# 查询入职日期是'2006-01-01'之后的员工id
select id
from emp
where entrydate > '2006-01-01';
 
# (select * from emp where entrydate > '2006-01-01') 查询结果作为一张临时表
# 给临时表起别名为 e
select e.*, d.name
from (select * from emp where entrydate > '2006-01-01') e
         left join dept d on e.dept_id = d.id;

多表查询案例练习

上面的例子涉及之前所说的emp员工表、dept部门表和薪资等级表，薪资等级表的表结构如下：

三张表的结构如下：

-- 例题1（隐式内连接）
select e.name, e.age, e.job, d.name
from emp e,
     dept d
where e.dept_id = d.id;
 
 
-- 例题2（显式内连接）
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（结果要去重）
select distinct d.id, d.name
from dept d
         inner join emp e on d.id = e.dept_id;
 
 
-- 例题4（左外连接）
select e.*, d.name
from emp e
         left outer join dept d on e.dept_id = d.id
where e.age > 40;
 
 
-- 例题5
# 隐式内连接
select e.name, sg.grade
from salgrade sg,
     emp e
where e.salary between sg.losal and sg.hisal;
 
# 显式内连接
select e.name, sg.grade
from salgrade sg
         inner join emp e on e.salary between sg.losal and sg.hisal;
 
 
-- 例题6
select e.*, sg.grade
from emp e,
     salgrade sg
where e.dept_id = (select id from dept d where d.name = '研发部')
  and e.salary between sg.losal and sg.hisal;
 
 
-- 例题7
# 方式一
select avg(e.salary)
from emp e
where e.dept_id = (select d.id from dept d where d.name = '研发部');
 
# 方式二
select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';
 
 
-- 例题8
select *
from emp
where salary > (select salary from emp where name = '灭绝');
 
 
-- 例题9
select *
from emp
where salary > (select avg(salary) from emp);
 
 
-- 例题10（放在where位置的子查询）
select e1.*
from emp e1
where e1.salary < (select avg(e2.salary)
                   from emp e2
                   where e1.dept_id = e2.dept_id);
 
 
-- 例题11（放在select位置的子查询）
select d.id,
       d.name,
       (select count(e.id)
        from emp e
        where e.dept_id = d.id) '部门员工数量'
from dept d;
 
 
-- 例题12
select s.name, s.no, c.name
from student_course sc,
     student s,
     course c
where sc.courseid = c.id
  and sc.studentid = s.id;