六、DQL查询语句 - 《MySQL》

6.1基础查询
6.2条件查询
- 6.2.1筛选条件
6.3排序查询
6.4分组查询
6.5连接查询
6.6子查询
6.7分页查询
6.8连接查询

6.1基础查询

# 查询单个/多个字段
SELECT [查询列表]
FROM 表名;
# 查询所有字段
SELECT *
FROM 表名;

6.1.1别名

方式一：

SELECT
    字段1 AS “别名1”,
    字段2 AS “别名2”,
    字段3 AS “别名3”
FROM 表名;

方式二：

SELECT
    字段1 “别名1”,
    字段2 “别名2”,
    字段3 “别名3”
FROM 表名;

注：如果别名中有空格，一定要用双引号””包起来，没有空格其实可以不加引号

6.1.2查询去重

关键字：distinct

SELECT DISTINCT 查询列表
FROM 表名;

6.1.3 +号的作用

MySQL中，+号只有一个作用，那就是运算符
例如：SELECT x+y FROM xxx;

如果x和y两个操作数都为数值型，则做加法运算
如果x和y两个操作数中，只要其中有一方为字符型，Mysql将视图把字符型数值转换成数值型，如果转换成功则继续做加法运算，如果转失败，则将其中的字符型转换成数值0
如果x和y两个操作数中，只要其中一方为null，则加法运算的结果一定为null

6.2条件查询

语法：

SELECT 查询列表
FROM 表名
WHERE 筛选条件;

6.2.1筛选条件

筛选条件支持条件表达式、逻辑表达式、模糊查询
条件表达式查询
- >、<、 =、 !=、 <>、 >=、 <=

逻辑表达式查询
- &&、||、 !、 and 、or、 not

模糊查询
like：一般都与通配符配合使用
通配符：

%：代表任意多个字符，包含0个字符
_：代表任意一个字符

between and
语法：between x and y，意为在x和y之间

使用between and可以提高语句的简洁度
包含临界值，意思就是x到y的闭区间
两个临界值调换顺序的话查询结果会发生变化

in
语法：x in (列表y)，意为x是列表y的其中一个

使用in提高语句简洁度
in列表的值类型必须一致或兼容
in列表中不支持通配符

is null：用来判断是否是空值，字面意思，为空则真

is not null：用来判断是否不是控制，不为空则真

is null 和 <=>

IS NULL 仅仅可以判断null值，可读性较高
<=>既可以判断null值，也可以用来判断普通的数值，可读性较差

6.3排序查询

SELECT 查询列表
FROM 表名
[WHERE筛选条件]
ORDER BY 排序列表 ASC | DESC

ASC | DESC
- ASC：升序排列
- DESC：降序排列
- 如果不写，默认是ASC，当然前提得有ORDER BY才行
ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
ORDER BY子句一般放在查询语句的最后面，除了LIMIT子句，它应该是最后的

6.4分组查询

SELECT 分组函数，列（要求出现在group by的后面）
FROM 表
【WHERE 筛选条件】
GROUP BY 分组的列表
【ORDER By 子句】
【HAVING 筛选条件】

注意：

分组查询中的筛选可以分为两类，分组前筛选和分组后筛选，这两类筛选的数据源是不一样的
- 筛选前的数据源为已存在表中的原始数据，放在group by前面，关键字为：WHERE
- 筛选后的数据源为分组后的结果集，放在group by后面，关键字为HAVING
- 分组函数做条件肯定放在HAVING后面，表中的原始数据做条件肯定放在WHERE后面
能用分组前筛选的，优先考虑使用分组前筛选
group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开，没有顺序要求）、表达式和函数
可以添加排序，排序放在整个分组查询最后的位置（也就是LIMIT）

6.5连接查询

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

什么是笛卡尔集？

假如表1有m行，表2有n行，查询的结果为m*n行
发生原因：没有有效的连接条件
解决办法：添加有效的连接条件

6.5.1连接查询分类

按年代分类：

sql92：MySQL中只支持内连接
sql99：MySQL中支持所有（内连接+外连接+交叉连接）

按功能分类：

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接
- 右外连接
- 全外连接
交叉连接

6.5.2 SQL92

等值连接

SELECT 【筛选字段】
FROM 
    表1 别名,
    表2 别名
WHERE 连接条件
【group by分组】
【having 分组后筛选】
【order by排序】;

注：

多表连接的结果为多表的交集部分
n表连接至少需要（n-1）个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配所有查询子句来使用，比如排序、分组、筛选

非等值连接

SELECT 【查询列表】
FROM 
    表1 别名,
    表2 别名
WHERE 连接条件（一般都是xxx between num1 and num2）
【group by分组】
【having 分组后筛选】
【order by排序】;

自连接

SELECT 【查询列表】
FROM
    表1 别名1，
    表1 别名2
WHERE 别名1.xxx = 别名2.xxx
【group by分组】
【having 分组后筛选】
【order by排序】;

6.5.3 SQL99

内连接

等值连接

SELECT 【查询列表】
FROM 表1 别名1
INNER JOIN 表2 别名2 ON 连接条件
INNER JOIN 表3 别名3 ON 连接条件
...
INNER JOIN 表n 别名n ON 连接条件
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】

非等值连接

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名 ON 表1.xxx between 表2.xxx and 表2.xxx
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】

自连接

SELECT 查询列表
FROM 表1 别名1
INNER JOIN 表1 别名2 ON 连接条件
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】

外连接

SELECT 查询列表
FROM 表1 别名
LEFT|RIGHT OUTER JOIN 表2 别名
ON 表1.xxx = 表2.xxx
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】

应用场景：用于查询一个表中有，另一个表中没有的记录
特点：

