数据库原理 实验报告

1、实验一实验名称： 熟悉SQL Server 2008的工作环境 成绩： 实验目的1.熟悉企业管理器、查询管理器的使用。2.熟悉SQL Server 2008给出的样例数据库的内容。实验内容和步骤1. 练习企业管理器的使用。熟练掌握SQL Server服务管理器和企业管理器的启动及参数配置等方法。2. 熟悉样例数据库。 在企业管理器中，依次打开两个样例数据库：Pubs、Northwind，查看其所拥有的数据对象，主要包括表、试图、存储过程、角色、规则、默认值、定义的数据类型、定义的函数等。3. 练习查询分析器的使用。 （1）打开企业管理器的工具菜单，如下图所示：4. 熟悉系统数据库的结构。（1）

2、 在企业管理器中展开Master数据库节点。（2） 熟悉Master数据库中的所有表。（3） 找出名为sysservers的表，右击该表名，选择“打开表”“返回所有行”命令。 （4）按照此方法查看其他表的内容。实验二实验名称： 创建数据库和数据表 成绩： 实验目的1. 了解SQL Server 2008数据库的逻辑结构和物理结构。2. 了解表的结构特点。3. 熟悉SQLServer的基本数据类型。4. 了解数据库、数据表、约束等相关的概念。5. 实现在企业管理器中创建数据库和表。6. 掌握使用Transact-SQL语句创建数据库和表的方法。实验内容和步骤练习创建数据库和数据表。（1） 打开“

3、我的电脑”或“资源管理器”，在磁盘中以自己的姓名或学号建立文件夹。（2） 创建名为“学生-选课”（即名为JWGL）的数据库，并存储在地（1）步建立的文件下。（3） 在建立的JWGL数据库中按照表9-1表9-3所示创建student、course和sc表9-1 student表9-2 course表9-3 sc(4)在数据表中输入数据。为表1和表3建立主键约束。实验三实验名称： 基本数据查询 成绩： 实验目的1. 掌握SELECT-From-Where语句的结构及使用，实现在数据库上进行简单查询。2. 掌握各种查询操作方法。实验内容和步骤练习1：简单查询（1） 查询全体学生的学号与姓名。SELE

4、CT Sno,Sn FROM STUDENT （2） 查询全体学生的姓名及年龄。SELECT Sn,Age FROM STUDENT （3） 查询全体学生的姓名、年龄及所在的系别（要用小写字母表示系名， 并用“系名”来表示列名）SELECT Sn,Age,Dept AS 系名 FROM STUDENT （4） 查询选修了课程的学生学号。SELECT Sno FROM SC WHERE Cno IS NOT NULL练习2：条件查询 （1）查询所有年龄在20岁以下的学生的姓名及其年龄SELECT Sn,Age FROM STUDENT WHERE Age<20; （2）查询年龄在20到23

5、岁之间的学生的姓名系别及年龄SELECT Sn,Dept,Age FROM STUDENT WHERE Age BETWEEN 20 AND 23;（4）查询年龄不在2023岁之间的学生的姓名、系别及年龄。SELECT Sn,Dept,Age FROM STUDENT WHERE Age NOT BETWEEN 20 AND 23; （4）查询所有姓“刘”的学生的姓名、学号和性别SELECT Sn,Sno,Sex FROM STUDENT WHERE Sn LIKE '刘%' （5）查询全体学生情况，按所在系的编号升序排列，同一系的学生按年龄降序排列SELECT * FROM

6、STUDENT ORDER BY IDCard ASC,Age Desc; （5） 查询所有不姓李的学生的姓名SELECT Sn FROM STUDENT WHERE Sn NOT LIKE '李%'实验四实验名称： 数据分组与汇总 成绩： 实验目的1. 掌握数据分组查询的方法。 2. 掌握统计函数的使用。2. 使用GROUP BY和HAVING子句对数据分组查询。4. 使用相应的运算符或子句生成汇总数据。实验内容和步骤练习1：使用统计函数（1） 查询学生的总人数SELECT COUNT(*) AS '人数' FROM STUDENT （2） 查询选修课程的人数

7、SELECT COUNT(Sno) FROM STUDENT WHERE DEPT IS NOT NULL; （3）计算选修了2 号课程的学生的平均成绩 SELECT AVG(Grade) AS 平均成绩 FROM SC WHERE (Cno='150102');（4）查询选修了3号课程的学生的最高分 SELECT MAX(Grade) AS最高分 FROM SCWHERE (Cno='150103');练习2：分组查询（1）求有成绩的每个学生的平均成绩 SELECT Sno,AVG(Grade) AS 学生平均成绩 FROM SC GROUP BY Sno;（

