SQL 常用语句总结

2022年8月14日
  1. 1. 配置样例数据库
  2. 2. 数据库
  3. 3. 表
  4. 4. 查询

作者：Vijini Mallawaarachchi

编译：weakish

【编者按】由于大量数据保存在关系数据库中，因此数据科学家难免要和 SQL 打交道。当然，面试的时候也常常考察 SQL。Moratuwa 大学生物信息学研究员 Vijini Mallawaarachchi 总结了常用的 SQL 语句用法，可供参考和温习。

本文总结了常用的 SQL 语句，尤其适合在面试前复习你的 SQL 知识。你可以尝试文中的例子，温习下你很久以前在数据库系统课程上学到的知识。

配置样例数据库

为了演示每个命令的用法，我们将使用一个样例数据库。生成该数据库的脚本可以从 Google 网盘下载：

如不便访问 Google 网盘，可以在论智公众号（ID: jqr_AI）留言sql recap获取。

下载文件后，输入以下命令进入 MySQL 控制台（假设你已经装好了 MySQL 或 MariaDB）。

1
mysql -u root -p

mysql 会提示你输入密码，输入安装配置 MySQL 服务时设置的密码即可。

输入如下命令生成样例数据库：

1
2
3
4
CREATE DATABASE university;
USE university;
SOURCE <DLL.sql文件路径>;
SOURCE <InsertStatements.sql文件路径>;

好了，现在让我们开始温习 SQL 语句吧。

数据库

1. 查看现有数据库

1
SHOW DATABASES;

2. 新建数据库

1
CREATE DATABASE <数据库名>;

3. 选择数据库

1
USE <数据库名>;

4. 从.sql文件引入 SQL 语句

1
SOURCE <.sql文件路径>;

5. 删除数据库

1
DROP DATABASE <数据库名>;

表

6. 查看当前数据库中的表

1
SHOW TABLES;

7. 创建新表

1
2
3
4
5
6
7
CREATE TABLE <表名> (
    <列名1> <列类型1>,
    <列名2> <列类型2>,
    <列名3> <列类型3>,
    PRIMARY KEY (<列名1>),
    FOREIGN KEY (<列名2>) REFERENCES <表名2>(<列名2>)
);

主键（PRIMARY KEY）用来标识一条记录（一行），所以每条记录的主键值必须是唯一的。主键可以定义在多列上，这称为联合主键（composite primary key）。

如果我们把表视作具有某种结构的数组（例如，C 语言中的 struct），那么外键（FOREIGN KEY）可以视作指针。

例子：

1
2
3
4
5
6
7
CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary NUMERIC(8,2), 
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name));

在上面的例子中，我们创建了一个教员（instructor）表，该表的主键是 ID，外键是教员所在的部门名称（dept_name），关联部门（department）表。此外，教员表还包括姓名（name）、薪水（salary）。其中，姓名有约束NOT NULL，表示姓名这一项不能为空。

8. 概述表中的列

使用如下语句查看表中的列的基本信息：

1
DESCRIBE <表名>;

下图显示了一些例子：

9. 在表中插入新纪录

1
2
INSERT INTO <表名> (<列名1>, <列名2>, <列名3>, …)
    VALUES (<值1>, <值2>, <值3>, …);

也可以省略列名（依序在所有列上插入新值）：

1
2
INSERT INTO <表名>
    VALUES (<值1>, <值2>, <值3>, …);

10. 在表中更新记录

1
2
3
UPDATE <表名>
    SET <列名1> = <值1>, <列名2> = <值2>, ...
    WHERE <条件>;

11. 清空表

1
DELETE FROM <表名>;

12. 删除表

1
DROP TABLE <表名>;

查询

13. SELECT

SELECT 语句可以从表中选择数据：

1
2
SELECT <列名1>, <列名2>, …
    FROM <表名>;

以下语句选择所有内容：

1
SELECT * FROM <表名>;

14. SELECT DISTINCT

SELECT DISTINCT 过滤掉了重复的值：

1
2
SELECT DISTINCT <列名1>, <列名2>, …
    FROM <表名>;

![][img-3]

15. WHERE

我们之前在更新记录时已经用到了 WHERE 关键字，用来指明条件。这里我们稍微详细一点地介绍下 WHERE。

WHERE 的条件通常是：

  • 比较文本（text）
  • 比较数字（numbers）
  • AND、OR、NOT 等逻辑运算

让我们来看一些例子：

1
2
3
SELECT * FROM course WHERE dept_name='Comp. Sci.';
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;

![][img-4]

16. GROUP BY

GROUP BY 语句可以分组结果，常用于 COUNT、MAX、MIN、SUM、AVG 等聚合函数（aggregate functions）。

1
2
3
SELECT <列名1>, <列名2>, …
    FROM <表名>
    GROUP BY <列名>;

让我们来看一个例子，列出每个部门的课程数量：

1
2
3
SELECT COUNT(course_id), dept_name 
     FROM course 
     GROUP BY dept_name;

![][img-5]

17. HAVING

乍看起来，HAVING 和 WHERE 很像：

1
2
3
4
SELECT <列名1>, <列名2>, …
    FROM <表名>
    GROUP BY <列名x>
    HAVING <条件>;

