MySQL-基础篇 ( 多表查询：相关 + 内连接 + 外连接 + 自链接 + 联合查询 + 子查询 )

MySQL-基础篇 ( 多表查询 )

目录

多表查询

分类

  • 连接查询
    • 内连接：相当于查询 A、B 交集部分数据
    • 外连接：
      • 左外连接：查询左表所有数据，以及两张表交集部分数据
      • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名
  • 子查询

多表关系

概述

  • 项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
    • 一对多 ( 多对一 )
    • 多对多
    • 一对一

一对多 ( 多对一 )

  • 一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键

多对多

  • 一个学生可以选修多个课程，一个课程也可以供多个学生选择
  • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

  • 用户与用户详情的关系
  • 多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的 ( UNIQUE 保证一对一关系 )

多表查询概述

  • 从多张表中查询数据
  • 由于单纯的直接多表查询：select * from 表1, 表2; 会查出 ( 表一数据条数 * 表二数据条数 ) 条 —— 笛卡尔积现象

笛卡尔积

  • 笛卡尔乘积是指在数学中，两个集合 A 集合和 B 集合的所有组合情况 ( 多表查询时，需要消除无效的笛卡尔积 )
  • 消除，即在语句中加上两表关联字段关系：select * from 表1, 表2 where 表1.xx = 表2.yy;

内连接

  • 查询的是两张表交集的部分
  • 分为隐式内连接和显式内连接两种
    • 显示内连接在三个或三个以上多表时效率要高于隐式
    • 隐式语法简单好理解

隐式内连接

  • SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

  • 练习：

    • 查询每一个员工的姓名及关联的部门的名称

    • 连接条件：emp.dept_id = dept.id

      select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
-- 常简写别名，写了别名之后，其他地方也只能用别名了 (from 是最先执行的部分)
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

显式内连接

  • SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

  • 练习：

    • 查询每一个员工的姓名及关联的部门的名称

    • 连接条件：emp.dept_id = dept.id

      select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- inner 关键字可以省略
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
      • 如果还有条件没补充，就在最后加上 where + 条件

外连接

  • 分为左外连接和右外连接两个部分
    • 其中连接后，在另一张表对应数据为空的话就都为 null
    • 左外连接变右外连接：调换 from 后的表顺序，换 left 为 right，查询结果不变
    • 所有通常都使用左外连接

左外连接

  • 相当于查询表 1 ( 左表 ) 的所有数据 ( 包含两表交集部分的数据 )

    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

  • 练习：

    • 查询 emp 表的所有数据和对应的部门名称

      select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
-- outer 关键字可以省略
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

右外连接

  • 相当于查询表 2 ( 右表 ) 的所有数据 ( 包含两表交集部分的数据 )

    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

  • 练习：

    • 查询 dept 表的所有数据和对应的员工信息

      select e.*, d.* from emp e right outer join dept d on e.dept_id = d.id;
-- outer 关键字可以省略
select e.*, d.* from emp e right join dept d on e.dept_id = d.id;

自连接

  • 自连接查询，可以是内连接查询，也可以是外连接查询

​ SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件 ...;

  • 练习：

    • 前提：在一张员工表中，每个员工都有自己的领导 id ( 公司最顶级职位除外 )，各级领导也属于公司员工，所以每一个员工的领导 id 都能在该员工表中找到对应的个人信息
    • 此时需要获取员工及其领导信息就需要这一张表做多表查询
    -- 获取员工与其领导的姓名：
select a.name, b.name from emp a, emp b where a.manager_id = b.id; -- 内连接：两张表交集部分的数据
-- 一张表起两个别名，即把一张表当两张表来看

-- 查询所有员工及其领导的姓名，如果员工没有领导，也需要查出来：
select a.name '员工', b.name '领导' from emp a left join emp b on a.manager_id = b.id; -- 外连接：会完全包含左表或者右表的数据
-- 这样就算是最顶级没有领导的人也会被查询出来了
    • 内连接：条件中的两个都非空且值相等才会查出来
    • 外连接：除了字段值匹配，条件中其中一方 ( 上题就指 a 员工的 id 不为空就会查出来 ) 的字段值为空也可以查出来

