数据库系统概论第3章课件.ppt

第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL3.1 SQL概述概述3.2 数据定义数据定义3.3 数据查询数据查询3.4 数据更新数据更新3.5 视图视图3.1 SQL3.1 SQL概述概述SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言数据查询数据定义数据操纵数据控制SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模式外模式模模 式式内模式内模式SQL支持关系数据库三级模式结构支持关系数据库三级模式结构3.2 3.2 数据定义数据定义 SQL的数据定义功能的数据定义功能: 3.2.1 3.2.1 模式的定义与删除模式的定义与删除CREATE SCHEMA AUTHORIZATION | CREATE SCHEMA StuCourse AUTHORIZATION WANG; DROP SCHEMA DROP SCHEMA StuCourse RESTRICT 3.2.2 3.2.2 基本表的定义、删除与修改（基本表的定义、删除与修改（1 1）基本表的定义基本表的定义CREATE TABLE （ ， ， ）；课程表课程表CourseCourse 例 建立一个“课程”表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问题问题下面建立一个下面建立一个“学生选课学生选课”表表SCSC方法是否正确？方法是否正确？CREATE SC (Sno CHAR(9) PRIMARY KEY null， Cno CHAR(4) PRIMARY KEY ， Grade SMALLINT null， FOREIGN KEY (Sno) REFERENCES Student(Sno)， FOREIGN KEY (Cno) REFERENCES Course(Cno)，); Sql server Sql server 主要数据类型主要数据类型数数 据据 类类 型型符符 号号 标标 识识数数 据据 类类 型型符符 号号 标标 识识整数型bigint, int, smallint, tinyintUnicode字符型nchar, nvarchar、精确数值型decimal, numeric文本型text, ntext浮点型float, real二进制型binary, varbinary货币型money, smallmoney日期时间类型datetime, smalldatetime, date, time位型bit时间戳型timestamp字符型char, varchar图像型image其他cursor, sql_variant, table, uniqueidentifier, xml3.2.2 3.2.2 基本表的定义、删除与修改（基本表的定义、删除与修改（2 2）修改基本表ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ；例：向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD S_entrance DATE；3.2.2 3.2.2 基本表的定义、删除与修改（基本表的定义、删除与修改（3 3）删除基本表 DROP TABLE RESTRICT| CASCADE；例:删除Student表，同时删除表上定义的索引等其他对象 DROP TABLE Student CASCADE ;3.2.3 3.2.3 索引的建立与删除索引的建立与删除索引的建立CREATE UNIQUE CLUSTER INDEX ON (, )；CREATE CLUSTER INDEX Stusname ON Student(Sname)；索引的删除DROP INDEX Stusname；3.33.3数据查询数据查询SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 3.3 3.3 数据查询数据查询 3.3.1 单表查询单表查询3.3.2 连接查询连接查询3.3.3 嵌套查询嵌套查询3.3.4 集合查询集合查询3.3.5 Select语句的一般形式语句的一般形式 3.3.1 3.3.1 单表查询单表查询 查询仅涉及一个表：n选择表中的若干列n选择表中的若干元组nORDER BY子句n聚集函数nGROUP BY子句nHaving 子句选择表中的若干列选择表中的若干列例1 选择部分列SELECT Sno，Sname FROM Student； 例选择所有列SELECT * FROM Student例获得经过计算的列SELECT Sname，2012-Sage FROM Student；例使用列别名SELECT Sname，2012-Sage year-of-birthFROM Student； 选择表中的若干元组（选择表中的若干元组（1 1）消除取值重复的行：DISTINCT SELECT DISTINCT Sno FROM SC；选择表中的若干元组（选择表中的若干元组（2 2）查查 询询 条条 件件谓谓 词词比 较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT比较大小比较大小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 刘%；空值的查询空值的查询 SELECT Sno，Cno FROM SC WHERE Grade IS NULL多重条件查询多重条件查询 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；ORDER BYORDER BY子句子句 可以按一个或多个属性列排序SELECT *FROM StudentORDER BY Sdept，Sage DESC； 当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示 聚集函数聚集函数 计数COUNT（DISTINCT|ALL ）计算总和SUM（DISTINCT|ALL ） 计算平均值AVG（DISTINCT|ALL ）最值 MAX（DISTINCT|ALL ） MIN（DISTINCT|ALL ） 例 查询学生总人数。 SELECT COUNT(*) FROM Student； 例 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；GROUP BYGROUP BY子句子句 对查询的中间结果表进行分组，以细化聚集函数的作用例 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；CnoCOUNT(Sno)122234344433548havinghaving子句子句对分组后的数据进行筛选，选出满足条件的组例 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； 问题问题Select、from、where、order by、group by、having子句在查询过程中的执行顺序？3.3.2 3.3.2 连接查询连接查询 连接条件或连接谓词：用来连接两个表的条件. . . BETWEEN . AND .连接操作的执行方法嵌套循环法(NESTED-LOOP)排序合并法(SORT-MERGE)索引连接(INDEX-JOIN连接查询的种类连接查询的种类一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接等值与非等值连接查询等值与非等值连接查询 查询学生的姓名及获得的成绩SELECT Student.sname,SC.grade FROM Student, SCWHERE Student.Sno = SC.Sno 注意：当两个以上的表具有相同的列名时，一定注意：当两个以上的表具有相同的列名时，一定要再列名前加上表名作为限制要再列名前加上表名作为限制问题：此查询有多问题：此查询有多少条记录满足条件？少条记录满足条件？自身连接自身连接 查询存在间接先行课程的课程号及其间接先行课程号 SELECT FIRST.Cno，SECOND.Cpno pcno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；CnoPcno173556外连接外连接(1)(1)查询每门课程号及其间接先行课程号（如果存在的话） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST LEFT OUT JOIN Course SECOND ON（FIRST.Cno=SECOND.Cpno) FoSecond.cpno172 null354null566null7null外连接（外连接（2 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.33.3.3嵌套查询嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块查询块嵌套查询:嵌套两个以上的查询块 SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN （SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= 2 ）； 带有比较运算符的子查询（带有比较运算符的子查询（1 1） 当能确切知道内层查询返回单值单值时，可用比较运算符（，=，=，!=或）。与ANY、some、ALL谓词配合使用带有比较运算符的子查询（带有比较运算符的子查询（2 2）查询与刘晨同一学院的学生 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘晨)；带有比较运算符的子查询（带有比较运算符的子查询（3 3）例41找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);相关子查询相关子查询 带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询（谓词的子查询（1 1）某一值需要与多值比较时的妥协方法（配合比较运算符使用）比较比较含义含义比较比较含义含义 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值= ALL小于等于子查询结果中的所有值 ANY小于子查询结果中的某个值 = ANY等于子查询结果中的某个值 = ANY大于等于子查询结果中的某个值 !=（或）ANY不等于子查询结果中的某个值= ALL大于等于子查询结果中的所有值!=（或）ALL不等于子查询结果中的任何一个值 带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询(2)(2)例 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ; 带有带有EXISTSEXISTS谓词的子查询谓词的子查询(1)(1)EXISTS( )-true EXISTS( )-FalseNOT EXISTS( )-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 ） ）； 例查询至少选修了学生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 3.3.4 集合查询集合查询并SELECT SnoFROM SCWHERE Cno= 1 UNIONSELECT SnoFROM SCWHERE Cno= 2 ；交交SELECT SnoFROM SCWHERE Cno= 1 INTERSECTSELECT SnoFROM SCWHERE Cno= 2 ；差差SELECT SnoFROM SCWHERE Cno= 1 EXCEPTSELECT SnoFROM SCWHERE Cno= 2 ；3.3.5 SELECT3.3.5 SELECT语句的一般格式语句的一般格式 SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC 3.4 3.4 数数 据据 更更 新新 3.4.1 插入数据插入数据3.4.2 修改数据修改数据3.4.3 删除数据删除数据 3.4.1 3.4.1 插入数据（插入数据（1 1）插入单行将一个新学生元组（学号：201115128； 课程号：2； 成绩： 95）插入到SC表中。 INSERT INTO SC (Sno,Cno,grade) VALUES (201115128， 2，95)；3.4.13.4.1插入数据（插入数据（2 2）同时插入多行 计算每个系的学生的平均年龄，并把结果存入在表avgage（Sdept，age）（假定表avgage已经存在） INSERT INTO avgage（sdept，age） SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept3.4.2 3.4.2 修改数据修改数据语句格式 UPDATE SET =，= WHERE ； UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； 注意：修改后的数据必须满足表的完整性约束注意：修改后的数据必须满足表的完整性约束3.4.3 3.4.3 删除数据删除数据语句格式 DELETE FROM WHERE ；问题：删除课程表中学分小于等于0的课程信息该怎么实现？Delete From course Where ccredit=03.5 3.5 视视 图图是基于基本表的虚表，是生成表基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图3.5 3.5 视视 图图3.5.1 定义视图定义视图3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用3.5.1 3.5.1 定义视图定义视图- -创建创建CREATE VIEW ( ，) AS WITH CHECK OPTION；CREATE VIEW IS_Student(Sno，Sname，Sage) AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；问题问题:为什么子查询不允许含有为什么子查询不允许含有ORDER BY子句和子句和DISTINCT短语？短语？3.5.1 3.5.1 定义视图定义视图- -删除删除DROP VIEW ；DROP VIEW IS_S13.5.2 3.5.2 查询视图查询视图对视图的查询方法与对基表的查询基本相同。对视图的查询大多数时候可以转换为对基表的直接查询。请举例。什么情况下对视图的查询不能转换为对基表的直接查询？3.5.3 3.5.3 更新视图更新视图- -受限受限对于一些不能唯一地唯一地有意义有意义地转换成对相应基本表的更新的视图是不能更新的。不同的DBMS对此有私有的规定。如Sql server规定，当视图依赖于多个基表时，该视图是不可更新的。3.5.4 3.5.4 视图的作用视图的作用1. 视图能够简化用户的操作2. 视图使用户能以多种角度看待同一数据 3. 视图对重构数据库提供了一定程度的逻辑独立性 4. 视图能够对机密数据提供安全保护5. 适当的利用视图可以更清晰的表达查询