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

前言

—李冠《蝶恋花·春暮》

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

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

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

说明

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

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

题目详情

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>, 创建表结构:

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>, 完善表结构:

## 添加主键(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>, 插入数据:

## 向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 语句,要求定义主码完整性约束和引用完整性约束。

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

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

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

3) 查询借阅了”Chinese Book”一书的读者,输出姓名及班级。

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) 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。

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

附上2 ~ 4 运行截图:

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

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

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

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

7) 查询当前借了”Chinese Book”但没有借”English Book”的读者,输出其借书
卡号,并按卡号降序排序输出。

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”班同学所借图书的还期都延长一周。

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 截图:

9) 从 T_BOOKS 表中删除当前无人借阅的图书记录

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) 如果经常按书名查询图书信息,请建立合适的索引。

create index index_t_books_name on t_books(bname);

11) 在 BORROW 表上建立一个触发器, 完成如下功能:如果读者借阅的书名是”Computer Book”, 就将该读者的借阅记录保存在 BORROW_SAVE 表中(注ORROW_SAVE 表结构同 BORROW 表) 。

## 先创建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 运行截图:

12) 建立一个视图,显示”力 01”班学生的借书信息(只要求显示姓名和书名)。

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”两本书的读者,输出其借书卡号,并按卡号升序排序输出。

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 表追加定义主码的语句。

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

15) 对 CARD 表做如下修改:

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

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运行截图: