现在的位置: 首页数据库, 面试>正文
数据库实践_MYSQL
2012年12月24日 数据库, 面试 暂无评论 ⁄ 被围观 13,046 views+

问题描述

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


MYSQL-准备

建表语句
DROP TABLE IF EXISTS CARD;
CREATE TABLE CARD (
CNO INT,
CNAME VARCHAR(20),
CLASS VARCHAR(20),
PRIMARY KEY(CNO)
)
DROP TABLE IF EXISTS BOOKS ;
CREATE TABLE BOOKS (
BNO INT,
BNAME VARCHAR(20),
AUTHOR VARCHAR(20),
PRICE DOUBLE,
QUANTITY INT,
PRIMARY KEY(BNO)
)
DROP TABLE IF EXISTS BORROW ;
CREATE TABLE BORROW (
CNO INT ,
BNO INT ,
RDATE DATETIME,
PRIMARY KEY(CNO,BNO)
)
–建外键
ALTER TABLE BORROW ADD CONSTRAINT FK_CNO FOREIGN KEY (CNO) REFERENCES CARD(CNO);
ALTER TABLE BORROW ADD CONSTRAINT FK_BNO FOREIGN KEY (BNO) REFERENCES BOOKS(BNO);
–查询
SELECT * FROM CARD;
SELECT * FROM BOOKS;
SELECT * FROM BORROW;
–插入语句
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1001′, ‘NAME1′,’CLASS1′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1002′, ‘NAME2′,’CLASS1′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1003′, ‘NAME3′,’CLASS1′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1004′, ‘NAME4′,’CLASS2′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1005′, ‘NAME5′,’CLASS2′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1006′, ‘NAME6′,’CLASS2′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1007′, ‘NAME7′,’CLASS3′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1008′, ‘NAME8′,’CLASS3′);
INSERT INTO CARD (CNO, CNAME, CLASS)VALUES(’1009′, ‘NAME9′,’CLASS3′);INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2001′, ‘数据结构’, ‘AUTHOR1′, ’38.0′, ’10′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2002′, ‘设计模式’, ‘AUTHOR2′, ’43.0′, ’10′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2003′, ‘组成原理’, ‘AUTHOR3′, ’30.0′, ’10′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2004′, ‘JAVA开发’, ‘AUTHOR4′, ’20.0′, ’10′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2005′, ‘HTML5′, ‘AUTHOR5′, ’28.0′, ’8′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2006′, ‘数据库’, ‘AUTHOR6′, ’50.0′, ’8′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2007′, ‘计算机网络’, ‘AUTHOR7′, ’40.0′, ’8′);
INSERT INTO BOOKS (BNO, BNAME, AUTHOR, PRICE, QUANTITY)VALUES(’2008′, ‘C++开发’, ‘AUTHOR8′, ’50.0′, ’5′); 

INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2001′, ’2011-12-9′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2003′, ’2011-12-9′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2004′, ’2011-12-2′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2006′, ’2011-12-22′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2007′, ’2011-12-5′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1001′, ’2008′, ’2011-12-9′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1002′, ’2001′, ’2011-12-6′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1003′, ’2002′, ’2011-12-17′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1002′, ’2002′, ’2011-12-23′);
INSERT INTO BORROW (CNO, BNO, RDATE)VALUES(’1005′, ’2001′, ’2011-12-9′);


MYSQL

1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。[-]

CREATE TABLE BORROW (
CNO INT ,
BNO INT ,
RDATE DATETIME,
PRIMARY KEY(CNO,BNO)
)
ALTER TABLE BORROW ADD CONSTRAINT FK_CNO FOREIGN KEY (CNO) REFERENCES CARD(CNO);
ALTER TABLE BORROW ADD CONSTRAINT FK_BNO FOREIGN KEY (BNO) REFERENCES BOOKS(BNO);
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
SELECT CNO,COUNT(*)
     FROM borrow
 GROUP BY cno
 HAVING COUNT(*) > 5;
3. 查询借阅了”数据结构”一书的读者,输出姓名及班级。
SELECT cname, class FROM card
WHERE cno IN ( SELECT a.cno FROM BORROW a,BOOKS b
     WHERE a.bno = b.bno AND b.bname=”数据结构”);
或者
     SELECT cname, class
          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 cno ,bno,rdate FROM borrow WHERE rdate < NOW();
5. 查询书名包括”网络”关键词的图书,输出书号、书名、作者。
SELECT bno,bname,AUTHOR
 FROM books
WHERE bname LIKE ‘%网络%’;
6. 查询现有图书中价格最高的图书,输出书名及作者。[-]
SELECT bname,author
     FROM books
 WHERE price = (
          SELECT MAX(price)
          FROM books);
7. 查询当前借了”数据结构”但没有借”JAVA开发”的读者,输出其借书卡号,并按卡号降序排序输出。
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=”JAVA开发” AND aa.cno=a.cno)
ORDER BY a.cno DESC;
8. 将”class1″班同学所借图书的还期都延长一周。
UPDATE b SET rdate = DATE_ADD(b.rdate,INTERVAL 7 DAY) FROM card a,borrow b WHERE a.cno = b.cno AND a.class = “CLASS1″;
UPDATE  borrow b,card a SET b.rdate =DATE_ADD(b.rdate,INTERVAL 7 DAY) WHERE a.cno = b.cno AND a.class = ‘CLASS1′;

9. 从BOOKS表中删除当前无人借阅的图书记录。
DELETE  FROM books a WHERE NOT EXISTS (SELECT * FROM borrow b WHERE a.bno = b.bno);
10.如果经常按书名查询图书信息,请建立合适的索引。
CREATE INDEX idx_books_bname ON books(bname);
11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是”HTML5″,就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
DROP TABLE IF EXISTS BORROW_SAVE ;
CREATE TABLE BORROW_SAVE (
CNO INT ,
BNO INT ,
RDATE DATETIME,
PRIMARY KEY(CNO,BNO)
)
DROP TRIGGER IF EXISTS TR_BORROW_SAVE;
DELIMITER $$
CREATE TRIGGER TR_BORROW AFTER INSERT ON BORROW FOR EACH ROW
BEGIN
INSERT INTO BORROW_SAVE SELECT a.* FROM borrow a,books b WHERE a.bno = b.bno AND b.bname = ‘HTML5′;
END$$
DELIMITER
 ;
12.建立一个视图,显示”CLASS2″班学生的借书信息(只要求显示姓名和书名)。
CREATE VIEW v_borrow_class2
AS
SELECT a.CNAME, b.BNAME
FROM CARD a,books b,borrow c
WHERE a.class = ‘CLASS2′ AND A.CNO = C.CNO AND B.BNO = C.BNO;
13.查询当前同时借有”数据结构”和”JAVA开发”两本书的读者,输出其借书卡号,并按卡号升序排序输出。
SELECT a.cno
FROM borrow a,books b
WHERE a.bno = b.bno AND b.bname IN( ‘数据结构’,'JAVA开发’)
GROUP BY a.cno
HAVING COUNT(*) = 2
ORDER BY a.cno ASC;
14.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
ALTER TABLE books DROP PRIMARY KEY(bno);
ALTER TABLE books ADD PRIMARY KEY(bno);
15.对CARD表做如下修改:
a. 将CNAME最大列宽增加到30个字符(假定原为20个字符)。
     ALTER TABLE card MODIFY cname VARCHAR(30);
b. 为该表增加1列NAME(系名),可变长,最大20个字符。–char 固定长度 varchar 可变长度 

     ALTER TABLE card ADD Dept VARCHAR(20);


给我留言

留言无头像?


无觅相关文章插件,快速提升流量