数据库系统实验指导书.doc

《数据库系统实验指导书.doc》由会员分享，可在线阅读，更多相关《数据库系统实验指导书.doc（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库系统实验指导书齐鲁理工学院目录实验一 使用向导创建和删除数据库3实验二 使用SQL语句创建和删除数据库5实验三 分别用向导和SQL语句创建和删除表6实验四 修改数据库基本表的定义8实验五 表更新操作10实验六 创建和删除索引12实验七 简单SELECT语句15实验八 子查询17实验九 使用子句的SELECT语句19实验十 创建视图23实验十一 使用视图25实验十二 SQL Server 2005 的安全性、备份与恢复27实验一 使用向导创建和删除数据库一 实验目的1 熟悉SQL Server 2005 中SQL Server Management Studio的环境2 了解SQL Ser

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

3、名为“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（简称SSMS）创建数据库。 1启动SSMS

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

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

6、,-数据初始长度为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表输入表的

7、属性：用以上方法在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) AND

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

9、；2.、掌握T-SQL中的INSERT、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软件学院插入数据之后使用命令：Sele

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

11、de ）中插入数据SNOCNOScore20100101000190201001010007862010010200018720100102000376201003060001872010030600039320100106000785（5） 对于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实验八

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

13、 on s.Sno=t.Sno join Course c on o=o and ame=高等数学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 (selec

14、t max(Sage) from student where sdept = 计算机学院)and 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 课程的学生姓名；SELE

15、CT sno,sname FROM student WHERE NOT EXISTS (SELECT * 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

16、 from SC where Cno in (select Cno from SC where 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

17、not exists(select *from sc sczwhere scx.sno =scz.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. 熟练掌握数据查

18、询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL 语言的嵌套查询语句的理解。二 实验准备1. 了解SELECT语句的GROUP BY和ORDER BY子句的作用。2. 了解统计函数和分组统计函数的使用方法。3. 熟悉查询分析器的运行环境。三 实验要求1. 在实验之前做好准备。2. 完成实验，并验收实验结果提交实验报告。四 实验内容 所有查询都全部用SQL语句实现1. 在数据库EDUC中实现如下查询1)求学生的总人数。 select count(*) from student2)求选修了课程的学生人数。select count(dist

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

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

21、书和总价格。 SELECT 书号,书名,定价FROM 图书WHERE 类别=计算机 ORDER BY 出版社COMPUTE COUNT(书号),SUM(定价) BY 出版社 COMPUTE COUNT(书号),SUM(定价)5）查询计算机类和机械工业出版社出版的图书。 select 书号,书名from 图书where 类别=计算机and 出版社=机械工业出版社表结构如下：图书：列名类型长度是否可为空书号char10类别char12是出版社char50作者char20是书名char50定价money8是读者：列名类型长度是否可为空编号char10姓名char8单位char50是性别char2是电

22、话char15是借阅：列名类型长度是否可为空串号char10书号char10读者编号char10借阅日期datetime8要求：图书表：书号为主码读者表：编号为主码；性别只能是“男”或“女”的CHECK 约束借阅表：串号为主码；书号为外码，参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合UNIQUE 约束实验数据：图书：书号类别出版社作者书名定价1001计算机机械工业出版社王民数据结构801002计算机机械工业出版社张建平计算机应用201003计算机电子工业出版社王敏数据库技术151004计算机电子工业出版社谭浩强C 语言251005英语中国人民大学

23、出版社张锦芯应用文写作251006管理高等教育出版社Robison管理学151007管理机械工业出版社Fayol工业管理701008数学机械工业出版社李平线性代数501009管理机械工业出版社Durark公司的概念141010数学机械工业出版社徐新国统计学15读者：编号姓名单位性别电话1001丁一数学院男812345671002赵二经济学院男822345671003张三管理学院女832345671004李四文学院男842345671005王五历史文化学院女852345671006孙六物理学院男862345671007周七生命科学院女872345671008徐八化学院男882345671009宋

24、九信息学院女892345671010刘十计算机学院女80234567借阅：串号书号读者编号借阅日期01100110032000-1-102100210052002-3-503100310081998-6-1804100410031997-12-805100510102001-5-406100610012005-7-250710071010997-11-308100810092004-2-109100910041996-9-110101010082000-6-4insert into 图书values(1001,计算机,机械工业出版社,王民,数据结构,80)insert into 图书value

25、s(1002,计算机,机械工业出版社,张建平,计算机应用,20)insert into 图书values(1003,计算机,电子工业出版社,王敏,数据库技术,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 图

26、书values(1008,数学,机械工业出版社,李平,线性代数,50)insert into 图书values(1009,管理,机械工业出版社,Durark,公司的概念,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,李四,文

27、学院,男,84234567)insert into 读者values(1005,王五,历史文化学院,女,85234567)insert into 读者values(1006,孙六,物理学院,男,86234567)insert into 读者values(1007,周七,生命科学院,女,87234567)insert into 读者values(1008,徐八,化学院,男,88234567)insert into 读者values(1009,宋九,信息学院,女,89234567)insert into 读者values(1010,刘十,计算机学院,女,80234567)insert into 借

28、阅values(01,1001,1003,2000-1-1)insert into 借阅values(02,1002,1005,2002-3-5)insert 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 in

29、to 借阅values(08,1008,1009,2004-2-1)insert into 借阅values(09,1009,1004,1996-9-1)insert into 借阅values(10,1010,1008,2000-6-4)实验十 创建视图一 实验目的1掌握创建视图的SQL语句的用法。2掌握使用企业管管理器创建视图的方法。3掌握使用创建视图向导创建视图的方法。4掌握查看视图的系统存储过程的用法。5掌握修改视图的方法。二 实验准备1了解创建视图方法。2了解修改视图的SQL语句的语法格式。3了解视图更名的系统存储过程的用法。4了解删除视图的SQL语句的用法。三 实验要求1. 用不同

30、的方法创建视图。2. 提交实验报告，并验收实验结果。四 实验内容1. 创建视图（1） 使用SSMS创建视图 在EDUC库中以“student”表为基础，建立一个名为“V_计算机系学生”的视图。在使用该视图时，将显示“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,scor

31、e from student s,course c,sc where s.sno=sc.sno and o=o 建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图COMPUTE_AVG_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,cno2. 修改视图 (1) 使用SSMS修改视图在SSMS中将视图COMPUTE_AVG_GRADE中改成建立在数学

32、系的学生学号、选修课程号以及平均成绩的视图。 (2) 使用SQL语句修改视图 在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。EXEC sp_rename V_计算机系学生, V_计算机系男生3. 删除视图（1） 使用SSMS删除视图 用SSMS删除视图“V_计算机系学生”（2） 使用SQL语句删除视图用SQL语句删除视图COMPUTE_AVG_GRADE;drop view COMPUTE_AVG_GRADE实验十一 使用视图一 实验目的1. 熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；2. 熟悉和掌握对数据表中视图的更新操作和SQL

33、命令的使用，并注意视图更新与基本表更新的区别与联系3. 学习灵活熟练的进行视图的操作，认识视图的作用二 实验准备1熟悉SQL SERVER 工作环境。2能连接到EDUC数据库。3复习有关视图操作的SQL语言命令。三 实验要求1. 在实验开始之前做好准备工作。2. 实验之后提交实验报告，思考视图和基本表的区别四 实验内容一 定义视图在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,s