查缺补漏 --之前没有get到的细节（SQL）

拿实习和牛客网练手的野路子本野，刷了一遍SQL必知必会，发现一堆细节木有注意过，记在这里啦~

1. 关于 SELECT

1. 数据表中的行从0开始编号，LIMIT n OFFSET m 返回从第 m+1 条数据起的 n 条数据，包括第 m+1 条数据。简化写法：LIMIT m, n。
   e.g. select * from table_1 limit 2,3 表示取第三、四、五条数据；而 select * from table_2 limit 2 offset 3 表示取第四、五条数据。

2. 一个习惯：Create table 时可以用注释描述列，这样较长时间后也不用纠结到底用的是什么数据维度了。

3. order by 也可以按 select 语句中，列的相对位置排序。这时的相对位置编号表示 select 中的第几个列（非从0开始编号）。
   （1）e.g. Select a,b,c from table_1 order by 2,3，排序列为 b 和 c。
   （2）问题：
   i. 有时更新 select 的列清单，但是忘了改 order by 子句；
   ii. 只能对应 select 列清单里的列。

4. between and 包括指定的开始值或结束值。

5. 通过过滤选择不包含指定值的所有行时，不会返回含NULL的行，因为数据库不知道它们是否匹配。

6. 一个习惯：and 比 or 的优先级较高，先进行运算。写 and 或 or 时，尽量按逻辑顺序加上括号以消除歧义。

7. in 操作符一般比一组 or 执行得更快。

8. 两个概念：
   （1）通配符：用来匹配值的一部分的特殊字符；
   （2）搜索模式：由字面值、通配符或二者组合构成的搜索条件。

9. 几个通配符

通配符	含义
%	任何字符出现任意次数（也可以是0次）
_	匹配单个字符一次
[ ]	指定要匹配的字符集，e.g. ‘[JM]%’ 表示以J或M开头。可以取反：’[^JM]%’ 表示不以J或M开头

10. 通配符注意：
    （1）一些DBMS会用空格填补字段，因此在检索 “以 character 结尾” 的数据时，可用 “%character%” 代替 “%character”。
    （2）%不会匹配 NULL。
    （3）通配符搜索时间开销大，应优先使用其他操作符，并尽量避免把其放在搜索模式的开始处。

11. （1）数据转换和格式化工作应尽量在数据库服务器上完成，而不是借助客户端应用程序。
    （2）从客户端来看，计算字段的数据与实际的列返回方式相同。

12. 操作符/函数整理

类型	操作符（Based on DBMS）	含义
字符串操作	+, ||, concat( )	字符串拼接，其中concat( )括号中可以有多个字符串
字符串操作	RTRIM( ), LTRIM( ), TRIM( )	去掉右边 OR 左边 OR 两侧的空格
字符串操作	UPPER( ), LOWER( ), LEFT( ), RIGHT(), LEN( )	顾名思义
字符串操作	SPLIT(str,regex), SUBSTR(str,offset,len)	分割字符串，截取字符串
时间	DATEPART( ), YEAR( ), strftime( ), DATE( ), GETDATE( ), to_date( ), date_diff( ), date_sub( ), date_add( )	不同DBMS不太一样，用的时候再查
数学	ABS( ), COS( ), SIN( ), TAN( ), EXP( ), PI( ), SQRT( )	顾名思义
聚集函数	AVG( ), COUNT( ), MAX( ), MIN( ), SUM( )	顾名思义

