关系型数据库

SQL不区分大小写, 但通常关键字习惯使用大写以区分非关键字.
SQL语句的结尾要求分号, 否则查询将不会得到执行.
尽管存在着各种CRUD操作, 但所有SQL语句都被认为是一种"查询", 相关的操作被视作查询的副作用.

SQL被标准化以后, 每过几年就会发布新的版本以适应新的需求, 例如SQL:2016添加了JSON支持.

不要将关系型数据库的"表"当作面向对象的模型看待.
表只是构成最终表的一部分, 通过联接多个表, 我们将得到符合我们需求的最终数据表.

SQL是声明式语言, 开发人员编写的查询会被查询优化器以各种方式优化, 例如并列的条件语句在查询时会被重新排序.

SQL语言作为一种DSL抽象了数据库查询过程, 实际的查询严重依赖于查询计划器, 这使得查询本身变得神秘化.

有充足的理由相信查询计划器并不总是能够发现最佳的查询方法,
由于SQL的存在, 程序员常常因无力通过正规方式干涉数据库采用的查询方法, 被迫采取一些hack手段,
这些hack手段往往没有可持续性和可维护性方面的保证.

表名, 列名, 关键字根据不同的偏好以及DBMS的大小写敏感性, 大致分为三种风格:

-- 大写关键字, 小写表名, 小写列名(最流行)
SELECT * FROM the_table WHERE the_table_name = 'My table';
-- 大写关键字, 单词首字母大写表名, 小写列名
SELECT * FROM TheTable WHERE the_table_name = 'My table';
-- 小写关键字, 大写表名, 大写列名
select * from THE_TABLE where THE_TABLE_NAME = 'My table';

通常情况下, 表和列名约定成俗地使用Snake case, 且尽可能使用小写字母.
这同时也是最适合PostgreSQL的写法.

表名通常是单数形式, 除非单词没有单数形式.
因为表名实际上描述的是一种关系(考虑交叉表该如何命名), 而不是集合.

列名总是单数形式.
列名不应该与表同名.

尽可能少的直接用 id 作为表的主标识符, 而是用类似 user_id 的名称替代,
因为表是基于关系建模, 而不是基于对象建模, 在关联多个表时, 后一种名称可能会更易于使用.

存储过程的名字一定要包含动词.

name, names, ages 是保留字, 不应该被用于列名和表名.

_id 标识符, 主键
_status 状态
_total 总和
_num 数值
_name 名字
_seq 系列值
_date 日期
_tally 计数值
_size 大小
_addr 地址

  • 数据查询语言 DQL(Data Query Language): SELECT, WHERE, ORDER BY, GROUP BY, HAVING
  • 数据定义语言 DDL(Data Definition Language): CREATE, ALTER, DROP
  • 数据操作语言 DML(Data Manipulation Language): INSERT, UPDATE, DELETE
  • 事务处理语言 TPL(Transaction Process Language): COMMIT, ROLLBACK
  • 数据控制语言 DCL(Data Control Language): GRANT, REVOKE

CREATE DATABASE mydb; 创建一个名为mydb的数据库
DROP DATABASE mydb; 删除名为mydb的数据库

用于控制数据库的安全系统, 一般通过交互式的程序配置, 而不手动使用命令配置.

建模可以通过纸笔或纯文本文档这样的简单工具完成,
但也可以使用Erwin或Powerdesigner这样专门的实体关系(ER)建模工具.
尽管后者除了能够生成SQL代码以外并不比前者更有优势(加上DBMS之间的差异, 可能需要手动细化设置).

专业建模工具的建模流程:

  1. 1.
    创建一种名为CDM的概念模型, 标识了现实世界的实体关系.
  2. 2.
    将CDM转换成PDM(物理模型), 这是现实世界的实体关系反映到数据库表时的样子.
  3. 3.
    PDM转换成特定数据库的SQL脚本, 用于创建数据库表.
CREATE TABLE tablename ( column_name datatype [NULL|NOT NULL] [CONSTRAINTS] , column_name datatype [NULL|NOT NULL] [CONSTRAINTS] , ... );

不同DBMS的数据类型存在差异, 因此数据类型需以具体DBMS文档为准.
列具备三个属性: 名称, 数据类型, 约束.

CREATE TABLE products (
prod_id INT PRIMARY KEY
, prod_name CHAR(254) NOT NULL
, prod_price DECIMAL(8,2) NOT NULL
, prod_desc VARCHAR(1000) NULL
, prod_quantity INTEGER NOT NULL DEFAULT 0
, created_at DATE NOT NULL DEFAULT CURRENT_DATE
);

允许NULL值为大多数DBMS的默认设置, 但建议专门标出.
不同DBMS的获取日期的方式不同, 因此默认值的设置方法也不同, 上表created_at使用的是PostgreSQL获取系统日期的方式.

ALTER TABLE tablename ( ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS] , ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS] , ... );

对表结构进行更新.
理想状态下, 不应在表中包含数据时对表结构进行更新.

ALTER TABLE vendors
ADD vend_phone CHAR(20);

并非所有DBMS都支持删除列, 例如SQLite就不支持.

ALTER TABLE vendors
DROP COLUMN vend_hone;

修改列在不同的DBMS里有很大不同, 因此请以DBMS的文档为准.

  1. 1.
    构建一个新表
  2. 2.
    用 INSERT SELECT 复制旧表数据到新表
  3. 3.
    检查新表是否符合预期
  4. 4.
    重命名旧表
  5. 5.
    重命名新表(改为旧表的原名)
  6. 6.
    重新创建触发器, 存储过程, 索引, 外键
  7. 7.
    删除旧表

DROP TABLE 表名;

主键约束即PRIMARY KEY, 不可重复, 不可为NULL.

基于最佳实践, 主键应该是不可变的, 已删除的主键也不可被重用.
然而, 一些用例会使得违反最佳实践的情况出现,
比如将主键作为字符串枚举值使用时, 如需重命名枚举值, 则主键就会遭到更新
(这种情况下, 唯一约束实际上比主键更符合语义, 因此也可以用唯一约束替代).

自增列, 加在PRIMARY KEY约束之后, 表明此主键会自动增长.

在PostgreSQL里, 自增是一种数据类型, 名为SERIAL.

CREATE TABLE users (
id SERIAL PRIMARY KEY
name VARCHAR(10) NOT NULL
)

唯一约束即UNIQUE, 不可重复, 可为NULL(NULL不算重复).
可以用UNIQUE配合NOT NULL以阻止NULL值.

CREATE TABLE table_name (
...
, UNIQUE (column_name1, column_name2)
);

非空约束即NOT NULL.

默认值约束即DEFAULT, 需在DEFAULT后加上默认值.

外键与其他表有关, 一个外键列必须引用另一张表具有唯一约束的列.

