目录
(1)建立一个图书管理数据库,包括 3 个表:图书表、读者表、借阅表。
(3) 查询全体图书的信息,其中单价打 8 折,并且将该列设置别名为“打折价”。
(7) 查询清华大学出版社、科学出版社、人民邮电出版社的图书信息
(8) 查询既不是清华大学出版社、也不是科学出版社出版的图书信息。
(9) 查找姓名的第二个字符是“建”并且只有 3 个字符的读者的读者号、姓名。
(13) 查询单价在 20 元以上,30 元以下的清华大学出版社出版的图书名、单价.
(14) 查询清华大学出版社或科学出版社出版的图书名、出版社、单价。
(18)查询借阅图书本数超过 2 本的读者号、总本数,并按借阅本数值从大到小排序。
一.【实验目的】
(1)掌握单表查询的语法结构。
(2)掌握简单查询的使用方法。
(3)掌握带条件查询的使用方法。
(4)掌握查询结果处理的方法。
(5)掌握分组查询的使用方法。
二.【实验准备】
(1)已经接入局域网的网络实验室。
(2)机器上已安装 SQL Server。
(3)已了解单表查询的语法格式为:
SELECT <目标列名表)
FROM<数据源表》
[WHERE 〈查询条件)]
<查询条件>中可用的运算符有比较运算符和逻辑运算符。
三.【实验内容及步骤】
(1)建立一个图书管理数据库。图书管理数据库中包括 3 个表:图书表、读者表、借阅表。3个表的结构如表 6-1~表 6-2 所示。
表6-1 图书表结构
列名 | 说明 | 数据类型 | 约束 |
图书号 | 图书唯一的图书号 | 字符串,长度为20 | 主键 |
书名 | 图书的书名 | 字符串,长度为50 | 空值 |
作者 | 图书的编著者名 | 字符串,长度为30 | 空值 |
出版社 | 图书的出版社 | 字符串,长度为30 | 空值 |
单价 | 出版社确定的图书的单价 | 数值型 | 空值 |
表6-2 读者表结构
列名 | 说明 | 数据类型 | 约束 |
读者号 | 读者唯一的编号 | 字符串,长度为10 | 主键 |
姓名 | 读者姓名 | 字符串,长度为8 | 非空值 |
性别 | 读者性别 | 字符串,长度为2 | 非空值 |
办公电话 | 读者办公电话 | 字符串,长度为8 | 空值 |
部门 | 读者所在部门 | 字符串,长度为30 | 空值 |
表6-3 借阅表表结构
列名 | 说明 | 数据类型 | 约束 |
读者号 | 读者唯一的编号 | 字符串,长度为10 | 外键,引用读者表的主键 |
图书号 | 图书唯一的编号 | 字符串,长度为20 | 外键,引用读者表的主键 |
借出日期 | 图书借出的日期 | 日期型 | 非空值 |
归还日期 | 图书归还的日期 | 日期型 | 空值 |
插入表6-4~表6-6所示的记录
表6-4 图书表中的记录
图书号 | 书名 | 作者 | 出版社 | 单价 |
TN913.2/530 | 电信网技术 | 马同名 | 人民邮电出版社 | 7.5 |
TP311.13/CM3 | 数据库原理 | 苗晨曦 | 清华大学出版社 | 28 |
TP311.132/zZG1 | XML数据库设计与开发 | 尹相军 | 清华大学出版社 | 38 |
TP316/ZW6 | 操作系统原理 | 吴庆冰 | 科学出版社 | 35 |
TP316/ZY1 | 操作系统技术 | 沈言 | 电子工业出版社 | 31 |
TP391.132.3/ZG5 | 网络数据库技术 | 李睿智 | 清华大学出版社 | 45 |
表6-5 读者表中的记录
读者号 | 姓名 | 性别 | 电话 |
002073 | 王平 | 男 | 66573249 |
002074 | 张力 | 女 | 66573249 |
002075 | 王辉煌 | 男 | 66573249 |
002076 | 李少建 | 男 | 66573249 |
002077 | 程淡云 | 女 | 66573249 |
表6-6 借阅表中的记录
读者号 | 图书号 | 借出日期 | 归还日期 |
002073 | TP311.132/ZG1 | 2009-1-27 | 2009-3-28 |
002073 | TP316/ZW6 | 2009-1-27 | 2009-3-28 |
002075 | TP311.132/ZG1 | 2009-1-27 | 2009-3-28 |
002074 | TP311.13/CM3 | 2009-1-27 | 2009-3-28 |
002075 | TP315/ZW6 | 2009-1-27 | 2009-3-28 |
002074 | TP311.132/ZG1 | 2009-3-28 | 2009-6-28 |
(2) 查询全体图书的图书号、书名、作者、出版社、单价。
(3) 查询全体图书的信息,其中单价打 8 折,并且将该列设置别名为“打折价”。
(4) 显示所有借阅者的读者号,并去掉重复行。
(5) 查询所有单价在 20~30 之间的图书信息。
(6) 查询所有单价不在 20~30 之间的图书信息。
(7) 查询清华大学出版社、科学出版社、人民邮电出版社的图书信息。
(8) 查询既不是清华大学出版社、也不是科学出版社出版的图书信息。
(9) 查找姓名的第二个字符是“建”并且只有 3 个字符的读者的读者号、姓名。
(10) 查找姓名以“王”开头的所有读者的读者号、姓名。
(11) 查询无归还日期的借阅信息。
(12) 查询有归还日期的借阅信息。
(13) 查询单价在 20 元以上,30 元以下的清华大学出版社出版的图书名、单价.
(14) 查询清华大学出版社或科学出版社出版的图书名、出版社、单价。
(15) 求读者的总人数。
(16) 求借阅了图书的读者的总人数。
(17) 求清华大学出版社图书的平均价格、最高价、最低价。
(18) 查询借阅图书本数超过 2 本的读者号、总本数,并按借阅本数值从大到小排序。
四.【实验预习】
数据库的查询:SELECT <输出列> FROM 表名 WHERE 查询条件
表达式比较:
等于(=):比较两个值是否相等。
不等于(<>或者!=):比较两个值是否不相等。
大于(>):比较一个值是否大于另一个值。
小于(<):比较一个值是否小于另一个值。
大于等于(>=):比较一个值是否大于或等于另一个值。
小于等于(<=):比较一个值是否小于或等于另一个值。
等等。
子查询:IN子查询,比较子查询, EXISTS子查询
连接:=/JOIN
指定查询结果分组方法:GROUP BY
指定查询结果分组后筛选条件:HAVING
指定查询结果排序顺序:ORDER BY
其他:INTO/UNION/EXCEPT/CTE
五.【实验结果】
(1)建立一个图书管理数据库,包括 3 个表:图书表、读者表、借阅表。
CREATE TABLE 图书表
(
图书号 char(20) NOT NULL PRIMARY KEY,
书名 char(50) NULL,
作者 char(30) NULL ,
出版社 char(30) NULL,
单价 int NULL
)
CREATE TABLE 读者表
(
读者号 char(10) NOT NULL PRIMARY KEY,
姓名 char(8) NOT NULL,
性别 char(2) NOT NULL ,
办公电话 char(8) NULL,
部门 char(30) NULL
)
CREATE TABLE 借阅表
(
读者号 char(10) NOT NULL ,
图书号 char(20) NOT NULL ,
借出日期 datetime NOT NULL ,
归还日期 datetime NULL,
FOREIGN KEY(读者号) REFERENCES 读者表(读者号),
FOREIGN KEY(图书号) REFERENCES 图书表(图书号)
插入数据
INSERT INTO 图书表
VALUES('TN913.2/530','电信网技术','马同名','人民邮电出版社','7'),
('TP311.13/CM3','数据库原理','苗晨曦','清华大学出版社','28'),
('TP311.132/zZG1','XML数据库设计与开发','尹相军','清华大学出版社','38'),
('TP316/ZW6','操作系统原理','吴庆冰','科学出版社','35'),
('TP316/ZY1','操作系统技术','沈言','电子工业出版社','31'),
('TP391.132.3/ZG5','网络数据库技术','李睿智','清华大学出版社','45')
INSERT INTO 读者表
VALUES('002073','王平','男','66573249',''),
('002074','张力','女','66573249',''),
('002075','王辉煌','男','66573249',''),
('002076','李少建','男','66573249',''),
('002077','程淡云','女','66573249','')
INSERT INTO 借阅表
VALUES('002073','TP311.132/ZG1','2009-1-27','2009-3-28'),
('002073','TP316/ZW6','2009-1-27','2009-3-28'),
('002075','TP311.132/ZG1','2009-1-27','2009-3-28'),
('002074','TP311.13/CM3','2009-1-27','2009-3-28'),
('002075','TP316/ZW6','2009-1-27','2009-3-28'),
('002074','TP311.132/ZG1','2009-3-28','2009-6-28')
(2) 查询全体图书的图书号、书名、作者、出版社、单价。
SELECT *
FROM 图书表
GO
(3) 查询全体图书的信息,其中单价打 8 折,并且将该列设置别名为“打折价”。
SELECT 打折价=单价*0.8
FROM 图书表
GO
(4) 显示所有借阅者的读者号,并去掉重复行。
SELECT DISTINCT 读者号 FROM 借阅表
(5) 查询所有单价在 20~30 之间的图书信息。
SELECT *
FROM 图书表
WHERE 单价 BETWEEN 20 AND 30
GO
(6) 查询所有单价不在 20~30 之间的图书信息。
SELECT *
FROM 图书表
WHERE 单价 NOT BETWEEN 20 AND 30
GO
(7) 查询清华大学出版社、科学出版社、人民邮电出版社的图书信息
SELECT *
FROM 图书表
WHERE 出版社='清华大学出版社'OR 出版社='人民邮电出版社'OR 出版社='科学出版社'
GO
(8) 查询既不是清华大学出版社、也不是科学出版社出版的图书信息。
SELECT *
FROM 图书表
WHERE 出版社<>'清华大学出版社'AND 出版社<>'人民邮电出版社'AND 出版社<>'科学出版社'
GO
(9) 查找姓名的第二个字符是“建”并且只有 3 个字符的读者的读者号、姓名。
SELECT 姓名,读者号
FROM 读者表
WHERE 姓名 LIKE '__建'
GO
(10) 查找姓名以“王”开头的所有读者的读者号、姓名。
SELECT 姓名,读者号
FROM 读者表
WHERE 姓名 LIKE '王%'
GO
(11) 查询无归还日期的借阅信息。
SELECT *
FROM 借阅表
WHERE 归还日期 IS NULL
GO
(12) 查询有归还日期的借阅信息。
SELECT *
FROM 借阅表
WHERE 归还日期 IS NOT NULL
GO
(13) 查询单价在 20 元以上,30 元以下的清华大学出版社出版的图书名、单价.
SELECT 书名,单价
FROM 图书表
WHERE 出版社='清华大学出版社'AND 单价 BETWEEN 20 AND 30
GO
(14) 查询清华大学出版社或科学出版社出版的图书名、出版社、单价。
SELECT 书名,出版社,单价
FROM 图书表
WHERE 出版社='清华大学出版社'OR 出版社='科学出版社'
GO
(15) 求读者的总人数。
SELECT COUNT(读者号) AS '读者总人数'
FROM 读者表
GO
(16) 求借阅了图书的读者的总人数。
SELECT COUNT(DISTINCT 读者号) AS '读者总人数'
FROM 借阅表
GO
(17)求清华大学出版社图书的平均价格、最高价、最低价。
SELECT AVG(单价) AS '平均价格',MAX(单价) AS '最高价',MIN(单价) AS '最低价'
FROM 图书表
WHERE 出版社='清华大学出版社'
GO
(18)查询借阅图书本数超过 2 本的读者号、总本数,并按借阅本数值从大到小排序。
SELECT 读者号,COUNT(DISTINCT 图书号) AS '总本数'
FROM 借阅表
GROUP BY ROLLUP(读者号)
HAVING COUNT(DISTINCT 图书号)>2
ORDER BY COUNT(DISTINCT 图书号) DESC
GO
六.【实验问题】
设立两个外键后,可能无法插入数据,右键将约束取消即可。