mysql-基础-多表查询

1. 多表关系

1. 概述：
   顶目开发中，在进行数据库表结构设计时，会根据业务需求及业务摸块之间的关系，分析并设表结构，由于业务之间相互关联，所以各个表结构之也存在着各种联系，基本上分为三种：

• 一对多〈多对一）
• 多对多
• 一对一

2. 一对多：

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

1. 多对多：

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

2. 一对一：

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

2. 多表查询：

1. 概述：
   a. 指从多张表中查询数据
   b. 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
   

2. 分类：
   a. 连接查询：

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

3. 连接查询-内连接：
   a. 语法：

   • 隐式内连接
     SELECT 字段列表 FROM 表1，表2 WHERE 条件… ;

   • 显式内连接
     SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件… ;

     内连接查询的是两张表交集的部分

b. 演示：

# 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 as e, dept as d where e.dept_id = d.id ;

    # 2. 查询每一个员工的姓名，及关联的部门的名称（显示外连接）
      # 表结构：emp, dept
      # 连接条件：emp.dept_id = dept.id

      - select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id ;
      - select e.name, d.name from emp as e inner join dept as d on e.dept_id = d.id ;

4. 连接查询-外连接：

• 语法：

# 1. 左外连接
  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件… ;
  # 相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据

# 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 dept d on e.dept_id = d.id ;

# 2. 查询emp表的所有数据，及对应部门信息（右外连接）
  # 表结构：emp, dept
  # 连接条件：emp.dept_id = dept.id
  
  - select d.*, e.* from emp e right __outer__ join dept d on e.dept_id = d.id ;
  - select d.*, e.* from emp e right join dept d on e.dept_id = d.id ;

5. 连接查询-自连接：

• 语法：

  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

  select a.name '员工' , b.name '领导' , from emp a left join emp b on a.managerid = b.id ;

6. 连接查询-联合查询-union/union all：

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

• b. 语法：

SELECT 字段列表 FROM 表A … 
  UNION [ALL]
SELECT 字段列表 FROM 表B … ; 

注意：

1. union ：会将查询的全部数据合并，并去除重复值
2. union all： 会将查询的全部数据合并，不去重
3. 对于联合查询的多张表的字段列表数必须保持一致，字段类型也需保持一致

• c. 演示：

# 1. 将薪资低于5000的员工，及年龄大于50 的员工全部查询出来
  # 表结构：emp
  select * from emp where salary < 5000
    union
  select * from emp where age > 50 ; 

7. 连接查询-子查询介绍：

• a. 概念：
  SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

• b. 语法：
  1. SELECT * FROM 表1 WHERE column1=（SELECT column1 FROM 表2);

  子查洵外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

• c. 分类：

  根据子查询结果不同，分为：

  • 标量子查询〈子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

  . 根据子查询位置，分为：
  WHERE之后、FROM之后、SELECT之后。

• d. 标量子查询：

  1. 概述：
     子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
     常用的操作符：= 、<>、>、>=、<、<=

  2. 演示：

1. 查询“销售部”的所有员工信息
select * from dept where dept.id = ( select * from dept where name = '销售部' ) ;

2. 查询在“令狐冲”之后入职的员工信息
select * from dept where entrydate > (select entrydate from dept where name = '令狐冲') ;

• e. 列子查询：

  1. 概述：
     子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
     常用的操作符：IN、NOT IN、ANY、SOME、ALL

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

  2. 演示：

# 1. 查询销售部和市场部的所有员工信息
  #. 表结构：emp，dept
    # 查询“销售部”和“市场部”的部门ID
    select id from dept where name = '销售部' or name = '市场部' ;
    # 根据部门ID，查询员工信息
    select * from dept where dept_id in (select id from dept where name = '销售部' or name = '市场部') ;
          

# 2. 查询比财务部所有人工资都高的员工信息
    #. 表结构：emp，dept
    
    1. #查询所有财务人员工资
      select salary from emp where dept_id = (select id from dept where nam = '财务部') ;
      
    2. # 查询比财务部所有人工资都高的员工信息
      select * from where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部')) ;
      
# 3. 查询比研发部其中任意一人工资高的员工信息
    #. 表结构：emp，dept
    
    1. #查询研发部所有人工资
      select salary from emp where dept_id = (select id from dept where name = '研发部') ;
      
    2. #比研发部任意一人工资高的员工信息
      select * from emp wherer salary > some (select salary from emp where dept_id = (select id from dept where name = '研发部') ) ;

• f. 行子查询：
  概述：
  子查询返回的结果是一行（可以是多列〕，这种子查询称为行子查询。
  常用的操作符：=、<>、IN、NOT、IN

  演示：

# 查询与“令狐冲”的薪资及直属领导相同的员工信息
# 查询“令狐冲”的薪资及直属领导
    select salary, managerid from emp where name = '令狐冲' ;
    
# 查询与“令狐冲”的薪资及直属领导相同的员工信息
    select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '令狐冲' ) ;

g. 表子查询：

1. 概述：子查询返回的结果是多行多列，这种子查询称为表子查询。
2. 常见操作符：IN
3. 演示：

#. 查询与“张无忌”，“岳不群”的职位和薪资相同的员工信息
    #. 表结构：emp
    
    1. #查询“张无忌”，“岳不群”的职位与薪资
      select job, salary from emp where name = '张无忌' or name = '岳不群' ;
    2. #查询与“张无忌”，“岳不群”的职位和薪资相同的员工信息
      select * from emp (job, salary) = in (select job, salary from emp where name = '张无忌' or name = '岳不群');


#. 查询入职时间是“2006-01-01”之后的员工信息，及其部门信息
    #. 表结构：emp，dept

    1. #查询入职时间为“2006-01-01"之后的员工信息
      select * from emp where entrydate > '2006-01-01';
    2. #查询这部分员工，所对应的部门信息
      select * from (select * from emp where entrydate > '2006-01-01') as e left join dept as d on e.dept_id = d.id ;

3. 多表查询综合案例：

创建表：

# 创建表
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);

需求：

1. 查询员工的姓名、年龄、职位、部门信息。(隐式内连接)
   –表结构：emp，dept
   –连接条件： emp.dept_id = dept.id

select e.name, e.age, e.job, b.name from emp e, dept d where e.dept_id = d.id;

1. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显式内连接)
   –表结构：emp，dept

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

select e.nam, e.age, e.job, d.name from enp 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; #distinct---去重

4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部，也需要展示出来。
   –表结构：emp，dept

   –连接条件：emp, 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 >= salarly.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 ;
- 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 salarly.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. 查询工资比”灭绝”高的员工信息。(–表结构：emp)

• a. #查询‘灭绝’的薪资

select salaty from emp where nam = '灭绝';

• b. #查询薪资比她高的员工信息

select * from emp where salary > (select salaty from emp where nam = '灭绝');

9. 查询比平均薪资高的员工信息。(–表结构：emp)

• a. #查询员工的平均薪资

select avg(salary) from emp;

• b. #查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

10. 查询低于本部门平均工资的员工信息。
    –表结构：emp

    –部门ID：1

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

• a. #查询指定部门平均薪资

select avg(e1.salary) from emp e1 where e1.dept_id = 1;

• b. #查询低于本部门平均薪资的员工信息

select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) ;

11. 查询所有的部门信息，并统计部门的员工人数。

a. select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) as '人数' from dept as d ;

12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称(course)
    a. –表结构：student, course, student_course
    b. –连接条件：student.id = student_course.student.id, course.id = student_course.id

select s.name, s.number, c.name, from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id ;