SQL查询语句是使用关系型数据库必须要掌握的，本文是自学SQL查询语句和相关知识的学习笔记，方便有需要时备查。感谢 SQL自学网 及其英文版本 SQLBolt 提供的学习资料。

SELECT语句

SELECT Startup

1
SELECT ... FROM ...

关系型数据库以数据表为基本单元，数据表的结构是一个二维的平面表，每列代表一个字段，每行代表一条记录，一般来讲字段是在建表之时即确定的，而记录则可随时通过SQL语句进行“增删改查”。

上面展示的是SELECT语句的基本结构。关键字FROM前代表要查询的字段名/列名，可以有一列或多列，FROM后则是待查询的表名，只能有一个。

SELECT和FROM后的字段名和表名可以设置别名，该别名将在后续的条件查询语句以及返回的记录中取代原先的字段名和表名。设置别名的例子如下：

1
2
3
4
5
6
7
8
9
10
11
12
-- country字段的别名是c，world表的别名是w
-- 由[]包裹的内容代表可写可不写
SELECT country [AS] c FROM world w
...
> ——————————————————
> c
> ——————————————————
> United States
> China
> Japan
> ...
> ——————————————————

条件查询

1
2
3
4
SELECT ... FROM ...
WHERE Condition1 
    AND(OR) Condition2
    AND(OR) ...

关键字WHERE后可以指定只返回满足条件的记录，可以通过AND/OR关键字指定多个条件。针对任意字段的条件主要有以下几种：

  • 大于/小于/大于等于/小于等于，运算符略
  • 等于（使用"=“而非”=="）/不等于（!=）
  • 枚举，in (…) / not in (…)
  • LIKE/NOT LIKE关键字
    • 后接匹配模式，一般针对字符串使用
    • "%"通配符：匹配大于等于0个任意字符
    • "-"通配符：匹配1个任意字符

结果过滤、分组和排序

1
2
3
4
5
6
SELECT DISTINCT ... FROM ...
WHERE Condition1 
    AND(OR) Condition2
    AND(OR) ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

与过滤、分组、排序相关的关键字有：

  • DISTINCT关键字，在返回的结果中删除各字段完全重复的记录；
  • ORDER BY关键字，依据指定的字段名排序后返回，ASC/DESC可以指定排序方式是升序还是降序，默认是ASC；
  • LIMIT关键字，限制返回记录的最大数目；
  • OFFSET关键字，限制从哪一行记录开始返回，注意这里的行号是从0开始的；

嵌套查询

1
2
3
4
5
6
SELECT DISTINCT ... FROM ...
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
    AND(OR) Conditions
    AND(OR) ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

如上所示，SELECT语句中可以嵌套另一个SELECT语句。如果比较条件的比较对象或者待查询的对象依赖于对数据库内部某个记录值的查询，那么在需要这个值的地方就可以嵌套语句，上面展示了嵌套语句用于过滤条件的情况。

JOIN语句

主键和多表联查

在关系型数据库的数据表中，一般会将某个字段设为主键（Primary Key），而每条记录都有一个 独一无二、不重复 的主键值。比如，在中国公民身份信息的数据库中，“身份证号”就扮演了主键的角色。

主键的作用在于，现代数据库一般都含有多张数据表，每张数据表都存储了不同的信息；但有时，我们需要根据某张表的信息来查询另一张表的信息。这个时候，主键就可以作为把不同的数据表连接起来的“桥梁”。

在多表联查的情境中，需要使用JOIN系列语句。

INNER JOIN 内联

1
2
3
4
5
6
7
8
-- Suppose "this_table" and "other_table" has primary key "id"
SELECT DISTINCT ... FROM this_table
INNER JOIN other_table ON this_table.id = other_table.id
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
    AND(OR) Conditions
    AND(OR) ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

INNER JOIN关键字起到了内联多个表的功能。在解释内联的含义之前，首先看一下INNER JOIN的用法：关键字ON之前是待连接的表名待连接的表名，ON之后是连接条件连接条件，在上方的范例中是两个表的主键值相等。

当然，ON之后也可以替换为其它任何条件，参考之前在条件查询中的关系，注意“表名.字段名”的写法。该语句产生的效果是，SQL引擎将other_table中id对应的记录追加到this_table与其id值相同的记录之后，并返回这些记录；而两个表中不符合连接条件的记录将被丢弃。从集合论的视角来看，返回的记录可以被视为两张表的交集，这也是“内联”这个术语的来源。

