数据库技术及应用课件汇 下 林育蓓 关系模式范式-大数据时代的数据管理

DatabaseTechnology&Applications数据库技术及应用关系模式范式关系模式的范式关系模式的范式指的是关系模式应该遵循的规范化形式。第一范式第二范式第三范式BC范式第一范式对于关系模式R，如果其每个属性都是简单属性，即每个属性都是不可再分的，则称R属于第一范式，简记为1NF。关系模式的第一范式是关系模式要遵循的最基本的规范化形式。第一范式【例6.17】设有以下关系r，请问r是否属于第一范式？学号姓名电话202113871633李M)H)202113871634章M)【解答】由于在关系r中，电话属性可进一步划分为移动电话（M）和家庭电话（H），因此，关系r不属于第一范式。可采用以下方法将r规范化成第一范式：学号姓名移动电话家庭电话202113871633李凌15912312321020-37648277202113871634章一范式【例6.17】设有以下关系r，请问r是否属于第一范式？学号姓名电话202113871633李M)H)202113871634章M)【解答】由于在关系r中，电话属性可进一步划分为移动电话（M）和家庭电话（H），因此，关系r不属于第一范式。可采用以下方法将r规范化成第一范式：学号姓名电话电话类型202113871633李动电话202113871633李庭电话202113871634章动电话第一范式【例6.17】设有以下关系r，请问r是否属于第一范式？学号姓名电话202113871633李M)H)202113871634章M)【解答】由于在关系r中，电话属性可进一步划分为移动电话（M）和家庭电话（H），因此，关系r不属于第一范式。可采用以下方法将r规范化成第一范式：学号姓名电话202113871633李凌15912312321202113871634章二范式对于函数依赖X→Y，如果存在X的真子集X’，使得X’→Y也成立，则称Y部分依赖于X。对于函数依赖X→Y，如果X的任一真子集X’，都没有X’→Y成立，则称Y完全依赖于X。如果关系模式R为1NF，并且R中的每一个非主属性都完全依赖于R的某个候选关键字，则称R属于第二范式，简记为2NF。

第二范式【例6.18】设有关系模式R（A，B，C）及其上面的函数依赖集F＝｛A→B，B→A，C→A｝，试分析R是否属于第二范式。【解答】因为C→A，A→B，根据Armstrong公理的传递律有C→B，所以有C→ABC，则C是主键，且A和B都完全依赖于C，因此R属于第二范式。

将非2NF的关系模式分解为满足2NF的关系模式集（1）用主属性构成的属性集的每一个子集作为主键构成一个关系模式。（2）把依赖于这些主键的属性加到对应的关系模式中。（3）去掉只有主键构成的关系模式。

第二范式【例6.19】设有关系模式R(学号，学生姓名，课程号，课程名，成绩，任课教师)，试分析R是否属于第二范式。如果R不属于第二范式，请将R分解成满足第二范式的关系模式集。【解答】关系模式R中存在以下函数依赖：学号→学生姓名、课程号→课程名、课程号→任课教师、（学号，课程号）→成绩，因此，关系模式R的主键是（学号，课程号）。而学生姓名、课程名、任课教师等属性部分依赖于该主键，因此关系模式R不属于第二范式。

第二范式【例6.19】设有关系模式R(学号，学生姓名，课程号，课程名，成绩，任课教师)，试分析R是否属于第二范式。如果R不属于第二范式，请将R分解成满足第二范式的关系模式集。【解答（续）】将R分解成满足第二范式的关系模式集，过程如下：

R1（学号，学生姓名）R2（课程号，课程名，任课教师）R3（学号，课程号，成绩）R1（学号）R2（课程号）

R3（学号，课程号）R第三范式如果有函数依赖X→Y，Y→Z成立，且Y→X不成立，Z不是Y的子集，则称Y传递依赖于X。如果关系模式R为2NF，并且R中的每一个非主属性都不传递依赖于R的某个候选关键字，则称R属于第三范式，简记为3NF。

第三范式

【例6.20】设有关系模式R（X，Y，Z）及其上面的函数依赖集F＝｛Y→Z，XZ→Y｝，试分析下列关系模式R是否属于第三范式。【解答】由XZ→Y可知XZ是主键，R中不存在部分依赖和传递依赖，因此，R属于第三范式。第三范式

【例6.21】设有关系模式R(学号，学生姓名，课程号，课程名，成绩，任课教师，教师所属学院)，试分析R是否属于第三范式。如果R不属于第三范式，请将R分解成满足第三范式的关系模式集。【解答】R中存在以下函数依赖：学号→学生姓名、课程号→课程名、课程号→任课教师、任课教师→教师所属学院、（学号，课程号）→成绩，因此，R的主键是（学号，课程号）。而学生姓名、课程名、任课教师等属性部分依赖于该主键，因此R不属于第二范式，也不属于第三范式。第三范式

【例6.21】设有关系模式R(学号，学生姓名，课程号，课程名，成绩，任课教师，教师所属学院)，试分析R是否属于第三范式。如果R不属于第三范式，请将R分解成满足第三范式的关系模式集。【解答（续1）】R1（学号,学生姓名）2NFR2（课程号，课程名，任课教师，教师所属学院）2NFR3（学号，课程号，成绩）2NFRR1（学号,学生姓名）3NFR2（课程号，课程名，任课教师，教师所属学院）2NFR3（学号，课程号，成绩）3NF第三范式

【例6.21】设有关系模式R(学号，学生姓名，课程号，课程名，成绩，任课教师，教师所属学院)，试分析R是否属于第三范式。如果R不属于第三范式，请将R分解成满足第三范式的关系模式集。【解答（续2）】RR5（任课教师，教师所属学院)R4（课程号，课程名，任课教师)3NF3NF

