SQL 基础笔记

贡献者： addis

本文处于草稿阶段。

本文参考 w3schools
以下用 SQLite 做测试。
SQL（Structured Query Language） 是操作数据库的语言
MySQL 是一个具体的管理数据库的软件，是 RDBMS（relational database management system）
开发者需要掌握 SQL 语言来使用 MySQL。不同的 RDBMS 基本都是用 SQL 语言，虽然有微小差异，但都兼容 SQL standard。
dump 操作常常用于备份数据库（而不是直接拷贝数据库文件）。数据库文件一般是加密的，但是 dump 不会加密。
一个 database 里面可以有许多 table，每个 field 就是 table 的一列，每个 record 就是一行
以下的命令可以在 DB BROWSER for SQLITE 里面实验（可以从 Ubuntu 商店下载），先创建数据库，然后选 Execute SQL，把命令或者 sql 里面的东西粘贴过去，执行即可。

1. SQL Basics

显示数据库 SHOW DATABASES;
用 -- 注释直到行末，用 /* */ 注释块
大部分操作都通过 SQL statement 完成，如 SELECT * FROM 表名;
命令可以随意换行，两个命令之间必须用 ; 隔开（即使不在一行）
SQL 关键字，表格名，列名都不区分大小写，一般关键词用大写
数据中的字符串用单引号，大部分数据库双引号也行（但不是标准），字符串区分大小写
一些最重要的关键字如
SELECT - 提取数据
UPDATE - 更新数据
DELETE - 删除数据
INSERT INTO - 添加数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 更改数据库
CREATE TABLE - 创建新表格
ALTER TABLE - 修改表格
DROP TABLE - 删除表格
CREATE INDEX - 新增 index (search key)
DROP INDEX - 删除 index

SELECT

SELECT 列1, 列2, ... FROM 表格名; 获取的数据表叫做 result-set。如果要选取所有列，用 * 即可 e.g. SELECT * FROM 表格名; 显示名为 表格名 的表格中全部内容
SELECT DISTINCT 列1, 列2, ... FROM 表格名; 仅列出（输出中）不完全相同的行，例如 SELECT DISTINCT Country FROM 表格名;
获取表格行数：SELECT COUNT(*) FROM 表格名
SELECT COUNT(DISTINCT Country) FROM 表格名; 显示表格中有多少不同的国家。（这个命令在 Microsoft Access 里面没用），除了 COUNT 还有 AVG 和 SUM
SELECT MIN(列) FROM 表格名;

WHERE

如果没有任何满足条件的行，那么返回一个空表格，不报错。
SELECT 列1, 列2, ... FROM 表格名 WHERE condition;
SELECT * FROM 表格名 WHERE Country='Mexico';
SELECT * FROM 表格名 WHERE CustomerID=1;
WHERE 后面可以是 =, >, <, >=, <=, <>（不等于，有时候 != 也行）, BETWEEN LIKE IN (在几个可能的值之中)
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
WHERE 后面的条件可以用 AND, OR, NOT
SELECT 列1, 列2, ... FROM 表格名 WHERE NOT condition;
SELECT * FROM 表格名 WHERE City='Berlin' OR City='München';
SELECT OCTET_LENGTH(列名) FROM 表名 WHERE ...; 可以返回 blob 的大小
SELECT EXISTS(SELECT 1 FROM 表格名 WHERE 条件); 可以检查表格中是否有满足条件的行。其中 SELECT 1 并不返回任意表格中的数据而是如果改行存在就返回 1（一行一列的数据）。而 SELECT EXISTS() 如果接收到任何返回值就返回 1，否则返回 0。
SELECT * FROM 表格名 WHERE 列名 LIKE '%字符%'; 可以搜索某列中是否包含 字符。

ORDER BY

用 ORDER BY 来进行排序 SELECT 列1, 列2, ... FROM 表格名 ORDER BY 列1, 列2, ... ASC|DESC;
SELECT * FROM 表格名 ORDER BY Country DESC;

GROUP BY