13. 函数注意：
    （1）COUNT(column_name)会忽略NULL行，而 COUNT(*)不会。
    （2）DISTINCT必指定列名，如：COUNT(DISTINCT column_name）

14. GROUP BY注意：
    （1）若在 SELECT 中用表达式，需要在 GROUP BY 中用相同的表达式，不能使用别名；ORDER BY 可以使用别名。
    （2）除聚集语句外，SELECT每一列都必须在 GROUP BY 中给出。
    （3）NULL会被作为一个分组返回。

15. UNION 注意：
    （1）每个 SELECT 包含相同的列、表达式、聚集函数；列数据类型可以不同，但必须能够隐含转换。
    （2）UNION 中的 ORDER BY 只能写在最后一个SELECT 的最后，且是对所有结果排序。

16. CASE WHEN

select stu_id,
(case when chinese>=90 then '优秀'
when chinese>=60 then '及格'
else '不及格' end) as 语文,
(case when math>=90 then '优秀'
when math>=60 then '及格'
else '不及格' end) as 数学,
from table 

17. 分组排序

（1）row_number( )：排序相同时不重复；
（2）rank( )：排序相同时重复，总数不变；
（3）dense_rank( )：排序相同时重复，总数减少。

e.g. 查询每个学生考得最好的科目并展示该科目的成绩，其中表 student_subject_score 结构如下：

student_name	subject_name	score

查询语句为：

select * from (
select s.*,row_number() over(partition by s.student_name order by s.score desc) rank from student_subject_score s
) where rank=1;

得：

student_name	subject_name	score	rank

2. 关于其他

2.1 INSERT：

（1）INSERT INTO 时，最好明确给出列名。如：

INSERT INTO table_1(a,b,c)
VALUES('233','2333','23333')

酱紫不怕后续改表的结构；也可以通过这种方式不给一些列赋值，只要这些列允许为 NULL或有缺省值。

（2）INSERT INTO SELECT：

INSERT INTO table_1(a,b,c)
SELECT a,b,c FROM table_old

如果 table_old 木有数据，也不报错，插入0条数据。
不要求 SELECT 子句里的列名匹配，位置能对上即可。

（3）SELECT INTO

将数据复制到一个新表，其中新表在运行中创建。在一些 DBMS 中，下面语句将建立一个新表 table_new。

SELECT a,b,c
INTO table_new 
FROM table_old

在另一些 DBMS中：

CREATE TABLE table_new AS SELECT a,b,c FROM table_old

这是用副本测试 SQL 语句的好办法。

2.2 UPDATE/DELETE 注意：

（1）注意 WHERE 语句，UPDATE/DELETE 之前可以先用 SELECT 试一下是不是准确的 WHERE 语句；
（2）如果真的要删除所有行，可以用更高效的 TRUNCATE TABLE；
（3）更改一行中的多个列值，则使用一个 SET 语句，其中各个列的键值对用逗号分隔即可。
（4）删除某一行某个列的值： SET column_name=NULL 即可。

2.3 CREATE/ALTER/DROP 注意：

（1）ALTER 时做备份！
（2）CREATE 时可以多使用 DEFAULT，而不是 NULL。
（3）DROP 时注意该表与其他表的关系。
（4）表结构更改还可以使用 SELECT INTO，然后把旧表重命名或删除。

2.4 视图 注意：

（1）视图：虚拟的表，只包含使用时动态检索数据的查询，不包含列或数据；
（2）使用视图的原因：

• 重用 SQL语句，简化操作（因为视图是一个查询）；
• 使用表的一部分，而不是整个表；
• 保护权限，可以授予用户访问一部分表的权限；
• 更改数据格式和表示。

（3）太多复杂或嵌套视图会显著降低性能。

2.5 约束 注意：

（1）约束：管理如何插入或处理数据库数据的规则，可在建立表时定义；
（2）通过 ALTER 添加约束：

--添加主键
ALTER TABLE vendors
ADD CONSTRAINT PRIMARY KEY(ven_id) 

--添加外键，表示cust_id中的任何值必须是 Customers 表中的 cust_id。
ALTER TABLE orders
ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

--添加唯一约束。
ALTER TABLE vendors
ADD CONSTRAINT UNIQUE(vend_name)

--添加检查约束。
ALTER TABLE customers
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

（3）唯一约束与主键约束

主键约束	唯一约束
任意两行值不相同	一样
不允许 NULL	允许 NULL
包含主键值的列不能修改或更新	可以修改或更新
主键值不能重用	可以重用
一个表只有一个	一个表可有多个
能定义外键	不能定义外键