CREATE TABLE orders (
order_num INTEGER NOT NULL PRIMARY KEY
, order_date DATETIME NOT NULL
, cust_id CHAR(10) NOT NULL REFERENCES customers(cust_id) -- 此为外键
)

当表存在引用关系时, 如需删除表, 必须先删除从表(引用表), 才能删除主表(被引用表).

有些DBMS支持为外键添加级联删除(cascading delete)的特性(通常为 ON DELETE CASCADE),
启用后删除外键所连接的表的记录时, 会将依赖此记录作为外键的记录一并删除.

外键字段为NULL值不会违反外键约束.

检查约束用于限制有效数据的范围.

CREATE TABLE order_items (
quantity INTEGER NOT NULL CHECK (quantity > 0)
);

引用完整性约束与多张表有关, 强制要求插入/更新数据时, 被约束的列的值需在另一张表里存在.
引用完整性约束写在创建表列清单的末尾.

CONSTRAINT 名称 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
CREATE TABLE specialities (
id INT PRIMARY KEY AUTO_INCREMENT
name VARCHAR(20) UNIQUE NOT NULL
)
CREATE TABLE subjects (
id INT PRIMARY KEY AUTO_INCREMENT
, special_id INT NOT NULL
, CONSTRAINT fk_subject_special_id
FOREIGN KEY (special_id)
REFERENCES specialities(id)
)

索引改善查询性能, 但增加了添加/更新/删除的成本(这些操作需要更新索引), 占用存储空间.

CREATE INDEX indexname ON tablename (column, ...);
CREATE INDEX prod_name_ind
ON products (prod_name);

仅供参考, 以具体DBMS为准.

数据类型 说明
CHAR 1~255个字符的定长字符串
NCHAR CHAR的多字节或Unicode字符
TEXT/LONG/MEMO/VARCHAR 变长字符串
NVARCHAR TEXT的多字节或Unicode字符
数据类型 说明
BIT 单个二进制值
DECIMAL/NUMERIC 定点或精度可变的浮点值(推荐用于存储浮点数, 固定精度不会出现精度问题)
FLOAT/NUMBER 浮点值(IEE754, 在数据库存储一些值时可能会出现精度问题, 例如59.95)
DOUBLE PRECISION/REAL 4字节浮点值(IEE754, 在数据库存储一些值时可能会出现精度问题, 例如59.95)
INT/INTEGER 4字节整数值(-2147483648~2147483647)
SMALLINT 2字节整数值(-32768~32767)
TINYINT 1字节整数值(0~255)
MONEY/CURRENCY 货币
数据类型 说明
DATE 日期
DATETIME/TIMESTAMP 日期时间
SMALLDATETIME 日期时间 精确到分
TIME 时间
数据类型 说明
BINARY 定长二进制
LONG RAW 变长二进制, 最多2GB
RAW 定长二进制, 最多255B
VARBINARY 变长二进制数据

通常不应该在数据库里存储二进制数据.

INSERT INTO tablename [(columns, ...)] VALUES (value, ...);
-- 基于列的顺序插入(不推荐)
INSERT INTO customers
VALUES ('Toy Land', 'New York')
, ('Land Toy', 'York New');
-- 匹配列的名称插入(推荐, 更安全)
INSERT INTO customers (cust_name, cust_city)
VALUES ('Toy Land', 'New York')
, ('Land Toy', 'York New');

在具名情况下, 插入的列可以不完整, 被省略的列需满足以下条件之一:

  • 该列被定义为允许NULL值
  • 该列具有默认值
INSERT INTO tablename [(columns, ...)] SELECT columns , ... FROM tablename , ... [WHERE ...];

查询并插入(从另一张表里查询结果, 将其作为数据插入此表):

INSERT INTO customers (cust_name, cust_city)
SELECT cust_name
, cust_city
FROM cust;

INSERT SELECT 并不关心列名是否匹配, 只在乎顺序.
如果 SELECT 返回多行, 每一行都会被用于 INSERT.

将一张表的内容复制到新表里(会在数据库里创建新的表)

SELECT *
INTO copy
FROM customers;

MariaDB, MySQL, Oracle, PostgreSQL, SQLite支持的另一种写法:

CREATE TABLE copy AS
SELECT *
FROM customers;
UPDATE tablename SET columnname = value , ... [WHERE ...];

更新表中的数据

UPDATE users
SET user_email = 'woshenmedoubuzhidao@blackglory.me'
, user_url = 'https://blackglory.me'
, user_other = NULL
WHERE user_name = 'BlackGlory'

如果省略WHERE子句, 会导致表中的所有行被修改.

UPDATE accounts
SET money = money + 1000
WHERE id = 1;
DELETE FROM tablename [WHERE ...];

删除表中的行

DELETE FROM users
WHERE user_name = 'BlackGlory'

如果省略WHERE子句, 会导致表中的所有行被删除.

当DBMS通过外键来实施表之间的关系时, 删除与外键相关的行会被DBMS阻止并抛出错误.

清空整张表, 效率比没有WHERE的DELETE高.
TRUNCATE TABLE 表名;

SELECT columnname , ... FROM tablename , ... [WHERE ...] [UNION ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...];

SELECT 列名 FROM 表名; 查询表, 多个列名以逗号分隔
SELECT * FROM 表名; 查询表内的所有列
SELECT employee_salary AS month_salary FROM employees; 查询表返回时, 替换结果的列名

SELECT DISTINCT manager_id FROM employees; 查询表返回时, 去除manager_id重复项
当有多个列名时, DISTINCT会作用于所有的列.

对于不同的DBMS, 可能有不同的形式:
SELECT prod_name FROM products LIMIT 5 查询前5项
SELECT prod_name FROM products LIMIT 5 OFFSET 5 查询5~10项
SELECT prod_name FROM products LIMIT 5, 5 查询5~10项

需要注意的是, 当OFFSET的值较大时, 性能会很差, 参见大表OFFSET.

SELECT employee_name, employee_salary FROM employees ORDER BY salary ASC;
查询表返回时, 以salary升序排序

SELECT employee_name, employee_salary FROM employees ORDER BY salary DESC;
查询表返回时, 以salary降序排序

ORDER BY 通常必须是 SELECT 语句的最后一条子句.
ORDER BY 可以按照列的先后顺序指定列( ORDER BY 1 指按第一列排序), 由于缺乏可维护性, 不应该使用.
升序(ASC)是默认的排序方式.

查询表返回时, 以salary降序排序, 以id升序排序.
在排序多个列时, 应该在每一列后写上顺序(如果忽略, 那一列会使用默认的ASC).

SELECT employee_id
, employee_jname
, employee_salary
FROM employees
ORDER BY employee_salary DESC
, employee_id ASC;

ORDER BY的排序不能保证稳定性, 需要手动指定一个唯一值来保证排序结果稳定.

NULL 值可能会影响排序, 因此需要在子句最后手动指定 NULL 的排序方式: NULLS LASTNULLS FIRST.

