Bobby Blog

large picture of the cover

数据库系统概论-基础篇-关系数据库标准语言SQL

结构化查询语言（Structured Query Language）是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。

以MySQL数据库为例，可能有些SQL不符合标准SQL。

SQL概述

SQL的产生与发展

1970年Codd提出关系模型之后，由于关系代数和关系都太数学了，难以被普通用户接受，于是1973年IBM开展了System R的研制工作，System R以关系模型为基础，但是摈弃了数学语言，以自然语言为方向，结果诞生了结构化英语查询语言Sequel（Structured English Query Language），后更名为SQL。

目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。同时，许多厂商对SQL基本命令集还进行了不同程度的扩充和修改，又可以支持标准以外的一些功能特性。因此，在使用具体系统时要查阅各产品的用户手册。

SQL的特点

要特点包括一下几个部分：

综合统一

SQL集数据定义语言DDL、数据操纵语言DML、数据控制DCL功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动：

  • 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。

  • 对数据库中的数据进行查询和更新。

  • 数据库重构和维护。

  • 数据库安全性、完整性控制，以及事务控制。

  • 嵌入式SQL和动态SQL定义。

高度非过程化

​​非关系数据模型的数据操作语言是“面向过程”的，必须指定存取路径。

​​SQL只要提出“做什么”，无需了解存取路径。存取路径的选择及SQL的操作过程由系统自动完成。

面向集合的操作方式

非关系数据模型采用面向记录的操作方式，操作对象是一条记录。

SQL采用集合操作方式，操作对象、查找结果可以是元组的集合；一次插入、删除、更新操作的对象可以是元组的集合。

以同一语法结构提供多种使用方式

SQL既是独立的语言，又是嵌入式语言。能够独立的用于联机交互的使用方式，又能够嵌入到高级语言（例如C，C++，Java）程序中使用。

语言简介，易学易用

​​SQL功能极强，由于设计巧妙，语言十分简洁，完成核心功能只用了9个动词。

SQL功能动词
数据查询SELECT
数据定义CREATE，DROP，ALTER
数据操纵INSERT，UPDATE，DELETE
数据控制GRANT，REVOKE

SQL的基本概念

支持SQL的关系数据库管理系统同样支持关系数据库的三级模式结构。其中外模式包括若干视图（view）和部分基本表（base table），数据库模式包括若干基本表，内模式包括若干存储文件（stored file）。
sql支持的三级模式.jpg

基本表：本身独立存在的表，在RDBMS中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

存储文件：逻辑结构组成了关系数据库的内模式。物理结构对用户隐蔽。

视图：从一个或几个基本表导出的表。数据库中只存放视图的定义而不存放视图对应的数据，是一个虚表；可以在视图上在定义视图。

学生-课程数据库

学生表Student

学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept
201215121李勇男20CS
201215122刘晨女19CS
201215123王敏女18MA
201215125张立男19IS

课程表Course

课程号Cno课程名Cname先行课Cpno学分Ccredit
1数据库54
2数学 2
3信息系统14
4操作系统63
5数据结构74
6数据处理 2
7PASCAL 语言64

学生选课表SC

学号Sno课程号Cno成绩Grade
201215121192
201215121285
201215121388
201215122290
201215122380

数据定义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表、视图和索引等。

模式的定义与删除

在MySQL中，每一个数据库就是一个模式，

定义模式

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

可以通过SHOW CHARACTER SET和SHOW COLLATION来查看字符集和排序规则；

例如，创建一个名为S_T的模式，字符集为utf8mb4，排序规则为utf8mb4_general_ci；

CREATE SCHEMA S_T CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

默认会在/var/lib/mysql目录下新建一个名为S_T的目录。

删除模式

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除数据库中的所有表并删除数据库，需要小心使用。

基本表的定义、删除与修改

定义基本表

使用CREATE TABLE 语句定义基本表，基本格式如下