联合查询 - union, union all

  • 对于 union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

    • SELECT 字段列表 FROM 表A ...

      UNION [ALL]

      SELECT 字段列表 FROM 表B ...;

    • union 将上下两个 select 查询结果合并了

  • 练习：

    • 对薪资低于 5000 的员工和年龄大于 50 岁的员工全部查询出来

      select * from emp where salary < 5000 -- 结果集A
union all
select * from emp where age > 50; -- 结果集B
-- 最终的为 AvB，并，直接对两个select查询得到的表数据上下拼接起来
-- union all：联合很有可能会产生查询结果的重复(即，工资小于5000时拿到数据，年龄大于50时又取到了)

select * from emp where salary < 5000
union
select * from emp where age > 50;
-- 但 union：联合之后自行去重操作

  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

子查询

  • SQL 语句中，嵌套 SELECT 语句，称为嵌套查询，又称子查询
  • SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
    • 括号内的就是嵌套的，子查询外面的开头的语句 SELECT 也可以是 INSERT / UPDATE / DELETE 中的任何一个
  • 子查询结果不同可分为以下几类
    • 标量子查询 ( 子查询结果为单个值 )
    • 列子查询 ( 子查询结果为一列 )
    • 行子查询 ( 子查询结果为一行 )
    • 表子查询 ( 子查询结果为多行多列 )
  • 根据子查询的位置不同，又可分为
    • WHERE 之后
    • FROM 之后
    • SELECT 之后

标量子查询

  • 子查询返回的结果是单个值 ( 数字、字符串、日期等 )，是最简单的形式

  • 常用操作符：=、<>、>、>=、<、<=

  • 练习：

    • 前提：员工表 emp 只有部门的 dept_id，没有名称，部门信息在 dept 表中，emp 表中有入职时间字段名，格式为 " 年-月-日 "

      -- 查询“销售部”的所有员工信息
-- 1. 先查销售部部门 id
select id from dept where name = '销售部';
-- 2. 再根据销售部部门 id，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 查询在“zyz”入职之后的员工信息
-- 1. 查询 zyz 的入职日期
select entrydate from emp where name = 'zyz';
-- 2. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = 'zyz');

列子查询

  • 子查询结果返回的结果是一列 ( 可以是多行 )

    • 如 ( 也是下方练习的返回结果的形式 )：

      salary
      8000
      10000
      95000

  • 常用的操作符：IN、NOT IN、ANY、SOME、ALL

    操作符 描述
    IN 在指定的集合范围之内，多选一
    NOT IN 不在指定的集合范围内
    ANY 子查询返回列表中，有任意一个满足即可
    SOME 与 ANY 等同，使用 SOME 的地方都可以使用 ANY
    ALL 子查询返回列表的所有值都必须满足

  • 练习：

    • 前提：员工表 emp 只有部门的 dept_id，没有名称，部门信息在 dept 表中

      -- 查询比财务部所有人工资都高的员工信息
-- 1. 查询财务部的部门id
select id from dept where name = '财务部';
-- 2. 查询该部门所有人的工资
select salary from emp where dept_id = (select id from dept where name = '财务部');
-- 3. 找出该部门工资最高的人的信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));
-- all 部分可以看为：salary > 8000 and salary > 10000 and salary >9500
-- 注意：max等聚合函数不能用在where的后面，是在select后的，即在第二步可以考虑使用，但为了举出all的例子，这里就没用

-- 查询比研发部其中任意一人工资高的员工信息
-- 1. 查询研发部所有员工的工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- 2. 比 ... 工资高的员工信息(任意一人高，即比研发部里面最小值要高即可)
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = '研发部'));
-- any、some：满足一个即可

行子查询

  • 子查询返回的结果是一行 ( 可以是多列 )

    • 如 ( 也是下方练习的返回结果的形式 )：

      salary managerid
      10000 1

  • 常用操作符：=、<>、IN、NOT IN

  • 练习：

    • 查询与 " zyz " 薪资和 " zyz " 直属领导都相同的员工信息

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

