数据库系统概论_第三章第四章知识总结

身影王座

已于 2022-08-30 11:12:01 修改

阅读量955

点赞数

分类专栏：数据库文章标签：数据库

于 2021-12-15 16:24:39 首次发布

本文链接：https://blog.csdn.net/qq_42148307/article/details/121939368

版权

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

这一章知识参见MySQL必知必会系列博客。

第三章、数据定义

3.1 模式的定义与删除

在SQL 中，模式定义语句如下:

CREATE SCHEMA<模式名>AUTHORIZATION<用户名>;

如果没有指定<模式名>，那么<模式名>隐含为<用户名>。

为用户WANG定义一个学生-课程模式S-T：

CREATE SCHEMA""S-T”AUTHORIZATION WANG;

定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

DROP SCHEMA<模式名><CASCADE |RESTRICT>;

其中CASCADE 和RESTRICT两者必选其一。选择了CASCADE（级联)，表示在删除模式的同时把该模式中所有的数据库对象全部删除;选择了RESTRICT（限制)，表示如果该模式中已经定义了下属的数据库对象（如表、视图等)，则拒绝该删除语句的执行。

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

CREATE TABLE<表名>(<列名><数据类型>[列级完整性约束条件]
									[，列名><数据类型>[列级完整性约束条件]]
									…
									[，<表级完整性约束条件>]);

CREATE TABLE Student
(Sno CHAR(9)PRIMARY KEY，/*列级完整性约束条件*/
Sname CHAR(20)UNIQUE，/* Sname取唯一值*/
Ssex CHAR(2)，
Sage SMALLINT，
Sdept CHAR(20));

建立学生选课表SC。

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student */
FOREIGN KEY (Cno) REFERENCES Course(Cno) /*表级完整性约束条件，Cno是外码,被参照表是Course*/
);

ALTER TABLE语句修改基本表，其一般格式为
				ALTER TABLE<表名>
				[ADD[COLUMN]<新列名><数据类型>[完整性约束]]
				[ADD<表级完整性约束>]
				[DROP [COLUMN]<列名>[CASCADE RESTRICT]]
				[DROP CONSTRAINT<完整性约束名>[RESTRICT |CASCADE ]]
				[ALTER COLUMN<列名><数据类型>];

向Student表增加“入学时间”列，其数据类型为日期型：

ALTER TABLE Student ADD S_entrance DATE;

不论基本表中原来是否已有数据，新增加的列一律为空值。

将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数：

ALTER TABLE Student 
ALTER COLUMN Sage INT;

增加课程名称必须取唯一值的约束条件：

ALTER TABLE Course 
ADD UNIOUE(Cname);

当某个基本表不再需要时，可以使用 DROP TABLE语句删除它。其一般格式为:

DROP TABLE<表名>[RESTRICT|CASCADE];

3.3 索引的建立与删除

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

CREATE [UNIQUE][CLUSTER] INDEX<索引名>ON<表名>(<列名>[<次序>][，<列名>[<次序>]]…);

其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC(升序)或DESC（降序)，默认值为ASC。

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。

CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

ALTER INDEX<旧索引名>RENAME TO<新索引名>;

将SC表的SCno索引名改为SCSno。

ALTER INDEX SCno RENAME TO sCSno;

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

DROP INDEX<索引名>;

3.4 数据查询

3.4.1 单表查询

SELECT [ALL  DISTINCT]<目标列表达式>[;目标列表达式]…
FROM<表名或视图名>[,<表名或视图名>…]|(<SELECT 语句>)[AS]<别名>
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC | DESC]];

如果有GROUPBY子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带 HAVING短语，则只有满足指定条件的组才予以输出。

如果有ORDER BY子句，则结果表还要按<列名2>的值的升序或降序排序。
在这里插入图片描述
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

查询计算机科学系(CS)、数学系(MA）和信息系(IS）学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'CS','MA','IS');

谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下:

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

%(百分号)代表任意长度（长度可以为0）的字符串。例如 a%b表示以a开头，以b结尾的任意长度的字符串。
_(下横线)代表任意单个字符。

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

逻辑运算符AND 和OR可用来连接多个查询条件。AND 的优先级高于OR，但用户可以用括号改变优先级。

聚集函数：

COUNT(*)：统计元组个数
COUNT( [DISTINCT|ALL]<列名>)：统计一列中值的个数
SUM( [DISTINCT|ALL]<列名>)：计算一列值的总和(此列必须是数值型)
AVG( [DISTINCT|ALL]<列名>)：计算一列值的平均值（此列必须是数值型)
MAX( [DISTINCT|ALL]<列名>)：求一列值中的最大值
MIN( [DISTINCT|ALL]<列名>)：求一列值中的最小值

计算选修1号课程的学生平均成绩。

SELECT AVG(Grade)
FROM sc
WHERE Cno='1 ';

求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)
FROM sC
GROUP BY Cno:

3.4.2 连接查询

SELECT Student.* ,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

左外联结：

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Gradc
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

查询每个学生的学号、姓名、选修的课程名及成绩。本查询涉及三个表，完成该查询的SQL语句如下:

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

3.4.3 嵌套查询

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

找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade >-(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

带有ANY (SOME)或ALL谓词的子查询
在这里插入图片描述
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

SELECT Sname,SageFROM Student
WHERE Sage<ANY (SELECT Sage
FROM Student
WHERE Sdept=' CS ')
AND Sdept <> 'CS';
/*注意这是父查询块中的条件*/

带有EXISTS谓词的子查询:

SELECT SnameFROM StudentWHERE EXISTS
(SELECT*FROM SC
WHERE Sno=Student.Sno AND Cno='1');

3.4.4 集合查询

集合操作主要包括并操作 UNION、交操作 INTERSECT和差操作EXCEPT。
查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT *
FROM Student
WHERE Sdept='CS
UNION
SELECT *
FROM Student
WHERE Sage<=19;

3.5 数据更新

插入元组：

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

插入子查询的结果：

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

对每一个系，求学生的平均年龄,并把结果存入数据库。

INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage) 
FROM Student
GROUPBY Sdept;

修改操作又称为更新操作,其语句的一般格式为：

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

带子查询的修改语句：
将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS' );

删除数据：

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

删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
( SELETE Sno
FROM Student
WHERE Sdept= 'CS" );

3.6 视图

建立视图：
CREATE VIEW<视图名>[(<列名>[,<列名>]…)]
AS<子查询>
[WITH CHECK OPTION];

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='TS'
WITH CHECK OPTION;

由于在定义IS_Student视图时加上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdent='TS。

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

删除视图：

DROP VIEW<视图名>[CASCADE];

在信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

更新视图：
更新视图是指通过视图来插入(INSERT)、删除（DELETE）和修改（UPDATE）数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。

将信息系学生视图IS_Student中学号为“201215122”的学生姓名改为“刘辰”。

UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';

DB2规定：

若视图是由两个以上基本表导出的,则此视图不允许更新。
若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT 和 UPDATE操作，但允许执行DELETE操作。
若视图的字段来自聚集函数，则此视图不允许更新。
若视图定义中含有GROUP BY子句，则此视图不允许更新。
若视图定义中含有DISTINCT短语，则此视图不允许更新。
若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。

视图的作用：

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

第四章、数据库的安全性

4.1 授权与回收

GRANT<权限>[,权限]…
ON<对象类型><对象名>[,<对象类型><对象名>]…
TO<用户>[<用户>]…
[WITH GRANT OPTION];

SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。
在这里插入图片描述
把查询Student表的权限授给用户U1。

GRANT SELECT
ON TABLE Student
TO UI;

把对 Student表和l Course表的全部操作权限授予用户U2和U3。

GRANTALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

把对表SC的查询权限授予所有用户。

GRANT SELECT
ON TABLE SC
TO PUBLIC;

把查询 Student表和修改学生学号的权限授给用户U4。

GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;

把对表SC的INSERT权限授予U5用户，并允许将此权限再授予其他用户。

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

REVOKE<权限>[,<权限>]…
ON<对象类型><对象名>[,对象类型><对象名>…
FROM<用户>[<用户>]…
[CASCADE|RESTRICT];

把用户U4修改学生学号的权限收回。

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

收回所有用户对表SC的查询权限。

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

把用户U5对SC表的INSERT权限收回。

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;

身影王座

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库系统概论_第三章第四章知识总结

文章目录数据定义模式的定义与删除数据定义模式的定义与删除定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
复制链接

扫一扫

专栏目录