数据库系统概论 第三章 关系数据库标准语言SQL

数据库系统概论 第三章 关系数据库标准语言SQL

3.1 SQL 概述

特点:

  • 统一综合
  • 高度非过程化
  • 面向集合的操作方式
  • 以同一种语法结构提供多种使用方法
  • 语言简洁,易学易用

3.2 学生-课程数据库示例

  • 学生表:Student(Sno, Sname, Ssex, Sage, Sdept)
  • 课程表:Course(Cno, Cname, Cpno, Crcredit)
  • 学生选课表:SC(Sno, Cno, Grade)

3.3 数据定义

四种操作对象:模式、表、视图、索引。

模式、视图不能直接修改,可先删除旧的,再添加新的。

定义基本表

  • <表名>:所要定义的基本表的名字
  • <列名>:组成该表的各个属性(列)
  • <列级完整性约束条件>:涉及相应属性列的完整性约束条件
  • <表级完整性约束条件>:涉及一个或多个属性列的完整性约束条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE <表名> (<列名><数据类型>[<列级完整性约束条件>],
[,<列名><数据类型>[<列级完整性约束条件>]]
...,
[,<表级完整性约束条件>] );

CREATE TABLE teacher (tho CHAR(9) primariy key,
tname varchar(10) unique,
tsex CHAR(3) check (tsex in ('男', '女'),
Sdept varchar(50));

CREATE TABLE course (cho char(4) primary key,
cname varchar(30) unique,
ctype char(6) check (ctype in ('必修','限选','任选')),
ccredit number(2,1) check ((ccredit>0) and (ccredit <= 8)),
cdep varchar(50));

CREATE TABLE class (cno char(4),
tno char(5),
cltime char(2) not null check ((substr(cltime,1,1) in ('1','2','3','4','5')) and (substr(cltime,2,1) in ('1','2','3','4','5'))),
clstu number(3,0))
primary key(cno,tno)
foreign key(cno) references course(cno),
foreign key(tno) references teacher(tno);

一个汉字占3个(UTF-8字符集)或两个(GBK)字节的位置。

删除基本表

1
DROP TABLE <表名> [RESTRICT | CASCADE];
  • RESTRICT:该表的删除是有限制的,该表不能被其他表的约束所引用,不能有视图等
  • CASCADE:该表的删除没有限制,相关的依赖对象一起被删除

修改基本表

  • <表名>:要修改的基本表
  • ADD子句:增加新列和新的完整性约束条件
  • ALTER COLUMN子句:用于修改列名和数据类型
  • DROP COLUMN:用于删除列
  • DROP子句:删除指定的完整性约束条件
1
2
3
4
ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ];
ALTER TABLE <表名> [ ALTER COLUMN <列名> <数据类型> ];
ALTER TABLE <表名> [ DROP COLUMN <列名>];
ALTER TABLE <表名> [ DROP <完整性约束名> ];

一个 alter table 只能带一个字句。

建立索引

  • 加快查询速度的有效手段

  • 语句格式

    1
    2
    CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
    ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
    • 用<表名> 指定要建索引的基本表名字
    • 索引可以建立在该表的一列或多列上,各列名之间用逗号分隔
    • 用<次序>指定索引值的排列次序,升序:ASC,降序:DESC。缺省值:ASC
    • UNIQUE 表明该索引的每一个索引值值对应唯一的数据记录
    • CLSUTER 表示要建立的索引是聚簇索引
  • 唯一值索引

    • 对已经含重复值的属性列不能建 UNIQUE 索引
    • 对某个列建立 UNIQUE 索引后,插入新纪录时 DBMS 会自动检查新纪录在该列上是否取了重复值。这相当于增加了一个 UNIQUE 约束。
  • 聚簇索引

    • 索引项的顺序与表中记录物理顺序一致的索引组织,类似于字典的拼音序。
    • 在一个基本表最多只能建立一个聚簇索引。
    • 用途:对于某些类型的查询,可以提高查询效率

删除索引

1
DROP INDEX <索引名>;

系统从数据字典中删去有关该索引的描述。

3.4 数据查询