假如要从一个购买记录表中选中某个顾客最后一次购买每件商品的记录，用 SELECT MAX("time") as max_time, "order_id" FROM "orders" WHERE "customer"='david' GROUP BY "item" ORDER BY max_time DESC;

INSERT INTO

INSERT INTO 表格名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...); 插入行，如果每列都插入，那么 (列1, 列2,...) 可以省略
第一列总是数字（无论 field 叫做什么）？插入时可以指定，如果不指定会自动更新。不能指定已经存在的数字。
如果某列是 optional 的，那么插入一行时可以不输入这列的信息，这列的值就是 NULL（见下文）
SELECT 列 FROM 表格名 WHERE 列 IS NULL;
SELECT 列 FROM 表格名 WHERE 列 IS NOT NULL;

UPDATE

改变已经存在的 record. WHERE 可以选择一条或多条 record
如果不用 WHERE，所有行的指定列都会被更新
UPDATE 表格名 SET 列1 = 值1, ..., 列n = 值n WHERE condition;
例如 UPDATE 表格名 SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE 表格名 SET 列名 = 列名 + 1 WHERE ... 数值加 1。

DELETE

DELETE FROM 表格名 WHERE condition; 删除符合条件的行
DELETE FROM 表格名; 删除表格的所有数据（表格还在）。
e.g. DELETE FROM 表格名 WHERE CustomerName='Alfreds Futterkiste';

ALTER

给表格重命名 ALTER TABLE 旧名字 RENAME TO 新名字;
从表格中删除一列 ALTER TABLE 表名 DROP COLUMN 列名;
给表格一列重命名 ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名;

CREATE

CREATE TABLE Person (列1名称类型选项1 选项2 ... , 列2名称类型选项1 选项2 ... , ...)
强烈不建议用 SQL 命令的关键字作为表名列名等（大小写都不行），例如 add。因为为了区分关键字，在命令中需要使用 "add", [add] 或者 `add` 来 escape。普通列名则不需要 escape。
数据类型详见这里，注意一般来说 INT 就是 INTEGER。
sqlite 支持的类型为 NULL, INT, REAL, TEXT, BLOB
选项 PRIMARY KEY 可以使表格的某一列不出现重复的值且不能为 NULL。一个表格中只能有一个 PRIMARY KEY，但可以是多行。
列名称类型 UNIQUE 可以保证该列没有重复数据。
AUTO_INCREMENT 选项可以在新增一行时（如果不提供）自动比该列当前的最大值加 1，例如 ID 号。在 sqlite 中叫做 AUTOINCREMENT 且只能出现在 INT PRIMARY KEY AUTOINCREMENT 中。
如果要在一个 table 中的某列指定另一个 table 中的某个 PRIMARY KEY，那就使用 FOREIGN KEY (本表列名) REFERENCES 别的表名 (别的表的列名)。例如在 Orders 表中链接 Persons 表中名为 PersonID 的 primary key：
```
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderNumber INT NOT NULL,
    PersonID INT,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons (PersonID)
);
```
一般如果没有指定 NOT NULL 或者 NULL，那就默认 NULL，也就是该列是 optional 的，允许 NULL 值。

DROP

DROP TABLE 表名 删除表。

2. 关联两个表格

　　可以在条件前面加上 JOIN 另一个表的一行 ON 当前表.列 = 另一个表.列，然后在这个命令的任何地方，就可以使用 当前表.列 和 另一个表.列。注意如果 WHERE 中的条件满足但 JOIN 中的条件不满足，那么该条记录将不会输出也不会报错。

　　一个例子：

SELECT "column1", "table2"."column2", "table3"."column3",
FROM "table1"
JOIN "table2" ON "table2"."column1" = "table1"."column2"
JOIN "table3" ON "table2"."column1" = "table1"."column3"
WHERE ...;

代码 1：stat.sh

SELECT COUNT(1) 
FROM history 
JOIN entries ON history.entry = entries.id 
WHERE 
history.author IN ( 
    SELECT id 
    FROM authors 
    WHERE name LIKE 'john' 
) AND 
history.time >= '20220101' AND 
history.time <= '20230101' AND 
entries.type = 'Xiao'