在确定连接条件时，当然也可以使用诸如大于、小于、LIKE等容易造成“一对多”的比较方式。此时返回表会这样处理：对于this_table中的每一条记录，都会生成一条与符合条件的other_table记录的连接记录。这会导致返回表的主键值不再唯一。

OUTER JOIN 外联

1
2
3
4
5
6
7
8
-- Suppose "this_table" and "other_table" has primary key "id"
SELECT DISTINCT ... FROM this_table
LEFT/RIGHT/FULL [OUTER] JOIN other_table ON this_table.id = other_table.id
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
    AND(OR) Conditions
    AND(OR) ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

外联与内联最为明显的区别是会保留不符合连接条件的记录行，而根据保留范围的不同，又可以分为三种连接方式：

  • LEFT (OUTER) JOIN，左联，只保留this_table中不符合连接条件的行；
  • RIGHT (OUTER) JOIN，右联，只保留other_table中不符合连接条件的行；
  • FULL (OUTER) JOIN，全连接，两表中不符合连接条件的都将保留。

需要注意的是，不论是内联还是外联，SELECT的主键字段名都与FROM后的表名中保持一致。

在外联语句的返回表中，不符合连接条件的记录可能不存在另一张表中某些字段的值，这些字段均使用 NULL 填充。NULL是SQL中一个很重要的概念，可以代表空值。在条件语句中判断某个字段的值是否为NULL，应使用IS NULL或IS NOT NULL。

查询结果统计

表达式

在SQL语句中，位于SELECT和FROM之间要返回的字段除直接使用相应字段名之外，还可以填写表达式，进行加减乘除模等运算。对字段名的操作等同于对该字段下所有字段值的操作。默认情况下，返回表的字段名与表达式相同，可以通过设置别名的方式来自定义返回字段名。

除四则运算之外，表达式也可以包含一些常见的统计函数：

  • COUNT() 计数函数，COUNT(*)可以统计数据表的总行数，而COUNT(col)可以统计某个字段非NULL行的行数；
  • MIN() / MAX() 最值函数，找到某一字段中的最大和最小值（字符串列怎么比较？bool列怎么比较？）
  • SUM() 求和函数，对某列的所有值求和；
  • AVG() 平均函数，对某列的所有非NULL值取平均；

另外，表达式也可以是判断条件，如colname IS NULL，此类表达式返回的是0/1，代表相应记录的字段值是否符合前述条件。

GROUP BY 语句

1
2
3
4
5
6
7
8
9
-- Suppose "this_table" and "other_table" has primary key "id"
SELECT DISTINCT ... FROM this_table
INNER/LEFT/RIGHT/FULL JOIN other_table ON this_table.id = other_table.id
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
  AND(OR) Conditions
  AND(OR) ...
GROUP BY ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

GROUP BY语句一般与统计函数（当前语境下也称为“聚合函数”）搭配使用，起到分组统计的作用，关键字后接作为分组依据的字段名或者表达式，可以有一个或者多个。

如果SELECT的字段涉及到了与分组依据或聚合函数无关的字段，则可能会有无法预计的后果（如返回随机值、报错等），这与GROUP BY 语句的执行机制有关。
在GROUP BY语句的执行过程中，首先依照分组依据，在返回表中创建相应数量的记录行，有几种分组依据的值，就创建几条记录；随后，如一条记录的其它字段在原查询的表中对应多个值，则使用给定的聚合函数将其计算为一个值，即返回表中相应记录的字段值；此时如果未给出聚合函数且仍对应多个值，就会出现报错或者随机返回一个值。

HAVING 语句

1
2
3
4
5
6
7
8
9
10
-- Suppose "this_table" and "other_table" has primary key "id"
SELECT DISTINCT ... FROM this_table
INNER/LEFT/RIGHT/FULL JOIN other_table ON this_table.id = other_table.id
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
  AND(OR) Conditions
  AND(OR) ...
GROUP BY ...
HAVING ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

HAVING语句的用法与WHERE语句几乎完全一致，区别在于它适用于对GROUP BY语句分组统计后的结果进行条件过滤；而WHERE语句则专注于对原表数据的过滤，不能用在GROUP BY语句之后。

查询小结

1
2
3
4
5
6
7
8
9
10
-- Suppose "this_table" and "other_table" has primary key "id"
SELECT DISTINCT ... FROM this_table
INNER/LEFT/RIGHT/FULL JOIN other_table ON this_table.id = other_table.id
WHERE col1 < (SELECT col2 FROM ... WHERE ...) 
  AND(OR) Conditions
  AND(OR) ...
