2015网络数据库实验指导书.doc

网络数据库实验指导书计算机科学与技术专业佛山科学技术学院计算机系2015年3月51目 录实验一 数据库和数据库表操作 1实验二 数据库单表查询 5实验三 数据库多表连接查询 9实验四 数据库嵌套查询和组合查询 11实验五 数据的更新 17实验六 T-SQL语言 19实验七 视图与索引 24实验八 存储过程的实现 27实验九 触发器的实现 32实验十 数据库的安全性 36实验十一 备份、还原与导入、导出 37实验十二 数据库应用系统设计38参考资料1王珊 萨师煊.数据库系统概论（第四版）.北京：高等教育出版社，20062方风波. 网络数据库项目教程. 北京：电子工业出版社，20123吴德胜. SQL Server入门经典. 北京：机械工业出版社，2013公共邮箱：邮箱密码：computer学生-课程数据库xscj中用到的四个表文件如下：1.学生表（Student表）Student表（学生表）结构字段名称数据类型长度空值说明Snochar10否学号Snamevarchar8否姓名Ssexchar2性别Sbirthsmalldatetime出生日期Sagesmallint年龄Dnochar2否系代号Student表（学生表）记录SnoSnameSsexSbirthSageDno备注2012314101李勇男1995-09-1320DX0112计算机1班2012314102刘晨女1996-07-2819DX0112计算机1班2012314201欧阳原野男1998-11-0217DX0112计算机2班2012314202周小李女1997-01-1518DX0112计算机2班2012394101郑涛男1996-05-1419DX0112网络1班2012394201吴小莉女1997-11-0518DX0112网络2班2012374120王敏女1999-07-1618DX0212电子信息工程1班2012214230欧大海男1996-04-0419LX0112数学与应用数学2班2.课程表（Course表）Course表（课程表）结构字段名称数据类型长度空值说明Cnochar6否课程编号Cnamevarchar20课程名称Cpnochar6先修课Ccreditsmallint学分Course表（课程表）记录CnoCnameCpnoCcredit1数据库542数学NULL23信息系统144操作系统635数据结构746数据处理NULL27PASCAL语言648DB_Design123.成绩表（SC表）SC表（成绩表）结构字段名称数据类型长度空值说明Snochar10否学号Cnochar6否课程编号Gradesmallint成绩SC表（成绩表）记录SnoCnoGrade201231410119220123141012592012314101388201231410217820123141022952012314102380201231420119020123142021802012394101169201239420118820123741201452012214230110020123141014NULL4.系表（Department表）Department表（系表）结构字段名称数据类型长度空值说明Dnochar2否系代号Dnamevarchar20系名称Collegevarchar20所属学院Department表（系表）结构DnoDnameCollegeDX01计算机系电信学院DX02电子系电信学院LX01数学系理学院LX02物理系理学院实验一 数据库和数据库表操作一、实验目的和要求1掌握利用查询窗口和对象资源管理器进行数据库及基本表的定义、删除与修改；2掌握索引的建立与删除的方法。二、实验内容与步骤（一）建立数据库【SY1-01】通过企业管理器或查询分析器建立学生-课程数据库xskc。CREATE DATABASE xscj ON PRIMARY(NAME=xscj_data,FILENAME=E:sjk2015xscj.mdf)LOG ON(NAME=xscj_log,FILENAME=E:sjk2015xscj.ldf)【注】先在E：盘上建立一个文件夹（例如：E:sjk2015），数据库文件保存到自建的文件夹中。（二）基本表的定义、修改与删除1定义基本表利用查询窗口和对象资源管理器创建基本表，并输入数据。【SY1-02】建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、出生日期Sbirth、年龄Sage、系代号Dno五个属性组成。要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。CREATE TABLE Student( Sno char(10) PRIMARY KEY, Sname Varchar(8) NOT NULL, Ssex char(2) DEFAULT 男 CHECK(Ssex in(男,女), Sbirth smalldatetime NULL, Sage int null, Dno char(4) NULL)【SY1-03】建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。要求“课程号”为主键，“课程名”属性不能为空。CREATE TABLE Course(Cno char(6) PRIMARY KEY, Cname varchar(20) NOT NULL, Cpno char(6) Null, Ccredit smallint)【SY1-04】建立学生成绩表SC，包含学号Sno、课程号Cno、成绩Grade三个字段。要求建立主键及与student、sc表联接的外键，并创建检查约束(Grade=0 and Grade=100)。CREATE TABLE SC(Sno char(10) NOT NULL, Cno char(6) NOT NULL, Grade int CHECK(Grade BETWEEN 0 AND 100), PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno)【SY1-05】建立系表Department，包含系代号Dno、系名称Dname、所属学院College三个字段。CREATE TABLE Department( Dno char(4) PRIMARY KEY, Dname Varchar(20) NOT NULL, College Varchar(20)2 修改基本表利用查询窗口和对象资源管理器修改基本表。【SY1-06】 向基本表Student中增加“入学时间”属性列，其属性名为S_entrance，数据类型为日期型。ALTER TABLE Student ADD S_entrance DATETIME;【SY1-07】 将Student表中Sage(年龄)的数据类型改为SMALLINT型。ALTER TABLE Student ALTER COLUMN Sage SMALLINT;【SY1-08】 增加课程名称必须取唯一值的约束条件。ALTER TABLE Course ADD UNIQUE(Cname);【SY1-09】将Student表的Dno列允许空值的属性更改为不允许为空。ALTER TABLE Student ALTER COLUMN Dno CHAR(4) NOT NULL;【SY1-10】删除Student表中的S_entrance列。ALTER TABLE Student DROP COLUMN S_entrance;3 删除基本表【SY1-11】 删除Student表。DROP TABLE Student;说明：此表删除后，请立即将其建立起来，以便后面的例子使用。（三）索引的建立和删除1 建立索引【SY1-12】 在基本表Student的Sname(姓名)列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。其语句为：CREATE CLUSTERED INDEX Stu_Sname ON Student(Sname);【SY1-13】分别为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按Sno(学号)升序建唯一索引，Course表按Cno(课程号)升序建唯一索引，SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。其语句为：CREATE UNIQUE INDEX Stu_Sno ON Student(Sno);CREATE UNIQUE INDEX Cou_Cno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);2 删除索引【SY1-14】删除Student表的Stu_Sname索引。DROP INDEX Student.Stu_Sname;建立数据库文件CREATE DATABASE xscj ON PRIMARY(NAME=xscj_data,FILENAME=E:sjk2015xscj.mdf)LOG ON(NAME=xscj_log,FILENAME=E:sjk2015xscj.ldf)建立表结构USE xscjCREATE TABLE Student( Sno char(10) PRIMARY KEY, Sname Varchar(8) NOT NULL, Ssex char(2) DEFAULT 男 CHECK(Ssex in(男,女), Sbirth smalldatetime NULL, Sage int null, Dno char(4) NOT NULL)CREATE TABLE Department( Dno char(4) PRIMARY KEY, Dname Varchar(20) NOT NULL, College Varchar(20)CREATE TABLE Course(Cno char(6) PRIMARY KEY, Cname varchar(20) NOT NULL, Cpno char(6) Null, Ccredit smallint)CREATE TABLE SC(Sno char(10) NOT NULL, Cno char(6) NOT NULL, Grade int CHECK(Grade BETWEEN 0 AND 100), PRIMARY KEY(Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno)输入表记录INSERT INTO Student VALUES(2012314101,李勇,男,1995-09-13,null,DX01)INSERT INTO Student VALUES(2012314102,刘晨,女,1996-07-28,null,DX01)INSERT INTO Student VALUES(2012314201,欧阳原野,男,1998-11-02,null,DX01)INSERT INTO Student VALUES(2012314202,周小李,女,1997-01-15,null,DX01)INSERT INTO Student VALUES(2012394101,郑涛,男,1996-05-14,null,DX01)INSERT INTO Student VALUES(2012394201,吴小莉,女,1997-11-05,null,DX01)INSERT INTO Student VALUES(2012374120,王敏,女,1999-07-16,null,DX02)INSERT INTO Student VALUES(2012214230,欧大海,男,1996-04-04,null,LX01)UPDATE Student SET Sage=year(getdate()-year(Sbirth)INSERT INTO Department VALUES(DX01,计算机系,电信学院)INSERT INTO Department VALUES(DX02,电子系,电信学院)INSERT INTO Department VALUES(LX01,数学系,理学院)INSERT INTO Department VALUES(LX02,物理系,理学院)INSERT INTO Course VALUES(1,数据库,5,4)INSERT INTO Course VALUES(2,数学,NULL,2)INSERT INTO Course VALUES(3,信息系统,1,4)INSERT INTO Course VALUES(4,操作系统,6,3)INSERT INTO Course VALUES(5,数据结构,7,4)INSERT INTO Course VALUES(6,数据处理,NULL,2)INSERT INTO Course VALUES(7,C语言,6,4)INSERT INTO Course VALUES(8,DB_Design,1,2)INSERT INTO SC VALUES(2012314101,1,92)INSERT INTO SC VALUES(2012314101,2,59)INSERT INTO SC VALUES(2012314101,3,88)INSERT INTO SC VALUES(2012314102,1,78)INSERT INTO SC VALUES(2012314102,2,90)INSERT INTO SC VALUES(2012314102,3,80)INSERT INTO SC VALUES(2012314201,1,90)INSERT INTO SC VALUES(2012314202,1,80)INSERT INTO SC VALUES(2012394101,1,85)INSERT INTO SC VALUES(2012394201,1,88)INSERT INTO SC VALUES(2012374120,1,45)INSERT INTO SC VALUES(2012214230,1,100)INSERT INTO SC VALUES(2012314101,4,NULL)实验二 数据库单表查询1 无条件查询【SY2-01】查询全体学生的详细信息。这是一个无条件的选择查询，其命令为：SELECT * FROM Student或SELECT Sno,Sname,Ssex,Sbirth,Dno FROM Student【SY2-02】查询全体学生的姓名（Sname）、学号（Sno）。这是一个无条件的投影查询，其命令为：SELECT Sname,Sno FROM Student;【SY2-03】查询全体学生的学号（Sno）、姓名（Sname）及年龄。由于SELECT子句的不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：SELECT Sno, Sname, year(getdate()-year(Sbirth) as 年龄 FROM Student;【SY2-04】查询全体学生的学号、姓名、年龄和所在系，要求用小写字母表示所有系代号。SELECT Sno, Sname, year(getdate()-year(Sbirth) as 年龄, LOWER(Dno)DnoFROM Student;【SY2-05】查询选修了课程的学生学号。SELECT DISTINCT SnoFROM SC;2 条件查询【SY2-06】查询计算机系（DX01）全体学生的学号（Sno）和姓名 （Sname）和系代号（Dno）。SELECT Sno, Sname,DnoFROM StudentWHERE Dno=DX01;【SY2-07】查询考试成绩有不及格的学生的学号。SELECT DISTINCT SnoFROM SCWHERE Grade60;【SY2-08】查询所有年龄在19岁以下的学生姓名（Sname）及年龄。SELECT Sname,SageFROM StudentWHERE Sage=18 AND Sage=20;或SELECT Sname, SageFROM StudentWHERE Sage BETWEEN 18 AND 20;【SY2-10】 查询年龄不在18-20岁之间的学生姓名（Sname）及年龄（Sage）。SELECT Sname, SageFROM StudentWHERE Sage NOT BETWEEN 18 AND 20;【SY2-11】查询计算机系和数学系学生的信息。SELECT * FROM StudentWHERE Dno IN (DX01, LX01);或SELECT * FROM StudentWHERE Dno=DX01 OR Dno=LX01【SY2-12】 查询既不是计算机系，也不是数学系的学生的信息。SELECT * FROM StudentWHERE Dno NOT IN (DX01, LX01);或SELECT * FROM StudentWHERE Dno!=DX01 AND Dno!=LX01【SY2-13】查询所有姓“欧”的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。 SELECT Sname, Sno, SsexFROM StudentWHERE Sname LIKE 欧%;【SY2-14】查询姓“欧”且全名为3个汉字的学生的信息。SELECT * FROM StudentWHERE Sname LIKE 欧_ _;【SY2-15】 查询所有不姓“刘”的学生姓名(Sname)和年龄(Sage)。SELECT Sname, SageFROM StudentWHERE Sname NOT LIKE 欧%;【SY2-16】查询课程名为“DB_Design”的课程号信息。 SELECT Cno,Cname,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ;【SY2-17】查询以DB_开头，且倒数第3个字符为“i”的课程的详细情况。SELECT *FROM CourseWHERE Cname LIKE DB_%i_ESCAPE;【SY2-18】 假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。SELECT Sno,Cno,GradeFROM SCWHERE Grade IS NULL;【SY2-19】查询所有有成绩的学生学号（Sno）和课程号（Cno）。SELECT Sno, Cno,Grade FROM SC WHERE Grade IS NOT NULL;【SY2-20】查询计算机系年龄在20岁以下的学生姓名。SELECT *FROM StudentWHERE Dno=DX01 AND Sage2实验三 数据库多表连接查询一、连接查询1 不同表之间的连接查询【SY3-01】 查询每个学生及其选修课程的情况。本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：SELECT Student.*, SC.*FROM Student, SCWHERE Student.Sno = SC.Sno； 说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno= SC.Sno;2 自身连接【SY3-02】 查询每一门课的间接先修课(即先修课的先修课)。在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Course表与其自身连接。为方便连接运算，这里为Course表取两个别名分别为A，B。则完成该查询的SQL语句为：SELECT A.Cno, A.Cname, B.CpnoFROM Course A, Course BWHERE A.Cpno =B.Cno;3 外连接【SY3-03】把例33中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为：SELECT Student.Sno, Sname, Ssex, Sdept, Cno, GradeFROM StudentLEFT JOIN SC ONStudent.Sno= SC.Sno;说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。SELECT Student.Sno, Sname, Ssex, Sdept, Cno, GradeFROM SCRIGHT JOIN Student ONSC.Sno=Student.Sno;4 复合条件连接【SY3-04】 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno;二、连接查询练习题1查询全体学生学号、姓名、性别、年龄和所在系；2查询计算机系年龄在19岁以上的学生的学号、姓名、出生日期；3查询选修了“数学”或“数据库”课程学生的学号、姓名、成绩；4查询选修了“数据库”课程的苏俄生的学号、姓名和成绩，查询结果按成绩降序排序；5.查询每个学生的学号、姓名、选修课的课程名及成绩；6.将所有学生所学课程成绩低于75分的课程名列出来。附源代码：1.SELECT Sno,Sname,Ssex,Year(Getdate()-Year(Sbirth) as 年龄,Dname FROM Student,DepartmentWHERE Student.Dno=Department.Dno或SELECT Sno,Sname,Ssex,Datediff(Year,Sbirth,Getdate() as 年龄,Dname FROM Student,DepartmentWHERE Student.Dno=Department.Dno2SELECT Sno,Sname,SbirthFROM StudentWHERE YEAR(getdate()-Year(Sbirth)19 and Dno=DX01或SELECT Sno,Sname,SbirthFROM Student,DepartmentWHERE Year(Getdate()-Year(Sbirth)25 and (Student.Dno=Department.Dno and Dname=计算机系)3.SELECT SC.Sno,Sname,Grade FROM Student,Course,SCWHERE Student.Sno=SC.Sno and Course.Cno=SC.Cno and (Cname=数学 or Cname=数据库)4.SELECT SC.Sno,Sname,GradeFROM Student,Course,SCWHERE Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname=数据库ORDER BY Grade DESC5.SELECT SC.Sno,Sname,Cname,GradeFROM Student,Course,SCWHERE Student.Sno=SC.Sno and Course.Cno=SC.Cno6.SELECT SC.Sno,Sname,Cname,GradeFROM Student,Course,SCWHERE Student.Sno=SC.Sno and Course.Cno=SC.Cno and GradeANY(SELECT Sbirth FROM Student WHERE Dno=DX01) AND DnoDX01【方法二】SELECT *FROM StudentWHERE Sbirth(SELECT MIN(Sbirth) FROM Student WHERE Dno=DX01) AND DnoDX01【SY4-07】查询其他系中比计算机系（DX01）学生年龄都小的学生。【方法一】SELECT *FROM StudentWHERE SbirthALL(SELECT Sbirth FROM Student WHERE Dno=DX01) AND DnoDX01【方法二】SELECT *FROM StudentWHERE Sbirth(SELECT MAX(Sbirth) FROM Student WHERE Dno=DX01) AND DnoDX014 带谓词EXISTS的嵌套查询【SY4-08查询所有选修了编号为“1”课程的学生姓名(Sname)。SELECT Sno,SnameFROM StudentWHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1 )【SY4-09查询所有选修了编号为“2”课程的学生姓名(Sname)。SELECT Sno,SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=2 )【SY4-10询与“李勇”在同一个系学习的学生信息。将【SY4-01】改为带谓词EXISTS的查询，其SQL语句如下：SELECT Sno,Sname,DnoFROM Student AWHERE EXISTS(SELECT * FROM Student B WHERE B.Dno=A.Dno AND B.Sname=李勇 )【SY4-11查询选修了所有课程的学生姓名。由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。其SQL语句为：SELECT Sno,SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno ) )二、集合查询1并运算【SY4-12查询选修了1号课程或4号课程的学生学号。【方法一】SELECT Sno FROM SC WHERE Cno=1UNIONSELECT Sno FROM SC WHERE Cno=4【方法二】SELECT DISTINCT SnoFROM SC WHERE Cno=1 OR Cno=4【SY4-13查询电子系（DX02）的学生或年龄小于19岁的学生信息。【方法一】SELECT * FROM Student WHERE Dno=DX02UNIONSELECT * FROM Student WHERE YEAR(GETDATE()-YEAR(Sbirth)19【方法二】SELECT * FROM Student WHERE Dno=DX02 OR YEAR(GETDATE()-YEAR(Sbirth)192.交运算【SY4-14查询选修了1号课程并且同时选修了4号课程的学生学号。【方法一】SELECT Sno FROM SC WHERE Cno=1INTERSECTSELECT Sno FROM SC WHERE Cno=4【方法二】SELECT SnoFROM SCWHERE Cno=1 AND Sno IN ( SELECT Sno FROM SC WHERE Cno=4 )【SY4-15】查询计算机系（DX01）的学生且年龄小于19岁的学生的交集，这实际上就是查询计算机系中年龄小于26岁的学生。【方法一】SELECT * FROM Student WHERE Dno=DX01INTERSECTSELECT * FROM Student WHERE YEAR(GETDATE()-YEAR(Sbirth)19【方法二】SELECT * FROM Stud