CREATE TABLE tbl_name (col_name data_type [列级完整性约束条件]
     [,col_name data_type [列级完整性约束条件]]
     ...
     [,表级完整性约束条件]);

例如，创建学生、课程、学生课程表

CREATE TABLE `S_T`.`Student` (
	Sno VARCHAR ( 10 ) PRIMARY KEY,
	Sname VARCHAR ( 20 ) UNIQUE,
	Ssex CHAR ( 2 ),
	Sage SMALLINT,
	Sdept VARCHAR ( 20 ) 
);

CREATE TABLE `S_T`.`Course` (
	Cno VARCHAR ( 4 ) PRIMARY KEY,
	Cname VARCHAR ( 20 ) NOT NULL,
	Cpno VARCHAR ( 4 ),
	Ccredit SMALLINT,
	FOREIGN KEY ( Cpno ) REFERENCES Course ( Cno ) 
);

CREATE TABLE `S_T`.`SC` (
	Sno VARCHAR ( 10 ),
	Cno VARCHAR ( 4 ),
	Grade SMALLINT,
	PRIMARY KEY ( Sno, Cno ),
	FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ),
	FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) 
);

如果完整性约束条件涉及到该表的多个属性列，则需要定义在表级上。

数据类型
关系模型中一个很重要的概念是域。每一个属性来自一个域，它的取值必须是域中的值。在SQL中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及长度。MySQL常用数据类型如下：

数据类型含义
TINYINT一个很小的整数（1字节）
SMALLINT短整数（2字节）
INT ，INTEGER长整数（4字节）
BIGINT大整数（8字节）
BOOL，BOOLEAN是TINYINT(1)的同义词，0代表false，1代表true
DECIMAL[(M[,D])]，DEC[(M[,D])]，NUMERIC[(M[,D])]，FIXED[(M[,D])精确定点数字，M为总位数(精度)，D为小数点后位数(刻度)。小数点和(负数)-符号在M中不计算。如果D为0，值没有小数点或小数部分。DECIMAL的最大位数(M)是65。支持的最大小数(D)是30。如果省略D，则默认为0。如果省略M，则默认为10。
FLOAT单精度浮点数，建议不指定精度和位数
DOUBLE双精度浮点数，建议不指定精度和位数
DATE日期，以'YYYY-MM-DD'的格式显示DATE值，但允许使用字符串或数字将值分配给DATE列。
DATETIME[(fsp)]日期和时间的组合，以“YYYY-MM-DD hh:mm:ss[.fraction]”的格式显示DATETIME值，但是允许对DATETIME列分配值，可以是字符串，也可以是数字。可以指定0到6之间的可选fsp值来指定分数秒精度。值为0表示没有小数部分。如果省略，默认精度为0。
TIMESTAMP[(fsp)]时间戳，时间戳值存储为从纪元('1970-01-01 00:00:00' UTC)以来的秒数。
TIME[(fsp)]时间，以'hh:mm:ss[.fraction]'格式显示TIME值，但允许使用字符串或数字分配TIME列的值。
YEAR[(4)]4位数字格式的一年。MySQL以YYYY格式显示YEAR值，但允许使用字符串或数字将值分配给YEAR列。取值范围为1901 ~ 2155或0000。
CHAR[(M)]一个固定长度的字符串，存储时总是用空格右填充到指定长度。M表示列长度，以字符为单位。M取值范围是0 ~ 255。如果省略M，则长度为1。
VARCHAR(M)一个变长字符串。M表示字符的最大列长。取值范围是0 ~ 65,535。
BINARY[(M)]BINARY类型类似于CHAR类型，但存储的是二进制字符串。可选长度M表示列的长度(以字节为单位)。如果省略，M默认为1。
VARBINARY(M)VARBINARY类型类似于VARCHAR类型，但存储的是二进制字符串。M表示以字节为单位的最大列长度。
BLOB[(M)]BLOB是一个二进制大对象，可以保存可变数量的数据。这四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们的区别仅在于它们可以保存的值的最大长度。
TEXT[(M)]四种文本类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型，具有相同的最大长度和存储需求。
ENUM('value1','value2',...)枚举。一个只能有一个值的字符串对象，从“value1”，“value2”，…， NULL或特殊的 'error value' 。ENUM值在内部表示为整数。一个ENUM列最多可以有65535个不同的元素。
SET('value1','value2',...)SET是一个字符串对象，它可以有0个或多个值，每个值必须从创建表时指定的允许值列表中选择。由多个SET成员组成的SET列值用逗号(，)分隔。其结果是SET成员值本身不应该包含逗号。SET值在内部表示为整数。一个SET列最多可以有64个不同的成员。

模式与表

每个基本表都属于一个模式，一个模式包含多个基本表。可以在定义基本表时指定其所属模式。

CREATE TABLE `模式名`.`表名` (...);

修改基本表

使用ALTER TABLE 语句修改基本表，基本格式如下

ALTER TABLE 表名 
	[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
	[ADD <表级完整性约束>]
	[DROP [COLUMN] <列名>]
	[DROP CONSTRAINT <完整性约束>]
	[MODIFY [COLUMN] <列名><数据类型>]

向Student表增加入学时间列，数据类型为DATE：

ALTER TABLE Student ADD COLUMN S_entrance DATE;

将年龄的数据类型改为INT：

ALTER TABLE Student MODIFY COLUMN Sage INT;

课程名称添加唯一约束：

ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

使用DROP TABLE语句删除基本表，基本格式如下

DROP TABLE tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

在MySQL中，RESTRICT和CASCADE关键字什么也不做。它们可以使从其他数据库系统进行移植变得更容易。

索引的建立与删除

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书的目录索引，能快速定位到需要查询的内容。常见的索引包括B+树索引，HASH索引等。索引虽然能加快数据库查询，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，要根据实际情况有选择的创建索引。

建立索引

在SQL中，使用CREATE INDEX语句创建索引，其一般格式为

（MySQL中CREATE INDEX映射到ALTER TABLE语句来创建索引）

CREATE [UNIQUE] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    
index_type: USING {BTREE | HASH}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]

为学生表创建学号升序唯一索引，课程表的课程号升序唯一索引，SC表学号升序和课程号降序唯一索引

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno ASC);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

