数据库系统实验指导书

1、数据库系统实验指导书齐鲁理工学院目录 TOC o 1-3 h z u HYPERLINK l _Toc427478313 实验一 使用向导创建和删除数据库 PAGEREF _Toc427478313 h 3 HYPERLINK l _Toc427478314 实验二 使用SQL语句创建和删除数据库 PAGEREF _Toc427478314 h 5 HYPERLINK l _Toc427478315 实验三 分别用向导和SQL语句创建和删除表 PAGEREF _Toc427478315 h 6 HYPERLINK l _Toc427478316 实验四 修改数据库基本表的定义 PAGEREF

2、_Toc427478316 h 8 HYPERLINK l _Toc427478317 实验五 表更新操作 PAGEREF _Toc427478317 h 10 HYPERLINK l _Toc427478318 实验六 创建和删除索引 PAGEREF _Toc427478318 h 12 HYPERLINK l _Toc427478319 实验七 简单SELECT语句 PAGEREF _Toc427478319 h 15 HYPERLINK l _Toc427478320 实验八 子查询 PAGEREF _Toc427478320 h 17 HYPERLINK l _Toc427478321

3、 实验九 使用子句的SELECT语句 PAGEREF _Toc427478321 h 19 HYPERLINK l _Toc427478322 实验十 创建视图 PAGEREF _Toc427478322 h 23 HYPERLINK l _Toc427478323 实验十一 使用视图 PAGEREF _Toc427478323 h 25 HYPERLINK l _Toc427478324 实验十二 SQL Server 2005 的安全性、备份与恢复 PAGEREF _Toc427478324 h 27实验一 使用向导创建和删除数据库实验目的熟悉SQL Server 2005 中SQL Se

4、rver Management Studio的环境了解SQL Server 2005数据库的逻辑结构和物理结构掌握使用向导创建和删除数据库的方法实验准备 1装有SQL Server 2005的PC机。2. 明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。实验要求熟练使用SSMS进行数据库的创建和删除操作完成用向导建立和删除数据库的实验报告实验内容 设有一学籍管理系统，其数据库名为“EDUC”，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为

5、“student_data”, 物理文件名为“student_data.mdf，存放路径为“E:sql_data”。日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“E:sql_data”。 (1) 使用向导创建上诉描述的数据库。 使用SQL Server Management Studio（简称SSMS）创建数据库。 （2）使用向导删除上面建立的数据库。 用SSMS删除添加建立的数据库EDUC。实验指导使用向导创建和删除数据库(1) 使用向导创建上诉描述的数据库。 使用SQL Server Management Studio（简称

6、SSMS）创建数据库。 1启动SSMS 在开始菜单中：所有程序SQL Server 2005 SQL Server Management Studio单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。2建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。 （2）使用向导删除上面建立的数据库。 用SSMS删除添加建立的数据库EDUC。实验二 使用SQL语句创建和删除数据库一. 实验目的：了解SQL Server 2005数据

7、库的逻辑结构和物理结构。掌握使用SQL 语句创建和删除数据库。二. 实验准备明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。了解用SQL语句创建和删除数据库的基本语法。三. 实验要求熟练使用查询分析器进行数据库的创建和删除操作完成用sql语句建立和删除数据库的实验报告四. 实验内容 1以下是创建数据库userdb1的SQL语句，create database userdb1on(name=userdb4_data,-数据文件的逻辑名称,注意不能与日志逻辑同名filename=g:xygluserdb4.mdf ,-物理名称，注意路径必须存在si

8、ze=5,-数据初始长度为5Mmaxsize=10,-最大长度为10Mfilegrowth=1)-数据文件每次增长1Mlog on( name=userdb4_log, filename=g:xygluserdb4.ldf , size=2 , maxsize=5 , filegrowth=1)运行上诉语句建立数据库userdb12.根据1的sql语句，写出创建数据库EDUC的sql语句，并建立数据库EDUC.3.用SQL语句删除步骤一建立的数据库userdb1。实验三 分别用向导和SQL语句创建和删除表实验指导创建表一用向导创建表用SQL Server 2005的SSMS创建student表

