SQL Server 总结复习 (二)
来源:asp之家 发布时间:2012-10-07 10:35:00
1. 排名函数与PARTITION BY
--所有数据
SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '语文'
-------------------------------------------
--ROW_NUMBER() 的使用 生成列从1开始依次增加
-------------------------------------------
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '语文'
--也可以在后面再加一个order by,则表示前面生成后的全部列又被以最后的列重新排列(排名列值不变)
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '语文' ORDER BY a.Id
--要在分组统计后使用排名函数,则先进行分组,用cte或嵌套查询表整出结果集,再用row_number函数处理
WITH b AS
(
SELECT stuid, SUM(score) AS score FROM ScoreTB GROUP BY stuid
)
SELECT * ,ROW_NUMBER() OVER (ORDER BY b.score desc) AS rownumer
FROM dbo.student AS a INNER JOIN b ON a.id = b.stuid
----------------------------------------------------------------------------
--RANK() 用法与ROW_NUMER函数想同,只是在出现order by同级时,排名会设置成一样,而下一个会根据之前的记录数生成序号
--例如前面三个是一样的,那么都是1,下一个则是4,示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--DENSE_RANK() 密集排名 用法与ROW_NUMER、RANK函数相同,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续
--例如前面三个是一样的,那么都是1,下一个则是2,示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。
--为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。
--它有一个参数,用来指定桶数,例如
----------------------------------------------------------------------------
SELECT ntile(2) OVER (ORDER BY B.SCORE DESC) AS GROUPID ,A.NAME, ISNULL(B.SCORE,0) SCORE, a.Id
FROM dbo.student AS a LEFT JOIN dbo.ScoreTB AS b ON a.Id = b.stuid AND scorename = '语文'
--------------------------------------------------------------------------
--PARTITION BY 类似于向排名函数应用一个group by,分组后对每一个组单独排名
--------------------------------------------------------------------------
--统计各个学科的排名依次为:
SELECT RANK() OVER (PARTITION BY b.scorename ORDER BY B.SCORE DESC) AS ROWNUMBER,b.scorename,
b.score, A.NAME, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid ORDER BY SCORENAME
2. TOP 新用法
DECLARE @num INT = 101
SELECT TOP (@num) * FROM Student ORDER BY Id --必须用括号括起来
SELECT TOP (@num) percent * FROM Student ORDER BY Id --只接受float并且1-100之间的数,如果传入其他则会报错
3. group by all 字段 / group by 字段
前者有点像left join ,right join的感觉,两者的主要区别体现在有where条件被过滤的聚合函数,会重新抓取出来放入查询的数据表中,只是聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。
当然从效率上来说,后者优于前者,就像inner join 优于left join一样
4. count(*)/count(0) 与 count(字段)
如果查询出来的字段中没有NULL值,那么俩种查询条件无任何区别,如果有NULL,后者统计出来的记录则是 总记录数 - NULL记录数
从性能上来说,前者高于后者,因为后者会逐行扫描字段中是否有NULL值,有NULL则不加以统计,减少了逻辑读的开销,从而性能达到提升
5. top n With ties 的用法
详见 http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
举个例子
select top 1 with ties * from student order by score desc
等价于
select * from student where score=(select top 1 score from student order by score desc)
6. Apply运算符
View Code
--准备数据
CREATE TABLE [dbo].[Student](
[Id] [int] NULL,
[Name] [varchar](50) NULL
)
go
INSERT INTO dbo.Student VALUES (1, '张三')
INSERT INTO dbo.Student VALUES (2, '李斯')
INSERT INTO dbo.Student VALUES (3, '王五')
INSERT INTO dbo.Student VALUES (4, '神人')
go
CREATE TABLE [dbo].[scoretb](
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
go
INSERT INTO [scoretb] VALUES (1,'语文',22)
INSERT INTO [scoretb] VALUES (1,'数学',32)
INSERT INTO [scoretb] VALUES (1,'外语',42)
INSERT INTO [scoretb] VALUES (2,'语文',52)
INSERT INTO [scoretb] VALUES (2,'数学',62)
INSERT INTO [scoretb] VALUES (2,'外语',72)
INSERT INTO [scoretb] VALUES (3,'语文',82)
INSERT INTO [scoretb] VALUES (3,'数学',92)
INSERT INTO [scoretb] VALUES (3,'外语',72)
--创建表值函数
CREATE FUNCTION [dbo].[fGetScore](@stuid int)
RETURNS @score TABLE
(
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
as
BEGIN
INSERT INTO @score
SELECT stuid,scorename,score FROM dbo.scoretb WHERE stuId = @stuid
RETURN;
END
GO
--开始使用
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
CROSS APPLY [dbo].[fGetScore](A.Id) B --相当于inner join效果
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
OUTER APPLY [dbo].[fGetScore](A.Id) B --相当于left join效果
--而不能这样使用
--SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN [dbo].[fGetScore](A.Id) B ON A.Id = B.stuid
-- SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN (SELECT * FROM [dbo].[fGetScore](A.Id)) B ON A.Id = B.stuid
7. INTERSECT和EXCEPT运算符
EXCEPT 只包含excpet关键字左边而且右边的结果集中不存在的那些行 INTERSECT 只包含两个结果集中都存在的那些行
往往EXISTS关键字可以代替上面的关键字,并且从性能中可以看到比他们更好,但EXCEPT/INTERSECT更便于阅读和直观。还是建议从性能更优入手。
8. 索引提高查询效率的原理
索引与EXISTS运算符在处理方式上很像,它们都可以在找到匹配值后立即退出查询运行,从而提高了查询性能
9. 表变量与临时表
主要区别: 1表变量不写日志,没有统计信息,频繁更改不会造成存储过程重新编译,不能建索引和统计信息,但是可以建立主键,变通实现索引查找,表变量不只是在内存中操作,数据量大的情况也会写tempdb,即物理磁盘的IO操作。 2.事务回滚对表变量无效(原因没有统计信息)
一般来说,数据量大,临时结果集需要和其他表二次关联用临时表 数据量小,单独操作临时结果集用表变量
10. 脚本和批处理
Go不是一条T-SQL命令,他只能被编译工具Management Studio, SQLCMD识别,如果用第三方工具,不一定支持GO命令。例如ADO.NET,ADO。
11. SQLCMD的运用
SQLCMD -Usa -Psa -Q "SELECT * FROM TESTDB.dbo.mytable"
SQLCMD -Usa -Psa -i testsql.sql 运行文件里的SQL语句
12. EXEC 使用说明
在执行过EXEC之后,可以使用类似@@ROWCOUNT这样的变量查看影响行数;不能在EXEC的参数中,针对EXEC字符串运行函数,例如cast(XX AS VARCHAR),对于EXEC的参数,只能用字符串相加,或者是整体的字符串。
13. WAITFOR 的含义
WAITFOR TIME <'TIME'> 定时执行; WAITFOR DELAY <'TIME'> 延迟执行
14. 存储过程 总结
1)用TRY/CATCH 替代 @@ERROR这种更科学,其一@@ERROR没有TRA/CATCH直观,其二遇到错误级别在11-19的错误,错误会使运行直接中断,导致@@ERROR判断错误与否无效。
2)使用RAISERROR 抛错
WITH LOG,当严重级别大于等于19时,需要使用这个选项
WITH SETERROR,使其重写@@ERROR值,方便外部调用
WITH NOWAIT 立刻将错误通知给客户端
15. 游标的复习
游标主要部分包括:1)声明 2)打开 3)使用或导航 4)关闭 5)释放
嵌套使用游标示例
DECLARE BillMsgCursor CURSOR FOR
SELECT TypeNo,TabDetailName FROM dbo.BillType
OPEN BillMsgCursor
DECLARE @TypeNo CHAR(5)
DECLARE @DetailName VARCHAR(50)
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DataFieldName VARCHAR(50)
DECLARE ColumnName CURSOR FOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID(@DetailName)
OPEN ColumnName
FETCH NEXT FROM ColumnName INTO @DataFieldName
PRINT '单据编号:' + @TypeNo
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ListDetailDataFiled.Add('''+@DataFieldName+''');'
FETCH NEXT FROM ColumnName INTO @DataFieldName
END
CLOSE ColumnName
DEALLOCATE ColumnName
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
END
CLOSE BillMsgCursor
DEALLOCATE BillMsgCursor
@@fetch_status值的意义:0 FETCH 语句成功;-1 FETCH 语句失败或此行不在结果集中;-2 被提取的行不存在
FETCH [NEXT/PRIOR/FIRST/LAST] FROM ... INTO 针对游标为SCROLL类型的
16. 游标的分类
1)静态游标(static):相当于临时表,会保存在tempdb里的私有表中,犹如快照表复制一份
a.一旦创建了游标,它就与实际记录相分离并不再维持任何锁
b.游标就是独立的,不再以任何方式与原始数据相关联
2)键集驱动的游标(keyset):需要在一定程度上感知对数据的修改,但不必了解最新发生的所有插入
a.表必须具有唯一索引
b.只有键集在tempdb中,而非整个数据集,对整个服务器性能产生有利的影响
c.能感知到对己是键集一部分的行所做的修改(改删),不能感知新增
3)动态游标(DYNAMIC)
a.完全动态,非常敏感,对底层数据做的所有事情都会影响,性能当然也是最差的
b.它们会带来额外的并发性问题
c.每发出一次FETCH,都要重建游标
d.可允许运行没有唯一索引的表中,但弊端会造成SQLSERVER无法追踪它在游标的位置造成死循环,应避免这样使用
4)快进游标(FAST_FORWARD)
在许多情况下,FAST_FORWARD游标会隐式转换为其他游标类型
猜你喜欢
- 本文介绍了linux下如何备份与恢复mysql数据库。数据库备份是非常重要的。如果定期做好备份,这样就可以在发生系统崩溃时恢复数据到最后一次
- MYSQL数据库安装完成后,默认最大连接数是100,一般流量稍微大一点的论坛或网站这个连接数是远远不够的,增加默认MYSQL连接数的方法有两
- Firefox 3 有一个很让人讨厌的bug:基于某种目的,在表单提交时 disable 掉提交按钮,通过后退键回到这个页面后,这个提交按钮
- 很久没有更新blog了,这段时间实在是发生了很多的事,累身累心。但还是有很多想做的事,比如更新merceCSS、把一直以来所总结的有关模块化
- 题目:用 JavaScript 代码实现空位补零,比如 pad(12, 3) => 012实现一:/* 平淡无奇法 */functio
- 在数据库开发方面,通过单表所表现的实现,有时候需要组合查询来找到我们需要的记录集,这时候我们就会用到连接查询。连接查询主要包括以下几个方面:
- 上次介绍了Access 导入 MSSQL 2000/2005 数据库工具,不过,在使用这个工具时还是有一些地方需要注意的,我把整个导入过程记
- 一直很想做这个效果,原理是监听鼠标滚轮事件;可将此效果继续发散到其他应用上,如图片缩放,页面缩放等。演示:<!DOCTYPE html
- 我的同事Fara给大家介绍了戴尔网站首页的改版设计,这里我还想和大家介绍一下戴尔是如何从网站用户使用体验的角度进行设计,让大家进一步了解戴尔
- 如何定义记录集打开的游标类型和锁定类型?我们知道,打开记录集时,可以定义记录集打开的游标类型和锁定类型。在adovbs.inc文件中就定义了
- HTML 的空白符处理规则HTML 中的“空白符”包括空格 (space)、制表符 (tab)、换行符 (CR/LF) 三种。我们知道,在默
- MySQL插件式存储引擎是MySQL数据库服务器中的组件,负责为数据库执行实际的数据I/O操作,并能允许和强制执行面向特殊应用需求的特定特性
- 我们在建立一个大型网站的时候,往往会包括很多相同的页面框架模式,甚至一些细节元素都是相同的。但令人困扰
- 网上有许关于固定表格的标题行的文章,但是既要固定标题行又要固定标题列的却几乎没有。现我写下如下代码以供大家参考:<html> &
- 概述做日志分析工作的经常需要跟成千上万的日志条目打交道,为了在庞大的数据量中找到特定模式的数据,常常需要编写很多复杂的正则表达式。例如枚举出
- 不加(0)的用法:set rs=conn.execute(sql)'将这个结果赋给rs这时要读取这个记录集第一个字段的数据就用rs(
- 在asp编程中,我们常常使用trim(rtrim ,ltrim)函数去掉一些数据的开头和结尾的空格,笔者最近写了一个asp聊天室,看到下面的
- 看着这个题目可能大家会有点陌生,不知道是什么,说的直白一点,RDFa就是让你的页面更好的被机器所理解,而不简简单单是展示你的页面,下面就关于
- Update Scanner这个Firefox附加软件也是一种很好的选择。Update Scanner可以同时跟踪多个网页,并为不同的网页设
- 前几天写了一个ajax的,总感觉代码比较多,今天晚上又得写了一下,感觉代码还是比较多,但还好的是,比较通用。谁有办法优化一下当然好。&nbs