【正文】 sert into tb_reader values(39。,39。,39。,2,39。,39。,39。,39。,39。,39。,39。,39。,1,4) insert into tb_reader values(39。,39。,39。,2,39。,39。,39。,39。,39。,39。,39。,39。,1,4) ============================ 借還圖書信息表 ============================ CREATE TABLE tb_borrowandback ( w_id int IDENTITY(1,1) primary key not null, 編號 r_id varchar(30) foreign key (r_id) references tb_reader(r_id) not null, 讀者編號 b_id varchar(30) foreign key (b_id) references tb_bookinfo(b_id) not null, 圖書編號 w_borrowTime datetime not null, 借出時間 w_ygbackTime datetime null, 應當歸還時間 w_sjbackTime datetime null, 實 際歸還時間 w_borrowoper varchar(50) foreign key (w_borrowoper) references tb_admin(a_id) null, 借出操作員 w_backoper varchar(50) foreign key (w_backoper) references tb_admin(a_id) null, 9 歸還操作員 w_isback bit null 是否歸還 ) insert into tb_borrowandback values(39。,39。,39。,39。,39。,39。,39。,39。) insert into tb_borrowandback values(39。,39。,39。,39。,39。,39。,39。,39。) insert into tb_borrowandback values(39。,39。,39。,39。,39。,39。,39。,39。) insert into tb_borrowandback values(39。,39。,39。,39。,39。,39。,39。,39。) insert into tb_borrowandback values(39。,39。,39。,39。,39。,39。,39。,39。) ============================ 流程控制 ============================ while根據(jù)超過日期計算應繳罰金 declare money money declare day int set day =0 while daydatediff(day,39。,getdate()) begin set day=day+1 set money=day*1 end select fare=money case函數(shù)獲取讀者年齡 select r_name,age= case when year(getDate()) year(r_birthday)=20 then 39。 when year(getDate()) year(r_birthday)between 20 and 40 then 39。 else 39。 end from tb_reader substring()使用 select b_id as 圖書編號 , substring(b_name,1,2) as 截取后的字符串 from tb_bookinfo where b_id = 39。 10 datediff的使用 select r_name,age= case when datediff(year,r_birthday,getDate()) =20 then 39。 when datediff(year,r_birthday,getDate())between 20 and 40 then 39。 else 39。 end from tb_reader 定義一個標量函數(shù)來查詢圖書類型為 “數(shù)據(jù)結(jié)構(gòu) ”的書本數(shù)量 create function () returns int as begin declare y int select y=count(*) from tb_bookinfo,tb_booktype where = group by having t_name=39。 return y end select () ============================ 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。 TOP 子句用于規(guī)定要返回的記錄的數(shù)目。%數(shù)據(jù) %39。7652439。9865139。4568239。3564139。6352