第3章-关系数据库的标准语言SQL分解课件.ppt

1、湖南工学院计算机系湖南工学院计算机系数据库原理数据库原理Principles of Database第第3章章 关系数据库的标准语言关系数据库的标准语言SQL数据库原理数据库原理第第3章章 关系数据库的标准语言关系数据库的标准语言SQL【本章掌握内容】1、数据定义语言的使用方法 2、数据操作语言的使用方法 【本章了解内容】1、存储过程的编写 2、触发器的编写第第3章章 关系数据库的标准语言关系数据库的标准语言SQLn3.1 SQL概述n3.2 SQL的数据定义n3.3 SQL的数据操纵n3.4 视图n3.5 触发器数据库原理数据库原理3.1 SQL概述概述nSQL最早是1974年由Boyce和

2、Chamberlin提出，并作为IBM公司研制的关系数据库管理系统原型System R的一部分付诸实施的。n现在SQL已经成了关系数据库的标准语言，并且发展了三个主要标准，即ANSI（美国国家标准机构）SQL；对ANSI SQL修改后在1992年采纳的标准，称为SQL-92或SQL2；最近又出了SQL-99，也称SQL3标准。数据库原理数据库原理3.1.1 SQL的特点的特点n1综合统一 n（1）数据定义语言（Data Definition Language，DDL）。DDL用于定义数据库的逻辑机构，是对关系模式一级的定义，包括基本表、视图及索引的定义。n（2）数据查询语言（Data Quer

3、y Language，DQL）。DQL用于查询数据。n（3）数据操纵语言（Data Manipulation Language，DML）。DML用于对关系模式中的具体数据进行增、删、改等操作。n（4）数据控制语言（Data Control Language，DCL）。DCL用于数据访问权限的控制。数据库原理数据库原理n2高度非过程化 n用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。n3用同一种语法结构提供两种使用方式 nSQL语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能够独立地用于联机交互的使用方式，

4、用户可以在终端键盘上直接输入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C）程序中，供程序员设计程序时使用。n4语言简洁，易学易用 数据库原理数据库原理3.1.2 SQL语言的基本概念语言的基本概念数据库的体系结构分为三级，SQL也支持这三级模式结构 n1基本表（Base Table）基本表是模式的基本内容。实际存储在数据库中的表对应一个实际存在的关系。n2视图（View）视图是外模式的基本单位，用户可以通过视图使用数据库中基于基本表的数据。视图是从其它表（包括其它视图）中导出的表，它仅是保存在数据的一种逻辑定义字典中，本身并不独立存储在数据库中，因此视图是一

5、种虚表。n3存储文件存储文件是内模式的基本单位。一个基本表对应一个或多个存储文件，一个存储文件可以存放在一个或多个基本表，一个基本表可以有若干个索引，索引同样存放在存储文件中。存储文件的存储结构对用户来说是透明的。数据库原理数据库原理SQL支持的数据库体系结构支持的数据库体系结构 数据库原理数据库原理3.2 SQL的数据定义的数据定义n通过SQL语言的数据定义功能，可以完成基本表、视图、索引的创建和修改数据库原理数据库原理数据类型数据类型 1）数值型nINTEGER：定义数据类型为整数类型，它的精度（总有效位）由执行机构确定。nSMALLINT：定义数据类型为短整数类型，它的精度由执行机构确定

6、。nNUMERIC(p,s):定义数据类型为数值型，并给定精度p（总的有效位）或标度s（十进制小数点右面的位数）。nFLOAT(p):定义数据类型为浮点数值型，其精度等于或大于给定的精度p。nREAL:定义数据类型为浮点数值型，它的精度由执行机构确定。数据库原理数据库原理nDOUBLE PRECISION:定义数据类型为双精度浮点类型，它的精度由执行机构确定。（2）字符类型nCHARACTER(n):定义数据类型为字符串，并给定串长度（字符数）。nVARCHAR(n):定义可变长度的字符串，其最大长度为n。（3）位串型nBIT(n):定义数据类型为二进制位串，其长度为n。nBIT VARYIN

7、G(n):定义可变长的二进制位串，其最大长度为n。（4）时间型nDATETIME:定义一个日期时间类型，日期和时间数据由有效的日期或时间组成。（5）布尔型nBOOLEAN:定义布尔数，其值可以是TRUE（真）或FALSE（假）。数据库原理数据库原理3.2.1 基本表的定义基本表的定义 CREATE TABLE（列完整性约束，列完整性约束，表级完整性约束）数据库原理数据库原理创建基本表例子创建基本表例子n例3.1 创建员工关系表。CREATE TABLE Employee（Eno CHAR（5），Ename VARCHAR（10），Sex CHAR（2），Marry CHAR（2），Dno CH

8、AR（4）；数据库原理数据库原理n例3.2 创建员工关系表时加入列完整性约束。CREATE TABLE Employee（Eno CHAR（5）NOT NULL UNIQUE，Ename VARCHAR（10）NOT NULL，Sex CHAR（2）NOT NULL，Marry CHAR（2）DEFAULT未，Dno CHAR（4）NULL）；数据库原理数据库原理3.2.2 基本表的修改与删除基本表的修改与删除 ALTER TABLE ADD 列完整性约束DROP COLUMN MODIFY ADD CONSTRAINT DROP CONSTRAINT 数据库原理数据库原理基本表修改例子基本表

9、修改例子n例3.3 在Employee表中增加一个Birth列（出生）。ALTER TALBE EmployeeADD Birth DATETIME NULL；n例3.4 修改Employee表中Marry列为BOOLEAN。ALTER TALBE EmployeeMODIFY Marry BOOLEAN；n例3.5 删除Employee表中新增的Birth列。ALTER TALBE EmployeeDROP COLUMN Birth；数据库原理数据库原理2基本表的删除基本表的删除 n当数据库某个基本表不再使用时，可以将其删除。当一个基本表被删除后，该表中的所有数据连同该表建立的索引都会被删除

10、。但由该表导出的视图的定义仍然存在数据字典当中，只是无法使用。DROP TABLE 数据库原理数据库原理3.2.3 索引的建立和维护索引的建立和维护 为了改善查询性能，可以建立索引。索引是根据表中的一列或若干列按照一定顺序建立的列值，与表中记录行之间具有一一对应关系的辅助表结构。索引属于物理存储的路径概念，而不是用户使用的逻辑概念。建立在多个列上的索引被称为复合索引。有两种重要的索引：n聚集索引（Clustered Index）n非聚集索引（Non-clustered Index）。数据库原理数据库原理聚集索引聚集索引 n聚集索引类似于按姓氏排列数据的电话簿。由于聚集索引规定了数据在表中的物理

11、存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。数据库原理数据库原理非聚集索引非聚集索引 n非聚集索引与书本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息则按另一种顺序存储（这可以由聚集索引规定）。与使用书中索引的方式相似，DBMS在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据 数据库原理数据库原理1创建索引创建索引 CREATE UNIQUE CLUST

12、ER INDEX ON （，）；说明：（1）UNIQUE：规定索引的每一个索引值只对应于表中的唯一记录。（2）CLUSTER：规定此索引为聚集索引。省略CLUSTER则表示创建的索引为非聚集索引。（3）：建立索引时指定列名的索引表是ASC（升序）或DESC（降序）。数据库原理数据库原理创建索引例子创建索引例子n例3.7 在Employee表的属性列Eno上创建一个非聚集索引。CREATE INDEX IDX_DNO_ENOON Employee（Dno ASC，Eno ASC）；n例3.8 在Employee表的属性列Ename上创建一个聚集索引。CREATE CLUSTER INDEX ID

13、X_ENAMEON Employee（Ename ASC）；数据库原理数据库原理2删除索引删除索引 n过多或不当的索引会导致系统低效。用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片，降低系统效率。nDROP INDEX 数据库原理数据库原理3.3 SQL的数据操纵的数据操纵nSQL语言的数据操纵功能主要包括查询（SELECT）、插入（INSERT）、删除（DELETE）和更新（update）四个方面。数据库原理数据库原理3.3.1 数据查询数据查询 nSQL数据查询是SQL语言中最重要、最丰富也是最灵活的内容。SELECT，FROM，WHERE P；n查询的基本