GROUP BY ...
HAVING ...
ORDER BY ... ASC/DESC
LIMIT ... OFFSET ...

以上是无嵌套情况下多表查询最全的语句模式，包含了在查询语句中可能出现的所有关键字。此外，在需要填充的部分，还可能出现以下要素：常量、别名（可选AS关键字）、表达式、判断条件。

在SQL语句执行时，大致是按照以下顺序：

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

当然，在一些复杂的情况下，具体的执行和计算顺序可能会有所不同；但是掌握一套基本的流程，对我们分析相关问题是很有帮助的。

对记录的操作

插入记录

在数据表中插入记录需要用到INSERT关键字和VALUE关键字，具体模式如下：

1
2
3
4
5
INSERT INTO table_name
(col1, col2, …) --This line is NOT necessary
VALUES (value1_1, value2_1 …),
       (value1_2, value2_2, …),
       ...

第二行的作用是确定要给哪些字段赋初值。如果没有第二行，那么在VALUES关键字之后需要按表中字段的顺序列出全部的值。此外，也可以用表达式代替简单的数值或字符串。

更新记录

我们可以使用UPDATE和SET关键字更新数据表中的记录，具体模式如下：

1
2
3
4
5
UPDATE table_name
SET col1 = value1,
    col2 = value2,
    ...
WHERE condition

SET关键字后是要更新的字段和新的值，而WHERE关键字则用于限定更新哪些记录。使用UPDATE语句时，经常犯的错误就是WHERE条件没写对导致错误更新，所以比较保险的方式是首先写一个查询语句，确认WHERE条件无误再改写成UPDATE语句。

删除记录

删除表中记录的操作方式与更新类似，使用DELETE关键字，模式如下：

1
2
DELETE FROM table_name
WHERE condition

同样，在执行删除语句前务必再三确认WHERE条件正确指向了你想要删除的记录。

表操作

创建表

可以使用CREATE TABLE关键字在数据库中创建一张新表，为了避免表名与已有的表重复，一般会同事使用IF NOT EXISTS语句。创建表的一个经典模式如下：

1
2
3
4
5
CREATE TABLE IF NOT EXISTS mytable (
  col1 DataType Constraint1 DEFAULT value1,
  col2 DataType Constraint2 DEFAULT value2,
  ...
)

括号中的内容被称作数据表的范式（Schema），定义了表中各字段的字段名、数据类型、约束条件（可选）以及默认值（可选）。

对于数据类型，每种数据库都有自己的定义，但是被普遍使用的主要有以下几种：

  • 整数：INTEGER
  • 布尔型：BOOLEAN
  • 浮点数：REAL, FLOAT, DOUBLE
    • 三种类型的精度按由低到高排列
  • 文本：TEXT, CHARACTER(max_chars), VARCHAR(max_chars)
  • 日期时间：DATE, DATETIME
  • 二进制数据：BLOB
    • 一般而言，数据库不会自动地理解二进制数据，所以在存储时需要添加必要的元数据信息，以便读取

对于约束条件，也有几种常用的关键字：

  • PRIMARY KEY指定数据表的主键，主键所在字段将作为表中记录的唯一标识符；
  • UNIQUE表示表中记录在对应字段的值不可重复，与PRIMARY KEY的主要区别是不承担记录标识符的功能；
  • FOREIGN KEY主要用于存在相互关联的多张表时，该关键字可以确保被修饰的字段一定能够在另一张表中对应一条记录的主键；
  • AUTOINCREMENT表示插入记录时该字段将自动填充递增的整数；
  • NOT NULL表示记录中该字段的值不能为NULL；
  • CHECK(exp)表示对字段值进行合法性判断，括号中是判断的条件，可以是任意形式能够输出真值的表达式。

修改表

这里所说的修改，主要是指对数据表的范式进行增加、修改、删除等操作。各个数据库引擎共同操作的主要语句格式如下：

1
2
3
4
ALTER TABLE mytable
ADD colname1 DataType1 Constraint1 DEFAULT value1; --add column
DROP colname2;  --remove column
RENAME TO new_table -- rename table

需要说明的是，对于修改表的操作，各个引擎的差异相对较大，因此最好的方式是在执行语句之前先查阅所用数据库的文档，如MySQL，Postgres，SQLite，Microsoft SQL Server等。

删除表

删除整张表的语句与创建表类似：

1
DROP TABLE IF EXISTS mytable

如果要删除的表与其它表存在字段上的依赖关系（如外键等），则需先妥善处理好这些依赖，再进行删除操作。