SQL 手册1. 信息系统，数据和信息现代世界的中心已经完全的围绕着信息和数据了，那么信息和数据不同的地方在哪里呢？

本文翻译自 The SQL Handbook

主要是为了学英语，有错漏感谢指正，respect！

1. 信息系统，数据和信息

现代世界的中心已经完全的围绕着信息和数据了，那么信息和数据不同的地方在哪里呢？

数据是单一的知识，他本身不具有任何意义，我们无法从中提取意义，从而不能对它进行深入分析。

信息是我们可以和数据连接的东西，从而产生了意义，可以被分析。

举个例子，36 是一个数据，知道 36 是小明的年龄则是信息。

根据这个例子：数据所代表的年龄的信息是信息系统的关键。

2. 什么是数据库（database）？什么是数据库管理系统（DBMS）？

数据库 是精心组织到系统中的信息集合。

数据库管理系统 是在计算机系统中允许组织数据并表示信息系统所必需的信息的技术。它往往作为一种封装数据库数据的软件出现，提供一种存储、检索、编辑、持久化和管理数据库的方法。

我们要求数据库管理系统一定要高效、私密和安全地存储数据，能处理大量的数据。

3. 你是否真的需要一个数据库呢？

并不是你的每一个项目都需要一个数据库。

很多时候你甚至不需要存储数据。

就算你需要存储数据，你也有不同的解决方法。

技术应用中，没有银弹

计算机提供很多不同的方式来存储数据，最明显的是存在文件里。

一个通用的例子是一个网站。一些网站利用数据库存储数据（比如 WordPress），一些则不是（比如静态网站）。

不使用数据库通常意味着更简单的部署到托管服务，因为首先你就不需要使用和管理数据库。

但是当需要处理大量数据时，从长远来看，数据库绝对是简化生活的好方法。

一如既往，这依然是复杂性与便利性的权衡。

4. 关系型数据库

我们有许多不同的数据库管理系统。

在过去，计算机专家们实验了很多不同的选项，其中一种非常受到欢迎：关系型数据库管理系统( Relational Database Management System, RDBMS)。为了简单，我们通常称呼它为关系型数据库**。

你可能听过 PostgreSQL, MySQL, Oracle, SQLite, MariaDB, SQL Server，这系统都是关系型数据库。

注意这些数据库名称里都带有 SQL ，它们也被称为SQL 数据库，对比于 NoSQL 数据库（比如 MongoDB 或者 DynamoDB ），这是不使用关系模型而且不使用 SQL 作为语言的 DBMS。

关系型数据库使用两个简单的概念来组织数据： 表（table） 和关系。这是为了让它们使用起来符合直觉，因为我们常常使用表格去组织分析数据，举个例子，比如你的 Excel 表格。

乍一看，关系型数据库都很类似。数据被存在一个或者多个表里。

一个表是一个集合，由行（row）与列（column）组成。

| Name   | Age |
| ------ | --- |
| Tony   | 36  |
| Rose   | 28  |
| Juliet | 16  |

每个表格包含着至少一列，列表示一个特定的数据类型，比如字符串或者数字等。

表的集合和有关列的所有规则（比如存储的数据类型）被称为模式（schema）。

每个表都可以定义对每行包含的数据的约束，例如，我们可以说某列的值不能为空。

表之间可以互相引用，形成关系。

比如我们可以这么说：car 表中 id:1 的行是 user 表里的id:2 的用户创造的。用这种方式，我们可以互相链接数据构建更复杂的应用。

关系型数据库为我们提供了通过 SQL 语言与他们交互的能力。

就像下面这样：

SELECT * FROM cars

INSERT INTO cars VALUES ('Lamborghini', 2010)

我们使用这个语言来定义模式，用数据填充表，最后在需要的时候查询数据。

本手册的其余部分将重点介绍 SQL。

SQL相当古老，诞生于1986年，它是一种经过实战检验的技术，用于各种应用程序。

5. SQL 介绍

SQL (Structured Query Language) 是我们用来和数据库管理系统用一种标准的方法来交换信息的语言。

顾名思义，他不是一种编程语言，而是专为查询诞生的语言，后来发展成为一个接口，对数据库执行更高级的操作，而不只是查询。

虽然 SQL 第一次公开发版是在 1986 年，但是它一直都在发展，更新很频繁，仅仅在三年后就再次更新，随后 1992，1999,2003,2006,2008,2011,2016 都在更新，最新的更新是在 2019 年。

SQL 被许多流行的数据库管理系统实现：Postgres, MySQL, Oracle, SQLite, MicroSoft SQL Server 等等。每个不同的数据库都实现标准的或者特定的版本，并在其上添加特性化功能，以简化查询的创建或添加特定功能。

SQL 是一个巨大的主题，你可以逐字逐句的花费数年时间去精通所有功能，在这本手册我们只关注基础让你起步并且能够运行起来。

我推荐你在一个在线实验工具里尝试运行我解释的 SQL，比如 DB FIddle 或者其他。本地跑你自己的数据库的话，推荐一个很棒的应用 tabPlus 。

作为日常应用程序中的开发人员，你可以使用ORM（对象关系映射）库来抽象 SQL 简化生活，但是了解底层的工作原理是绝对必要的，并且ORM的许多术语都反映了底层 SQL 命名约定。

而且，有时候使用普通的 SQL 语句来解决问题就够了。

6. 创建一张表

一个数据库由一张或多张表组成。在 SQL 中创建一张表是通过 CREATE TABLE 语句来完成。

在创建时，你需要指明表格列的名字和类型。

一些最重要最常见的类型是：

CHAR
VARCHAR
DATE
TIME
DATETIME
TIMESTAMP
SMALLINT
INTERGER
DECIMAL
FLOAT

请参阅你使用的 DBMS 的手册以查看这些代表的精确值，因为对于不同的实现，这些值可能会有不同。而且，每个 DBMS 还能实现非标准类型比如 BIGINT 或 TINYINT或 TEXT，甚至有建议实现 JSON 和 array 类型。就算是非标准的实现，只要你知道如何使用一定能用，但是，同时也要记住，在不同的 DBMS 中切换会有风险，比如从 PostgreSQL 切换到 SQLite。

下面这个例子将会创建一个 people 表，它有两列，一列是 age，类型是 integer，一列是 name，类型是可变字符串：

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

7. 向表中插入数据

当你有一张表之后，你就可以往表中插入数据。

比如上面这张 people 表，你可以通过 INSERT INTO 命令添加数据：

INSERT INTO people VALUES (37, '小美')

你可以一条语句同时插入多个数据：

INSERT INTO people VALUES (37, '小明'), (9, '小小明')

8. 查询数据

当表中有数据之后，你就可以使用 SELECT 命令从表中获取数据了。

比如，你可以通过 SELECT * FROM people 来获取表中所有的数据：

SELECT * FROM people

结果如下：

age | name
----+-------
 37 | 小美
 37 | 小明
  9 | 小小明

只获取特定的列比如 name：

SELECT name FROM people

结果如下：

name
-------
 小美
 小明
 小小明

计算表中有多少行：

SELECT COUNT(*) FROM people

结果如下：

count
-------
     3

你可以使用 WHERE 子句来过滤一些行：

SELECT age FROM people WHERE name = '小明'

结果如下：

age
----
 37

搜索结果可以根据列的值进行排序，有升序（ASC）和降序（DESC）两种方式，使用 ORDER BY 子句：这是降序，搜索结果按照 age 列的值从大到小排序：

SELECT * FROM people ORDER BY age DESC

这是升序，搜索结果按照 age 列的值从小到大排序：

SELECT * FROM people ORDER BY age ASC

升序是默认的，所以你可以不写 ASC：

SELECT * FROM people ORDER BY age

9. 处理空的单元格

当我们使用 6 中的 CREATE TABLE 命令创建表时：

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

SQL 不会对空的数据进行处理，而是把它们也当做一条记录：

INSERT INTO people VALUES (null,null)

这样，我们就有了一条空的记录：

SELECT * FROM people

结果如下：

age | name
----+-------
 37 | 小美
 37 | 小明
  9 | 小小明
    |

而这个空的记录会影响我们的查询结果，为了解决这个问题，我们在声明的时候，使用 NOT NULL 关键字，来指定这个列不允许为空：

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL
);

这样，当我们尝试执行插入空值的语句时，就会报错：

INSERT INTO people VALUES (null,null)

结果如下：

ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (null, null).

牢记：空字符串是有效的非空值

10. 唯一值（Unique）和主键（Primary key）

使用下面这个命令创建表：

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL
);

我们可以向表中插入多条数据。有时候，我们可能在一列里有重复的值，为了数据的唯一性，我们可以使用 UNIQUE 关键字来强制这个列的值都是各不相同的：

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL UNIQUE
);

现在，如果你尝试添加两次 ‘小明’：

INSERT INTO people VALUES (37,'小明')
INSERT INTO people VALUES (37,'小明')

就会收获一个报错：

ERROR:  duplicate key value violates unique constraint "people_name_key"
DETAIL:  Key (name)=(小明) already exists.

一个主键是一个索引的同时还有一个功能：它是我们标识表里某一行的主要方式。

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

例如主键可以是用户的一封邮件，也可以时每条记录的唯一 id 以便自动生成一条记录，无论主键的值是什么，我们只需要知道我们可以通过主键去引用表中的某一行。

11. 更新表中的数据

存在表里的数据可以通过使用 UPDATE 命令更新：

UPDATE people SET age=10 WHERE name='小明'

使用 WHERE 字句是必要的，否则像下面这种命令：

UPDATE people SET age=2

将会更新表中所有的行

12. 更新表结构

我们可以改变一个已经存在的表的结构，ALTER TABLE 命令会负责这个，后面紧随你想要的改变：

ALTER TABLE people ADD COLUMN born_year INT;

这样会增加一个新的列，默认空值

age | name | born_year
-----+-------+------------
37  | 小明 |
37  | 小美 |
---

丢弃某一列：

ALTER TABLE people DROP COLUMN bore_year;

born_year 列就不在表中了：

age | name  
-----+-------
37  | 小明 
37  | 小美 
---

13. 删除数据和表

我们可以使用 DELETE FROM 命令从表中删除数据。

比如删除所有行：

DELETE FROM people;

删除特定行可以使用 WHERE 字句：

DELETE FROM people WHERE name='小明'

如果想删除表而不是表里的数据，可以使用 DROP TABLE 命令完成：

DROP TABLE people;

14. 连接（join）

join 是一种将两个表中的数据结合起来的方法，非常强大的工具。

假定我们现在有两张表，people 和 cars:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE cars (
  model CHAR(20) NOT NULL,
  owner CHAR(20) NOT NULL PRIMARY KEY
);

添加一些数据：

INSERT INTO people VALUES (8,'小明');
INSERT INTO people VALUES (37,'小美');
INSERT INTO cars VALUES ('奔驰','小明');
INSERT INTO cars VALUES ('宝马','小美');

现在，我们需要关联两张表，因为警察截停了小明的车，小明看起来很年轻，警察想从从数据库中找到他的年龄，我们使用 JOIN 命令：

SELECT age FROM people JOIN cars
ON people.name = cars.owner
WHERE cars.model = '奔驰';

这个命令会返回：

age
-----
8

发生什么事了？我们使用 JOIN 命令连接两张不同的表：通过 people 表中的 name 和 cars 表中的 owner。最后，在新的表中找到 model 为 ‘奔驰’ 的这一行的 age 的值。

联接是一个复杂性可能会增加的主题，因为有许多不同类型的联接可用于对多个表执行更充满想象力的操作。

15. 使用 DISTINCT 删除重复项

有时候你想要筛选表里重复的数据， DISTINCT 就派上用场了。

比如我们有这么一个表：

CREATE TABLE people (
  name CHAR(20)
);

让我们添加一些数据：

INSERT INTO people VALUES
('小明'), ('小美'),
('路人甲'),('路人甲'),('路人甲'),
('路人甲'),('路人甲'),('路人甲');

如果我们现在运行下面这个查询

SELECT * FROM people;

会出现很多重复项：

| name   
---------
| 小明      
| 小美      
| 路人甲     
| 路人甲     
| 路人甲     
| 路人甲     
| 路人甲     
| 路人甲

在查询语句汇总添加 DISTINCT 关键字可以筛选掉重复项

SELECT DISTINCT * FROM people;

| name   
---------
| 小明      
| 小美      
| 路人甲

16. 使用 `LIKE`携带通配符

使用 LIKE，你可以使用通配符从表中选择符合适配规则的一些行。

例如你有下面的表结构和数据：

CREATE TABLE people (
  name CHAR(20)
);

INSERT INTO people VALUES 
('Joe'), ('John'), ('Johanna'), ('Zoe');

下面这条查询语句将会返回三行，因为它们都以‘Jo’开头：

SELECT * FROM people WHERE name LIKE 'Jo%';

而下面这个查询语句只能找到 John 和 Johanna：

SELECT * FROM people WHERE name LIKE 'Joh%';

注意我使用 % 去匹配了多个字符。

你可以使用 _ 去匹配一个字符，比如下面这个查询语句你可以找到 Joe 和 Zoe：

SELECT * FROM people WHERE name LIKE '_oe';

17. 索引(index)

当一个数据库表里的数据变得很多时，数据库在执行一些操作的速度就会变慢。为了防止这样的情况发生，你可以添加索引。

索引在数据库里的作用非常类似，它是一种在查找数据时告诉数据库做什么优化能快速找到数据的方法。

当表之间的关系变得复杂的时候，索引也会变得很有价值。

简单的说，要添加哪些索引不取决于表结构，而是取决于你执行的查询。

例如，如果你有一张 users 表，带有一个 name 的字段，而且你频繁地通过查询语句获取这个值，那么 name 应该成为一个索引。

可以通过这样的语法创建一个索引：

CREATE INDEX index_name ON users (name);

在一张巨大的表里使用 WHERE 字句也会有效果。

索引可以提高性能，但是也有额外的花费：他们使选择更快，但是降低了插入和更新的速度，还有，增加了使用内存。

和所有事情一样，这依然是一个权衡，必须仔细衡量性能带来的好处和使用的代价。

一个好消息是，你可以在遇到性能问题时再去添加和删除索引，不必一开始就进行优化。

18. 聚合数据

你可以要求数据库对存在表里的数据执行一系列的操作，比如对数字嘞的列求和，或计算平均值。

现在我们有如下的表结构和基础数据：

CREATE TABLE people (
  name CHAR(20),
  age INT
);

INSERT INTO people VALUES 
('Joe', 20), 
('John', 30), 
('Johanna', 25), 
('Zoe', 23);

AVG() 计算平均值

SELECT AVG(age) FROM people;

这样你可以得到 people 表里 age 列的平均值

| avg  |
| ---- |
| 24.5 |

一般情况下，我们会为函数的结果起一个别名作为选择的名称：

SELECT AVG(age) as average FROM people;

结果为：

| average |
| ------- |
| 24.5    |

类似的，SUM() 计算它们的和：

SELECT SUM(age) as total FROM people;

| total |
| ----- |
| 98    |

使用 MAX() 和 MIN() 可以获得最大值和最小值

SELECT MAX(age), MIN(age) FROM people;

| max | min |
| --- | --- |
| 30  | 20  |

19. 数据分组

你可以使用 GROUP BY 子句来对数据进行分组。

比如我们现在有这样的表结构和样本数据：

CREATE TABLE people (
  name CHAR(20),
  department CHAR(20),
  age INT
);

INSERT INTO people VALUES 
('Joe', 'Sales', 20), 
('John', 'Sales', 30), 
('Johanna', 'IT', 25), 
('Zoe', 'IT', 23);

我们想获取每个 department 的评价年龄，我们可以这样写查询语句：

SELECT department, AVG(age) as total FROM people GROUP BY department;

| department | total  |
| ---------- | ------ |
| Sales      | 25     |
| IT         | 24     |

20. 限制和偏移

你可以限制检索到的行数，这不在 SQL 标准中，每个数据库的实现方式和执行方式都不同，在 MySQL 和PostgreSQL 中使用 LIMIT:

SELECT * FROM people ORDER BY name LIMIT 10;

这样可以获取到返回结果的前十行，同样，你可以设置偏移量，让获取结果不从第一个开始，下面这个例子中获取结果的第11行开始的10行：

SELECT * FROM people ORDER BY name LIMIT 10 OFFSET 10;

21. 注释

你可以在 SQL 查询语句中添加注释：通过使用 '--'连字符在一行的开头

-- 这是一行注释
SELECT * FROM people;

或使用 /* */ 来进行行内注释或跨行注释

SELECT * FROM /* test */ people;

SELECT * FROM /* 
this is just a test */ 
people;

SQL 手册

1. 信息系统，数据和信息

2. 什么是数据库（database）？什么是数据库管理系统（DBMS）？

3. 你是否真的需要一个数据库呢？

4. 关系型数据库

5. SQL 介绍

6. 创建一张表

7. 向表中插入数据

8. 查询数据

9. 处理空的单元格

10. 唯一值（Unique）和主键（Primary key）

11. 更新表中的数据

12. 更新表结构

13. 删除数据和表

14. 连接（join）

15. 使用 DISTINCT 删除重复项

16. 使用 LIKE携带通配符

17. 索引(index)

18. 聚合数据

19. 数据分组

20. 限制和偏移

21. 注释

16. 使用 `LIKE`携带通配符