14、结构包括了三个字句：SELECT、FROM、WHERE。数据库原理数据库原理 n（1）SELECT子句，对应关系代数中的投影运算，用于列出查询结果的各属性。n（2）FROM子句，对应关系代数中的广义笛卡尔乘积，用于列出被查询的关系：基本表或视图。n（3）WHERE子句，对应关系代数中的选择谓词，这些谓词涉及FROM子句中的关系的属性，用于指出连接、选择等运算要满足的查询条件。nSQL数据查询的基本结构在关系代数中等价于：A1,A2,An(p(R1R2Rm)数据库原理数据库原理SQL数据查询的一般格式为：数据查询的一般格式为：SELECT ALL|DISTINCT 别名1，别名2FROM 表别名

15、1，表别名2WHERE GROUP BY HAVING ORDER BY ASC|DESC 数据库原理数据库原理1单表无条件查询单表无条件查询 n单表无条件查询是指只含有SELECT子句和FROM子句的查询 SELECT ALL|DISTINCT 别名1，别名2FROM 表别名1，表别名2 数据库原理数据库原理 查询关系中的指定列查询关系中的指定列 n例3.10 查询所有学生的学号、姓名、年龄。SELECT Sno,Sname,AgeFROM Student;n例3.11 查询所有课程的基本情况。SELECT Cno,Cname,CreditFROM Course数据库原理数据库原理 DIST

16、INCT保留字的使用 当查询的结果只包含元表中的部分列时，结果中可能会出现重复列，使用DISTINCT保留字可以使重复列值只保留一个。查询列中含有运算的表达式 SELECT子句的目标列中可以包含带有+、的算术运算表达式，其运算对象为常量或元组的属性。数据库原理数据库原理列名中含有算术表达式例子列名中含有算术表达式例子n【例3.13】查询所有学生的学号、姓名和出生年份。SELECT Sno,Sname,2005-AgeFROM Student 数据库原理数据库原理（4）查询列中含有字符串常量）查询列中含有字符串常量【例3.14】查询每门课程的课程名和学分。SELECT Cname,学分,Cred

17、itFROM Course n这种书写方式可以使查询结果增加一个原关系里不存在的字符串常量列，元组在该列上的每个值就是字符串常量。数据库原理数据库原理（5）查询列中含有集函数）查询列中含有集函数 nCOUNT（*）统计查询结果中的元组个数nCOUNT（）统计查询结果中一个列上值的个数nMAX（）计算查询结果中一个列上的最大值nMIN（）计算查询结果中一个列上的最小值nSUM（）计算查询结果中一个数值列上的总和nAVG（）计算查询结果中一个数值列上的平均值 除COUNT（*）外，其他集函数都会先去掉空值再计算。在前加入DISTINCT保留字，会将查询结果中重复的列去掉后再计算。数据库原理数据库原

18、理列名含有函数运算例子列名含有函数运算例子n【例3.15】COUNT函数的使用。nSELECT COUNT（*）FROM Student 统计学生表中的记录数。nSELECT COUNT（Place）FROM Student 统计学生的籍贯（去掉空值）。nSELECT COUNT（DISTINCT Place）FROM Student 统计学生的籍贯种类数。数据库原理数据库原理2单表带条件查询单表带条件查询 n一般地，数据库中的数据量都非常大，显示表中所有的行是很不现实的，可以在查询的时候根据查询条件对表进行水平分割，使用WHERE子句即可实现。SELECT ALL|DISTINCT 别名1，

19、别名2FROM 表别名1，表别名2WHERE 数据库原理数据库原理（1）使用关系运算表达式的查询）使用关系运算表达式的查询 n使用比较运算符的条件表达式的一般形式为：和 常量值 n例3.17 查询籍贯是湖北的学生信息。SELECT*FROM Student WHERE Slace=湖北 数据库原理数据库原理n例3.18 查询选修了C01号课程且考试及格的学生学号和成绩。SELECT Sno,Grade FROM StudyWHERE Cno=C01 AND Grade=60 数据库原理数据库原理（2）使用特殊运算符）使用特殊运算符 ANSI标准SQL允许在WHERE子句中使用特殊的运算符。运算

20、符含 义 IN、NOT IN 判断属性值是否在一个集合内BETWEENAND、NOT BETWEENAND 判断属性值是否在某个范围中IS NULL、IS NOT NULL 判断属性值是否为空LIKE、NOT LIKE 判断字符串是否匹配数据库原理数据库原理n例3.19 查询籍贯为湖北和福建两地的学生信息。SELECT*FROM StudentWHERE Place IN(湖北,福建)n相当于SELECT*FROM StudentWHERE Place=湖北 OR place=福建)数据库原理数据库原理n例3.20 从Study表中查询考试成绩在85和95之间的学生学号。SELECT SnoF

