数据库题目整理及详解(三)

前言

—李冠《蝶恋花·春暮》

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

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

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

枯草堆中顽强的四叶草


说明

接着之前的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);

create_table


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);

修改表结构

desc显示表结构


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;

运行截图:

card_values

books_values

borrow_values1

borrow_values2


题目详解

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

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


2) 找出借书超过 3 本的读者,输出借书卡号及所借图书册数。

1
select cno, count(*) cs from t_borrow br group by cno having count(*) > 3;

3) 查询借阅了”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';

4) 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。

1
2
select cno, bno, rdate from t_borrow where rdate < current_date();
## 注意:current_date()函数是当前日期, 相当于oracle中的sysdate.

附上2 ~ 4 运行截图:

2-4


5) 查询书名包括”Chinese”关键词的图书,输出书号、书名、作者。

1
select bno, bname, author from t_books where bname like '%Chinese%';

6) 查询现有图书中价格最高的图书,输出书名及作者。

1
select bname, author from t_books where price =(select max(price) from t_books);

7) 查询当前借了”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;

8) 将”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;

附上5 ~ 8 截图:

5-8


9) 从 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语句.

10) 如果经常按书名查询图书信息,请建立合适的索引。

1
create index index_t_books_name on t_books(bname);

11) 在 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数据运行通过.

附上9 ~ 11 运行截图:

9-11


12) 建立一个视图,显示”力 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;

13) 查询当前同时借有”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;

14) 假定在建 BOOKS 表时没有定义主码,写出为 BOOKS 表追加定义主码的语句。

1
2
alter table t_books 
add constraint pk_t_books primary key (bno);

15) 对 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;

附上12 ~ 15运行截图:

12-15


参考资料

[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

Enjoy it ? Donate me ! 求鼓励,求支持!
0%