《数据库系统概论第3章课件.ppt》由会员分享，可在线阅读，更多相关《数据库系统概论第3章课件.ppt（59页珍藏版）》请在三一办公上搜索。
1、第三章 关系数据库标准语言SQL,3.1 SQL概述3.2 数据定义3.3 数据查询3.4 数据更新3.5 视图,3.1 SQL概述,SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言数据查询数据定义数据操纵数据控制,SQL支持关系数据库三级模式结构,3.2 数据定义,SQL的数据定义功能:,3.2.1 模式的定义与删除,CREATE SCHEMA AUTHORIZATION|CREATE SCHEMA StuCourse AUTHORIZATION WANG;DROP SCHEMA DROP SCHEMA StuCourse RESTRICT,
2、3.2.2 基本表的定义、删除与修改（1）,基本表的定义CREATE TABLE（，）；,课程表Course,例 建立一个“课程”表Course CREATE TABLE Course(Cno CHAR(4)PRIMARY KEY，Cname CHAR(40)，Cpno CHAR(4)，Ccredit SMALLINT，FOREIGN KEY(Cpno)REFERENCES Course(Cno);,先修课,Cpno是外码 被参照表是Course被参照列是Cno,问题,下面建立一个“学生选课”表SC方法是否正确？CREATE SC(Sno CHAR(9)PRIMARY KEY null，Cno
3、 CHAR(4)PRIMARY KEY，Grade SMALLINT null，FOREIGN KEY(Sno)REFERENCES Student(Sno)，FOREIGN KEY(Cno)REFERENCES Course(Cno)，);,Sql server 主要数据类型,3.2.2 基本表的定义、删除与修改（2）,修改基本表ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN；例：向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD S_entrance DATE；,3.2.2 基本表的定义、删除与修改（3）
4、,删除基本表 DROP TABLE RESTRICT|CASCADE；例:删除Student表，同时删除表上定义的索引等其他对象 DROP TABLE Student CASCADE;,3.2.3 索引的建立与删除,索引的建立CREATE UNIQUE CLUSTER INDEX ON(,)；CREATE CLUSTER INDEX Stusname ON Student(Sname)；索引的删除DROP INDEX Stusname；,3.3数据查询,SELECT ALL|DISTINCT，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC；,3.3 数
5、据查询,3.3.1 单表查询3.3.2 连接查询3.3.3 嵌套查询3.3.4 集合查询3.3.5 Select语句的一般形式,3.3.1 单表查询,查询仅涉及一个表：选择表中的若干列选择表中的若干元组ORDER BY子句聚集函数GROUP BY子句Having 子句,选择表中的若干列,例1 选择部分列SELECT Sno，Sname FROM Student；例选择所有列SELECT*FROM Student例获得经过计算的列SELECT Sname，2012-Sage FROM Student；例使用列别名SELECT Sname，2012-Sage year-of-birthFROM S
6、tudent；,选择表中的若干元组（1）,消除取值重复的行：DISTINCT SELECT DISTINCT Sno FROM SC；,选择表中的若干元组（2）,比较大小SELECT DISTINCT SnoFROM SCWHERE Grade60；确定范围SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23；,确定集合SELECT Sname，SsexFROM StudentWHERE Sdept IN(IS，MA，CS);字符匹配 SELECT*FROM Student WHERE Sname LIKE 刘%；,空值
7、的查询 SELECT Sno，Cno FROM SC WHERE Grade IS NULL多重条件查询 SELECT Sname FROM Student WHERE Sdept=CS AND Sage20；,ORDER BY子句,可以按一个或多个属性列排序SELECT*FROM StudentORDER BY Sdept，Sage DESC；当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,聚集函数,计数COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）最值 MAX（DISTIN
8、CT|ALL）MIN（DISTINCT|ALL）,例 查询学生总人数。SELECT COUNT(*)FROM Student；例 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC；,GROUP BY子句,对查询的中间结果表进行分组，以细化聚集函数的作用例 求各个课程号及相应的选课人数。SELECT Cno，COUNT(Sno)FROM SC GROUP BY Cno；,having子句,对分组后的数据进行筛选，选出满足条件的组例 查询选修了3门以上课程的学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT
9、(*)3；,问题,Select、from、where、order by、group by、having子句在查询过程中的执行顺序？,3.3.2 连接查询,连接条件或连接谓词：用来连接两个表的条件.BETWEEN.AND.连接操作的执行方法嵌套循环法(NESTED-LOOP)排序合并法(SORT-MERGE)索引连接(INDEX-JOIN,连接查询的种类,一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接,等值与非等值连接查询,查询学生的姓名及获得的成绩SELECT Student.sname,SC.grade FROM Student,SCWHERE Student.Sno=SC
10、.Sno 注意：当两个以上的表具有相同的列名时，一定要再列名前加上表名作为限制,问题：此查询有多少条记录满足条件？,自身连接,查询存在间接先行课程的课程号及其间接先行课程号 SELECT FIRST.Cno，SECOND.Cpno pcno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno=SECOND.Cno；,外连接(1),查询每门课程号及其间接先行课程号（如果存在的话）SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST LEFT OUT JOIN Course SECOND ON（FIRST.Cno
11、=SECOND.Cpno),外连接（2）,左外连接列出左边关系（如本例Student）中所有的元组 右外连接列出右边关系中所有的元组 全外连接列出两个连接表中的所有元组,复合条件连接,复合条件连接：WHERE子句中含多个连接条件查询选修2号课程且成绩在90分以上的所有学生SELECT Student.Sno,SnameFROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade 90；,3.3.3嵌套查询,一个SELECT-FROM-WHERE语句称为一个查询块嵌套查询:嵌套两个以上的查询块 SELECT Sname/*外层
12、查询/父查询*/FROM Student WHERE Sno IN（SELECT Sno/*内层查询/子查询*/FROM SC WHERE Cno=2）；,带有比较运算符的子查询（1）,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。与ANY、some、ALL谓词配合使用,带有比较运算符的子查询（2）,查询与刘晨同一学院的学生 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname=刘晨)；,带有比较运算符的子查询（3）,例41找出每个学生超过他选修课程平均成绩的课程
13、号。SELECT Sno，Cno FROM SC x WHERE Grade=(SELECT AVG(Grade)FROM SC y WHERE y.Sno=x.Sno);,相关子查询,带有ANY（SOME）或ALL谓词的子查询（1）,某一值需要与多值比较时的妥协方法（配合比较运算符使用）,带有ANY（SOME）或ALL谓词的子查询(2),例 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage CS;,带有EXISTS谓词的子查询(1),EXISTS()-true EXISTS()-FalseNOT EXI
14、STS()-true NOT EXISTS()-false,例查询所有选修了1号课程的学生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=1)；,例 查询选修了全部课程的学生姓名。SELECT Sname FROM Student WHERE NOT EXISTS（SELECT*FROM Course WHERE NOT EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno）；,例查询至少选修了学生
15、200215122选修的全部课程的学生号码。SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS(SELECT*FROM SC SCY WHERE SCY.Sno=200215122 AND NOT EXISTS(SELECT*FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,3.3.4 集合查询,并SELECT SnoFROM SCWHERE Cno=1 UNIONSELECT SnoFROM SCWHERE Cno=2；,交SELECT SnoFROM SCWHERE Cno=1 IN
16、TERSECTSELECT SnoFROM SCWHERE Cno=2；,差SELECT SnoFROM SCWHERE Cno=1 EXCEPTSELECT SnoFROM SCWHERE Cno=2；,3.3.5 SELECT语句的一般格式,SELECT ALL|DISTINCT 别名，别名 FROM 别名，别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC,3.4 数 据 更 新,3.4.1 插入数据3.4.2 修改数据3.4.3 删除数据,3.4.1 插入数据（1）,插入单行将一个新学生元组（学号：201115128；课程号：2；成绩：95）插入到SC
17、表中。,INSERT INTO SC(Sno,Cno,grade)VALUES(201115128，2，95)；,3.4.1插入数据（2）,同时插入多行 计算每个系的学生的平均年龄，并把结果存入在表avgage（Sdept，age）（假定表avgage已经存在）INSERT INTO avgage（sdept，age）SELECT Sdept，AVG(Sage)FROM Student GROUP BY Sdept,3.4.2 修改数据,语句格式 UPDATE SET=，=WHERE；UPDATE Student SET Sage=22 WHERE Sno=200215121；注意：修改后的数
18、据必须满足表的完整性约束,3.4.3 删除数据,语句格式 DELETE FROM WHERE；问题：删除课程表中学分小于等于0的课程信息该怎么实现？Delete From course Where ccredit=0,3.5 视 图,是基于基本表的虚表，是生成表基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图,3.5 视 图,3.5.1 定义视图3.5.2 查询视图3.5.3 更新视图3.5.4 视图的作用,3.5.1 定义视图-创建,CREATE VIEW(，)AS WITH CHECK OPTION；CREATE VIEW IS_Student(Sno，Sname，Sage)A
19、S SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；问题:为什么子查询不允许含有ORDER BY子句和DISTINCT短语？,3.5.1 定义视图-删除,DROP VIEW；DROP VIEW IS_S1,3.5.2 查询视图,对视图的查询方法与对基表的查询基本相同。对视图的查询大多数时候可以转换为对基表的直接查询。请举例。什么情况下对视图的查询不能转换为对基表的直接查询？,3.5.3 更新视图-受限,对于一些不能唯一地有意义地转换成对相应基本表的更新的视图是不能更新的。不同的DBMS对此有私有的规定。如Sql server规定，当视图依赖于多个基表时，该视图是不可更新的。,3.5.4 视图的作用,1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据 3.视图对重构数据库提供了一定程度的逻辑独立性 4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询,
