《数据库系统》实验指导手册

数据库系统实验指导手册SQL Server 2005 的安装和环境介绍3实验一 使用向导创建和删除数据库12实验一 使用向导创建和删除数据库13实验二 使用SQL语句创建和删除数据库15实验二 分别用向导和SQL语句创建和删除表16Create Table Course17实验三 分别用向导和SQL语句创建和删除表19实验四 修改数据库基本表的定义21实验五 表更新操作23实验六 创建和删除索引25实验六 创建和删除索引26实验七 简单SELECT语句28实验八 子查询30实验九 使用子句的SELECT语句32实验十 创建视图36实验十一 使用视图38实验十二 SQL Server 2005 的安全性、备份与恢复40实验十二 SQL Server 2005 的安全性、备份与恢复41实验列表第1次实验：学会用SQL server2005的工具和SQL语言 （4学时） 实验一: 使用向导创建和删除数据库实验二: 使用SQL语句创建和删除数据库实验三: 两种方法创建和删除表实验四: 修改表的定义实验五 表更新操作第2次实验：熟练使用SQL查询语句，学会使用SQL更新语句，使用SQL创建和使用视图（4学时）实验六: 创建和删除索引实验七: 简单Select语句实验八: 子查询 实验九: 使用子句的Select语句第3次实验：学会用SQl server 2005创建和使用 触发器和存储过程 （4学时）实验十: 创建视图实验十一: 使用视图 实验十二: 数据库的安全性、备份和恢复SQL Server 2005 的安装和环境介绍安装SQL Server 20051 根据安装机器软硬件的要求，选择一个合适的版本，以下以开发版为例2 将 SQL Server 2005 DVD 插入 DVD 驱动器。如果DVD驱动器的自动运行功能无法启动安装程序无法启动安装程序，请导航到 DVD 的根目录然后启动splash.hta。3 在自动运行的对话框中，单击“运行 SQL Server 安装向导”。4 在“最终用户许可协议”页上，阅读许可协议，再选中相应的复选框以接受许可条款和条件。接受许可协议后即可激活“下一步”按钮。若要继续，请单击“下一步” 若要结束安装程序，请单击“取消”。如下图：5 在“SQL Server 组件更新”页上，安装程序将安装 SQL Server 2005 的必需软件。有关组件要求的详细信息，请单击该页底部的“帮助”按钮。若要开始执行组件更新，请单击“安装”。更新完成之后若要继续，请单击“完成”。6 在 SQL Server 安装向导的“欢迎”页上，单击“下一步”以继续安装。7 在“系统配置检查 (SCC)”页上，将扫描安装计算机，以检查是否存在可能妨碍安装程序的条件。8 在“注册信息”页上的“姓名”和“公司”文本框中，输入相应的信息。若要继续，请单击“下一步”9 在“要安装的组件”页上，请选择要安装的组件。选择各个组件组时，“要安装的组件”窗格中会显示相应的说明。您可以选中任意一些复选框。建议全选。若要安装单个组件，请单击“高级”。否则，请单击“下一步”继续。10 在“实例名”页上，请为安装的软件选择默认实例或已命名的实例。计算机上必须没有默认实例，才可以安装新的默认实例。若要安装新的命名实例，请单击“命名实例”，然后在提供的空白处键入一个唯一的实例名。11 在“服务帐户”页上，为 SQL Server 服务帐户指定用户名、密码和域名。您可以对所有服务使用一个帐户。12 在“身份验证模式”页上，选择要用于 SQL Server 安装的身份验证模式。如果选择 Windows 身份验证，安装程序会创建一个 sa 帐户，该帐户在默认情况下是被禁用的。选择“混合模式身份验证”时，请输入并确认系统管理员 (sa) 登录名。建议选择混合模式，并输入安全的密码。13 如果选择 Reporting Services 作为要安装的功能，将显示“报表服务器安装选项”页。使用单选按钮选择是否使用默认值配置报表服务器。如果没有满足在默认配置中安装 Reporting Services 的要求，则必须选择“安装但不配置服务器”安装选项。若要继续安装，请单击“下一步”14 在“错误报告”页上，可以清除复选框以禁用错误报告。有关错误报告功能的详细信息，请单击该页底部的“帮助”。若要继续安装，请单击“下一步”。15 在“准备安装”页上，查看要安装的 SQL Server 功能和组件的摘要。若要继续安装，请单击“安装”。16 在“安装进度”页上，可以在安装过程中监视安装进度。若要在安装期间查看某个组件的日志文件，请单击“安装进度”页上的产品或状态名称。17 在“完成 Microsoft SQL Server 安装向导”页上，可以通过单击此页上提供的链接查看安装摘要日志。若要退出 SQL Server 安装向导，请单击“完成”。18 如果提示您重新启动计算机，请立即重新启动。19 如果成功安装了SQL Server 2005，则在开始菜单中添加了如下程序和相应的服务：（2）SQL Server的环境介绍1. Analysis Services提供“部署向导”，为用户提供将某个Analysis Services项目的输出部署到某个目标服务器的功能。2. 配置工具其子菜单中提供的配置管理器“SQL Server Configulation manager”用于查看和配置SQL Server的服务。以下是SQL Server 2005 系统的6个服务。右击某个服务名称，可以查看该服务的属性，并且可以启动、停止、暂停和重新启动相应的服务。也可以使用操作系统“我的电脑”“管理”选项，在【计算机管理】窗口中查看和启动、停止、暂停和重新启动相应的服务。3. 文档和教程提供了SQL Server 2005的联机帮助和示例数据库概述。4. 性能工具子菜单提供了“SQL Server Profiler”和“数据库引擎优化顾问”用户数据库性能调试和优化工具。5.SQL Server Business Intelligence Development Studio商务智能（BI）系统开发人员设计的集成开发环境，构建于Visual Studio 2005 技术之上，为商业智能系统开发人员提供了一个丰富、完整的专业开发平台，支持商业智能平台上的所有组件的调试、源代码控制以及脚本和代码的开发。6.SQL Server Management Studio它将 SQL Server 早期版本中包含的SSMS、查询分析器和分析管理器的功能组合到单一环境中，为不同层次的开发人员和管理员提供 SQL Server 访问能力。实验一 使用向导创建和删除数据库一 实验目的1 熟悉SQL Server 2005 中SQL Server Management Studio的环境2 了解SQL Server 2005数据库的逻辑结构和物理结构3 掌握使用向导创建和删除数据库的方法二 实验准备 1装有SQL Server 2005的PC机。2. 明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。三 实验要求1. 熟练使用SSMS进行数据库的创建和删除操作2. 完成用向导建立和删除数据库的实验报告四 实验内容 设有一学籍管理系统，其数据库名为“EDUC”，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“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（简称SSMS）创建数据库。 1启动SSMS 在开始菜单中：所有程序SQL Server 2005 SQL Server Management Studio单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。2建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。 （2）使用向导删除上面建立的数据库。 用SSMS删除添加建立的数据库EDUC。实验二 使用SQL语句创建和删除数据库一. 实验目的：1、 了解SQL Server 2005数据库的逻辑结构和物理结构。2、 掌握使用SQL 语句创建和删除数据库。二. 实验准备1 明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。2 了解用SQL语句创建和删除数据库的基本语法。三. 实验要求3、 熟练使用查询分析器进行数据库的创建和删除操作4、 完成用sql语句建立和删除数据库的实验报告四. 实验内容 1以下是创建数据库userdb1的SQL语句，create database userdb1on(name=userdb4_data,-数据文件的逻辑名称,注意不能与日志逻辑同名filename=g:xygluserdb4.mdf ,-物理名称，注意路径必须存在size=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语句创建和删除表一实验目的1了解表的结构特点。2了解SQL Server的基本数据类型。3学会在SSMS中创建表。4学会使用T-SQL语句创建表。二实验要求1. 熟练使用SSMS创建和删除基本表。2. 是用查询分析器，完成用SQL语句创建和删除基本表。3. 完成用SSMS和SQL创建和删除基本表的实验报告。三. 实验准备1. 确定数据库包含包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。2. 已完成实验二，成功创建了数据库EDUC。3了解常用的创建表的方法。四. 实验内容1. 在实验三建立的数据库EDUC中，本课程实验需要如下几个表： 表2.1 class表（班级信息表）字段名称类 型允许空值主 键说 明ClsNOChar(6)NOT NULL是班号ClsNameVarchar(16)NOT NULL班名DirectorVarchar(10)NULL辅导员SpecialtyVarchar(30)NULL专业表2.2 student表（学生信息表）字段名称类 型允许空值主 键说 明SnoChar(8)NOT NULL是学号SnameVarchar(10)NOT NULL姓名SexChar(2)性别：男、女ClsNOChar(6)NULL班级的编号，（外键）参照表ClassSaddrVarchar(20)NULL住址Sagenumeric(3, 0)年龄，大于10，但小于30岁HeightDecimal(4,2)身高SdeptVarchar(30)NULL所在院系表2.3 course表（课程信息表）字段名称类 型允许空值主 键说 明CnoChar(4)NOT NULL是主键CnameVarchar(16)NOT NULL课程的名称Cpnochar(4)NULL先修课程的课程号（外键）,参照cnoCcreditTinyint学分表2.4 sc表（学生选课成绩表）字段名称类 型允许空值主 键说 明SnoChar(8)NOT NULL是学号，参照Student，与Cno组成主键CNOChar(4)NOT NULL是课程号，参照CourseScoreNumeric(4,1)NULL成绩2. 根据如上表结构用向导创建各表。3. 用SSMS删除所建立的表Student，Sc,和Course4. 在查询分析器中用sql语句删除表classDrop table class5. 根据如上表结构用SQL语句创建各表。CREATE TABLE class (ClsNO char(6) PRIMARY KEY,ClsName varchar(16) NOT NULL,Director varchar(10),Specialty varchar(30),stotal int NULL,monitor varchar(50) NULL） CREATE TABLE student (Sno char(8) PRIMARY KEY CLUSTERED,Sname varchar(10) NOT NULL,Ssex char(2),ClsNO char(6),Saddr varchar(20),Sage numeric(3, 0),Height decimal(4, 2),Sdept varchar(30)ALTER TABLE student WITH CHECK ADD CHECK (Sage(10) AND Sage(10) AND Sage(2004) AND 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”进行各种有关记录数据的操作；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软件学院插入数据之后使用命令：Select * from Student; 检查插入数据的正确性（2）向表（Class）中插入数据ClsNOClsNameDirectorSpecialtyCS01计算机一班王宁计算机应用CS02计算机二班王宁计算机应用MT04数学四班陈晨数学PH08物理八班葛格物理插入数据之后使用命令：Select * from Class; 检查插入数据的正确性（3）向表（Course ）中插入数据CnoCnameCpnoCredit0001高等数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select * from Course; 检查插入数据的正确性（4）向表（Grade ）中插入数据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实验八 子查询一 实验目的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 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 (select 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 课程的学生姓名；SELECT 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 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 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. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL 语言的嵌套查询语句的理解。二 实验准备1. 了解SELECT语句的GROUP BY和ORDER BY子句的作用。2. 了解统计函数和分组统计函数的使用方法。3. 熟悉查询分析器的运行环境。三 实验要求1. 在实验之前做好准备。2. 完成实验，并验收实验结果提交实验报告。四 实验内容 所有查询都全部用SQL语句实现1. 在数据库EDUC中实现如下查询1)求学生的总人数。 select count(*) from student2)求选修了课程的学生人数。select count(distinct 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(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）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。 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是电话char15是借阅：列名类型长度是否可为空串号char10书号char10读者编号char10借阅日期datetime8要求：图书表：书号为主码读者表：编号为主码；性别只能是“男”或“女”的CHECK 约束借阅表：串号为主码；书号为外码，参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合UNIQUE 约束实验数据：图书：书号类别出版社作者书名定价1001计算机机械工业出版社王民数据结构801002计算机机械工业出版社张建平计算机应用201003计算机电子工业出版社王敏数据库技术151004计算机电子工业出版社谭浩强C 语言251005英语中国人民大学出版社张锦芯应用文写作251006管理高等教育出版社Robison管理学151007管理机械工业出版社Fayol工业管理701008数学机械工业出版社李平线性代数501009管理机械工业出版社Durark公司的概念141010数学机械工业出版社徐新国统计学15读者：编号姓名单位性别电话1001丁一数学院男812345671002赵二经济学院男822345671003张三管理学院女832345671004李四文学院男842345671005王五历史文化学院女852345671006孙六物理学院男862345671007周七生命科学院女872345671008徐八化学院男882345671009宋九信息学院女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 图书values(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 图书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,李四,文学院,男,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 借阅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 into 借阅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. 用不同的方法创建视图。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,score 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中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。 (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命令的使用，并注意视图更新与基本表更新的区别与联系3.