SELECT查询的结果是一张虚拟表, 因此可以在查询时计算出新的列.
也可以在不带有表的情况下直接用SELECT做计算, 例如 SELECT 3 * 2;.
在计算出新的列时, 尽可能用AS给新列一个名字, 这是最佳实践.

SELECT employee_salary * 12 FROM employees; 查询表返回时, 计算结果, 得出新列salary*12
SELECT empolyee_salary * 12 AS year_salary FROM employees; 查询表返回时, 计算结果, 得出新列年薪

用||拼接字段(DB2, Oracle, PostgreSQL, SQLite, Open Office Base)
SELECT vend_name || '(' || vend_country || ')' AS vend_title FROM vendors;

用Concat函数拼接字段(MySQL, MariaDB)
SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title FROM Vendors;

对列进行计算的函数

  • AVG() 返回某列的平均值
  • COUNT() 返回某列的行数(会忽略NULL行), COUNT(*) 表示计算所有行数(包括NULL行)
    COUNT可以与DISTINCT组合使用 COUNT(DISTINCT column).
  • MAX() 返回某列的最大值, 此函数会忽略NULL行
  • MIN() 返回某列的最小值, 此函数会忽略NULL行
  • SUM() 饭后某列的加总

聚集函数可以与DISTINCT配合使用(DISTINCT的反面是ALL, 这是默认行为, 所以不用手动写ALL), 以消除重复项
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products

按列中的数据进行分组, SELECT 用到的除聚合函数以外的每一列都必须在 GROUP BY 里列出
可以对多个列进行分组.
GROUP BY 必须在 WHERE 之后, ORDER BY 之前.

按vend_id分组, 并返回每个组里有多少项

SELECT vend_id
, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id

用HAVING查询分组(相当于作用于组的WHERE)

SELECT vend_id
, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

GROUP BY隐式地带有DISTINCT的功能:
当GROUP BY不使用任何聚合函数的时候, GROUP BY等同于DISTINCT.

SELECT employee_id, salary FROM employees WHERE salary > 10000; 条件查询
当条件是字符串时, 字符串值需使用单引号.
条件查询会按先后顺序进行搜索, 因此运算成本最低, 过滤内容最多的模式应该被放在最前面, 以提高查询性能.
条件查询中也可以使用字段计算.

运算符:

  • = 相等
  • !=, <> 不等
  • AND, OR, NOT 逻辑运算(AND操作符的优先级大于OR, 组合使用时尽可能加上括号以增强可读性)
  • >, <, >=, <= 大于小于
  • IS NULL 是空值(NULL和任何其他值用运算符计算的结果都是NULL, 因此需要专门的运算符用来判定NULL)
  • IS NOT NULL 非空值
SELECT empolyee_id
, salary
FROM empolyees
WHERE salary BETWEEN 6000 AND 10000;
SELECT empolyee_id
, salary
FROM empolyees
WHERE salary IN (6000, 7000, 8000, 9000, 10000);

_ 表示单个通配字符
% 表示多个(包括0个在内)通配字符)
[] 表示单字符的范围, 跟正则表达式类似, 如需匹配单字J或M, 则为 [JM], 也可以用^否定J或M, 则为 [^JM].

SELECT empolyee_id
, empolyee_name
FROM empolyees
WHERE name LIKE 'Black%';

SIMILAR TO是"SQL: 1999"引入的一个正则表达式运算, 其定位介于LIKE和DBMS的正则表达式功能之间.
和LIKE一样, SIMILAR TO仅在模式与整个字符串匹配时才成功.

该运算符基本上是没有意义的, 因为它通常会被DBMS重写为内部的正则表达式, 所以性能只会比正则表达式差.

'abc' SIMILAR TO 'abc' -- true
'abc' SIMILAR TO 'a' -- false
'abc' SIMILAR TO '%(b|d)%' -- true
'abc' SIMILAR TO '(b|c)%' -- false
SELECT empolyee_id
, salary
, CASE
WHEN salary >= 10000 THEN 'A'
WHEN salary >= 8000 AND salary < 10000 THEN 'B'
WHEN salary >= 6000 AND salary < 8000 THEN 'C'
WHEN salary >= 4000 AND salary < 6000 THEN 'D'
ELSE 'E'
END AS 'level'
FROM empolyees;

任何体面的查询优化器都会优化多个WHERE条件并找出最佳的查询方式, 并对WHERE条件的顺序进行重新排序,
因此WHERE条件的顺序 无关紧要.

子查询组合多个查询语句解决问题, 例如查询工资大于 Bruce 的行:

SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE first_name = 'Bruce');

子查询也可以在字段计算中使用:

SELECT cust_name
, cust_state
, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

子查询中的括号表示这是一个"集合", 与IN的括号是等同的, 不是"改变运算优先级".
当子查询得到的结果是 单列多行 时, 则需要通过ALL或ANY关键字来明确条件判断的方式:

SELECT *
FROM employees
WHERE salary > ALL (SELECT salary
FROM employees
WHERE first_name = 'Bruce');

在PostgreSQL里, 有 COALESCE 函数可以将多个值缩小至单个值, 亦可提供默认值.

当子查询得到的结果是多行多列时, 它只能作为一张临时表使用:

SELECT employee_id
, first_name
, salary
FROM (SELECT employee_id
, first_name
, salary
FROM employee
ORDER BY salary DESC) AS temp;

SQL 要求临时表必须有一个名字.

子查询的缺点是查询语句必须能够单独运行, 不能引用同一级别的其他表中的字段.
举例来说, 如果要在子查询里引用与子查询同级表的字段作为条件, 则查询语句会失败.
尽管通过一些方式可以绕过此限制, 但在可读性和性能方面都会有负面影响.

LATERAL子查询弥补子查询的上述缺点, 使用LATERAL子查询只需要在原来的子查询语句前加上LATERAL即可.

合并查询可以纵向拼接两个表的查询结果,
合并要求查询结果的列数相同(第一张表的列名会被使用),
且要求相同位置的列的数据类型兼容(可以隐式类型转换).

SELECT * FROM t1;
UNION
SELECT * FROM t2;

SELECT语句是以UNION分隔开的,
如果查询需使用ORDER BY子句, 则 ORDER BY必须位于最后一个SELECT后面, 且只允许一条ORDER BY.

默认情况下, UNION会自动去除重复的行, 要保留重复项, 则需要ALL关键字:

SELECT * FROM t1
UNION ALL
SELECT * FROM t2;

联接是关系型数据库伸缩性(scale)的体现, 通过联接表, 可以将多张表显示成一张新的虚拟表.

表别名即是在FROM后使用AS关键字给表添加别名,
这在联接表时可以将表名简化成单个字符, 以避免重复输入完整表名.

