数据库题目整理及详解（三）

Posted on | In
Symbols count in article: 24k | Reading time ≈ 22 mins.

前言

—李冠《蝶恋花·春暮》

遥夜亭皋闲信步。
才过清明，渐觉伤春暮。
数点雨声风约住。朦胧淡月云来去。

桃杏依稀香暗渡。
谁在秋千，笑里轻轻语。
一寸相思千万绪。人间没个安排处。

大早, 喜见枯草堆中顽强的四叶草:

DB-2-show

说明

接着之前的SQL语句继续整理, 内容大多参考了互联网上的很多内容, 好多都相似, 结尾缀上出处, 感谢分享. 这一部分内容, 在mysql中也亲自测试了, 带有截图.

这一部分主要内容为：mysql数据库改变表结构相关的SQL语句, 在书写上的格式、规范及环境配置等，详见：数据库题目整理及详解（一），说明部分 .

题目详情

  1. 设某图书馆借阅系统数据库包含如下关系:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
T_CARD(Cno, Cname, Class)
T_BOOKS(Bno, Bname, Author, Price, Quantity)
T_BORROW(Cno, Bno, Rdate)

Cno, Cname, Class 分别表示借书卡的卡号、名称和所在班级;
Bno, Bname, Author, Price, Quantity 分别表示图书的编号、名称、作者、价格和数量;
Cno, Bno, Rdate 分别表示借阅条目的借书卡号、图书编号和还书日期。请使用 SQL 语句表示如下功能:


(1)  写出建立该数据库表的 SQL 语句,要求定义主码完整性约束和引用完整性约束。
(2)  找出借书超过 3 本的读者,输出借书卡号及所借图书册数。
(3)  查询借阅了"Chinese Book"一书的读者,输出姓名及班级。
(4)  查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
(5)  查询书名包括"Chinese"关键词的图书,输出书号、书名、作者。
(6)  查询现有图书中价格最高的图书,输出书名及作者。
(7)  查询当前借了"Chinese Book"但没有借"English Book"的读者,
     输出其借书卡号,并按卡号降序排序输出。
(8)  将"class_1"班同学所借图书的还期都延长一周。
(9)  从 T_BOOKS 表中删除当前无人借阅的图书记录。
(10) 如果经常按书名查询图书信息,请建立合适的索引。
(11) 在 BORROW 表上建立一个触发器,完成如下功能:
     如果读者借阅的书名是"Computer Book", 就将该读者的借阅记录
     保存在 BORROW_SAVE 表中(注ORROW_SAVE表结构同 BORROW 表)。
(12) 建立一个视图,显示"力 01"班学生的借书信息(只要求显示姓名和书名)。
(13) 查询当前同时借有"Computer Book"和"Math Book"两本书的读者,
     输出其借书卡号,并按卡号升序排序输出。
(14) 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
(15) 对 CARD 表做如下修改:
	 a. 将 NAME 最大列宽增加到10个字符(假定原为6个字符);
	 b. 为该表增加1列NAME(系名), 可变长, 最大20个字符。

解前初始化

1>, 创建表结构:

1
2
3
4
5
6
7
8
9
10
11
create database books;         ## 创建名为：books的数据库
use books;                     ## 使用emp数据库

## 创建t_card表
create table t_card (cno int(10) primary key, name varchar(100), class varchar(100));           

## 创建t_books表
create table t_books (bno int(10) primary key, bname varchar(100), author varchar(100), price numeric(10,2), quantity int(10));					     

## 创建t_books表
create table t_borrow (cno int(10), bno int(10), rdate date);

2>, 完善表结构:

1
2
3
4
5
6
7
## 添加主键(t_borrow表)
alter table t_borrow add constraint borrow_pk primary key (cno, bno);

## 添加外键(t_borrow表)
alter table t_borrow add constraint borrow_fk_cno foreign key (cno) references t_card(cno);

alter table t_borrow add constraint borrow_fk_bno foreign key (bno) references t_books(bno);