21、ROM StudyWHERE Grade BETWEEN 85 AND 95SELECT SnoFROM StudyWHERE Grade=85 AND Grade=95 数据库原理数据库原理（3）字符串比较）字符串比较 n在SQL中可以使用关系运算符来进行字符串比较。实际上比较的是它们的词典顺序（如字典顺序或字母表顺序）。n如果a1a2an和b1b2bm是两个字符串，若a1b1或a1=b1且a2b2或a1=b1,a2=b2且a3b3，当nm并且1a2an=b1b2bn时，字符串a1a2anb1b2bm，也就是第一个字符串正好是第二个字符串的前缀。n例如fodderfoo 数据库原理数据库原理

22、nSQL也提供了一种简单的模式匹配功能用于字符串比较，可以使用LIKE和NOT LIKE来实现=和的比较功能 n基本格式为：LIKE/NOT LIKE 数据库原理数据库原理通配符通配符 n字符串常数中通常要使用通配符。在字符串常数中除通配符外的其它字符只代表自己。通配符可以出现在字符串的任何位置。但通配符出现在字符串首时查询效率会变慢。通 配 符 含 义%表示任意长度的字符串_（下划线）表示任意的单个字符 数据库原理数据库原理n例3.21 查询姓王的学生的学号、姓名、年龄。SELECT Sno,Sname,AgeFROM StudentWHERE Sname LIKE 王%n若通配符本身就是字

23、符串常量的内容，则可增加短语ESCAPE，使之转义。如：WHERE sname LIKE To_%ESCAPE，则紧跟在 后面的 _ 不是通配符而是字符串常量的一个字符而已。该查询条件为：Sname属性值以“To_”开头，后面紧跟任意个字符的串。数据库原理数据库原理3分组查询和排序查询分组查询和排序查询 n（1）GROUP BY与HAVING n含有GROUP BY的查询称为分组查询。GROUP BY子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。分组查询一般和查询列的集函数一起使用，当使用GROUP BY子句后，所有的集函数都将是对每一个组进行运算，

24、而不是对整个查询结构进行运算。数据库原理数据库原理n例3.22 查询每一门课程的平均得分。在Study关系表中记录着学生选修的每门课程和相应的考试成绩。由于一门课程可以有若干个学生学习，SELECT语句执行时首先把表Study的全部数据行按相同课程号划分成组，即每一门课程都有一组学生和相应的成绩，然后再对各组执行AVG(grade)SELECT Cno,AVG（Grade）FROM StudyGROUP BY Cno 数据库原理数据库原理n查询课程的平均得分 Cno AVG(Grade)C0175.5 C02 83 C03 75C04 73.5C05 97 数据库原理数据库原理n例3.23 查

25、询被3人以上选修的每一门课程的平均成绩、最高分、最低分。SELECT Cno,AVG（Grade）,MAX（Grade）,MIN（Grade）FROM StudyGROUP BY CnoHAVING COUNT（*）=3n本例中SELECT语句执行时首先按Cno把表Study分组，然后对各组的记录执行AVG(Grade)、MAX(Grade)、MIN(Grade)等集函数，最后根据HAVING子句的条件表达式COUNT(*)=3过滤出组中记录数在3条以上的分组。数据库原理数据库原理（2）排序查询）排序查询 nSELECT子句的ORDER BY子句可使输出的查询结果按照要求的顺序排列。由于是控制

