MySQL多表查询

概览
连接查询
子查询
分页查询
分页请求语法

概览

左外连接	内连接	右外连接	全连接*

SELECT FROM A LEFT JOIN B ON A.key=B.key	SELECT FROM A INNER JOIN B ON A.key=B.key	SELECT FROM A RIGHT JOIN B ON A.key=B.key	SELECT FROM A FULL JOIN B ON A.key=B.key
左外连接主表有从表没有	右外连接主表有从表没有	全连接没有内连接*

| | SELECT
FROM A
LEFT JOIN B
ON A.key=B.key
WHERE B.key is null; | SELECT
FROM A
RIGHT JOIN B
ON A.key=B.key
WHERE A.key is null; | SELECT
FROM A
FULL JOIN B
ON A.key=B.key
WHERE A.key is null
OR B.key is null; |

“全连接”、“全连接没有内连接”在语句MySQL中不适用，改为分别左右查询之后进行union操作

连接查询

笛卡尔积

select 字段1，字段2 from 表1，表2,...;  #笛卡尔乘积现象，完全连接得到 n*m 行结果
select name, boyname from beauty,boys;  # 笛卡尔积现象：12 * 4
select name, boyName from beauty,boys
where beauty.boyfriend_id = boys.id;  # 正确结果：5 * 1

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
- 发生原因：没有有效的连接条件；
- 如何解决：添加有效的连接条件；

解决办法：连接查询，分类：

按功能分类：
- 内连接（包括：等值连接、非等值连接、自连接）
- 外连接（包括：左外连接、右外连接、全外连接）
- 交叉连接
按年代分类：
- sql92：仅仅支持内连接，也支持一部分外连接（oracle、sqlserver支持，mysql不支持）
- sql99【推荐使用】：支持内连接、外连接（mysql不支持全外连接）、交叉连接

以下以SQL99实现

内连接

SELECT * FROM t_emp a INNER JOIN t_dept b ON  a.deptId = b.id;

# 隐式内连接：使用where条件消除无用数据
SELECT * FROM t_emp a , t_dept b WHERE  a.deptId = b.id;

左外连接

右外连接

左外连接取左表的独有部分（即，查询没有门派的人）

右外连接取右表的独有部分（即，查询没有人的门派）

注意：判断字段是否为NULL时，不能使用’=’
因为= NULL的结果不会报错，但是结果永远为false。所以必须使用IS NULL来进行判空

全外连接

MySQL不支持全外连接，要查询两个表的全集，需要合并两个查询结果，所以要使用 UNION 关键字

查询两表独有

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

子查询不同情况

子查询的结果是单行单列的
- 查询可以作为条件，使用运算符去判断。运算符： > >= < <= =
```
#查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
```

子查询的结果是多行单列的

子查询可以作为条件，使用运算符in来判断

#查询'财务部'和'市场部'所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的
- 子查询可以作为一张虚拟表参与查询
```
#查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
```
  | 操作符 | 含义 | 操作符 | 含义 | | —- | —- | —- | —- | | = | Equal to | IN/NOT IN | 等于列表中的任意一个，相当于：=ANY | <>ANY | | > | Greater than | ANY|SOME | 和子查询返回的某（任一）一个值比较 | | >= | Greater than or equal to | ALL | 和子查询返回的所有（任何）值比较 | | < | Less than |
  |
  | | <= | Less than or equal to |
  |
  | | <> | Not equal to |
  |
  |

分页查询

应用场景：当要查询的条目数太多，一页显示不全，需要分页提交sql请求
limit语句放在查询语句的最后
公式：要显示的页数为page，每一页条目数为size，查询的那一页：limit (page-1)*size,size; ```sql

分页请求语法
SELECT 查询列表 from 表名【JOIN TYPE JOIN 表2】【ON 连接条件】【WHERE 筛选条件】【GROUP BY 分组字段】【HAVING 分组后的筛选】【ORDER BY 排序字段】 LIMIT OFFSET, SIZE;

OFFSET：要显式条目的起始索引（起始索引从0开始） SIZE：要显式的条目个数

#查询第11条-第15条员工信息
SELECT * FROM employees LIMIT 10,5;

#查询有奖金的员工信息，只显示工资较高的前十名
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10;


<a name="Ig254"></a>
## 联合查询

- 含义：合并、联合，将多次查询结果合并成一个结果
- 应用场景：要查询的结果来自多个表，多个表没有直接的连接关系（不能Join查询）但查询的信息一致
- 特点
   - 要求多条查询语句的查询列数必须一致，否则报错
   - 要求多条查询语句的查询的各列类型、顺序最好一致，否则没有意义
   - union 去重，union all包含重复项 
```sql
    #联合查询语法
    查询语句1
    union 【all】
    查询语句2
    union 【all】
    ...
    查询语句n；


    #查询部门编号>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,csex FROM t_ca WHERE csex = '男'
    UNION
    SELECT t_id,tname,tsex FROM t_ua WHERE tsex = 'male';

概览