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

1. 数据库的创建和删除

创建数据库

CREATE DATABASE <数据库名称>;

DROP DATABASE <数据库名称>;

2. 创建表：

关系表的设计

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

语句：

CREATE TABLE <表名>
(
    <列名1> <数据类型> <该列所需约束>,
    <列名2> <数据类型> <该列所需约束>,
    <列名3> <数据类型> <该列所需约束>,
    ...
    <该表的约束1>, <该表的约束2>...
);

举例：

CREATE TABLE `persons`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` CHAR(20) NOT NULL, 
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

字段类型（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. 删除表和更新表定义

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

DROP TABLE <表名>;

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

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

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

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

删除列定义：

ALTER TABLE <表名> DROP COLUMN <列名>;

三、DML

万恶之源 NULL

1. 查询（重点）

基本语句：

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. 聚合与排序（重点）

聚合函数

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

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 会将 NULL 作为一组特定数据，显示为空。

聚合对SELECT子句的限制

首先要理解的是：

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

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

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

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

对聚合结果进行过滤(HAVING)

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

NOTE：

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

排序(ORDER BY)

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

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. 数据的增、删、改

插入(INSERT INTO) 也算用的多了

语法：

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

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

删除(DELETE)

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

DELETE FROM <表名>;

条件删除：

DELETE FROM <表名>
    WHERE <条件>;

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

更新(UPDATE)

UPDATE <表名>
    SET <列名1> = <算术表达式1>,
        <列名2> = <表达式2>,
        ...
    WHERE <条件>;

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

四、DCL - 事务处理(MySQL)

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

START TRANSACTION;
    -- do somthing
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 实现事务原子性，必须使用存储过程检查执行状态！举例如下：

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 会被自动提交。

P.S. 许多语言的数据库 API 会定义自己的事务操作，不一定与这里一致。