sqlmysql

进阶6：多表连接查询

含义

• 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象
表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件分类

• 按年代分类

sql92标准：MySQL仅仅支持内连接，Oracle等对外连接支持的也不好
sql99标准【推荐】：支持内连接 + 外连接(左外和右外) + 交叉连接

• 按功能分类
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接（MySQL不支持）
  • 交叉连接

为表起别名

• 提高语句的简洁度
• 区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

sql92标准

等值连接

• 多表等值连接的结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名
• 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选 ```sql

  1、等值连接

  案例1：查询女神名和对应的男神名

  SELECT name, boyName FROM boys, beauty WHERE beauty.boyfriend_id = boys.id;

案例2：查询员工名和对应的部门名

SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;

2、为表起别名

查询员工名、工种号、工种名

SELECT e.last_name, e.job_id, j.job_title FROM employees e, jobs j WHERE e.job_id = j.job_id;

3、两个表的顺序是否可以调换

SELECT e.last_name, e.job_id, j.job_title FROM jobs j, employees e WHERE e.job_id = j.job_id;

4、可以加筛选

案例1：查询有奖金的员工名、部门名

SELECT last_name, department_name, commission_pct FROM employees e, departments d WHERE e.department_id = d.department_id AND e.commission_pct IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name, city FROM departments d, locations l WHERE d.location_id = l.location_id AND city LIKE ‘_o%’;

5、可以加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数, city FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY city;

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name, d.manager_id, MIN(salary) FROM departments d, employees e WHERE d.department_id = e.department_id AND commission_pct IS NOT NULL GROUP BY department_name, d.manager_id;

6、可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title, COUNT() FROM employees e, jobs j WHERE e.job_id = j.job_id GROUP BY job_title ORDER BY COUNT() DESC;

7、可以实现三表连接？

案例：查询员工名、部门名和所在的城市

SELECT last_name, department_name, city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id ORDER BY department_name DESC;

**非等值连接**
```sql
#案例：查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e,
     job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
  AND g.`grade_level` = 'A';

自连接

#案例：查询员工名和上级的名称
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e,
     employees m
WHERE e.`manager_id` = m.`employee_id`;

sql99标准

通过join关键字实现连接
语法
**select 字段, ...**
**from 表1**
**[inner|left outer|right outer|cross] join 表2 on 连接条件**
**[inner|left outer|right outer|cross] join 表3 on 连接条件**
**[where 筛选条件]**
**[group by 分组字段]**
**[having 分组后的筛选条件]**
**[order by 排序的字段或表达式]**
分类
内连接★ [inner]
外连接
左外★ left [outer]
右外★ right [outer]
全外 full [outer]
交叉连接 cross

内连接

语法
**select 查询列表**
**from 表1 别名**
**[inner] join 表2 别名 on 连接条件;**
分类
等值
非等值
自连接
特点

1. 可添加排序、分组、筛选
2. inner可以省略
3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4. inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集 ```sql

   1、等值连接

   案例1.查询员工名、部门名

   SELECT last_name, department_name FROM departments d

     JOIN employees e ON e.`department_id` = d.`department_id`;

案例2.查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name, job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE ‘%e%’;

案例3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）

①查询每个城市的部门个数

②在①结果上筛选满足条件的

SELECT city, COUNT() 部门个数 FROM departments d INNER JOIN locations l ON d.location_id = l.location_id GROUP BY city HAVING COUNT() > 3;

案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

①查询每个部门的员工个数

SELECT COUNT(*), department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY department_name

② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT() 个数, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY department_name HAVING COUNT() > 3 ORDER BY COUNT(*) DESC;

案例5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name, department_name, job_title FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY department_name DESC;

2、非等值连接

查询员工的工资级别

SELECT salary, grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

查询工资级别的员工数>20的个数，并且按工资级别降序

SELECT COUNT(), grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal GROUP BY grade_level HAVING COUNT() > 20 ORDER BY grade_level DESC;

3、自连接

查询员工的名字、上级的名字

SELECT e.last_name, m.last_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id;

查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name, m.last_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.last_name LIKE ‘%k%’;

<a name="brV7m"></a>
#### 外连接
用于查询一个表中有，另一个表没有的记录<br />特点
1. 外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值<br />如果从表中没有和它匹配的，则显示null
2. 外连接查询结果=内连接结果+主表中有而从表没有的记录
左外连接，left join左边的是主表<br />右外连接，right join右边的是主表<br />左外和右外交换两个表的顺序，可以实现同样的效果
3. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
```sql
#引入：查询男朋友不在男神表的的女神名
#左外连接
SELECT b.*, bo.*
FROM boys bo
         RIGHT OUTER JOIN beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
#案例1：查询哪个部门没有员工
#左外
SELECT d.*, e.employee_id
FROM departments d
         LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右外
SELECT d.*, e.employee_id
FROM employees e
         RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#全外 MySqL不支持全外连接
SELECT b.*, bo.*
FROM beauty b 
                 FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id;

交叉连接

SELECT b.*, bo.*
FROM beauty b
         CROSS JOIN boys bo;

sql92和 sql99pk对比

• 功能：sql99支持的较多
• 可读性：sql99实现连接条件和筛选条件的分离，可读性较高

进阶7：子查询

