


版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、(1) 批处理1(2) 变量 3(3) 逻辑控制 5( 4)函数 7(4.1)系统函数 7(4.2)自定义函数 12(5) 高级查询 22(6) 存储过程 35(7) 游标 35(8) 触发器 50SQL Server 数据库的高级操作(1) 批处理(2) 变量(3) 逻辑控制(4) 函数(5) 高级查询*/(1) 批处理将多条 SQL 语句作为一个整体去编译,生成一个执行计划,然后,执行!理解批处理的关键在于 编译 ,对于由多条语句组成的一个批处理,如果在编译时,其中,有一条出现语法错误,将会导致编译失败!create table t(a int,b int- 注释- 如果多行注释中包含了批
2、处理的标识符 go- 在编译的过程中代码将会被 go 分割成多个部分来分批编译- 多行注释的标记将会被分隔而导致编译出错- 以下几条语句是三个非常经典的批处理- 你猜一下会添加几条记录!/*insert into t values (1,1)go*/insert into t values (2,2)go/*insert into t values (3,3)*/go- 查询看添加了几条记录select * from ttruncate table t(2) 变量- 全局变量SQL Server 中全局变量由系统定义、系统维护,用户一般仅可对其进行读取!- 查看 SQL Server 版本pr
3、int version- 服务器名称print servername- 系统错误编号insert into t values (a,a)print errorinsert into t values (a,a)if error = 245print Error- SQL Server 版本的语言信息print LANGUAGE- 一周的第一天从星期几算起print datefirst- CPU 执行命令所耗费时间的累加print cpu_busy- 获取最近添加的标识列的值create table tt(a int identity(3, 10),b int)insert into tt (b
4、) values (1)print identityselect * from tt- 局部变量局部变量由用户定义,仅可在同一个批处理中调用和访问declare intAge tinyintset intAge = 12print intAgedeclare strName varchar(12)select strName = stateprint strName select au_lname, strName from authors(3) 逻辑控制- IF 条件判断 declare i int set i = 12 if (i 10) begin - print Dadadada! p
5、rint Dadadada! end - else begin print XiaoXiao! print XiaoXiao! end- While 循环控制 declare i int; set i = 12; print i return;while (i 18) beginprint i;set i = i + 1;if i 15break;end;- CASE 分支判断select au_lname, state, 犹他州 from authors where state = UTselect au_lname, state, 密西西比州 from authors where stat
6、e = MIselect au_lname, state, 肯塔基州 from authors where state = KSselect au_lname, state,case statewhen UT then 犹他州 when MI then 密西西比州 when KS then 肯塔基州 when CA then 加利福利亚 else stateend from authors(4) 函数(4.1)系统函数- 获取指定字符串中左起第一个字符的ASC 码print ascii(ABCDEF)- 根据给定的 ASC 码获取相应的字符print char(65)- 获取给定字符串的长度p
7、rint len(abcdef)- 大小写转换print lower(ABCDEF)print upper(abcdef)- 去空格print ltrim(abcd dfd df )print rtrim(abcd dfd df )- 求绝对值print abs(-12)- 幂- 3 的 2 次方print power(3,2)print power(3,3)随机数- 0 - 1000 之间的随机数print rand() * 1000- 获取圆周率print pi()- 获取系统时间print getdate()- 获取 3 天前的时间print dateadd(day, -3 , getd
8、ate()- 获取 3 天后的时间print dateadd(day, 3 , getdate()- 获取 3 年前的时间print dateadd(year, -3 , getdate()- 获取 3 年后的时间print dateadd(year, 3 , getdate()- 获取 3 月后的时间print dateadd(month, 3 , getdate()- 获取 9 小时后的时间print dateadd(hour, 9 , getdate()- 获取 9 分钟后的时间print dateadd(minute, 9 , getdate()- 获取指定时间之间相隔多少年print
9、 datediff(year, 2005-01-01, 2008-01-01)- 获取指定时间之间相隔多少月print datediff(month, 2005-01-01, 2008-01-01)- 获取指定时间之间相隔多少天print datediff(day, 2005-01-01, 2008-01-01)- 字符串合并print abc + defprint abcderprint abc + 456print abc + 456- 类型转换print abc + convert(varchar(10), 456)select title_id, type, price from ti
10、tles- 字符串连接必须保证类型一致(以下语句执行将会出错 )- 类型转换select title_id + type + price from titles- 正确select title_id + type + convert(varchar(10), price) from titles print 123 + convert(varchar(3), 123) print 123 + 123print convert(varchar(12), 2005-09-01,110)- 获取指定时间的特定部分print year(getdate()print month(getdate()pri
11、nt day(getdate()- 获取指定时间的特定部分print datepart(year, getdate()print datepart(month, getdate()print datepart(day, getdate()print datepart(hh, getdate()print datepart(mi, getdate()print datepart(ss, getdate()print datepart(ms, getdate()- 获取指定时间的间隔部分- 返回跨两个指定日期的日期和时间边界数print datediff(year, 2001-01-01, 200
12、8-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 datediff(mi, 2001-01-01, 2008-08-08)print datediff(ss, 2001-01-01, 2008-08-08)- 在向指定日期加上一段时间的基础上,返回新的 datetime 值print dateadd(year, 5, getdate()print da
13、teadd(month, 5, getdate()print dateadd(day, 5, getdate()print dateadd(hour, 5, getdate()print dateadd(mi, 5, getdate()print dateadd(ss, 5, getdate()- 其他print host_id()print host_name()print db_id(pubs)print db_name(5)- 利用系统函数作为默认值约束drop table tttcreate table tttstu_name varchar(12), stu_birthday dat
14、etime default (getdate()alter table tttadd constraint df_ttt_stu_birthday default(getdate() for stu_birthdayinsert into ttt values (ANiu, 2005-04-01)insert into ttt values (ANiu, getdate()insert into ttt values (AZhu, default)sp_help tttselect * from ttt(4.2)自定义函数select title_idfrom titleswhere type
15、 = businessselect stuff(title_id,1,3,ABB), typefrom titles where type = businessselect count(title_id) from titles where type = business select title_id from titles where type = business select *,count(dbo.titleauthor.title_id)FROM dbo.authors INNER JOINdbo.titleauthor ON dbo.authors.au_id = dbo.tit
16、leauthor.au_idselect au_id, count(title_id)from titleauthor作品数量group by au_idSELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS FROM dbo.authors left outer JOINdbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_idGROUP BY dbo.authors.au_idorder by 作品数量 - 自定义函数的引子(通过这个子查询来引入函数的作用)- 子
17、查询- 统计每个作者的作品数- 将父查询中的作者编号传入子查询- 作为查询条件利用聚合函数 count 统计其作品数量select au_lname,(select count(title_id) from titleauthor as ta where ta.au_id = a.au_id) as TitleCountfrom authors as a order by TitleCount- 是否可以定义一个函数- 将作者编号作为参数统计其作品数量并将其返回select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCoun
18、t from authorsorder by TitleCount- 根据给定的作者编号获取其相应的作品数量create function GetTitleCountByAuID(au_id varchar(12) returns intbeginreturn (select count(title_id)from titleauthor end- 利用函数来显示每个作者的作品数量create proc pro_CalTitleCountasselect au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from aut
19、horsorder by TitleCountgo- 执行存储过程execute pro_CalTitleCount- vb 中函数定义格式function GetTitleCountByAuID(au_id as string) as integerGetTitleCountByAuID = ?end function- SALES 作品销售信息select * from sales- 根据书籍编号查询其销售记录(其中, qty 表示销量)select * from sales where title_id = BU1032select sum(qty) from sales where t
20、itle_id = BU1032- 利用分组语句( group by ),根据书籍编号统计每本书总销售量(其中, qty 表示销量) select title_id, sum(qty) from sales group by title_id- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量- 然后,将其应用到任何一条包含了书籍编号的查询语句中select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales- 定义一个函数根据书籍编号来计算其总销售
21、量create function GetTotalSaleByTitleID(tid varchar(24)returns intbeginreturn(select sum(qty) from sales where title_id = tid)end- 统计书籍销量的前 10 位- 其中,可以利用函数计算结果的别名作为排序子句的参照列select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titles order by TotalSales desc- 根据书籍编号计算其销量
22、排名create function GetTheRankOfTitle(id varchar(20)returns intbeginreturn(select count(TotalSales)from titleswhere ToalSales (select TotalSalesfrom titleswhere title_id=id )end- 根据书籍编号计算其销量排名select dbo.GetTheRankOfTitle(pc1035) from titlesselect count(title_id) + 1from titleswhere dbo.GetTotalSaleByT
23、itleID(title_id) dbo.GetTotalSaleByTitleID(pc1035)- 删除函数drop function GetRankByTitleId- 根据书籍编号计算其销量排名create function GetRankByTitleId(tid varchar(24)returns intbeginreturn (select count(title_id) + 1from titleswhere dbo.GetTotalSaleByTitleID(title_id) dbo.GetTotalSaleByTitleID(tid) end- 在查询语句中利用函数统计
24、每本书的总销量和总排名select title_id, title,dbo.GetTotalSaleByTitleID(title_id) as TotalSales, dbo.GetRankByTitleId(title_id) as TotalRank from titlesorder by TotalSales desc- 查看表结构sp_help titles- 查看存储过程的定义内容sp_helptext GetRankByTitleId sp_helptext sp_helptext sp_helptext xp_cmdshell- ORDER DETAILS 订单详细信息sele
25、ct * from order detailsselect * from order details where productid = 23- 根据产品编号在订单详细信息表中统计总销售量select sum(quantity) from order details where productid = 23- 构造一个函数根据产品编号在订单详细信息表中统计总销售量create function GetTotalSaleByPID(Pid varchar(12)returns intbeginreturn(select sum(quantity) from order details where
26、 productid = Pid) end select * from products- 在产品表中查询,统计每一样产品的总销量select productid, productname, dbo.GetTotalSaleByPID(productid) from productsCREATE FUNCTION LargeOrderShippers ( FreightParm money )RETURNS OrderShipperTab TABLEShipperIDint,ShipperNamenvarchar(80),OrderIDint,ShippedDatedatetime,Freig
27、htmoney)ASBEGININSERT OrderShipperTabSELECT S.ShipperID, S.CompanyName,O.OrderID, O.ShippedDate, O.FreightFROM Shippers AS S INNER JOIN Orders AS OON S.ShipperID = O.ShipViaWHERE O.Freight FreightParmRETURNENDSELECT * FROM LargeOrderShippers( $500 )- 根据作者编号计算其所得版权费create function fun_RoyalTyper ( au
28、_id id)returns int asbegindeclare rt intselect rt = sum(royaltyper) from titleauthor where au_id = au_id return (rt)endgo版权费select 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 idasbeginde
29、clare au_id idselect au_id = au_idfrom authors return(au_id)版权税此方法将会遗漏end goselect 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
30、titles- 查找最贵书籍的价格 (排序 ) ,如果存在多本价格最贵的书,select top 1 title_id, pricefrom titles order by price desc- 查找最贵书籍的价格 ( 子查询 ) select title_id, pricefrom 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.
31、pub_id = t.pub_id where pub_name = New Moon Books- 查询指定出版社出版的书 ( 子查询 )select titlefrom titleswhere pub_id = (select pub_idfrom publisherswhere pub_name =New Moon Books)- 查询指定出版社出版的书 ( 分开查询 )select title from titles where pub_id = 0736select pub_idfrom publishers- 重点- 理解相关子查询的基础select * from titles w
32、here type = businessselect * 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
33、 JOIN titles ON titles.pub_id = publishers.pub_idWHERE pub_name = Algodata Infosystems )SELECT title, advanceFROM titlesWHERE advance all(SELECT advanceFROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_idWHERE pub_name = Algodata Infosystemsand advance is not null)declare i int set
34、 i = 12 if i all(select price from titles where type = business)select title_id, price from titleswhere price (select max(price) from titles where type = business)select title_id, price from titleswhere price any(select price from titles where type = business)select title_id, price from titleswhere
35、price (select min(price) from titles where type = business )if exists(select * from titles where type = 123) print ZZZZZelseprint BBBBBif exists(select * from authorswhere city = Berkeley and state =UT)print Welcomeelseprint Bye-Bye- 筛选出 business 以及 trad_cook 类型的书籍 ( 联合查询 )select title_id, type from
36、 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 Into) select * into
37、au from authorsselect * from au- 查看数据表结构 (Select Into 并没有对数据表的约束进行复制 ) sp_help authorssp_help au- 分页 (子查询的经典应用之一 )- Jobs 职务信息表( pubs 数据库)- 在实际项目中,显示职务信息时,而职务信息量非常庞大,可能需要将其分为若干个页面来显示- 比如:每页显示 4 条记录, 那么,第一页将显示 1 ,2,3,4,第二页将显示 5,6 ,7,8- 显示所有信息SELECT * FROM jobs- 显示前 4 信息select top 4 * from jobs- 显示前 8
38、信息- 显示前 12 信息select top 12 * from jobs- 寻找规律,每一页的信息源于前(页面大小 * 页码)条信息的反序结果的前 页面大小 条记 录- 比如:第二页就是前 8 条记录的反序结果的前 4 条select top 4 * from (select top 8 * from jobs) as ttorder by job_id desc - 当然,对于期望按升序显示查询结果的要求可以对查询结果进行再次排序select * from (select top 4 *from (select top 8 * from jobs) as ttorder by job_i
39、d desc) as sttorder by job_id- SQL 命令中不支持在 select 的查询列表中直接使用局部变量- 比如: select top PageSize * from jobssp_executesql 来执行- 那么,可以考虑对 sql 命令进行拼装,然后,利用系统存储过程exec sp_executesql NSelect * from jobs- 存储过程的实现- 其中, CurrentPageSize 用于确定最后一页的页面大小create proc proGetJobsByPageCurrentPageSize int,PageSize int,Curren
40、tPage intasDeclare strSql nvarchar(400)set strSql = select * from(select top + convert(nvarchar(4), CurrentPageSize) + *from (select top + convert(nvarchar(4),(PageSize * CurrentPage) + * from jobs) as ttorder by job_id desc) as sttorder by job_idexec sp_executesql strSqlgo- 测试(6) 存储过程- 扩展存储过程- 查询系统
41、目录下文件信息xp_cmdshell dir *.* - 启动 Windows 系统服务 xp_cmdshell net start iisadmin(7) 游标- 游标的五个基本操作步骤:- 声明declare cur_titles cursorfor select title, price from titles- 打开open cur_titles- 提取fetch cur_titles fetch next from cur_titles- 关闭close cur_titles- 释放deallocate cur_titles- 利用游标遍历所有书籍信息,通过冒泡排序法进行比较,找出最
42、高价格的书- 这一段为批处理版- 与批处理版相比,存储过程版更方便调试以及代码的重用- 声明declare cur_titles cursorfor select title, price from titles- 打开open cur_titlesdeclare title varchar(80)declare price numeric(9,4)- 提取fetch cur_titles into title, pricefetch cur_titles into title_temp, price_tempwhile fetch_status = 0beginif price price_
43、tempbeginset price = price_tempset title = title_tempendfetch cur_titles into title_temp, price_tempend- 关闭close cur_titles- 释放deallocate cur_titles- 显示处理结果go- 定义一个存储过程- 利用游标遍历所有书籍信息,通过冒泡排序法进行比较,找出最高价格的书(游标具体应用 的经典)- 这段存储过程的实现代码相对下面的实现方式略有不同- 代码重复,但是思路更清晰create procedure pro_GetMaxTitleas- 声明declare
44、 cur_titles cursorfor select title, price from titles- 打开open cur_titles- 存储最贵的书籍信息declare title varchar(80)declare price numeric(9,4)- 存储从游标中提取出来的书籍的信息提取fetch cur_titles into title, price- 判断是否存在书籍信息if fetch_status 0beginprint 没有书籍信息 !- 关闭close cur_titles- 释放deallocate cur_titles- 结束存储过程returnend f
45、etch cur_titles into title_temp, price_temp- 判断是否只存在一本书if fetch_status 0begin- 显示处理结果close cur_titles - 释放deallocate cur_titles- 结束存储过程returnendwhile fetch_status = 0beginif price price_tempbeginset price = price_tempset title = title_tempendfetch cur_titles into title_temp, price_tempend- 显示处理结果close cur_titles- 释放deallocate cur_titles go- 定义一个存储过程- 利用游标遍历所有书籍信息,通
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 单位物业维修合同标准文本
- 出租别墅入股合同范例
- 会计试用合同标准文本
- 单位临时工转包合同范本
- 供水安装合同标准文本标准
- 公路县道合同范例
- 加工面店转让合同标准文本
- 修路回收物资合同范例
- 保密基础知识培训
- led拼接屏合同标准文本
- 2025年江苏无锡市江阴市新国联集团有限公司子公司招聘笔试参考题库附带答案详解
- Unit2 Travelling Around Reading for Writing 说课稿-2024-2025学年高中英语人教版(2019)必修第一册
- 《新能源汽车滚装运输安全技术指南》2022
- 宗祠祭祖祭文范文
- 年产8.5万吨钙基高分子复合材料项目可行性研究报告模板-立项备案
- 美育(威海职业学院)知到智慧树答案
- rules in the zoo动物园里的规则作文
- 《森林防火安全教育》主题班会 课件
- 人工喂养课件教学课件
- 2024年第三届浙江技能大赛(信息网络布线赛项)理论考试题库(含答案)
- 2024年同等学力申硕英语考试真题
评论
0/150
提交评论