无损连接并保持函数依赖地把关系模式R分解成3NF关系模式集

【例6.22】设有关系模式R(A,B,C,D)及其函数依赖集F={A→B,C→D}，试分析R是否属于第三范式。如果R不属于第三范式，请将R分解为无损连接和保持函数依赖的满足3NF的关系模式集。【解答】经分析可得，R的主键是AC。B和D对主键是部分依赖，因此R不属于3NF。将R分解过程如下：（1）根据最小函数依赖集的定义，可知F就是一个最小函数依赖集。（2）没有一个函数依赖的左右边相加等于关系模式R的所有属性，因此，R需要分解。（3）对于函数依赖A→B，构建一个关系模式R1(A,B)；对于函数依赖C→D，构建一个关系模式R2(C,D)。（4）R的唯一候选键是AC，而R1和R2都不包含AC，因此需要单独构建一个关系模式R3(A,C)。（5）分解结束，得到一个无损连接和保持函数依赖的满足3NF的关系模式集{R1(A,B),R2(C,D),R3(A,C)}。无损连接并保持函数依赖地把关系模式R分解成3NF关系模式集

【例6.23】设有关系模式R（W，X，Y，Z）及其上面的函数依赖集F＝｛W→XY,Y→Z，XZ→Y,X→Y｝，请将R分解为无损连接和保持函数依赖的满足3NF的关系模式集。【解答】（1）根据最小函数依赖集的定义，可知F不是一个最小函数依赖集。因此，首先求F的最小函数依赖集Fmin:把F中右边包含多个属性的函数依赖分解为右边只包含一个属性的函数依赖，得到与F等价的G，G={W→X,W→Y,Y→Z,XZ→Y,X→Y}。删除G中冗余的函数依赖：由于X→Y,根据Armstrong公理的增广律，XZ→YZ成立，可把XZ→Y删除，得到与G等价的函数依赖集H，H={W→X,W→Y,Y→Z,X→Y}。H中各个函数依赖的左部不存在冗余属性，因此，H即为Fmin。无损连接并保持函数依赖地把关系模式R分解成3NF关系模式集

【例6.23】设有关系模式R（W，X，Y，Z）及其上面的函数依赖集F＝｛W→XY,Y→Z，XZ→Y,X→Y｝，请将R分解为无损连接和保持函数依赖的满足3NF的关系模式集。【解答（续）】（2）没有一个函数依赖的左右边相加等于关系模式R的所有属性，因此，R需要分解。（3）对于W→X，构建R1(W,X)；对于W→Y，构建R2(W,Y)；由于W→X和W→Y的左部相等，因此把R1和R2合并得到R3(W,X,Y)。对于Y→Z和X→Y，分别构建R4(Y,Z)和R5(X,Y)。（4）R的唯一候选键是W，R3中包含W，因此不需要单独构建一个只包含W的关系模式。（5）分解结束，得到一个无损连接和保持函数依赖的满足3NF的关系模式集{R3(W,X,Y),R4(Y,Z),R5(X,Y)}。无损连接并保持函数依赖地把关系模式R分解成3NF关系模式集Boyce-Codd范式如果关系模式R为1NF，并且R中的每一个函数依赖X→Y（Y∉X），必有X是R的超键，则称R属于Boyce-Codd范式，简记为BCNF。BCNF要求满足的条件比3NF所要求的更高。如果关系模式R是BCNF的，那么R必定是3NF，反之，则不一定成立。

Boyce-Codd范式

【例6.24】设有关系模式R（X，Y，Z）及其上面的函数依赖集F＝｛Y→Z，XZ→Y｝，试分析下列关系模式R是否属于BCNF。【解答】由XZ→Y可知XZ是主键，因此，对于函数依赖Y→Z，其左边不是R的超键，因此R不属于BCNF。

输入：关系模式R(U)及在其上面的函数依赖集F输出：R的一个分解ρ={R1，R2，…,Rn}，且ρ是无损连接的分解步骤如下：（1）初始化置ρ={R}。（2）如果ρ中所有关系模式都是BCNF，转（4）。（3）如果ρ中有一个关系模式S不是BCNF，那么在S中必能找到一个函数依赖X→A，X不是S的超键且A∉X，把S分解为S1和S2，S1=XA，S2=S-A。转（2）。（4）分解结束，输出ρ。值得注意的是，在步骤（3）中，若存在多个函数依赖X→A，X不是S的超键且A∉X，对这些函数依赖处理的顺序不一样，得到的BCNF关系模式集有可能不一样。把关系模式R无损连接地分解成BCNF关系模式集

【例6.25】设有关系模式R（A,B,C,D,E）及其上面的函数依赖集F＝｛ABC→DE，BC→D,D→E｝，试分析下列关系模式R是否属于BCNF。如果R不属于BCNF，请将R无损连接地分解为满足BCNF的关系模式集。【解答1】R的主键是(A,B,C)，因为BC→D和D→E的左边都不是R的超键，所以R不属于BCNF。把关系模式R无损连接地分解成BCNF关系模式集R(A,B,C,D,E)F={ABC→DE，BC→D,D→E}R1(D,E)F1={D→E}R2(A,B,C,D)F2={ABC→D,BC→D}D→E,D不是键R21(B,C,D)F21={BC→D}R22(A,B,C)F22=𝜑BC→D,BC不是键BCNFBCNFBCNF

