--插入数据insertinto book(book_name,author_id,price,publisher)values('Windows 2000 Professional 看图速成','1','30.0','明耀工作室'),('3D Studio MAX实例精选','2','35.0','明耀工作室'),('Windows 2000 网络管理','1','45.0','唐唐出版社'),('MAthematica 4.0入门与提高','1','30.0','东东出版社'),('书籍','1','30.0','测试删除')
--使用公式 查询书籍数量的总金额select book.book_name,orderform.book_number,(book.price * orderform.book_number)'总金额:',book.book_id ,order_id from orderform,book
where book.book_id=order_id
--if begin语句declare@pricefloatset@price=50if(select price from book where book_name like'%网络%')>@pricebeginprint'这本书太贵了'endelsebeginprint'这边性价比还可以'end
--case语句select book_name 书名,price 价格,casewhen price >=45then'太贵了我买不起'when price >=35then'还可以考虑一下'else'挺便宜的,可以买一本'endas 想法
from book
--return语句--创建存储过程USE sl
GO
CREATEPROC pro @booknameCHAR(50)ASIF(SELECT price FROM book WHERE book_name LIKE@bookname)>=50RETURN1ELSERETURN2--使用存储过程DECLARE@return_valueintEXEC@return_value=pro '%网络管理%'IF@return_value=1PRINT'这本书太贵了!'ELSEPRINT'这本书还可以 考虑考虑!'
GO
--自定义函数CREATEFUNCTION lf(@higthDECIMAL(4,1),@widthdecimal(4,1),@heightDECIMAL(4,1))RETURNSDECIMAL(12,4)ASBEGINRETURN(@higth*@width*@height)END
GO
PRINT'立方:'+CONVERT(CHAR(25),dbo.lf(10,2,5))
--自定义函数返回结果集,存储过程则不能。USE sl
go
CREATEFUNCTION selectbook(@bookpriceFLOAT)RETURNS@bookinfoTABLE(
book_name CHAR(25),
au_name CHAR(10),
price FLOAT,
pub CHAR(50))ASBEGININSERT@bookinfoSELECT book.book_name,authors.author_name,book.price,book.publisher from book,authors
WHERE book.author_id = authors.author_id AND book.price >@bookpricereturnEND
GO
SELECT*FROM dbo.selectbook(30)