26、输出结果，因此ORDER BY子句只能用于最终的查询结果。基本格式是：nORDER BY ASC|DESC n有了ORDER BY子句后，SELECT语句的查询结果表中各元组将按照要求的顺序排列：首先按第一个值排列；前一个相同的，再按下一个值排列 数据库原理数据库原理n例3.24 查询所有学生的基本信息，并按年龄升序排列，年龄相同的按学号降序排列。SELECT*FROM StudentORDER BY Age,Sno DESC 数据库原理数据库原理4多表查询多表查询 n在数据库中通常存在着多个相互关联的表，用户常常需要同时从多个表中找出自己想要的数据，这就涉及到多个数据表的查询。SQL通过连接

27、查询、并操作、交操作、差操作可以实现关系代数中的运算功能。数据库原理数据库原理（1）连接查询）连接查询 nSQL提供在FROM子句中列出每个关系，然后在SELECT子句和WHERE子句中引用FROM子句中的关系的属性，而WHERE子句中用来连接两个关系的条件称为连接条件。n例3.25 查询籍贯为湖北的学生的学号、选修的课程号和相应的考试成绩。SELECT Student.Sno,Cno,GradeFROM Student,StudyWHERE Student.Sno=Study.Sno AND Place LIKE 湖北数据库原理数据库原理 在等值连接中，目标列可能出现重复的列，例如：SELE

28、CT Student.*,Study.*FROM Student,StudyWHERE Student.Sno=Study.Sno AND Place LIKE 湖北 n这里Student.Sno和Study.Sno是两个重复列 数据库原理数据库原理n 还有一种特殊的连接运算，它不带连接条件，称为乘积运算 SELECT Student.Sno,Cno,GradeFROM Student,Study两个关系的乘积会产生大量没有意义的元组，并且这种操作要消耗大量的系统资源，一般很少使用。数据库原理数据库原理可以进行两个以上的连接。n例3.26 查询籍贯为湖北的学生的姓名、选修的课程名称和相应的考试

29、成绩。SELECT Sname,Cname,GradeFROM Student,Study,CourseWHERE Student.Sno=Study.Sno AND Study.Cno=Course.CnoAND Place LIKE 湖北 数据库原理数据库原理（3）自身连接）自身连接 n有一种连接是一个关系与自身进行的连接，这种连接称做自身连接。SQL允许为FROM子句中的关系R的每一次出现定义一个别名。这样在SELECT子句和WHERE子句中的属性前面就可以加上“别名.”。数据库原理数据库原理n例3.27 查询籍贯相同的两个学生的基本信息。SELECT A.*FROM Student A

30、,Student BWHERE A.Place=B.Placen该例中要查询的内容属于表Student。上面的语句将表Student分别取两个别名A、B。这样A、B相当于内容相同的两个表。将A和B中籍贯相同的元组进行连接，经过投影就得到了满足要求的结果。数据库原理数据库原理（3）并操作）并操作 nSQL使用UNION把查询的结果并起来，并去掉重复的元组，如果要保留所有重复，则必须使用UNION ALL。n【例3.28】查询籍贯是湖北的学生以及姓张的学生的基本信息。SELECT*FROM StudentWHERE Place LIKE 湖北UNIONSELECT*FROM StudentWHER

31、E Sname LIKE 张%数据库原理数据库原理（4）交操作）交操作 nSQL使用INTERSECT把同时出现在两个查询中的结果取出，实现交操作，并且也会去掉重复的元组，如果要保留所有重复，则必须使用INTERSECT ALL。n例3.29 查询年龄大于18岁、姓张的学生的基本信息。SELECT*FROM StudentWHERE Age 18INTERSECTSELECT*FROM StudentWHERE Sname LIKE 张%数据库原理数据库原理（5）差操作）差操作 SQL使用MINUS把出现在第一个查询结果中但不出现在第二个查询结果中的元组取出，实现差操作。n例3.30 查询年龄