3>, 插入数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
## 向t_card插入9条数据
insert into t_card values(1, 'bryant', 'class_1');
insert into t_card VALUES(2, 'foxus', 'class_1');
insert into T_CARD VALUES(3, 'ennel', 'class_2');
insert into T_CARD VALUES(4, 'keliy', 'class_3');
insert into t_card VALUES(3, 'ennel', 'class_2');
insert into t_card VALUES(4, 'keliy', 'class_3');
insert into T_CARD VALUES(5, 'cinal', 'class_2');
insert into t_card VALUES(6, 'oopp', 'class_3');
insert into t_card VALUES(5, 'cinal', 'class_2');
select * from t_card;

## 向t_books插入10条数据
insert into t_books values (1, 'Chinese Book', 'Mr.mao', 35.8, 20);
insert into t_books values (2, 'Math Book', 'Mr.xiao', 55.4, 20);
insert into t_books values (3, 'English Book', 'Mr.li', 22.6, 20);
insert into t_books values (4, 'Computer Book', 'Mr.yang', 78.8, 15);
insert into t_books values (5, 'Music Book', 'Mr.wang', 25.3, 15);
insert into t_books values (6, 'History Book', 'Mr.mao', 40.8, 12);
insert into t_books values (7, 'Physics Book', 'Mr.tang', 46.6, 10);
insert into t_books values (8, 'Chemistry Book', 'Mr.zou', 33.9, 10);
insert into t_books values (9, 'Biology Book', 'Mr.tu', 23, 10);
insert into t_books values (10, 'Political Book', 'Mr.ke', 36.2, 10);
select * from t_books

## 向t_borrow插入23条数据
insert into t_borrow values(1, 1, '2015-5-21');
insert into t_borrow values(2, 1, '2015-5-21');
insert into t_borrow values(3, 3, '2015-5-28');
insert into t_borrow values(4, 8, '2015-6-21');
insert into t_borrow values(5, 4, '2015-5-11');
insert into t_borrow values(6, 10, '2015-5-31');
insert into t_borrow values(1, 9, '2015-6-10');
insert into t_borrow values(2, 3, '2015-7-2');
insert into t_borrow values(3, 5, '2015-6-5');
insert into t_borrow values(4, 6, '2015-6-2');
insert into t_borrow values(5, 9, '2015-5-22');
insert into t_borrow values(6, 1, '2015-6-1');
insert into t_borrow values(1, 3, '2015-6-1');
insert into t_borrow values(2, 4, '2015-6-15');
insert into t_borrow values(3, 8, '2015-5-15');
insert into t_borrow values(4, 10, '2015-6-22');
insert into t_borrow values(5, 7, '2015-6-13');
insert into t_borrow values(6, 6, '2015-5-18');
insert into t_borrow values(1, 8, '2015-5-19');
insert into t_borrow values(2, 9, '2015-5-20');
insert into t_borrow values(3, 6, '2015-6-15');
insert into t_borrow values(4, 2, '2015-6-15');
insert into t_borrow values(5, 2, '2015-6-15');
select * from t_borrow;

题目详解

  1. 写出建立该数据库表的 SQL 语句,要求定义主码完整性约束和引用完整性约束。

见前部分的解前初始化内容.

  1. 找出借书超过 3 本的读者,输出借书卡号及所借图书册数。
1
select cno, count(*) cs from t_borrow br group by cno having count(*) > 3;
  1. 查询借阅了"Chinese Book"一书的读者,输出姓名及班级。
1
2
3
4
5
select c.cno, c.name cname, c.class classname 
from t_card c, t_books b, t_borrow bo 
where c.cno=bo.cno and 
      b.bno=bo.bno and 
	  b.bname='Chinese Book';
  1. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
1
2
select cno, bno, rdate from t_borrow where rdate < current_date();
## 注意:current_date()函数是当前日期, 相当于oracle中的sysdate.
  1. 查询书名包括"Chinese"关键词的图书,输出书号、书名、作者。
1
select bno, bname, author from t_books where bname like '%Chinese%';
  1. 查询现有图书中价格最高的图书,输出书名及作者。
