数据库系统概论复习笔记(三)关系数据库标准语言SQL
教材:数据库系统概论(第五版)
出版社:高等教育出版社
思维导图(考试部分)
第三章 关系数据库标准语言SQL
SQL:结构化查询语言,是关系数据库的标准语言
3.1 SQL概述
SQL的特点
- 综合统一
- 高度非过程化(必须指定存取路径)
- 面向集合的操作方式
- 以同一种语法结构提供两种使用方法
- 语言简洁,易学易用
SQL支持关系数据库三级模式结构
3.3 数据定义
数据库(有的系统称为目录)→ 模式 → 表以及视图、索引等
- 一个关系数据库管理系统的实例(Instance)中可以建立多个数据库
- 一个数据库中可以建立多个模式
- 一个模式下通常包括多个表、视图和索引等数据库对象
3.3.1 数据库模式的定义与删除
这里的模式,不是关系模式,也不是三级模式结构中的模式,指的是数据库管理系统(软件)的一个层次,也是实际的一个用户数据库
定义模式
CREATE SCHEMA <模式名>AUTHORIZATION<用户名>
定义数据库模式实际上定义了一个命名空间
删除模式
DROP SCHEMA <数据库模式名> <CASCADE|RESTRICT>
CASCADE(级联)
删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT(限制)
- 如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。
- 仅当该模式中没有任何下属的对象时才能执行。
3.3.2 基本表的定义、删除与修改
定义基本表
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
常用完整性约束
- 主码约束:PRIMARY KEY
- 唯一性约束:UNIQUE
- 非空值约束:NOT NULL
- 参照完整性约束 FOREIGN KEY
修改基本表
ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ ALTER COLUMN <列名> <数据类型> ];
ALTER TABLE Student ADD S_entrance DATE;
- <表名>:要修改的基本表
- ADD子句:增加新列和新的完整性约束条件
- DROP子句:删除表中的列和删除指定的完整性约束条件
- ALTER COLUMN子句:用于修改列名和数据类型
删除基本表
直接删除属性列:
例:ALTER TABLE Student Drop Scome;
3.3.3 索引的建立与删除
建立索引的目的:加快查询速度
索引类型:
- 顺序文件上的索引
- B+树索引(具有动态平衡的优点)
- 散列(hash)索引(具有查找速度快的特点)
- 位图索引
- 采用B+树
建立索引
CREATE [UNIQUE] [CLUSTER] [NONCLUSTERED] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
- <表名>:要建索引的基本表的名字
- 索引:可以建立在该表的一列或多列上,各列名之间用逗号分隔
- <次序>:指定索引值的排列次序,升序:ASC,降序:DESC,缺省值:ASC
- UNIQUE:此索引的每一个索引值只对应唯一的数据记录
- CLUSTER:表示要建立的索引是聚簇索引
- NONCLUSTERED:创建非聚集索引。缺省是创建非聚集索引
例子
SC表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>
例子
将SC表的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno;
删除索引
DROP INDEX <索引名>;
DROP INDEX <表名>.<索引名>
例子
删除Student表的Stusname索引
DROP INDEX Stusname;
3.3.4 数据字典
数据字典是关系数据库管理系统内部的一组系统表,它记录了数据库中所有定义信息
- 关系模式定义
- 视图定义
- 索引定义
- 完整性约束定义
- 各类用户对数据库的操作权限
- 统计信息等
关系数据库管理系统在执行SQL的数据定义语句时,实际上就是在更新数据字典表中的相应信息
3.4 数据查询
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
- SELECT子句:指定要显示的属性列
- FROM子句:指定查询对象(基本表或视图)
- WHERE子句:指定查询条件
- GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
- HAVING短语:只有满足指定条件的组才予以输出
- ORDER BY子句:对查询结果表按指定列值的升序或降序排序
3.4.1 单表查询
查询仅涉及一个表
选择表中的若干列
查询全部列:SELECT * FROM Student;
选择表中的若干元组
消除取值重复的行
指定DISTINCT关键词,去掉表中重复的行(作用范围是所有目标列)
查询满足条件的元组
- 确定范围
BETWEEN … AND …
NOT BETWEEN … AND …
- 确定集合
IN <值表>
NOT IN <值表>
例:WHERE Sdept IN (‘CS’,'MA’,‘IS’ );
- 字符匹配
[NOT] LIKE ‘<匹配串>’
当匹配模板为固定字符串时:
可以用 = 运算符取代 LIKE 谓词
用 != 或 < >运算符取代 NOT LIKE 谓词
匹配串:
% (百分号) 代表任意长度(长度可以为0)的字符串
_ (下横线) 代表任意单个字符。
[ ]:匹配[ ]中的任意一个字符
[^ ]:不匹配[ ]中的任意一个字符
- 涉及空值的查询
IS NULL
IS NOT NULL
- 多重条件查询
AND和 OR来连接多个查询条件
ORDER BY子句
可以按一个或多个属性列排序
例:ORDER BY Grade DESC;
聚集函数
聚合函数就用来输入多个数据,输出一个数据的
WHERE子句中不能用聚集函数作为条件表达式。聚集函数只能用于SELECT子句和GROUP BY 中的HAVING子句
- 统计元组个数
COUNT(*)
- 统计一列中值的个数
COUNT([DISTINCT|ALL] <列名>)
- 计算一列值的总和(此列必须为数值型)
SUM([DISTINCT|ALL] <列名>)
- 计算一列值的平均值(此列必须为数值型)
AVG([DISTINCT|ALL] <列名>)
- 求一列中的最大值和最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)
GROUP BY子句(分组查询)
- 如果未对查询结果分组,聚集函数将作用于整个查询结果
- 对查询结果分组后,聚集函数将分别作用于每个组
按指定的一列或多列值分组,值相等的为一组
GROUP BY子句的作用对象是查询的中间结果表
3.4.2 连接查询
等值与非等值连接查询
等值连接:连接运算符为=
自身连接
- 一个表与其自己进行连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性,因此必须使用别名前缀
例
查询每一门课的间接先修课(即先修课的先修课)
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
多表连接
查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course/*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
3.4.3 嵌套查询
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
3.4.4 集合查询
种类:
- 并操作UNION
参加UNION操作的各结果表的列数必须相同;对应项的数据类型也必须相同
自动去掉重复元组,如果要保留重复元组,用ALL。
- 交操作INTERSECT
- 差操作EXCEPT
3.4.5基于派生表的查询
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,这时子查询生成的临时派生表成为主查询的查询对象
3.5 数据更新
3.5.1 插入数据
两种插入数据方式
插入元组
INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );
INTO子句
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序可以不一致
- 没有指定属性列:表示要插入的是一条完整的元组,且属性列属性与表定义中的顺序一致
- 指定部分属性列:插入的元组在其余属性列上取空值
例子:将一个新学生元组(学号:201215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。
INSERT
INTO Student
VALUES ('201215128','陈冬','男', 18, 'IS');
插入子查询结果
可以一次插入多个元组
3.5.2 修改数据
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
- 修改指定表中满足WHERE子句条件的元组
- SET子句给出<表达式>的值用于取代相应的属性列
- 如果省略WHERE子句,表示要修改表中的所有元组
例子:将计算机系学生的年龄增加1岁。
UPDATE Student
SET Sage= Sage+1
WHERE sdept='cs';
3.5.3 删除数据
DELETE
FROM <表名>
[WHERE <条件>];
3.7 视图
视图的特点
- 虚表,是从一个或几个基本表(或视图)导出的表
- 只存放视图的定义,不存放视图对应的数据
- 基表中的数据发生变化,从视图中查询出的数据也随之改变
- 对视图数据的操作最终都会转换为对基本表的操作
3.7.1 定义视图
建立视图 CREATE VIEW
CREATE VIEW <视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
组成视图的属性列名:全部省略或全部指定
全部省略:
由子查询中SELECT目标列中的诸字段组成
明确指定视图的所有列名:
- 某个目标列是聚集函数或列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句
行列子集视图
若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,保留了主码
带表达式的视图
带虚拟列的视图
分组视图
带有GROUP BY子句的查询来定义视图
修改视图 ALTER VIEW
ALTER VIEW 视图名 [( 列名[ ,...n ] )]
AS
查询语句
删除视图 DROP VIEW
DROP VIEW <视图名>[CASCADE];
3.7.2 查询视图
略
3.7.3 更新视图
允许对行列子集视图进行更新
3.7.4 视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当的利用视图可以更清晰的表达查询