9、输入表的属性：用以上方法在SSMS中创建其他表。二用SQL语句创建表在SQL Server 2005 的查询分析器中，用sql语句创建表。如student表：CREATE TABLE student(Sno char(8) PRIMARY KEY , -学号(主键)Sname varchar(10) NOT NULL, -姓名Ssex char(2) CHECK (Ssex=女 OR Ssex=男), -性别ClsNO char(6) , -班级编号Saddr varchar(20) , -住址Sage numeric(3, 0) CHECK (Sage(10) AND Sage(2004)

10、AND year=(2008)删除列 （1）用SQL语句将Course表中的year字段删除。 ALTER TABLE course drop column year实验五 表更新操作实验目的学会在SSMS中操作表。熟悉使用UPDATE/INSERT/DELETE语句进行表操作；能将这些更新操作应用于实际操作中去；实验准备成功完成实验四，向EDUC数据库的各表中插入、修改和删除数据。了解INSERT、UPDATE及DELETE语句。实验要求完成下面的实验内容，并提交实验报告；在实验报告中附上相应的代码；实验内容1使用“SSMS”进行各种有关记录数据的操作；2.、掌握T-SQL中的INSERT、

11、UPDATE及DELETE语句的使用方法(1) 向表（Student）中插入数据SnoSnameSsexClsNOSaddrSageHeightSdept20100101王军男CS01下关40#201.76计算机学院20100102李杰男CS01江边路96#221.72计算机学院20100306王彤女MT04中央路94#191.65数学学院20100107吴杪女PH08莲化小区74#181.60物理学院20100501张亮男SS01一环路193#181.71软件学院20100502李梅女SS01长江路2#181.60软件学院插入数据之后使用命令：Select * from Student; 检

12、查插入数据的正确性（2）向表（Class）中插入数据ClsNOClsNameDirectorSpecialtyCS01计算机一班王宁计算机应用CS02计算机二班王宁计算机应用MT04数学四班陈晨数学PH08物理八班葛格物理插入数据之后使用命令：Select * from Class; 检查插入数据的正确性（3）向表（Course ）中插入数据CnoCnameCpnoCredit0001高等数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select * from Course; 检查插入数据的正确性（4）向表（Grade ）中插入数据SNOCNOScore

13、20100101000190201001010007862010010200018720100102000376201003060001872010030600039320100106000785对于student表，将所有班级号为CS01的，并且年龄小于20岁的学生的班级号改为CS02 Update studentSet clsNo=CS02 where clsNo=CS01 and Sage=90 查询每一门课的间接先行课。 select o,s.cpno from course f join course s on f.cpno=o实验八 子查询实验目的掌握子查询的表示。进一步掌握SQL

14、 Server 查询分析器的使用方法，加深对SQL 语言的嵌套查询语句的理解实验准备掌握SQL语句的基本语法，并熟悉查询分析器的工作环境。完成了实验四，创建了数据库EDUC及各个基本表。了解子查询的表示方法，熟悉IN 比较符、ANY、EXISTS操作符的用法。实验要求在实验开始之前做好准备工作。完成实验，并验收实验结果提交实验报告。实验内容1在数据库EDUC中实现一下查询：1）求选修了高等数学的学生学号和姓名；select t.sno,t.Sname from Student t join SC s on s.Sno=t.Sno join Course c on o=o and ame=高等数

15、学2）求0001 课程的成绩高于张三的学生学号和成绩； select sc.sno,score from SC where cno=0001 and score(select score from sc,student s where cno=0001 and sname=张三 and sc.sno=s.sno)3）求其他专业中比计算机应用专业某一学生年龄小的学生信息（即求其它系中年龄小于计算机应用专业年龄最大者的学生）；select * from student where Sage (select max(Sage) from student where sdept = 计算机学院)and

16、 sdept 计算机学院4）求其他系中比计算机应用专业学生年龄都小的学生信息；select * from student where Sage (select min(Sage) from student where sdept = 计算机学院)and sdept 计算机学院;5）求选修了0002 课程的学生姓名；SELECT sname FROM Student JOIN SC ON Student.Sno = SC.Sno and cno=00026）求没有选修0002 课程的学生姓名；SELECT sno,sname FROM student WHERE NOT EXISTS (SELE

17、CT * FROM SC WHERE sno=student.sno AND cno=0002)7）查询选修了全部课程的学生的姓名；select Sname from Student swhere not exists (select c.* from Course cwhere not exists (select * from SC where Sno=S.Sno and Cno=C.Cno) 8）求至少选修了学号为“20100102”的学生所选修的全部课程的学生学号和姓名。select Sno from SC where Cno in (select Cno from SC where

18、Sno=20100102) group by Sno having count(*)=(select count(*) from SC where Sno=20100102)select distinct scx.sno,snamefrom sc scx,studentwhere scx.sno =student.sno and not exists(select *from sc scy,studentwhere sno =20100102and scy.sno =student.sno and not exists(select *from sc sczwhere scx.sno =scz

19、.sno and o =o) with t as(select num=count(1),a.SNO from Sc a,Sc b where a.CNO=b.CNO and b.SNO=20100102 group by a.SNO) select t.SNO,sname from t,student s where num=(select num from t where SNO=20100102) and t.sno=s.sno实验九 使用子句的SELECT语句实验目的1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server 查询分析器的使用，加深

20、对SQL 语言的嵌套查询语句的理解。实验准备了解SELECT语句的GROUP BY和ORDER BY子句的作用。了解统计函数和分组统计函数的使用方法。熟悉查询分析器的运行环境。实验要求在实验之前做好准备。完成实验，并验收实验结果提交实验报告。实验内容 所有查询都全部用SQL语句实现在数据库EDUC中实现如下查询1)求学生的总人数。 select count(*) from student2)求选修了课程的学生人数。select count(distinct sno) from sc 3)求课程的课程号和选修该课程的人数。select cno,count(*) from sc group by

21、cno4)求选修课超过3 门课的学生学号 SELECT sno FROM sc GROUP BY sno HAVING COUNT(*)3提高操作实验 建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2 倍。 select A.类别from 图书as A group by A.类别having max(A.定价)=ALL(select 2*avg(B.定价) from 图书as B group by B.类别)2）求机械工业出版社出版的各类图书的平均定价，分别用GROUP

