찾다
데이터 베이스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 13, 2025 am 12:18 AM

데이터베이스 및 프로그래밍에서 MySQL의 위치는 매우 중요합니다. 다양한 응용 프로그램 시나리오에서 널리 사용되는 오픈 소스 관계형 데이터베이스 관리 시스템입니다. 1) MySQL은 웹, 모바일 및 엔터프라이즈 레벨 시스템을 지원하는 효율적인 데이터 저장, 조직 및 검색 기능을 제공합니다. 2) 클라이언트 서버 아키텍처를 사용하고 여러 스토리지 엔진 및 인덱스 최적화를 지원합니다. 3) 기본 사용에는 테이블 작성 및 데이터 삽입이 포함되며 고급 사용에는 다중 테이블 조인 및 복잡한 쿼리가 포함됩니다. 4) SQL 구문 오류 및 성능 문제와 같은 자주 묻는 질문은 설명 명령 및 느린 쿼리 로그를 통해 디버깅 할 수 있습니다. 5) 성능 최적화 방법에는 인덱스의 합리적인 사용, 최적화 된 쿼리 및 캐시 사용이 포함됩니다. 모범 사례에는 거래 사용 및 준비된 체계가 포함됩니다

MySQL : 소기업에서 대기업에 이르기까지MySQL : 소기업에서 대기업에 이르기까지Apr 13, 2025 am 12:17 AM

MySQL은 소규모 및 대기업에 적합합니다. 1) 소기업은 고객 정보 저장과 같은 기본 데이터 관리에 MySQL을 사용할 수 있습니다. 2) 대기업은 MySQL을 사용하여 대규모 데이터 및 복잡한 비즈니스 로직을 처리하여 쿼리 성능 및 트랜잭션 처리를 최적화 할 수 있습니다.

Phantom은 무엇을 읽고, Innodb는 어떻게 그들을 막을 수 있습니까 (다음 키 잠금)?Phantom은 무엇을 읽고, Innodb는 어떻게 그들을 막을 수 있습니까 (다음 키 잠금)?Apr 13, 2025 am 12:16 AM

InnoDB는 팬텀 읽기를 차세대 점화 메커니즘을 통해 효과적으로 방지합니다. 1) Next-Keylocking은 Row Lock과 Gap Lock을 결합하여 레코드와 간격을 잠그기 위해 새로운 레코드가 삽입되지 않도록합니다. 2) 실제 응용 분야에서 쿼리를 최적화하고 격리 수준을 조정함으로써 잠금 경쟁을 줄이고 동시성 성능을 향상시킬 수 있습니다.

MySQL : 프로그래밍 언어는 아니지만 ...MySQL : 프로그래밍 언어는 아니지만 ...Apr 13, 2025 am 12:03 AM

MySQL은 프로그래밍 언어가 아니지만 쿼리 언어 SQL은 프로그래밍 언어의 특성을 가지고 있습니다. 1. SQL은 조건부 판단, 루프 및 가변 작업을 지원합니다. 2. 저장된 절차, 트리거 및 기능을 통해 사용자는 데이터베이스에서 복잡한 논리 작업을 수행 할 수 있습니다.

MySQL : 세계에서 가장 인기있는 데이터베이스 소개MySQL : 세계에서 가장 인기있는 데이터베이스 소개Apr 12, 2025 am 12:18 AM

MySQL은 오픈 소스 관계형 데이터베이스 관리 시스템으로, 주로 데이터를 신속하고 안정적으로 저장하고 검색하는 데 사용됩니다. 작업 원칙에는 클라이언트 요청, 쿼리 해상도, 쿼리 실행 및 반환 결과가 포함됩니다. 사용의 예로는 테이블 작성, 데이터 삽입 및 쿼리 및 조인 작업과 같은 고급 기능이 포함됩니다. 일반적인 오류에는 SQL 구문, 데이터 유형 및 권한이 포함되며 최적화 제안에는 인덱스 사용, 최적화 된 쿼리 및 테이블 분할이 포함됩니다.

MySQL의 중요성 : 데이터 저장 및 관리MySQL의 중요성 : 데이터 저장 및 관리Apr 12, 2025 am 12:18 AM

