关系型数据库

ALTER TABLE vendors
  ADD vend_phone CHAR(20);

ALTER TABLE vendors
 DROP COLUMN vend_hone;

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

CREATE INDEX prod_name_ind
    ON products (prod_name);

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

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);

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

'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;

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;

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

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

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

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

• 左外联接使用联接关键字左边的表为主表
• 右外联接使用联接关键字右边的表为主表

-- 以表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;

• 数据查询功能变得难以使用(例如, 不得不在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)
     , ...;

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 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;

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

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

-- 基于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)

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

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
);

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 ...;

• 一对一关系的查询永远只返回一行结果, 即使反向查询也是如此.
• 一对多关系的正向查询返回多行结果, 反向查询返回一行结果.
• 多对多关系的正向查询返回多行结果, 反向查询返回多行结果.
  多对多关系一定会需要一张交叉表来存储这种关系.

• 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地址拆到与任务分配者有关的表里.

• 尽可能的把表拆分成只包含一个主键列和一个属性列的形式.
  使用第四范式将在查询时大量使用多表联接.

SELECT EXISTS(SELECT * FROM table_name) AS record_exists

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

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

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