8、2）求有成绩的每个学生的总成绩 SELECT Sno,SUM(Grade) AS 学生总成绩 FROM SC GROUP BY Sno;（3）求每门课程的平均成绩 SELECT Cno,AVG(Grade) AS 课程平均成绩 FROM SC GROUP BY Cno;（3） 求平均成绩大于85分的学生的学号及平均成绩SELECT Sno,AVG(Grade) AS Grade FROM SC GROUP BY Sno HAVING AVG(Grade)>85; （5）求平均成绩大于85分的课程的课程号及课程平均成绩。 SELECT Cno,AVG(Grade) AS Grade FRO

9、M SC GROUP BY Cno HAVING AVG(Grade)>85;（6）求各个课程号及相应的选课人数 程序：SELECT Cno,COUNT(Cno) AS 选课人数 FROM SC GROUP BY Cno; （7）查询人数多于2人（含2人）的系的编号及人数 SELECT IDCard,COUNT(IDCard) AS 人数 FROM Student GROUP BY IDCard HAVING COUNT(IDCard)>=2;实验五实验名称： 多表连接 成绩： 实验目的1. 了解多表连接的作用。2. 掌握使用不同类型的连接方式进行表的连接操作的方法。实验内容和步骤

10、1. 查询选修“数据结构”课程的学生的学号 Select Sno as 学号,Cn as 选课 from SC,Course Where SC.Cno=Course.Cno and Course.Cn='数据结构'2. 查询学生姜珊选修的课程的平均分Select avg(Grade) as 平均分 from Student,SC Where Student.Sno=SC.Sno and Sn='姜珊' 3. 查询选修“数据库原理”课程的学生的平均分Select avg(Grade)as 平均分 from Course,SC Where Course.Cno=SC

11、.Cno and Cn='数据库原理' 4. 查询学生“李斌”的所有成绩，显示的列包括姓名，成绩Select Sn, Grade as 成绩 from Student ,SC Where Student.Sno=SC.Sno and Sn='李斌'5. 查询学生选修课的考试情况，显示的列名包括学生姓名，课程名称，成绩Select Sn as 学生姓名,Cno as 课程名称, Grade as 成绩 from Student,SC Where Student.Sno=SC.Sno;6. 查询成绩大于70分的学生姓名Select Sn from Student ,

