《数据库系统》第三章 关系数据库标准语言SQL
本章重点
SQL
- 创建/删除表、修改表结构
- 查询
- 视图
- 安全性、完整性(授权)
SQL概述
SQL的特点(理解即可)
- 综合统一
- 高度非过程化(过程化:一步一步告诉它要做啥 非过程化:无需具体告知怎么做,只说“做什么”就行)
- 面向集合的操作方式(对应关系数据库的数据结构——关系)
- 以同一种语法结构提供多种使用方式
- 语言简洁,易学易用
SQL的动词(重点)
SQL功能 | 动词 |
---|---|
数据查询 | SELECT |
数据定义 | CREATE, DROP, ALTER |
数据操纵 | INSERT, UPDATE, DELETE |
数据控制 | GRANT(授权), REVOKE(回收) |
学生-课程数据库
- 学生表:Student(Sno, Sname, Ssex, Sage, Sdept)
- 课程表:Course(Cno, Cname, Cpno, Ccredit)
- 学生选课表:SC(Sno, Cno, Grade)
关系的主码加下划线表示。
数据定义
模式的定义与删除(了解)
模式定义:
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
目前,在CREATE SCHEMA中可以接受CREATE TABLE, CREATE VIEW 和 GRANT子句。
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]
删除模式:
DROP SCHEMA <模式名><CASCADE|RESTRICT>
基本表的定义、删除与修改
SQL语言使用CREATE TABLE语句定义基本表。基本格式如下:
CREATE TABLE <表名> (<列名><数据类型> [列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]]...[,<表级完整性约束条件>])
e.g. 建立学生表Student。
CREATE TABLE Student (
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE, /* UNIQUE Sname取唯一值 */
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
)
e.g. 建立课程表Course。
CREATE TABLE Course (
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4), /* 先修课 */
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno) /* 【背下来】表级完整性约束条件,Cpno是外码,被参照表是Course,被参照列是Cno */
)
e.g. 建立学生选课表SC。
CREATE TABLE SC (
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno, Cno),
FOREIGN KEY Sno REFERENCES Student(Sno),
FOREIGN KEY Cno REFERENCES Course(Cno) /* 最后一个没有逗号 */
)
注:后续的举例中使用的表参照上述定义。
常用数据类型:
- CHAR(n):长度为n的定长字符串
- VARCHAR(n):最大长度为n的变长字符串
- INT:长整数(4字节)
- SMALLINT:短整数(2字节)
- BIGINT:大整数(8字节)
- REAL:取决于机器精度的单精度浮点数
- DOUBLE PRECISION:取决于机器精度的双精度浮点数
- FLOAT(n):可选精度的浮点数,精度至少为n位数字
- BOOLEAN:逻辑布尔量
- DATE:日期,包含年、月、日,格式为YYYY-MM-DD
- TIME:时间,包含一日的时、分、秒,格式为HH:MM:SS
模式与表(了解)
在表名中可以明显地给出模式名。
e.g.
CREATE TABLE "S-T".SC(...);
修改基本表
ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];
e.g. 将年龄的数据类型由字符型改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;
删除基本表
DROP TABLE <表名> [RESTRICT|CASCADE];
RESTRICT和CASCADE的区别(课后习题)
- RESTRICT:表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如CHECK, FOREIGN KEY等),不能有视图,不能有触发器,不能有存储过程或函数等。如果存在这些依赖该表的对象,则此表不能被删除。
- CASCADE(级联删除):表的删除没有限制条件。在删除基本表的同时,相关的依赖对象,例如视图,都将被一起删除。
- 默认情况是RESTRICT。
索引的建立与删除(了解)
- B+
- 哈希
- 聚簇
数据查询(重点)
一般格式:
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...
FROM <表名或视图名> [,<表名或视图名>...] |(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
- SELECT:DISTINCT表示“去重”
- WHERE:可以继续嵌套"SELCT FROM WHERE"结构
- GROUP BY:having是对每个分组的数据进行判断,看它是否符合条件
- ORDER BY:ascend/descend-升序/降序 可以按多个属性排序
执行顺序:
参考:https://blog.csdn.net/qq_40912214/article/details/113564284
- FROM
- WHERE
- GROUP BY (HAVING)
- SELECT [DISTINCT]
- ORDER BY
单表查询
e.g. 查询全体学生的姓名、学号、所在系。
SELECT Sno,Sname,Sdept
FROM Student;
e.g. 查询全体学生的详细记录。
SELECT *
FROM Student
e.g. (查询经过计算的值)查询全体学生的姓名及其出生年份。
SELECT Sname,2023-Sage
FROM Student;
这里的“2023-Sage”是一个计算表达式,用2023减去学生的年龄,结果如下表所示:
Sname | 2023-Sage |
---|---|
李勇 | 1994 |
刘晨 | 1995 |
王敏 | 1996 |
张立 | 1995 |
消除取值重复的行:用DISTINCT消除它们
e.g. 查询选修了课程的学生的学号,要求去重。
SELECT DISTINCT Sno
FROM SC;
不写DISTINCT的时候默认为ALL。
带比较的查询语句
e.g. 查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname, Sage
FROM Student
WHERE Sage<20;
e.g. 查询考试成绩不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
注:这里应当使用DISTINCT,当一个学生存在不及格的课程时,其学号只列一次。
确定范围
使用BETWEEN…AND…和NOT BETWEEN…AND…
e.g. 查询年龄不在20到23岁之间的学生姓名、院系和年龄。
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
使用谓词IN来查找属性值属于指定集合的元组。与其相反的是NOT IN。
e.g. 查询院系为CS、MA和IS学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');
特殊字符
- “%”代表任意长度的字符串。
- “_”代表任意单个字符。
注:
- 若需要使用“%”和“_”,需要配合LIKE使用。
- 数据库字符集为ASCII时一个汉字需要两个“_”;当字符集为GBK时只需要一个“_”。
如果LIKE后面的匹配串中不含通配符,则可以用“=”取代LIKE,用“!=”或“<>”取代NOT LIKE。
e.g. 查询所有姓刘的学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE "刘%";
e.g. 查询名字中第二个字为“阳”的学生的姓名和学号。
SELECT Sname, Sno
FROM Student
WHERE Sname LIKE "_阳%";
要查询的字符串中含有通配符
e.g. 查询DB_Design课程的课程号和学分。
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
ESCAPE '\'表示“\”为换码字符。
涉及空值的查询
e.g. 查询选课没成绩的学生的学号和相对应的课程号。
SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;
多重条件查询
- AND-“且”
- OR-“或”(IN谓词实际上是多个OR运算符的缩写)
e.g. 查询计算机科学系(CS)年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;
ORDER BY子句
使用ORDER BY子句可以对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认升序。
ORDER BY子句中,先给出用于参照排序的属性的名称,再给出排序方式。
e.g. 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
对于空值:
- 按升序排,含空值的元组最后显示
- 按降序排,含空置的元组最先显示
聚集函数(背过)
函数 | 作用 |
---|---|
COUNT(*) | 统计元组个数 |
COUNT([DISTINCT|ALL] <列名>) | 统计一列中值的个数 |
SUM([DISTINCT|ALL] <列名>) | 计算一列值的总和(此列必须是数值型) |
AVG([DISTINCT|ALL] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX([DISTINCT|ALL] <列名>) | 求一列值中的最大值 |
MIN([DISTINCT|ALL] <列名>) | 求一列值中的最小值 |
e.g. 查询学生总人数。
SELECT COUNT(*)
FROM Student;
e.g. 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
e.g. 计算选修1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';
e.g. 查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC, Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
注意不要落下WHERE语句中的第二个条件。
当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值。
聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
GROUP BY子句
GROUP BY子句将查询结果按某一列或多列的值分组,值相等的为一组。
分组后聚集函数将作用于每一个组,即每一组都有一个函数值。
e.g. 求各个课程号及相应的选课人数。
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
该语句对查询结果按Cno的值分组,所有具有相同的Cno值的元组为一组,然后对每一组作用聚集函数COUNT进行计算,以求得该组的学生人数。
查询结果可能是:
Cno | COUNT(Sno) |
---|---|
1 | 22 |
2 | 34 |
3 | 44 |
4 | 33 |
5 | 48 |
如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING短语指定筛选条件。
e.g. 查询选修了三门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno HAVING COUNT(*)>3
解释:先GROUP BY分组,后HAVING限制条件(对每个组的元组进行条件判断)
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或试图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。
e.g. 查询平均成绩大于等于90分的学生学号和平均成绩。
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno HAVING AVG(Grade)>=90;
连接查询
- 等值连接与自然连接的区别:自然连接会删除重复的属性列,且在自然连接中不能用“.*”
- 外连接与自然连接的语法不同(e.g. 试求哪一门课这学期没人选 外连接的ON代替了WHERE并起到连接的作用)
e.g. 查询每个学生及其选修课程的情况。
SELECT SC.*, Student.*
FROM SC, Student
WHERE SC.Sno = Student.Sno;
e.g. 查询每个学生及其选修课程的情况,要求使用自然连接完成。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, SC.Sno, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno;
e.g. 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Grade > 90 AND SC.Cno = '2';
自身连接(难点)
e.g. 查询每一门课程的间接先修课(即先修课的先修课)。
Course表:
FIRST:
Cno | Cname | Cpno | Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | 2 | |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
6 | 数据处理 | 2 | |
7 | PASCAL语言 | 6 | 4 |
SECOND:
Cno | Cname | Cpno | Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | 2 | |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
6 | 数据处理 | 2 | |
7 | PASCAL语言 | 6 | 4 |
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
多表连接
e.g. 查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno, Student.Sname, Course.Cname, SC.Grade
FROM Student, Course, SC
WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno;
嵌套查询
在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
带有IN谓词的子查询
e.g. 查询与“刘晨”在同一个系学习的学生。
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨');
不相关子查询:子查询的查询条件不依赖于父查询
相关子查询:子查询的查询条件依赖于父查询(内层嵌套块必须由外层传参数;内层子查询不能order by)
e.g. 查询选修了课程名为“信息系统”的学生学号和姓名。
SELECT Sno, Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname='信息系统'
)
);
SELECT Student.Sno, Sname
FROM Student, Course, SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Course.Cname='信息系统';
e.g. ⭐ 找出每个学生超过他自己选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
e.g. 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。(带有ANY(SOME)或ALL谓词的子查询)
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY(SELECT Sage
FROM Stuent
WHERE Sdept='CS')
AND Sdept<>'CS';
e.g. 查询所有选修了1号课程的学生姓名。(带有EXISTS谓词的子查询)
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Cno='1' AND SC.Sno=Student.Sno);
注:从外到内执行,对每一行来说,拿出学号来,看是否符合判断条件。带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值true或逻辑假值false。 由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。
与EXISTS相反的还有NOT EXISTS。
e.g. ⭐⭐⭐查询选修了全部课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
理解:不存在任意一个课程,这个学生没选。
难点:全部->双重否定
e.g. ⭐⭐⭐查询至少选修了学生201215122选修的全部课程的学生号码。
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno='201215122' AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));
集合查询
集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。
e.g. 查询计算机科学系的学生以及年龄不大于19岁的学生。(求并集)
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
e.g. 查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
e.g. 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;
基于派生表的查询
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,这时子查询生成的临时派生表成为主查询的查询对象。
理解:(SELECT FROM WHERE)的结构可以放在FROM子句里。
e.g. (难)找出每个学生超出他自己选修课程平均成绩的课程号。
SELECT Sno, Cno
FROM SC, (SELECT Sno,Avg(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;
先求出来每个人的平均成绩,并生成一个临时表。
数据更新
插入数据
插入元组
INSERT
INTO <表名> [(<属性列1>[,<属性列2>]...)]
VALUES(<常量1>[,<常量2>]...);
e.g. 将一个新学生元组(学号:21020007023,姓名:韩翔,性别:男,所在系:CS,年龄:20岁)插入到Student表中。
INSERT
INTO Student (Sno, Sname, Ssex, Sdept, Sage)
VALUES ('21020007023', '韩翔', '男', 'CS', 20);
e.g. 将学生张成民的信息插入到Student表中。
INSERT
INTO Student
VALUES ('201215126', '张成民', '男', 18, 'CS');
注意VALUES中常量与Student表表头的对应关系。
e.g. 插入一条选课记录(‘201215128’, ‘1’)。
INSERT
INTO SC
VALUES ('201215128', '1', NULL);
插入子查询结果
模板:
INSERT
INTO <表名> [(<属性列1>[,<属性列2>...]])
子查询;
e.g. 对每个系,求学生的平均年龄,并把结果放入数据库。
CREATE TABLE Dept_age
(Sdept CHAR(15)
Avg_age SMALLINT);
INSERT
INTO Dept_age(Sdept, Avg_age)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept;
如果没有最后一行的GROUP BY,则求出来的是每个学生的平均年龄。
- 有分组:求小组的平均
- 没分组:求的是每个人的
修改数据
模板:
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]...
[WHERE <条件>];
e.g. 将学生201215121的年龄改为22岁。
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
e.g. 将所有学生的年龄增加1岁。
UPDATE Student
SET Sage=Sage+1;
e.g. 将计算机科学系全体学生的成绩置0。
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
删除数据
模板:
DELETE
FROM <表名>
[WHERE <条件>];
e.g. 删除学号为201215128的学生记录。
DELETE
FROM Student
WHERE Sno='201215128';
e.g. 删除所有的学生选课记录。
DELETE
FROM SC;
e.g. 删除计算机科学系所有学生的选课记录
DELETE
FROM SC
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
空值的处理
所谓空值就是“不知道”或“不存在”或“无意义”的值。
e.g. 缓考的同学不能直接记0分。
e.g. 向SC表中插入一个元组,学生号是“201215126”,课程号是“1”,成绩为空。
INSERT
INTO SC
VALUES ('201215126', '1', NULL);
空值的判断
用IS NULL或IS NOT NULL来表示。
e.g. 从Student表中找出漏填了数据的学生信息。
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
空值与另一个值(包括另一个空值)的比较运算的结果为UNKNOWN。有了UNKNOWN后,传统的逻辑运算中的二值(TRUE, FALSE)逻辑就扩展成了三值逻辑。
x y | x AND y | x OR y | NOT x |
---|---|---|---|
T T | T | T | F |
T U | U | T | F |
T F | F | T | F |
U T | U | U | U |
U U | U | U | U |
U F | F | U | U |
F T | F | T | T |
F U | F | U | T |
F F | F | F | T |
总结(非常好用):
- UNKNOWN取NOT后仍未UNKNOWN
- 按照T-U-F的优先级顺序:AND中取低的,OR中取高的
e.g. 找出选修1号课程的不及格的学生。
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
e.g. 选出选修1号课程的不及格的学生以及缺考的学生。
SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
视图
视图:不存数据,只存定义。
定义视图
模板:
CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];
注:
- 第一行中的属性名可全部省略或全部给出
- 第二行中的子查询一般不含order by子句和distinct短语
- WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。
e.g. 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS';
e.g. 建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。
e.g. 建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)。
CREATE VIEW IS_S1
AS
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Sdept='IS' AND
Student.Sno=SC.Sno AND
SC.Cno='1';
可以创建视图的视图。
e.g. 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade>=90;
删除视图
DROP VIEW <视图名> [CASCADE]
查询视图
使用SELECT-FROM-WHERE结构。
从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称为视图消解。