32、大于20岁的学生基本信息与女生的基本信息的差集。SELECT*FROM StudentWHERE Age 20MINUSSELECT*FROM StudentWHERE Sex LIKE 女 数据库原理数据库原理5嵌套查询嵌套查询 n嵌套查询是指一个SELECT-FROM-WHERE查询块嵌入在另一个SELECTFROM WHERE查询块WHERE子句中的查询。n外层查询称为父查询，内层查询称为子查询。子查询中还可以嵌套其它子查询，即允许多层嵌套查询，其执行过程是由里到外的，每一个子查询是在上一级查询处理之前完成的 n在子查询中不能使用ORDER BY子句。数据库原理数据库原理（1）返回单值的

33、子查询）返回单值的子查询 n子查询返回的检索信息是单一的值。这类子查询看起来就像常量一样，因此经常把这类子查询的结果与父查询的属性用关系运算符来比较。n例3.31 查询选修了C语言的学生的学号和相应的考试成绩。SELECT Sno,GradeFROM StudyWHERE Cno=（SELECT CnoFROM CourseWHERE Cname LIKE C语言）数据库原理数据库原理n例3.32 查询考试成绩大于总平均分的学生学号。SELECT DISTINCT SnoFROM StudyWHERE Grade（SELECT AVG（Grade）FROM Study）n在嵌套查询中，只有能确

34、切知道内层查询返回的是单值时，才可以直接使用关系运算符进行比较。数据库原理数据库原理（2）返回多值的子查询）返回多值的子查询 n使用ALL、ANY等谓词来解决。n例3.33 查询成绩至少比选修了C02号课程的一个学生成绩低的学生学号。SELECT SnoFROM StudyWHERE Grade ANY（SELECT GradeFROM StudyWHERE Cno=C02）AND Cno C02nANY运算符表示至少一个或某一个，因此使用ANY就可表示至少比某集合中的一个少的含义数据库原理数据库原理n实际上，比最大的值小就等价于ANY，该例子可用聚合函数MAX来实现。SELECT SnoFR

35、OM StudyWHERE Grade （SELECT MAX（Grade）FROM StudyWHERE Cno=C02）AND Cno C02 数据库原理数据库原理n例3.34 查询成绩比所有选修了C02号课程的学生成绩低的学生学号。SELECT SnoFROM StudyWHERE Grade ALL（SELECT GradeFROM StudyWHERE Cno=C02）AND Cno C02nALL运算符表示所有或者每个，因此使用ALL就可表示至少比某集合所有都少的含义。实际上，比最小的值小就等价于ALL数据库原理数据库原理n该例子可用聚合函数MIN来实现。SELECT SnoFRO

36、M StudyWHERE Grade （SELECT MIN（Grade）FROM StudyWHERE Cno=C02）AND Cno C02 数据库原理数据库原理n对于在父查询中需要判断某个属性的值与子查询结果中某个值相等的这类查询可以用IN实现，也就是说可以用IN来代替“=ANY”。n例3.35 查询选修了C语言的学生的基本信息。SELECT*FROM StudentWHERE Sno IN（SELECT SnoFROM StudyWHERE Cno INSELECT CnoFROM CourseWHERE Cname LIKE C语言）数据库原理数据库原理（3）相关子查询）相关子查询n

37、子查询的查询条件依赖于父查询，每次要对子查询中的外部元组变量的某一项赋值，这类子查询称为相关子查询。n在相关子查询中经常使用EXISTS谓词。子查询中含有EXISTS谓词后不会返回任何结果，只得到“真”或“假”。数据库原理数据库原理n【例3.36】查询选修了C语言的学生的学号。SELECT SnoFROM StudyWHERE EXISTS （SELECT*FROM CourseWHERE Study.Cno=Course.Cno AND Cname LIKE C语言）n首先取外层查询中Study表的第一个元组，根据它与内层查询相关的属性值（即Cno值）处理内层查询，若WHERE子句的返回值为