联接表可以合并多个表, 条件(ON)决定两张表里哪些行会被联接.
不满足联接条件的行将不会包含在结果里,
如果省略条件, 则会得到笛卡尔积(表A的每一行与表B的每一行形成联接, 即相乘).

SELECT *
FROM employees
INNER JOIN jobs
ON employees.job_id = jobs.job_id;

另一种写法更简单的内联接(非SQL规范, 但被很多DBMS实现):

SELECT *
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;

不推荐使用这种简便形式, 因为会真正的WHERE子句混淆.

自联接即一张表自己联接自己:

SELECT c1.cust_id
, c1.cust_name
, c1.cust_contact
FROM Customers AS c1
, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
-- 相当于以下子查询
SELECT cust_id
, cust_name
, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');

可以看出其实自联接的可读性不如子查询, 因为自联接把对同一张表的两次查询并在了一个WHERE里.
一些DBMS处理联接时的速度比子查询快很多, 这是自联接仍被使用的原因.

外联接和内联接的不同之处在于, 当外联接的表不存在对应行时, 会以NULL填充相关字段.
因此外联接查询总是 返回主表里的所有行, 所以外联接的 性能总是比内联接差.

外联接查询需要手动指定以其中的一张表为主表,
根据主表的不同, 分为左外联接(LEFT OUTER JOIN)和右外联接(RIGHT OUTER JOIN):

  • 左外联接使用联接关键字左边的表为主表
  • 右外联接使用联接关键字右边的表为主表
-- 以表a为主表, 联接表b, 表a里不与表b匹配的行将以NULL填充表b的列
SELECT *
FROM a
LEFT OUTER JOIN b
ON a.id = b.id;
-- 以表b为主表, 联接表a, 表b里不与表a匹配的行将以NULL填充表a的列
SELECT *
FROM a
RIGHT OUTER JOIN b
ON a.id = b.id;

当外联接多个表时, 需要注意给这些联接的表设定条件, 否则可能得到笛卡尔积.

全外联接与一般的外联接不同, 它同时以左右两张表的字段为基础返回结果, 因此能返回两张表里的所有行.
不是所有DBMS都支持全外联接.

-- 联接表a和表b, 表a里不与表b匹配的行将以NULL填充表b的列, 表b里不与表a匹配的行将以NULL填充表a的列
SELECT *
FROM a
FULL OUTER JOIN b
ON a.id = b.id;
  1. 1.
    SELECT
  2. 2.
    FROM
  3. 3.
    WHERE
  4. 4.
    GROUP BY
  5. 5.
    HAVING
  6. 6.
    ORDER BY
  7. 7.
    LIMIT
  1. 1.
    FROM
  2. 2.
    WHERE
  3. 3.
    GROUP BY
  4. 4.
    HAVING
  5. 5.
    SELECT
  6. 6.
    ORDER BY
  7. 7.
    LIMIT

视图是一种虚拟表, 它把复杂的 SELECT 查询封装成一个虚拟表, 以便重用和简化复杂的查询语句.
视图可以嵌套视图, 但需记住由于视图往往封装了复杂的查询, 嵌套视图可能导致大量的查询.

CREATE VIEW viewname AS SELECT columns , ... FROM tables , ... [WHERE ...] [GROUP BY ...] [HAVING ...];
CREATE VIEW product_customers AS
SELECT cust_name
, cust_contact
, prod_id
FROM customers, orders, order_items
WHERE customers.cust_id = orders.cust_id;

视图也可以使用计算字段, 以此增加视图的可读性.

如果视图已存在, 则会对该视图进行替换.

CREATE OR REPLACE VIEW product_customers AS
SELECT cust_name
, cust_contact
, prod_id
FROM customers, orders, order_items
WHERE customers.cust_id = orders.cust_id;

查询具体的DBMS, 通常是ALTER VIEW.

DROP VIEW 视图名;

存储过程相当于SQL里的函数, 执行存储过程相当于完成一系列操作.
存储过程在DBMS里会被编译和优化, 因此性能更高.

不同的DBMS使用不同的存储过程创建形式, 存储过程通常是不可移植的.
一些DBMS的存储过程支持SQL以外的语言.

单一职责原则认为, 数据库只应负责存储数据和保证数据完整性(通过约束和触发器), 存储过程是不必要的.
因此, 人们更倾向于使用程序语言执行SQL, 而不是调用存储过程.

CREATE PROCEDURE procedurename [parameters] [options] AS SQL statement;
EXECUTE AddNewProduct (
'JTS01'
, 'Stuffed Eiffel Tower'
, 6.49
, 'Plush stuffed toy with the text LaTour Eiffel in red white and blue'
);

触发器是特殊的存储过程, 会在特定的数据库活动发生时自动执行(例如INSERT, UPDATE, DELETE).
触发器只与特定的表关联, 不会对整个数据库生效.
根据DBMS的不同, 触发器的语法不同.
触发器的速度没有约束快, 因此当约束足够使用时, 应该优先使用约束.

常见用途:

  • 保证数据格式一致, 例如大小写转换
  • 记录关于某张表内容变动的日志
  • 复杂验证
  • 更新时间戳
  • 自动删除符合条件的行
  • 复杂的默认值

PostgreSQL的触发器:

CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE customers
SET cust_state = Upper(cust_state)
WHERE customers.cust_id = :OLD.cust_id -- :OLD指变动的那一行
END;

最小惊讶原则认为, 数据库不应该在插入/修改/删除时发生执行的SQL语句以外的副作用.
由于触发器很可能对应用程序和开发人员不透明(尤其是在很早的版本里定义的触发器),
嵌套的触发器会导致不透明的连续行为, 因此拒绝使用触发器是有道理的.

综上所述, 除非触发器的作用是与维持数据完整性 密切相关 的原子操作(完整性约束只能在数据库级别执行),
否则就不应使用, 任何不包含RAISE的触发器都应该被删除
(RAISE用于实现复杂的数据完整性检查, 抛出错误并放弃导致触发执行的语句).

触发器是数据库级的功能, 因此无法获得应用程序级的配置(例如环境变量).
这使得触发器不可能根据应用程序级的配置采取不同的行为,
除非应用程序将配置写入数据库, 而这种行为很可能是一种污染.

因此, 应尽可能使用应用程序级的功能, 而不是触发器, 以避免不必要的重写.

事务处理把多个操作写入缓冲区, 包装成一个原子操作, 最终要么都执行, 要么都不执行.

关系型数据库的事务满足ACID:

  • Atomicity 原子性
    多个操作被当作一个整体.
  • Consistency 一致性
    一个操作失败会导致回滚.
  • Isolation 隔离性
    事务与并行的事务之间存在隔离, 在一个事务里只能观察到另一个事务执行前的状态或执行完毕后的状态.
  • Durability 持久性
    事务对系统的影响是持久的, 而不是临时的.

需要完成一系列具有原子性的操作--全部成功或全部不成功--的时候.

原子操作隐式包含了事务.