22、BY 和GROUP BY ALL表示。 select distinct 类别, avg(定价) 平均定价 from 图书where 出版社=机械工业出版社 group by 类别3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。 SELECT 书号,书名,定价FROM 图书WHERE 类别=计算机 COMPUTE SUM(定价),COUNT(书号)4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。 SELECT 书号,书名,定价FROM 图书WHERE 类别=计算机 ORDER BY 出版社COMPUTE COUNT(书号),SUM(定

23、价) BY 出版社 COMPUTE COUNT(书号),SUM(定价)5）查询计算机类和机械工业出版社出版的图书。 select 书号,书名from 图书where 类别=计算机and 出版社=机械工业出版社表结构如下：图书：列名类型长度是否可为空书号char10类别char12是出版社char50作者char20是书名char50定价money8是读者：列名类型长度是否可为空编号char10姓名char8单位char50是性别char2是电话char15是借阅：列名类型长度是否可为空串号char10书号char10读者编号char10借阅日期datetime8要求：图书表：书号为主码读者表：

24、编号为主码；性别只能是“男”或“女”的CHECK 约束借阅表：串号为主码；书号为外码，参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合UNIQUE 约束实验数据：图书：书号类别出版社作者书名定价1001计算机机械工业出版社王民数据结构801002计算机机械工业出版社张建平计算机应用201003计算机电子工业出版社王敏数据库技术151004计算机电子工业出版社谭浩强C 语言251005英语中国人民大学出版社张锦芯应用文写作251006管理高等教育出版社Robison管理学151007管理机械工业出版社Fayol工业管理701008数学机械工业出版社李平

25、线性代数501009管理机械工业出版社Durark公司的概念141010数学机械工业出版社徐新国统计学15读者：编号姓名单位性别电话1001丁一数学院男812345671002赵二经济学院男822345671003张三管理学院女832345671004李四文学院男842345671005王五历史文化学院女852345671006孙六物理学院男862345671007周七生命科学院女872345671008徐八化学院男882345671009宋九信息学院女892345671010刘十计算机学院女80234567借阅：串号书号读者编号借阅日期01100110032000-1-10210021005

26、2002-3-503100310081998-6-1804100410031997-12-805100510102001-5-406100610012005-7-250710071010997-11-308100810092004-2-109100910041996-9-110101010082000-6-4insert into 图书values(1001,计算机,机械工业出版社,王民,数据结构,80)insert into 图书values(1002,计算机,机械工业出版社,张建平,计算机应用,20)insert into 图书values(1003,计算机,电子工业出版社,王敏,数据库技

27、术,15)insert into 图书values(1004,计算机,电子工业出版社,谭浩强,C 语言,25)insert into 图书values(1005,英语,中国人民大学出版社,张锦芯,应用文写作,25)insert into 图书values(1006,管理,高等教育出版社,Robison,管理学,15)insert into 图书values(1007,管理,机械工业出版社,Fayol,工业管理,70)insert into 图书values(1008,数学,机械工业出版社,李平,线性代数,50)insert into 图书values(1009,管理,机械工业出版社,Durar

28、k,公司的概念,14)insert into 图书values(1010,数学,机械工业出版社,徐新国,统计学,15)insert into 读者values(1001,丁一,数学院,男,81234567)insert into 读者values(1002,赵二,经济学院,男,82234567)insert into 读者values(1003,张三,管理学院,女,83234567)insert into 读者values(1004,李四,文学院,男,84234567)insert into 读者values(1005,王五,历史文化学院,女,85234567)insert into 读者va

