MySQL基础-多表查询

超、凢脫俗 2024-05-24 01:52 3阅读 0赞

> 本文介绍MySQL的多表查询

#### 文章目录 ####

*  多表关系
 *   *  一对多
     *  多对多
     *  一对一
 *  查询
 *   *  内连接
     *  外连接
     *  自连接
     *  联合查询
     *  子查询
     *   *  标量子查询
         *  列子查询
         *  行子查询
         *  表子查询
     *  多表查询案例

## 多表关系 ##

*  一对多（多对一）
 *  多对多
 *  一对一

### 一对多 ###

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

### 多对多 ###

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

### 一对一 ###

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

## 查询 ##

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

### 内连接 ###

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

隐式内连接：  
`SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;`

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

显式性能比隐式高

语法：

#隐式内连接
    SELECT 字段列表 FROM 表列表 WHERE 条件...;
    
    #显式内连接
    SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

实例：

#查询每一个员工的姓名，以及关联的部门名称（隐式）
    SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;
    
    #查询每一个员工的姓名，以及关联的部门名称（显示）
    SELECT e.name,d.name FROM emp e INNER 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 条件 ...;`

实例：

#查询emp表的所有数据，和对应的部门信息(左外连接)
    SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
    
    #查询dept表的所有数据，和对应的员工信息(右外连接)
    SELECT d.*,e.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

### 自连接 ###

当前表与自身的连接查询，自连接必须使用表别名

语法：  
`SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;`

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

实例：

#查询员工及其直属领导的名字
    SELECT e1.name 员工,e2.name 领导 FROM emp e1 JOIN emp e2 ON e1.managerid = e2.id;
    
    #查询所有员工emp及其领导的名字emp,如果没有领导也要查询出来
    SELECT e1.*,e2.name 领导 FROM emp e1 LEFT JOIN emp e2 ON e1.managerid = e2.id;

### 联合查询 ###

把多次查询的结果合并，形成一个新的查询集  
语法：

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

注意事项：

*  UNION ALL 会有重复结果，UNION 不会
 *  联合查询比使用or效率高，不会使索引失效

实例：

#将薪资低于5000的员工，和年龄大于50岁的员工都查询出来。
    SELECT * FROM emp WHERE salary < 5000 UNION SELECT * FROM emp WHERE age > 50 ORDER BY id ASC;

>  *  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
>  *  UNION ALL会直接将两个结果集合并，而UNION会进行去重

### 子查询 ###

SQL语句中嵌套SELECT语句，称谓**嵌套查询，又称子查询**。  
`SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);`  
子查询外部的语句可以是 `INSERT / UPDATE / DELETE / SELECT` 的任何一个

根据子查询结果可以分为：

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

根据子查询位置可分为：

*  WHERE 之后
 *  FROM 之后
 *  SELECT 之后

#### 标量子查询 ####

子查询返回的结果是单个值（数字、字符串、日期等）。  
常用操作符：`- < > > >= < <=`

实例：

#查询销售部的所有员工信息
    SELECT * FROM emp  WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
    
    #查询方东白之后入职的员工信息
    SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = '方东白');
    
    #查询xxx入职之后的员工信息
    select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

#### 列子查询 ####

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用操作符：`IN、NOT IN、ANT、SOME、ALL`  
![在这里插入图片描述][35b3dbbd597646e986712f3c971bcb79.jpeg_pic_center]  
实例：

-- 查询销售部和市场部的所有员工信息
    select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');
    
    -- 查询比财务部所有人工资都高的员工信息
    select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
    
    -- 查询比研发部任意一人工资高的员工信息
    select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

#### 行子查询 ####

返回的结果是一行（可以是多列）。  
常用操作符：`=, <, >, IN, NOT IN`

例子：

#查询与张无忌的薪资及直属领导都相同的员工
    SELECT * FROM emp WHERE salary = (SELECT salary FROM emp WHERE name = '张无忌') AND managerid = (SELECT managerid FROM emp WHERE name = '张无忌');
    
    #或
    SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name = '张无忌');

#### 表子查询 ####

返回的结果是多行多列  
常用操作符：`IN`

例子：

#查询与鹿杖客、宋远桥的职位和薪资相同的员工信息
    
    SELECT * FROM emp WHERE job IN (SELECT job FROM emp WHERE name = '鹿杖客' OR name = '宋远桥') AND salary IN (SELECT salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥');
    
    #查询入职日期是2006-01-01之后的员工信息，及部门信息
    SELECT e.*,d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.entrydate > '2006-01-01';

### 多表查询案例 ###

#查询员工的姓名、年龄、职位、部门信息
    SELECT e.name,e.age,e.job,d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
    
    #查询年龄小于30岁的员工姓名、年龄、职位、部门信息
    SELECT e.name,e.age,e.job,d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE age < 30;
    
    #查询拥有员工的部门ID、部门名称
    SELECT d.* FROM dept d WHERE d.id IN (SELECT e.dept_id FROM emp e);
    
    #查询所有年龄大于40的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
    SELECT e.name,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 >s.losal AND e.salary <s.hisal AND e.dept_id = 1;
    
    #查询研发部员工的平均工资
    SELECT AVG(salary) FROM emp WHERE dept_id = 1;
    
    #查询工资比灭绝高的员工信息
    SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '灭绝');
    
    #查询比平均工资高的员工信息
    SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
    
    #查询比本部门平均工资低的员工信息
    SELECT * FROM emp e2 WHERE e2.salary < (SELECT AVG(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id);
    
    #查询所有的部门信息，并统计部门的员工人数
    SELECT d.id,d.name,(SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) 人数 FROM dept d;

[35b3dbbd597646e986712f3c971bcb79.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/4d8d5a8323e34805970114c4b80a0de1.jpeg