武汉大学数据库例题及解答

《武汉大学数据库例题及解答》由会员分享，可在线阅读，更多相关《武汉大学数据库例题及解答（24页珍藏版）》请在金锄头文库上搜索。

1、【例 4.3 】 创建大学教学管理数据库， 数据库名为 JXGL， 其主数据文件逻辑名称为 JXGL_ data，数据文件的操作系统文件名称为 JXGL.mdf，数据文件初始大小为 5 MB，最大值为 200 MB，以 5%的增量增加。日志逻辑文件名称为 JXGL_log，日志的操作系统文件名称为 JXGL.ldf，日志文件初始大小为 5 MB，可按 2 MB 增量增加，最大值为 50 MB。 CREATE DATABASE JXGL ON ( NAME = JXGL_data, /默认为主数据文件 FILENAME = + data_path + JXGL.mdf, SIZE = 5, MA

2、XSIZE = 200, FILEGROWTH = 5% ) LOG ON ( NAME = JXGL_log, FILENAME = + data_path + JXGL.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 2MB ) 【例】创建 test 数据库，包含一个主文件组和两个次文件组。 CREATE DATABASE test ON PRIMARY /*定义在主文件组上的文件*/ ( NAME=pri_file1, FILENAME= C:Program FilesMicrosoft SQL Server MSSQLData pri_fil

3、e1.mdf , SIZE=10,MAXSIZE=50,FILEGROWTH=15%), ( NAME=pri_file2, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData pri_file2.ndf , SIZE=10,MAXSIZE=50,FILEGROWTH=15%), FILEGROUP Grp1 /*定义在次文件组 Grp1 上的文件*/ ( NAME=Grp1_file1, FILENAME= C:Program FilesMicrosoft SQL Server MSSQLData Grp1_file1.ndf , S

4、IZE=10,MAXSIZE = 50,FILEGROWTH=5), FILEGROUP Grp2 /*定义在次文件组 Grp2 上的文件*/ ( NAME = Grp2_file1, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData Grp2_file1.ndf , SIZE=10,MAXSIZE=50,FILEGROWTH=5), LOG ON /*定义事务日志文件*/ ( NAME=test_log, FILENAME= C:Program FilesMicrosoft SQL ServerMSSQLData test_log.

5、ldf , SIZE=5,MAXSIZE=25,FILEGROWTH=5 ) GO 例：在原有数据库的基础上增加一个文件组 date1,添加一个新文件并加入到文件组 date1 中 ALTER DATABASE stuDB ADD FILEGROUP date1 GO ALTER DATABASE stuDB ADD FILE （NAME=stuDB_data1, -主数据文件的逻辑名 FILENAME=D:projectstuDB_data1.ndf, -主数据文件的物理名 SIZE=5mb, -主数据文件初始大小 MAXSIZE=100mb, -主数据文件增长的最大值 FILEGROWTH

6、=15% -主数据文件的增长率 ） TO FILEGROUP date1 GO 【例】删除已经创建的“ 学生管理数据库” 。 DROP DATABASE 学生管理数据库 GO 例:在 xsgl 数据库中创建 kc 表。 USE xsgl GO CREATE TABLE kc ( 课程号 char(4) PRIMARY KEY, 课程名 char(16) not null, 学分 smallint, 学时数 smallint ) GO 【例】在 xs 表的“专业”列和“姓名”列上创建名为“IX_zyxm” 的非聚集、复合索引。 运行如下命令。 CREATE NONCLUSTERED INDEX

7、IX_zyxm ON xs (专业,姓名) GO -查看和维护索引信息 EXEC sp_helpindex -删除索引 DROP INDEX 索引名,. ON 表名 【例】向 xs 表中添加如表 4-1 所示字段 USE xsgl GO ALTER TABLE xs ADD 电话 CHAR(8)NULL ADD 电子邮件 CHAR(40) NULL GO 【例】将 xs 表中的电子邮件字段的列长改为 20。 ALTER TABLE xs ALTER COLUMN 电子邮件 CHAR(20) NULL 【例】在 SC 表中增加完整性约束定义，使 grade 在 0-100 之间。 ALTER T

8、ABLE SC ADD CONSTRAINT grade_CHK CHECK(grade BETWEEN 0 AND 100) 【例】将 xs 表中的电话列删除。 ALTER TABLE xs DROP COLUMN 电话 【例】查询所有女生的信息并将结果保存在名为“女生表”的数据表中。 USE xsgl GO SELECT * INTO 女生表 FROM xs WHERE 性别=女 【例】查询所有男生的信息并将结果存入临时表中。 USE xsgl SELECT * INTO #TEMPDB FROM xs WHERE 性别=男 要查看临时表的内容可用下面的语句： SELECT * FROM