修改索引

如果需要对已经创建的索引进行重命名，可以使用如下语句

ALTER TABLE 表名 RENAME INDEX 旧索引名 TO 新索引名;

删除索引

删除索引使用DROP INDEX语句，其一般格式为

DROP INDEX index_name ON tbl_name

数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。

数据查询

数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询，其一般格式为

SELECT
    [ALL | DISTINCT ]
    select_expr [, select_expr] ...
    [FROM table_references]
    [WHERE where_condition]
    [GROUP BY col_name, ...]
    [HAVING where_condition]
    [ORDER BY col_name [ASC | DESC], ...]

单表查询

单表查询是指仅涉及一个表的查询。

选择表中的若干列

选择表中的全部或部分列即关系代数的投影运算。

查询指定列：例如查询全体学生的学号和姓名

SELECT Sno, Sname FROM Student;

查询全部列：例如查询全体学生详细记录

SELECT * FROM Student;

查询经过计算的值：例如查询全体学生的出生年份

SELECT Sno,Sname, YEAR(NOW()) - Sage FROM Student;

可以通过AS为查询列指定别名，AS可省略：

SELECT Sname, YEAR(NOW()) - Sage AS Sborn FROM Student;

选择表中的若元组

使用DISTINCT消除重复的行：例如查询选修了课程的学生学号

SELECT DISTINCT Sno FROM SC;

使用WHERE子句查询满足条件的元组：

常用查询条件

查询条件谓词
比较=，>，<，>=，<=，!=，<>，<=>
确定范围BETWEEN AND，NOT BETWEEN AND
确定集合IN，NOT IN
字符匹配LIKE，NOT LIKE
空值IS NULL，IS NOT NULL
多重条件（逻辑运算）AND，OR，NOT