38、真（即内层查询结果非空），则取此元组放入结果表中 数据库原理数据库原理n与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。n例3.37 查询所有没选C04号课程的学生的姓名。SELECT SnameFROM StudentWHERE NOT EXISTS （SELECT*FROM StudyWHERE Study.Sno=Student.Sno AND Cno=C04）数据库原理数据库原理nSQL语言中没有全称量词，因此必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词

39、：n例3.38 查询选修了全部课程的学生姓名。由于没有全称量词，这里将题目的意思转换成等价的存在量词形式：查询这样的学生姓名，没有一门课程是他不选的。该查询可以形式化的表示如下：n用p表示谓词“该学生选课”n用x表示谓词“课程”，则上述查询可表示为，即“对于任何一门课程该学生都选了”，将它转换为等价的带有存在量词的谓词后，则表示为，即“没有任何一门课程该学生不选”。()()x pxp 数据库原理数据库原理 n该查询涉及3个关系表：存放学生姓名的Student表、存放所有课程信息的Course表、存放学生选课信息的Study关系表。其SQL语句如下：SELECT SnameFROM Studen

40、tWHERE NOT EXISTS（SELECT*FROM CourseWHERE NOT EXISTS（SELECT*FROM StudyWHERE Sno=Student.Sno AND Cno=Course.Cno）数据库原理数据库原理nSQL语言中也没有蕴函逻辑运算，因此必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词：pqpq 数据库原理数据库原理n例3.39 查询至少选修了学生03061选修的全部课程的学生学号。本题的查询要求解释如下：查询这样的学生，凡是03061选修的课程，他都选了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为03061的学生选

41、修了课程y，则x也选修了y，那么就将他的学号选出来。该查询可以形式化如下：n用p表示谓词“学生03061选修了课程y”n用q表示谓词“学生x选修了课程y”则上述查询可表示为：该查询可转换为如下等价形式：()()()()()ypqypqypqy pq 数据库原理数据库原理n它所表达的语义为：不存在这样的课程y，学生03061选了y，而学生x没有选。用SQL语言可表示如下：SELECT DISTINCT SnoFROM Study XWHERE NOT EXISTS（SELECT*FROM Study YWHERE Y.Sno=03061 AND NOT EXIST（SELECT*FROM Stu

42、dy ZWHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno）数据库原理数据库原理3.3.2 插入数据插入数据 当基本表建立以后，就可以往表中插入数据了，在SQL中插入数据使用INSERT语句实现。INSERT语句有两种插入形式：n插入单个元组n插入多个元组。数据库原理数据库原理1插入单个元组插入单个元组 INSERT INTO (，)VALUES（，）n其中，指定要插入元组的表的名字；，为要添加列值的列名序列；VALUES后则一一对应要添加列的输入值。数据库原理数据库原理n例3.40 在学生表中插入一个学生记录（04027，肖文，男，19，福建）。INSERT INTO Stu

43、dentVALUES（04027,肖文,男,19,福建）n例3.41 在学习表中插入一个学生选课记录（04027,05）。INSERT INTO Study（Sno,Cno）VALUES（04027,05）数据库原理数据库原理2插入多个元组插入多个元组 INSERT INTO (，)子查询 n这种形式可将子查询的结果集一次性插入基本表中。如果列名序列省略，则子查询所得到的数据列必须和要插入数据的基本表的数据列完全一致。如果列名序列给出，则子查询结果与列名序列要一一对应。数据库原理数据库原理n【例3.42】如果已建有课程平均分表Course_avg（Cno,Average），其中average表

44、示每门课程的平均分，向Course_avg表中插入每门课程的平均分记录。INSERT INTO Course_avg（Cno,Average）SELECT Cno,AVG（Grade）FROM StudyGROUP BY Cno 数据库原理数据库原理3.3.3 删除数据删除数据 nSQL提供了DELETE语句用于删除每一个表中的一行或多行记录。DELETE是数据操纵语句，只是删除表中的每条记录，不能删除表的定义。DELETE语句的一般格式：nDELETE FROM WHERE 数据库原理数据库原理n例3.43 删除籍贯为湖北的学生基本信息。DELETE FROM StudentWHERE Pl