事务隔离与锁是不同的, 根据不同的隔离级别, 数据库锁的情况也会不同.

这种事务隔离级别相当于无隔离性, 关系型数据库通常不会使用此事务隔离级别.
同时开启两个事务, 第一个事务对行进行了更新, 第二个事务可以读到这行更新.

第二个事务的读取得到的数据是脏数据, 因此该操作被称作"脏读(dirty read)".

定义(来自PostgreSQL文档):

一个事务读取了另一个并行未提交事务写入的数据.

为解决"脏读问题"而出现的事务隔离级别, 大多数数据库(例如PostgreSQL)默认使用此事务隔离级别.
同时开启两个事务, 第一个事务对行进行了更新, 第二个事务无法读到第一个事务的更新.
如果第一个事务在第二个事务读之前进行了提交, 则第二个事务可以读到第一个事务修改后的结果.

这个隔离级别的问题在于, 在同一个事务里读取同一行数据, 仍会因为其他事务的提交而出现不一致的读取结果,
这种现象被称作不可重复读.

定义(来自PostgreSQL文档):

一个事务重新读取之前读取过的数据, 发现该数据已经被另一个事务(在初始读之后提交)修改.

第一个事务更新表中的所有行, 第二个事务往表中插入了新行, 第二个事务提交.
第一个事务再次读取行时, 会发现有一行未被更新, 这就是在第二个事务里插入的新行,
这种情况被称作幻读.

定义(来自PostgreSQL文档):

一个事务重新执行一个返回符合一个搜索条件的行集合的查询, 发现满足条件的行集合因为另一个最近提交的事务而发生了改变.

我个人认为这个定义和"不可重复读"是重复的, 只是级别不同:

  • 不可重复读发生在具体的行上
  • 幻读发生在表的所有行上.

为解决"不可重复读"和"幻读"问题而出现的事务隔离级别, MySQL默认使用此事务隔离级别.
在这种隔离级别下, 同一个事务里的多次读取结果是一致的(根据第一次读取的时间决定), 不会受其他事务的更新影响.

这个隔离级别的问题在于, 尽管"不可重复读"和"幻读"问题被解决了,
但仍然存在"先开启但后提交"的事务覆写另一个事务的更新结果的问题,
因为"先开启但后提交"的事务读取到的结果可能是旧的, 这是不理想的, 因为先提交的事务等同于没有发生过.

在可重复读级别下, PostreSQL会抛出序列化失败错误, 应用程序必须中断当前事务并回滚, 从头开始重试整个事务.

定义(来自PostgreSQL文档):

成功提交一组事务的结果与这些事务所有可能的串行执行结果都不一致.

为解决"可重复读"的覆写问题而出现的事务隔离级别, 也是最高的隔离级别.
在该级别下, 开启事务会导致整个数据表被锁定,
其他的事务都需要排队等待此事务被提交, 相当于没有并发.

数据库生成一个对事务一致的版本快照, 事务处理时读操作只能看到该快照.
事务的写操作提交时, 仅当事务写入的目标在快照拍摄后没有被其他事务更新时, 事务提交才会成功.

一些DMBS实现了快照隔离, 采用它的原因在于它比可序列化级别提供更好的性能.
实践中的快照隔离是在多版本并发控制(MVCC)里实现的, 每次写入对象时都会生成一个新版本的对象.

快照隔离存在一种异常情况, 即两个事务的写入之间有某种隐含联系, 但又不处于同一个写入对象.
举例来说, 帐户A和帐户B的余额各为100元, 帐户允许负值, 但两个帐户相加不能为负值.
现在同时发生的事务A和事务B分别对帐户A和帐户B减去200元,
在可序列化级别, 事务A使帐户A的余额变为-100元, 事务B提交失败, 因为帐户A+帐户B已经为0.
在快照隔离级别, 事务A使帐户A的余额变为-100元, 事务B使帐户B的余额变为-100元,
虽然两个事务都成功, 但不是我们想要的结果.

不同DBMS的事务开始操作不同, 请查阅相关文档.
在事务执行期间, 客户端观察到的数据库(用SELECT语句查询得到的结果)是临时且具备隔离性的.

ROLLBACK [ TO savepointname]; -- 或 ROLLBACK TRANSACTION;

在事务开始后, 撤销 SQL 语句, 撤回到事务开始之前.
回滚操作需要客户端在发现错误后手动执行.
ROLLBACK;

COMMIT [TRANSACTION];

一般SQL语句会隐式提交(implicit commit), 但在事务里, 需要显式提交.
COMMIT;

该功能用于部分回滚, 即回滚到保存点的位置, 而不是回滚到事务开始.
DBMS会有相应的ROLLBACK语法来回滚到保存点.

游标提供一种可以滚动浏览检索结果的功能.
游标通常是被最终用户使用, 而不是客户端使用(即服务器后端), 因此开发人员通常不会使用此特性.
不同DBMS的游标也有不同的语法.

为了避免修改表结构, 人们可能会用一个逗号分隔的文本列保存列表, 文本可以是具体的值, 也可以是其他表的主键.

缺点:

  • 数据查询功能变得难以使用(例如, 不得不在SQL里使用正则表达式进行匹配)
  • 数据类型和格式无法得到保证, 因为只能使用文本.

直接创建多个列来存储多值属性存在诸多缺点:

  • 列的数量是有限的
  • 查询时需要重复给每个列指定条件, 非常麻烦
  • 更新时需要先找出空的列, 导致操作不原子化, 可能引起并发问题
  • 无法阻止重复数据

创建一张专门的交叉表(也称为映射表/联合表/多对多表)以一对一储存这种关系.
由于索引的存在, 这种交叉表事实上查询性能很高, 同时具有外键的引用完整性验证功能.

