SQL 基础笔记（一）-工具盒子

本笔记整理自《SQL 基础教程》、《MySQL 必知必会》和网上资料。个人笔记不保证正确。

一、基础 {#一基础}

SQL，即结构化查询语言，是为访问与操作关系数据库中的数据而设计的语言。

关系数据库以行(row)为单位读写数据
SQL 根据功能的不同，可分为三类（其中DML用得最多，增删查改嘛）
1. DDL(Data Definition Language, 数据定义语言): CREATE/DROP/ALTER
2. DML(Data Manipulation Language, 数据操作语言): SELECT/INSERT/UPDATE/DELETE
3. DCL(Data Control Language, 数据控制语言): COMMIT/ROLLBACK/GRANT/REVOKE
SQL 语句要以分号结尾。换行在 SQL 中不表示结束，而等同于空格。
SQL 不区分**关键字(Keyword)**的大小写，但是描述符就不一定了。
- 这里有个坑：MySQL 中，数据库和表其实就是数据目录下的目录和文件，因而，操作系统的敏感性决定数据库名和表名是否大小写敏感 。这就意味着数据库名 和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix/Linux 系统中是大小写敏感的。（注意仅指数据库名和表名）可通过修改配置文件的lower_case_table_names属性来统一这一行为。
- 而字段名 、字段内容 都是内部数据，是操作系统无关的。它们的大小写敏感性，由 MySQL 的的校对（COLLATE）规则来控制。该规则体现在 MySQL 的校对字符集（COLLATION）的后缀上：比如 utf8字符集，utf8_general_ci表示不区分大小写，这个是 utf8 字符集默认的校对规则；utf8_general_cs 表示区分大小写，utf8_bin 表示二进制比较，同样也区分大小写。
SQL 中的字符串和日期需要用单引号引用起来，日期有特定格式年-月-日

修改字符集：set names <字符集名> 记住在 MySQL 中，utf-8mb4 才是完全的 utf-8字符集。

二、DDL {#二ddl}

1. 数据库的创建和删除 {#1-数据库的创建和删除}

创建数据库

|---|-------------------------------------| | 1 | sql CREATE DATABASE <数据库名称>; |

|---|-----------------------------------| | 1 | sql DROP DATABASE <数据库名称>; |

2. 创建表： {#2-创建表}

关系表的设计 {#关系表的设计}

关系表的设计，要确保把信息分解成多个表，一类信息一个表，各表通过某些常用的，基本不会改变的值（即关系表设计中的关系，也常称为外键）互相关联。尽量不要有冗余数据。

语句： {#语句}

|-----------------|----------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 | sql CREATE TABLE <表名> ( <列名1> <数据类型> <该列所需约束>, <列名2> <数据类型> <该列所需约束>, <列名3> <数据类型> <该列所需约束>, ... <该表的约束1>, <该表的约束2>... ); |

举例：

|-------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 | sql CREATE TABLE `persons` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` CHAR(20) NOT NULL, PRIMARY KEY (`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |

后面的是设置引擎和默认字符集。工作上，表的设计一定要深思熟虑，因为改起来很困难。

字段类型（MySQL） {#字段类型mysql}

有四类数据类型：字符串、数字、日期、二进制。它们又根据数据长度的区别，下分为多个类型。

字符串：
数字
日期
二进制

约束 {#约束}

SQL 约束是除了数据类型之外，对列中数据追加的限定条件。

类型约束：NOT NULL、AUTO_INCREMENT、UNSIGNED（这个只 MySQL 支持）
默认值：DEFAULT，举例 <列名3> VARCHAR(32) NOT NULL DEFAULT "los angeles"
表约束：PRIMARY KEY 主键约束（主键默认 UNIQUE 且 NOT NULL）

此外还有 FOREIGN KEY 和 CHECK 两个约束语句，在进阶笔记中介绍。

P.S. 字段约束也可以写成表约束（比如主键约束），而反过来很可能不行。

3. 删除表和更新表定义 {#3-删除表和更新表定义}

删除表（危险操作）
- 删除整个表：

|---|-----------------------------| | 1 | sql DROP TABLE <表名>; |

- 只清空表内数据，但留下表：

|---|------------------------------------------------------------------| | 1 | sql TRUNCATE <表名>; -- 非标准SQL语句，但是大部分DB都支持。（可能不能ROLLBACK） |

更新表定义（麻烦的操作）所以所创建表前要仔细想好格式了，更新表定义是不得已才能为之。添加列定义：

|---|-------------------------------------------------------------| | 1 | sql ALTER TABLE <表名> ADD COLUMN <列名> <数据类型> <该列的约束>; |

删除列定义：

|---|-----------------------------------------------| | 1 | sql ALTER TABLE <表名> DROP COLUMN <列名>; |

三、DML {#三dml}

万恶之源 NULL

1. 查询（重点） {#1-查询重点}

基本语句：

|-----------|------------------------------------------------------------------------------------| | 1 2 3 4 5 | fallback SELECT <字段1> AS <别名1>, <字段2> AS <别名2>, ... FROM <表名> WHERE <过滤条件>; |

可用 DISTINCT 修饰列名 ，使查询结果无重。例：SELECT DISTINCT <列名> FROM <表名>
过滤条件可使用比较运算(<>、=等)和逻辑运算(AND OR NOT).
过滤条件中，比较运算会永远忽略 NULL 值 ，如果需要对 NULL 值做操作，需要使用 IS NULL 或 IS NOT NULL（说忽略也许不太准确，NULL 既不为真也不为假，反正少用 NULL。。）
包含NULL的四则运算，得到的结果总为NULL

2. 聚合与排序（重点） {#2-聚合与排序重点}

聚合函数 {#聚合函数}

即对列进行统计分析的函数，主要有五个：

COUNT：计算列的行数。（只有COUNT(*)会计算NULL行）
SUM：求该列之和。
AVG：求该列的平均值。
MAX/MIN：求该列的最大/最小值

NOTE：

聚合函数计算时会排除所有NULL行。只有COUNT(*)例外，NULL行也会被它计数。
MAX/MIN 几乎适用于所有数据类型的列（对非数值型，以其二进制值来排序），而SUM/AVG只能用于数值类型的列。
聚合函数操作的列，也能用DISTINCT修饰。例：SELECT COUNT(DISTINCT <列名>) FROM <表名>
聚合函数只能用于SELECT子句和还没讲的HAVING子句（以及 ORDER BY 子句）中。

分组(GROUP BY) {#分组group-by}

分组以聚合键为分类标准，将数据分为多个逻辑组，从而能分别对每个组进行聚合运算。（分组是为了分类聚合）

若出现了 GROUP BY 子句，查询结果一定是每一组一行。
GROUP BY 会将 NULL 作为一组特定数据，显示为空。

聚合对SELECT子句的限制 {#聚合对select子句的限制}

首先要理解的是：

聚合函数的操作对象为某一列，而产生的结果只有一个值。
GROUP BY 的操作对象为一列或者多列，产生的结果呢，是每一组一个值。

因此为了避免歧义，只要使用了聚合函数或 GROUP BY 二者之一，SELECT 子句就只能包含：

常数
其他聚合函数（该聚合函数的操作对象可以为其他列）
如果使用了 GROUP BY 子句，还能包括该子句所指定的列名。（也就是聚合键）但是绝不能包含其他的列名，因为这会有歧义。

此外，还有一个问题是由 SQL 的执行顺序引起的。应该能很容易猜到，SELECT 语句的执行顺序和书写顺序是不一致的 。查询应该是从表开始，所以 FROM 语句一定先执行。然后应该要过滤(WHERE)，再是分组(GROUP BY)，最后才是 SELECT 语句。（就已经学到的子句而言，顺序是这样）因此按理说，SELECT 语句定义的别名，是不能在 GROUP BY 里使用的。（也有些DB支持该用法，但不通用）

对聚合结果进行过滤(HAVING) {#对聚合结果进行过滤having}

从刚刚说过的SQL执行顺序可见，WHERE要比GROUP BY先执行，因此如果想过滤分组后的结果，不能用它。而应该使用 HAVING 子句。HAVING 子句和 WHERE 子句都是用来过滤的，但是执行顺序的不同也就决定了它们的用途不同。

NOTE：

有时候，会发现某个过滤条件，不论是先执行（就是写在WHERE子句中）还是后执行（写在HAVING 中）都没问题，这时候应该将它写在WHERE子句中，这样GROUP BY操作的数据会更少，处理更快。
HAVING 子句的元素，也存在和 SELECT 子句同样的限制。不能使用聚合键以外的列名。

排序(ORDER BY) {#排序order-by}

ORDER BY 子句在 SELECT 子句之后执行，因此它能使用 SELECT 子句中定义的别名。（而 GROUP BY 之前已经说过不能用别名了）格式：

|-----------------|---------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 | sql SELECT <字段1> AS <别名1>, <字段2> AS <别名2>, ... FROM <表名> WHERE <过滤条件1> GROUP BY <列名1>, <列名2>... HAVING <过滤条件2> ORDER BY <列名/别名1>, <列名/别名2>... ; |

多排序键/列：指定多排序键时的排序规则为：优先使用左侧的列，如果该列存在相同值，再接着参考右侧的键，依此类推。（如果左侧键值不同，右侧的键就不会被使用了）
NULL 值的顺序：排序键中出现了 NULL 值时，这类值会在结果的开头或结尾汇总，究竟是排在开头还是结尾，并没有特殊规定。
ORDER BY 子句只影响结果的先后顺序，因此排序键可以是结果集以外的东西，比如其他的列，或者使用了 GROUP BY 时，还能用聚合函数。

3. 数据的增、删、改 {#3-数据的增删改}

插入(INSERT INTO) 也算用的多了 {#插入insert-into-也算用的多了}

语法：

|-------|---------------------------------------------------------------| | 1 2 3 | sql INSERT INTO <表名> (列名1, 列名2...) VALUES (值1, 值2...); |

或者也可以使用 SELECT 语句来替代 VALUES 子句，达到将 SELECT 到的结果集插入某表的效果。（但是不要用ORDER BY，因为结果是集，没有顺序，排序是徒劳无功的）插入时主键不能重复，否则会报错。（因此如果需要包含重复数据，一般都会定义一个自增的id字段）

删除(DELETE) {#删除delete}

清空表（危险操作，而且效率不如 TRUNCATE）：

|---|------------------------------| | 1 | sql DELETE FROM <表名>; |

条件删除：

|-----|-----------------------------------------| | 1 2 | sql DELETE FROM <表名> WHERE <条件>; |

因此使用DELETE时，一定要记得带WHERE，不然就好玩了。。

更新(UPDATE) {#更新update}

|-----------|------------------------------------------------------------------------------| | 1 2 3 4 5 | sql UPDATE <表名> SET <列名1> = <算术表达式1>, <列名2> = <表达式2>, ... WHERE <条件>; |

同 DELETE 一样，不带 WHERE 子句的 UPDATE 是很危险的。

四、DCL - 事务处理(MySQL) {#四dcl---事务处理mysql}

事务是一系列不可分割的数据库操作 ，也就是说，这一系列操作要么全部执行，要么全部不执行。如果执行过程中发生了问题（检查执行状态），可以通过执行 ROLLBACK 回滚到该事务执行前的状态。（注意并不会自动回滚）

|-------|---------------------------------------------------------| | 1 2 3 | mysql START TRANSACTION; -- do something COMMIT; |

START TRANSACTION: 标识事务的开始
COMMIT：提交事务。一旦提交，所执行过的操作就已成定论，恢复不了了。
ROLLBACK：事务回滚，只能回滚未 COMMIT 的 DML 操作 ！也就是说只能用在START TRANSACTION 和 COMMIT 之间，并且只能回滚 INSERT/UPDATE/DELETE。（回滚 SELECT 没啥意义）
SAVEPOINT <保留点> 和 ROLLBACK TO <保留点>：同样只能用在 START TRANSACTION 和COMMIT 之间，其优势在于，ROLLBACK TO 可以指定回滚到某特定保留点，更灵活，而ROLLBACK 只能回滚到事务开始前。

需要注意的有：

COMMIT 和 ROLLBACK 语句也是事务的结束，因此如果执行了 ROLLBACK，那它与 COMMIT 之间的内容会被跳过。（在这一点上，它相当于大多数 PL 的 return）
如果事务执行出现问题，问题行后面的所有语句都不会被执行！包括 COMMIT 和ROLLBACK！如果想用纯 SQL 实现事务原子性，必须使用存储过程检查执行状态！举例如下：

|-------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 | fallback CREATE PROCEDURE my_test() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK -- 检测到 SQLEXCEPTION 则 rollback，然后 exit START TRANSACTION INSERT INTO table_test VALUES(1, 'A') INSERT INTO table_test VALUES(1, 'B') -- 这里主键冲突，会触发 SQLEXCEPTION COMMIT END CALL my_test() |

或者在 PL 中通过异常处理执行 ROLLBACK 。（事务虽然中止了，但并未结束！所以仍然可以ROLLBACK 或者 COMMIT）

数据何时被提交到数据库 {#数据何时被提交到数据库}

显式提交：在事务中使用 COMMIT 提交数据操作被称为显式提交
隐式提交：非 DML 操作会被立即提交，也就是说这些语句本身就隐含了提交语义
自动提交：如果 AUTOCOMMIT 被设置为 ON，当前 session 中的 DML 语句会在执行后被自动提交（START TRANSACTION 内部的 DML 除外，在它内部必须显式 COMMIT）

所有的 DML 语句都是要显式提交的，MySQL session 的 AUTOCOMMIT 默认为 ON，所以 DML 会被自动提交。