实验2SQL语句

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_course）——课程编号、课程名、先修课、学分

2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下：

图书类别信息表（tb_booktype)——类别编号、类别名称

图书信息表（tb_book）——图书编号、类别编号、书名、作者、出版社、定价、库存数

读者信息表（tb_reader）——读者编号、姓名、性别、学号、班级、系部

借阅信息表（tb_borrow)——图书编号、读者编号、借阅日期、归还日期

3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。

4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL。

5、通过SQL语句删除课程信息表。 （三）创建和删除索引

1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY，要求按照该表中库存数字段的降序创建。

2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME，要求按照该表中的姓名字段的升序创建。

3、使用SQL语句删除之前创建的两个索引。

（四）数据库及数据表设计 根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。

（五）数据查询

通过SSMS向各数据表中添加以下记录。 （1）图书类别信息表 （2）图书信息表 (3）读者信息表

（4）借阅信息表

对以上数据表，完成以下操作：

（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”视图。

三、 实验步骤

（一）创建数据库和模式 create database db_Library on primary (

name=db_Library_data,

filename='D:\\DATA\\db_Library_data.mdf', size=10mb, maxsize=300, filegrowth=10% )

log on

(

name=db_Library_log,

filename='D:\\DATA\\db_Library_log.ldf', size=5mb,

maxsize=200mb, filegrowth=2mb )

use db_Library go

create schema L_C （二）创建和管理数据表 create table L_c.tb_course (

课程编号char(10), 课程名char(10), 先修课char(10), 学分numeric(4,2) )

create table tb_booktype (

类别编号char(2), 类别名称char(8) )

create table tb_book (

图书编号char(5) , 类别编号char(1), 书名char(15), 作者char(6), 出版社char(15), 定价numeric(6,2), 库存int

)

create table tb_reader (

读者编号char(6), 姓名char(6), 性别char(2), 学号char(7), 班级char(5), 系部char(8) )

create table tb_borrow (

图书编号char(5), 读者编号char(6), 借阅日期datetime, 归还日期datetime )

alter table tb_reader drop column 系部

alter table tb_reader add 系部char(10)

alter table tb_book

alter column 定价numeric(6,2);

drop table L_c.tb_course （三）创建和删除索引

create index IX_S_QUANTITY on tb_book(库存desc);

create unique index IX_S_NAME on tb_reader(姓名asc); drop

index

IX_S_QUANTITY

on

tb_book ,IX_S_NAME on tb_reader; （五）数据查询 (1)

select *

from tb_book;

(2)

select 读者编号,姓名,班级 from tb_reader;

(3)

select DATEDIFF(day,借阅日期,归还日期) as diffdate

from tb_borrow; (4)

select 图书编号 from tb_borrow group by 图书编号;

(5)

select 书名,作者 from tb_book

where 图书编号=10006;

(6)

select 图书编号,书名 from tb_book

where 库存between 5 and 10;

(7)

select *

from tb_reader

where 系部 in ('计算机系','电子系') and 姓名like '张%';

(8) select *

from tb_book

where 书名like '%英语%';

(9)

select 性别,count (性别) as 总数 from tb_reader group by 性别

(10)

select 类别编号,avg (定价) as 平均定价, sum (库存) as 库存总数 from tb_book group by 类别编号;

(11)

select tb_borrow.图书编号,count (*) as 所借人数 from tb_reader,tb_borrow,tb_book

where tb_borrow.图书编号=tb_book.图书编号 and tb_borrow.读者编号=tb_reader.读者编号 group by tb_borrow.图书编号 order by 所借人数 DESC