SQL语句大全集合.doc

《SQL语句大全集合.doc》由会员分享，可在线阅读，更多相关《SQL语句大全集合.doc（29页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流SQL语句大全集合【精品文档】第 29 页SELECT * FROM service_promotion WHERE gmt_modified = TO_DATE(2001-9-01,yyyy-mm-dd) AND gmt_modified 10) begin - print Dadadada! print Dadadada! end - else begin print XiaoXiao! print XiaoXiao! end- While循环控制declare i int;set i = 12;print ireturn;while (i 18)b

2、egin print i; set i = i + 1; if i 15 break;end;- CASE 分支判断select au_lname, state, 犹他州 from authors where state = UTselect au_lname, state, 密西西比州 from authors where state = MIselect au_lname, state, 肯塔基州 from authors where state = KSselect au_lname, state, case state when UT then 犹他州 when MI then 密西西

3、比州 when KS then 肯塔基州 when CA then 加利福利亚 else state endfrom authors(4.1)系统函数- 获取指定字符串中左起第一个字符的ASC码print ascii(ABCDEF)- 根据给定的ASC码获取相应的字符print char(65)- 获取给定字符串的长度print len(abcdef)- 大小写转换print lower(ABCDEF)print upper(abcdef)- 去空格print ltrim( abcd dfd df )print rtrim( abcd dfd df )- 求绝对值print abs(-12)-

4、 幂- 3 的 2 次方print power(3,2)print power(3,3)- 随机数- 0 - 1000 之间的随机数print rand() * 1000 - 获取圆周率print pi()- 获取系统时间print getdate()- 获取3天前的时间print dateadd(day, -3 , getdate()- 获取3天后的时间print dateadd(day, 3 , getdate()- 获取3年前的时间print dateadd(year, -3 , getdate()- 获取3年后的时间print dateadd(year, 3 , getdate()-

5、获取3月后的时间print dateadd(month, 3 , getdate()- 获取9小时后的时间print dateadd(hour, 9 , getdate()- 获取9分钟后的时间print dateadd(minute, 9 , getdate()- 获取指定时间之间相隔多少年print datediff(year, 2005-01-01, 2008-01-01)- 获取指定时间之间相隔多少月print datediff(month, 2005-01-01, 2008-01-01)- 获取指定时间之间相隔多少天print datediff(day, 2005-01-01, 20

6、08-01-01)- 字符串合并print abc + defprint abcderprint abc + 456print abc + 456- 类型转换print abc + convert(varchar(10), 456)select title_id, type, price from titles- 字符串连接必须保证类型一致(以下语句执行将会出错)- 类型转换select title_id + type + price from titles- 正确select title_id + type + convert(varchar(10), price) from titlesp

7、rint 123 + convert(varchar(3), 123)print 123 + 123print convert(varchar(12), 2005-09-01,110)- 获取指定时间的特定部分print year(getdate()print month(getdate()print day(getdate()- 获取指定时间的特定部分print datepart(year, getdate()print datepart(month, getdate()print datepart(day, getdate()print datepart(hh, getdate()prin

8、t datepart(mi, getdate()print datepart(ss, getdate()print datepart(ms, getdate()- 获取指定时间的间隔部分- 返回跨两个指定日期的日期和时间边界数print datediff(year, 2001-01-01, 2008-08-08)print datediff(month, 2001-01-01, 2008-08-08)print datediff(day, 2001-01-01, 2008-08-08)print datediff(hour, 2001-01-01, 2008-08-08)print dated

9、iff(mi, 2001-01-01, 2008-08-08)print datediff(ss, 2001-01-01, 2008-08-08)- 在向指定日期加上一段时间的基础上，返回新的 datetime 值print dateadd(year, 5, getdate()print dateadd(month, 5, getdate()print dateadd(day, 5, getdate()print dateadd(hour, 5, getdate()print dateadd(mi, 5, getdate()print dateadd(ss, 5, getdate()- 其他p

10、rint host_id()print host_name()print db_id(pubs)print db_name(5)- 利用系统函数作为默认值约束drop table tttcreate table tttstu_name varchar(12),stu_birthday datetime default (getdate()alter table tttadd constraint df_ttt_stu_birthday default (getdate() for stu_birthdayinsert into ttt values (ANiu, 2005-04-01)inse

11、rt into ttt values (ANiu, getdate()insert into ttt values (AZhu, default)sp_help tttselect * from ttt（4.2）自定义函数select title_idfrom titles where type = businessselect stuff(title_id,1,3,ABB), type from titles where type = businessselect count(title_id) from titles where type = businessselect title_id

12、 from titles where type = businessselect *,count(dbo.titleauthor.title_id)FROM dbo.authors INNER JOINdbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_idselect au_id, count(title_id)from titleauthorgroup by au_idSELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS 作品数量FROM dbo.author

13、s left outer JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_idGROUP BY dbo.authors.au_idorder by 作品数量- 自定义函数的引子（通过这个子查询来引入函数的作用）- 子查询- 统计每个作者的作品数- 将父查询中的作者编号传入子查询- 作为查询条件利用聚合函数count统计其作品数量select au_lname, (select count(title_id) from titleauthor as ta where ta.au_id = a.au_id ) as Ti

14、tleCountfrom authors as aorder by TitleCount- 是否可以定义一个函数- 将作者编号作为参数统计其作品数量并将其返回select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCount- 根据给定的作者编号获取其相应的作品数量create function GetTitleCountByAuID(au_id varchar(12)returns intbegin return (select count(title_id)

15、 from titleauthor where au_id = au_id)end - 利用函数来显示每个作者的作品数量create proc pro_CalTitleCountasselect au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCountgo- 执行存储过程execute pro_CalTitleCount- vb中函数定义格式function GetTitleCountByAuID(au_id as string) as integer GetTit

16、leCountByAuID = ?end function- SALES 作品销售信息select * from sales- 根据书籍编号查询其销售记录（其中，qty 表示销量）select * from sales where title_id = BU1032- 根据书籍编号统计其总销售量（其中，qty 表示销量）select sum(qty) from sales where title_id = BU1032- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）select title_id, sum(qty) from sales group

17、 by title_id- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量- 然后，将其应用到任何一条包含了书籍编号的查询语句中select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales- 定义一个函数根据书籍编号来计算其总销售量create function GetTotalSaleByTitleID(tid varchar(24)returns intbegin return(select sum(qty) from sales wher

18、e title_id = tid)end- 统计书籍销量的前10位- 其中，可以利用函数计算结果的别名作为排序子句的参照列select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales desc- 根据书籍编号计算其销量排名create function GetTheRankOfTitle(id varchar(20)returns intbegin return(select count(TotalSales) from titles

19、where ToalSales ( select TotalSales from titles where title_id=id)end- 根据书籍编号计算其销量排名select dbo.GetTheRankOfTitle(pc1035) from titlesselect count(title_id) + 1from titles where dbo.GetTotalSaleByTitleID(title_id) dbo.GetTotalSaleByTitleID(pc1035)- 删除函数drop function GetRankByTitleId- 根据书籍编号计算其销量排名crea

20、te function GetRankByTitleId(tid varchar(24)returns intbegin return (select count(title_id) + 1 from titles where dbo.GetTotalSaleByTitleID(title_id) dbo.GetTotalSaleByTitleID(tid)end- 在查询语句中利用函数统计每本书的总销量和总排名select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales, dbo.GetRankByTitl

21、eId(title_id) as TotalRankfrom titlesorder by TotalSales desc- 查看表结构sp_help titles- 查看存储过程的定义内容sp_helptext GetRankByTitleIdsp_helptext sp_helptext sp_helptext xp_cmdshell- ORDER DETAILS 订单详细信息select * from order details select * from order details where productid = 23- 根据产品编号在订单详细信息表中统计总销售量select su

22、m(quantity) from order details where productid = 23- 构造一个函数根据产品编号在订单详细信息表中统计总销售量create function GetTotalSaleByPID(Pid varchar(12)returns intbegin return(select sum(quantity) from order details where productid = Pid)endselect * from products- 在产品表中查询，统计每一样产品的总销量select productid, productname, dbo.GetT

23、otalSaleByPID(productid) from productsCREATE FUNCTION LargeOrderShippers ( FreightParm money )RETURNS OrderShipperTab TABLE ShipperID int, ShipperName nvarchar(80), OrderID int, ShippedDate datetime, Freight moneyASBEGIN INSERT OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedD

24、ate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightParm RETURNENDSELECT * FROM LargeOrderShippers( $500 )- 根据作者编号计算其所得版权费create function fun_RoyalTyper ( au_id id)returns intasbegin declare rt int select rt = sum(royaltyper) from titleauthor wh

25、ere au_id = au_id return (rt)endgoselect top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as 版权费 from authorsorder by dbo.fun_RoyalTyper(au_id) descgocreate function fun_MaxRoyalTyper_Au_id ()returns idasbegin declare au_id id select au_id = au_id from authors order by dbo.fun_RoyalTyper(au_id) r

26、eturn(au_id)endgoselect dbo.fun_MaxRoyalTyper_Au_id()goselect au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as 版权税 from authorswhere au_id = dbo.fun_MaxRoyalTyper_Au_id()go(5)高级查询select title_id, price from titles- 查找最高价格select max(price) from titles- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏select top 1 t

27、itle_id, price from titlesorder by price desc- 查找最贵书籍的价格(子查询)select title_id, price from titleswhere price = (select max(price) from titles)- 查询指定出版社出版的书(连接)select p.pub_name as 出版社, t.title as 书籍名称from publishers as p join titles as t on p.pub_id = t.pub_idwhere pub_name = New Moon Books- 查询指定出版社出版

28、的书(子查询)select title from titles where pub_id = (select pub_id from publishers where pub_name = New Moon Books)- 查询指定出版社出版的书(分开查询)select title from titles where pub_id = 0736select pub_id from publishers where pub_name = New Moon Books- 重点- 理解相关子查询的基础select * from titles where type = businessselect *

29、 from titles where type = business123select * from titles where 1 = 1 - 在订单表中寻找满足以下条件的订单编号以及相应的客户编号- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品- 然后将产品编号为23的产品订购量返回判断是否大于20USE northwindSELECT orderid, customeridFROM orders AS or1WHERE 20 SELECT MAX(advance) FROM publishers INNER JOIN titles ON titles.pub_id =

30、publishers.pub_id WHERE pub_name = Algodata InfosystemsSELECT title, advanceFROM titlesWHERE advance all SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id WHERE pub_name = Algodata Infosystems and advance is not nulldeclare i intset i = 12if i allselect price from

31、 titles where type = businessselect title_id, price from titleswhere price select max(price) from titles where type = businessselect title_id, price from titleswhere price anyselect price from titles where type = businessselect title_id, price from titleswhere price select min(price) from titles whe

32、re type = businessselect price from titles where type = business-if exists(select * from titles where type = 123) print ZZZZZelse print BBBBBif exists(select * from authors where city = Berkeley and state =UT) print Welcomeelse print Bye-Bye- 筛选出business以及trad_cook类型的书籍(联合查询)select title_id, type fr

33、om titles where type = businessunionselect title_id, type from titles where type = trad_cook- 统计business类型的书籍的总价(联合查询)select title, price from titles where type = businessunionselect 合计:, sum(price) from titles where type = business- 统计所有书籍的类型剔除重复(Distinct)select distinct type from titles- 作者记录的复制(Select Into)select * into au from authorsselect * from au- 查看数据表结构(Select Into并没有对数据表的约束进行复制)sp_help authorssp_help au- 分页(子查询的经典应用之一)- Jobs 职