Transcript DB03 关系数据库标准语言-SQL

第三章 关系数据库
标准语言－SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
0
3.1 SQL概述
关系数据库的标准语言—结构化查询语言
(Structured Query Language)，简称SQL。它是
一种非过程化的、功能极强的、通用的数据库
语言，几乎所有的关系数据库管理系统都支持
SQL。
1.SQL数据库的体系结构要点
SQL语言支持关系数据库三级模式结构:
1
3.1 SQL概述
用户
SQ
L
视图1
基本表1
基本表2
存储文件1
外模式
视图2
基本表3
基本表4
模式
存储文件2 存储模式
2
3.1 SQL概述
(1)一个SQL数据库是表的汇集。它用一个或多个SQL模式定
义。一个SQL模式是表和授权的静态定义。
(2)一个SQL表由行的集合构成，一行是列的序列，每列对应
一个数据项。
(3)一个表可以是一个基本表或是一个视图。基本表是实际存
储在数据库中的表，而视图是由基本表或其他视图构成的
表的定义，称为虚表。
(4)一个基本表可以存储在一个或多个存储文件中，一个存储
文件也可存放一个或多个基本表。
(5)用户可以用SQL语句对基本表和视图进行查询等操作，视
图和基本表在用户看来都是关系（即表格）。
(6)SQL用户可以是应用程序，也可以是终端用户。SQL语句
3
可嵌入主语言中使用，也可独立使用。
3.1 SQL概述
2.SQL的组成
主要由四部分组成：
数据定义。这部分也称为“SQL DDL”，用于定
义SQL模式、基本表、视图和索引。
数据操纵。这部分也称为“SQL DML”，分为数
据查询和数据更新两类。
数据控制。这部分包括对基本表和视图的授权，
完整性规则的描述，事务控制等。
嵌入式SQL的使用规定。
4
3.1 SQL概述
3.SQL的特点
 SQL的语言简洁，易学易用
 SQL是一种非过程化语言
 SQL采用面向集合的操作方式
 SQL具有十分灵活和极强的查询功能
 SQL以同一种语法结构提供两种使用方法