【例6.25】设有关系模式R（A,B,C,D,E）及其上面的函数依赖集F＝｛ABC→DE，BC→D,D→E｝，试分析下列关系模式R是否属于BCNF。如果R不属于BCNF，请将R无损连接地分解为满足BCNF的关系模式集。【解答2】R的主键是(A,B,C)，因为BC→D和D→E的左边都不是R的超键，所以R不属于BCNF。把关系模式R无损连接地分解成BCNF关系模式集R(A,B,C,D,E)F={ABC→DE，BC→D,D→E}R1(B,C,D)F1={BC→D}R2(A,B,C,E)F2={ABC→E,BC→E}BC→D,BC不是键BCNF由BC→D和D→E得BC→ER21(B,C,E)F21={BC→E}R22(A,B,C)F22=𝜑BC→E,BC不是键BCNFBCNF各范式间的联系Notice:下面一种比上面一种要求更严格。要符合某一种范式必须也满足它上边的所有范式。

规范化的实质根据语义确定参与数据库的各个属性确定哪些是主属性，哪些是非主属性确定所有的候选关键字并且选定主键找出属性间的函数依赖和多值依赖关系模式分解原则设计出来的关系模式至少要达到3NF的要求无损连接保持函数依赖分解出来的关系模式个数和属性总数越少越好

本章小结函数依赖Armstrong公理最小函数依赖集无损连接的分解保持函数依赖的分解1NF、2NF、3NF、BCNF感谢观看！DatabaseTechnology&Applications数据库技术及应用SQL概述SQL的功能SQL功能动词数据定义CREATEDROPALTER数据更新INSERTUPDATEDELETE数据查询SELECT

数据控制GRANTREVOKE

SQL(StructuredQueryLanguage)是1974年由IBM公司的RayBoyce和DonChamberlin依据Codd关系数据库的12条准则的数学定义提出来的。SQL的特点一体化非过程化面向集合一种语法两种使用方式简洁易学关系数据库的实现（以openGauss为例）连接数据库创建数据库创建表结构建立表间关系向表中输入数据连接openGauss数据库创建数据库创建表结构openGauss常用的数据类型及其使用方法数据类型使用说明大小数值可用来进行算术计算的数字数据。设置“字段大小”属性定义一个特定的数字类型。其中NUMERIC、DECIMAL是任意精度类型。1、2、4或8个字节，与“字段大小”属性定义有关。例如NUMERIC[(p[,s])]，DECIMAL[(p[,s])]，p为总位数，s为小数位数日期/时间用于存储日期和时间值4、8、12、16个字节字符字符串类型指CHAR、CHARACTER、NCHAR、VARCHAR、CHARACTERVARYING、VARCHAR2、NVARCHAR2、CLOB和TEXT。10MB~1GB-8203字节布尔即true或false1字节“学生”表结构“课程”表结构“选修”表结构建立表间关系建立表间关系向表中输入数据——手动输入数据向表中输入数据——从Excel表批量导入数据向表中输入数据——从Excel表批量导入数据向表中输入数据——从Excel表批量导入数据数据定义SQL的数据对象创建数据对象定义撤消更改数据库CREATEDATABASEDROPDATABASEALTERDATABASE基本表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW

索引CREATEINDEXDROPINDEX

结构定义CREATECREATEDATABASE<数据库模式名>CREATETABLE<表名>（<列名><数据类型>[列级完整性约束条件][，<列名><数据类型>[列级完整性约束条件]]……[，<表级完整性约束条件>]）其中[]内的内容是可选项。结构定义CREATE【例7.1】在Navicat中用SQL语句来实现7.1.2节中通过图形化界面创建的数据库和表。

