1. 基础查询
这是一段包含大多数关键字段的基础查询语句
/*
多行注释写法
多行注释写法
*/
-- 单行注释写法
SELECT DISTINCT `index_1`,`index_2`,SUM(`value_1`) AS 求和, SUM(`value_1`)*2 AS 求和*2 --SELECT选择字段
FROM db.tabel --FROM选择库表
WHERE `index_1` = "condition" AND `index_1` LIKE '%A' -- WHERE添加筛选条件
GROUP BY `index_1`,`index_2` -- 聚合分组
HAVING SUM(`value_1`) > 100 -- 聚合后添加筛选条件
ORDER BY SUM(`value_1`) DESC -- 排序 DESC降序
LIMIT 3 OFFSET 1 -- 返回三条记录，跳过一条记录，简写：LIMIT 3,1
以上面基础查询语句为例，逐步说明各关键字段含义，并拓展同类关键字含义。
1.1 SELECT 与 DISTINCT 与 AS
SELECT
字段选取需要的列，并且可以生成
常数列，日期列
计算列（常用算数运算符：加+ 减- 乘* 除/)
聚合列（聚合请看1.1.4）
SELECT * --全部列
SELECT col1, col2 --指定列
SELECT 1 AS "常数列", '2022-08-30' AS "日期列", col1/col2 AS "计算列" --生成列
DISTINCT
对整个表进行去重，多个NULL行也会被合为一行
AS
为列设定别名
使用中文时需要用双引号括起来
也可以简写，用空格替代AS
SELECT col1 AS "列1", col2 c2--指定列
1.2 FROM
选择从哪个表中获取数据，也可以选择给表起别名
FROM tabel --选择表
FROM db.tabel t--选择库与表，并起别名
1.3 WHERE 与 AND 与 LIKE
WHERE
用于过滤数据
- 在GROUP BY分组和聚合函数之前对数据行进行过滤，也就是筛选的是底表
- WHERE子句中不能使用聚合函数。
-- 示例写法
WHERE col1 条件语句 condition 逻辑运算符 col2 条件语句 condition2
AND-逻辑运算符
AND是逻辑运算符，用于连接多个条件
AND表示和，OR表示或，NOT表示非，还可以加上括号调整运算顺序
运算优先级：括号内、NOT、AND、OR
NOT不能单独使用，必须和其他查询条件结合使用
用法：
SELECT * FROM tabel WHERE NOT index_1 = 2;
SELECT * FROM tabel WHERE index_1 <> 2;# 等价于上一句
LIKE-谓词与比较运算符
LIKE属于谓词，谓词就是返回值为bool的函数
谓词包括（’LIKE’、’IN’、’BETWEEN’、’IS NULL’、’EXIST’）
在这个位置同样也可以使用比较运算符
- LIKE：模糊匹配，与通配符结合使用
-- 示例写法
WHERE col1 LIKE '%a'
| 通配符 | 作用 | 示例 |
|---|---|---|
| % | 替代 0 个或多个字符 | ‘%a%’：含有a的数据 |
| _ | 替代1个字符 | ‘_a’的：两位且结尾字母是a的 |
| [charlist] | 字符列中的任何单一字符 | [ab]1：开头是a或b，结尾为1的 |
| [^charlist]或[!charlist] | 不在字符列中的任何单一字符 | [^ab]1：开头不是a或b，结尾为1的 |
- IN：匹配值在列表中的数据
是OR的简便用法
-- 示例写法
WHERE col1 IN (value1,value2,···,valueN)
IN后面可以跟子查询出的列表
- BETWEEN：选取介于两个值之间的数据范围内的值
BETWEEN的范围可以是数值、文本或者日期，是闭区间，包含两端的值
-- 示例写法
WHERE col1 BETWEEN value1 AND value2
- 比较运算符
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
字符串也能进行比较，根据按照字典顺序进行比较，大致是0-9 a-z的顺序，从第一个字符开始逐步向后比较，例如”222”<”3”，”aaa”<”aba”
- IS NULL
NULL在前面的运算符中，既不是真，也不是假，所以两个完全相反的条件，都不返回Null值
只有在设置IS NULL时，才会返回空值
-- 示例写法
WHERE col1 IS NULL --记录是空值
WHERE col1 IS NOT NULL --记录不是空值
- EXIST
EXIST用于判断记录是否存在
EXIST只需要在右侧写一个参数，该参数通常是关联子查询
例子，
商品总表Product，商品在售表ShopProduct
查询主店在售商品的销售单价
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT * -- 这里通常写*
FROM ShopProduct AS SP
WHERE SP.shop_name = '主店'
AND SP.product_id = P.product_id);
1.4 GROUP BY
从1.1.4到1.1.7，属于SQL的透视表语句
GROUP BY 指定聚合键，对表进行分组，结合聚合函数使用，聚合函数用在SELECT字段中
注意：
- 聚合键中包含NULL，会以空行呈现出来
- GROUP BY 子句中不能使用 SELECT 子句中列的别名
- 使用GROUP BY时，SELECT中只能存在聚合键、聚合函数、常数三种列
只使用 GROUP BY 子句和聚合函数是无法同时得出小计和合计的。如果想
要同时得到，可以使用GROUPING 运算符
1.5 HAVING
HAVING和WHERE同样是筛选语句，并且条件语句写法和WHERE相同
但HAVING是筛选GROUP BY聚合之后的透视表
并且HAVING中能使用聚合函数，但WHERE中不行
注意：
- HAVING中包含的列，只能是聚合键、聚合函数、常数三种列
- HAVING中最好只写有关聚合函数的条件，聚合键的筛选可以放到WHERE里，速度更快也更好理解
- HAVING中不能使用SELECT中的别名
1.6 ORDER BY 与 DESC
ORDER BY 子句对查询结果进行排序
DESC（descendent） 降序 ASC（ascendent） 升序
注意：
- ORDER BY中可以指定多个排序键
- 排序健中包含 NULL 时，会在开头或末尾进行汇总
- ORDER BY 子句中可以使用 SELECT 子句中定义的列的别名
- ORDER BY 子句中可以使用 SELECT 子句中未出现的列或者聚合函数
- ORDER BY 子句中不要使用列的编号
1.7 LIMIT 与 OFFSET
LIMIT是限制返回结果的行数，
OFFSET是从第几行开始返回，默认为0，第一行表示为0
下面是等价写法
LIMIT 3 OFFSET 0
LIMIT 3 --OFFSET默认为0，不用写
LIMIT 3 OFFSET 1
LIMIT 3,1 --简写法
2. 子查询
子查询就是一次性的视图
-- 示例写法
SELECT col1, SUM_col2
FROM ( SELECT col1, SUM(col2) AS SUM_col2
FROM table_name
GROUP BY col1 ) AS Sub_tabel
写在FROM后面，用括号括起来的基础查询语句，就是子查询
括号后需要用AS起别名，Oracle中需要省略AS，否则会报错
注意：
- 使用子查询时，会先执行FROM里面内层的查询语句，再执行外层的查询语句
- 子查询可以多层嵌套，但嵌套层数的增加，SQL语句会变得越来越难读懂，性能也会越来越差，经量避免使用多层嵌套
标量子查询
子查询中有一类被称为标量子查询，标量子查询必须而且只能返回 1 行 1列的结果
例如：10 或者 “武汉” 这样的单一值
标量子查询，可以用在比较运算中等任何使用单一值的地方
用例：
在WHERE中用标量子查询实现聚合值用于比较
SELECT col1, col2, col3
FROM table_name
WHERE col3 > (SELECT AVG(col3)
FROM table_name) AS AVG_col3
关联子查询
关联子查询，有些难理解，先看例子
SELECT 班级 , 姓名, 身高
FROM table1 AS T1 --设置外层表别名，可以在子查询中用
WHERE 身高 > (SELECT AVG(身高)
FROM table1 AS T2 --设置内层表别名，只能在子查询中用，子查询外无法使用
WHERE T1.班级 = T2.班级 --关联子查询关键句
GROUP BY 班级)
上面代码是在查询，每个班级中，身高高于本班平均身高的人
在子查询中加入了WHERE语句，使得子查询返回结果是标量子查询，只返回本班级的平均身高的单一值，而不是所有班级平均身高的表
这就是关联子查询
使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“ < 表名 >.< 列名 > ”的形式记述
在细分的组内进行比较时，需要使用关联子查询。
注意：
- 关联子查询的关联条件，一定要写在子查询中
3. 多表查询
3.1 表加减
表的加减，即UNION （并集）、 INTERSECT （交集）、 EXCEPT （差集），统称为集合运算
集合运算注意事项：
- 作为运算对象的记录的列数必须相同
- 作为运算对象的记录中列的类型必须一致
- 可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次
UNION
取并集，在表一后面接上表二，默认去重，要保留重复行，需要加上ALL，即 UNION ALL
SELECT col1, col2
FROM table_name1
UNION -- 默认去重，要保留重复行，需要加上ALL，即 UNION ALL
SELECT col1, col2
FROM table_name2
ORDER BY col2 --只能在最后使用
INTERSECT
取交集，保留表一和表二中共同存在的行
SELECT col1, col2
FROM table_name1
INTERSECT
SELECT col1, col2
FROM table_name2
EXCEPT
取差集，保留表一中存在，但在表二中不存在的行，注意表一和表二的顺序
-- table_name1 - table_name2
SELECT col1, col2
FROM table_name1
INTERSECT
SELECT col1, col2
FROM table_name2
3.2 JOIN联结
联结可以理解为excel中的vlookup,vlookup属于左联结
在两个表中选取一列作为联结键，联结键值相同的行左右拼接合并为一行，就是联结
联结分为内外联结，外联结又分为左、右、全三种
还有一种很少用的交叉联结 CROSS JOIN（笛卡儿积）
-- 内联结(INNER JOIN) 只能选取出同时存在于两张表中的数据
SELECT t1.id, t1.col1, t2,col1 --同时存在于两张表中的列，必须用<表的别名>.<列名>”来命名
FROM tabel1 t1 # 主表
INNER JOIN tabel2 t2 # 连接的附表
ON t1.id = t2.id; # 联结键，主表附表连接条件，在本句后面可以继续写查询语句
--外联结
-- 左外联结, 选出t1存在的记录
FROM tabel1 t1
LEFT OUTER JOIN tabel2 t2
-- 右外联结, 选出t2存在的记录
FROM tabel1 t1
RIGHT OUTER JOIN tabel2 t2
-- 全外联结, 选出两表都存在的记录
FROM tabel1 t1
FULL OUTER JOIN tabel2 t2
--交叉联结
FROM tabel1 t1
CROSS JOIN tabel2 t2
解释一下交叉联结，交叉联结返回的行数是N*M，即两表的行数的乘积
因为交叉联结是对两张表中的全部记录进行交叉组合
4. CASE表达式
CASE表达式是一个判断语句，作为表达式，可以写在SQL查询的任何地方。
CASE表达式相当于其他语言里的 “if···else···”
语法(有简易写法，但只建议用下面这种写法)
CASE
WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
.
.
.
ELSE < 表达式 >
END
5. 窗口函数
5.1 窗口函数基础语法
窗口函数也称为 OLAP 函数（OnLine Analytical Processing）
窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作
基础语法：
< 窗口函数 > OVER ([ PARTITION BY < 列清单 >]
ORDER BY < 排序用列清单 >)
-- [] 中的内容可以省略
用例：根据不同的商品种类，生成销售单价的升序排名
SELECT product_name, product_type, sale_price,
-- 窗口函数开始
RANK () --选择使用的窗口函数
OVER
(PARTITION BY product_type --在横向上对表进行分组，可选
ORDER BY sale_price) -- 需要用窗口函数进行运算的列
-- 窗口函数结束
AS ranking -- 用窗口函数结果生成排名列
FROM Product
通过 PARTITION BY 分组后的记录集合称为窗口，即数据范围
不指定PARTITION BY时，将整个表作为一个大的窗口来使用
各个窗口在定义上绝对不会包含共通的部分
用例中可以看到，窗口函数是在SELECT子句中生成一列数据
原则上窗口函数只能在 SELECT子句中使用
5.2 常用窗口函数
排序窗口函数
以列表[100,100,150,200]为例子给排名
- RANK 函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次
例子：1、1、3、4，跳过了2 - DENSE_RANK 函数
计算排序时，即使存在相同位次的记录，也不会跳过之后的位次
例子：1、1、2、3，没有跳过2 - ROW_NUMBER 函数
赋予唯一的连续位次，即使存在相同位次
例子: 1、2、3、4
聚合函数
所有聚合函数都可以作为窗口函数使用
- SUM()：会按ORDER BY指定列排序，逐行累加
- AVG(): 会按ORDER BY指定列排序，逐行扩大取数范围求均值，并记录在最新一行
相当于按ORDER BY指定列的顺序，不断扩大窗口，同时运行函数，将结果记录在当前行
演示：
SELECT index_col, cost,
聚合函数(cost) OVER (ORDER BY index_col) AS current_
FROM Product
下面是结果与过程展示（=号后面是过程）
| index_col | value | SUM() | AVG() |
|---|---|---|---|
| 1 | 100 | 100=100 | 100=(100)/1 |
| 2 | 200 | 300=100+200 | 150=(100+200)/2 |
| 3 | 150 | 450=100+200+150 | 150=(100+200+150)/3 |
5.3 指定框架（汇总范围）
在窗口函数中，加入下面关键字，还能限定窗口的框架，常用于计算移动平均
- ROWS：指定行范围
- PRECEDING：向前数
- FOLLOWING：向后数
例子1：求近3年的花费
SELECT year, cost,
SUM(cost) OVER (ORDER BY year
ROWS
2 PRECEDING) AS moving_sum
FROM Product
效果示例：
| year | cost | moving_sum | 当前行标记 | 窗口范围 |
|---|---|---|---|---|
| 2018 | 1 | 1=1 | ||
| 2019 | 2 | 3=1+2 | 窗口 | |
| 2020 | 3 | 6=1+2+3 | 窗口 | |
| 2021 | 4 | 9=2+3+4 | 当前行 | 窗口 |
| 2022 | 5 | 12=3+4+5 |
例子2：求后3年的花费
SELECT year, cost,
SUM(cost) OVER (ORDER BY year
ROWS
2 FOLLOWING) AS moving_sum
FROM Product
效果示例：
| year | cost | moving_sum | 当前行标记 | 窗口范围 |
|---|---|---|---|---|
| 2018 | 1 | 6=1+2+3 | ||
| 2019 | 2 | 9=2+3+4 | 当前行 | 窗口 |
| 2020 | 3 | 12=3+4+5 | 窗口 | |
| 2021 | 4 | 9=4+5 | 窗口 | |
| 2022 | 5 | 5=5 |
例子3：求前后3年的花费
SELECT year, cost,
SUM(cost) OVER (ORDER BY year
ROWS
BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM Product
效果示例：
| year | cost | moving_sum | 当前行标记 | 窗口范围 |
|---|---|---|---|---|
| 2018 | 1 | 3=1+2 | ||
| 2019 | 2 | 6=1+2+3 | 窗口 | |
| 2020 | 3 | 9=2+3+4 | 当前行 | 窗口 |
| 2021 | 4 | 12=3+4+5 | 窗口 | |
| 2022 | 5 | 9=4+5 |
6. GROUPING 运算符
- ROLLUP
ROLLUP 可以同时得出合计和小计，是非常方便的工具，返回结果就是excel常见的透视表
SELECT
index_col1,index_col2,
GROUPING(index_col1),GROUPING(index_col2), --使用GROUPING函数
SUM(cost) AS sum_cost
FROM tabel_name
GROUP BY ROLLUP(index_col1,index_col2) -- MySQL中，本句要写成 GROUP BY index_col1,index_col2 WITH ROLLUP
GROUPING函数，若参数列含NULL时，返回1，原始数据时返回0
结合CASE语句可以区分NULL的分组
- CUBE
而CUBE的返回结果要比ROLLUP多，多返回的结果，是将聚合键按不同顺序排列的结果。
语句也很简单，直接把上面代码的ROLLUP替换成CUBE就行
这里对比一下ROLLUP与CUBE的结果，会发现多了第二第三行，也就是加上了
ROLLUP(index_col2,index_col1)的小记
ROLLUP
| index_col1 | index_col2 | sum_cost |
|---|---|---|
| 3300 | ||
| 一 | 1100 | |
| 一 | 百 | 100 |
| 一 | 千 | 1000 |
| 二 | 2200 | |
| 二 | 百 | 200 |
| 二 | 千 | 2000 |
CUBE
| index_col1 | index_col2 | sum_cost |
|---|---|---|
| 3300 | ||
| 百 | 300 | |
| 千 | 3000 | |
| 一 | 1100 | |
| 一 | 百 | 100 |
| 一 | 千 | 1000 |
| 二 | 2200 | |
| 二 | 百 | 200 |
| 二 | 千 | 2000 |
- GROUPING SETS
GROUPING SETS 用于从中取出个别条件对应的不固定的结果。
由于期望获得不固定结果的情况少之又少，因此与 ROLLUP 或者 CUBE 比起来，使用 GROUPING SETS 的机会也就很少了。
7. 函数
7.1 文本函数
常用文本函数：
| 算数函数 | 含义 | 备注 |
|---|---|---|
| str1||str2 | 字符串拼接 | SQL Server用”+”，MySQL用CONCAT(str1, str2) |
| LENGTH(str) | 字符串字节数 | SQL Server用LEN()，MySQL中字符数用CHAR_LENGTH，字节数不等于字符数 |
| LOWER(str) | 大写转小写 | |
| UPPER(str) | 小写转大写 | |
| REPLACE(str,被替换字符,替换字符) | 字符串替换 | |
| SUBSTRING(str FROM 截取的起始位置 FOR 截取的字符数) | 字符串截取 | PostgreSQL/MySQL专用语法, SQL Server用SUBSTRING(str,截取的起始位置,截取的字符数)， Oracle/DB2用SUBSTR(str,截取的起始位置,截取的字符数) |
7.2 日期函数
| 日期函数 | 含义 | 备注 |
|---|---|---|
| CURRENT_DATE | 当前日期 | SQL Server 无法使用, Oracle和DB2用法见下文 |
| CURRENT_TIME | 当前时间 | SQL Server 无法使用, Oracle和DB2用法见下文 |
| CURRENT_TIMESTAMP | 当前日期与时间 | Oracle和DB2用法见下文 |
| EXTRACT(日期元素 FROM 日期) | 截取日期元素 | 日期元素：YEAR MONTH DAY HOUR MINUTE SECOND |
Oracle和DB2中获取当前日期时间，可以看出日期函数在两个数据库中写法的规律
-- 当前日期
-- Oracle
-- 指定临时表（ DUAL ）
SELECT CURRENT_DATE
FROM dual;
-- DB2
/* CURRENT 和 DATE 之间使用了半角空格，指定临时表 SYSIBM.SYSDUMMY1 */
SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1;
-- 当前时间
-- Oracle
-- 指定临时表（ DUAL ）
SELECT CURRENT_TIMESTAMP
FROM dual;
-- DB2
/* CURRENT 和 TIME 之间使用了半角空格，指定临时表 SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1;
-- 当前日期与时间
-- Oracle
-- 指定临时表（ DUAL ）
SELECT CURRENT_TIMESTAMP
FROM dual;
-- DB2
/* CURRENT 和 TIME 之间使用了半角空格，指定临时表 SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1;
7.3 算术函数
常用的 + - * / 就是算数函数
常用算数函数：
| 算数函数 | 含义 | 备注 |
|---|---|---|
| ABS(num) | 绝对值 | |
| MOD(被除数，除数) | 求余 | SQL Server不支持该函数，用% |
| ROUND(num,保留小数位) | 四舍五入 |
7.4 转换函数
| 算数函数 | 含义 | 备注 |
|---|---|---|
| CAST(转换前的值 AS 想要转换的数据类型) | 转换类型 | |
| COALESCE(expression_1,expression_2,···) | 将NULL转为其他数值 | 参数无限，输出时从左到右判断值是否为NULL，返回第一个不是NULL的值 |
| 数据类型 | 解释 |
|---|---|
| INTEGER | 数值类型 |
| DATE | 日期类型 |
7.5 聚合函数
常用聚合函数：
| 聚合函数 | 含义 |
|---|---|
| COUNT | 计算表中的记录数（行数） |
| SUM | 计算表中数值列中数据的合计值 |
| AVG | 计算表中数值列中数据的平均值 |
| MAX | 求出表中任意列中数据的最大值 |
| MIN | 求出表中任意列中数据的最小值 |
注意：
- COUNT(*)统计整个表行数（包含NULL行）；COUNT(列名)不统计NULL行
- 聚合函数会将 NULL 排除在外。但 COUNT (*)例外，并不会排除 NULL
- AVG在遇到NULL时，分母=总行数-NULL行数
- MAX / MIN 函数几乎适用于所有数据类型的列。 SUM / AVG 函数只适用于数值类型的列
- 在聚合函数内使用DISTINCT，会先删除重复数据后再聚合，例如算种类COUNT(DISTINCT col_Type)