外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示null
- 外连接查询结果=内连接结果+主表中有而从表中没有的记录
左外连接，left outer join左边的是主表
右外连接，right outer join右边的是主表
全外连接=内连接的结果+表1中有但表2中没有+表2中有但表1中没有的结果
左外连接和右外连接交换两个表的顺序，可以实现同样的效果

交叉连接

SELECT 查询列表
FROM 表1 别名
CROSS JOIN 表2 别名

????????这不是笛卡尔积

6.6子查询

什么是子查询？

嵌套在其他语句内部的select语句称为子查询（内查询）
外面的语句可以是insert、update、delete、select，一般select作为外面的比较多
外面如果为select语句，则称为外查询或主查询

6.6.1子查询分类

按出现的位置分
- select后面
  - 仅仅支持标量子查询
- from后面
  - 表子查询
- where或having后面
  - 标量子查询
  - 列子查询
  - 行子查询
- exists后面
  - 标量子查询
  - 列子查询
  - 行子查询
  - 表子查询
按结果集分
- 标量子查询（单行子查询）：结果集为一行一列
- 列子查询（多行子查询）：结果集为多行一列
- 行子查询：结果集为多行多列
- 表子查询：结果集任意（是结果集就行）

6.6.2select

标量子查询

# 案例：查询每个部门的员工个数
# 第一种方法
SELECT
    d.*,
    count(employee_id)
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id;
# 第二种方法
SELECT
    d.*,
    (
        SELECT count(*)
        FROM employees e
        WHERE e.department_id = d.department_id
    )
FROM departments d;

6.6.3 from后面

表子查询

# 案例：查询每个部门的平均工资的工资等级
SELECT
    ag_dep.*,
    j.grade_level
FROM
    (
        SELECT
            d.department_id,
            d.department_name,
            avg(salary) avg_salary
        FROM employees e
        INNER JOIN departments d ON e.department_id = d.department_id
        GROUP BY d.department_id
    ) ag_dep
INNER JOIN job_grades j ON ag_dep.avg_salary BETWEEN j.lowest_sal AND highest_sal;

6.6.4 where或having后面

标量子查询

# 1.标量子查询
# 案例：谁的工资比Abel高？
USE myemployees;
# 第一步：查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
# 第二步：查询员工信息，满足salary>第一步的结果
SELECT *
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
    );

# 案例：返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资
# 第一步：先查出143员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
# 第二步：查出141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
# 第三步：查询员工信息，满足salary>第一步的结果并且job_id = 第二步的结果
SELECT
    last_name,
    job_id,
    salary
FROM employees
WHERE
    salary > (
        SELECT salary
        FROM employees
        WHERE employee_id = 143
        )
    AND
    job_id = (
        SELECT job_id
        FROM employees
        WHERE employee_id = 141
        );

# 案例：返回公司工资最少的员工的last_name,job_id,salary
SELECT
    last_name,
    job_id,
    salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
    );

# 案例：查询最低工资大于50号部门最低工资的部id和其最低工资
SELECT
    department_id,
    MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    );

# 非法使用标量子查询
SELECT
    department_id,
    MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT salary
    FROM employees
    WHERE department_id = 50
    );

列子查询

# 2.列子查询(多行子查询)
# 案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id IN (1400, 1700)
    )
ORDER BY last_name;
# 连接查询也可以做
SELECT last_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id IN (1400, 1700)
ORDER BY last_name;

# 案例：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id、salary
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id != 'IT_PROG'
    AND
    salary < ANY(
        SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG'
        );
# 第二种方式：
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id != 'IT_PROG'
    AND
    salary < (
        SELECT DISTINCT MAX(salary)
        FROM employees
        WHERE job_id = 'IT_PROG'
        );

# 案例：返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id和salary
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id <> 'IT_PROG'
    AND
    salary < ALL (
        SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG'
    );
# 第二种方式
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id <> 'IT_PROG'
    AND
    salary < (
        SELECT DISTINCT MIN(salary)
        FROM employees
        WHERE job_id = 'IT_PROG'
    );

行子查询

# 3.行子查询（结果集一行多列/多行多列）
# 案例：查询最小的员工编号并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary) = (
    SELECT
        MIN(employee_id),
        MAX(salary)
    FROM employees
    );

6.6.5 exists后面

/*
exists：检查是否存在集合中，1为true，0为falase
语法：
exists(完整的查询语句)
结果：1或0
*/
SELECT exists(
    SELECT *
    FROM employees
    WHERE salary = 3000000
           );

# 案例：查询有员工部门名
SELECT department_name
FROM departments d
WHERE exists(
    SELECT *
    FROM employees e
    WHERE e.department_id = d.department_id
          );

# 案例：查询没有女朋友的男生信息
SELECT * FROM beauty;
SELECT b.*
FROM boys b
WHERE b.id NOT IN (
    SELECT boyfriend_id
    FROM beauty
    );

SELECT b.*
FROM boys b
WHERE NOT exists(
    SELECT boyfriend_id
    FROM beauty g
    WHERE g.boyfriend_id = b.id
    );

6.7分页查询

select 查询列表
from 表
limit [offset], size;

offset 代表起始的条目索引，默认从0开始
size 代表要显示的条目数

公式：

# 假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size, size

6.8连接查询

union联合查询：将多条查询语句的结果合并成一个结果
应用场景：要查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的的信息一致时，可以使用联合查询

# 去重的联合查询
查询语句1
union
查询语句2
union
……
union
查询语句n

# 不去重的联合查询
查询语句1
union all
查询语句2
union all
……
union all
查询语句n

注：

要求多条语句的查询列是一样的（都是n列）
要求多条查询语句查询的的每一列的类型和顺序是一致的（名字对名字，编号对编号等等，否则查询出的数据无意义）
使用union关键字默认是去重的，如果想要包含重复项，可以使用union all