MySQL은 데이터 저장, 관리, 쿼리 및 보안에 적합한 오픈 소스 관계형 데이터베이스 관리 시스템입니다. 1. 다양한 운영 체제를 지원하며 웹 응용 프로그램 및 기타 필드에서 널리 사용됩니다. 2. 클라이언트-서버 아키텍처 및 다양한 스토리지 엔진을 통해 MySQL은 데이터를 효율적으로 처리합니다. 3. 기본 사용에는 데이터베이스 및 테이블 작성, 데이터 삽입, 쿼리 및 업데이트가 포함됩니다. 4. 고급 사용에는 복잡한 쿼리 및 저장 프로 시저가 포함됩니다. 5. 설명 진술을 통해 일반적인 오류를 디버깅 할 수 있습니다. 6. 성능 최적화에는 인덱스의 합리적인 사용 및 최적화 된 쿼리 문이 포함됩니다.

MySQL을 사용하는 이유는 무엇입니까? 혜택과 장점MySQL을 사용하는 이유는 무엇입니까? 혜택과 장점Apr 12, 2025 am 12:17 AM

MySQL은 성능, 신뢰성, 사용 편의성 및 커뮤니티 지원을 위해 선택됩니다. 1.MYSQL은 효율적인 데이터 저장 및 검색 기능을 제공하여 여러 데이터 유형 및 고급 쿼리 작업을 지원합니다. 2. 고객-서버 아키텍처 및 다중 스토리지 엔진을 채택하여 트랜잭션 및 쿼리 최적화를 지원합니다. 3. 사용하기 쉽고 다양한 운영 체제 및 프로그래밍 언어를 지원합니다. 4. 강력한 지역 사회 지원을 받고 풍부한 자원과 솔루션을 제공합니다.

InnoDB 잠금 장치 (공유 잠금, 독점 잠금, 의도 잠금, 레코드 잠금, 갭 잠금, 차세대 자물쇠)를 설명하십시오.InnoDB 잠금 장치 (공유 잠금, 독점 잠금, 의도 잠금, 레코드 잠금, 갭 잠금, 차세대 자물쇠)를 설명하십시오.Apr 12, 2025 am 12:16 AM

InnoDB의 잠금 장치에는 공유 잠금 장치, 독점 잠금, 의도 잠금 장치, 레코드 잠금, 갭 잠금 및 다음 키 잠금 장치가 포함됩니다. 1. 공유 잠금을 사용하면 다른 트랜잭션을 읽지 않고 트랜잭션이 데이터를 읽을 수 있습니다. 2. 독점 잠금은 다른 트랜잭션이 데이터를 읽고 수정하는 것을 방지합니다. 3. 의도 잠금은 잠금 효율을 최적화합니다. 4. 레코드 잠금 잠금 인덱스 레코드. 5. 갭 잠금 잠금 장치 색인 기록 간격. 6. 다음 키 잠금은 데이터 일관성을 보장하기 위해 레코드 잠금과 갭 잠금의 조합입니다.

See all articles

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

인기 기사

R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
3 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. 최고의 그래픽 설정
3 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. 아무도들을 수없는 경우 오디오를 수정하는 방법
3 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25 : Myrise에서 모든 것을 잠금 해제하는 방법
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌

뜨거운 도구

맨티스BT

맨티스BT

Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.

MinGW - Windows용 미니멀리스트 GNU

MinGW - Windows용 미니멀리스트 GNU

이 프로젝트는 osdn.net/projects/mingw로 마이그레이션되는 중입니다. 계속해서 그곳에서 우리를 팔로우할 수 있습니다. MinGW: GCC(GNU Compiler Collection)의 기본 Windows 포트로, 기본 Windows 애플리케이션을 구축하기 위한 무료 배포 가능 가져오기 라이브러리 및 헤더 파일로 C99 기능을 지원하는 MSVC 런타임에 대한 확장이 포함되어 있습니다. 모든 MinGW 소프트웨어는 64비트 Windows 플랫폼에서 실행될 수 있습니다.

ZendStudio 13.5.1 맥

ZendStudio 13.5.1 맥

강력한 PHP 통합 개발 환경

에디트플러스 중국어 크랙 버전

에디트플러스 중국어 크랙 버전

작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경