表子查询

  • 子查询返回的结果是多行多列

    • 如 ( 也是下方练习的第二题的返回结果的形式 )：

      id name entrydate dept_id
      2 zyz 2020-01-01 1
      5 javawa 2021-01-01 2

  • 常用操作符：IN

  • 练习：

    -- 查询与"zyz"、"javawa"的职位和薪资相同的员工信息
-- 1. 查询"zyz"、"javawa"的职位和薪资
select job, salary from emp where name = 'zyz' or name = 'javawa';
-- 2. 查询与 ... 相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name = 'zyz' or name = 'javawa');
-- 一行的话是 =，而多行的话就是用 in 了

-- 查询入职日期是“2001-01-01”之后的员工信息及其部门信息
-- 1. 入职日期是"2001-01-01"之后的员工信息
select * from emp where entrydate > '2001-01-01';
-- 2. 查询这部分员工对应的部门信息
select e.*, d.* from (select * from emp where entrydate > '2001-01-01') e left join dept d on e.dept_id = d.id;
-- 把第一步查询得到的表(指定日期后的)与另一张表进行联查

多表查询案例

  • 语句太长可以 ctrl + alt + L 格式化语句

image-20230724223526906

image-20230725222459441

  • 题目与解答：

    -- 查询员工的姓名、年龄、职位、部门信息
select e.name, e.age, e.job, d.* from emp e, dept d where e.dept_id = d.id;

-- 查询年龄小于 30 岁的员工姓名、年龄、职位、部门信息(用显示内连接方法)
select e.name, e.age, e.job, d.* from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
-- 也可以用隐式 where 来连接(非 inner join on)

-- 查询拥有员工的部门 ID、部门名称
select distinct e.dept_id, d.name from emp e, dept d where e.dept_id = d.id;
-- 勿忘去重

-- 查询所有年龄大于 40 岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来(即需要外连接处理)
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
-- 左外连 ——> 缩写为左连

-- 查询所有员工的工资等级
# select e.name, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
select e.name, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal;

-- 查询 " 研发部 " 所有员工的信息及工资等级
select e.*, s.grade from emp e, salgrade s where e.dept_id = (select id from dept d where d.name = '研发部') and (e.salary between s.losal and s.hisal);

-- 查询 " 研发部 " 员工的平均工资
select avg(e.salary) from emp e where e.dept_id = (select id from dept d where d.name = '研发部');

-- 查询工资比 " 灭绝 " 高的员工信息
select * from emp e where e.salary > (select salary from emp where name = '灭绝');

-- 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

-- 查询低于本部门平均工资的员工信息
# select avg(salary), dept_id from emp e group by dept_id
# select e.* from emp e, (select avg(salary) avg, dept_id from emp e group by dept_id)avgtab where e.salary < avgtab.avg and e.dept_id = avgtab.dept_id;
select e.* from emp e inner join (select avg(salary) avg, dept_id from emp e group by dept_id)avgtab on e.salary < avgtab.avg and e.dept_id = avgtab.dept_id;
# select * from emp e1 where e1.salary < (select avg(e2.salary) from emp e2 where e1.dept_id = e2.dept_id); -- 直来直去的法子

-- 查询所有的部门信息，并统计部门的员工人数
# select count(*) from emp where dept_id = '1'
select d.*, (select count(*) from emp e where e.dept_id = d.id) from dept d;

-- 查询所有学生的选课情况，展示出学生名称，学号，课程名称
select s.name, s.no, c.name from student s left join student_course sc on s.id=sc.studentid left join course c on sc.courseid=c.id

  • 表创建插入语句：

    create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

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

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);


create table salgrade
(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

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);

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


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


create table student_course
(
    id        int auto_increment comment '主键' primary key,
    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, 4);
posted @ 2023-08-17 19:25  朱呀朱~  阅读(125)  评论(0)  编辑  收藏  举报