1
select bname, author from t_books where price =(select max(price) from t_books);
  1. 查询当前借了"Chinese Book"但没有借"English Book"的读者,输出其借书
    卡号,并按卡号降序排序输出。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select cno 
from t_borrow br2 
where exists (
	select cno 
	from t_borrow br, t_books b 
	where b.bname='Chinese Book' and 
		  br.bno=b.bno and 
		  br.cno=br2.cno) and 
		  not exists (
			  select cno 
			  from t_borrow br, t_books b 
			  where b.bname='English Book' and 
				  br.bno=b.bno and 
				  br.cno=br2.cno) 
group by cno 
order by cno desc;
  1. 将"class_1"班同学所借图书的还期都延长一周。
1
2
3
4
5
6
7
8
update t_borrow br set br.rdate=br.rdate + 7 
where exists (
	select cno 
	from t_card 
	where class='class_1' and cno=br.cno);  

## 查看一下更新详情	
select * from t_borrow;
  1. 从 T_BOOKS 表中删除当前无人借阅的图书记录
1
2
3
4
5
6
delete from t_books 
where t_books.bno not in (
	select distinct c.bno from t_borrow c);

## 注意: 如果使用not exist语句将会产生如下错误提示:
ERROR 1093 (HY000): You can't specify target table 't_books' for update in FROM clause , 推荐使用in语句.
  1. 如果经常按书名查询图书信息,请建立合适的索引。
1
create index index_t_books_name on t_books(bname);
  1. 在 BORROW 表上建立一个触发器, 完成如下功能:如果读者借阅的书名是"Computer Book", 就将该读者的借阅记录保存在 BORROW_SAVE 表中(注ORROW_SAVE 表结构同 BORROW 表) 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
## 先创建t_borrow_save表
create table t_borrow_save(cno int(10), bno int(10), rdate date);

## 创建触发器
create or replace trigger trg_borrow 
after insert 
on t_borrow 
for each row 
	begin 
		if :new.bno=4 
		then  
			insert into t_borrow_save(cno, bno, rdate)
			values(:new.cno, :new.bno, :new.rdate); 
		end if;
	end;

## 删除触发器
drop trigger trg_borrow;

## 注意此处, 在oracle数据运行通过.
  1. 建立一个视图,显示"力 01"班学生的借书信息(只要求显示姓名和书名)。
1
2
3
4
5
6
7
8
create view view_t_borrow as
select c.name cname, b.bname bname
from t_borrow br, t_card c, t_books b
where br.cno=c.cno and 
	br.bno=b.bno and c.class='class_1';

## 显示结果
select * from view_t_borrow;
  1. 查询当前同时借有"Computer Book"和"Math Book"两本书的读者,输出其借书卡号,并按卡号升序排序输出。
1
2
3
4
5
select a.cno 
from t_borrow a, t_books b
where a.bno=b.bno and b.bname in ('Computer Book', 'Math Book')
group by a.cno having count(*)=2 
order by a.cno desc;
  1. 假定在建 BOOKS 表时没有定义主码,写出为 BOOKS 表追加定义主码的语句。
1
2
alter table t_books 
add constraint pk_t_books primary key (bno);
  1. 对 CARD 表做如下修改:

a. 将 NAME 最大列宽增加到 10 个字符(假定原为 6 个字符);
b. 为该表增加 1 列 NAME(系名),可变长,最大 20 个字符。

1
2
3
alter table t_card modify name varchar(50);
alter table t_card add name1 varchar(20);
alter table t_Card drop column name1;

参考资料

[1]. http://blog.csdn.net/tu451953337/article/details/45190849
[2]. http://mrxiong.blog.51cto.com/287318/1651098/
[3]. http://blog.csdn.net/jeamking/article/details/5953986
[4]. http://blog.163.com/yang_jianli/blog/static/1619900062011282225274/
[5]. http://www.111cn.net/database/mysql/36105.htm
[6]. http://blog.csdn.net/wjeson/article/details/17166205
[7]. http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html
[8]. http://www.jb51.net/article/49207.htm

0%