比碩筆試題2017

1、精選優(yōu)質(zhì)文檔-傾情為你奉上SELECT * FROM card;SELECT * FROM books;SELECT * FROM borrow;- MySQL 較新的版本中（MySQL 6.0.5），也還沒有產(chǎn)生微秒的函數(shù)，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 D

2、ATE_ADD(2013-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.c

4、lass AS 班級,bs.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班同學當期正在借閱圖書的應還日期延長一周（注意根據(jù)還書日期判斷)UPDATE borrow SET sd

5、ate=(DATE_ADD(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表中刪除當前無人借閱的圖書記錄，（注意根據(jù)還書日期判斷）；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.查詢當前庫存數(shù)量超過十本的圖書記錄,輸出書號，書名，作者，庫存冊數(shù)；SE

7、LECT bno AS 書號,bname AS 書名,author AS 作者,quantity AS 庫存冊數(shù) FROM books WHERE quantity 10;- 10.按類別統(tǒng)計圖書借閱數(shù)據(jù)，輸出類別，圖書種類數(shù)，總借閱數(shù)；SELECT bs.category AS 類別,COUNT(bs.category) AS 圖書種類數(shù),COUNT(bw.bno) AS 總借閱數(shù) FROM books AS bs JOIN borrow AS bw ON bs.bno=bw.bno GROUP BY category;- 附加1.查找本年度借閱量最多的十本書，輸出書號，書名，作者，庫存冊數(shù)

8、；- 附加2.查找現(xiàn)有圖書中價格最高的圖書，輸出書號，書名，作者，庫存冊數(shù)；- 附加3.查詢借閱過“計算方法” 但沒有借閱過“計算方法習題集”的讀者，輸出其借書卡號，姓名，班級，并按卡號降序排序輸出；- 附加4.查找圖書的借閱對比，輸出書號，書名，作者，類別，本月借閱次數(shù)，上月借閱次數(shù)，按本月借閱次數(shù)倒敘排序；- 附加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(VA

10、RCHAR(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), GETDATE(

11、), 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), GETDAT

12、E(), 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), G

13、ETDATE(), 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 0

14、5 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): 2006-05-