45、ace LIKE 湖北此查询会将籍贯列上值为“湖北”的所有记录全部删除。在WHERE条件中同样可以使用复杂的子查询。n例3.44 删除成绩不及格的学生的基本信息。DELETE FROM StudentWHERE Sno IN（SELECT SnoFROM StudyWHERE Grade 60）数据库原理数据库原理3.3.4 修改数据修改数据 n在SQL中使用UPDATE语句修改满足指定条件元组的指定列值。满足指定条件的元组可以是一个元组，也可以是多个元组。UPDATE语句的一般格式为：UPDATE SET =，=WHERE 数据库原理数据库原理n【例3.45】将数据库原理的学分改为3。UPD

46、ATE CourseSET Credit=3WHERE Cname LIKE 数据库原理n在WHERE条件中同样可以使用复杂的子查询。n【例3.46】将所有选了数据结构的学生成绩加5分。UPDATE StudySET Grade=Grade+5WHERE cno IN（SELECT Cno FROM CourseWHERE Cname LIKE 数据结构）数据库原理数据库原理3.4 视图视图 n视图是外模式的基本单位，从用户观点来看，视图和基本表是一样的。实际上视图是从若干个基本表或视图导出来的表，因此当基本表的数据发生变化时，相应的视图数据也会随之改变。视图定义后，可以和基本表一样被用户查询

47、、更新，但通过视图来更新基本表中的数据要有一定的限制。数据库原理数据库原理3.4.1 建立视图建立视图 SQL语言用CREATE VIEW命令建立视图，其一般格式为：CREATE VIEW（，）AS（子查询）WITH CHECK OPTION nWITH CHECK OPTION是可选项，该选项表示对所建视图进行INSERT、UPDATE和DELETE操作时，让系统检查该操作的数据是否满足子查询中WHERE子句里限定的条件，若不满足，则系统拒绝执行。数据库原理数据库原理n例3.47 建立一个湖北考籍的学生信息视图。CREATE VIEW HBStudentASSELECT Sno,Sname,

48、Sex,AgeFROM StudentWHERE Place LIKE 湖北 n例3.48 建立一个学习了C语言的所有学生信息的视图。CREATE VIEW C_stud_info（Sno,Sname,Sex,Place）ASSELECT Sno,Sname,Sex,PlaceFROM StudentWHERE Sno IN（SELECT SnoFROM StudyWHERE Cno IN（SELECT CnoFROM CourseWHERE Cname LIKE C语言）数据库原理数据库原理3.4.2 删除视图删除视图 n在SQL中删除视图使用DROP VIEW语句，其一般格式为：nDROP

49、 VIEW n例3.49 删除视图HBStudent。DROP VIEW HBStudent本例将从数据字典中删除视图HBStudent的定义。数据库原理数据库原理3.4.3 查询视图查询视图 n视图已经建立，用户就可对视图进行查询操作。从用户角度来说，查询视图与查询基本表是一样的，可是视图是存在于数据库当中的虚表，所以DBMS执行对视图的查询实际上是根据视图的定义转换成等价的对基本表的查询。n因此DBMS对某SELECT语句进行处理时，若发现被查询对象是视图，则DBMS将进行下述操作：（1）从数据字典中取出视图的定义。（2）把视图定义的子查询和本SELECT语句定义的查询相结合，生成等价的对

50、基本表的查询（此过程称为视图的消解）。（3）执行对基本表的查询，把查询结果（作为本次对视图的查询结果）向用户显示。数据库原理数据库原理【例3.50】在例3.47建立的视图HBStudent中查找年龄大于20岁的学生基本信息。SELECT Sno,Sname,Sex,AgeFROM HBStudentWHERE Age 20本例在执行时会转化为下列执行语句：SELECT Sno,Sname,Sex,AgeFROM StudentWHERE Place LIKE 湖北 AND Age 20数据库原理数据库原理3.4.4 更新视图更新视图 n视图更新是指对视图进行插入（INSERT）、删除（DELE