9、#TEMPDB 【例】查询所有学生的学号，姓名及年龄。 USE xsgl GO SELECT 学号,姓名,年龄=DATEDIFF(YY,出生时间,GETDATE() FROM xs GO 【例】查询 xs 表中前 5 条记录 USE xsgl GO SELECT TOP 5 * FROM xs GO 【例】在 cj 表中求每门课程成绩大于 90 分的人数， 并显示统计的明细。 USE xsgl GO SELECT * FROM cj WHERE 成绩=90 ORDER BY 课程号 COMPUTE COUNT(学号) BY 课程号 GO 【例】用命令方式通过视图修改王丹同学的“ 计算机基础”

10、课成绩，将成绩改为 91 分，并查看修改结果。 具体操作命令如下。 UPDATE 电子商务专业学生成绩视图 SET 成绩=91 WHERE 姓名=王丹AND 课程名=计算机基础 GO SELECT * FROM 电子商务专业学生成绩视图 WHERE 姓名=王丹 AND 课程名=计算机基础 GO 例 5.1 图 1.11 所示的大学教学管理数据库中的专业关系 SS，可用如下的表定义语句定义： CREATE TABLE SS （SCODE# CHAR(5) PRIMARY KEY, SSNAME VARCHAR(30) NOT NULL）; 例 5.2 图 1.11 所示的大学教学管理数据库中的学

11、生关系表 S，可用如下的表定义语句定义： CREATE TABLE S （S# CHAR(9) PRIMARY KEY， SNAME CHAR(10) NOT NULL， SSEX CHAR(2) CHECK(SSEX IN ( 男 ， 女) ， SBIRTHIN DATE NOT NULL， PLACEOFB CHAR(16)， SCODE# CHAR(5) NOT NULL， CLASS CHAR(5) NOT NULL） ； 例：教学管理数据库系统中的学习关系 SC。 CREATE TABLE SC (S# CHAR(9), C# CHAR(7)， GRADE INT， PRIMARY

12、KEY(S#，C#)； 例 5.7 给专业表 SS 增加一个新属性 NOUSE_COLUMN，设其数据类型为 NUMERIC(8，1)。 语句应为： ALTER TABLE SS ADD NOUSE_COLUMN NUMERIC(8，1)； 例 5.8 删除专业表 SS 中增加的属性 NOUSE_COLUMN 的两种删除语句形式分别为： ALTER TABLE SS DROP NOUSE_COLUMN CASCADE； ALTER TABLE SS DROP NOUSE_COLUMN RESTRICT； 例 5.9 将专业表 SS 中的专业名称 SSNAME(30)修改为 SSNAME(40)

13、（即长度增加 10）的列修改语句： ALTER TABLE SS MODIFY SSNAME VARCHAR(40)； 例：给学习关系 SC 中插入王丽丽同学（学号为 200401003）学习计算机网络课（课程号为 C403001）的成绩（89 分） 。 INSERT INTO SC（S#，C#，GRADE） VALUES（200401003 ， C403001 ，89） ； 例 5.12 写出将学生关系 S 中的学生名字“王丽丽” （学号为 200401003）改为“王黎丽”的数据修改语句。 UPDATE S SET SNAME王黎丽 WHERE S#200401003 ； 例 5.13 写

14、出将所有女同学的专业改为 S0404 的数据修改语句。 UPDATE S SET SCODE# S0404 WHERE SSEX女 ； 例 5.14 从学生关系 S 中删除学号为 200403001 的学生的信息。 DELETE FROM S WHERE S# =200403001 ； 例 5.15 删除专业关系中的全部信息。 DELETE FROM SS； 例 5.19 查询所有学生所学课程的最高分数、最低分 数和平均分数。 SELECT MAX(GRADE) AS 最高分数, MIN(GRADE) AS 最低分数, AVG(GRADE) AS 平均分数 FROM SC； 例 5.20 写出

15、查询所有学习了计算机网络课（课程号为 C403001)的学生的学号和成绩的查询语句。 学习关系模式：SC（S#，C#，GRADE） SELECT S#，GRADE FROM SC WHERE C# =C403001； 例 5.21 查询选修了计算机网络课(课程号为 C403001)或信 息安全技术课(课程号为 C403002)的学生的学号。 SELECT S# FROM SC WHERE C# =C403001 OR C# =C403002； 例 5.22 查询年龄在 21 岁至 28 岁之间学生的基本信息。 SELECT * FROM S WHERE YEAR(GETDATE()-YEAR(