CREATE TABLE contacts (
product_id BIGINT UNSIGNED NOT NULL
, account_id BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (product_id, account_id)
, FOREIGN KEY (product_id) REFERENCES products(product_id)
, FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
INSERT INTO contacts (product_id, account_id)
VALUES (123, 12)
, (123, 34)
, (345, 23)
, ...;

本质上和交叉表一样, 只是关联的列不是外键, 而是具体值.
这也被称为"将多列转换为多行".

注意, 除非数据库本身支持用GraphQL作为查询语言,
否则GraphQL并不能帮助服务器查询树形结构, 因为反映到SQL查询时仍然会出现N+1个查询.

NoSQL数据库也无法处理无限深度的树形结构.
该问题事实上的最佳解决方案应该是使用图数据库.

当我们需要存储树形结构(例如用户评论)时, 人们可能会下意识地将数据以邻接表的形式进行存储,
这意味着每行数据都作为一个节点存在, 且指向一个父节点.

该模式的缺陷在于, 传统SELECT每次查询只能查询到节点的直接后代, 无法查询一个节点的所有后代.
如果用联接查询更多层次的后代, 很容易遇上SQL联接次数的限制, 而且SQL的可读性也不太好.

SELECT c1.*, c2.*, c3.*, c4.*
FROM comments AS c1 -- 1st level
LEFT OUTER JOIN comments AS c2
ON c2.parent_id = c1.comment_id -- 2st level
LEFT OUTER JOIN comments AS c3
ON c3.parent_id = c2.comment_id -- 3rd level
LEFT OUTER JOIN comments AS c4
ON c4.parent_id = c3.comment_id -- 4th level

实际上, 可以通过WITH子句实现递归查询(SQL-99标准, 被PostgreSQL等数据库支持):

WITH comment_tree (comment_id, bug_id, parent_id, author, comment, depth)
AS (
SELECT *, 0 AS depth FROM comments
WHERE parent_id IS NULL
UNION ALL
SELECT c.*, ct.depth+1 AS depth
FROM comment_tree AS ct
JOIN comments AS c
ON (ct.comment_id = c.parent_id)
)
SELECT * FROM comment_tree WHERE bug_id = 1234;

该方案把父节点列换成了从根节点开始的绝对路径.
因此, 可以通过LIKE快速查找出节点的子节点.

SELECT *
FROM comments AS c
WHERE '1/4/6/7/' LIKE c.path || '%';

此方案的缺点是, 虽然查找方便, 但维护路径字符串较为麻烦.
路径字符串本身也存在长度限制, LIKE的查询开销也不小.

该方案创建了一张新的交叉表, 用来存储祖先节点与后代节点的关系.
每一个祖先与后代都会有一条记录, 因此根节点也会与最末端的节点有一条记录.
原来的表不再需要记录父节点.
此方案可以快速查找出节点的子节点, 因为所有祖先与后代的关系都已经写在交叉表里了.

此方案的缺点是, 使用了空间换时间的做法, 越是深层的节点, 在交叉表就有越多记录.

主键总应该是自增id和独立于域模型之外吗? 这可能是错的, 一些人认为代理键打破了第三范式.
通常来说, 如果模型已经存在具有唯一性的属性(自然键), 就没有必要使用伪主键, 因为自然键提供更好的人类可读性.
由于键只是DBMS提供的一种抽象, 自然键理应被优化到接近代理键的性能.

在DBMS不优化自然键的情况下, 有以下原因放弃使用自然键:

  • 自然键的类型需要较大的空间来存储.
  • 关心类型的比较性能, 例如字符串比较显然会比整数比较慢.

另一件需要注意的事是, 确保自然键的不可变性:
在未来的任何时刻都不应该更新自然键, 因为有级联更新这个特性就更新自然键是绝对错误的行为.

总是使用名为id的伪主键会使得我们无法使用USING简化多表联接查询, 因为每张表的id注定具有不同功能.
如果我们使用属性作为主键, 就可以直接用USING:

-- 基于bug_id的多表联接查询
SELECT *
FROM bugs AS b
JOIN bugs_products AS bp
ON (b.bug_id = bp.bug_id); -- 两张表都用了bug_id这个属性, 在一张表里是主键, 一张表里是外键
-- 用USING简化后
SELECT *
FROM bugs
JOIN bugs_products
USING (bug_id);

尤其是在使用交叉表的时候, 应该直接用组合键作为主键.

CREATE TABLE bugs_products (
bug_id BIGINT UNSIGNED NOT NULL
, product_id BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (bug_id, product_id) -- 组合键
, FOREIGN KEY (bug_id) REFERENCES bugs(bug_id)
, FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO bugs_products (bug_id, product_id)
VALUES (1234, 1)
, (1234, 2)
, (1234, 3)

应该尽可能使用关系数据库的引用完整性约束, 以避免出现预期以外的数据(由应用程序保证引用完整性是不安全的).
没有引用完整性约束的关系数据库价值很低, 还不如直接用NoSQL.
NoSQL同样依赖编写得正确无误的应用程序保证引用完整性, 在使用和功能层面上自由度还更高.

人们认为外键约束导致相关的列在更新时变得麻烦,
例如删除一条被其他记录依赖的记录必须删除相关的子记录, 否则就会遇到外键约束错误.

使用级联更新可以让外键约束导致的需要执行多条SQL语句的麻烦消失, 因为数据库会自动更新或删除相关的记录.

有时, 一些数据模型的属性是易变甚至是随意的, 因此不可能将它们作为表的列进行存储.

这种需求其实就是文档数据库等NoSQL的使用场景.

这种模式用"实体"列指向相关表的主键, 用"属性"保存扩展属性的名称, 用"值"保存扩展属性的值.
扩展属性表的主键是"实体"与"属性"的组合键.

这种模式的缺点是:

  • 使得查询语句变麻烦
  • 扩展属性不具备数据库约束功能(例如引用完整性, 值范围检查)
  • 无法使用SQL数据类型(因此无法保证类型统一, 例如日期)

这种解决方案通过将扩展属性标准化来避免EAV模型的种种问题.
实现标准化的方式有很多种, 例如将公共属性和扩展属性合并, 单独分裂成不同类型的实体;
或者将扩展属性分类为不同的表, 在这些表里用外键引用公共表的主键.

事实标准化需要稳定的扩展属性, 因此不适用于那些随意的属性.

举例来说, 一个comments表可以记录bugs表或feature_requests表里的评论
(姑且不论bugs和feature_request分成两张表是否合理).
因此, comments表应该有bugs表或feature_requests表的外键, 这时就出现了需求:
外键要么是bugs表, 要么是feature_requests表, 但我们无法在SQL里定义这种外键.

多态关联没有使用真正的外键约束,
而是用一个issue_type列表示"外键"对应的表(也称为多态列), 用issue_id列表示"外键"对应的主键.

这个反模式类似于EAV模型.
缺点很明显, 没有外键约束, 因此失去使用关系数据库的优势.

使用两张交叉表表示comments表与bugs表, 及comments表与feature_requests表的关系.
这样comments表就不需要直接引用bugs表或feature_requests表, 也就不存在多态关联问题.

该方案的缺点是, 一个comments表的记录存在同时与bugs表和feature_requests表存在关联的可能性.

该方案将bugs表和feature_requests表的公共部分提取成一个基类表issues, 然后将comments与issues表关联.

该方案没有缺点, 有效利用了数据库的引用完整性功能.

CREATE TABLE bugs (
-- ...
status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED'))
);

缺点:

  • 添加新枚举时需要修改表结构.
  • 程序无法通过查询数据库获取枚举值列表, 因此需要在程序里单独定义一份枚举值列表.
CREATE TABLE bug_status (
status VARCHAR(20) PRIMARY KEY
);
INSERT INTO bug_status (status)
VALUES ('NEW')
, ('IN PROGRESS')
, ('FIXED');
CREATE TABLE bugs (
--- ...
status VARCHAR(20)
, FOREIGN KEY (status) REFERENCES bug_status(status) ON UPDATE CASCADE
);

如果连接的外键是一个id而不是具体值, 那么 ON UPDATE CASCADE 是没有意义的,
因为外键连接的是一个主键, 基于最佳实践, 主键不应该更新.

一些DBMS可以创建自定义的枚举数据类型, 会比单独创建表要更容易维护约束.
然而, 枚举数据类型的值虽然容易添加, 但通常不会像表的记录那样容易删除.

幽灵文件指的是在数据库引用了文件系统上的文件, 但由于种种原因, 文件系统上的文件丢失, 导致引用不再可用.

将文件直接存储在数据库上, 对于引用完整性和事务支持方面来说是有意义且理想的.

问题的根源在于用数据库存储二进制文件有以下问题:

  • 往数据库存储二进制比较昂贵, 性能也较差
  • 很多现有的文件处理工具只适合用于文件系统
  • 数据库备份变得巨大

对一些数据库来说, 可能支持具有引用完整性和事务支持的二进制引用(例如SQL Server的FILESTREAM).

在一个PostgreSQL 13案例中, 此优化能将查询的性能提升16倍.

原始查询:

SELECT ...
FROM tbl
INNER JOIN ...
LEFT OUTER JOIN ...
ORDER BY ...
LIMIT ... OFFSET ...;

变换成:

SELECT ...
FROM tbl
INNER JOIN ...
LEFT OUTER JOIN ...
WHERE id in (SELECT id
FROM tbl
ORDER BY ...
LIMIT ... OFFSET ...)
ORDER BY ...;

在PostgreSQL 13用EXPLAIN ANALYZE可以发现原始查询在带有联接时无法利用索引, 而变换后的查询可以.
这可能是因为原始查询在联接表时混入了太多条件, 导致PostgreSQL 13选择了错误的查询计划.

在PostgreSQL里, 使用子查询改变查询计划是一个"技巧":
https://www.postgresql.org/docs/13/explicit-joins.html

数据库中的行是没有顺序的, 因此数据库按顺序查找需要先排序所有行, 性能会很差.
为行添加序号可以解决此问题, 因此先进行排序, 然后建立带有序号的缓存表.
之后就可以靠WHERE子句过滤掉不需要的值, 排序的行数量会少很多.

此方案的问题在于它假定WHERE子句的条件是固定的, 因此行的序号都是连续的.
一旦需要动态的WHERE条件, 就会有一些行被排除, 从而破坏序号的连续性, 无法使用WHERE模拟OFFSET.

该方案是分页专用的, 它是普通序号方案的另一个版本.
通过记录上一页最后一个项目的序号, 可以用WHERE子句过滤掉低于和等于此序号的行.
该方案的优点在于, 不需要依赖序号的连续性,
但为了记录上一页最后一个项目的序号, 有必要需要建立一个缓存系统.

当OFFSET值很大时, 可以建立相反顺序的缓存表(例如实例化视图)再进行查询.

此解决方案只缓解了末端部分行的查询性能.
如果表特别大, 则位于表中间的行查询起来性能依旧会很差.

SQL Query Builder是介于手写SQL和ORM中间的模式.
它让开发者可以用编程语言的语法编写SQL代码, 因此SQL Query Builder生成的代码是可预测的.

它的最主要优点是消除了手写SQL时产生的重复代码, 且在生成动态查询时, 比手动拼接字符串要容易.

DAO是对数据库表访问的封装: 将一张表的INSERT, UPDATE, DELETE, SELECT等常规操作封装成易于使用的类方法.

ORM分为两种:

  • 自己封装的ORM, 仅仅用来把SQL结果集合封装成结构体以供编程语言使用.
    像JavaScript这样的动态语言很可能不需要ORM, 因为它们不需要手动将结果集映射到对象.
  • 使用第三方ORM框架, 这种框架很可能集成了DAO部分.

ORM框架的问题:

  • 使用ORM框架容易导致Smart UI模式, 进而导致代码的耦合性增加(解决方案: 封装成领域模型)
  • 一些ORM框架的代码可读性可能还不如直接使用SQL(解决方案: 使用SQL)

ORM框架有两种常见的模式/风格.

这种模式将DAO方法直接嵌入在ORM里, 不符合单一职责原则, 且实例方法与静态方法容易造成混乱.

// example how to save AR entity
const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.isActive = true;
await user.save();
// example how to remove AR entity
await user.remove();
// example how to load AR entities
const users = await User.find({ skip: 2, take: 5 });
const newUsers = await User.find({ isActive: true });
const timber = await User.findOne({ firstName: "Timber", lastName: "Saw" });

这种模式的DAO和ORM是分离的, 所有DAO方法都放在Repository里, 符合领域驱动设计(DDD)的模型.

const userRepository = connection.getRepository(User);
// example how to save DM entity
const user = new User();
user.firstName = "Timber";
user.lastName = "Saw";
user.isActive = true;
await userRepository.save(user);
// example how to remove DM entity
await userRepository.remove(user);
// example how to load DM entities
const users = await userRepository.find({ skip: 2, take: 5 });
const newUsers = await userRepository.find({ isActive: true });
const timber = await userRepository.findOne({ firstName: "Timber", lastName: "Saw" });

这些关系的本质区别在于相关查询的返回结果的数量:

  • 一对一关系的查询永远只返回一行结果, 即使反向查询也是如此.
  • 一对多关系的正向查询返回多行结果, 反向查询返回一行结果.
  • 多对多关系的正向查询返回多行结果, 反向查询返回多行结果.
    多对多关系一定会需要一张交叉表来存储这种关系.
  • ORM框架用对象建模替代了关系建模.
    这种说法有一定道理, 但会让人感觉它们只有学术意义, 因为 现实世界的数据库里到处都是对象建模.
  • ORM框架没有使用存储过程等数据库特性.
    为了避免供应商锁定, 不使用存储过程是很正常的, 此外存储过程并不一定比编程语言容易编写.
  • ORM框架实际上是ORM和DAO的合并.
    显然, 大多数ORM框架都包含DAO.
  • ORM框架经常生成愚蠢, 缓慢和难以理解的SQL.
    这取决于特定的ORM框架, 不可否认生成的SQL会比手写的性能更差,
    相对的, ORM产生的查询更具有可缓存性(ORM框架还知道应该在什么时候更新缓存).
  • ORM框架将具体实现变得极为不透明, 由于这种不透明性, 使用ORM框架的开发人员更容易写出性能低下的代码.
    一些ORM框架具有令人感到意外的自以为是的决策.

目标:

  • 以一种我们能够理解的方式表达这个世界中的事务
  • 减少数据的冗余存储, 防止异常或不一致的数据
  • 支持完整性约束
  • 行之间没有上下顺序
  • 列之间没有左右顺序
  • 重复行是不允许的
  • 每一列只有一种类型, 每一行只有一个值
  • 行没有隐藏组件
  • 表必须是一个关系
  • 表必须没有重复组合
    例如:
    • 多个列里不能有来自同一个域的值(tag1, tag2, tag3...)
    • 同一列里不能有多个值("tag1,tag2,tag3")
  • 使用主键与外键建立表与表之间的引用, 避免在多张表里使用重复的数据
  • 表只含有与表自身相关的属性
    例如, issues表不应该同时记录任务的分配者和分配者的Email.
    因为Email是与issue无关的, 只与任务分配者有关, 因此应该把Email地址拆到与任务分配者有关的表里.
  • 尽可能的把表拆分成只包含一个主键列和一个属性列的形式.
    使用第四范式将在查询时大量使用多表联接.
  • SQL变得繁琐.
  • 表的数量太多, 带来额外的认知负担.
  • 多表联接在性能上不如单表查询.

注意: SQLite的单个表查询只能利用一个索引, 因此很可能不适用于以下概念.

  • 主键不需要再定义索引, 因为主键一般会自带内部索引.
  • 唯一值不需要再定义索引, 因为唯一值一般会自带内部索引.
  • 索引值应该尽可能少在表里重复出现(最好是唯一的), 这样的索引在性能方面才有意义.
  • 根据查询执行计划(QEP)找出需要优化的查询, 使用 EXPLAIN 语句.
  • 索引在平衡时效率最高, 在插入, 更新, 删除操作后, 索引会逐渐变得不平衡.
    因此要么让索引能够定期自动平衡, 要么就应该定期手动重建索引.

全文搜索请使用DBMS内建的全文搜索引擎或Apache Solr等全文搜索引擎, 而不是使用LIKE或正则表达式.
LIKE和正则表达式的缺点是无法利用索引, 所以需要遍历整个表, 记录越多则性能越差.

全文搜索引擎使用了基于词汇的倒排索引(inverted index, 也称为反向索引), 因此效率更高.

不要执着于只用一条SQL语句, 使用多个简单的查询有时更好.
复杂的查询更难维护, 更容易写出意大利面条式的SQL语句.
我们可以用UNION来连接起多个简单的查询结果.

有两种方法:

这三种关键字只是不同的DBMS用于指定返回记录数的子句.

MySQL, SQLite支持LIMIT
SQL Server支持TOP
Oracle支持ROWNUM

SQLite支持EXISTS运算符, 可用于判断子查询是否返回任何行.
亦存在NOT EXISTS用于判断不存在.

SELECT EXISTS(SELECT * FROM table_name) AS record_exists

使用加盐的哈希值保存密码, 加盐的目的是防止攻击者在得到哈希后通过字典破解出密码.
为了避免攻击者专门针对盐值生成字典, 盐值必须是随机的.

CREATE TABLE accounts (
account_id SERIAL PRIMARY KEY
, account_name VARCHAR(20)
, email VARCHAR(100) NOT NULL
, password_hash CHAR(32) NOT NULL
, salt BINARY(8) NOT NULL
);
INSERT INTO accounts (account_id, account_name, email, password_hash, salt)
VALUES (123, 'billkarwin', 'bill@example.com', SHA2('xyzzy' || '-0xT!sp9', '-0xT!sp9');
SELECT (password_hash = SHA2('xyzzy' || salt)) AS password_matches
FROM accounts
WHERE account_id = 123;

如果想要SQL查询不包含密码明文, 则需要先用SELECT获取盐值, 然后在应用程序里完成哈希, 再进行匹配查询.

在SQL里使用占位符, 将参数直接作为数据发送给数据库处理, 该方法可以有效避免SQL注入.

$stmt = $pdo->prepare("SELECT * FROM projects WHERE project_name = ?");
$params = array($_REQUEST["name"]);
$stmt->execute($params);

该方法的缺陷是DBMS可能只支持参数占位符, 对于集合, 表名, 列名无法使用占位符.

用正则表达式等方法严格过滤输入内容, 比转义输入内容要有效和安全得多.

为每个版本的数据库Schema提供与上一个版本相关的升级和降级(回滚)脚本, 并且将它们保存在代码版本控制系统里.
如果升级和降级是在编程语言里完成的, 则通常将相关函数命名为up和down, 一些ORM框架也会提供迁移功能.

在生产环境里, 每一次数据库升级前都必须确保数据库已经备份.

升级脚本应该被视作一种补丁, 每个脚本拥有自己的唯一标识符(作为文件名前缀), 脚本统一保存在migrations文件夹.
在数据库里建立一个changelog表, 记录当前数据库打上补丁的唯一标识符, 打补丁的时间, 操作人员, 补丁的名称.

使用升级/回滚脚本, 或Flyway这样的Schema迁移方案.

Flyway可以通过Docker支持Schema版本化, 脚本由SQL语言实现, 但撤销迁移是专业版才提供的功能.
由于版本迁移的实际原理很简单(只是比照数据库里的版本号调用对应的脚本), 建议自己实现, 以避免供应商锁定.

向后兼容的操作可以直接升级:

  • 添加表或视图
  • 添加列
  • 删除程序未使用的列

向后不兼容的操作需按以下方式升级, 拿重命名列举例:

  1. 1.
    创建新列
  2. 2.
    复制旧列的数据到新列
    这是最难的一步, 因为旧程序仍在运行, 会有新的数据写入到旧的列里.
    解决方法: 用数据库触发器自动复制旧列的写入到新列.
  3. 3.
    将程序升级至新版本
  4. 4.
    删除旧列的数据.

对数据库的测试属于集成测试, 对数据库进行mock和stub的单元测试是反模式.

用docker-compose启动真实的测试数据库(保证测试环境中的数据库与生产环境一致).
如果可以测试DAO, 则应该测试DAO, 在更高层的组件里测试数据库会引入额外的脆弱性.

确保jest开启 --runInBand, 以免并行运行数据库测试.

通过配置让数据库只在内存中运行(数据不写入磁盘)来保证测试速度:

docker run --rm -p 5432:5432 postgres:11.1 -c fsync=off

对于不支持只在内存中运行的数据库, 可以将其挂载到基于RAM的文件系统:

docker --rm \
--mount type=tmpfs,destination=/var/lib/postgresql/data \
-p 5432:5432 postgres:11.1

MySQL的元查询通常都是以函数的形式呈现.

SHOW DATABASES; 查看全部数据库

CREATE DATABASE mydb CHARACTER SET gbk; 在创建mydb数据库的同时设置编码字符集为gbk
ALTER DATABASE mydb CHARACTER SET utf8; 将mydb数据库的编码字符集设置为utf8

CREATE DATABASE IF NOT EXISTS mydb; 在mydb不存在时, 创建mydb数据库

SELECT DATABASE(); 查看当前数据库
USE mydb; 使用mydb数据库

SELECT SYSDATE(); 当前系统时间
SELECT CURTIME(); 当前系统日期