29、lues(1006,孙六,物理学院,男,86234567)insert into 读者values(1007,周七,生命科学院,女,87234567)insert into 读者values(1008,徐八,化学院,男,88234567)insert into 读者values(1009,宋九,信息学院,女,89234567)insert into 读者values(1010,刘十,计算机学院,女,80234567)insert into 借阅values(01,1001,1003,2000-1-1)insert into 借阅values(02,1002,1005,2002-3-5)inse

30、rt into 借阅values(03,1003,1008,1998-6-18)insert into 借阅values(04,1004,1003,1997-12-8)insert into 借阅values(05,1005,1010,2001-5-4)insert into 借阅values(06,1006,1001,2005-7-25)insert into 借阅values(07,1007,1010,997-11-3)insert into 借阅values(08,1008,1009,2004-2-1)insert into 借阅values(09,1009,1004,1996-9-1)

31、insert into 借阅values(10,1010,1008,2000-6-4)实验十 创建视图实验目的1掌握创建视图的SQL语句的用法。2掌握使用企业管管理器创建视图的方法。3掌握使用创建视图向导创建视图的方法。4掌握查看视图的系统存储过程的用法。5掌握修改视图的方法。实验准备1了解创建视图方法。2了解修改视图的SQL语句的语法格式。3了解视图更名的系统存储过程的用法。4了解删除视图的SQL语句的用法。实验要求用不同的方法创建视图。提交实验报告，并验收实验结果。实验内容创建视图使用SSMS创建视图 在EDUC库中以“student”表为基础，建立一个名为“V_计算机系学生”的视图。在使

32、用该视图时，将显示“student”表中的所有字段. create view V_计算机系学生as select * from student where sdept=计算机学院(2) 使用SQL语句创建视图 在查询分析器中建立一个每个学生的学号、姓名、选修的课名及成绩的视图S_C_GRADE；create view S_C_GRADE as select s.sno,sname,cname,score from student s,course c,sc where s.sno=sc.sno and o=o 建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图COMPUTE_AVG_

33、GRADE;create view COMPUTE_AVG_GRADE as select s.sno,cno,avg(score) as pjcj from student s,sc where s.sno=sc.sno and sdept=计算机学院 group by s.sno,cno修改视图 (1) 使用SSMS修改视图在SSMS中将视图COMPUTE_AVG_GRADE中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。 (2) 使用SQL语句修改视图 在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。EXEC sp_rename

34、 V_计算机系学生, V_计算机系男生删除视图使用SSMS删除视图 用SSMS删除视图“V_计算机系学生”使用SQL语句删除视图用SQL语句删除视图COMPUTE_AVG_GRADE;drop view COMPUTE_AVG_GRADE实验十一 使用视图实验目的熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系学习灵活熟练的进行视图的操作，认识视图的作用实验准备1熟悉SQL SERVER 工作环境。2能连接到EDUC数据库。3复习有关视图操作的SQL语言命令。实验要求在实验开始之前做好准备工作。实

35、验之后提交实验报告，思考视图和基本表的区别实验内容定义视图在EDUC数据库中，以Student、Course 和Sc 表为基础完成以下视图定义定义计算机系学生基本情况视图V_Computer；create view V_Computer as select * from student where sdept=计算机学院将Student、 Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_Gcreate view V_S_C_G as select s.sno,sname,o,cname,score from student s,course c,sc whe