16、SBIRTHIN) BETWEEN 21 AND 28; 例 5.23 查询各个同学所学课程的平均分数。 SELECT S# ，AVG(GRADE) AS 平均分数 FROM SC GROUP BY S# ； 例 5.24 查询每个专业男、女生的人数。 SELECT SCODE# AS 专业代码，SSEX AS 性别， COUNT(*) AS 人数 FROM S GROUP BY SCODE#，SSEX； 例 5.25 查询学生总数超过 300 人的专业及其具体的总 人数。 SELECT SCODE#，COUNT(*) FROM S GROUP BY SCODE# HAVING COUNT(*

17、)300； 例 5.26 按学号递增的顺序（查询）显示学生的基本信 息。 SELECT * FROM S ORDER BY S# ASC； 例 5.27 按学号递增、课程成绩递减的顺序（查询）显 示学生的课程成绩。 SELECT S#，C#，GRADE FROM SC ORDER BY S# ASC，GRADE DESC； 例查询选修了 3 号课程的学生的学号及其成绩，查询结果 按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 例 5.28 查询学生关系 S 中姓李的学生的学号和姓名。 SELECT S#，S

18、NAME FROM S WHERE SNAME LIKE 李% ； 例 5.29 查询出生日期在 1982 年 1 月 1 日到 1982 年 12 月 31 日之间的所有学生的学号、姓名和出生日期，并按日期的递增顺序排列。 SELECT S#，SNAME，SBIRTHIN FROM S WHERE SBIRTHIN BETWEEN 1982-1-1AND 1982-12-31 ORDER BY SBIRTHIN； 例 5.30 查询所有学生当前的年龄，并按年龄递增顺序排列。 SELECT S#,SNAME, year(getdate()-year(SBIRTHIN) AS AGE FROM