含义
出现在其他语句中的select语句，称为子查询或内查询
外部可以是incert update delete select等，当是select语句时，称为主查询或外查询
分类
按子查询出现的位置
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或having后面 ★
标量子查询(单行) ✔️
列子查询(单列多行) ✔️
行子查询 表子查询 (多列)
exists后面（相关子查询）
表子查询
按结果集的行列数不同
标量子查询（结果集一行一列）
列子查询（结果集一列多行）
行子查询（结果集一行多列）
表子查询（结果集为一张表）
关联子查询

where或having后面

特点

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询，一般搭配着单行操作符使用

< >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all

1. 非相关子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

   标量子查询（单行子查询）

   ```sql

   案例1：谁的工资比 Abel 高?

   ①查询Abel的工资

   SELECT salary FROM employees WHERE last_name = ‘Abel’;

   ②查询员工的信息，满足 salary>①结果

   SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name = ‘Abel’ );

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

①查询141号员工的job_id

案例3：返回公司工资最少的员工的last_name, job_id和salary

①查询公司的 最低工资

SELECT MIN(salary) FROM employees;

②查询last_name,job_id和salary，要求salary=①

SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

①查询50号部门的最低工资

SELECT MIN(salary) FROM employees WHERE department_id = 50;

②查询每个部门的最低工资

SELECT MIN(salary), department_id FROM employees GROUP BY department_id;

③ 在②基础上筛选，满足min(salary)>①

SELECT MIN(salary), department_id FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );

非法使用标量子查询，结果不是一行一列，包括多行一列和没有结果❌

SELECT MIN(salary), department_id FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT salary FROM employees WHERE department_id = 250 );

查询平均工资最低的部门信息

select d.* from departments d where department_id = (select department_id from employees group by department_id order by avg(salary) limit 1);

<a name="DQaEl"></a>
#### 列子查询
![image.png](https://cdn.nlark.com/yuque/0/2020/png/1379492/1591689470524-0449c63e-4598-44bb-974f-225c8a760ba1.png#height=135&id=KPqyH&margin=%5Bobject%20Object%5D&name=image.png&originHeight=538&originWidth=1602&originalType=binary&ratio=1&size=116991&status=done&style=shadow&width=401)    IN就是=ANY
```sql
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700);
#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400, 1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的
#员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
  AND job_id <> 'IT_PROG';
#或
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)
  AND job_id <> 'IT_PROG';

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的
#员工的员工号、姓名、job_id 以及salary
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ALL (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
  AND job_id <> 'IT_PROG';
#或
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < (
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)
  AND job_id <> 'IT_PROG';

行或表子查询

#案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id, salary) = (SELECT MIN(employee_id), MAX(salary)
                              FROM employees);

#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#②查询最高工资
SELECT MAX(salary)
FROM employees;
#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(SELECT MIN(employee_id)
                                     FROM employees)
AND salary=(SELECT MAX(salary)
                        FROM employees);

select后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,
       (SELECT COUNT(*)
        FROM employees e
        WHERE e.department_id = d.department_id
       ) 个数
FROM departments d;

#案例2：查询员工号=102的部门名
#结果只能是一列
SELECT (
  SELECT department_name
  FROM departments d
  INNER JOIN employees e ON d.department_id = e.department_id
  WHERE e.employee_id = 102
) 部门名;

from后面

将子查询结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表，筛选
SELECT  ag_dep.*,g.`grade_level`
FROM (SELECT AVG(salary) ag, department_id
            FROM employees
            GROUP BY department_id) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

语法
**EXISTS(完整的查询语句)**
结果
1或0

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例1：查询所有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.department_id IN (SELECT DISTINCT department_id
                                                  FROM employees);
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(SELECT *
                         FROM employees e
                         WHERE d.`department_id`=e.`department_id`);

#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.`id`=b.`boyfriend_id`
);

进阶8：分页查询

当要显示的数据，一页显示不全，需要分页提交sql请求
语法
**select 查询列表**
**from 表**
**join type join 表2 on 连接条件**
**where 筛选条件**
**group by 分组字段**
**having 分组后的筛选**
**order by 排序的字段】**
**limit 【offset,】size;**

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点

1. 起始条目索引从0开始
2. limit子句放在查询语句的最后
3. 公式：select from 表 **limit (page-1)sizePerPage, sizePerPage**

每页显示条目数sizePerPage
要显示的页数 page

#案例1：查询前五条员工信息
SELECT * FROM  employees LIMIT 0, 5;
SELECT * FROM  employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10, 15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10;

进阶9：联合查询

union 联合、合并
将多条查询语句的结果合并成一个结果
语法
**查询语句1**
**union**
**查询语句2**
**union**
**...**
应用场景
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点 ★

1. 要求多条查询语句的查询列数是一致的
2. 要求多条查询语句的查询的每一列的类型和顺序最好一致（不报错）
3. union关键字默认去重，如果使用union all可以包含重复项 ```sql

   引入的案例：查询部门编号>90或邮箱包含a的员工信息

   SELECT * FROM employees WHERE email LIKE ‘%a%’ OR department_id>90;;

SELECT FROM employees WHERE email LIKE ‘%a%’ UNION SELECT FROM employees WHERE department_id>90;

案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex=’男’ UNION ALL SELECT t_id,tname FROM t_ua WHERE tGender=’male’; ```