36、re s.sno=sc.sno and o=o将各系学生人数，平均年龄定义为视图V_NUM_AVGcreate view V_NUM_AVG as select sdept,count(*),avg(Sage) as Avgage from student group by sdept定义一个反映学生出生年份的视图V_YEAR create view V_YEAR as select year(getdate()-sage from student将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G create view V_AVG_S_G as select sno,count(

37、*) as cnum avg(score) as avgscore from sc group by sno将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G create view V_AVG_C_G as select cno,count(*) as snum,avg(score) as avgscore from sc group by cno使用视图查询以上所建的视图结果。查询平均成绩为90分以上的学生学号、姓名和成绩； select s.sno,sname,avgscore from V_AVG_S_G v,student s where s.sno=v.sno and av

38、gscore90查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；select SC.*,Student.sname from SC join Student on SC.SNO=Student.SNO where not exists( select a.SNO,b.SNO from SC a left join ( select a.* from SC a join V_AVG_C_G b on a.CNO=b.CNO and a.score =b.avgscore )b on a.SNO=b.SNO and a.CNO=b.CNO where SC.SNO=a.SNO and b.

39、SNO is null )按系统计各系平均成绩在80分以上的人数，结果降序排列； select sdept,count(*)as cnum,avg(score) as score from sc join student s on sc.sno=s.sno group by sdept having avg(score)80 order by score desc修改视图 create view V_IS as select * from student where sdept=IS通过视图V_计算机系，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结

40、果; update V_计算机系set sno=S1_MMM where sno=S1update V_计算机系set sno=S4_MMM where sno=S4通过视图V_IS，新增加一个学生记录 (S12,YAN XI,19,IS)，并查询结果 insert into V_IS系(Sno,Sname,Sage,Sdept) values (S12,YAN XI,19,IS)通过视图V_IS，新增加一个学生记录 (S13,YAN XI,19,MA)，并查询结果通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果delete V_IS where sno=S12 or s

41、no=s3要通过视图V_S_C_GRADE，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？并说明原因可以update V_S_C_G set sname=S12_MMM where sno=S12 要通过视，图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？并说明原因实验十二 SQL Server 2005 的安全性、备份与恢复一、实验目的1.掌握 SQL Server中的触发器的使用方法；2.掌握存储过程的操作方法。二、实验准备 了解数据库的安全性、备份与恢复 三、实验要求1. 在实验之前做好准备2.利用SSMS可视化设置数据库用户及操作权限；进行数据

42、库的备份和恢复；3.利用Transact-SQL语句进行备份和恢复操作。4. 试验之后提交实验报告，并验收实验结果四、实验内容3.实验内容 管理和设计SQL Server登录信息，实现服务器级的安全控制。 设计和实现数据库级的安全保护机制。 设计和实现数据库对象级的安全保护机制使用SSMS规划和执行备份操作使用Transact-SQL语句执行备份操作 backup database EDUC to DISK = h:databaseEDUC2.bak使用SSMS执行恢复操作使用Transact-SQL语句执行恢复操作 RESTORE DATABASE tt FROM DISK = h:data

43、baseEDUC.bak with MOVE EDUC TO D:tt.mdf, MOVE EDUC_log TO d:tt_log.ldf创建维护数据库的备份设备实验指导一在SSMS中为所属的SQL 服务器设置为Windows身份验证模式、SQL Server和Windows混合安全认证模式。其步骤如下：1在SSMS中为所属的SQL 服务器设置为SQL Server和Windows 混合安全认证模式。其步骤如下：在企业管理器窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，在出现的对话框中选择“安全性”，则出现如图12-1所示的对话框。 图12-1 SQL

44、Server属性对话框服务器身份验证选择“SQL Server和Windows”单选项。在SQL Server企业管理器中为自己建立一个服务器用户、数据库用户和数据库角色。在SSMS窗口中展开服务器组，展开服务器，用鼠标单击“安全性”文件夹右侧的+，用鼠标右击“登录名”，在弹出的菜单中选择“新建登录名”项，则出现新建登录对话框，如图12-2所示。图12-2 新建登录对话框在新建登录对话框中有常规，服务器角色、用户映射、安全对象和状态五个选项卡。在常规选项卡中，输入用户名，选择SQL Server安全验证，输入用户密码。在服务器角色选项卡中，需要确定用户所属的服务器角色，在本例中采用缺省值即可。

45、在用户映射选项卡中，需要指定此登录可以访问的数据库（本例选中EDUC）、数据库角色（在本例中采用缺省值即可）。单击“确定”按钮，既完成了创建登录用户的工作。新建数据库引擎查询，如图12-3所示。在连接到数据库引擎窗口（图12-4）选择SQL Server安全验证，用户名为humin，输入用户口令，连接到SQL Server。在“查询”窗口中可以输入SQL语句。如“SELECT * FROM student”。运行后，得到消息“拒绝了对对象student（数据库EDUC，所有者dbo）的SELECT权限。可见用户humin没有对学生表的SELECT权限。图12-3图12-4要想将EDUC数据库的操作权限赋予数据库用户humin，有两种方法。方法一：通过SSMS在SSMS中展开服务器组，展开服务器，用鼠标单击“数据库”文件夹左侧的+，用鼠标单击EDUC数据库文件夹左侧的+， 用鼠标单击安全性文件夹左侧的+用鼠标右击“用户”。选择“humin”项，用鼠标右击，在弹出的菜单中选择“属性”项，则出现数据库用户属性对话框，如图12-5所示。图12-5 数据库用户属性对话框如图12-6所示，安全对象对话框下面是有关数据库用户和角色所对应的权限表。这些权限均以复选框的形式表示。复选框有三种状态：“”为授权；“”为废除权；空