语句格式

  • SELECT子句:指定要显示的属性列
  • FROM子句:指定查询对象(基本表或视图)
  • WHERE子句:指定查询条件
  • GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用集函数。
  • HAVING短语:筛选出只有满足指定条件的组
  • ORDER BY子句:对查询结果表按指定列值的升序或降序排序
1
2
3
4
5
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];

单表查询

选择表中的若干列

1
2
3
4
5
6
7
8
9
10
11
# 查询指定列
select Sno,Sname from Student;

# 查询全部列
select * from Student;

# 查询经过计算的值
select Sname,2019-Sage from Student;

# 使用列别名该别查询结果的列标题
select Sname name, Sno id from Student;

选择表中的若干元组

消除取值重复的行
1
select distinct Sno from SC;
查询满足条件的元组

使用 where 子句,常用的查询条件

1
2
3
4
5
6
7
8
# 比较大小
select Sname from Student where Sdept='CS';

# 确定范围
select Sname,Sdept,Sage from Student where Sage between 20 and 23;

# 确定集合
select Sname,Ssex from Student where Sdept in ('CS','MA','IS');
字符匹配
1
[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']
  • <匹配串> 可以是一个完整的字符串,也可以含有通配符 % 和 _。
  • %:任意长度(包括 0)的字符串,a%b 表示以 a 开头, b 结尾的任意长字符串
  • _:代表单个字符,a_b 表示以 a 开头,b 结尾,长度为 3 的任意字符串
1
2
3
select Sname,Sno,Ssex from Student where Sname like '刘%';
select Sname from Student where Sname like '欧阳__';
# 一个汉字占两个字符的位置,所以匹配串欧阳后面需要两个下划线
  • 如果查询的字符串本身就含有通配符 % 或 _,这时用 ESCAPE '<换码字符>' 短语,对通配符进行转移
1
2
# 查询 DB_ 开头,倒数第三个字符为 i 的课程的所有信息
select * from Course where Cname like 'DB\_%i__' ESCAPE '\';
  • 第一个 _ 前面有换码字符 \,所以被转义为普通的 _ 字符,而 i 后面的两个 _ 仍作为通配符
空值查询
1
select Sno,Cno from SC where Grade is NULL; # 不能用 =
多重条件查询

用 AND 和 OR 来联结多个查询条件,AND 的默认优先级高于 OR

1
select Sname from Student where Sdept='CS' AND Sage<20;

ORDER BY 子句

ORDER BY 子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,缺省值为升序。

1
select Sno, Grade from SC where Cno='3' order by Grade DESC;

空值在升序排列中最后显示,降序排列中最先显示。

聚集函数

1
2
select count(*) from Student;
select count(distinct Sno) from SC;

聚集函数除 count(*) 外,都跳过空值而只处理非空值。

GROUP BY 子句

将查询结果按某一列或多列的值分组,值相等的为一组。聚集函数将作用于每一个分组。

分组后要求对分组进行筛选,最终只输出满足指定条件的组,用 having 短语指定筛选条件。

1
select Sno from SC group by Sno having count(*) > 3;
  • where 和 having
    • where 子句作用域基表或视图,从中选择满足条件的元组
    • having 作用域组,从中选择满足条件的组

连接查询

笛卡尔积

关系代数中广义笛卡尔积的实现:

1
select S.*, SC.* from S, SC

等值与非等值连接查询

1
2
3
4
5
6
7
select 属性列表 from 表1,表2,... where [<表1>].<列1><比较运算符>[<表2>].<列2>

# 不去掉重复列
select Student.*, SC.* from Student, SC where Student.Sno = SC.Sno;

# 把目标列中重复的属性列去掉,即自然连接对上例用自然连接完成
select Sntudent.Sno,Same,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno = SC.Cno;

自身连接

表与自身进行连接

1
select First.Cno,Second.Cpno from Course First,Course Second where First.Cpno = SECOND.Cno;

外连接

在等值连接中不符合条件的元组就被舍弃,若要保留住这些数据,用外连接。留住左表中的信息称为左连接,留住右表中的数据称为右连接。

1
2
3
4
5
# 左外连接
select * from (Student Left Outer Join SC on Student.sno = SC.sno);

# 右外连接
select * from (Student Right Outer Join SC on Student.sno = SC.sno);

复合条件连接

where 子句可以有多个条件连接,称为复合条件连接。

1
select Student.Sno,Sname from Student,SC where Student.Sno = SC.Sno and SC.Cno='2' and SC.Grade > 90;

两个以上的表连接称为多表查询

嵌套查询

一个 select-from-where 被称为一个查询块,将一个查询块嵌套在另一个查询块的 where子句或 having 短语的条件中的查询称为嵌套查询

  • 不相关子查询:子查询的查询条件不依赖于父查询
    • 由里向外逐层处理,每个子查询在上一级处理之前求解
  • 相关子查询:子查询的查询条件依赖于父查询
    1. 首先去外层查询的第一个元组,根据它于内层查询相关的属性值处理内层查询,若 where 子句返回值为真,则取此元组放入结果表;
    2. 然后再取外层表的下一个元组;
    3. 重复这一过程,直至外层表全部检查完为止。

带有 IN 谓词的子查询

1
select Sno,Sname,Sdept from Student where Sdept in (select Sdept from Student where Sname='刘晨');

带有比较运算符的子查询

子查询一定要分在比较符之后

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select Sno,Sname,Sdept from Student where Sdept = (select Sdept from Student where Sname='刘晨');

# 相关子查询举例
select Sno,Cno from SC x where Grade >= (select AVG(Grade) from SC y where y.Sno = x.Sno);

# Step1 从外层查询中取出 SC 的一个元组 x,将元组 x 的 Sno 值(200215121)传送给内层查询
select AVG(Grade) from SC y where y.Sno = '200215121';

# Step2 执行内层查询,得到值,再去外层查询
select Sno,Cno from SC x where Grade >= 88;

# Step3 执行外层查询,得出当前元组查询结果

# Step4 从外层查询取出下一个元组,重复该过程

带有 ANY(SOME) 或 ALL 谓词的子查询

符号 意义
> ANY 大于子查询结果中的某个值
> ALL 大于子查询结果中的所有
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
= ALL 等于子查询结果中的所有值(通常没有实际意义)
!=(<>) ANY 不等于子查询结果中的某个值
!=(<>) ALL 不等于子查询结果的任何一个值
1
select Sname,Sage from Student where Sage < ANY (select Sage from Student where Sdept='CS')

用聚集函数实现子查询通常比 ANY 或 ALL 查询效率要高

带有 EXISTS 谓词的子查询

  • 存在量词 \(\exist\)
  • 带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑值 true 或 false
  • 由 EXISTS 引出的子查询,其目标列表达式通常都用 *,因为其只返回 true 或 false,给出列名无意义
1
select Sname from Student where not exists (select * from SC where Sno = Student.Sno and Cno = '1');
  • 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换
  • 所有带 IN 谓词、比较运算符、ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换
  • 用 EXISTS / NOT EXISTS 实现全称量词
  • SQL 中没有全称量词 \(\forall\),但是 \((\forall x)P \equiv \neg(\exist x(\neg P))\)
1
2
3
4
5
6
7
# 查询选修了全部课程的学生
select Sname from Student
where not exists (select * # 对于一个学生不存在
from Course
where not exists # 一门课没有
(select * from SC # 被他选修
where Sno = Student.Sno and Cno = Course.Cno));

集合查询

select 语句的查询结果是元组的集合,所以多个 select 语句的结果可以进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT和差操作 EXCEPT。

参加集合操作的各查询结果的列数必须相同,对应项的数据类型也必须相同。

1
2
select * from Student where Sdept = 'CS' UNION
select * from Student where Sage <= 19;

SELECT 语句的一般格式

1
2
3
4
5
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [,<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY <列名1> [,<列名1’>] ... [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC] [,<列名2’> [ASC|DESC]]...];
  • 目标列表达式
    • *
    • <表名>.*
    • COUNT( [ DISTINCT|ALL ]* )
    • [ <表名>. ] <属性列名表达式> [ , [ <表名>. ] <属性列名表达式> ]...
  • 聚集函数
  • COUNT / SUM / AVG / MAX / MIN ( [ DISTINCT | ALL ] <列名> )
  • where 子句
    • <属性列名> θ <属性列名> / <常量> / [ANY | ALL] (SELECT 语句)
    • <属性列名> [NOT] BETWEEN <属性列名> / <常量> / (SELECT 语句) AND <属性列名> / <常量> / (SELECT 语句)
    • <属性列名> [NOT] IN (<值1>[, <值2> ] ... ) / (SELECT 语句)
    • <属性列名> [NOT] LIKE <匹配串>
    • <属性列名> IS [NOT] NULL
    • [NOT] EXISTS (SELECT 语句)
    • <条件表达式> AND / OR <条件表达式> (AND / OR <条件表达式>)

3.5 数据更新

插入数据

  • INTO 子句
    • 指定要插入数据的表名及属性列
    • 属性列的顺序可与表定义中的顺序不一致
    • 如果没有指定属性列:表示要插入的是一条完整的元组,且属性列属性与表定义中的顺序一致
    • 如果指定部分属性列:插入的元组在其余属性列上取空值 / 默认值
  • VALUES / SELECT 子句
    • 提供的值必须与 INTO 子句匹配
    • 值的个数、值的类型

插入元组

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

插入子查询结果

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

修改数据

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

删除数据

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

3.6 视图

  • 视图是从一个或者几个表(或者视图)到处的表
  • 是一个虚表,与基本表不同,数据库只存放视图的定义,而不存放视图对应的数据,这些数据仍存放再原来的基本表中。当基本表中的数据发生变化,从视图中查询出的数据也随之改变了。
  • 视图定义后
    • 可以和基本表一样被查询,被删除
    • 意义在一个视图之在定义新的视图
    • 对视图的增删改操作有一定限制

定义视图

建立视图

1
2
3
4
5
6
7
CREATE VIEW <视图名>  [(<列名> [, <列名>]...)] AS  <子查询> [WITH  CHECK  OPTION];

Create view 学生成绩单(学号, 姓名, 课程号, 成绩)
As
Select student.sno, sname, cno, grade
From sc, student
Where sc.sno = student.sno;

DBMS 执行 CREATE VIEW 语句时只是把视图的定义存入数据字典,并不执行其中的 SELECT 子查询子句。在对视图查询时,按视图的定义从基本表中将数据查出。

子查询可以是任意复杂的 SELECT 子句,但通常不允许含有 ORDER BY 子句和 DISTINCT 短语。

WITH CHECK OPTION 表示对视图进行增删改操作时,要保证满足视图定义中的谓词条件。

组成视图的属性名有两种方式:全部省略或全部指定。

  • 全部省略:由子查询中 SELECT 目标列中的诸字段组成
  • 必须指定视图的所有列名
    • 某个目标列是集函数或列表达式
    • select 查询目标列为 *
    • 多表连接时选出了几个同名列作为视图的字段
    • 需要在视图中为某个列启用新的更合适的名字

删除视图

1
DROP VIEW <视图名> [CASCADE];
  • 使用 CASCADE,把该视图和由它导出的所有视图一起删除
  • 不使用 CASCADE,由该视图到处的其他视图仍定义在数据字典中,但已不能使用,必须显式删除

查询视图

与查询基本表相同

DBMS 实现视图查询的方法:实体化视图法

  1. 有效性检查:检查所查询的视图是否存在?若存在
  2. 执行视图定义,将视图临时实体化,生成临时表
  3. 查询视图转换为查询临时表
  4. 查询完毕,删除被实体化的视图(临时表)

视图消解法

  1. 进行有效性检查,检查查询的表、视图等是否存在。如果存在,则从数据字典中取出视图的定义
  2. 把视图定义中的子查询与用户的查询结合起来,转换成等价的对基本表的查询
  3. 执行修正后的查询

更新视图

与更新基本表相同

  1. 若视图由多个表导出,则不允许更新。如P120例3在插入数据时会异常。

  2. 视图字段来自表达式或常数,不允许更新。

  3. 视图字段来自集函数则不允许更新。

  4. 分组视图不允许更新。

  5. 视图中含DISTINCT短语,不允许更新。

  6. 视图定义中含嵌套查询,则不允许更新。

  7. 在不允许更新的视图上定义的视图,不允许更新

视图的作用

对视图的操作最终都要转换为对基本表的操作,而且更新时s有多种限制。这是由于:

  1. 视图能够简化用户的操作
  2. 视图使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当的利用视图可以更清晰的表达查询

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×