數(shù)據(jù)庫課程設計--圖書管理系統(tǒng)-資料下載頁
2025-01-16 17:01本頁面
【正文】 ================while根據(jù)超過日期計算應繳罰金declare @money moneydeclare @day int set @day =0while @daydatediff(day,39。20131139。,getdate()) begin set @day=@day+1 set @money=@day*1 end select fare=@moneycase函數(shù)獲取讀者年齡select r_name,age= case when year(getDate()) year(r_birthday)=20 then 39。小屁孩39。 when year(getDate()) year(r_birthday)between 20 and 40 then 39。成年39。 else 39。老年39。 endfrom tb_readersubstring()使用select b_id as 圖書編號, substring(b_name,1,2) as 截取后的字符串from tb_bookinfo where b_id = 39。6352439。datediff的使用select r_name,age= case when datediff(year,r_birthday,getDate()) =20 then 39。小屁孩39。 when datediff(year,r_birthday,getDate())between 20 and 40 then 39。成年39。 else 39。老年39。 endfrom tb_reader定義一個標量函數(shù)來查詢圖書類型為“數(shù)據(jù)結(jié)構(gòu)”的書本數(shù)量create function () returns intasbegin declare @y int select @y=count(*) from tb_bookinfo,tb_booktype where = group by having t_name=39。數(shù)據(jù)結(jié)構(gòu)39。 return @yendselect () ============================ SELECT查詢============================單表查詢select * from tb_reader 讀者查詢select * from tb_borrowandback 借還圖書查詢select * from tb_bookinfo 圖書信息查詢 多表查詢/*查詢讀者編號為YY1212借閱圖書的信息圖書編號、圖書名字、圖書作者、出版社、價格*/select ,b_name,b_author,b_translator,b_pubName,b_price from tb_reader,tb_borrowandback,tb_bookinfo where = and = and =39。YY121239。TOP 子句用于規(guī)定要返回的記錄的數(shù)目。對于擁有數(shù)千條記錄的大型表來說，TOP 子句是非常有用的/*圖書借閱排行榜，由ORDER BY與TOP配合能實現(xiàn)排行榜的*/select top 10 b_id,b_name,b_author,b_translator,b_pubName,b_price from tb_bookinfo order by b_borrownum/*讀者借閱排行榜，由ORDER BY與TOP配合能實現(xiàn)排行榜的*/select top 10 r_id,r_name from tb_reader order by r_num/*查詢價格在30之間圖書between...and...*/select b_id,b_name,b_author,b_translator,b_pubName,b_price from tb_bookinfo where b_price between and /*對圖書的模糊查詢like*/select b_id,b_name,b_author,b_translator,b_pubName,b_price from tb_bookinfo where b_name like 39。%數(shù)據(jù)%39。/*對多個讀者同時查詢其借閱情況In*/select ,b_name,b_author,b_translator,b_pubName,b_price from tb_reader,tb_borrowandback,tb_bookinfo where = and = and in (select r_id from tb_reader)/*修改圖書類型信息*/update tb_bookinfo set t_id = 2 where b_id = 39。7652439。update tb_bookinfo set t_id = 3 where b_id = 39。9865139。update tb_bookinfo set t_id = 4 where b_id = 39。4568239。update tb_bookinfo set t_id = 5 where b_id = 39。3564139。/*查詢所有圖書價格*/select ,b_name,b_price from tb_booktype,tb_bookinfo where = order by t_id/*計算同一個類型的書籍的價格 sum()和group by*/select t_name as 圖書類型名稱,sum(b_price) as 總價錢from tb_booktype,tb_bookinfo where = group by /*刪除圖書編號為的書籍delete*/delete from tb_bookinfo where b_id = 39。6352439。/*創(chuàng)建表的備份復件或者用于對記錄進行存檔備份借還信息表，讀者表以及圖書信息表select into*/SELECT ,*(自己把三個表里面的字段都寫好)*/ INTO tb_reader_tb_borrowandback_tb_bookinfo_Backup FROM tb_reader,tb_borrowandback,tb_bookinfo where = and =
電大資料相關推薦
鄂ICP備17016276號-1