19、S ORDER BY AGE； 例 5.31：统计各教研室开设课程的门数。 分析课程关系模式及其当前值可知，各门课程号的第 1 位由字符 C 开头，第 2 至第 4 位为教研室编号，第 5 至第 7 位是该教研室所开课程的序号。所以仅由课程关系就可以统计出各教研室所开设课程的门数，其查询语句如下： SELECT substring(c#, 2, 3) AS 教研室, count(c#) AS 开课门数 FROM C GROUP BY substring(c#, 2, 3); 例 5.32 查询所有学习了数据结构课（课程号为 C401001）的学生的学号和姓名。 S（S#，SNAME，SSEX，

20、SBIRTHIN，PLACEOFB，SCODE#，CLASS） SC（S#，C#E，DRADE） SELECT S.S#，SNAME FROM S，SC WHERE S.S# = SC.S# AND C# =C401001 ； 例 5.33 查询选修了“信息安全技术”课程的学生的学 号与姓名。 S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE#，CLASS） SC（S#，C#E，DRADE） C（C#，CNAME，CLASSH） SELECT S.S#，SNAME FROM S，SC，C WHERE S.S# = SC.S# AND SC.C# = C.C# AN

21、D CNAME =信息安全技术 ； 例查询每一门课的间接先修课。 SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST， Course SECOND WHERE FIRST.Cpno = SECOND.Cno; 例查询每个学生基本情况及其选修课程的情况。 SELECT Student.*,SC.Cno,Grade FROM Student ,SC WHERE Student.Sno=SC.Sno（*）; SELECT Student.*,SC.Cno,Grade FROM Student ,SC WHERE Student.Sno=SC.Sno; 例 5

22、.34 查询张华同学（学号为 200401001）的那个班的女同学的基本信息。 SELECT * FROM S WHERE CLASS=(SELECT CLASS FROM S WHERE S# =200401001) AND SSEX =女 ； 例 5.35 检索考试成绩比该课程平均成绩低的学生的成绩。 SELECT S#，C#，GRADE FROM SC WHERE GRADE ALL (SELECT GRADE FROM S，SC WHERE S.S# = SC.S# AND SCODE# = S0403)； 例查询其他系中比 IS 系任一个学生年龄小的学生(其中 某一个)名单。 SEL

23、ECT Sname FROM Student WHERE Sage ANY （ SELECT Sage FROM Student WHERE Sdept= IS ） AND Sdept IS ; 例 5.40 查询所有学习了数据结构课（课程号为 C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# =C401001)； 直观的意义为：查询的是那些，在学习关系中存在所学课程为数据结构的学生的学号和姓名。 例 5.41 查询没有学习数据结构课（课程号为

24、 C401001）的学生的学号和姓名。 SELECT S.S#，SNAME FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S# = S.S# AND C# =C401001); 例查询与“ 刘晨” 在同一个系学习的学生的学号、姓名和所属系。 SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS （ SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname= 刘晨 ）; 例 5.42 合并学生关系和专业关系中的专业代码。 SE

25、LECT SCODE# FROM S UNION SELECT SCODE# FROM SS； 例 5.43 查询有成绩的学生的学号。 SELECT S# FROM S INTERSECT SELECT S# FROM SC WHERE GRADE IS NOT NULL; 例 5.44 查询没有成绩的学生的学号。 SELECT S# FROM S MINUS SELECT S# FROM SC WHERE GRADE IS NOT NULL; 例查询选修了课程 1 的学生集合与选修了课程 2 的 学生集合的交集。 SELECT Sno FROM SC WHERE Cno= 1 INTERSE

26、CT SELECT Sno FROM SC WHERE Cno= 2 ; 例 5.45 写出教学安排视图： TA(C#，CNAME，CLASSH，TNAME，TRSECTION） 的创建语句。 课程关系模式：C（C#，CNAME，CLASSH） 教师关系模式： T（T#，TNAME，TSEX，TBIRTHIN，TITLEOF，TRSECTION，TEL） 讲授关系模式： TEACH（T#，C#） 例 5.45 写出教学安排视图： TA(C#，CNAME，CLASSH，TNAME，TRSECTION） 的创建语句。 CREATE VIEW TA AS SELECT C#，CNAME，CLASSH

27、，TNAME，TRSECTION FROM C，TEACH，T WHERE C.C#=TEACH.C# AND TEACH.T#=T.T#； 其中，只有视图名 TA，没有选项说明视图 TA 的列名表与 SELECT 语句中的列名表相同。 例 5.45 写出查询每个学生平均成绩的视图 A_GRADE(S#，AVG_GRADE) 的创建语句。 学生关系模式： S（S#，SNAME，SSEX，SBIRTHIN， ，CLASS） 学习关系模式： SC（S#，C#，GRADE） 例 5.45 写出查询每个学生平均成绩的视图 A_GRADE(S#，AVG_GRADE) 的创建语句。 CREATE VIEW

28、 A_GRADE（S#，AVG_GRADE） AS SELECT S.S#，AVG(GRADE) FROM S，SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROUP BY S.S#； 例 5.46 利用视图 A_GRADE 查询学生所学课程的平均成绩。 SELECT * FROM A_GRADE； 如果用查询语句实现查询学生所学课程的平均成绩，其查询语句应为： SELECT S.S#，AVG(GRADE) FROM S，SC WHERE S.S# = SC.S# AND GRADE IS NOT NULL； GROUP BY S.S#; 例 5.

29、47 设在教学管理数据库系统中，要建立某些临时表来辅助有关管理过程。若其中有一个临时表用于暂存选修了“信息安全技术”课程的学生的学号、姓名、专业名称和班级，表的其格式为： S_C（S#，SNAME，SSNAME，CLASS） 例 5.47（续）将从有关表中查询出的有关数据组成的记录插入该表的插入语句： INSERT INTO S_C(S#，SNAME，SSNAME，CLASS) SELECT S.S#，SNAME，SSNAME，CLASS FROM S，SS WHERE S.SCODE# = SS.SCODE AND S# IN (SELECT S# 学习了信息安全技术课程的学生的学号 FRO

30、M SC WHERE C# IN (SELECT C# 信息安全技术课程的课程号 FROM C WHERE CNAME =信息安全技术)； 例 5.48 设在教学管理数据库系统中， 有一个临时表用于暂存平均成绩大于等于 80 分的女同学的学号和平均成绩。临时表的格式为： S_AVG(S#,AVG_GRADE) 则将从有关表中查询出的有关数据组成的记录插入该表的插入语句为： INSERT INTO S_AVG(S#,AVG_GRAGE) SELECT S#,AVG(GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SSEX = 女 ） GROU

31、P BY S# HAVING AVG(GRADE) = 80； 例 5.49 为了加强对高水平尖子人才的重点培养， 学校拟将计算机科学与技术专业(专业代码为：S0401)中，各门课程均在 85 分以上的学生单独编为 200400 班，所以需要修改学生基本情况数据库中的“班级”属性。 其实现语句为： UPDATE S SET CLASS = 200400 WHERE SCODE = S0401 AND S# IN (SELECT S# FROM SC GROUP BY S# HAVING MIN(GRADE) 85)； 例 5.50 由于试题难度原因，需要将“计算机网络”课的成绩提高 5%。 实

32、现语句为： UPDATE SC SET GRADE = GRADE * 1.05 WHERE C# IN (SELECT C# FROM C WHERE CNAME = 计算机网络 ） ； 嵌入式 # 例 5.51 向课程数据库表中插入一个数据记录。 INSERT INTO C(C#，CNAME，CLASSH) VALUES(C403001， 计算机网络 ，60)； INSERT INTO C(C#，CNAME，CLASSH) VALUES(num，cname1，classh1)； 例 6.10 根据由宿主变量 S1 给出的学生的学号值， 查询学生的姓名、性别和出生年月。 SELECT SNA

33、ME，SSEX，SBIRTHIN FROM S INTO sname1，ssex1，sbirth WHERE S# = s1 ； 1、定义游标 例 5.53：定义一个静态游标，实现从学习关系表中，查询由主变量 s1 的值给出的学号的学生所学的全部课程的课程号和分数的功能。 DECLARE CC1 CURSOR STATIC FOR SELECT C#，GRADE FROM SC WHERE S# = :s1 打开游标语句的句法格式为： OPEN GLOBL | 例 5.54：写出例 5.53 定义的游标的打开游标语句。 OPEN CC1 例 5.55：假设专业表 SS 中的当前值如表 5.10

34、 所示。 表 5.10 专业表 SS 的当前值 SCODE# SSNAME S0401 计算机科学与技术 S0402 指挥自动化 S0403 网络工程 S0404 信息研究与安全 利用游标机制查询专业关系中的所有数据记录。 USE JXGL GO DECLARE CC2 CURSOR /* 定义游标 CC2，默认为 FORWARD_ONLD 游标*/ FOR SELECT * FROM SS; OPEN CC2; /* 打开游标 */ FETCH NEXT FROM CC2; /* 读取第 1 行数据 */ /* 用 WHILE 循环语句控制游标的执行，当正常读出 1 行时， 继续循环，否则跳

35、出循环停止 FETCH 操作 */ WHILE FETCH_STATUS=0 BEGIN FETCH NEXT FROM CC2; END CLOSE CC2; /* 关闭游标 */ DEALLOCATE CC2; /* 删除不再使用的游标 */ GO 索引 为学生-课程数据库中的 Student，Couse，SC 3 个表建立 索引。其中 Student 表按学号升序建立唯一索引，Couse 表按 课程号升序建立聚集索引，SC 表按学号升序和课程号降序建 立唯一索引。 CREATE UNIQUE INDEX Stusno ON Student（sno） ； 执行命令后，为 Student 表

36、建立一个索引名为 Stusno 的唯一索引，该索引在 SNO 列的排序上具有唯一性，不存在重复值。 CREATE CLUSTER INDEX Coucno ON Couse（cno） ； 执行此命令后，为 Couse 表建立一个索引名为 Coucno 的聚集索引，Couse 表中的记录将按照 cno 值的升序存放。 CREATE UNIQUE INDEX SCno ON SC（sno ASC，Cno DESC） ； 执行命令后，为 SC 表建立一个索引名为 SCno 的唯一索引， 此索引为 SNO 和 CNO 两列的复合索引，即对 SC 表中的行先按 SNO 的递增顺序索引，对于相同的 SNO

37、，又按 CNO 的递增顺序索引。 由于有 UNIQUE 的限制，所以该索引在(SNO,CNO)组合列的排序上具有唯一性，不存在重复值。 DROP INDEX ; 授权 例 1 把查询 Student 表权限授给用户 U1 GRANT SELECT ON TABLE Student TO U1； 例 2 把对 Student 表和 Course 表的全部权限授给用户 U1 和 U2 GRANT ALL PRIVILEGES ON TABLE Student， Course TO U1，U2； 例 3 把对 SC 表的查询权限授给所有用户 GRANT SELECT ON TABLE SC TO P

38、UBLIC； 例 4 把查询 Student 表和修改学生学号的权限授给用户 U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4; 例 5 把对表 SC 的 INSERT 权限授予 U5 用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION; 例 6 DBA 把在数据库 S_C 中建立表的权限授予用户 U8 GRANT CREATETAB ON DATABASE S_C TO U8; 2、收回授权语句： REVOKE , ON FROM , ; 例 7 把用户

39、U4 修改学生学号的权限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4; 例 8 收回所有用户对表 SC 的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC; 求闭包 例已知 R(U),U=A,B,C,D,E,G， R 上的 FD 集 F=ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG， X=BD，求 X，BDA 是否成立？ (1)X（0）=BD。 (2)X（1）=BDEG (3)X（2）=BCDEG (4)X（3）=ABCDEG 最小函数依赖集 判断无损分解 例6.7 设 R(ABCDE)，

40、F=AC，BC，CD，DEC，CEA， =R1(AD)，R2(AB)，R3(BE)，R4(CDE)，R5(AE)，检验分解 是否具有无损连接性。 （1）构造表 A B C D E R1 a1 b12 b13 a4 b15 R2 a1 a2 b23 b24 b25 R3 b31 a2 b33 b34 a5 R4 b41 b42 a3 a4 a5 R5 a1 b52 b53 b54 a5 （2）根据依赖关系依次修正 修正AC A B C D E R1 a1 b12 b13 a4 b15 R2 a1 a2 b23 b24 b25 R3 b31 a2 b33 b34 a5 R4 b41 b42 a3

41、a4 a5 R5 a1 b52 b53 b54 a5 A B C D E R1 a1 b12 b13 a4 b15 R2 a1 a2 b13 b24 b25 R3 b31 a2 b33 b34 a5 R4 b41 b42 a3 a4 a5 R5 a1 b52 b13 b54 a5 （3）根据依赖关系依次进行修正，如果可以在一行中出现 a1,a2,a3,a4,a5 则表明保持无损连接，否则不是为无损连接 求解所有关键字 例：设 R（XYZW） ，F=WY ，YW， XWY ，ZWY， XZW ，求 R 的所有侯选关键字。 解：1、求 F 的最小依赖集 Fmin = WY ，YW， XY ，ZY

42、2、构造函数依赖图 FDG 3、关键属性集为X,Z 4、无独立回路。 故 R 只有唯一侯选关键字：XZ 例:设有关系模式 R（A,B,C,D,E） ，R 的函数依赖集 FABC,CDE,BD,EA，求 R 的所有候选键。 均为 LR 类,令 Y=ABCDE。 依次取一个属性，计算该属性对 F 的闭包 A+=ABCDE E+=ABCDE A，E 为 R 的候选键 从 Y 中去掉已经是候选键的属性 A 和 E，令 Y=BCD,再从 Y 中取两个属性，并计算其对 F的闭包 BC+=ABCDE CD+=ABCDE R 的候选键：A、E、BC 和 CD BCNF 分解 例：设 R（ABCDE） ，F=A

43、B ，BC， CDB，R 的码为 ADE 求 R 的具有无损连接的 BCNF 分解。 解：1、求 F 的最小函数依赖集 Fmin=AB ，BC， CDB 2、R 的码为 ADE， AB 不符合 BCNF 要求，分解： R1： U1 =A，B， F1 =AB，码为 A R2： U2 =A，C，D，E， F2 =AC，码为 ADE 得 = R1，R2 3、因为 R1 是 BCNF 范式，而 R2中 AC 不符合 BCNF 要求， 则对 R2分解： R3： U3 =A，C， F3 =AC，码为 A R4： U4 =A，D，E， F4 = ，码为 ADE 得 = R1，R3 ，R4 3、因为 R3 ，

44、R4是 BCNF 范式，则 R 的具有无损连接的 BCNF 分 解为： = R1（A，B） ，R3（A，C） ，R4（A，D，E） 例 1.具有 3 个批处理和使用了两种类型注释的脚本的例子。 程序清单： USE test -打开 test 数据库 GO - 多行（行内）注释的第一行 - 多行（行内）注释的第二行 SELECT * FROM S GO /* （块）注释语句的第一行. （块）注释语句的第二行. */ SELECT * FROM SC GO - 在 T-SQL 语言调试过程中使用注释语句 局部变量 例 2. GO 查询学生关系表 S 中女同学的信息。 USE JXGL -打开 JX

45、GL 数据库 GO DECLARE sex CHAR(2) -声明局部变量 SET sex = 女 -根据局部变量值进行查询 SELECT S# AS 学号, SNAME AS 姓名, SBIRTHIN AS 出生年月, PLACEOFB AS 籍贯, SCODE# AS 专业编号, CLASS AS 班级 FROM S WHERE SSEX = sex 例 3.查询和“王丽丽”在同一个班的女同学的信息。 USE JXGL -打开 JXGL 数据库 GO DECLARE sex CHAR(2) ,classl CHAR(6)- -声明局部变量 SET sex = 女 SELECT classl

46、 =CLASS FROM S WHERE SNAME=王丽丽 -根据局部变量值进行查询 SELECT S# AS 学号, SNAME AS 姓名, SBIRTHIN AS 出生年月, PLACEOFB AS 籍贯, SCODE# AS 专业编号, CLASS AS 班级 FROM S WHERE SSEX = sex AND CLASS=classl GO 例 4. 查询和“王丽丽”在同一个班的女同学的信息。 USE JXGL -打开 JXGL 数据库 GO DECLARE sex CHAR(2) ,classl CHAR(6) -声明局部变量 SET sex = 女 SELECT class

47、l =CLASS FROM S WHERE SNAME=王丽丽 -根据局部变量值进行查询 SELECT S# AS 学号, SNAME AS 姓名, SBIRTHIN AS 出生年月, PLACEOFB AS 籍贯, SCODE# AS 专业编号, CLASS AS 班级 FROM S WHERE SSEX = sex AND CLASS=classl GO PRINT (error) 例 5.按学号分组查询每个学生的平均成绩，并按比如 “ 200401001 同学的平均成绩为 88 分” 的格式显示 每个学生的平均成绩。 USE JXGL -打开 JXGL 数据库 GO SELECT S#

48、+ 同学的平均成绩为 + CAST(AVG(GRADE) AS CHAR(2) + 分 FROM SC GROUP BY S# GO 用户自定义函数 例 6. 创建一个返回今天是一周的第几天的用户自定义 标量函数。 create function get_weekday (date datetime) Returns int as begin return datepart(weekday, date) end 执行语句： Select dbo.get_weekday(convert(datetime,20150416,11) 执行结果： 例 7. 创建一个自定义函数，用于返回某班的学生关系表

49、。 USE JXGL GO CREATE FUNCTION s_table (class1 VARCHAR(7) RETURNS TABLE AS RETURN (select * from S where CLASS=class1) GO 执行语句： Select * from dbo. s_table（ 2000401 ） 【例】在 xsgl 数据库中，创建多语句表值函数。该函数接收专业编号，给出该专业所有学生的考试科数。 -建立新的函数 CREATE FUNCTION dbo.zy(ZY AS CHAR(8) RETURNS zykm TABLE( 学号 CHAR(10) PRIMARY

50、 KEY, 姓名 CHAR(6), 科数 INT ) AS BEGIN DECLARE ks TABLE( 学号 CHAR(10), 科数 INT ) INSERT ks SELECT 学号,科数=COUNT(学号) FROM cj GROUP BY 学号 INSERT zykm SELECT A.学号,A.姓名,B.科数 FROM S A ,ks B WHERE A.学号=B.学号 AND 专业=ZY RETURN END GO -调用函数显示某一专业的学生的考试科数 SELECT * FROM zy(c4001) GO 流程控制 DECLARE ErrorVar int BEGIN ins

51、ert into c(cno,cname,credit) values(c4010004, 计算机原理 ，3） select ErrorVar=error IF (ErrorVar 0) BEGIN PRINT 操作失败！ PRINT Error encountered,+cast(ErrorVar AS varchar(10) END END 例 8.查询学号为200401001同学的平均成绩，如果平均分在 60 分以上，显示200401001 同学成绩合格 ；否则显示200401001 同学成绩不合格 。 USE JXGL GO DECLARE text1 char(100), avg_g

52、rade float SELECT avg_grade =(SELECT AVG(GRADE) FROM SC WHERE S#=200401001) IF avg_grade 60 SET text1 = 200401001 同学成绩合格 ELSE SET text1 = 200401001 同学成绩不合格 SELECT text1 GO 【例】在 sc 表中查询是否开过“c401001”课，如果开过，计算该课的平均分。 IF EXISTS(SELECT * FROM sc WHERE cno=c401001) BEGIN DECLARE AVG FLOAT SET AVG=(SELECT

53、AVG(grade) FROM SC WHERE cno=c401001) SELECT 已开过, AVG AS 平均分 END ELSE PRINT 没有开过 【例】显示 sc 表中的数据，并使用 CASE 语句将课程号替换为课程名。 SELECT sno as 学号, 课程名=CASE cno WHEN c401001 THEN 数据结构 WHEN c401002 THEN 操作系统 WHEN c402001 THEN 计算机原理 WHEN c402002 THEN 通信原理 WHEN c403001 THEN 计算机网络 WHEN c403002 THEN 信息安全技术 WHEN c40

54、4001 THEN 信息编码与加密 END , grade as 成绩 FROM sc GO 例 9. 查询学号为200401001同学的平均成绩，如果平均分在 85 分以上，显示200401001同学成绩优秀 ；如果平均分在 85 分以下、60 分以上，显示200401001同学成绩合格 ；如果平均分在 60 分以下，显示200401001 同学成绩不合格 。 USE JXGL GO DECLARE avg_grade float SET avg_grade =(SELECT AVG(GRADE) FROM SC WHERE S#=200401001) SELECT CASE WHEN av

55、g_grade 85 THEN 200401001 同学成绩优秀 WHEN avg_grade 60 AND avg_grade =90 THEN 优 WHEN 成绩=80 THEN 良 WHEN 成绩=70 THEN 中 WHEN 成绩=60 THEN 及格 ELSE 不及格 END FROM sc 循环 【例】用 WHILE 语句计算 2 的 10 次方。 DECLARE MY_VAR INT,MY_RESULT INT SET MY_VAR=10 SET MY_RESULT=1 WHILE MY_VAR0 BEGIN SET MY_RESULT=MY_RESULT*2 SET MY_VA

56、R=MY_VAR-1 END PRINT MY_RESULT 【例】用循环的 PRINT 语句输出 S 表中女生的学号，姓名和系的信息。 use JXGL GO DECLARE info varchar(200), curs CURSOR SET curs=CURSOR FOR SELECT 学号： +sno+;姓名： +sname +;系： + sdept from S where sex=F OPEN curs FETCH NEXT FROM curs INTO info WHILE (fetch_status=0) BEGIN PRINT info FETCH NEXT FROM cur

57、s INTO info END GO 实现无条件退出执行的批处理命令、存储过程或触发器。 RETURN 语句可以返回一个整数给调用它的过程或应用程序，返回值 0 表明成功返回，保留 1 到 99 代表不同的出错原因。 【例】在 sc 表中查询某学生的某科成绩是否及格。 DECLARE x char(8),y char(7) Set x=20040201 Set y=c401001 IF(SELECT grade FROM SC WHERE sno=x AND cno=y)=60 RETURN 1 ELSE RETURN 2 -调用存储过程 MY_TEST，通过返回值判断是否及格 DECLARE

58、 i INT EXEC i=存储过程名字 IF i=1 PRINT 及格 ELSE PRINT 不及格 存储过程 例 3.创建一个存储过程，其实现的功能是返回名字中有一个丽字的学生信息。要求使用输入参数。 -使用带通配符参数的存储过程 USE JXGL- GO CREATE PROCEDURE S_info s_name varchar(10) =%丽% AS SELECT * FROM S WHERE SNAME LIKE s_name GO EXECUTE S_info杨% 例 4.创建一个存储过程， 其实现的功能是根据某学生的学号返回其所学的课程门数。 要求使用输入和输出参数. -带输入

59、和输出参数的存储过程 USE JXGL- GO CREATE PROCEDURE C_s s_number varchar(9)，total int OUTPUT AS SELECT total=count(C#) FROM SC WHERE S# = s_number GO 下面通过一个批处理来运行上面的存储过程。在批处理中，声明一个局部变量，执行上述存储过程并将游标赋值给局部游标变量，然后通过该游标变量读取记录。 USE jxgl GO DECLARE MyCursor CURSOR -声明输出参数 EXECUTE stu_cursor stu_cursor=MyCursor OUTPUT

60、 -执行存储过程 WHILE(FETCH_STATUS=0) -提取游标 BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor -关闭游标 DEALLOCATE MyCursor -释放游标 GO 【例】重新创建存储过程，使其每次运行时重新编译和优化。 USE xsgl GO IF EXISTS(SELECT name FROM SYSOBJECTS WHERE name=stu_cj1 AND type=P) DROP PROCEDURE stu_cj1 GO CREATE PROCEDURE stu_cj1 name char(10),cname char(16) WITH RECOMPILE AS SELECT s.学号,姓名,课程名,成绩 FROM s INNER JOIN sc ON s.学号=sc.学号 INNER JOIN c ON sc.课程号=c.课程号 WHERE s.姓名=name AND c.课程名=cname GO