数据库应用实验教案

1、 课程实 验 教 案课程编号： 总学时： 适用年级专业（学科类）： 开课时间 使用教材： 授课教师姓名： 实验二：数据库定义实验一、实验目的掌握在SQL Server Query Analyzer中利用CREATE、DROP、ALTER等SQL语句创建和删除数据库，创建、删除、更新基本表。二、实验内容1、利用查询分析器创建学生课程数据库（student），保存在学生学号命名的文件夹下。 注意观察数据库文件的后缀名。2、利用查询分析器在student数据库中，创建基本表学生（Student）、课程（Course）、选课（SC）,各表属性具体描述如下：学生（学号，姓名，年龄，性别，所在系）； /*

2、学号是主码，姓名取值惟一*/课程（课程号，课程名，先行课，学分）； /*课程号是主码*/选课（学号，课程号，成绩）。 /*学号和课程号为主码*/Student(Sno char(10), Sname char(10), Ssex char(2), Sage int, Sdept Varchar(10) )Course(Cno char(4), Cname char(20), Cpno char(4), Ccredit int) SC(Sno char(10), Cno char(4), Grade int)要求：<为属性选择合适的数据类型。<定义必要的列级约束。<建库、建表、

3、建立表间联系（表间完整性约束）。注意：（1）将建立表SQL语句保存为以表名命名的sql脚本文件。（2）Allow Null和Default Value在定义表中的约束作用是什么？3、在查询分析器窗体下键入如下命令DROP TABLE Student及DROP TABLE SC命令,运行后观察数据库student中的表有什么变化？再重新运行student.sql及 sc.sql，再观察数据库student中的表有什么变化？分析DROP TABLE Student及DROP TABLE SC命令的功能。4、在查询分析器窗体下完成下列操作命令： （1）在Student表中增加一个新的属性“专业”Sm

4、ajor char(20);（2）将Student表中的Sdept字段的长度更改为20；（3）删除Student表中刚增加的属性“专业”；（4）删除Student表、SC表和Course表。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统实验三：数据更新实验一、实验目的了解企业管理器中数据表数据的输入、修改和删除操作，熟练掌握使用SQL语句向数据库输入数据、修改数据和删除数据的方法。二、实验内容学生课程数据库的三个基本表为学生（学号，姓名，年龄，性别，所在系）；课程（课程号，课程名，先行课，学分）；选课（学号，课程号，成绩）。1、利用企业

5、管理器更新数据在学生课程数据库的学生、课程和选课个表中各输入下列表中的记录。Student表学号姓名性别年龄所在系200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19ISCourse表课程号课程名先行课学分1数据库542数学23信息系统144操作系统635数据结构746数据处理27Pascal语言64SC表学号课程号成绩2002151211922002151212852002151213882002151222902002151223802、使用查询分析器更新数据，写出相应的SQL语句，并查看、分析执行的结果。（1）使用S

6、QL语句删除Student表、SC表和Course表中的所有数据；（2）使用SQL语句将上面三个表中的数据添加到Student表、SC表和Course表中；（3）将Student表中每个学生的年龄都增加1岁；（4）将SC表中每个学生的每门课程成绩都乘以0.8。（5）思考：删除表和删除表中的数据使用的SQL语言有什么不同？三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统实验四：数据查询实验一、实验目的了解在企业管理器或查询分析器中执行数据查询的方法；掌握SQL Server Query Analyzer中简单查询、连接查询、嵌套查询操作方法

7、。二、实验内容1、使用企业管理器进行查询（验证性，了解部分）（1）查询数据表的全部数据在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，这时将显示该表的全部数据。（2）使用SELECT语句查询数据表的数据在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏SQL窗格”，把子窗口分为上下两部分，上面部分能输入不同的SQL命令。执行时单击工具栏按钮“运行”即可。（3）学习和使用QBE查询在企业管理器中，选择需要查询数据的表，用鼠标右键单击该表，从弹出的快捷

8、菜单中选择“打开表”命令，在打开返回表内容的子窗口中，单击工具栏按钮“显示/隐藏条件窗格”，把子窗口分为上下两部分，上面部分能选择QBE查询条件，执行时单击工具栏按钮“运行”即可。若单击工具栏按钮“显示/隐藏SQL窗格”，则在SQL窗格中会显示自动生成的对应SQL语句。2、使用查询分析器进行查询（验证性）（1）查询Student表中所有学生的学号、姓名和性别。SELECT Sno, Sname, SsexFROM Student（2）可以用“*”来选取数据表的全部列例如：查询Student表中所有学生的基本情况。SELECT *FROM Student（3）在查询结果中增加计算列，还可修改数据

9、列的显示名称。例如：查询Student表中所有学生的学号、姓名、性别和出生年份。SELECT Sno 学号, Sname 姓名, Ssex 性别, Year(GetDate() - Sage 出生年份FROM Student（4）使用WHERE子句，可以选择满足条件的部分记录例如：查询成绩在8590分之间的学生情况。SELECT Sno, Cno, GradeFROM SCWHERE Grade >= 85 AND Grade <= 90（5）使用DISTINCT关键字，可以消除重复记录例如：查询有成绩的学生的学号。SELECT DISTINCT SnoFROM SC（6）使用IN

10、关键字，选择不连续条件的记录例如：查询学生成绩为80或85的学生的学号。SELECT *FROM SCWHERE Grade IN (80, 85)（7）使用谓词LIKE和通配符“%”或“_”，实现模糊查询例如：查询姓“张”的学生的基本情况。SELECT *FROM StudentWHERE Sname LIKE '张%'注意：“%”代表0个或多个字符，“_”代表一个字符。有的书上说，一个汉字占两个字符，但这里一个汉字只占一个字符位置，这与系统的设置有关。（8）使用ORDER BY子句，对查询结果进行排序例如：查询所有学生的2号课的成绩，并按成绩由高向低排序。SELECT *F

11、ROM SCWHERE Cno = 2ORDER BY Grade DESC（9）使用TOP关键字，选择查询结果的前几条记录例如：查询2号课成绩最高的学生记录。SELECT TOP 1 *FROM SCWHERE Cno = '2'ORDER BY Grade DESC注意：如果2号课成绩存在并列最高，则使用下面的SQL语句。SELECT TOP 1 WITH TIES *FROM SC WHERE Cno = '2'ORDER BY Grade DESC（10）用WHERE子句指定连接条件例如：查询所有有2号课程成绩的学生的学号、姓名和成绩。SELECT St

12、udent.Sno, Sname, GradeFROM Student, SCWHERE Student.Sno = SC.Sno AND Cno = '2' (11)将查询结果存储到表中例如：将总分在200分以上的学生的情况（学号、姓名、性别、总成绩）存储到数据表GradeList中，假设数据表GradeList不存在。SELECT A.Sno, Sname, Ssex, SUM(Grade) AS sumGradeINTO GradeListFROM Student A, SCWHERE A.Sno = SC.SnoGROUP BY A.Sno, Sname, SsexHA

13、VING (SUM(SC.Grade) >= 200)该语句执行成功后，在企业管理器中可以看到已经创建的数据表GradeList，并将查询结果是存储在该表中了。（12）将查询结果保存到变量中例如：查询学号为200215121的学生1号课的成绩，将其保存到变量Grade中。DECLARE Grade INT -定义变量SELECT Grade = Grade -给变量赋值FROM SCWHERE Sno = '200215121'PRINT Grade -显示变量的值注意：要将查询结果保存到变量中，只能将查询结果集中第一条记录的值赋给变量。3、分析设计部分在学生选课库中实现

14、下列数据查询操作，并写出相应的SQL脚本。(1)求计算机系学生的学号和姓名(2)求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列(3)求选修课程2成绩在80-90之间的学生学号和成绩,并将成绩乘以0.8输出(4)求数学或计算机系姓张的学生的信息(5)求缺少了成绩的学生的学号和课程号(6)查询各门课程及相应的选课人数。(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩(8)在FROM子句中用INNER JOIN连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩。 (9)查询学生中年龄相同的学生情况（使用自连接查询）。(10)查询所有学生的总成绩（

15、包括没有成绩的学生）、学号和姓名（外部连接查询）。(11)查询某课程成绩在90分以上的学生的学号和姓名（使用谓词IN连接子查询）。(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统实验五：用户权限管理实验一、实验目的掌握SQL Server中有关用户、角色及操作权限的设置方法。二、实验内容1. 用超级用户登录学生数据库student。2. 建立两个新用户:用户名stu1,密码stu1（登录名login1）；用户名stu2,密码stu2（登录名login2）和用户名s

16、tu3,密码stu3（登录名login3）。3. 授予stu1对SC具有select和insert权限,授予stu1对student具有select,update,insert和delete权限, 授予stu1对Course具有select,update,insert和delete权限。4授予stu2对SC具有select权限,授予stu2对student具有select权限, 授予stu2对Course具有select权限；授予stu2对Course具有更新属性Ccredit的权限。5测试授权以用户Stu1登录学生数据库：（1）查询SC、student和Course表中的所有数据；（2）分别

17、向SC、student和Course表中插入一组合法数据；（3）分别对SC、student和Course表更改一个元组；（4）分别对SC、student和Course表删除一个元组。观察运行结果并分析原因。以用户Stu2登录学生数据库：（1）分别向SC、student和Course表中插入一组合法数据；（2）更新Course表中课程号为1的课程的名称为人工智能；（3）更新Course表中课程号为1的课程的学分为3。观察运行结果并分析原因。以用户Stu1登录学生数据库：（1）收回stu1对SC表的select权限，对student表的update和insert权限，对Course表的delete

18、权限；（2）查询SC、student和Course表中的数据，观察运行结果；（3）分别向SC、student和Course表中插入一组合法数据，观察运行结果；（4）分别对SC、student和Course表更改一个元组，观察运行结果；（5）分别对SC、student和Course表删除一个元组，观察运行结果。思考Course是否具有delete权限执行效果有何不同？6创建数据库角色 Myrole，设置访问student表的select和insert权限，并添加用户成员stu3。以stu3登录查看验证。7使用SQL命令完成：删除登录名login1、login2和login3；删除用户名stu1、

19、stu2和stu3；删除角色Myrole。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统实验六：数据完整性实验一、实验目的：掌握使用SQL中的PRIMARY KEY、CHECK、FOREIGN KEYREFERENCES、NOT NULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。二、实验步骤：（一）验证分析部分1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性。在查询分析器窗体下键入如下命令：CREATE TABLE Student(sno CHAR(5) NOT NULL UNIQUE,

20、 sname CHAR(8), ssex CHAR(1),sage INT,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno); UPDATE Student SET sno= WHERE sdept=CS;UPDATE Student SET sno=200215121 WHERE sname=王敏;运行并观察结果。2、利用SQL查询分析器用FOREIGN KEYREFERENCES子句保证实体完整性。在查询分析器窗体下键入如下命令：(1)先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：alter TABLE SC a

21、dd CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON update CASCADE;然后执行以下语句Update Student SET sno=200215128 WHERE sno=200215121;(2) 先删除原来SC表中关于Sno 的外键，再将SC的Sno外键修改为：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON delete CASCADE;然后执行以下语句delete from student

22、WHERE sno=200215122;运行并观察结果。3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。CREATE TABLE Student(sno CHAR(5) , sname CHAR(8) CONSTRAINT U1 UNIQUE, ssex CHAR(1),sage INT CONSTRAINT U2 CHECK FOR sage<=28,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno)执行以下SQL语句Insert into values(2002101,li,女,89,

23、DS)运行并观察结果。思考：如果要求sdept输入内容只能为字母该如何约束？（二）设计分析部分 在Student数据库中，完成以下完整性约束设置。1、设置Student表中Ssex的取值只能是男或女；2、设置Student表中Sdept的取值默认为CS；3、删除操作测试（1）设置Course表和 SC表之间的关系，实现cascade delete related records。（2）删除Course 表中记录，查看SC表中数据的变化（SC表中Cno有与Course表中Cno对应的记录删除和无对应记录的删除都要测试），有什么结果，为什么？4. 修改操作测试（1）在SC表中，将课程号为3的改为3

24、3，都有什么结果？为什么？（2）将Course表和sc表的课程号为3的都改为33，请用完整性约束实现？5. 插入数据测试（1）在STUDENT表中插入一条新纪录： 95006 李三 男 21 IS（2）在SC表中插入一条新纪录： 95008 3 80 插入执行后都有什么结果？为什么？如何完成上述操作？三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统实验七：触发器设计实验一、实验目的：掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。二、实验内容：要求为Course表编写一个名为CourseTrigger插入触发器，

25、当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。Teacher(userid char(20), name char(20),department char(20), age)Course(courseid int, userid char(20), name char(20), grade int)说明：1对表Teacher和Course之间不允许建立外键约束；2本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。三、实验学时2学时四、实验设备与环境Windows 20

26、03平台 + SQL Server 2005系统课外补充：在Student、Course和SC表中创建以下触发器(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录 (不允许使用外键级联删除约束)。(2)创建一个触发器，不允许删除Course表中的任何数据。实验八、概念数据模型设计实验一、实验目的：熟悉PowerDesigner环境，掌握创建概念数据模型的方法。 二、实验内容：1设计出版社出版图书管理系统的概念数据模型，其设计CDM图结构如下图所示。完成下图所示的CDM图，并对图中的实体和标识符进行完善和补充。2将实验2中创建的CDM模型转换成SQL S

27、ERVER对应的逻辑模型。3实验报告中写出完善的E-R图和对应的逻辑模型图。三、实验学时2学时四、实验设备与环境Windows 2003平台 + PowerDesigner + SQL Server 2005系统实验九 存储过程设计一、实验目的 掌握使用SQL Server2005平台设计存储过程的方法。二、实验内容设计要求：1、使用STUDENT数据库中的学生表、课程表、选课表，创建一个带参数的存储过程userproc。该存储过程的作用是：当任意输入一个学生的姓名时，将从三个表中返回该学生的学号、选修的课程名称和课程成绩。2、将姓名为XXX的学生所有课程的成绩减少10分（其中学生姓名为存储过程的参数）。三、实验学时2学时四、实验设备与环境Windows 2003平台 + SQL Server 2005系统存储过程设计辅助实验一 实验目的 通过实验掌握SQL SERVER存储过程的基本设计方法。二、内容和方法1. 创建存储过程步骤1) 写SQL语句 例如：查询所有学生的记录 select * from student2) 测试SQL语句 执行这些SQL语句。确认符合要求。3) 若得到所需结果，则创建过程 如果发现符合要求，则按照存储