12、SCWhere Student.Sno=SC.Sno and Grade>70;实验六实验名称： 数据更新操作 成绩： 实验目的1. 掌握在企业管理器中对数据表进行插入、修改、删除的数据操作。2. 掌握使用Transact-SQL语句对数据库表进行插入、修改和删除的数据操作。3. 了解Transact-SQL语句对表数据操作的灵活控制能力。实验内容和步骤1. 向学生表中插入学生“XXX”的基本信息Insert into Student(Sno,Sn,Sex,Age,IDCard,Dept)Values('100107','); 2. 修改学生“XXX”的信息 3.

13、 删除课程中“数据库原理”的课 Delete from Course where Cn='数据库原理'实验七实验名称： 索引及其应用 成绩： 实验目的1. 掌握使用企业管理器和Transact-SQL语句创建索引的方法。2. 了解不同索引对查询性能的影响。实验内容和步骤1. 给S建立一个唯一索引CREATE UNIQUE INDEX STU ON Student(Sn);2. 给S建立一个唯一索引程序：create unique index SCI on Student(Sno,Sn) 结果：实验八实验名称： 实现视图 成绩： 实验目的1.掌握使用企业管理器和Transact-

14、SQL语句创建视图的方法。2.握视图加密技术。3.学会通过视图来修改数据。实验内容和步骤1. 创建和检验试图 CREATE VIEW St AS SELECT Sno,Sn,Dept FROM Student WITH CHECK OPTION;2. 创建一个新的学生信息表的视图，并插入一行新数据。1. 创建和检验试图 程序：SELECT Sno,Sn,Dno FROM Student 结果： 3. 创建一个新的学生信息表的视图，并插入一行新数据。 程序: CREATE VIEW SeASSELECT Sno,Sn, Sex,Age,Bp,Dno FROM Student 结果： 程序：INS

15、ERT INTO Se(Sno,Sn)VALUES('100107','束毓岚 '); 实验九实验名称： 实现存储过程 成绩： 实验目的1. 掌握使用企业管理器和Transact-SOL语句创建存储过程的方法。2. 掌握带参数和不带参数的存储过程的创建方法。3. 掌握显示存储过程信息的方法。实验内容1. 创建如下不带参数的简单存储过程。查询成绩在6084分之间的学上的学号和课程号。程序：USE JWGL GO CREATE proc 成绩 AS SELECT Sno,Cno FROM SC WHERE Grade BETWEEN 60 AND 84;结果：2.

16、创建如下带输入参数的存储过程。（1）根据输入的学号，删除该学生的选课成绩记录。程序：CREATE proc myprocd Sno char(5) AS  PRINT 'Sno：'    DELETE  FROM  SCWHERE Sno=Sno结果：（2）向SC表中插入数据。程序：CREATE proc myprocd_SC Sno char(5),Cno char(1),Grade decimal(4,1) AS PRINT 'Sno：,Cno: ,Grade: 'IN

17、SERT INTO SC(Sno,Cno,Grade)VALUES(Sno,Cno,Grade)结果：3. 创建如下带输出参数的存储过程，并写出一个调用该过程的例子。根据输入的学号、课程号，输出其成绩。程序：CREATE procd 查询成绩Sno char(5) ,Cno char(1) AS  SELECT Grade FROM  SCWHERE SC.Sno=Sno AND SC.Cno=Cno结果：实验十实验名称： 实现触发器 成绩： 实验目的1.创建维护数据完整性的触发器2.创建强制复杂业务规则的触发器实验内容针对学生表（Student）、课程

18、表（Course）、学生选课表（SC）和表9-4，实现如下实验：创建一个名为stu_insert的触发器，向学生表Student插入记录时，自动更新班级表中的class中的学生人数Number。表9-4 班级表（Class）ClnoSpecialityNumber95311计算机软件295312计算机应用2程序：CREATE TRIGGER stu_insert ON StudentFOR INSERT, UPDATEASUPDATE Class SET Number=Number+1WHERE Clno=(SELECT Clno FROM inserted) 结果：实验十一实验名称： SQL

19、 Server的安全管理 成绩： 实验目的1. 掌握的SQL Server安全性机制。2. 熟练掌握实现权限授予和回收的方法。3. 掌握设计和实现安全保护机制的方法。实验内容1. 在企业管理器中设置SQL Server2000登录身份验证模式。步骤为：打开“企业管理器”展开“SQL Server组”选中“数据库服务器”单击鼠标右键，选择“属性”命令“安全性”选项卡设置身份验证模式为“SQL Server和Windows”。2. 添加SQL Server身份验证登录账户。（1） 分别使用SQL语句和图形化界面方式添加以下两个SQL Server验证登录账户。 账户1名为TestDB，密码为123

20、456；程序：exec sp_addlogin 'TestDB','123456'结果： 账户2为CreateDB，密码为654321程序：exec sp_addlogin 'CreateDB','654321'结果：（2） 写出添加账户1的SQL语句。exec sp_addlogin 'TestDB','123456'3. 服务器角色的管理。（1） 在企业管理器中查找所有服务器角色。（2） 尝试添加或删除服务器角色。（3） 尝试修改服务器角色的权限。（4） 将账户CreateDB加入到Dbceat

21、or角色成员中(写出其SQL语句)。4. 添加或删除数据库用户角色。（1） 使用图形化工具盒SQL语句两种方法，为JWGL数据库添加TestDB，并在默认状态下查看数据库角色类型。（2） 授予CreateDB操作JWGL数据库的所有权限。（3） 尝试为数据库新增数据库角色。（4） 了解服务器角色和数据库角色的区别。5. 授权和回收权限。（1） 授予TestDB对Student表执行SELECT的权限。程序：GRANT SELECT ON TABLE StudentTO TestDB;（2） 授予TestDB对SC表执行SELECT、INSERT的权限，并允许其将该权限授予其他用户。程序：GRA

22、NT SELECT,INSERT ON TABLE SCTO TestDB;（3） 收回TestDB的SELECT和INSERT权限。程序：REVOKE SELECT,INSERT ON TABLE SC FROM TestDB;实验十二实验名称： 备份与恢复 成绩： 实验目的1.掌握使用企业管理器规划和执行备份操作的方法2.掌握使用企业管理器执行恢复操作的方法实验内容1.创建备份设备在本练习中，利用SQL server企业管理器和Transact-SQL语句，创建包含数据库和事务日志备份的备份设备。（1） 企业管理器备份数据库。利用SQL server企业管理器，对JWGL数据库执行完全数据库备份，并且将其存储到磁盘文件中。在企业管理器中，选择“管理” “备份” “备份数据库”命令，利用表9-5所示信息修改相应的备份选项，单击“确定”按钮执行备份工作。 表9-5 备份信息字段值数据库JWGL名称JWGL