那么，HAVING 和 WHERE 有什么不同呢？让我们先来看一个例子，列出开了不止一门课程的部门开设的课程数：

1
2
3
4
SELECT COUNT(course_id), dept_name 
    FROM course 
    GROUP BY dept_name 
    HAVING COUNT(course_id)>1;

这里 HAVING 不能换成 WHERE，因为 WHERE 直接针对行操作，且在 GROUP BY 之前运行（即先通过 WHERE 筛选行，之后再将筛选出的行通过 GROUP BY 分组）。假设 SQL 中不存在 HAVING 语句，那么我们只能先新建一张表，将COUNT(course_id)作为新表的列，然后在新表上再通过 WHERE 进行筛选（当然，实际上 SQL 提供了派生表、CTE 等机制，并不用真的手工建新表）。

![][img-6]

18. ORDER BY

ORDER BY 可以对结果进行排序，在没有明确指定 ASC（升序）或 DESC（降序）的情况下，默认按升序排列。

1
2
3
SELECT <列名1>, <列名2>, …
FROM <表名>
ORDER BY <列名1>, <列名2>, …, ASC|DESC;

例子：

1
2
SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;

![][img-7]

19. BETWEEN

BETWEEN 语句用于指定区间。

1
2
3
SELECT <列名1>, <列名2>, …
    FROM <表名>
    WHERE <列名x> BETWEEN <值1> AND <值2>;

其中 “值” 可能是数字，文本，乃至日期等。

例如，列出薪资在 50000 和 100000 之间的教员：

1
2
SELECT * FROM instructor 
    WHERE salary BETWEEN 50000 AND 100000;

![][img-8]

20. LIKE

LIKE 用于匹配文本中的特定模式。

1
2
3
SELECT <列名1>, <列名2>, …
    FROM <表名>
    WHERE <列名x> LIKE <模式>;

模式中可以使用以下两个通配符：

  • % （零个、一个或多个字符）
  • _ （单个字符）

例子：列出课程名中包含 “to” 的课程，以及课程 ID 以 “CS-” 开头的课程。

1
2
SELECT * FROM course WHERE title LIKE '%to%';
SELECT * FROM course WHERE course_id LIKE 'CS-___';

![][img-9]

21. IN

IN 语句表示值属于某个集合。

1
2
3
SELECT <列名1>, <列名2>, …
    FROM <表名>
    WHERE <列名n> IN (<值1>, <值2>, …);

例子：列出计算机科学、物理、电子工程部门的学生。

1
2
SELECT * FROM student 
    WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');

![][img-10]

22. JOIN

JOIN 用来组合两张以上表中的值。下图展示了 JOIN 的三种类型：

![][img-11]

1
2
3
4
SELECT <列名1>, <列名2>, …
    FROM <表名1>
    JOIN <表名2>
    ON <表名1.列名x> = <表名2.列名x>

让我们来看三个例子，分别对应三种 JOIN 的类型。

第一个例子，列出课程时包含开设课程的部门详情：

1
2
3
SELECT * FROM course 
    JOIN department 
    ON course.dept_name=department.dept_name;

![][img-12]

第二个例子，列出所有具有前置课程的课程的详情：

1
2
3
4
SELECT prereq.course_id, title, dept_name, credits, prereq_id 
    FROM prereq 
    LEFT OUTER JOIN course 
    ON prereq.course_id=course.course_id;

![][img-13]

最后一个例子，列出所有课程的详情，不管是否具有前置课程：

1
2
3
4
SELECT course.course_id, title, dept_name, credits, prereq_id 
    FROM prereq 
    RIGHT OUTER JOIN course 
    ON prereq.course_id=course.course_id;

![][img-14]

23. 视图

视图（view）是虚拟的 SQL 表。它包含行和列，和一般的 SQL 表格很类似。视图总是显示数据库中的最新数据。

CREATE VIEW

创建视图：

1
2
3
4
CREATE VIEW <视图名> AS
    SELECT <列名1>, <列名2>, …
    FROM <表名>
    WHERE <条件>;

DROP VIEW

删除视图：

1
DROP VIEW <视图名>;

例如，创建 3 学分的课程视图：

1
2
3
CREATE VIEW my_view AS
    SELECT * FROM course
    WHERE credits=3;

![][img-15]

24. 聚合函数

我们之前已经提到聚合函数，这里列出最常用的一些聚合函数：

  • COUNT(列名) 返回行数
  • SUM(列名) 返回指定列的值之和
  • AVG(列名) 返回指定列的平均值
  • MIN(列名) 返回指定列的最小值
  • MAX(列名) 返回指定列的最大值

25. 嵌套子查询

在 SQL 请求中，可以嵌套 SELECT-FROM-WHERE 表达式，称为嵌套子查询（nested subqueries）。

例如，查找 2009 年秋、2010 年春都开的课程：

1
2
3
4
5
6
7
SELECT DISTINCT course_id 
    FROM section 
    WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (
        SELECT course_id 
            FROM section 
            WHERE semester = ‘Spring’ AND year= 2010
    );

![][img-16]

希望你觉得这篇文章有用。

感谢阅读！

祝面试顺利！

Please Contact the Author before Reproduce.

© Mint Forge. All Rights Reserved.