5
第三章 关系数据库
标准语言－SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
6
3.2 数据定义
1.定义、删除、修改基本表
SQL数据定义功能包括：
定义基本表(创建、删除、修改三个命令)
定义视图(创建、删除，不提供修改，因为是个虚表)
定义索引(创建、删除，不能修改，因为依附于基本表)
⑴定义基本表
CREATE TABLE <表名>(<列名><数据类型>[列级完整性约
束]
[,<列名> <数据类型>[列级完整性约束]…]
[,<表级完整性约束>]）；
7
3.2 数据定义
⑵修改基本表
ALTER TABLE <表名>
[ADD <新列名> <数据类型>[完整性约束]]
[DROP <完整性约束名>]
[MODIFY <列名> <数据类型>]；
⑶删除基本表
DROP TABLE <表名>；
8
3.2 数据定义
2.建立与删除索引
SQL支持用户根据应用环境需要，在基本表上建
立一个或多个索引，以提供各种存取路径，加快
查找速度。
⑴建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][，<列名>[<次序>]]…)；
⑵删除索引
DROP INDEX <索引名>
9
3.2 数据定义
【例1】建立一个学生关系Student，由学号Sno 、姓名
Sname 、性别Ssex 、年龄Sage 、所在系Sdept五个
属性组成，其中学号不能为空且值是唯一的。
解：CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20),
Ssex CHAR(2),
Sage INT,
Sdept CHAR(15));
10
3.2 数据定义
【例2】向Student表增加“入学时间”列，其数据类型为日
期型。
解： ALTER TABLE Student ADD DATE;
【例3】删除关于学号必须取唯一值的约束。
解： ALTER TABLE Student DROP UNIQUE(Sno);
【例4】为Student表按学号升序建立唯一索引。
解： CREATE UNIQUE INDEX Stusno ON
Student(Sno);
【例5】删除Student表的Stusno索引。
解： DROP INDEX Stusno;
11
第三章 关系数据库
标准语言－SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
12
3.3 查询
数据库查询是数据库的核心操作，SQL语句提
供了SELECT语句进行数据库的查询。该语句
具有灵活的使用方式和丰富的功能，尤其是目
标列表达式和条件表达式，可有多种选择形式。
学会灵活应用SELECT语句组成不同形式的查
询块，是掌握SQL的关键。
SELECT语句格式
一个完整的SELECT语句包括五个子句，其
中前两个子句是必不可少的，其他子句可以省
略。
13
3.3 查询
格式如下：
SELECT [DISTINCT] <目标列表达式> 序列
FROM <基本表名或视图名> 序列
[WHERE <行条件表达式>]
[GROUP BY <列名1> [HAVING <组条件表达式
>]]
[ORDER BY <列名2> [ASC∕DESC]];
14
SELECT [DISTINCT] <目标列表达式> 序列
FROM <基本表名或视图名> 序列
3.3 查询
[WHERE <行条件表达式>]
[GROUP BY <列名1> [HAVING <组条件表达式>]]
[ORDER BY<列名2>[ASC∕DESC]]
SELECT语句执行过程：
根据WHERE子句的行条件表达式，从FROM子
句指定的基本表或视图中找出满足条件的元组，
再按SELECT子句中的目标列表达式，选出元组
中的属性值形成结果表。
如果有GROUP子句，则将结果按<列名1>的值进
行分组，该属性列值相等的元组为一个组，每个
组产生结果表中的一条记录。通常会在每组中进
行聚合操作。若GROUP子句带HAVING短语，则
只有满足指定条件的组才给予输出。
15
3.3 查询
如果有ORDER子句，则结果表还要按<列名2>
的值的升序或降序排序。
SQL的查询语句，很容易看成是关系代数的表
达式。SELECT子句指定作投影运算，当
FROM子句指出多个关系时，则表示要做笛卡
尔积运算，WHERE子句指定做选择运算，当
查询要求做关系代数的自然连接时，则不仅要
在FROM子句中给出多个关系，还必须在
WHERE子句的条件中包含自然连接的条件。
16
3.3 查询
下面通过实例来讨论SELECT的使用方法。假
设已建立三个表：
学生表 S(S#,SN,SE,SA,SD)
课程表 C(C#,CN,PC#,CR)
选修表 SC(S#,C#,G)
1.简单查询
用SELECT语句实现投影运算与选择运算，
一般只用到SELECT语句的一个查询块：
17
3.3 查询
SELECT <目标列表达式>
FROM 表名
WHERE <行条件表达式>
投影运算对应于SELECT子句，其变化方式主
要表现在SELECT子句的<目标列表达式>上。
而选择运算对应于WHERE子句，可通过不同
的 <行条件表达式>，查询满足要求的元组。
大多数情况下，投影与选择运算是混合出现的。
18
3.3 查询
【例1】查询全体学生的姓名，学号，所在系。
解： SELECT SN，S#，SD
FROM S；
【例2】查询考试成绩不及格的学生的学号。
解： SELECT DISTINCT S#
FROM SC
WHERE G＜60；
【例3】查询计算机系或信息系年龄在20岁以下的学生姓名。
解： SELECT SN
FROM S
WHERE (SD=‘计算机’ OR SD=‘信息’) AND SA＜20；
3.3 查询
2.排序查询
通过SELECT语句中的ORDER BY子句，可
以控制查询结果的显示顺序。ORDER BY子句
对指定的一个或多个属性列，可按升序(ASC)或
降序(DESC)重新排列。
【例4】查询选修了C810号课程的学生的学号及成绩,查询
结果按分数的降序排列。
解： SELECT S# ，G
FROM SC
WHERE C# =‘C810’
ORDER BY G DESC；
20
3.3 查询
3.使用聚合函数
聚合函数用于实现数据统计功能，SQL提供
了许多聚合函数，主要包括：
 COUNT([DISTINCT/ALL]*)
统计元组个数
 COUNT([DISTINCT/ALL]<列名>) 统计一列中值的个
数
 SUM([DISTINCT/ALL]<列名>) 计算一列值的总和
 AVG([DISTINCT/ALL]<列名>) 计算一列值的平均值
 MAX([DISTINCT/ALL]<列名>) 求一列值中的最大值
 MIN([DISTINCT/ALL]<列名>) 求一列值中的最小值
21
3.3 查询
【例5】查询选修了课程的学生人数。
解： SELECT COUNT (DISTINCT S#)
FROM SC
【例6】统计C810号课程的学生平均成绩。
解： SELECT AVG（G）
FROM SC
WHERE C# =‘C810’；
22
3.3 查询
4.数据分组查询
使用聚合函数可以实现对关系中的所有查询
的元组进行聚合运算，但在实际应用中，经常
要将查询的结果分组，然后再对每个分组进行
统计。这时就可以利用SELECT语句提供的
GROUP BY子句和HAVING短语来实现分组统
计。
23
3.3 查询
【例7】查询选修了3门以上4学分课程的学生的学号。
解： SELECT S#
FROM SC
WHERE CR =‘4’
GROUP BY S#
HAVING COUNT（*）＞3；
WHERE子句与HAVING短语的根本区别在于作
用的对象不同
 WHERE作用于基本表或视图
 HAVING作用于组，从中选出满足条件的组
24
3.3 查询
5.表的连接查询
数据库的多个表之间一般都存在某种内在的
联系，它们共同提供有用的完整的信息。因此
在查询中，经常涉及到多个表的数据，这就需
要进行连接查询，这是关系数据库中最主要的
查询。其格式一般为：
SELECT 列名1，列名2，…
FROM 表1，表2，…
WHERE 连接条件
25
3.3 查询
【例8】查询每个学生以及选修课程的情况。
解：经分析可知，有关学生情况存储在S表中，而选课
情况存储在SC表中，所以此查询涉及两个表中的数据。
表S与表SC之间的联系是通过两个表都具有的属性S#
实现的。因此，这是一个等值连接。
SELECT S.﹡，SC.﹡
FROM S，SC
WHERE S.S# = SC.S# ；
26
3.3 查询
【例9】查询选修了“C902”号课程，且成绩高于学号为
“BP00204018”的学生成绩的所有学生情况。
解：题目要求查询所有选修了某门课程且成绩高于另一
a1和a2是为SC定义了
指定的也选修了此门课的学生成绩。要得到这个信息，
两个不同的别名,相当
首先要找到那位指定的学生成绩，然后以此为标准，再
于对SC表作自身连接
查找所有超过标准的学生。
SELECT a1.S#，a1.C#，a1.G
FROM SC a1， SC a2
WHERE a1.C# =‘C902’ AND a1.G > a2.G
AND a2.S# =‘BP00204018’
AND a2.C# =‘C902’；
27
3.3 查询
6.嵌套查询
当一个查询是另一个查询的条件时，需要用嵌
套查询来实现。嵌套查询可以使用几个简单的
查询块构造功能强大的复合命令，且结构清晰。
【例10】查询与陈莉同年龄的所有学生。
解：完成这个查询可以首先确定陈莉的年龄，然后再查
找所有与她同龄的学生。
SELETE S#，SN，SA
FROM S
WHERE SA IN
(SELETE SA
FROM S
WHERE SN =‘陈莉’)；
28
3.3 查询
嵌套查询引出子查询的概念，子查询分为三种:
(1)简单子查询：子查询只执行一次，其结果用于
父查询，子查询的查询条件不依赖于父查询。
子查询与父查询之间的连接:
若返回单值(不论单表或多表),用比较运算符
若返回多值,用>ANY,<ALL,IN和NOT IN等与查
询条件一起构造返回一组值的子查询。
(2)相关子查询：子查询的查询条件依赖于外层父
查询的某个属性值，子查询反复执行。
29
3.3 查询
【例11】查询选修了课号为“C108”且成绩高于该课程平
均分的学生成绩表，并由高到低排序。
解：1)在SC中求C108的平均分(这是子查询返回单值,且
只执行一次)
这是简单子查询
2)在SC中求高于平均分的成绩表(这是主查询)
SELETE S#，C#，G
FROM SC
WHERE C# =‘C108’ AND G >
(SELETE AVG(G)
FROM SC
WHERE C# =‘C108’)
30
ORDER BY G DESC；
扫描SC：→Record1
……
3.3 唤醒子查询，把Record1
查询
的C#传给子查询,子查询
根据主查询的C#,计算C#
的平均分并送回主查询。
【例12】查询其成绩比该课程平均成绩高的学生成绩表。
解：在SC中被学生选修的课程不止一门，对每门课程都
要计算平均分，然后再找出成绩高于平均分的学生成绩
表。程序的执行过程如下：
主查询：SELETE S#，C#，G
FROM SC
WHERE G > (待查学生所选课程的平均分)
子查询：SELETE AVG(G)
FROM SC
WHERE C# = (主查询待选行的课程号C#)
31
3.3 查询
最后得到：
这是相关子查询
SELETE S#，C#，G
FROM SC a
WHERE G >
(SELETE AVG(G)
FROM SC b
WHERE a.C# = b.C#)
理解这个相关子查询的关键是别名，出现在主查询和
子查询的FROM语句中，这样同一个表相当于两个表。
32
3.3 查询
(3)带EXIST测试的子查询：EXIST代表存在量词
Ǝ ，用在WHERE中，后面跟子查询，构成一
个条件。当子查询返回值至少有一个时，条件
与刘晨选修同样课
为真(‘T’)；否则条件为假(‘F’)。
程的学生不止一个
刘晨不止选
【例12】查询与‘刘晨’选修了同样课程的学生学号和姓名。
修一门课
分析：1)在S中找出刘晨的S#；
2)在SC中找出刘晨所选的课程；
3)在SC中找出与刘晨选修了同样课程的学生S#；
4)在S中找出相对应的SN。
33
扫描SC：→Record1
用S#与S连接,看是否刘晨选修,若是
则放入结果表.
3.3 查询
→Record2
用S#与S连接, … …
由第1)、2)步得到：
全部扫描完,找出一组刘晨选修的课.
SELETE C#
FROM SC
WHERE EXIST
(SELETE *
FROM S
WHERE SC.S# = S.S# AND SN =‘刘晨’)
34
扫描S：→Record1
用S#与SC连接,看这个学生是否选
修了课程,且选修的课程与刘晨相同,
若是则放入结果表.
3.3 查询
→Record2
SC连接, … …
用S#与
由第3)、4)步得到：
SELETE S#, SN
FROM S
WHERE EXIST
(SELETE *
FROM SC
WHERE S.S# = SC.S# AND C# IN
35
3.3 查询
解: SELETE S#，SN
FROM S a1
WHERE EXIST
(SELETE *
FROM SC b1
WHERE a1.S# = b1.S# AND C# IN
(SELETE C#
FROM SC b2
WHERE EXIST
(SELETE *
FROM S a2
WHERE b2.S#=a2.S# AND a2.SN=‘刘晨’)))
36
第三章 关系数据库
标准语言－SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
37
3.4 数据更新
1.插入数据
SQL用INSERT命令向表中输入数据，有两种方
式:
插入单个元组
INSERT
INTO <表名> [<列名表>]
VALUES (元组值)
插入子查询结果
INSERT
INTO <表名> [<列名表>]
子查询
38
3.4 数据更新
2.删除数据
SQL用DELETE命令删除表中的行，格式为：
DELETE
FROM <表名>
[WHERE <条件表达式>]
该命令只删除表的数据，不删除表的定义。而
DROP命令则不但把表的内容删除，还把表的
结构也一同删除。
39
3.4 数据更新
3.修改数据
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件表达式>]
该命令可修改某一元组值，也可修改多个元组
值，也可把子查询嵌入UPDATE语句中，构造
修改的条件。
40
3.4 数据更新
【例1】将一个记录(BP00204008,陈莉,女,18,信息)插入S中.
解： INSERT
INTO S
VALUES (‘BP00204008’,‘陈莉’,‘女’,18,‘信息’);
【例2】将学生BP00204001的年龄改为21岁。
解： UPDATE S
SET SA = 21
WHERE S# =‘BP00204001’;
【例3】删除所有的学生选课记录。
解： DELETE
FROM SC；
41
第三章 关系数据库
标准语言－SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
42
3.5 视图
视图是一个虚表，就像一个窗口，透过它可以
看到DB中自己感兴趣的数据及其变化。
视图一经定义就像基本表一样被查询、删除，
也可在一个视图上再定义新视图，但视图的更
新操作则有一定的限制。
43
3.5 视图
1.定义视图
CREATE VIEW <视图名>[(列名表)]
AS <子查询语句>
[WITH CHECK OPTION]
2.删除视图
DROP VIEW <视图名>
3.更新视图
可以用INSERT，DELETE，UPDATE三类操
作。
44
3.5 视图
【例1】建立信息系学生的视图，并要求进行修改和插入
操作时仍保证该视图只有信息系学生。
解： CREATE VIEW 信息_S
AS
SELECT S#，SN，SA
FROM S
WHERE SD =‘信息’
WITH CHECK OPTION；
由于在定义信息_S视图时加上了WITH CHECK
OPTION
子句，以后对视图进行插入，修改和删除等操作时，
DBMS会自动加上SD =‘信息’的条件。
45
3.5 视图
【例2】建立信息系选择了9801号课程的学生视图。
解：CREATE VIEW 信息_9801(S#，SN，G)
AS
SELECT S.S#，SN，G
FROM S，SC
WHERE SD =‘信息’ AND
S.S# = SC.S# AND
SC.C# =‘9801’；
由于视图信息_9801的属性列中包括了S关系与SC关
系的同名列S#，所以必须在视图名后面明确说明视图
的各个属性列名。
46
3.5 视图
视图不仅可以建立在一个或多个基本表上，也可以建立在
一个或多个已定义好的视图上，或同时建立在基本表与视
图上。
【例3】建立信息系选修了9801号课程且成绩在90分以上的
学生视图。
解： CREATE VIEW 信息1_9801
AS
SELECT S#，SN，G
FROM 信息_9801
WHERE G >= 90;
47
3.5 视图
【例4】将学生学号及平均成绩定义为一个视图。
解： CREATE VIEW S_G
AS
SELECT S#，AVG(G)
FROM SC
GROUP BY S#;
【例5】删除视图‘信息_9801’。
解： DROP VIEW 信息_9801;
48
3.5 视图
4.查询视图
视图定义后，用户就可以像对基本表进行查询一样对
视图进行查询了。
【例6】在信息系学生视图中找出年龄小于20的学生。
解： SELETE S#，SA
FROM 信息_S
WHERE SA < 20
DBMS执行此查询时，将其与信息_S视图定义中的子
查
询结合起来，转换成对基本表S的查询。
49
3.5 视图
修正后的查询语句为：
SELETE S#，SA
FROM S
WHERE SD =‘信息’ AND SA < 20;
50
 第三章结束 
51