SQL备忘--Like/逻辑运算/Order By排序/Top N特殊处理备忘

1. Like 模糊查询

基本语法

在where 条件中使用LIKE关键字进行匹配

WHERE c1 LIKE ‘%end’ //可以匹配到“friend”等单词

匹配模式

• 百分号（%）： 表示匹配零个或多个任意字符
• 下划线（_）：表示匹配一个任意字符

转义

转义字符：ESCAPE关键字

如果查找模式包含了%或者_，需要用到转义字符

SELECT *
FROM table_name
WHERE c1 LIKE '%100#%%' ESCAPE '#'					--SQL定义'#'为转义字符关键字

转义字符后面紧跟的’%‘表示此处要匹配一个原字符’%'，因此上面的案例能匹配到：“进度已达100%，完成”

MYSQL和PostgreSQL自带’\'转义字符

如果省略ESCASE子句，则MYSQL和PostgreSQL中，可用默认的转义字符’\'进行转义， 例如“WHERE c1 LIKE ‘%100\%%’”

---

2. 逻辑运算符

AND OR 逻辑运算符

与其他语法相同，与或门有短路运算，当第一步能判断出结果时，不会进行第二步判断

NOT 逻辑非运算符

与其他运算符一起使用时，表示将随后的运算结果取反

NOT > 查找【不大于】指定值的数据
NOT BETWEEN xxx AND yyy 查找指定范围外的数据
NOT LIKE 不匹配某个模式
NOT c1 IS NULL 不为空， 等价于 c1 IS NOT NULL

运算符优先级：

1. 比较运算符的优先级比逻辑运算符的优先级高
2. 在逻辑运算符中NOT比AND的优先级高， AND比OR的优先级高
3. 各数据库对sql的优先级实现略有差异。为保证数据准确，最好能用小括号明确计算顺序，而不要过于依赖数据库自己的计算顺序

4. 排序

1. ORDER BY 可以用表达式

SELECT *
FROM table_name
WHERE c1="XXX"
ORDER BY c2*12 - c3

2. ORDER BY 可以用数字表示用第几列排序

SELECT *
FROM table_name
WHERE c1="XXX"
ORDER BY 2			--第二列排序

3. 空值在排序中的控制

排序位置

1. Mysql、Microsoft SQL Server、SQLite会把空值排在最前
2. Oracle和PostgreSQL会把空值排在最后

主动控制

Oracle、PostgreSQL及SQLite支持使用 NULLS FIRST关键字，将空值排在最前；或者用NULLS LAST关键字，将空值排在最后。

SELECT *
FROM table_name
WHERE c1="XXX"
ORDER BY c5	 NULL FIRST	

4. 中文排序控制

1. Oracle

• 默认使用AL32UTF8字符编码，中文按照偏旁部首进行排序
• 可以用SCHINESE_PINYIN_M来指定拼音排序

SELECT *
FROM table_name
WHERE c1=""
ORDER BY NLSSORT(c5, 'NLS_SORT = SCHINESE_PINYIN_M')

2. MYSQL8.0

• 默认使用utf8mb4字符编码，中文按照偏旁部首进行排序
• 可以用转换函数实现其他中文排序

SELECT *
FROM table_name
WHERE c1=""
ORDER BY CONVERT(c5 USING GBK)		--按拼音排序

3. Microsoft SQL Server

• 字符集是根据操作系统所在的区域进行设置，中国地区则默认使用Chinese_PRC_CI_AS排序规则，按照偏旁部首进行排序。
• 可以用COLLATE实现其他中文排序

SELECT *
FROM table_name
WHERE c1=""
ORDER BY  c5 COLLATE Chinese_PRC_CI_AI_KS_WS		--按拼音排序

还可用别的方式：

ORDER BY  c5 COLLATE Chinese_Stroke_CI_AS		--按笔画顺序排序

4. PostgreSQL

• 默认使用UTF-8字符集，按照偏旁部首进行排序。
• 可以用COLLATE实现其他中文排序

SELECT *
FROM table_name
WHERE c1=""
ORDER BY  c5 COLLATE "zh_CN"		--按拼音排序

5. SQLite

• 默认使用UTF-8字符集，按照偏旁部首进行排序.
• 无法改变其他排序

5. Top-N 取值

SQL标准定义了 FETCH和OFFSET子句限定返回结果数量（但只有Oracle、Microsoft SQL Server及PostgreSQL支持这种语法）

SELECT *
FROM table_name
OFFSET 0 ROWS
FETCH FIRST 5 ROWS ONLY;

OFFSET 表示偏移量，0则是从首项开始取。下面的FETCH表示取多少条数据，这里5表示仅取5条数据；ONLY限制了即使最后有多条重复数据也舍弃，最多只取前五条

Mysql、PostgreSQL、 SQLite支持另一种语法：LIMIT子句

SELECT *
FROM table_name
LIMIT 10 OFFSET 0;

也可以实现从首项开始取，取10条数据记录