DQL Review

2020-07-05

字数统计: 2.1k字 | 阅读时长≈ 8分

DQL Review

数据库是大二的时候学的，后来很少用过了，一般也是一些简单的 select，最近看到一些 SQL 的应用，写篇笔记回顾一下。

数据库查询语言

SELECT
{* | <字段列名>}    		-- 要查询字段的名称
[
FROM <表 1>, <表 2>…    -- 要查询的数据来源
[WHERE <表达式>				 -- 查询要满足的条件
[GROUP BY <group by definition>  		-- 按照指定的字段分组
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]		-- 根据某个字段排序，有 ASC 和 DESC
[LIMIT[<offset>,] <row count>] 		-- 查询出来的数据条数
]

查询表中的字段

* 通配符查询，查询所有字段，按照数据表中字段的顺序进行排列，不能改变字段的排列顺序。
指定要查询的字段名称

注意：由于索引的存在，为了走索引加快查询速度，尽量指明要查询的字段，而不是使用通配符

使用 DISTINCT 过滤重复数据

1	SELECT DISTINCT<字段名> FROM <表名>;

注意：

DISTINCT 关键字只能在 SELECT 语句中使用
如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重

使用 DISTINCT 关键字返回不重复字段的条数

1	SELECT COUNT(DISTINCT<字段>) FROM <表名>;

使用 LIMIT 限制查询结果的条数

LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。

指定初始位置
1
LIMIT 初始位置，记录数；
不指定初始位置
1
LIMIT 记录数；
记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。
LIMIT 和 OFFSET 组合使用
1
LIMIT 记录数 OFFSET 初始位置；

使用 ODER BY 排序

1
2
3

-- ASC 升序，默认
-- DESC 降序
ORDER BY <字段名>[ASC|DESC];

注意：

ORDER BY 关键字后可以跟子查询
当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待
ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序

使用 WHERE 条件查询

1	WHERE 查询条件;

查询条件：

带比较运算符和逻辑运算符的查询条件
带 BETWEEN AND 关键字的查询条件
带 IS NULL 关键字的查询条件
带 IN 关键字的查询条件
带 LIKE 关键字的查询条件

模糊查询

1	[NOT] LIKE '字符串'；

支持 % ：代表任何长度的字符串，字符串的长度可以为 0

“_”：只能代表单个字符，字符的长度不能为 0

注意：

如果查询内容中包含通配符，可以使用“\”转义符
“%”通配符可以到匹配任意字符，但是不能匹配 NULL

BETWEEN AND 范围查询

1	[NOT] BETWEEN 取值 1 AND 取值 2;

BETWEEN AND 能匹配指定范围内的所有值，包括起始值和终止值

空值查询

IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。

1	IS [NOT] NULL

如果字段的值是空值，则满足查询条件，该记录将被查询出来。

GROUP BY 分组查询

1	GROUP BY <字段名>;

查询结果会只显示每个分组的第一条记录

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

1
2
3

SELECT <字段 1>,GROUP_CONCAT(<字段 2>) 
FROM <表名> 
GROUP BY <字段 1> WITH ROLLUP;

HAVING 过滤分组

1	HAVING <查询条件>;

对分组后的数据进行过滤

WHERE 和 HAVING 关键字的区别：

WHERE 用于过滤数据行，而 HAVING 用于过滤分组

WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。

WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤。

WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。

WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

连接

COSS JOIN 交叉连接 — 笛卡尔积

1
2
3

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句];

INNER JOIN 内连接

1	SELECT <字段名> FROM <表1> [INNER] JOIN <表2> [ON子句];

查询结果都是符合连接条件的记录

LEFT/RIGHT JOIN 外连接

左外连接

1	SELECT <字段名> FROM <表1> LEFT [OUTER] JOIN <表2> <ON子句>;

以左表为基准，保留左表的全部数据，右表匹配不上的为 NULL

右外连接

1	SELECT <字段名> FROM <表1> RIGHT [OUTER] JOIN <表2> <ON子句>;

以右表为基准，保留右表的全部数据，左表匹配不上的为 NULL

子查询

子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套，子查询经常出现在 WHERE 子句中。

1 2	-- 操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字 WHERE <表达式> <操作符> (子查询)；

注意：

子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置

-- 嵌套在 SELECT 语句的 SELECT 子句中
SELECT (子查询) FROM 表名;
-- 嵌套在 SELECT 语句的 FROM 子句中
SELECT * FROM (子查询) AS 表的别名;

只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的 SELECT 子句中出现的字段，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。

联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起，而 UNION 是将 UNION ALL 后的结果进行一次 DISTINCT 去除掉重复数据

处理无效数据

对于数值列或 TIME 列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点，并把结果值存储起来。
对于除 TIME 列以外的其他类型列，非法值会被转换成与该类型一致的“零”值。
对于字符串列（不包括 ENUM 或 SET），过长的字符串将被截断到该列的最大长度。

常用函数

字符串函数

函数	功能
LOWER	将字符串所有字符变为小写
UPPER	将字符串所有字符变为大写
CONCAT	进行字符串拼接
LEFT	返回字符串最左边的字符
RIGHT	返回字符串最右边的字符
INSERT	字符串替换
LTRIM	去掉字符串左边的空格
RTRIM	去掉字符串右边的空格
REPEAT	返回重复的结果
TRIM	去掉字符串行尾和行头的空
SUBSTRING	返回指定的字符串
LPAD	用字符串对最左边进行填充
RPAD	用字符串对最右边进行填充
STRCMP	比较字符串 s1 和 s2
REPLACE	进行字符串替换

数值函数

日期和时间函数

流程函数

函数	功能
IF(value,t f)	如果 value 是真，返回 t；否则返回 f
IFNULL(value1, value2)	如果 value1 不为 NULL，返回 value1，否则返回 value2
CASE WHEN[value1] THEN[result1] …ELSE[default] END	如果 value1 是真，返回 result1，否则返回 default
CASE[expr] WHEN[value1] THEN [result1]… ELSE[default] END	如果 expr 等于 value1，返回 result1，否则返回 default

本文作者： Kelly Liu
本文链接： http://tiantianliu2018.github.io/2020/07/05/DQL-Review/
版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！