数据库常用知识补充

本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

1． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
CREATE TABLE BORROW(CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),

RDATE datetime, PRIMARY KEY(CNO,BNO))

2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。
SELECT CNO,借图书册数=COUNT(*) FROM BORROW GROUP BY CNO HAVING COUNT(*)>5
3． 查询借阅了"水浒"一书的读者，输出姓名及班级。
SELECT FROM CARD c WHERE EXISTS(SELECT * FROM BORROW a,BOOKS b
WHERE a.BNO = b.BNO AND b,BNAME='水浒' AND a.CNO=c.CNO)
4． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
SELECT * FROM BORROW WHERE RDATE<GETDATE
5． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。
SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE BNAME LIKE %网络%
补充：当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助
LIKE来实现模糊查询。
LIKE需要借助两个通配符：
%：表示0到多个字符
_：标识单个字符
6． 查询现有图书中价格最高的图书，输出书名及作者。
SELECT BNO，BNAME,AUTHOR FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS)
7． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
SELECT a.CNO FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME='计算方法'
AND NOT EXISTS (SELECT * FROM BORROW aa,BOOKS bb WHERE aa.BNO=bb.BNO AND bb.BNAME='计算方法
习题集'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC
8． 将"C01"班同学所借图书的还期都延长一周。
UPDATE b SET RDATE ADD(DAY,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO AND a.CLASS='C01'
9． 从BOOKS表中删除当前无人借阅的图书记录。
DELETE A FROM BOOKS a
WHERE NOT EXISTS (SELECT * FROM BORROW WHERE BNO=a.BNO)
10．如果经常按书名查询图书信息，请建立合适的索引。
CREATE CLUSTERED INDEX IDX_BBOKS_BNAME ON BOOKS(BNAME)
补充：Culstered Index聚集索引 主键就是聚集索引（按照物理存放的位置建立的 所以一张表只有一聚
集索引；聚集索引在搜索时，速度会比非聚集索引快）
non-clustered非聚集索引 可以有多个
11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将
该读者的借阅记录保存在BORROW_SAVE表中（注BORROW_SAVE表结构同BORROW表）。
CREATE TRIGGER TR_SAVE ON BORROW 
FOR INSERT ,UPDATE AS 
IF @@ROW COUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO AND b.BNAME='数据库技术及应用'
12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。
CREATE VIEW V_VIEW AS
SELECT a.NAME,b.BNAME FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS='力01'
13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
SELECT a.CNO FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME IN('计算方法'，'组合数学')
GROUP BY a.CNO 
HAVING COUNT(*)=2 
ORDER BY a.CNO ASC
补充：ORDER BY 排序操作，必须出现在SELECT中的最后一个子句。ASC升序，DESC降序
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)
15．对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
ALTER TABLE CARD MODIFY NAME VARCHA(10) 
b. 为该表增加1列NAME（系名），可变长，最大20个字符。
ALTER TABLE CARD ADD 系名 VARCHAR2(20)
注意：增加的列只能增加到当前表中列的最后，不能插入到现有的列中；
建表之后，可以改变表中列的数据类型、长度和默认值，
注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，
把长度由大改小，有可能不成功，比如原来的类型是VARCHAR2(100),其中已经存放了100个字节长度的数据，
如果要改为VARCHAR2（80），则不会修改成功。


当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
ORDER BY子句：执行顺序为从左到右排序，消耗资源。

数据库设计： 

工具：POWERDESIGNER  E-R图（实体-关系图）

数据库表设计：必须遵守三范式，

1.1 第一范式（1NF）无重复的列---------属性（字段）唯一

指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

1.2 第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]--------记录（实例）唯一

完全依赖是指不能存在仅依赖主关键字一部分的属性（设有函数依赖W→A，若存在XW，有X→A成立，那么称W→A是局部依赖，否则就称W→A是完全函数依赖）。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

1.3 第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]---------表唯一

第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。

数据库设计步骤：

1.数据库需求分析:需求收集和分析,得到数据字典和数据流图<设计实体>
2.数据库概念结构设计:对用户需求综合、归纳与抽象,形成概念模型,用E-R图表示.<实体E-R图集成，Sysbase的POWERDESIGNER工具>
3.数据库逻辑结构设计:将概念结构转换为某个DBMS所支持的数据模型.<设计表结构（如字段、数据类型、长度、约束条件、描述/别名）>
4.数据库物理结构实现:为逻辑数据模型选取一个最适合应用环境的物理结构.<使用T-SQL语言设计得到数据库和数据表>
5.数据库实施阶段：建立数据库,编制与调试应用程序,组织数据入库,程序试运行.
6.数据库运行和维护阶段：对数据库系统进行评价、调整与修改.