《数据库原理及应用》实验指导书
1
目 录
实验1：数据库的定义实验„„„„„„„„„„„„„„„„„„„„ 3 实验2：数据库的查询实验„„„„„„„„„„„„„„„„„„„„10 实验3：数据库的统计查询、视图实验„„„„„„„„„„„„„„15
2
实验1：数据库的定义实验
实验目的:
要求学生熟练掌握和使用SQL、T-SQL、SQL Server 企业管理器（Enterpriser Manager Server ）创建数据库、表、索引和修改表结构，并学会使用查询分析器（SQL Server Query Analyzer）,接收T－SQL语句和进行结果分析。 实验内容：
ｌ）创建数据库和查看数据库属性。
２）创建表、确定表的主码和约束条件，为主码建索引。 ３）查看和修改表结构。
４）熟悉SQL Server 企业管理器（Enterpriser Manager）和 查询分析器（Query Analyzer）工具的使用方法 具体实验任务如下。 １．基本操作实验
1)启动SQL Server200下的企业管理器（Enterpriser Manager）如图1.1所示，点击“数据库”右键，利用“新建数据库”建立图书读者数据库如图1.2所示；
图1.1 企业管理器界面
3
图1.2 新建“图书_读者”界面
2)在企业管理器（Enterpriser Manager）中查看图书读者数据库的属性，并进行修改，使之符合要求。
3）通过企业管理器（Enterpriser Manager），在建好了图书数据库中建立图书、读者和借阅３个表，方法是点击“图书_读者”右键“新建”下的“表”,如图1.3所示：
图1.3 新建表
4
其结构为：
图书（书号，类别，出版社，作者，书名，定价）； 读者（编号，姓名，单位，性别，电话）： 借阅（书号，读者编号，借阅日期）
要求为属性选择合适的数据类型，定义每个表的主键，Allow Null（是否允许空值）和Default Value（缺省值）等列级数据约柬。
４）在Enterpriser Manager建立图书、读者和借阅３个表的表级约束：每个表的PrimaryKey（主键）约束；借阅表与图书表间、借阅表与读者表之间的ＦＯＲＥＩＧＮＫＥＹ（外码）约束，要求按语义先确定外码约束表达式，再通过操作予以实现；实现借阅表的书号和读者编号的UNIQUE（惟一性）约束；实现读者性别只能是“男”或“女”的check（检查）约束，如图1.4、1.5、1.6、1.7所示。
图1.4 图书表
5
图1.5 读者表
图1.6 check界面
6
图1.7 借阅表
这样，各表就定义好了，接下来要求同学们自己利用“管理关系”将各表的级联关系画出来，注意分清楚主键表与外键表，关系应在主键表上建立。 ２．提高操作实验
建立学生_课程库操作，在SQL Server 企业管理器（Enterpriser Manager）中实现。库中表结构为：
学生（学号，姓名，年龄，性别，所在系）： 课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）。 要求：
ｌ）建库、建表、建立表间联系。 ２）选择合适的数据类型。
３）定义必要的索引、列级约束和表级约束。 ３选择操作实验
（1）将自设计的数据库应用系统中的数据库、库中的表、索引和约束用T-SQL表达，并通过企业管理器或查询分析器实现建库、建表、建立表间联系和建立必要的索引、列级约束和表级约束的操作。 (2)内容：
7
1）分别利用企业管理控制台创建方式、向导创建方式及命令行方式将下面各表建立到教学管理数据库中。 注意：表创建存在先后顺序，实际操作中注意体会。 (1)学生表(student)，采用企业管理控制台创建方式。 字段名 代码 类型 约束 学号 s_no char(6) 主键 姓名 s_name char(10) 非空 性别 s_sex char(2) 只取男、女 出生日期 s_birthday date 入学成绩 s_score number(5,1) 附加分 s_addf number(3,1) 班级编码 class_no char(5) 与班级表中class_no外键关联 (2)班级表(class)，采用企业管理控制台向导创建方式。 字段名 代码 类型 约束 班级编码 class_no char(5) 主键 班级名 class_name char(10) 非空 所属系部 dept_no char(2) 与系部表中dept_no外键关联 (3)系部表(department)，采用命令行方式。 字段名 代码 类型 约束 系部编码 dept_no char(2) 主键 系部名 dept_name char(10) 非空 (4)课程表(course) ，采用命令行方式。 字段名 代码 类型 约束 课程编码 course_no char(5) 主键 课程名 course_name char(20) 非空 (5)教师表(teacher) ，采用企业管理控制台向导创建方式。 字段名 代码 类型 约束 教师编码 t_no char(6) 主键 8
教师姓名 t_name 性别 t_sex char(8) 非空 char(2) 只取男、女 出生日期 t_birthday date 职称 tech_title char(10) (6)选修表(choice) ，采用命令行方式。 字段名 代码 学生编码 s_no 类型 char(6) 约束 主键, 与学生表中s_no外键关联,级联删除 主键, 与课程表中course_no外键关联 课程编码 course_no char(5) 成绩 score number(5,1) (7)授课表(teaching) ，采用企业管理控制台创建方式。 字段名 代码 教师编码 t_no 类型 约束 char(6) 主键, 与教师表中t_no外键关联,级联删除 课程编码 course_no char(5) 主键, 与课程表中course_no外键关联 2）分别利用企业管理控制台和命令行方式查看这些基本表的各种信息。 3） 分别利用企业管理控制台和命令行方式为某基本表创建索引，查看其信息，然后删除它。 4）分别在每个表中采用sql语句插入相应的数据。 实验报告要求： 1．写出实验目的、实验内容、实验结果和主要实验步骤。 2．列出实验前准备的实验数据表格，指出图书_读者数据库和学生_课程库中各表的属性定义和数据约束。 3．列出实验中出现的问题和解决方法。 4．写出实验体会。9
实验2：数据库的查询实验
实验目的：
使学生掌握SQL Server查询分析器的使用方法，加深对SQL和T-SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的SQL表达和查询分析操作方法。 实验内容：
１）简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。
２）连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。 实验方法：
将查询需求用T-SQL语言表示；在SQL Server 查询分析器的输入区中输入T-SQL查询语句；设置查询分析器的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。 实验要求： １．基本操作实验 （１）简单查询实验
在学生选课库中实现其数据查询操作。 求数学系学生的学号和姓名：
T-SQL语句：select 学号,姓名 from 学生 where 所在系=’数学’
求选修了课程的学生学号：
T-SQL语句：select distinct 学号 from 选课
求选修C1课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列：
10
T-SQL语句：select 学号,成绩 from 选课 where 课程号=’C1’order by 成绩desc,学号asc
求选修课程C1成绩在80~90之间的学生学号和成绩,并将成绩乘以0.8输出： T-SQL语句：select 学号,成绩*0.8 from 选课 where 课程号=’C1’and 成绩 between 80 and 90
求数学或计算机系姓张的学生的信息:
T-SQL 语句：select * from 学生 where 所在系 in(‘数学’,’计算机’)and 姓名 like ‘张%’
求缺少了成绩的学生的学号和课程号
T-SQL 语句：select 学号,课程号 from 选课 where 成绩 is NULL
（２）连接查询实验
在学生选课库中实现其数据连接查询操作。 查询每个学生的情况以及他(她)所选修的课程：
T-SQL 语句：select 学生.*,选课.* from 学生,选课 where 学生.学号=选课.学号
求学生的学号、姓名、选修的课程及成绩：
T-SQL 语句：select学生.学号,姓名,课程名,成绩 from 学生,课程,选课 where学生.学号=选课.学号 and 课程.课程号=选课.课程号
求选修课程C1且成绩在90分以上的学生学号、姓名及成绩：
T-SQL语句：select 学生.学号,姓名,成绩 from 学生,选课 where 学生.学号=选课.学号 and 课程号=’C1’and 成绩>90
查询每一门课的间接先行课（即先行课的先行课）:
11
T-SQL语句：select A.课程号,A.课程名,B.先行课 from 课程 A，课程 B where A.先行课=B.课程号
（3）嵌套查询实验：
①求选修了高等数学的学生学号和姓名。
T-SQL语句：select 学号,姓名 from 学生 where 学号 in (select 学号 from 选课 where 课程号 in(select 课程号 from 课程 where 课程名=’高等数学’))
②求C1课程的成绩高于张三的学生学号和成绩。
T-SQL语句：select 学号,成绩 from 选课 where 课程号='C1' and 成绩>(select 成绩 from 选课 where 课程号='C1' and 学号=(select 学号 from 学生 where 姓名='张三')) ;
③求其他系中比计算机系某一学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）
T-SQL语句：select * from 学生 where 年龄 T-SQL语句：select * from 学生 where 年龄 T-SQL语句：Select 姓名 from 学生 where exists(select * from 选课 where 学生.学号=学号 and 课程 =’C2’) ⑥求职没有选修C2课程的学生姓名。 T-SQL语句：Select 姓名from 学生 where not exists(select * from 选课 where 学生.学号=学号 and 课程号=‘C2’) ⑦查询送修了全部课程的学生姓名。 12 T-SQL语句：Select 姓名 from 学生 where not exists(select * from 课程 where not exists(select * from 选课 where 学生.学号=学号 and 课程.课程号=课程号)) ⑧求职至少选修了学生为“S2”的学生所选修的全部课程的学生学号的姓名。 T-SQL语句： Select 学号,姓名 from 学生 where not exists(select * from 选课 选课1 where 选课1.学号=’S2’and not exists(select * from 选课 选课2 where 学生.学号=选课2.学号 and 选课2.课程号=选课1.课程号)) ２．提高操作实验 ｌ）建立职工部门库和职工、部门表，并向表中输入数据 职工表 职工号 姓名 性别 年龄 所在部门 1010 李勇 男 20 11 1011 刘晨 女 19 14 1012 王敏 女 22 12 1014 张立 男 21 13 部门表 部门号 部门名称 电话 11 12 13 14 生产科 566 计划科 578 一车间 467 科研所 2 ）用T-SQL语句在职工部门库中实现其数据内连接和各种外查询操作。 ３．选择操作实验 (1) 设职工—社团库有三个基本 建立职工—社团数据库和基本表，向库中输入一定的记录。 职工（职工号，姓名，负责人，活动地点） 社会团体（编号，名称，负责人，活动地点） 参加（职工号，编号，参加日期） 按以下要求用T-SQL语句表示。 13 1定义职工表、社会团体表和参加表，并说明其主码和参照关系。 2参加唱歌队或篮球队的职工事情和姓名。 3查找没有参加任何社会团的职工情况。 4查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。 ３）通过SQL Server 查询分析器实现查询操作。 （２）设工程—零件库有四个表 ｌ）建立工程—零件数据库和基本表，向库中输入一定的记录。 供应商（供应商代码，姓名，所在城市，联系电话）； 工程（工程代码，工程名，负责人，预算）； 零件（零件代码，零件名，规格，产地，颜色）； 供应零件（供应商代码，工程代码，零件代码，数量）。 2）以下要求用Ｔ-SQL语句表示。 （1）找出天津市供应商的姓名和电话。 （2）查找使用预算在50000—100000元之间的工程的信息，并将结果按预算降序排列。 （3）找出使用供应商S1所供零件名称及其数量。 （4）找出工程项目J2使用的各种零件名称及其数量。 ３）通过SQL Server 查询分析器实现查询操作。 实验报告要求： 1）写出实验目的、实验内容、实验结果和主要实验步骤。 2）写出实验操作的T-SQL查询语句、实验结果。 3）写出实验中出现的问题、注意事项和解决方法。 4）写出实验体会。 注意事项： 1）查询分析器设置。 2）T-SQL语句结束时不加“；”. 3）内连接、左外部连接和右外部连接的含义及表达方法。 4）输入SQL语句时应注意，语句中使用西文操作符号，包括引号、等号、逗号等。 14 思考题： 1）如何提高数据查询和连接速度？ 2）如何存储SQL代码和查询结果？ 15 实验3：数据库的统计查询、视图实验 实验目的： 使学生掌握SQL Server中的视图创建向导和图表创建向导的使用方法，加深对视图和SQL Server图表作用的理解。 实验内容： ｌ）创建、查看、修改和删除视图。 ２）创建、编辑和删除数据库图表。 用视图创建向导创建视图，用工具和向导创建数据库图表 １．基本操作实验 1)查找这样的图书类别:要求类别最高的图书定价不低于全部按类别分组的图书平均定价的0.8倍. T-SQL语句：Select A.* from 图书A group by A.类别 having max(A.定价)>=all(select 0.8*avg(B.定价) from 图书B group by A.类别) 2)求机械工业出版社出版的各类图书的平均定价,用Group By表示. T-SQL语句：Select 类别,avg(定价) 平均价 from 图书 where 出版社=’机械出版社’group by 类别 oder by 类别 asc 3)列出计算机类图书的书号,名称及价格,最后求出册数和总价格. T-SQL语句：Select 书号,书名,定价 from 图书 where 类别=’计算机’oder by 书号asc compute count(*),sum(定价) 4)列出计算机类图书的书号,名称及价格,并求出各出版社这类书的总价格,最后求出全部册数和总价格. T-SQL语句：Select 书号,书名,定价 from 图书 where 类别=’计算机类’order by 出版社 compute count(*),sum(定价) by 出版社 compute count (*),sum(定价) 16 5)查询计算机类和机械工业出版社出版的图书. T-SQL语句：Select * from 图书 where 类别=’计算机类’union all select * from 图书 where 出版社=’机械工业出版式’ 6）在SQL Server 企业管理器中利用视图向导来创建视图，打开企业管理器，选中图书数据库，点击“工具”下面的“向导”，创建借阅．计算机图书视图；如图6.1所示，点击“创建视图向导”就可以一步步创建所要的视图了，注意创建的视图不要有重复列，最好考虑清楚所创建的视图要有一定的意义。 图3.1视图向导 也可以在查询分析器中写SQL 命令来创建： Create View借阅_计算机图书 As select 图书.*，借阅.读者编号，借阅.借阅日期 From 图书，借阅 Where 图书编号=借阅.书号 AND 图书.类别=’计算机’ ２．提高操作实验 求学生的总人数. 求选修课程的学生人数. 17 求课程和选修该课程的人数. 求选修课超过3门的学生学号. 转换为T-SQL语句，在学生选课库中实现其数据查询操作。 在SQL Server 企业管理器中，用视图创建向导创建这３个视图。 建立计算机系学生的视图 由学生、课程和选修课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩 ③将学生的学号、总成绩、平均成绩定义成一个视图 ３．选择操作实验 将自设计的数据库应用项目中的子模式，用T-SQL描述其视图定义，并在SQL，SQL Server 企业管理器中，用视图创建向导创建这些视图。 实验报告要求： 1．写出实验目的、实验内容、实验结果和主要实验步骤。 2．列出实验前准备的实验数据：相关的T-SQL语句；视图结构信息；关系图信息。 3．列出实验中出现的问题、注意事项和解决方法。 4．写出实验体会。 注意事项： 1．参照表和主表之间的关系。主码和外码间的关系。 2．视图中字段名的重命名问题。 思考题： 1．为什么要建立视图？视图和基本表有什么不同？ 2．视图和关系图有什么不同？ 18 因篇幅问题不能全部显示，请点此查看更多更全内容