网络数据库 习题参考答案
1.在对数据进行什么操作时,DBMS 会检查缺省值约束?在对数据进行什么操作时,DBMS 会检
查 CHECK 约束?
答:对数据进行插入,DBMS 会检查缺省值约束。
对数据进行插入、修改,DBMS 会检查 CHECK 约束。
2.UNIQUE 约束与 PRIMARYKEY 约束的区别?
答:(1) UNIQUE 约束允许有一个 NULL;而 PRIMARYKEY 约束不允许有一个 NULL;
(2) 一个表的 UNIQUE 约束允许有多个,它定义表的候选键;而 PRIMARYKEY 约束只能有
一个,它定义表的主键。
3.设有一图书馆数据库,包括三个表:图书表、读者表、借阅表。三个表的结构如表 3-8、
表 3-9、3-10,数据如第一章图 1-3 所示。完成以下习题。
表 3- 1 图书表结构
列名
说明
数据类型
约束
图书号 图书唯一的图书号
定长字符串,长度为 20 主键
书名
作者
图书的书名
变长字符串,长度为 50 空值
图书的编著者名
变长字符串,长度为 30 空值
出版社 图书的出版社
变长字符串,长度为 30 空值
单价
出版社确定的图书的单价 浮点型,FLOAT
空值
表 3- 2 读者表结构
列名
说明
数据类型
约束说明
读者号
读者唯一编号 定长字符串,长度为 10
主键
姓名
性别
读者姓名
读者性别
定长字符串,长度为 8
定长字符串,长度为 2
办公电话 读者办公电话 定长字符串,长度为 8
部门
读者所在部门 变长字符串,长度为 30
非空值
非空值
空值
空值
表 3- 3 借阅表结构
列名
说明
数据类型
约束说明
读者号
读者的唯一编号 定长字符串,长度为 10 外码,引用读者表的主键
图书号
图书的唯一编号 定长字符串,长度为 20 外码,引用图书表的主键
借出日期 图书借出的日期 定长字符串,长度为 8,
非空值
归还日期 图书归还的日期 定长字符串,长度为 8,
空值
为’yymmdd’
为’yymmdd’
主键为:(读者号, 图书号)
(1)用 SQL 语句创建图书馆数据库。
答: CREATE DATABASE 图书馆
(2)用 SQL 语句创建上述三个表。
CREATE TABLE 图书 (
图书号 CHAR(20)
NOT NULL ,
书名 VARCHAR(50)
NULL ,
作者 VARCHAR(30)
NULL ,
出版社 VARCHAR(30)
NULL ,
单价 FLOAT
NULL
)
ALTER TABLE 图书
ADD CONSTRAINT PK1
PRIMARY KEY(图书号)
等价于:
CREATE TABLE 图书 (
图书号 CHAR(20)
PRIMARY KEY ,
书名 VARCHAR(50)
NULL ,
作者 VARCHAR(30)
NULL ,
出版社 VARCHAR(30)
NULL ,
单价 FLOAT
NULL
)
等价于:
CREATE TABLE 图书 (
图书号 CHAR(20)
NOT NULL ,
书名 VARCHAR(50)
NULL ,
作者 VARCHAR(30)
NULL ,
出版社 VARCHAR(30)
NULL ,
单价 FLOAT
NULL,
PRIMARY KEY(图书号)
)
注意,除空值/非空值约束外,其它约束都可定义一个约束名,用 CONSTRAINT <约束名>来定
义,如:
CREATE TABLE 图书 (
图书号 CHAR(20)
NOT NULL ,
书名
作者
VARCHAR(50)
NULL ,
VARCHAR(30)
NULL ,
出版社 VARCHAR(30)
NULL ,
单价
FLOAT
NULL,
CONSTRAINT
PK1
PRIMARY KEY(图书号)
)
CREATE TABLE 读者(
读者号 CHAR(10)
PRIMARY KEY,
姓名
性别
‘王%’
CHAR(8)
NOT NULL ,
CHAR(2)
NOT NULL , SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘王%’
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘王%’
SELECT * FROM 借阅 WHERE 归还日期 IS NULL
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘王%’
SELECT * FROM 借阅 WHERE 归还日期 IS NULL
办公电话 CHAR(8)
NULL ,
部门
VARCHAR(30)
NULL
)
列后的 NULL 空值约束可以省略,因为缺省是 NULL。
CREATE TABLE 借阅(
读者号 CHAR(10)
NOT NULL ,
图书号 CHAR(20)
NOT NULL ,
借出日期 CHAR(8)
NOT NULL ,
归还日期 CHAR(8)
NULL,
PRIMARY KEY (读者号, 图书号),
FOREIGN KEY (读者号)
REFERENCES 读者(读者号),
FOREIGN KEY (图书号)
REFERENCES 图书(图书号)
)
注意,除空值/非空值约束外,其它约束都可定义一个约束名,用 CONSTRAINT <约束名>
来定义,如:
CREATE TABLE 借阅(
读者号 CHAR(10)
NOT NULL ,
图书号 CHAR(20)
NOT NULL ,
借出日期 CHAR(8)
NOT NULL ,
归还日期 CHAR(8)
NULL,
CONSTRAINT
PK2
PRIMARY KEY (读者号, 图书号),
CONSTRAINT
CK2
FOREIGN KEY (读者号)
REFERENCES 读者(读者号),
CONSTRAINT
CK3
FOREIGN KEY (图书号)
REFERENCES 图书(图书号)
)
上述定义中的主键约束,由于涉及表中的两个列,因此只能定义为表级约束。有两个外键约
束,均可定义在列之后,作为列级约束。但通常定义为表级约束,因为外键定义较长。等价
定义为:
CREATE TABLE 借阅(
读者号 CHAR(10)
NOT NULL
FOREIGN KEY (读者号)
REFERENCES 读者(读者号),
图书号 CHAR(20)
NOT NULL FOREIGN KEY (图书号)
REFERENCES 图书(图书号),
借出日期 CHAR(8)
NOT NULL ,
归还日期 CHAR(8)
NULL,
PRIMARY KEY (读者号, 图书号)
)
也等价于:CREATE TABLE 借阅(
读者号 CHAR(10)
NOT NULL REFERENCES 读者(读者号),
图书号 CHAR(20)
NOT NULL REFERENCES 图书(图书号),
借出日期 CHAR(8)
NOT NULL ,
归还日期 CHAR(8)
NULL,
PRIMARY KEY (读者号, 图书号)
)
(3)基于图书馆数据库的三个表,用 SQL 语言完成以下各项操作:
1)给图书表增加一列“ISBN”,数据类型为 CHAR(10)
ALTER TABLE 图书
ADD
ISBN
CHAR(10)
2)为刚添加的 ISBN 列增加缺省值约束,约束名为 ISBNDEF,缺省值为’7111085949’;
ALTER TABLE 图书
ADD
CONSTRAINT ISBNDEF
DEFAULT ‘7111085949’ FOR ISBN
3)为读者表的’办公电话’列,添加一个 CHECK 约束,要求前五位’88320’,约束名为
CHECKDEF。
ALTER TABLE 读者
ADD
CONSTRAINT CHECKDEF
CHECK (办公电话 LIKE ‘(88320)[0-9][0-9][0-9]’)
4)删除图书表中 ISBN 列增加缺省值约束;
ALTER TABLE 图书
DROP
CONSTRAINT
ISBNDEF
5)删除读者表中“办公电话”列的 CHECK 约束;
ALTER TABLE 读者
DROP
CONSTRAINT
CHECKDEF
6)删除图书表中新增的列 ISBN;
ALTER TABLE 图书
DROP
COLUMN
ISBN
(4)基于图书馆数据库的三个表,用 SQL 语言完成以下数据更新操作:
1)向读者表加入一个新读者,该读者的信息为:
(‘200197’, ‘王小平’, ‘男’ , ‘88320732’,’存中楼’)
INSERT INTO 读者 VALUES (‘200197’, ‘王小平’, ‘男’, ‘88320732 ‘, ‘物理系’)
2)向借阅表插入一个借阅记录,表示读者’王小平’借阅了一本书,图书号为’TP316/ZW6’,
借出日期为当天的日期,归还日期为空值。
INSERT INTO 借阅
VALUES(‘200197’,’TP316/ZW6’,CONVERT(CHAR(8),GETDATE(),112),NULL)
3)读者’王小平’在借出上述图书后 10 归还该书;
UPDATE 借阅 SET 归还 日期 = 借出 日期 + 10 WHERE 读者 号 =(SELECT 读者 号 FROM 读者
WHERE 姓名=’王小平’ )
4)当读者’王小平’按期归还图书时,删除上述借阅记录;
DELETE FROM 借阅
WHERE 读者号=(SELECT 读者号 FROM 读者
WHERE 姓名= ‘王小平’ )
(5)针对以上三个表,用 SQL 语言完成以下各项查询:
1)查询全体图书的图书号,书名,作者,出版社,单价。
SELECT 图书号,书名,作者,出版社,单价 FROM 图书
等价于:
SELECT * FROM 图书
2)查询全体图书的信息,其中单价打 8 折,并且将该列设置别名为’打折价’ 。
SELECT 图书号,书名,作者,出版社,单价*0.8 ‘打折价’ FROM 图书
SELECT 图书号,书名,作者,出版社,单价*0.8 AS ‘打折价’ FROM 图书
SELECT 图书号,书名,作者,出版社,’打折价’=单价*0.8
FROM 图书
3)显示所有借阅者的读者号,并去掉重复行。
SELECT
DISTINCT 读者号 FROM 借阅
若要保留重复行,则用:
SELECT ALL 读者号 FROM 借阅
4)查询所有单价在 20 到 30 之间的图书信息。
SELECT * FROM 图书
WHERE 单价 BETWEEN 20 AND 30
此句等价于:
SELECT * FROM 图书
WHERE 单价>= 20.00 AND 单价 <=30.00
5)查询所有单价不在 20 到 30 之间的图书信息。
SELECT * FROM 图书
WHERE 单价 NOT BETWEEN 20.00 AND 30.00
此句等价于:
SELECT * FROM 图书
WHERE 单价< 20
OR 单价 >30
6)查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
SELECT * FROM 图书
WHERE 出版社 IN (‘机械工业出版社’, ‘科学出版社’, ‘人民邮电出版社’)
此句等价于:
SELECT * FROM 图书
WHERE 出版社=’机械工业出版社’
OR 出版社= ‘科学出版社’ OR 出版社= ‘人民邮电出版社’
7)查询既不是机械工业出版社、也不是科学出版社出版的图书信息
SELECT * FROM 图书
WHERE 出版社 NOT IN (‘机械工业出版社’, ‘科学出版社’)
此句等价于:
SELECT * FROM 图书
WHERE 出版社!=’机械工业出版社’ AND 出版社!= ‘科学出版社’
8)查找姓名的第二个字符是’建’并且只有两三个字符的读者的读者号、姓名。
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘_建_’
9)查找姓名以’王’开头的所有读者的读者号、姓名。
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘王%’
10)查找姓名以’王’、’张’或’李’开头的所有读者的读者号、姓名。
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘[王张李]%’
11)查找姓名不是以’王’、’张’或’李’开头的所有读者的读者号、姓名。
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 LIKE ‘[^王张李]%’
此句等价于:
SELECT 读者号,姓名 FROM 读者 WHERE 姓名 NOT LIKE ‘[王张李]%’
12)查询无归还日期的借阅信息。
SELECT * FROM 借阅 WHERE 归还日期 IS NULL
不等价于:SELECT * FROM 借阅 WHERE 归还日期=’’
13)查询有归还日期的借阅信息。
SELECT * FROM 借阅 WHERE 归还日期 IS NOT NULL
14)查询单价在 20 元以上,30 元以下的机械工业出版社出版的图书名,单价。
SELECT 书名,单价 FROM 图书 WHERE 出版社=’机械工业出版社’ AND 单价>20.00 AND 单
价<30.00
15)查询机械工业出版社或科学出版社出版的图书名,出版社,单价。
SELECT 书名,出版社,单价 FROM 图书 WHERE 出版社=’机械工业出版社’ OR 出版社=’
科学出版社’
16)求读者的总人数
SELECT COUNT(*) AS ‘读者总人数’ FROM 读者
17)求借阅了图书的读者的总人数
SELECT COUNT(DISTINCT 读者号) AS ‘借阅过图书的读者总人数’ FROM 借阅
18)求机械工业出版社图书的平均价格、最高价、最低价。
SELECT AVG(单价) AS ‘平均价’,MAX(单价) AS ‘最高价’, MIN(单价) AS ‘最低价’
FROM 图书 WHERE 出版社=’机械工业出版社’
19) 查询借阅图书本数超过 2 本的读者号、总本数。并按借阅本数值从大到小排序。
SELECT 读者号,COUNT(图书号) AS ‘总本数’
FROM 借阅
GROUP BY 读者号
HAVING COUNT(*) > 2
ORDER BY COUNT(图书号) DESC
(6)针对以上三个表,用 SQL 语言完成以下各项多表连接查询、子查询、组合查询
1)查询读者的基本信息以及他/她借阅的情况。
SELECT 读者.*,借阅.*
FROM 读者, 借阅
WHERE 读者.读者号 = 借阅. 读者号
上述是等值连接,改为自然连接,表示如下:
SELECT 读者.*,借阅.图书号,借阅.借出日期,借阅.归还日期
FROM 读者, 借阅
WHERE 读者.读者号 = 借阅. 读者号
2)查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期。
SELECT 读者.读者号,姓名,书名,借出日期,归还日期
FROM 读者, 图书,借阅
WHERE 读者.读者号 = 借阅.读者号 AND 图书.图书号=借阅.图书号
3)查询借阅了机械工业出版社出版,并且书名中包含’数据库’三个字的图书的读者,显示
读者号、姓名、书名、出版社,借出日期、归还日期。
SELECT 读者.读者号,姓名,书名,出版社,借出日期,归还日期
FROM 读者,图书,借阅
WHERE 读者.读者号 = 借阅.读者号 AND 图书.图书号=借阅.图书号
AND 出版社=’机械工业出版社’ AND 书名 LIKE ‘%数据库%’
4)查询至少借阅过 1 本机械工业出版社出版的书的读者的读者号、姓名、书名,借阅本数,
并按借阅本数多少降序排列。
SELECT 借阅.读者号,姓名,书名, COUNT(借阅.图书号) ‘借阅本数’
FROM 读者, 图书,借阅
WHERE 读者.读者号 = 借阅.读者号 AND 图书.图书号=借阅.图书号
AND 出版社=’机械工业出版社’
GROUP BY 借阅.读者号, 姓名,书名
HAVING COUNT(借阅.图书号) >=1
ORDER BY COUNT(借阅.图书号) DESC
5)查询与’王平’的办公电话相同的读者的姓名。
SELECT R2.姓名
FROM 读者 R1, 读者 R2
where R1.办公电话= R2.办公电话 AND R1.姓名= ‘王平’
6)查询办公电话为’88320701’的所有读者的借阅情况,要求包括借阅了书籍的读者和没有
借阅的读者,显示他们的读者号、姓名、书名、借阅日期。
SELECT 读者.读者号,姓名,图书号,借出日期
FROM 读者, 借阅
WHERE 读者.读者号*=借阅.读者号
AND 办公电话=’88320701’
上述左外连接可可以用右外连接等价表示:
SELECT 读者.读者号,姓名,图书号,借出日期
FROM 读者,借阅
WHERE 借阅.读者号 =* 读者.读者号
AND 办公电话=’88320701’
在 SQLSERVER2000 中,以上左外连接还可等价表示为:
SELECT 读者.读者号,姓名,图书号,借出日期
FROM 读者 LEFT JOIN 借阅 ON 读者.读者号= 借阅.读者号
WHERE 办公电话=’88320701’
或者用右连接表示为:
SELECT 读者.读者号,姓名,图书号,借出日期
FROM 借阅 RIGHT JOIN 读者 ON 借阅.读者号= 读者.读者号
WHERE 办公电话=’88320701’
7)查询所有单价小于平均单价的图书号、书名、出版社
SELECT 图书号,书名,出版社 FROM 图书
WHERE 单价< (SELECT AVG(单价) ‘平均单价’ FROM 图书)
8)查询’科学出版社’的图书中单价比’机械工业出版社’最高单价还高的的图书书名、单
价。
SELECT 图书号,单价 FROM 图书
WHERE 出版社=’科学出版社’ AND 单价> (
SELECT MAX(单价) FROM 图书 WHERE 出版社=’机械工业出版社’)
等价于:
SELECT 图书号,单价 FROM 图书
WHERE 出版社='科学出版社' AND 单价>ALL (