Skip to content

学考自救指北 —— SQL 篇

杂项约 2.3 千字
检测到 KaTeX 加载失败,可能会导致文中的数学公式无法正常渲染。

SQL(Structured Query Language,结构化查询语言)是一种具有数据操纵和数据定义等多种功能的数据库语言。

基本概念

这些可以不用会,但最好了解一下。

  • 数据库(database):保存有组织的数据的容器(通常是一个文件或一组文件)。
  • 数据表(table):某种特定类型数据的结构化清单。
  • 模式(schema):关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储,包含存储什么样的数据,数据如何分解,各部分信息如何命名等信息。数据库和表都有模式。
  • (column):表中的一个字段。所有表都是由一个或多个列组成的。
  • (row):表中的一个记录。
  • 主键(primary key):一列(或一组列),其值能够唯一标识表中每一行。

SQL 语法

  1. SQL 语句不区分大小写(数据库表名、列名和值是否区分大小写,依赖于具体的 DBMS 以及配置)。
    例如:SELECTselectSelect 是相同的。
  2. 多条 SQL 语句必须以半角分号(;)分隔,部分数据库系统要求在每条 SQL 语句后都添加分号。
  3. 处理 SQL 语句时,所有空格都被忽略。SQL 语句可以写成一行,也可以分写为多行。

SQL 中的注释有两种形式:

  • 单行注释:-- 后面跟着任意字符串,包括空格。
  • 多行注释:以 /* 开始,*/ 结束。

部分引擎还支持以 # 号开头的注释,在此不做过多叙述。

基本操作

创建表

下方的 SQL 语句创建了一个名为 students 的数据表,表中包含了学生的编号、姓名、出生日期、班级,其中编号每次插入新行都会自动加一、班级(class)默认为「高一年级 A16 班」,并将学生编号设置为了数据表的主键(PRIMARY KEY (`id`) 子句),还设置了这些字段不能为空值(NOT NULL)。

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `birthdate` date NOT NULL,
  `class` char(20) NOT NULL DEFAULT '高一年级A16班',
  PRIMARY KEY (`id`)
);
常用数据类型
数据类型 说明
INTEGER (简写 int) 整数型
NUMERIC(全长, 小数位数) 数值型
CHAR 定长字符串
VARCHAR 可变长字符串
DATE 日期型

示例:NUMERIC(7, 2) 表示数值型,有 5 位整数,2 位小数,全长 7 位。

关于主键

主键的值不允许修改,也不允许复用(不能将已经删除的主键值赋给新数据行的主键)。

删除表

下方的 SQL 语句删除了一个名为 students 的数据表。

DROP TABLE `students`;

更新表定义

更新表定义可以使用 ALTER 子句。

增加列

下方的语句可以为 students 数据表在 class 列后再增加一个新的列,新列的名称为 address,数据类型为 varchar(100)

ALTER TABLE `students` ADD COLUMN `address` varchar(100) NOT NULL AFTER `class`;

如果不写 AFTER 子句,则新列会被添加到数据表的最后面。也可以使用 FIRST 替代这个子句,这样新列会被添加到数据表的开头。

删除列

下方的语句可以删除 students 表中名为 address 的列。

ALTER TABLE `students` DROP COLUMN `address`;

更改表名

下方的语句可以将名为 students 的表更名为 student

ALTER TABLE `students` RENAME TO `student`;

数据增加

基本格式:

INSERT INTO `students` (`name`, `birthdate`, `class`) VALUES ('张三', '1926-08-17', '高一年级A16班');

这个语句的意思是向名为 students 的表中插入一行,其中 name 列的值为 张三birthdate 列的值为 1926-08-17class 列的值为 高一年级A16班,其他列均为默认值。

数据检索

基本格式:

SELECT `name` FROM `students`;

这个语句的意思是从名为 students 的表中查询 name 列(字段)的值,即获取所有学生的名字。

字段名可以使用通配符代替,这样可以获取所有字段的值,例如:

SELECT * FROM `students`;

数据删除

可以使用 DELETE 语句来删除数据。

DELETE FROM `students` WHERE `id` = 1;

这个语句可以删除编号为 1 的学生。

数据更新

可以使用 UPDATE 语句来更新数据。

UPDATE `students` SET `name` = '李四' WHERE `id` = 1;

这个语句可以将编号为 1 的学生的姓名改为 李四

