比硕笔试题2017

1、SELECT * FROM card;SELECT * FROM books;SELECT * FROM borrow;- MySQL 较新的版本中（MySQL 6.0.5），也还没有产生微秒的函数，now() 只能精确到秒。 MySQL 中也没有存储带有毫秒、微秒的日期时间类型。 SELECT DATE_FORMAT(NOW(),%Y-%m-%d %h:%i:%s); SELECT DATE_FORMAT(NOW(),%Y-%m-%d %T); SELECT DATE_FORMAT(2010-12-01 06:03:16,%Y-%m-%d %T); SELECT DATE_ADD(2013-

2、01-18, INTERVAL 1 YEAR); SELECT DATE_ADD(DATE_FORMAT(2010-12-01 06:03:16,%Y-%m-%d %T),INTERVAL 7 DAY); - SELECT convert(varchar(16),getdate(),121); - Select convert(char(10),getdate(),121) SELECT DATE_FORMAT( rdate,%Y-%m-%d %T) FROM borrow;SELECT rdate FROM borrow; - 1.查询【借阅过(借书已还？)】“水浒”一书的读者卡号、姓名、班

3、级。 SELECT o AS 卡号, AS 姓名,cd.class AS 班级 FROM card AS cd JOIN borrow AS bw ON o = o JOIN books AS bs ON bw.bno = bs.bno WHERE bs.bname=水浒 AND DATE_FORMAT( bw.rdate,%Y-%m-%d %T) 5; - 5.查找【过期未还借阅记录】，输出借阅者卡号，姓名，班级，书名，借书日期，应还日期； SELECT o AS 借阅者卡号, AS 姓名,cd.class AS 班级,bs

4、.bname AS 书名,bw.bdate AS 借书日期,bw.sdate AS 应还日期 FROM card AS cd JOIN borrow AS bw ON o = o JOIN books AS bs ON bw.bno = bs.bno WHERE DATE_FORMAT( bw.sdate,%Y-%m-%d %T) DATE_FORMAT(NOW(),%Y-%m-%d %T) AND bw.rdate =null;- 6.将co1班同学当期正在借阅图书的应还日期延长一周（注意根据还书日期判断)UPDATE borrow SET sdate=(DATE_ADD

5、(DATE_FORMAT(sdate,%Y-%m-%d),INTERVAL 7 DAY)WHERE id IN (SELECT a.id FROM (SELECT bw.id FROM card AS cd JOIN borrow AS bw ON o=o WHERE cd.class = col) AS a );- 法二：update borrow as bw inner join card as cdon o=o set bw.sdate= (DATE_ADD(DATE_FORMAT(bw.sdate,%Y-%m-%d),INTERVAL 7 DAY

6、) where cd.class = co1;- 7.从books表中删除当前无人借阅的图书记录，（注意根据还书日期判断）；DELETE FROM books WHERE bno IN( SELECT a.bno FROM (SELECT bs.bno FROM books AS bs JOIN borrow AS bwON bs.bno = bw.bnoWHERE DATE_FORMAT(bw.rdate,%Y-%m-%d %T) DATE_FORMAT(bw.bdate,%Y-%m-%d %T) AS a ) );- 9.查询当前库存数量超过十本的图书记录,输出书号，书名，作者，库存册数；

7、SELECT bno AS 书号,bname AS 书名,author AS 作者,quantity AS 库存册数 FROM books WHERE quantity 10;- 10.按类别统计图书借阅数据，输出类别，图书种类数，总借阅数；SELECT bs.category AS 类别,COUNT(bs.category) AS 图书种类数,COUNT(bw.bno) AS 总借阅数 FROM books AS bs JOIN borrow AS bw ON bs.bno=bw.bno GROUP BY category;- 附加1.查找本年度借阅量最多的十本书，输出书号，书名，作者，库存

8、册数；- 附加2.查找现有图书中价格最高的图书，输出书号，书名，作者，库存册数；- 附加3.查询借阅过“计算方法” 但没有借阅过“计算方法习题集”的读者，输出其借书卡号，姓名，班级，并按卡号降序排序输出；- 附加4.查找图书的借阅对比，输出书号，书名，作者，类别，本月借阅次数，上月借阅次数，按本月借阅次数倒叙排序；- 附加5.查询每本书的最后一位借阅者，输出书号，书名，作者 ，借阅日期，卡号，姓名，班级； - 日期格式SELECT CONVERT(VARCHAR(100), GETDATE(), 0): 05 16 2006 10:57AMSELECT CONVERT(VARCHAR(100)

9、, GETDATE(), 1): 05/16/06SELECT CONVERT(VARCHAR(100), GETDATE(), 2): 06.05.16SELECT CONVERT(VARCHAR(100), GETDATE(), 3): 16/05/06SELECT CONVERT(VARCHAR(100), GETDATE(), 4): 16.05.06SELECT CONVERT(VARCHAR(100), GETDATE(), 5): 16-05-06SELECT CONVERT(VARCHAR(100), GETDATE(), 6): 16 05 06SELECT CONVERT(

10、VARCHAR(100), GETDATE(), 7): 05 16, 06SELECT CONVERT(VARCHAR(100), GETDATE(), 8): 10:57:46SELECT CONVERT(VARCHAR(100), GETDATE(), 9): 05 16 2006 10:57:46:827AMSELECT CONVERT(VARCHAR(100), GETDATE(), 10): 05-16-06SELECT CONVERT(VARCHAR(100), GETDATE(), 11): 06/05/16SELECT CONVERT(VARCHAR(100), GETDAT

11、E(), 12): SELECT CONVERT(VARCHAR(100), GETDATE(), 13): 16 05 2006 10:57:46:937SELECT CONVERT(VARCHAR(100), GETDATE(), 14): 10:57:46:967SELECT CONVERT(VARCHAR(100), GETDATE(), 20): 2006-05-16 10:57:47SELECT CONVERT(VARCHAR(100), GETDATE(), 21): 2006-05-16 10:57:47.157SELECT CONVERT(VARCHAR(100), GETD

12、ATE(), 22): 05/16/06 10:57:47 AMSELECT CONVERT(VARCHAR(100), GETDATE(), 23): 2006-05-16SELECT CONVERT(VARCHAR(100), GETDATE(), 24): 10:57:47SELECT CONVERT(VARCHAR(100), GETDATE(), 25): 2006-05-16 10:57:47.250SELECT CONVERT(VARCHAR(100), GETDATE(), 100): 05 16 2006 10:57AMSELECT CONVERT(VARCHAR(100),

13、 GETDATE(), 101): 05/16/2006SELECT CONVERT(VARCHAR(100), GETDATE(), 102): 2006.05.16SELECT CONVERT(VARCHAR(100), GETDATE(), 103): 16/05/2006SELECT CONVERT(VARCHAR(100), GETDATE(), 104): 16.05.2006SELECT CONVERT(VARCHAR(100), GETDATE(), 105): 16-05-2006SELECT CONVERT(VARCHAR(100), GETDATE(), 106): 16

14、 05 2006SELECT CONVERT(VARCHAR(100), GETDATE(), 107): 05 16, 2006SELECT CONVERT(VARCHAR(100), GETDATE(), 108): 10:57:49SELECT CONVERT(VARCHAR(100), GETDATE(), 109): 05 16 2006 10:57:49:437AMSELECT CONVERT(VARCHAR(100), GETDATE(), 110): 05-16-2006SELECT CONVERT(VARCHAR(100), GETDATE(), 111): 2006/05/16SELECT CONVERT(VARCHAR(100), GETDATE(), 112): SELECT CONVERT(VARCHAR(100), GETDATE(), 113): 16 05 2006 10:57:49:513SELECT CONVERT(VARCHAR(100), GETDATE(), 114): 10:57:49:547SELECT CONVERT(VARCHAR(100), GETDATE(), 120):