用CREATEDATABASE命令创建“选课管理数据库”（EMS）：结构定义CREATE【例7.1】在Navicat中用SQL语句来实现7.1.2节中通过图形化界面创建的数据库和表。CREATETABLEpublic.课程(

课程号varchar(35)NOTNULL,

课程名varchar(20)NOTNULL,

学时int2NOTNULL,

学分float4NOTNULL,CONSTRAINTCourse_pkeyPRIMARYKEY(课程号));结构定义CREATE【例7.1】在Navicat中用SQL语句来实现7.1.2节中通过图形化界面创建的数据库和表。CREATETABLEpublic.学生(

身份证号varchar(18)NOTNULL,

学号varchar(12)NOTNULL,

姓名varchar(10)NOTNULL,

性别varchar(1)NOTNULLDEFAULT'男'::charactervarying,

班级varchar(20),

生日dateNOTNULL,CONSTRAINT"学生_pkey"PRIMARYKEY(学号),CONSTRAINT"学生_性别_check"CHECK(性别='男’or性别='女'));结构定义CREATE【例7.1】在Navicat中用SQL语句来实现7.1.2节中通过图形化界面创建的数据库和表。CREATETABLEpublic.选修(

课程号varchar(35)NOTNULL,

学号varchar(12)NOTNULL,

成绩int4,CONSTRAINT选修_pkeyPRIMARYKEY(课程号,学号),CONSTRAINTFK_学号FOREIGNKEY(学号)REFERENCESpublic.学生(学号)ONDELETENOACTIONONUPDATENOACTION,CONSTRAINTFK_课程号FOREIGNKEY(课程号)REFERENCESpublic.课程(课程号)ONDELETENOACTIONONUPDATENOACTION);结构更新ALTER增加属性列ALTERTABLE<基本表名>ADD<新列名><数据类型>[完整性约束条件]-<表名>是要更新的基本表名称-ADD子句用于增加新列和新的完整性约束条件【例7.2】在“学生”表中添加一个新的地址属性ADDRESS。ALTERTABLE学生ADDADDRESSVARCHAR（30）；结构更新ALTER删除属性列ALTERTABLE<基本表名>DROP<属性列名>[CASCADE|RESTRAIN]-CASCADE表示在基本表删除某属性列时，所有引用到该属性列的视图和约束也要一起自动删除-RESTRAIN表示在没有视图或约束引用该属性列时，才可以在基本表中删除该列，否则就拒绝删除操作【例7.3】在“学生”表中删除属性列ADDRESS。ALTERTABLE学生DROPADDRESSCASCADE;结构更新ALTER修改属性列ALTERTABLE<基本表名>MODIFY<属性列名><类型>【例7.4】在“学生”表中将学号的长度修改为“12”。ALTERTABLE学生MODIFY学号CHAR（12）；结构更新ALTER补充定义主键ALTERTABLE<表名>ADDPRIMARYKEY（<列名表>）需要指出，被定义为主键的属性列应当是非空和满足唯一性要求的。结构更新ALTER删除主键ALTERTABLE<表名>DROPPRIMARYKEY（<列名表>）或者ALTERTABLE<表名>DROPCONSTRAINT<主键约束名>【例7.5】删除“学生”表中主键“学号”的SQL语句如下。ALTERTABLE学生DROPCONSTRAINT学生_pkeyCASCADE;结构更新ALTER【例7.6】在“学生”表中添加主键“学号”以及在“选修”表中添加外键的SQL语句如下。ALTERTABLE学生ADDPRIMARYKEY(学号);ALTERTABLE选修ADDCONSTRAINTFK_学号FOREIGNKEY(学号)REFERENCESpublic.学生(学号)ONDELETENOACTIONONUPDATENOACTION;结构撤销DROP【例7.7】撤销“学生”表，但要求只有在没有视图或约束引用“学生”表的属性列时才能撤销，否则拒绝撤销。DROPTABLE学生RESTRICT；DROPDATABASE<数据库名>DROPTABLE<基本表名>[CASCADE|RESTRICT]-RESTRICT表示没有视图或约束引用“学生”表的属性列时才能撤销，否则拒绝撤销，意味着要先删除视图或相关约束，再来删除该表。-CASCADE表示删除表的时候会把相关的视图等一并删除。数据查询SELECT基本语法SELECT[ALL|DISTINCT]<属性名>[，<属性名>]…FROM<基本表名或视图名>[，<基本表名或视图名>]…[WHERE<逻辑条件式>][GROUPBY<属性名1>[HAVING<逻辑表达式>]][ORDERBY<属性名2>[ASC|DESC]]单表查询【例7.8】在“选课管理数据库”中查询“课程”表的全部记录情况。SELECT*FROM课程;单表查询【例7.9】在“学生”表中查询学生的学号、姓名和性别。SELECT学号,姓名,性别FROM学生;单表查询【例7.10】（查询指定属性列）查询所有选修了课程的学生的学号。SELECT学号FROM选修；说明：该查询的结果中会有重复的学号。SELECT后加DISTINCT即可去掉重复学号：SELECTDISTINCT学号FROM选修；单表查询【例7.11】查询全体学生的分数都增加10分后的“选修”表。SELECT学号，成绩+10FROM选修；具条件的属性列查询运算符含义集合成员运算IN,NOTIN在集合中,不在集合中字符匹配运算LIKE与-和%进行单个、多个字符匹配空值运算ISNULL,ISNOTNULL为空,不能为空比较运算>,>=,<,,=<,=,<>大于,大于等于,小于,小于等于,等于,不等于逻辑运算AND,OR,NOT与,或,非,具条件的属性列查询【例7.12】在“选修”表中查找不及格的学生的学号。SELECT学号FROM选修WHERE成绩<60;具条件的属性列查询【例7.13】在“选修”表中查找课程号为“1026”的不及格的学生学号。SELECT学号FROM选修WHERE成绩<60AND课程号='1026';具条件的属性列查询【例7.14】在“选修”表中查找成绩在[60,80]这个区间内的记录。SELECT*FROM选修WHERE成绩BETWEEN60AND80;具条件的属性列查询【例7.15】在“选修”表中查找成绩为{60,70,80}的学生。SELECT*FROM选修WHERE成绩IN(60,70,80);查询通配符通配符含

义%包含0个或多个字符_包含1个字符[]指定范围，如[a-d]代表a、b、c和d[^]不属于指定的范围，如[^a-d]代表排除了a、b、c和d以外的其他字符查询通配符【例7.16】显示“学生”表中姓“马”的同学的基本信息。SELECT*FROM学生WHERE姓名LIKE'马%';查询通配符【例7.17】若要查找的马姓同学的姓名只有两个字。SELECT*FROM学生WHERE姓名LIKE'马_';空值查询【例7.18】将“选修”表中有成绩的记录显示出来。SELECT*FROM选修WHERE成绩ISNOTNULL;排序查询【例7.19】在“学生”表中查询学生的基本情况（包括学生的学号、姓名和性别），结果按照学号的降序排列。SELECT学号,姓名,性别FROM学生ORDERBY学号DESC;若将DESC改成ASC或者不写，则查询结果会根据ORDERBY后面的字段升序排列。排序查询【例7.20】在“学生”表中查询学生的基本情况，并按性别的升序显示结果；对于性别相同的记录，再按学号的降序进行排列。SELECT*FROM学生ORDERBY性别ASC,学号DESC;排序查询【例7.21】在“选修”表中查找课程号为“1026”的成绩最高的前三名学生的学号。SELECT学号FROM选修WHERE课程号='1026'ORDERBY成绩DESCLIMIT3;连接查询内连接（INNERJOIN）外连接（OUTERJOIN）连接（LEFTJOIN）右连接（RIGHTJOIN）全连接（FULLJOIN）内连接基本语法SELECT<属性或表达式列表>FROM<表名>[INNER]JOIN<表名>ON<连接条件>/USING<字段1>[WHERE<限定条件>]只有满足给出的连接条件时，相应结果才会出现在结果关系表中。内连接【例7.22】查询选修了课程号为“1025”课程的所有学生学号与姓名。SELECT学生.学号,姓名FROM学生JOIN选修ON学生.学号=选修.学号WHERE课程号=’1025’；SELECT学生.学号,姓名FROM学生JOIN选修USING(学号)WHERE课程号='1025'；SELECT学生.学号,姓名FROM学生,选修WHERE学生.学号=选修.学号AND课程号='1025'；内连接【例7.23】查询修读课程名为“数据库”的所有学生的姓名。SELECT姓名FROM学生JOIN选修ON学生.学号=选修.学号JOIN课程ON选修.课程号=课程.课程号WHERE课程名='数据库';内连接【例7.24】查询至少修读了学生学号为“202101231234”所修读的一门课的学生学号。SELECT选修1.学号FROM选修选修1，选修选修2WHERE选修1.课程号=选修2.课程号AND选修2.学号=‘202101231234’；外连接【例7.25】查询所有学生的基本信息和选课情况。SELECT学号，姓名，生日，班级，身份证号，课程号，成绩FROM学生JOIN选修ON学生.学号=选修.学号；学生李敏没有选修任何课程，由于在“选修”表没有李敏的相应元组，所以查询结果中不会出现她的学号。外连接基本语法SELECT<属性或表达式列表>FROM<表名>LEFT|RIGHT|FULL[OUTER]JOIN<表名>ON<连接条件>/USING<字段1>[WHERE<限定条件>]外连接【例7.26】查询所有学生的基本信息和选课情况。SELECT学生.学号,姓名,班级,身份证号,课程号,成绩FROM学生LEFTJOIN选修ON学生.学号=选修.学号;外连接【例7.27】查询所有课程的基本信息和被选修的情况，包括没有人选修的也要列出。SELECT课程.课程号，课程名，学时，学分，成绩FROM选修RIGHTJOIN课程ON学生.学号=选修.学号；嵌套查询作为WHERE子句中的逻辑表达式，嵌套子查询有三种形式：字段[NOT]IN子查询字段θSOME/ANY/ALL子查询[NOT]EXISTS(子查询)嵌套查询【例7.28】查询修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHERE学号IN (SELECT学号 FROM选修 WHERE课程号=‘1025’)；嵌套查询【例7.29】查询没有修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHERE学号NOTIN (SELECT学号 FROM选修 WHERE课程号=‘1025’)；嵌套查询【例7.30】查询修读课程名为“数据库”的所有学生的姓名。SELECT姓名FROM学生 WHERE学号IN(SELECT学号FROM选修

WHERE课程号IN (SELECT课程号FROM课程 WHERE课程名=‘数据库’))；嵌套查询【例7.31】查询修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHERE学号=SOME (SELECT学号 FROM选修 WHERE课程号=‘1025’)；嵌套查询【例7.32】查询没有修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHERE学号<>ALL (SELECT学号 FROM选修 WHERE课程号=‘1025’)；嵌套查询【例7.33】查询课程号为“1025”的最高分的学生姓名。SELECT姓名FROM学生INNERJOIN选修USING(学号)WHERE课程号=‘1025’AND成绩>=ALL (SELECT成绩

FROM选修

WHERE课程号=‘1025’AND成绩ISNOTNULL)；嵌套查询【例7.34】查询学生“贺易”本期选修课程号“1025”的成绩。SELECT成绩FROM选修WHERE课程号=‘1025’AND学号=（SELECT学号 FROM学生 WHERE姓名=‘贺易’）;嵌套查询【例7.35】查询修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHEREEXISTS (SELECT* FROM选修 WHERE课程号=’1025’AND学生.学号=选修.学号)；嵌套查询【例7.36】查询没有修读课程号为“1025”的所有学生的姓名。SELECT姓名FROM学生WHERENOTEXISTS (SELECT* FROM选修 WHERE课程号=‘1025’AND学生.学号=选修.学号)；嵌套查询【例7.37】查询课程号为“1025”的最高分的学生姓名。SELECT姓名FROM学生INNERJOIN选修选修1ON学生.学号=选修1.学号WHERE课程号=‘1025’ANDNOTEXISTS

（SELECT* FROM选修选修2WHERE课程号=‘1025’AND选修1.成绩<选修2.成绩）；嵌套查询【例7.38】查询学生“贺易”选修课程号“1025”的成绩。SELECT成绩FROM选修WHERE课程号=‘1025’ANDEXISTS

（SELECT* FROM学生 WHERE姓名=‘贺易’AND选修.学号=学生.学号）；聚合函数COUNT函数ICOUNT（[DISTINCT|ALL]*）统计关系中元组个数。COUNT函数IICOUNT（[DISTINCT|ALL]<列名>）统计关系中给定列中属性值个数。SUM函数SUM（[DISTINCT|ALL]<列名>）计算关系中数值型属性值总和。AVG函数AVG（[DISTINCT|ALL]<列名>）计算关系中数值型属性值平均值。MAX函数MAX（[DISTINCT|ALL]<列名>）计算关系中给定属性列中数值型属性值的最大者。MIN函数MIN（[DISTINCT|ALL]<列名>）计算关系中给定属性列中数值型属性值的最小者。聚合函数【例7.39】查询全体学生人数。SELECTCOUNT(*)FROM学生；聚合函数【例7.40】查询学生张怡修读的课程数。SELECTCOUNT(*)FROM学生INNERJOIN选修USING(学号)WHERE姓名=’张怡’；聚合函数【例7.41】查询学号为“202101231234”的学生所修读课程的平均成绩。SELECTAVG(成绩)FROM选修WHERE学号=‘202101231234’；聚合函数【例7.42】查询可供学生选修的课程门数。SELECTCOUNT(*)FROM课程；聚合函数【例7.43】查询已有学生选修的课程门数。SELECTCOUNT(DISTINCT课程号)FROM选修；分组基本语法SELECT[ALL|DISTINCT]<属性名>[，<属性名>]…FROM<基本表名或视图名>[，<基本表名或视图名>]…[WHERE<逻辑条件式>][GROUPBY<属性名1>[HAVING<逻辑表达式>]][ORDERBY<属性名2>[ASC|DESC]]分组【例7.44】给出每门课程的平均成绩。SELECT课程号,AVG(成绩)FROM选修GROUPBY课程号；分组【例7.45】给出每个学生修读课程的门数:SELECT学号,COUNT(课程号)FROM选修GROUPBY学号；分组【例7.46】例7.33选修课程号为“1025”的最高分的学生姓名的查询也可以写为：SELECT姓名FROM学生INNERJOIN选修ON学生.学号=选修.学号WHERE课程号=‘1025’AND成绩=SOME

（SELECTMAX(成绩) FROM选修

WHERE课程号=‘1025’）;分组【例7.47】给出有10个或10个以上学生所修读课程的课程号和学生数。SELECT课程号,COUNT(学号）FROM选修GROUPBY课程号HAVINGCOUNT(*)>=10；数据更新数据更新功能删除插入修改基本语句DELETEFROM<表名>[WHERE<条件>]INSERTINTO<表名>[属性列]…VALUES(属性值1，属性值2，…)或子查询UPDATE<表名>SET属性名=属性值，…[WHERE<条件>]基本语义删除元组插入元组修改属性值数据删除基本语法DELETEFROM<基本表名>WHERE<条件>删除多个元组的值【例7.48】删除学生“刘玉”的记录。DELETEFROM学生WHERE姓名=‘刘玉’；带子查询删除语句【例7.49】删除21医学院全体学生的选课记录。DELETEFROM选修WHERE‘21医学院’=(SELECT班级

FROM学生 WHERE学生.学号=选修.学号）；数据插入基本语法INSERTINTO<表名>(字段列表)VALUES(值列表)或INSERTINTO<表名>(字段列表)子查询数据插入【例7.50】往“课程”表添加入一条课程号为“1032”，课程名为“大学物理”，学时为64，学分为3的记录。INSERTINTO课程(课程号,课程名,学时,学分)VALUES('1032','大学物理',64,3);或INSERTINTO课程VALUES('1032','大学物理',64,3);数据插入【例7.51】将一个学生新记录（身份证号学号202101231255,姓名：陈静，性别：女，生日：2001-12-29）插入到“学生”表中。INSERTINTO学生(身份证号,学号,姓名,性别,生日)VALUES(,'202101231255','陈静','女','2001-12-29')；数据插入【例7.52】创建一张新表“学生平均分”，有两个字段（学号，成绩），将每个学生选修的平均分放至该表中。CREATETABLE学生平均分(学号varchar(12)NOTNULL,成绩int4)；INSERTINTO学生平均分SELECT学号,AVG(成绩)FROM选修GROUPBY学号；数据修改基本语法UPDATE<基本表名>SET<列名>=表达式[，<列名>=表达式]…WHERE<逻辑条件>数据修改【例7.53】将“选修”表里所有记录的成绩字段增加5。UPDATE选修SET成绩=成绩+5;数据修改【例7.54】将“课程”表里课程名为“计算机基础”的课程改名为“大学计算机基础”。UPDATE课程SET课程名='大学计算机基础'WHERE课程名='计算机基础';视图管理视图的作用视图是一张虚表，数据来自由定义视图的查询所引用的表。对视图进行数据更新时实际上是对基本表进行相应的更新（一般对视图的更新操作是有很多限制条件的）。在openGauss中不支持视图的更新。SQL视图管理机制的意义简化用户操作用户可以多角度看待同一数据提供一定的逻辑独立性对数据提供各种角度的安全保护视图创建基本语法CREATEVIEW<视图名>([<列名>[,<列名>]…])AS<子查询>[WITHCHECKOPTION]；//表示用视图进行更新、插入和删除操作时要保证更新的元组满足视图定义中的谓词条件。视图创建【例7.55】创建一个“21计算机学院”的学生视图：CREATEVIEWCS21_SASSELECT*FROM学生 WHERE班级=‘21计算机学院’WITHCHECKOPTION；系统会自动检查或者加上班级=’21计算机学院’的条件视图创建【例7.56】定义学生姓名、修读的课程名及成绩的视图。CREATEVIEWS_C_G(学号,姓名,课程名,成绩)ASSELECT学生.学号,姓名,课程名,成绩FROM学生,课程,选修WHERE学生.学号=选修.学号AND选修.课程号=课程.课程号;视图撤销DROPVIEW<视图名>;【例7.57】撤销已建立的一个视图S_C_G。DROPVIEWS_C_G；查询视图【例7.58】用例7.55中定义的视图CS21_S做查询，查询计算机系中的女学生。SELECT*FROMCS21_SWHERE性别=’女’；SELECT*FROM学生WHERE班级=’21计算机学院’

AND性别=’女’；查询视图【例7.59】在S_C_G视图中查询成绩在85分以上的学生的学号、姓名和课程名称。SELECT学号,姓名,课程名FROMS_C_GWHERE成绩>=85；查询视图【例7.60】查询21计算机学院所有学生的选课情况。SELECT*FROMCS21_SINNERJOIN选修USING(学号);本章小结SQL的数据定义SQL的数据查询SQL的数据更新视图的概念与作用感谢观看！DatabaseTechnology&Applications数据库技术及应用数据库完整性实施数据完整性的必要性正确性（correctness）数据语法的正确性如数值型数据中只能含有数字而不能含有字母有效性（valid）数据是否属于所定义域的有效范围如年龄不能是负数，在0到150之间。相容性（consistency）同一事实的两个数据应当一致如数据库中同时有年龄和出生年份这两个数据，则年龄应该等于当前年份-出生年份防止各类错误数据进入数据库！DBMS完整性控制的功能定义提供完整性约束条件的定义机制，确定要遵从的数据规则检查检查用户发出的操作请求是否违背完整性约束条件处理如果发现用户操作请求与完整性约束条件不符，需要采取一定的动作实体完整性规则的定义通过定义或者修改表结构的时候定义PRIMARYKEY来实现。定义PRIMARYKEY的子句在CREATETABLE命令中的格式为：PRIMARYKEY（<列名序列>）；//一个关系只能有一个PRIMARYKEY，键值非空且唯一实体完整性约束控制的实现【例8.1】新建学生S表（学号，姓名，年龄，身份证号），并定义实体完整性。CREATETABLES(SNOCHAR（8）PRIMARYKEY，/*在属性列级别上定义主键*/SNameCHAR(10),Sageint，SecurityNOCHAR(18));实体完整性约束控制的实现【例8.1】新建学生S表（学号，姓名，年龄，身份证号），并定义实体完整性。CREATETABLES(SNOCHAR（8），SNameCHAR(10),Sageint，SecurityNOCHAR(18),PRIMARYKEY（SNO))；/*在关系级别上定义主键*/实体完整性约束控制的实现【例8.2】新建课程C表（课程号，课程名，学分），并定义实体完整性。CREATETABLEC(CNOCHAR(8),CNameCHAR(10),HourNUMERIC(3),PRIMARYKEY(CNO));/*在关系级别上定义主键*/实体完整性约束控制的实现【例8.3】数据插入违反实体完整性而失败的例子往S表插入两条数据：INSERTINTOSVALUES('S001','小明',19,’200006’);/*插入成功*/INSERTINTOSVALUES('S001','小红',20,’300456’);/*插入失败*/INSERTINTOSVALUES('S002','小红',20,’300456’);/*插入成功*/UPDATESSETSNO='S001'WHERESNAME='小红'/*更新失败*/同样学号的记录已存在同样学号的记录已存在参照完整性规则的定义参照完整性（外键）定义的子句在CREATETABLE命令中一般格式为：FOREIGNKEY（<列名序列>）REFERENCES关系名<目标关系名>|（<列名序列>）

[ONDELETE<ACTION>][ONUPDATE<ACTION>]违反参照完整性的处理方法操作对象相关操作INSERTDELETEUPDATE

被参照表不需要检查根据参照表中外键定义的ONDELETE…（用户显示定义的方式，提供四种：CASCADE、NOACTION

、SETNULL和SETDEFAULT（系统默认的方式NOACTION）根据参照表中外键定义的ONUPDATE…（用户显示定义的方式，提供四种：CASCADE、NOACTION、SETNULL和SETDEFAULT（系统默认的方式NOACTION）参照表违反则拒绝执行不需要检查违反则拒绝执行参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。建立选修SC表（SNO，CNO，Grade）并定义主键和建立参照关系：CREATETABLESC(SNOCHAR(8),CNOCHAR(10),GradeNUMERIC(3),PRIMARYKEY(SNO,CNO),FOREIGNKEY(SNO)REFERENCESS(SNO)ONDELETECASCADE,FOREIGNKEY(CNO)REFERENCESC(CNO)ONUPDATENOACTION);参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（1）往表中插入以下数据数据：INSERTINTOSVALUES('A001','李红',18);INSERTINTOSVALUES('A003','陈诚',18);INSERTINTOCVALUES('C001','C语言');INSERTINTOSCVALUES('A001','C001',95);INSERTINTOSCVALUES('A003','C001',85);参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（2）往SC表（参照关系）中插入数据：INSERTINTOSCVALUES('A001','C002',95);由于C表（被参照关系）中无课程C002，违反了参照完整性，所以C表的更新操作失败，系统拒绝插入。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（3）更新SC表（参照关系）中学号为“A001”，课程号为“C001”的记录：UPDATESCSETSNO='A002'WHERESNO='A001'ANDCNO='C001';由于S表中无学生“A002”，所以SC表的更新操作失败，系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（4）更新S表（被参照关系）中学号为“A001”的记录：UPDATESSETSNO='A002'WHERESNO='A001';在S表更新学生“A001”的学号时，在SC表中还存在着学生“A001”的选课信息，由于没有定义外键SNO的更新操作，ONUPDATE子句默认为NOACTION，故系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（5）更新C表（被参照关系）中课程号为“C001”的记录：UPDATECSETCNO='C002'WHERECNO='C001';在C表更新课程“C001”时，在SC表中还存在着课程“C001”的选课信息，且ONUPDATE子句中定义的是NOACTION，故系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（6）删除S表（被参照关系）中学号为“A001”的记录：DELETEFROMSWHERESNO='A001';在S表中删除学生“A001”时，在SC表中还存在着学生“A001”的信息，所以S表的删除操作违反了参照完整性。由于定义了外键SNO的删除操作为CASCADE，所以系统除了删除S表中学生“A001”的记录之外，还会删除SC表中学生“A001”选修的记录。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（7）删除C表（被参照关系）中课程号为“C001”的记录：DELETEFROMCWHERECNO='C001';在C表删除课程“C001”时，在SC表中还存在着课程“C001”的信息，所以C表的删除操作违反了参照完整性。由于没有定义外键CNO上的删除操作，故默认为ONDELETENOACTION，所以系统拒绝删除课程表记录。用户自定义完整性约束NOTNULL非空DEFAULT

默认值UNIQUE

唯一值CHECK

满足表达式的取值用户自定义完整性约束【例8.5】DEFAULT约束使用实例CREATETABLEEMPL(DnoNUMERIC(2),/*Dno为部门号*/EnoCHAR(8)UNIQUENOTNULL,/*属性Eno取值唯一,而且不能为空*/SalaryNUMERIC(10)DEFAULT8000);/*属性Salary具默认值约束*/用户自定义完整性约束【例8.6】UNIQUE约束使用实例CREATETABLEDEPT(DEPNONUMERIC(2),DnameCHAR(8)UNIQUENOTNULL,/*Dname为候选键*/LocationCHAR(10),PRIMARYKEY(DEPNO));/*在关系级别上定义主键*/用户自定义完整性约束【例8.7】CHECK约束使用实例——在S表关系中限定Gender只能取“male”或“female”：ALTERTABLESADDCOLUMNGenderCHAR(1)CHECK(GenderIN('M','F'));用户自定义完整性约束【例8.8】CHECK约束使用实例——在S表中加入CHECK约束S1——限制学生年龄为18到25岁：ALTERTABLESADDCONSTRAINTS1CHECK(SageBETWEEN18AND25);数据库安全性保护安全性控制的一般模型安全性保护的措施用户身份鉴别基于访问控制技术审计技术用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（1）创建用户student1，密码是student@123：CREATEUSERstudent1WITHPASSWORD"student@123";用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（2）将用户student1的登录密码由student@123修改为abcd@123：ALTERUSERstudent1IDENTIFIEDBY'abcd@123'REPLACE'student@123';是系统提供的最外层安全保护措施，其方法是每个用户在系统中必须有一个标志自己身份的标识符，用以和其它用户相区别。用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（3）使用视图PG_USER来查看当前系统中的用户列表：SELECT*FROMpg_user;用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（4）删除用户student1：DROPUSERstudent1CASCADE;基于访问控制技术访问控制（accesscontrol）就是一个数据库用户访问数据库资源权限的一种规定和管理。是数据库安全保护的主体技术。数据库用户类型角色机制授权回收数据库用户类型数据库用户管理员用户初始用户系统管理员普通用户数据库用户管理【例8.10】添加各类用户的例子（1）在EMS数据库内，创建系统管理员用户admin，密码为admin@123：CREATEUSERadminWITHSYSADMINPASSWORD"admin@123";数据库用户管理【例8.10】添加各类用户的例子（2）创建普通用户student1，密码是student1@123：CREATEUSERstudent1WITHPASSWORD"student1@123";数据库用户管理【例8.10】添加各类用户的例子（3）创建安全管理员security1，密码是security1@123：CREATEUSERsecurity1WITHCREATEROLEPASSWORD"security1@123";数据库用户管理【例8.10】添加各类用户的例子（4）使用视图pg_user来查看当前系统中的用户列表：SELECT*FROMpg_user;数据库用户管理【例8.10】添加各类用户的例子（5）使用视图pg_roles来查看当前系统中的角色列表：SELECT*FROMpg_roles;数据库用户管理【例8.10】添加各类用户的例子（6）使用系统表pg_authid来查看用户属性：SELECT*FROMpg_authidWHERErolsystemadmin='t’;/*该用户是管理员用户*/SELECT*FROMpg_authidWHERErolcreaterole='t’;/*该用户拥有创建角色权限*/数据库用户管理（7）删除student1用户：DROPUSERstudent1;角色机制角色(Role)是一类具有相同数据权限的用户(User)的集合。角色机制角色创建：GRANTROLE<角色名>;角色授权：GRANT<权限>[，<权限>]…ON<数据对象类型><数据对象名>TO<角色名>[，<角色>]…角色机制将角色授予其它用户：GRANT<角色名>[，<角色>]…TO<用户名>[，<用户名>]…[WITHGRANTOPTION]；将角色授予其它角色：GRANT<角色名1>TO<角色名2>；角色机制角色收回：REVOKE<权限>[，<权限>]…ON<数据对象类型><数据对象名>FROM<角色名>[，<角色>]…角色机制授权语句：GRANT{<权限1>，<权限2>…|ALL}[ON<数据对象类型><数据对象名>]TO{<用户/角色>[，用户/角色]…|PUBLIC}[WITHGRANTOPTION]/*表示获得权限的用户可以将其获得的权限继续授权给其它用户*/授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（1）创建老师角色Teacher，密码为teacher@123；创建学生用户Raul，密码为raul@123；创建学生用户White，密码为white@123；创建学生用户Mary，密码为mary@123：CREATEROLETeacherWITHPASSWORD"teacher@123";CREATEUSERRaulWITHPASSWORD"raul@123";CREATEUSERWhiteWITHPASSWORD"white@123";CREATEUSERMaryWITHPASSWORD"mary@123";授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（2）将对“学生”表的查询、更新和插入权授予角色Teacher：GRANTSELECT,UPDATE,INSERTONTABLE学生TOTeacher;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（3）将具有上述权限的角色授予Raul，White和Mary：GRANTTeacherTORaul,White,Mary;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（4）将Teacher赋予White的权限收回：REVOKETeacherFROMWhite;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（5）增加Teacher在“学生”表上的DELETE权限：GRANTDELETEONTABLE学生TOTeacher;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（6）收回Teacher在“学生”表上的UPDATE权限：REVOKEUPDATEONTABLE学生FROMTeacher;授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（1）创建用户Tom，Lily：CREATEUSERTomWITHPASSWORD"Tom@1234";CREATEUSERLilyWITHPASSWORD"Lily@1234";授权语句【例8.12】将“学生”