当然,将名字为「张三」的学生改名为「李四」也不是不可以:

UPDATE `students` SET `name` = '李四' WHERE `name` = '张三';

数据过滤

SQL 中的数据过滤可以使用 WHERE 子句和一些辅助函数来完成。

单条件筛选

下方的语句可以从 students 表中选择出所有名为「张三」的学生(即 name 的值为 张三 的所有行)并输出该行的所有信息。

SELECT * FROM `students` WHERE `name` = '张三';

多条件筛选

下方的语句可以从 students 表中选择出「高一年级 A16 班」中所有名为「张三」的学生(即 name 字段的值为 张三class 字段的值为 高一年级A16班),并输出这些学生的生日。

SELECT birthdate FROM `students` WHERE `name` = '张三' AND `class` = '高一年级A16班';

如果只需要满足这两个条件之中的某一个可以使用 OR 代替 AND

下方的语句可以从 exams 表中选择出「2021 - 2022 学年第一学期期中考试」中总成绩大于 300 分的所有学生。

SELECT * FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试' AND `score` > 300;

IN 操作符

下方的语句可以从 students 表中选择出所有在「高一年级 A15 班」和「高一年级 A16 班」的学生。

SELECT * FROM `students` WHERE `class` IN ('高一年级A15班', '高一年级A16班');

也可以这样写,不过不太简洁:

SELECT * FROM `students` WHERE `class` = '高一年级A15班' OR `class` = '高一年级A16班';

NOT 操作符

下方的语句可以从 students 表中选择出所有 不在「高一年级 A15 班」和「高一年级 A16 班」的学生。

SELECT * FROM `students` WHERE `class` NOT IN ('高一年级A15班', '高一年级A16班');

通配符匹配

使用 LIKE 子句可以实现通配符匹配,例如:

SELECT * FROM `students` WHERE `name` LIKE '张%';

这个语句可以选择出所有名字以 开头的学生。

而使用 _ 可以匹配任意一个字符,例如:

SELECT * FROM `students` WHERE `name` LIKE '马_国';

这个语句可以选择出所有名字有三个字的,且名字以 开头、以 结尾的学生。

数据处理

排序

排序可以使用 ORDER BY 子句来完成,例如:

SELECT * FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试' ORDER BY `score`;

这个语句可以选择出所有名为 2021-2022学年第一学期期中考试 的考试,并按照 score 字段进行排序。

如果需要降序排序,那么为 ORDER BY 子句添加 DESC 即可:

SELECT * FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试' ORDER BY `score` DESC;

LIMIT 子句

可以使用这个子句来限制输出数量,比如下面这个语句就可以输出考试中成绩前十名的学生:

SELECT * FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试' ORDER BY `score` DESC LIMIT 10;

内建函数

SQL 中常用的内建函数有 5 个,分别是 count sum avg max min

COUNT 函数

下面的语句返回指定列的值的数目(未去重):

SELECT COUNT(`name`) FROM `exams`;

如果需要对数据进行去重,可以使用 DISTINCT 子句:

SELECT COUNT(DISTINCT `name`) FROM `exams`;

也可以统计表中数据的行数,使用 COUNT(*) 子句:

SELECT COUNT(*) FROM `exams`;

SUM 函数

下面的语句可以返回指定列中的所有值的总和:

SELECT SUM(`score`) FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试';

这个语句可以返回名为「2021-2022 学年第一学期期中考试」的考试中所有同学们获得的总分之和。

AVG 函数

下面的语句可以返回指定列中的所有值的平均值:

SELECT AVG(`score`) FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试';

这个语句可以返回名为「2021-2022 学年第一学期期中考试」的考试的平均分。

MIN 函数

下面的语句可以返回指定列中的所有值的最小值:

SELECT MIN(`score`) FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试';

这个语句可以返回名为「2021-2022 学年第一学期期中考试」的考试中的最低分。

MAX 函数

SELECT MAX(`score`) FROM `exams` WHERE `name` = '2021-2022学年第一学期期中考试';

这个语句可以返回名为「2021-2022 学年第一学期期中考试」的考试中的最高分。

参考资料

  1. SQL 教程,菜鸟教程。
  2. Transact-SQL 参考,Microsoft SQL Server 中文文档。
  3. MySQL 8.0 Reference Manual,Oracle Docs。

本文是在 GitHub Copilot 的辅助之下编写的,版权共有。如有错误请及时指正。