使用字符匹配时，通配符%代表任意长度字符，_代表任意单个字符，一般语法格式如下：

[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']

例：查询姓刘的学生姓名、学号和性别

SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';

例：查询以DB_Design开头的课程的课程号和学分

SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB/_Design%' ESCAPE'/';

ORDER BY子句

可以使用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认为升序。对于空值，排序时现实的次序由具体的系统实现来决定。

聚集函数

常用聚集函数

函数说明
COUNT(expr)返回由SELECT语句检索的行中expr的非null值的数量。结果是一个BIGINT值，如果没有匹配的行返回0。COUNT(*)有些不同，因为它返回检索的行数的计数，无论它们是否包含NULL值。
COUNT(DISTINCT expr,[expr...])返回具有不同非null expr值的行数计数。如果没有匹配的行返回0。
SUM([DISTINCT] expr)返回expr的和，如果返回集没有行，SUM()返回NULL。DISTINCT关键字只能用于求和expr的不同值。
AVG([DISTINCT] expr)返回expr的平均值。DISTINCT选项可用于返回expr不同值的平均值。如果没有匹配的行，AVG()返回NULL。
MAX([DISTINCT] expr)返回expr的最大值。DISTINCT关键字可以用来查找expr不同值的最大值，但是，这与省略DISTINCT产生相同的结果。如果没有匹配的行，MAX()返回NULL。对于MAX()， MySQL目前比较ENUM和SET列的字符串值，而不是字符串在集合中的相对位置。这与ORDER BY比较它们的方式不同。
MIN([DISTINCT] expr)返回expr的最小值。DISTINCT关键字可用于查找expr不同值的最小值，但是，这与省略DISTINCT产生相同的结果。如果没有匹配的行，MIN()返回NULL。对于MIN()， MySQL目前比较ENUM和SET列的字符串值，而不是字符串在集合中的相对位置。这与ORDER BY比较它们的方式不同。

GROUP BY子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚合函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每组都有一个函数值。如果分组后还要求按一定的条件对这些组进行筛选，可以使用HAVING短语指定筛选条件。

例：查询选修了三门以上课程的学生学号；

SELECT Sno, COUNT(Cno) cno_count FROM SC GROUP BY Sno HAVING cno_count > 3;

MySQL还支持对GROUP BY使用WITH ROLLUP进行修饰，该修饰符导致汇总输出包含表示更高级别(即超聚合)汇总操作的额外行。

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

添加之后

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

连接查询

连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

等值与非等值连接查询

语法：

SELECT * FROM 表1,表2 WHERE 表1.列名 <比较运算符> 表2.列名

SELECT * FROM 表1 [INNER|CROSS] JOIN 表2 ON 表1.列名 <比较运算符> 表2.列名

连接两个表的条件称为连接条件或连接谓词，当连接运算符为=时，称为等值连接；使用其他运算符为非等值连接。连接谓词中的列名称为连接字段。连接条件中的各字段类型必须是可比的，但名称不必相同。

若在等值连接中把目标列中重复的属性列去掉则为自然连接。

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

外连接

在普通连接中，只有满足连接条件的元组才能作为结果输出。但有些时候，我们需要指定某张表为主体，将主体表中不满足连接条件的元组也一并输出。

语法：

SELECT * FROM 表1 [LEFT,RIGHT] JOIN 表2 ON 表1.列名 <比较运算符> 表2.列名

多表连接

连接操作除了可以是两表连接、自身连接外，还可以是两个以上的表进行连接，称为多表连接。

嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询快嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

例如：

SELECT
	Sname /* 外层查询/ 父查询*/
FROM
	Student 
WHERE
	Sno IN ( 
        SELECT Sno FROM SC WHERE Cno = 2  /* 内层查询/ 子查询*/
    );

上层的查询块称为外层查询或父查询，下层的查询块称为内层查询或子查询；SQL语言允许多层嵌套查询；

带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以带有谓词IN是嵌套查询中最经常使用的谓词。

例如，查询选修了课程名为“信息系统”的学生学号和姓名：

SELECT Sno, Sname FROM	Student WHERE	Sno IN ( SELECT Sno FROM SC WHERE Cno = ( SELECT Cno FROM Course WHERE Cname = '信息系统' ) );

在上面的例子中，子查询的条件不依赖于父查询，称为不相关子查询。上面的例子也可以使用连接查询替代，如下：

SELECT s.Sno,	s.Sname FROM Student s 	INNER JOIN SC sc ON s.Sno = sc.Sno 	INNER JOIN Course c ON sc.Cno = c.Cno WHERE c.Cname = '信息系统';

实际应用中，能够用连接运算表达式的尽可能采用连接运算。

带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询的结果是返回单个值时，可以使用= 、>、<、>=、<=、<>、!=、<=>等比较运算符。

例如，找出每个学生超过他选修课程平均成绩的课程号。 找出每个学生超过他选修课程平均成绩的课程号：

SELECT sc1.Sno, sc1.Cno FROM SC sc1 WHERE sc1.Grade > ( SELECT AVG( sc2.Grade ) FROM SC sc2 WHERE sc2.Sno = sc1.Sno);

内层查询是求一个学生的平均成绩的，至于是哪个学生的平均成绩要看参数sc1.Sno的值，而该值是与父查询相关的，因此这类查询称为相关子查询。可能的执行过程为：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。

带有ANY（SOME）或ALL谓词的子查询

子查询返回单值时可以使用比较运算符，但返回多值时要用ANY或ALL谓词修饰符。使用ANY或ALL谓词时必须使用比较运算符。

例如：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄：

SELECT Sname,Sage FROM Student WHERE Sdept != 'CS' AND Sage < ANY (SELECT Sage FROM Student WHERE Sdept = 'CS');

可以使用聚集函数替代，通常很少使用。

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。传统上，EXISTS子查询以SELECT *开始，但它也可以以SELECT 5或SELECT column1或任何东西开始。MySQL在这样的子查询中忽略了SELECT列表，所以没有区别。

例如，查询所有选修了1号课程的学生姓名：

SELECT Sname FROM Student s WHERE EXISTS (SELECT * FROM SC sc WHERE s.Sno = sc.Sno AND sc.Cno = 1);

使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。MySQL支持UNION操作。

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（derived table）。

语法：

SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

数据更新

数据更新操作有三种：新增、修改、删除，在SQL中有相应的三类语句。

插入数据

插入元组

语法：

INSERT INTO < 表名> [(< 属性列1>[,< 属性列2 >…)] VALUES (< 常量1> [,< 常量2>]… );

插入子查询结果

语法：

INSERT INTO < 表名> [(< 属性列1> [,< 属性列2>… )] 子查询;

修改数据

语法：

UPDATE < 表名> SET < 列名>=< 表达式>[,< 列名>=< 表达式>]… [WHERE < 条件>];

删除数据

语法：

DELETE FROM < 表名> [WHERE < 条件>];

空值的处理

空值的判断

判断一个属性的值是否为空值，使用IS NULL或IS NOT NULL来表示。

空值的约束条件

主键属性不能取空值；

有NOT NULL约束条件的不能取空值。

空值的算数运算、比较运算和逻辑运算

空值与另一个值的算数运算结果为空值。

空值与另一个值的比较运算的结果为UNKNOWN，有UNKNOWN后，传统的二值（TRUE，FALSE）逻辑就扩展蹭了三值逻辑。

例如，找出选修1 号课程的不及格的学生：

SELECT Sno FROM SC WHERE Cno = 1 AND (Grade < 60 OR Grade IS NULL )	;


参考文献：数据库系统概论-第5版[王珊，萨师煊]

site logo

Hi,Developer

© 2025 Bobby Blog