实验 SQL语言
一、实验目的
1、理解数据库以及数据表的设计；
2、熟悉SQL Server2005中的数据类型；
3、熟悉使用SQL语句创建和删除模式和索引；
4、掌握使用SQL语句创建、修改和删除数据表；
5、掌握使用SQL语句查询表中的数据；
6、掌握使用SQL语句插入、修改和删除数据表中的数据；
7、掌握使用SQL语句创建、删除、查询和更新视图。
二、实验内容
（一）创建数据库和模式
1、通过SQL语句创建图书信息管理数据库，命名为“db_Library”，数据文件和日志文件放在D盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为db_Library_data，数据文件的操作系统名为db_Library_data.mdf，文件初始大小为10MB，最大可增加至300MB，增幅为10%；日志文件的逻辑名为db_Library_log，日志文件的操作系统名为db_Library_data.ldf，文件初始大小为5MB，最大可增加至200MB，增幅为2MB。
2、通过SQL语句在该数据库中创建模式L-C。
（二）创建和管理数据表
要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。
1、通过SQL语句将以下数据表创建在L-C模式下：
课程信息表（tb_cour）——课程编号Cour number 、课程名Cour name 、先修课The first cour 、学分credit
2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：
图书类别信息表（tb_booktype)——类别编号Type number 、类别名称Category name
图书信息表（tb_book）——图书编号ISBN 、类别编号Type number、书名title 、作者author、出版社BookPublic、定价BookPrice、库存数Inventory number
读者信息表（tb_reader）——读者编号Reader ID 、姓名、性别、学号Student ID 、班级、系部pastern
借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期Borrowing date 、归还日期Return date
3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。
4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。
5、通过SQL语句删除课程信息表。
（三）创建和删除索引
1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。
2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。
3、使用SQL语句删除之前创建的两个索引。
（四）数据库及数据表设计
根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。
（五）数据查询
通过SSMS向各数据表中添加以下记录。
（1）图书类别信息表
类别编号 | 类别名称 | 类别编号 | 类别名称 | 类别编号 | 类别名称 |
1 | 数学 | 4 | 文学 | 不安抗辩权7 | 建筑 |
2 | 英语 | 5 | 艺术 | 8 | 化学 |
3 | 计算机 | 6 | 电子信息 | 9 | 物理 |
| | | | | |
（2）图书信息表
图书编号 | 类别编号 | 书名 | 作者 | 出版社 | 定价 | 库存数 |
10001 | 3 | 数据库管理 | 王珊 | 高等教育出版社 | 35.50 | 油画滤镜 10 |
10002 | 3 | 软件测试 | 贺平 | 机械工业出版社 | 24.60 | 5 |
10003 | 3 | C++程序设计 | 谭浩强 | 清华大学出版社 | 30.00 | 8 |
10004 | 4 | 红楼梦 | 曹雪芹 | 人民文学出版社 | 70.00 | 5 |
10005 | 4 | 西游记 | 罗贯中 | 人民文学出版社 | 60.00 | 8 |
10006 | 4 | 红与黑 | 司汤达 | 人民文学出版社 | 50.00 | 5 |
10007 | 1 | 高等数学 | 李翼 | 清华大学出版社 | 28.00 | 4 |
10008 | 8 | 有机化学 | 张翔 | 高等教育出版社 | 29.00 | 5 |
10009 | 2 | 大学英语 | 王琳 | 高等教育出版社 | 25.00 | 10 |
10010 | 2 | 英语教程 | 王琳 | 高等教育出版社 | 25.00 | 5 |
| | | | | | |
（3）读者信息表
向雷锋学习读者编号 | 姓名 | 小米查真伪性别 | 学号 | 班级 | 所在系 |
R10001 硬盘温度多少正常 | 张小航 | 男 | 0851101 | 08511 | 计算机系 |
R10002 | 王文广 | 女 | 0851102 | 08511 | 计算机系 |
R10003 | 李理 | 女 | 0851103 | 08511 | 计算机系 |
R10004 | 李彦宏 | 男 | 0851201 | 08512 | 计算机系 |
R10005 | 张丽霞 | 女 | 0851202 | 08512 | 计算机系 |
R10006 | 王强 | 男 | 0721104 | 07211 | 电子系 |
R10007 | 张宝田 | 男 | 0721204 | 07212 | 电子系 |
R10008 | 宋文霞 | 女 | 0761104 | 07611 | 建工系 |
R10009 | 刘芳菲 | 女 | 0881104 | 08811 | 外语系野餐简笔画 |
R10010 | 常江宁 | 男 | 0881204 | 08812 | 外语系 |
| | | | | |
（4）借阅信息表
图书编号 | 读者编号 | 借阅日期 | 愿者上钩归还日期 |
10002 | R10003 | 2009-9-20 | 2009-10-20 |
10003 | R10003 | 2009-9-20 | 2009-10-20 |
10004 | R10003 | 2009-9-30 | 2009-10-30 |
10009 | R10003 | 2009-9-30 | 2009-10-30 |
10009 | R10007 | 2009-5-20 | 2009-6-20 |
10010 | R10007 | 2009-5-20 | 2009-6-20 |
10009 | R10009 | 2009-5-30 | 2009-6-30 |
10010 | R10009 | 2009-5-22 | 2009-6-22 |
10002 | R10009 | 2009-5-22 | 2009-6-22 |
10003 | R10009 | 2009-5-30 | 2009-6-30 |
| | | |
对以上数据表，完成以下操作：
（1）查询每本图书的所有信息；
（2）查询每个读者的读者编号、姓名和班级；
（3）查询每条借阅记录的借阅天数（函数DATEDIFF获取两个日期的差）；
（4）查询被借阅过的图书的图书编号；
（5）查询图书编号为“10006”的书名和作者；
（6）查询库存数在5到10本之间的图书的图书编号和书名；
（7）查询计算机系或电子系姓张的读者信息；
（8）查询书名包括“英语”的图书信息；
（9）统计男读者、女读者的人数；
（10）统计各类图书的类别编号、平均定价以及库存总数；
（11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；
（12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；
（13）查询借阅了“大学英语”一书的读者，输出读者姓名、性别、系部；
（14）查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；（LEFT OUTER JOIN）
（15）查询现有图书中价格最高的图书，输出书名、作者、定价；
（16）查询借阅了“大学英语”但没有借阅“C++程序设计”的读者，输出读者姓名、性别、系部；
（17）统计借阅了2本以上图书的读者信息；
（18）查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
（19）查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息；（用集合查询完成）
（20）查询计算机系中比其他系所有读者借书数量都多的读者的信息；
（21）在读者信息表中插入一条新的记录（读者编号：R10011；姓名：张三；所在系：电子系）；
（22）定义一个表tb_booknew，包含图书编号、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；
（23）将类别编号为“3”的所有图书的库存数增加5；
（24）将“C++程序设计”这本书的归还日期增加一个月（函数DATEADD）。
（25）删除姓名为“张三”的读者的信息；
（26）删除tb_bknew表中的所有数据；
（27）创建一个名为“读者借阅信息_VIEW”的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算机系的读者借阅信息；
（28）创建一个名为“图示借阅信息_VIEW”的视图，要求显示图书的借阅情况，包括图书编号、书名、库存数、借阅次数字段；
（29）查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数；
（30）删除“图示借阅信息_VIEW”视图。
三、实验步骤
u [master]
go
create databa db_Library
on primary
(
name=N'db_Library_data',
filename='E:\SQL\db_Library_data.mdf',
size=10MB,
maxsize=300MB,无领导小组面试
filegrowth=10%
)
log on
(
name='db_Library_log',
filename='E:\SQL\db_Library_data.ldf',
size=5MB,
maxsize=200MB,
filegrowth=2MB
)
go
u db_Library
go
create schema "L-C" authorization guest
create table tb_cour --课程信息表
( Cournumber int ,
Courname char(20),
Thefirstcour char(20),
Credit int)
go
create table tb_booktype --图书类别信息表
( Typenumber int,
Categoryname char(20)
)
go
create table tb_book --图书信息表
( ISBN int,
Typenumber int,
Title char(20),
Author char(20),
BookPublic char(20),
BookPrice float,
Inventorynumber int)
go
create table tb_reader --读者信息表
(
ReaderID char(20),
Name char(20),
Sex char(2),
StudentID int ,
Class char(20),
Pastern char(20)
)
go
create table tb_borrow --借阅信息表
