数据库系统概论第3章的知识

1、第三章 关系数据库标准语言SQL1第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 数据查询3.4 数据更新3.5 视图23.1 SQL概述SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言数据查询数据定义数据操纵数据控制3SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模 式内模式SQL支持关系数据库三级模式结构53.2 数据定义 SQL的数据定义功能: 63.2.1 模式的定义与删除CREATE SCHEMA AUTHORIZATION | CREATE SCHEMA StuCourse

2、AUTHORIZATION WANG; DROP SCHEMA DROP SCHEMA StuCourse RESTRICT 73.2.2 基本表的定义、删除与修改（1）基本表的定义CREATE TABLE （ ， ， ）；8课程表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是外码 被参照表是C

3、ourse被参照列是Cno9问题下面建立一个“学生选课”表SC方法是否正确？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)，); 10Sql server 主要数据类型数 据 类 型符 号 标 识数 据 类 型符 号 标 识整数型bigint, int, smallint, tinyintUnicode

4、字符型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 基本表的定义、删除与修改（2）修改基本表ALTER TABLE ADD 完整性约束 DROP ALTE

5、R COLUMN ；例：向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD S_entrance DATE；123.2.2 基本表的定义、删除与修改（3）删除基本表 DROP TABLE RESTRICT| CASCADE；例:删除Student表，同时删除表上定义的索引等其他对象 DROP TABLE Student CASCADE ;133.2.3 索引的建立与删除索引的建立CREATE UNIQUE CLUSTER INDEX ON (, )；CREATE CLUSTER INDEX Stusname ON Student(Sname

6、)；索引的删除DROP INDEX Stusname；143.3数据查询SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；153.3 数据查询 3.3.1 单表查询3.3.2 连接查询3.3.3 嵌套查询3.3.4 集合查询3.3.5 Select语句的一般形式 163.3.1 单表查询 查询仅涉及一个表：选择表中的若干列选择表中的若干元组ORDER BY子句聚集函数GROUP BY子句Having 子句17选择表中的若干列例1 选择部分列SELECT Sno，Sname FROM Student；例选择

7、所有列SELECT * FROM Student例获得经过计算的列SELECT Sname，2012-Sage FROM Student；例使用列别名SELECT Sname，2012-Sage year-of-birthFROM Student；18选择表中的若干元组（1）消除取值重复的行：DISTINCT SELECT DISTINCT Sno FROM SC；19选择表中的若干元组（2）查 询 条 件谓 词比 较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值I

8、S NULL，IS NOT NULL多重条件（逻辑运算）AND，OR，NOT比较大小SELECT DISTINCT SnoFROM SCWHERE Grade60；确定范围SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 21确定集合SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );字符匹配 SELECT * FROM Student WHERE Sname LIKE 刘%；22空值的查询 SELECT Sno，Cno FROM SC WHERE Gr

9、ade IS NULL多重条件查询 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；23ORDER BY子句 可以按一个或多个属性列排序SELECT *FROM StudentORDER BY Sdept，Sage DESC； 当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示 24聚集函数 计数COUNT（DISTINCT|ALL ）计算总和SUM（DISTINCT|ALL ） 计算平均值AVG（DISTINCT|ALL ）最值 MAX（DISTINCT|ALL ） MIN（DISTINCT|ALL

10、 ）25 例 查询学生总人数。 SELECT COUNT(*) FROM Student； 例 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；26GROUP BY子句 对查询的中间结果表进行分组，以细化聚集函数的作用例 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；CnoCOUNT(Sno)12223434443354827having子句对分组后的数据进行筛选，选出满足条件的组例 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY

11、 Sno HAVING COUNT(*) 3； 28问题Select、from、where、order by、group by、having子句在查询过程中的执行顺序？293.3.2 连接查询 连接条件或连接谓词：用来连接两个表的条件. . . BETWEEN . AND .连接操作的执行方法嵌套循环法(NESTED-LOOP)排序合并法(SORT-MERGE)索引连接(INDEX-JOIN30连接查询的种类一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接31等值与非等值连接查询 查询学生的姓名及获得的成绩SELECT Student.sname,SC.grade FROM S

12、tudent, SCWHERE Student.Sno = SC.Sno 注意：当两个以上的表具有相同的列名时，一定要再列名前加上表名作为限制问题：此查询有多少条记录满足条件？32自身连接 查询存在间接先行课程的课程号及其间接先行课程号 SELECT FIRST.Cno，SECOND.Cpno pcno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；CnoPcno17355633外连接(1)查询每门课程号及其间接先行课程号（如果存在的话） SELECT FIRST.Cno，SECOND.Cpno FROM Cours

13、e FIRST LEFT OUT JOIN Course SECOND ON（FIRST.Cno=SECOND.Cpno) FoSecond.cpno172 null354null566null7null34外连接（2） 左外连接列出左边关系（如本例Student）中所有的元组 右外连接列出右边关系中所有的元组 全外连接列出两个连接表中的所有元组35复合条件连接复合条件连接：WHERE子句中含多个连接条件查询选修2号课程且成绩在90分以上的所有学生SELECT Student.Sno, SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND SC

14、.Cno= 2 AND SC.Grade 90； 363.3.3嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块嵌套查询:嵌套两个以上的查询块 SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN （SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= 2 ）； 37带有比较运算符的子查询（1） 当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。与ANY、some、ALL谓词配合使用38带有比较运算符的子查询（2）查询与刘晨同一学院的学生 SELECT Sno，Sname，Sde

15、pt FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘晨)；39带有比较运算符的子查询（3）例41找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);相关子查询 40带有ANY（SOME）或ALL谓词的子查询（1）某一值需要与多值比较时的妥协方法（配合比较运算符使用）比较含义比较含义 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所

16、有值= ALL小于等于子查询结果中的所有值 ANY小于子查询结果中的某个值 = ANY等于子查询结果中的某个值 = ANY大于等于子查询结果中的某个值 !=（或）ANY不等于子查询结果中的某个值= ALL大于等于子查询结果中的所有值!=（或）ALL不等于子查询结果中的任何一个值 41带有ANY（SOME）或ALL谓词的子查询(2)例 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ;

17、 42带有EXISTS谓词的子查询(1)EXISTS( )-true EXISTS( )-FalseNOT EXISTS( )-true NOT EXISTS( )-false43例查询所有选修了1号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )； 44例 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXI

18、STS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；45 例查询至少选修了学生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 ) )；463.3.4 集合查询并SE

19、LECT 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 ；473.3.5 SELECT语句的一般格式 SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC 483.4 数 据 更 新 3.4.1 插入数据3.4.2 修改数据3.4.3 删除数据 493.4.1 插入数据（1）插入单行将一个新学生元组（学号：201115128； 课程号：2； 成绩： 95）插入到SC表中。 INSERT INTO SC (Sno,Cno,grade) VALUES (201115128， 2，95)；503.4.1插入数据（2）同时插入多行 计算每个系的学生的平均年龄，