搜尋
首頁資料庫mysql教程SQL Server 总结复习 (二)

SQL Server 总结复习,学习sqlserver的朋友可以参考下

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 定时执行; WAITFOR DELAY 延迟执行
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游标会隐式转换为其他游标类型
陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
MySQL的許可與其他數據庫系統相比如何?MySQL的許可與其他數據庫系統相比如何?Apr 25, 2025 am 12:26 AM

MySQL使用的是GPL許可證。 1)GPL許可證允許自由使用、修改和分發MySQL,但修改後的分發需遵循GPL。 2)商業許可證可避免公開修改,適合需要保密的商業應用。

您什麼時候選擇InnoDB而不是Myisam,反之亦然?您什麼時候選擇InnoDB而不是Myisam,反之亦然?Apr 25, 2025 am 12:22 AM

選擇InnoDB而不是MyISAM的情況包括:1)需要事務支持,2)高並發環境,3)需要高數據一致性;反之,選擇MyISAM的情況包括:1)主要是讀操作,2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用,如電商平台,而MyISAM適合讀密集型且無需事務的應用,如博客系統。

在MySQL中解釋外鍵的目的。在MySQL中解釋外鍵的目的。Apr 25, 2025 am 12:17 AM

在MySQL中,外鍵的作用是建立表與表之間的關係,確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性,使用時需注意性能優化和避免常見錯誤。

MySQL中有哪些不同類型的索引?MySQL中有哪些不同類型的索引?Apr 25, 2025 am 12:12 AM

MySQL中有四種主要的索引類型:B-Tree索引、哈希索引、全文索引和空間索引。 1.B-Tree索引適用於範圍查詢、排序和分組,適合在employees表的name列上創建。 2.哈希索引適用於等值查詢,適合在MEMORY存儲引擎的hash_table表的id列上創建。 3.全文索引用於文本搜索,適合在articles表的content列上創建。 4.空間索引用於地理空間查詢,適合在locations表的geom列上創建。

您如何在MySQL中創建索引?您如何在MySQL中創建索引?Apr 25, 2025 am 12:06 AM

toCreateAnIndexinMysql,usethecReateIndexStatement.1)forasingLecolumn,使用“ createIndexIdx_lastNameEnemployees(lastName); 2)foracompositeIndex,使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees(lastName,firstName,firstName);” 3)forauniqe instex,creationexexexexex,

MySQL與Sqlite有何不同?MySQL與Sqlite有何不同?Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要區別在於設計理念和使用場景:1.MySQL適用於大型應用和企業級解決方案,支持高性能和高並發;2.SQLite適合移動應用和桌面軟件,輕量級且易於嵌入。

MySQL中的索引是什麼?它們如何提高性能?MySQL中的索引是什麼?它們如何提高性能?Apr 24, 2025 am 12:09 AM

MySQL中的索引是數據庫表中一列或多列的有序結構,用於加速數據檢索。 1)索引通過減少掃描數據量提升查詢速度。 2)B-Tree索引利用平衡樹結構,適合範圍查詢和排序。 3)創建索引使用CREATEINDEX語句,如CREATEINDEXidx_customer_idONorders(customer_id)。 4)複合索引可優化多列查詢,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5)使用EXPLAIN分析查詢計劃,避

說明如何使用MySQL中的交易來確保數據一致性。說明如何使用MySQL中的交易來確保數據一致性。Apr 24, 2025 am 12:09 AM

在MySQL中使用事務可以確保數據一致性。 1)通過STARTTRANSACTION開始事務,執行SQL操作後用COMMIT提交或ROLLBACK回滾。 2)使用SAVEPOINT可以設置保存點,允許部分回滾。 3)性能優化建議包括縮短事務時間、避免大規模查詢和合理使用隔離級別。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

WebStorm Mac版

WebStorm Mac版

好用的JavaScript開發工具

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器