SQL Server

点击我看文档视频资源

01数据库的基本概念

数据库(DataBase):按照一定的数据结构来组织,存储和管理数据的仓库,分为关系型数据库和非关系型数据库

数据库管理系统(DataBase Management System DBMS):为管理数据库而设计的一个电脑软件系统

数据库系统:由数据库和数据库管理系统组成

02创建数据库

登录数据库的时候如果数据库在本机上,服务器名称:local | . | 127.0.0.1

数据库在远程服务器上: ip地址,端口号(如果有的话)

身份验证:windows身份验证(windows身份拥有最高权限,相当于数据库管理员)

SQL Server身份验证(需要账号,密码,需要先以window身份登进去创建用户并赋予角色)

03数据库的组成

数据库是以文件的形式存在的,由文件和文件组组成

数据库文件:

a.主要数据文件,扩展名.mdf,存放数据和数据库的初始化信息,每个数据库只能有一个主要数据文件

b.次要数据文件,扩展名.ndf,可以有0个到多个,当数据比较多的话,可以放到次要数据文件中

c.事务日志文件,扩展名.ldf,存放用于恢复数据库的所有日志信息.每个数据库至少有一个日志文件,可以有多个

数据库文件组:现在了解为时尚早,之后再说吧

04数据库常用对象介绍

表 字段 视图 索引 存储过程 触发器 约束 缺省值

(不常用的:函数,事务)

视图:一个或多个表中导出的虚拟表,其数据和数据结构建立在表的查询基础上

索引:提供一种快速访问数据的方式,检索数据时不是对全表进行扫描,而是通过索引快速定位到要查找的数据

存储过程:完成某一功能的sql集合,编译后存储到数据库中,以名称进行调用.

触发器:在数据库中,属于用户自定义的事务命令集合,针对表来说,当对表进行增删改查操作时,命令就会自动触发而去执行

约束: 对数据表中的列进行的一种限制.可以更好的规范表中的列

缺省值:可以对表中的列指定一个默认值.

05数据库的数据类型

数值型:

a.整型:bigint int smallint tinyint 8->4->2->1->

b.浮点型:

float 近似数值,存在精度损失,避免使用等号,而是使用><

decimal:精确数值,不存在精度的损失 decimal(18,2)

货币类型:money smallmoney 8->4

二进制数据类型:(使用不太多)

bit 允许0,1或者null

varbinary(n) 可变长度的二进制数据最多8000字节

varbinary(max) 可变长度的二进制数据最多2G字节

image 可变长度的二进制数据最多2G字节

字符型:

注意:

a.如果字符只有英文使用character字符串就可以,如果包含英文则使用unicode字符串,避免出现乱码问题.

b.有var前缀的字符类型是可变字符,比如:

定义varchar(100)属性的字段能够容纳的字符只有100个,不能超过100,但是如果存储的只有五个,他也不会浪费存储空间,这就是可变;定义char(100)属性的字段也是只能容纳100个字符,但是如果只存储5个,sql server会插入95个空格填满这列,这样就会很浪费空间了,所以一般使用varchar来存储字符串和文本值

c.我工作中的数据库使用的字符集是cp950(繁体中文),使用这种字符集时character字符一个长度占一个字节,存储一个英文或者数字用一个字节,存储一个中文用两个字节;unicode字符一个长度占两个字节,存储一个英文或数字或中文都是用两个字节.如果一个列的数据类型是char(1),那么该列是不能存储一个汉字的.

查看数据库使用编码的字符集: select serverProperty('SqlCharSetName')

在我工作的数据库中有如下情况:

是不是很奇怪,aaa表中的name列数据类型是nchar(3),为什么"宋宝涛"保存到数据库中还是乱码?

这是因为在保存至数据库前使用的字符集是cp950(繁体中文),使用该字符集找不到"宝涛"二字符对应的编码,所以就默认保存"?"字符对应的编码. 在查找该数据时使用的字符集是Unicode字符集,巧合的是Unicode字符集和cp950字符集对"?"字符的编码数字值是一样的,所以就查出了"宋??".如果想要避免这种情况,请在插入数据的时候就告诉数据库aaa表中的name列使用的是Unicode字符集,即在前面加上N,如下:

` insert into aaa select N'宋宝涛'
 

日期型:

其他数据类型:

只需要记住uniqueidentifier 全球唯一标识符,能够保证生成的每一个标识都是全球唯一的(我的天这是真的吗?)

06创建表及其主外键

主键:唯一标识一条数据,值不能重复,不能为空.(可以设置联合主键,两个列作为一个主键)

创建一个主键的同时也默认创建了一个唯一聚集索引.

标识列:一个列设置成标识列,他就不能再手动插入,每插入一条数据会自动生成,可以设置标识列的数据类型是整型(可以设置标识增量和标识种子)

外键:一般在两个表之间要建立关联的时候需要创建外键,一个列创建为外键他在另一个表中必须是主键.(如果在外键表插入一个外键不存在的值是插不进去的)

07数据库约束

约束定义:规定表中的数据规则.如果违反数据规则就会被阻止.

约束可以在表创建时或者创建后进行创建.()

约束分类:

主键 primary key约束

外键 foreign key约束

先建立主表中的主键,然后再定义从表中的外键,只有主表中的主键才能被外表中的外键使用.主表限制了从表的插入和更新操作.当删除主表中的数据应该先删除从表中的相关数据,再删除主表中的数据.

Unique约束 唯一性约束

确保表中的一列数据不能有相同的值,再给一个列设置为唯一键时会为该列自动创建一个唯一非聚集索引

Check约束

通过逻辑表达式来判断数据的有效性,用来限制输入一列或者多列值的范围

Dafault约束 默认值约束

08数据库脚本

数据库脚本:创建数据库对象(数据库对象请看上面)的语句集合.包含:存储过程[Procedure]，事务[transaction]等，索引[Index]，触发器[Trigger]，函数[Function]等。

SQL:

结构化查询语言(Structured Query Language) 简称SQL,是一种特殊目的的编程语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名

T-SQL:

T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程式与数据库沟通的主要语言。T-SQL为SQL的扩展语言，譬如加入了程序语言中的if,while 等语法，同时可以使用函数等数据库对象.

09脚本创建数据库

创建数据库:

use master  --选择要使用的数据库
go

create database TestSongDataBase
on primary
(
	name='TestSongDataBase',   --数据库主要数据文件的逻辑名
	filename='D:\DataBase\TestSongDataBase.mdf',		--主要数据文件的路径(绝对路径)
	size=5MB,   --数据库主要文件的初始大小
	filegrowth=1MB    --文件的增量
)
log on  --创建日志文件
(
	name='TestSongDataBase_log',   --数据库日志文件的逻辑名
	filename='D:\DataBase\TestSongDataBase_log.ldf',		--日志文件的路径(绝对路径)
	size=5MB,   --数据库日志的初始大小
	filegrowth=10%    --文件的增量
)
go

go --批处理命令

如果只是执行一条语句，有没有GO都一样如果多条语句之间用GO分隔开就不一样了每个被GO分隔的语句都是一个单独的事务

为什么创建数据库要使用master?

master:系统数据库,它记录了SQL Server系统的所有系统级信息,还记录了其他数据库的存在,数据库的文件位置,sql server的初始化信息.

删除数据库:

drop TestSongDataBase

10 T-SQL创建表

tips:

哪个字段被设置成主键,那个字段就会被自动建立唯一聚集索引.

创建表:

use TestSongDataBase
create table ProductInfos
(
	Id int primary key identity(1,1) not null,
	ProNO varchar(50) not null,
	ProName nvarchar(50) not null,
	TypeId int not null,
	Price decimal(18,2) default(0.00),
	ProCount int default(0)
)
go


create table ProductType
(
	TypeId int identity(1,1) primary key not null,
	TypeName nvarchar(50) not null
)
go

删除表:

drop table ProductInfos --将表结构及其数据全部删除
go

truncate table ProductInfos --不删除表结构,只删除里面的数据
go

 

11 SQL修改表

--添加一列
alter table ProductInfos
add ProRemark nvarchar(max) null

--刪除一列
alter table ProductInfos
drop column ProRemark

--修改一列(注意:修改列名要慎重,因为修改列名可能会破坏脚本和存储过程)
alter table ProductInfos
alter column ProNo nvarchar(50) null

--修改列名(需要执行存储过程)
exec sp_rename 'ProductInfos.ProCount','Count','column'

12 T-SQL创建约束(主键,外键,Unique,Check,Default)

在创建表的时候创建外键约束

create table ProductInfos
(
	Id int primary key identity(1,1) not null,--主键约束
	ProNO varchar(50) not null unique,  --unique约束
	ProName nvarchar(50) not null,
	TypeId int not null foreign key references ProductType(TypeId),--外键约束
	Price decimal(18,2) default(0.00) check(price<10000),--default约束和check约束
	ProCount int default(0) 
)

在表创建完成后创建约束

--主键
alter table ProductInfos
add constraint PK_ProductInfos primary key(Id)

--外键
alter table ProductInfos
add constraint FK_ProductInfos foreign key(TypeId) references ProductType(TypeId)

--Unique
alter table ProductInfos
add constraint UQ_ProductInfos_ProNo unique(ProNo)

alter table ProductInfos
add constraint UQ_ProductInfos_ProNo unique(ProNo,ProName)--联合多个列的唯一约束


--Check
alter table ProductInfos
add constraint CK_ProductInfos_Price check(price<10000)

--Default
alter table ProductInfos
add constraint DF_ProductInfos_ProCount default(0) for ProCount

13. T-SQL插入数据 --insert DML(数据操纵语言)

--1.单条数据
insert into ProductType(TypeName)
values('衣服类')

insert into ProductType(TypeName)
select '食品类'     --这种插入方式有点新颖

--2.多条数据
drop table aaa_test
create table aaa_test
(
	c1 int primary key identity(1,1),
	c2 nvarchar(10) null,
	c3 nvarchar(10) null
)

insert into aaa_test(c2,c3)
values('c21','c31'),('c22','c32')

insert into aaa_test(c2,c3)
select 'c21','c31' union   
select 'c21','c31'

--注意:这里只能插入一条数据因为union具有去重的操作,使用union all则可以插入两条重复的数据,所以使用union all的效率更高
select * from aaa_test

--3.克隆数据--将一个表中的数据复制到另一个表
--目标表在数据库中已经存在
insert into aaa_test(c2)
select TypeName from ProductType

--目标表在数据库中不存在(注意:创建的新表aaa_test2没有目标表所拥有的约束,索引)
select TypeName into aaa_test2 from ProductType

14. T-SQL更新删除数据

--更改(注意加where条件,不加where条件会更改整个表)
update aaa_test set c2='c23' where c1=2

--删除(注意:不加where会删除整个表的数据,同时删除后标识列会按照之前的最大值继续自增)
delete from  aaa_test where c1=2

--清空整个表的数据,并对表进行初始化(标识列会从初始值自增),表面上和delete from aaa_test
truncate table aaa_test

--注意:truncate的效率要比delete的高,因为delete每删除一条数据就记录一条日志;truancate 不会记录日志,不会激活触发器.
--truncate drop 是即时操作,不能rollback;delete insert upate 在事务中可以rollback
--慎用truncate,使用truncate删除数据一旦删除不能恢复

T-SQL查询数据之单表查询

--1.在表的查询中最好只查询需要的列.因为这样既可以节省内存,又可以提高查询的效率
--2.给列命别名(三种方式)
select ProNO as '产品编号',ProName '产品名字','产品数量'=ProCount from ProductInfos

--3.排序
select * from ProductInfos order by ProNO,ProCount   --默认升序排列(先排ProNO,再排ProCount)
select * from ProductInfos order by ProNO asc,ProCount desc

16. SQL查询之模糊查询

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

17.SQL查询之范围查询

select top 10 * from ProductInfos
select top 10 percent * from ProductInfos

>
<
>=
<=

<>
in
not in
between 相當於>= and <=(推荐使用between,因为>= and <=会一条一条的进行判断,而between给一个区间效率会高一些.)(这一句说的对吗?我持怀疑态度)

18. 聚合函数

select count(1) 伪造列,求一个表的记录数
count(1) count(*) 一般使用count(1),因为count(1)的效率要比count(*)的效率要高(是吗?)
2.sum()
3.max()
4.min()
5.avg()
 

19. 分组查询

--统计各部门有多少个用户
select deptId,count(1) from UserInfos--出现在select里面的列必须出现在group by之后,或者包含在聚合函数中
where age>20
group by deptId
having count(1)>5   --分组后的筛选条件.
order by deptId

20. 连接查询之内连接

连接查询:根据两个或者多个表之间的关系,从这些表中查询数据
目的:实现多表查询
分类:内连接,外连接,交叉连接

--显式连接
select userId,userName,age,d.deptId,d.deptName from UserInfos u
inner join DeptInfos d on u.deptid=d.deptid --on用于关联条件 u.deptid和d.deptid之间也可以用> < >= <= <>比较运算符,一般不使用
--如果关联条件有多个可以u.deptid=d.deptid and ...
--隐式连接
select userId,userName,age,d.deptId,d.deptName from UserInfos u, DeptInfos d
where u.deptid=d.deptid

--上面这两种写法在效果和效率方面都是一样的.
--如果用户表某个用户的deptid在部门表里不存在,则是查不出该用户的.

21.连接查询之外连接

外连接的分类:左外连接,右外连接,全外连接,简称左链接,右连接,全连接,

左链接:left (outer) join : 右表的行数和左表相同,如果没有匹配上显示null
select userId,userName,age,d.deptId,d.deptName from UserInfos u
left join DeptInfos d on u.deptid=d.deptid

右链接:right (outer) join: 左表的行数和右表相同,如果没有匹配上显示null
select userId,userName,age,d.deptId,d.deptName from UserInfos u
right join DeptInfos d on u.deptid=d.deptid

全连接:full (outer) join : 左表和右表中的数据都会显示出来(当左表中存在匹配不到右表中的数据显示null,
当右表中存在匹配不到左表中的数据显示null)
select userId,userName,age,d.deptId,d.deptName from UserInfos u
full join DeptInfos d on u.deptid=d.deptid
 

22.连接查询之交叉连接

--连接查询之交叉连接 迪卡尔积(用的不多)
--交叉连接:cross join : 如果不带where条件返回的是两个表行数乘积条数据,带where子句等价于inner join
--交叉连接不能使用on筛选条件
select userId,userName,age,d.deptId,d.deptName from UserInfos u
cross join DeptInfos d on u.deptid=d.deptid
where u.deptid=d.deptid
 

23. 类型转换函数

--convert(类型(长度),表达式)
select convert(varchar,2256)
select convert(varchar(10),getdate(),120)--120日期格式
--cast(表达式 as 数据类型(长度))    --转换成时间时不能指定时间的格式
select cast(2 as varchar)

24. 数据库內置函数操作

24.1.字符串函数操作

--在数据库中索引开始的位置是1
select lower('ABC')--转化成小写
select upper('abc')--转化成大写
select ltrim(' ABC')
select rtrim('abc ')
select reverse('ABC') --CBA

select left('ABCDEFG',3)  --ABC 返回字符串左边起三个字符
select right('ABCDEFG',3) --EFG 返回字符串右边起三个字符

select charindex('ab','cabdf') --2 返回字符串中指定的子串出现的开始位置,默认不区分大小写
select charindex('AB','cabdf'collate latin1_general_cs_as )  --0 大小写敏感  cs代表case sensitive
select charindex('AB','cabdf'collate latin1_general_cs_as )  --0 大小写不敏感  ci代表case insensitive
select patindex('%ab%','cabdf')--2 和上面的含义一样,只不过需要在子串前后添加%

select replicate('ABC',3)--ABCABCABC

select substring('ABCDEFG',3,4)--CDEF 从字符串左边起第三个开始,取四个字符

select replace('abcdef' ,'bc', 'gg' ) --aggdef
select stuff('abcdef',2,3,'tt')        --attef

select LEN('123456')  --6   得到字符串中字符的個數
select concat('first','last')--将两个字符串拼成一个字符串
select concat('first','-','-','last')   --结果:first--last

24.2.日期函数操作

select GETDATE()
select DATEADD(DAY,2,'2020-03-24')
--获取SQL最小日期,在最小日期加上0天,0月,0年效果是一样的
select DATEADD(DAY,0,0)
--获取最小日期的前一天,也就是最小日期的上一个月的最后一天
select DATEADD(DAY,0,-1)
--计算当前日期加一个月的日期,也就是下个月的今天
select DATEADD(MONTH,1,GETDATE())
--计算GETDATE()与最小日期相差了多少月
select DATEDIFF(MONTH,0,GETDATE())
--计算当月最后一天的日期
select EOMONTH(GETDATE())


--DATEPART(datepart,date)--datepart:要获取时间的哪一部分;date:完整的时间
select datepart(YEAR,getdate())  
select datepart(quarter,getdate())  --获取时间中的季度
select datepart(MONTH,getdate())   
select datepart(day,getdate())   
select datepart(week,getdate())  --一年中的第几周
select datename(WEEKDAY,getdate())  --今天是星期几
select datepart(HOUR,getdate())  --时
select datepart(MINUTE,getdate())  --分
select datepart(SECOND,getdate())  --秒
--用不同的格式显示日期
select CONVERT(varchar(10),GETDATE(),101) --USA  mm/dd/yy  结果:04/19/2021
select CONVERT(varchar(10),GETDATE(),102) --ANSI  yy.mm.dd  结果:2021.04.19
select CONVERT(varchar(10),GETDATE(),103) --British/French  dd/mm/yy  结果:19/04/2021
select CONVERT(varchar(10),GETDATE(),111) --japan  yy/mm/dd  结果:2021/04/19
select convert(varchar(30),GETDATE(),120) --yyyy-mm-dd hh:mi:ss  結果:2021-04-19 14:38:35
select convert(varchar(10),GETDATE(),120) --yyyy-mm-dd hh:mi:ss  結果:2021-04-19

24.3.其他函数操作或关键字

--isnull
--当@a=''或者'     '或者null的时候都会输出11111111
declare @a varchar(50)=null
if ISNULL(@a,'')=''
print '11111111'

--all any some
--select invoices larger than all invoices of client 3 (发票额度大于client_id=3的客户的所有发票额度的发票都会被筛选出来)
select * from invoices
where invoice_total>all
(
	select invoice_total
	from invoices
	where client_id=3
)
--发票额度大于client_id=3的客户的任一发票额度的发票都会被筛选出来
--any和some作用相同
select * from invoices
where invoice_total>any
(
	select invoice_total
	from invoices
	where client_id=3
)

--isnull(expression1,expression2) 
--如果expression1为null,则取expression2的值,不管expression是否为null
select isnull(null,null)

--coalesce(expression1,expression2,...)
--从左到右返回第一个不为null的值,expression至少有一个非null,即expression不能全为null
select coalesce(null,null,null)

--return的使用:
        --return会无条件的结束查询和程序,return之后语句不会再执行,同时可以向发出呼叫的程序返回一个整数值

        --语法:return [integer_expression]--只能是整数值,如果不写默认返回0
alter proc sp_getUserInfo
as
begin
	begin
		select top 2 * from aaa_user
		return 15
		select top 4 * from aaa_user
	end
end

declare @a varchar(20)
exec @a= aaaa  --获取整数值
select @a

exec aaaa --也可以不要返回的整数值

 

--case
--语法:

--Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   

--Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END


--实例:
select userNo,userName,
case siteCode
	when 'LF' then '廊坊'
	when 'LH' then '龍華'
	else '其他'
end SiteName
from aaa_user

select userNo,userName,
case 
	when GradeLevel<30 then '高級領導'
	when GradeLevel>100 then '低層領導'
	else '中層領導'
end GradeLevel
from aaa_user

--goto语句的使用:(不建议使用,因为会增加代码的阅读难度)
--语法:

lable:
Goto lable

declare @level int
select @level=GradeLevel from aaa_user where UserNO='P1187656'
if @level<20
	goto print1
else
	goto print2
print1:
	print 'big boss'
	goto theEnd
print '1111'
print2:
	print 'lower boss'
	goto theEnd
theEnd:

25. 索引介绍

索引的作用：提高查询速度

索引是什么？索引类似于查询字典时的拼音和偏旁部首，通过拼音和偏旁部首能够快速的定位到想要查找的字。这样就不用进行全书查找了（全表扫描）

索引会占用存储空间，设置索引时并不是越多越好，索引并不是总能提高系统性能

给一个表的列设为主键时该列会自动被创建为唯一聚集索引

索引分为：聚集索引和非聚集索引

聚集索引：clustered 逻辑顺序和物理顺序是一致的

非聚集索引：nonClustered 逻辑顺序和物理顺序并不一致

聚集索引的查询效率高于非聚集索引

一个表中最多只能有一个聚集索引，可以有0个以上非聚集索引

如果需要在多个列上创建索引，这些列可以创建组合索引

在小数据类型的列上创建的索引访问速度会比较快

索引是看不见的，但是当你查询大数据的时候，他的优势是显而易见的

图解｜从根儿上理解MySQL索引

上面链接的知识同样适用于SQL Server

26. 创建索引

索引分类:聚集索引,非聚集索引,主鍵索引,唯一索引
如果不指定索引的类型，一般创建的都是非聚集索引

--创建聚集索引
create clustered index PK_UserInfos
on UserInfos(UserId)
with
(
    drop_existing=on --on：当存在PK_UserInfos索引的时候会先删除，然后再重新创建
)                                    --off：当存在PK_UserInfos索引的时候会报错

--创建唯一非聚集索引
create unique nonclustered index uq_UserInfos
on UserInfos(userId)
with
(
    pad_index=on, --索引页预留空间
    fillfactor=70,   --数据占整个页存储空间的百分比
    ignore_dup_key=on    --on:insert操作时，userId包含有重复值的数据会被拒绝，非重复的正常插入
                        --off:insert操作时，只要userId有重复值所有的数据都会被拒绝
)

--创建复合索引
create unique nonclustered index uq_UserInfos2
on UserInfos(userId,deptID)
with
(
    pad_index=on, --索引页预留空间
    fillfactor=70,
    ignore_dup_key=on    --on:insert操作时，userId包含有重复值的数据会被拒绝，非重复的正常插入
                        --off:insert操作时，只要userId有重复值所有的数据都会被拒绝
)

注意：a.一般我们不会这样创建索引，在我们创建主键和唯一键的时候就自动创建了索引，所以大多数情况下我们不需要创建索引

b.一个表中已经存在了主键（即存在了唯一聚集索引），就不能再创建聚集索引，一个表中只能有一个聚集索引

c.删除索引: drop index index_name on table_name

27.视图介绍

视图：虚拟表，不包含数据

与表的区别：表存储的是数据，视图存储的是查询语句（索引视图除外）

作用：可以简化我们的查询，增加数据的保密性和安全性(视图只能查询所以安全性有保证)。

缺点：增加了维护成本，并没有提高查询效率。

分类：

标准视图（经常使用），

索引视图：被具体化的视图；可以创建索引来显著提高查询性能，聚集了很多行的查询，不太适合经常更新基本数据集

分区视图：一台或者多台服务器间水平连接一组成员表的分区数据，使数据看起来就像来自一个表

注意：

我们不仅可以在select中使用视图,我们也可以在insert/delete/update中使用视图,但是要局限于特定情况:
视图中没有distinct关键字/group by或者having子句/任何聚合函数/union运算符. 此时这个视图已经upate和delete条件了,当视图中有基础表中所有不为空的列才满足insert条件

但是出于安全考虑我们可能没有某张表的直接权限,只能通过视图修改数据(增删改查),前提是:视图是可更新视图.

28.脚本创建视图

a.创建标椎视图：

create view vUserInfos
as
select u.*,d.deptName from UserInfos u
left join dept d on u.deptID=d.deptID
 

b.创建索引视图：

create view vUserInfos_index with schemabinding
as
select u.userId,u.userName,u.deptID,u.age from dbo.UserInfos u
go

create unique clustered index uq_vUserInfos_index--给视图创建唯一聚集索引
on vUserInfos_index(userId)
go
 

注意：1.with schemabinding后需要在关联的表名前添加架构(如:dbo等),同时不能使用select *，需要把所需要的列给一一列举出来，

2.视图的sql语句有外连接(左外连接,右外连接,全外连接),该视图是不能创建索引的

3.数据是实际存在的删除视图里面的数据，基础表里面的数据也会被删除（慎用）

(如果基础表有两个及以上，就算视图创建了索引也是删除不了数据的)

创建分区视图：

create view vTest
as
select * from Test1
union all
select * from Test2
 

29.存储过程介绍

存储过程：一种为了完成特定功能的一个或一组sql语句的集合。经编译后存储在服务器端的数据库中，可以利用存储过程加速sql语句的执行。

分类：

系统存储过程：存在master数据库中，其他数据库中是可以直接调用的，就不需要在前面加上数据库名。创建新的数据库时这些存储过程在新的数据库中自动创建

自定义存储过程：用户自己创建，可以传入参数，也可以有返回值，表示存储过程是否执行成功。

编辑

30.脚本创建存储过程

create proc sp_proc1
@p1 int,
@p2 varchar(20)
as
begin
declare @sql varchar(2000)
select * from userInfos
end

31.触发器的介绍

触发器：一种特殊类型的存储过程。

触发器的调用：通过事件触发，自动调用执行。

对表进行插入，更新，删除操作时，如果定义了相应的触发器，系统会自动执行。

分类:DDL(数据定义语言)触发器：create alter drop 不常用

DML(数据操纵语言)触发器：insert delete update

DML触发器分两种：after触发器：insert delete update ：操作之后触发

instead of触发器：insert delete update ：不执行定义的操作，而执行相应的触发器

32.触发器涉及的两个表：inserted deleted

inserted deleted : 插入表 删除表

两个表都是逻辑表，也是虚表，系统在内存中创建的，不会存储到数据库中。只读的，读取但不能修改数据。结构与操作表的结构相同。触发器执行过程中存在并且可以访问，工作完成之后这两张表会自动删除。

对表的操作： inserted deleted

insert 存放插入的数据 无

delete 无 存放删除的数据

update 存放更新后的数据 存放更新前的数据

inserted：存放插入或者更新后的数据

deleted：存放删除或者更新前的数据

触发器在执行过程中这2个表都是可以访问的，提交之前是不是可以撤销呢--事务的回滚？

答：可以，触发器是一种特殊的事务，可以设置一些数据校验，如果不满足让其回滚。

33.after触发器之insert delete update

after insert：

create trigger trigger_UserInfos_insert
on UserInfos 
for insert  --for insert 也可以写成 after insert
as    --触发器不能用begin  end
    declare @userId varchar(20)
    declare @userName varchar(20)
    select @userId=userId,@userName=userName  from inserted--inserted表是只读的
    print @userId+'--'+@userName+'--'+'数据插入成功！'
go

after update：

 

create trigger trigger_UserInfos_update
on UserInfos
for update
as
	declare @userId_before varchar(20)
	declare @userName_before varchar(20)
	declare @userId_after varchar(20)
	declare @userName_after varchar(20)
	select @userId_before=userid,@userName_before=userName from deleted
	select @userId_after=userid,@userName_after=userName from inserted
	print '修改前:'+@userId_before+@userName_before
	print '修改后:'+@userId_after+@userName_after
go

after delete：

alter trigger trigger_UserInfos_delete
on UserInfos
for delete
as
	if(OBJECT_ID('UserInfosBackup','u') is  null)--判断表UserInfosBackup是否存在
	begin
		print 'null'
		select * into UserInfosBackup from deleted
	end
	else
	begin
		print 'not null'
		insert into UserInfosBackup select * from deleted
	end
	declare @userId varchar(20)
	declare @userName varchar(20)
	print '备份成功'
go

34.instead of 触发器之insert delete update

instead of触发器和after触发器差不多，只不过after触发器是在执行了定义的操作（insert delete update）后要执行的操作，instead of触发器是执行的定义的操作由触发器里面的操作代替。

instead of insert：

create trigger trigger_UserInfos_insteadOfInsert
on UserInfos
instead of insert
as
	declare @age  int
	declare @userName varchar(20)
	select @age=age,@userName=userName from inserted
	print '插入操作已被取代'

36.游标介绍及分类

游标是什么：对数据查询结果集的一种访问机制，用户对结果集进行逐条访问。

游标访问对象:结果集

游标的作用：定位到结果集的某一行，对当前数据进行读取。

数据的结果集是放在TempDB里，相当于内存中，所以游标适用于数据量比较小的情况下使用。

游标的分类：静态，动态，只进，键集驱动游标

静态：对数据库如何操作结果集都不会变，支持滚动，

动态：与静态相对，结果集获取所有的改变，对数据库的操作都可以通过游标可见。api函数或t-sql where current of子句通过游标进行更新。游标外部所做的更新直到提交时才可见。前后滚动。

只进：不支持滚动，从头读到尾，结果集获取所有的改变，对数据库所做的更改是可见的（仅限于提取前）

游标的声明周期：声明游标->打开游标->读取数据->关闭游标->释放游标

37.创建只进游标

--1.声明游标  只进游标是不允许向前翻动的
declare user_cursor cursor local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin
	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor  into @userId,@userName,@age
end
--关闭游标
close user_cursor
--释放游标
deallocate user_cursor

38.创建静态游标

--1.声明游标  static标识该游标是静态游标
declare user_cursor_static cursor static local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor_static
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor_static  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin	
	update [dbo].[user] set age=19 where userid='32BECDFF-9886-4ED9-ACF2-AAEA5799B18E'

	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor_static  into @userId,@userName,@age
end
--关闭游标
close user_cursor_static
--释放游标
deallocate user_cursor_static

39.创建动态游标

--1.声明游标  dynamic标识该游标是动态游标
declare user_cursor_dynamic cursor dynamic local--local创建局部游标，global创建全局游标
for
select userId,userName,age from [dbo].[user] where age>14
--2.打开游标
open user_cursor_dynamic
--3.取值
declare @userid uniqueidentifier,@userName nvarchar(10),@age int
--提取数据：   next向下，prior向上，first第一个数据，last最后一个数据， 
--absolute n 绝对位置第n个，relative n 相对位置第n个(相对位置正值向前,负值向后)
fetch next from user_cursor_dynamic  into @userId,@userName,@age

while @@FETCH_STATUS=0  --判断值是否提取成功
begin	
	update [dbo].[user] set age=33 where current of user_cursor_dynamic--更改当前动态游标所在位置的数据

	print cast(@userId as varchar(100))+','+@userName+','+cast(@age as varchar)
	fetch next from user_cursor_dynamic  into @userId,@userName,@age
end
--关闭游标
close user_cursor_dynamic
--释放游标
deallocate user_cursor_dynamic

40.自定义函数之标量函数

自定义函数：根据自己的需要，自己定义函数

自定义函数分类：标量函数，多语句表值函数，内嵌表值函数

标量函数：对单一值的操作，返回单一值 begin end

内嵌表值函数：相当于参数化的视图，返回一个表，没有begin end

多语句表值函数：返回的也是一个表，只不过多语句表值函数可以多次查询，筛选和合并，弥补了内嵌表值函数的不足 begin end

创建标量函数：

alter function GetAge(@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
returns int
with encryption --此处可写可不写，如果指定encryption，则函数被加密
as  --此处可写可不写
begin
	declare @age int
	select @age=age from [user] where userid=@userid--加上[]标识user不是关键字或者预留字
	return @age
end

select dbo.GetAge('2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
--可以使用默认值进行调用函数，如上面的@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C'和下面的dbo.GetAge(default)，如果上面的@userid没有指定默认值，下面的dbo.GetAge(default)会返回null
select dbo.GetAge(default)

注意调用标量函数的时候需要带上架构(比如:dbo.)，而内嵌表值函数和多语句表值函数则不需要。

创建内嵌表值函数：

alter function GetUserInfo(@userid uniqueidentifier='2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')
returns table
as
return select * from [user] where userid=@userid
go
--调用函数
select * from GetUserInfo('2F7A94F2-A41B-40B5-8CEC-0C7B3793443C')

select * from GetUserInfo(default)

注意：1.内嵌表值函数里面是没有begin end的

2.内嵌表值函数的return后面有且仅有一句T-SQL语句

创建多语句表值函数：

create function GetUserInfo(@deptid uniqueidentifier)
returns @userInfos table (userName nvarchar(10),deptId uniqueidentifier,createDate datetime,age int)
with encryption --此处可写可不写，如果指定encryption，则函数被加密
as  --此处可写可不写
begin
	insert into @userInfos select userName,deptID,createDate,age from [user] where deptID=@deptid
	--这里可以写多条插入语句
	return  --后面什么也不跟
end

--调用
select * from GetUserInfo('921EC616-FFD4-4AFB-83CD-702751D1189B')

函数总结：

a.函数一般做查询使用

b.如果使用内嵌表值函数就能实现的就不要使用多语句表值函数，因为多语句表值函数效率会低一些。

c.函数不能够修改基表中的数据，也就是不能使用insert，update，delete语句,但是多语句表值函数可以更改@userInfos表里的值,最终呈现用户所想要的数据,而行内表值函数不能完成这样的操作

42.事务的介绍

什么是事务：一系列T-SQL语句构成的逻辑工作单元。

事务应用：应用于多表操作。

当事务执行过程中出现异常，系统死机或者断电，数据并没有保存到数据库。使用事务最终的结果要么是所有的操作都成功执行要么是所有的操作都不执行，提高了数据的安全性。

事务的特性：四个特性 (CAID)

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务分类：显式事务，隐式事务,自动提交事务

事务执行的大概流程：

begin try
begin tran  --开启显式事务
	--T-SQL操作（增删改）
commit tran --提交事务
end try
begin catch
rollback tran --回滚事务
end catch




set implicit_transactions on  --开启隐式事务
--T-SQL操作
commit tran
rollback tran 
set implicit_transactions off  --关闭隐式事务

示例如下:

--这里指定不指定事务名称均可
BEGIN TRANSACTION tran_UpdateTable --开启事务(tran_UpdateTable：事务名称)

DECLARE @tran_error int;--定义错误变量
SET @tran_error=0;        --错误变量的初始值为0

--使用 try catch进行错误捕捉
BEGIN TRY
UPDATE a_Students SET Name='孙悟空' WHERE Number='100001';
UPDATE a_StudentsScore SET C# ='我是分数,应该是数字' WHERE Number='100001';
END TRY

BEGIN CATCH
set @tran_error=@tran_error+1; --（计算捕捉到的错误数）加分号或不加都能正常执行
END CATCH

--判断是否有执行错误
IF(@tran_error>0)
    BEGIN 
        ROLLBACK TRANSACTION tran_UpdateTable  --执行出错，回滚事务（tran_UpdateTable：指定事务名称）
        PRINT '有【'+CONVERT(VARCHAR(50),@tran_error)+'】条执行失败，进行回滚:'; 
    END 
ELSE
    BEGIN
        COMMIT TRANSACTION tran_UpdateTable --没有异常，提交事务(tran_UpdateTable：指定事务名称)
        --事务执行成功后，查看修改后的数据
        SELECT  s.Number ,
        s.Name ,
        sc.ClassName ,
        ss.C# ,
        ss.SqlDB ,
        ss.Java ,
        ss.Python
        FROM    a_Students s
        INNER JOIN a_StudentClass sc ON s.ClassId = sc.ClassId
        INNER JOIN a_StudentsScore ss ON s.Number = ss.Number
        WHERE s.Number='100001'
    END

事务一般放在存储过程中。

显示事务和隐式事务的区别:

	显式事务	隐式事务
开启事务	执行命令begin tran开启	先执行set implicit_transactions on语句,之后再执行sql命令时系统会自动为我们创建事务
关闭事务	执行命令commit tran或者rollback tran后关闭事务	执行命令commit tran或者rollback tran后会关闭当前的事务,之后在执行sql命令系统又会帮我们创建一个新的事务.当我们执行命令set implicit_transactions off后,我们再执行sql命令时系统就不会为我们自动创建事务了

总结:隐式事务和显式事务的不同之处在于隐式事务会为我们自动创建新事务,而显式事务需要我们begin tran 显式声明.

自动提交事务： 这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

事务不隔离导致的问题

在 SQL Server 中，如果没有事务隔离机制，会引发多种数据一致性和并发控制方面的问题，以下是详细介绍：

1. 脏读（Dirty Read）

• 问题描述
  脏读指的是一个事务读取了另一个尚未提交的事务中修改的数据。当事务 A 对某数据进行修改但还未提交，此时事务 B 读取了这个被修改但未提交的数据。如果事务 A 随后进行了回滚操作，那么事务 B 读取到的数据就是无效的“脏数据”。

• 示例场景
  假设银行系统中有一个账户表 Accounts，包含 AccountID 和 Balance 两个字段。事务 A 要将账户 1 的余额从 1000 元增加到 2000 元，但在操作过程中出现错误需要回滚。在事务 A 回滚之前，事务 B 读取了账户 1 的余额为 2000 元，并基于这个错误的余额进行了后续的业务操作，如给用户发送余额变动提醒等。当事务 A 回滚后，账户 1 的实际余额还是 1000 元，这就导致事务 B 的操作依据了错误的数据。

• SQL 示例

-- 事务 A
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 1;

-- 事务 B 此时读取数据
SELECT Balance FROM Accounts WHERE AccountID = 1;

-- 事务 A 回滚
ROLLBACK;

2. 不可重复读（Non - Repeatable Read）

• 问题描述
  不可重复读是指在同一个事务中，多次读取同一数据时，由于其他事务对该数据进行了修改并提交，导致每次读取的结果不一致。

• 示例场景
  在一个订单处理系统中，事务 A 要统计某个客户的订单总金额。在事务 A 第一次读取订单数据并计算总金额时，事务 B 对该客户的部分订单金额进行了修改并提交。当事务 A 再次读取订单数据计算总金额时，得到的结果与第一次不同，这就影响了事务 A 统计结果的准确性。

• SQL 示例

-- 事务 A
BEGIN TRANSACTION;
SELECT SUM(OrderAmount) FROM Orders WHERE CustomerID = 1;

-- 事务 B 执行更新操作
UPDATE Orders
SET OrderAmount = OrderAmount * 1.1
WHERE CustomerID = 1;
COMMIT;

-- 事务 A 再次读取
SELECT SUM(OrderAmount) FROM Orders WHERE CustomerID = 1;
COMMIT;

3. 幻读（Phantom Read）

• 问题描述
  幻读是指在一个事务中，按照某个条件查询数据时，由于其他事务插入或删除了符合该条件的数据并提交，导致在后续相同条件的查询中，结果集发生了变化，就好像出现了“幻影”数据一样。

• 示例场景
  在一个图书管理系统中，事务 A 要统计所有价格在 50 元以上的图书数量。在事务 A 第一次查询时，得到了一定数量的图书记录。此时，事务 B 插入了几本价格在 50 元以上的图书并提交。当事务 A 再次按照相同条件查询图书数量时，发现结果比第一次多了，这就是幻读现象。

• SQL 示例

-- 事务 A
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Books WHERE Price > 50;

-- 事务 B 插入新记录
INSERT INTO Books (Title, Price) VALUES ('New Book', 60);
COMMIT;

-- 事务 A 再次查询
SELECT COUNT(*) FROM Books WHERE Price > 50;
COMMIT;

4.丢失更新（Lost Update）

• 问题描述
  丢失更新指的是两个或多个事务同时对同一数据进行更新操作，其中一个事务的更新结果被其他事务的更新覆盖，导致部分更新操作丢失，最终数据没有反映出所有事务的更新意图。

• 示例场景

假设在一个库存管理系统中有一个商品表 Products，包含 ProductID 和 StockQuantity 两个字段。现在有两个并发事务对同一商品的库存数量进行更新操作。

• • 事务 A：要将商品 1 的库存数量减少 10 件。
  • 事务 B：要将商品 1 的库存数量减少 5 件。

这些问题会严重影响数据库中数据的一致性和完整性，以及应用程序的正确性和可靠性。事务隔离机制就是为了解决这些问题而设计的，通过不同的隔离级别来平衡并发性能和数据一致性的需求。

事务的隔离级别

级别	名称	隔离级别	脏读	不可重复读	幻读
1	读未提交	read uncommited	是	是	是
2	读已提交	read commited	否	是	是
3	可重复读	repeatable read	否	否	是
4	串行化	serializable	否	否	否
2-3之间	快照	snapshot	否	否	否

dbcc useroptions --查看数据库默认隔离级别

dbcc全称database console command

1. 读未提交（READ UNCOMMITTED）---- 相当于使用with(nolock)

• 特点：这是最低的隔离级别，允许一个事务读取另一个未提交事务修改的数据，会出现脏读、不可重复读和幻读问题。

• 优点：事务之间的阻塞最小，并发性能最高，因为不需要等待其他事务提交。

• 缺点：数据的一致性最差，可能会读取到无效或不一致的数据。

• 适用场景：对数据一致性要求不高，追求高并发性能的场景，如一些统计分析系统，允许读取到暂时的、未确定的数据。

• 使用锁：排他锁（写锁 增删改的数据上的排他锁会在事务结束后才被释放）

• 注意:读未提交允许脏读取，但不允许丢失更新。

• 代码示例：

--用于初始化数据库
drop database Test

create database Test

use Test

create table Account
(
id int primary key,
name varchar(20),
balance int
)

insert into Account
select 1,'zhang',1000
union
select 2,'li',1000

select * from Account

a事务	b事务
set tran isolation level read uncommitted	set tran isolation level read uncommitted
begin tran select * from Account where id=1 结果:是1000
	begin tran update Account set balance=balance-500 where id=1
select * from Account where id=1 结果:b事务的读未提交对该数据添加了排他锁，但是读未提交会绕过排他锁的限制去读取数据，此时就造成脏读取：500
update Account set balance=balance+300 where id=1 结果：因为b事务的读未提交对该数据添加了排他锁，此语句一直不能执行，直至b事务提交或者回滚才会执行此语句，所以不会丢失更新
	commit
select * from Account where id=1 结果:是800

2. 读已提交（READ COMMITTED）

• 特点：这是 SQL Server 的默认隔离级别。一个事务只能读取另一个已经提交事务修改的数据，避免了脏读问题，但仍可能出现不可重复读和幻读问题。

• 优点：在一定程度上保证了数据的一致性，同时并发性能也比较好，因为只需要等待其他事务提交。

• 缺点：在事务执行过程中，多次读取同一数据可能会得到不同的结果。

• 适用场景：大多数业务场景都可以使用这个隔离级别，如一般的业务系统，对数据一致性有一定要求，但可以接受不可重复读和幻读的情况。

• 使用锁：共享锁（读取到数据后会立马释放共享锁，不会等到事务结束），排他锁（增删改的数据上的排他锁会在事务结束后才被释放）

• 代码示例：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Employees;
COMMIT TRANSACTION;

3. 可重复读（REPEATABLE READ） ---- 相当于使用with(HOLDLOCK)

• 特点：保证在一个事务内，多次读取同一行数据的结果是相同的，避免了脏读和不可重复读问题，但仍可能出现幻读问题。在事务执行期间，其他事务不能修改当前事务已经读取的数据。

• 优点：进一步提高了数据的一致性，确保事务内数据的稳定性。

• 缺点：并发性能会有所下降，因为在事务执行期间会对读取的数据加锁，其他事务无法修改这些数据。

• 适用场景：对数据一致性要求较高，需要保证事务内多次读取同一数据结果相同的场景，如财务系统中的数据查询。

• 使用锁：共享锁（对读取的数据所加的共享锁在事务结束后才会被释放），排它锁（增删改的数据上的排他锁会在事务结束后才被释放）

• 代码示例：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Employees WHERE EmployeeID = 1;
-- 可以多次读取同一行数据，结果保持一致
SELECT * FROM Employees WHERE EmployeeID = 1;
COMMIT TRANSACTION;

4. 串行化（SERIALIZABLE）

• 特点：这是最高的隔离级别，完全避免了脏读、不可重复读和幻读问题。它通过对事务操作的数据集加范围锁，确保事务可以串行执行，就像事务是一个接一个依次执行的一样。

• 优点：提供了最高的数据一致性，保证事务执行的结果与串行执行的结果相同。

• 缺点：并发性能最差，因为事务之间会相互阻塞，可能会导致大量的锁等待和死锁问题。

• 适用场景：对数据一致性要求极高，且对并发性能要求不高的场景，如涉及金融交易的核心系统，需要确保数据的绝对一致性。

• 使用锁：共享锁（对读取的数据所加的共享锁在事务结束后才会被释放，还会在查询范围上加上范围锁，防止其他事务插入新的数据），排它锁（增删改的数据上的排他锁会在事务结束后才被释放）

• 代码示例：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Employees WHERE Salary > 5000;
-- 其他事务无法插入、删除或修改符合条件的行
COMMIT TRANSACTION;

5. 快照隔离（SNAPSHOT ISOLATION）

• 特点：这是 SQL Server 提供的一种特殊的隔离级别。当启用快照隔离时，事务读取的是数据在事务开始时的快照，而不是当前数据。这样可以避免脏读、不可重复读问题，并且在一定程度上减少了锁的使用，提高了并发性能。但它仍然可能出现幻读问题（如果使用了读提交快照隔离，还可以进一步减少幻读的影响）。

• 优点：在保证数据一致性的同时，提高了并发性能，减少了锁竞争和死锁的可能性。 因为事务在读取数据时不会持有共享锁，因此不会阻塞其他事务对数据的修改。但是，在更新数据时，仍然需要获取写锁。

• 缺点：需要额外的存储空间来保存数据快照，并且可能会导致长时间运行的事务占用大量资源。

• 适用场景：适用于对并发性能有较高要求，同时需要保证数据一致性的场景，如在线交易系统。

• 启用和使用示例：

-- 首先需要在数据库级别启用快照隔离
ALTER DATABASE YourDatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 在事务中使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Employees;
COMMIT TRANSACTION;

更新冲突检测：当事务尝试更新数据时，SQL Server 会检查自快照创建以来，目标数据是否已被其他事务修改。如果数据已被修改，则会引发更新冲突（Update Conflict），导致事务失败。

幻读情况：
在快照隔离级别下，一个事务在多次执行相同的查询时，可能会看到新插入的行，从而产生幻读。因为快照隔离只是保证了读取到的数据是事务开始时的快照版本，但对于新插入的行，它们在事务开始时并不存在，所以当查询重新执行时，就可能会看到这些新插入的行。

6. 读已提交快照（READ COMMITTED SNAPSHOT）

• 特点：它是基于快照隔离的一种变体，结合了 READ COMMITTED 和快照隔离的特点。在该隔离级别下，事务读取的是已提交事务的最新快照，并且在读取数据时不会阻塞其他事务的更新操作，提高了并发性能。但同样可能存在幻读问题。

• 优点：提高了并发性能，减少了锁等待和死锁的发生，同时保证了读取的数据是已提交的。 事务在读取数据时不会持有共享锁，因此不会阻塞其他事务对数据的修改。但是，在更新数据时，仍然需要获取写锁。

• 缺点：和快照隔离一样，需要额外的存储空间来保存数据快照。

• 适用场景：适用于读多写少的场景，如数据仓库系统。

• 启用和使用示例：

-- 在数据库级别启用读已提交快照
ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON;

-- 在事务中默认使用该隔离级别
BEGIN TRANSACTION;
SELECT * FROM Employees;
COMMIT TRANSACTION;

不同的隔离级别在数据一致性和并发性能之间进行了不同的权衡，你需要根据具体的业务需求来选择合适的隔离级别。

幻读情况：
由于每个语句执行时读取的是已提交数据的快照，新插入的行在当前语句执行时并不会被看到，因此可以避免幻读问题。每个语句都是基于一个一致的快照来执行的，不会受到其他事务插入新行的影响。

43 锁

不同事务隔离级别下共享锁释放时间
读未提交

在 SQL Server 中，锁是一种用于管理并发访问的机制，它确保多个用户或进程在同时访问数据库资源时不会互相干扰，保证数据的完整性和一致性。以下从多个方面详细介绍 SQL Server 中的锁：

锁的类型

共享锁（Shared Locks，S 锁）

• 用途：用于只读操作，多个事务可以同时在同一资源上持有共享锁。例如，当执行 SELECT 语句时，通常会在读取的数据行或页上放置共享锁。

• 示例：多个用户可以同时查询同一张表，因为他们都可以获取该表上的共享锁，互不影响。

• 兼容性：共享锁之间是兼容的，但与排他锁不兼容。也就是说，在一个资源上持有共享锁时，其他事务可以同时获取该资源的共享锁，但不能获取排他锁。也就是加了共享锁的资源其他事务无法写。

排他锁（Exclusive Locks，X 锁）

• 用途：用于数据修改操作，如 INSERT、UPDATE 和 DELETE 语句。当一个事务对某个资源持有排他锁时，其他事务不能再对该资源加任何类型的锁，直到该排他锁被释放。

• 示例：当执行 UPDATE 语句修改某一行数据时，会在该行上放置排他锁，防止其他事务同时修改该行数据。

• 兼容性：排他锁与任何其他类型的锁都不兼容，即一个资源上持有排他锁时，其他事务不能对该资源加共享锁或排他锁。也就是加了排他锁的资源其他事务无法读写。

更新锁（Update Locks，U 锁）

• 用途：主要用于解决在读取数据时可能出现的死锁问题。当一个事务准备更新数据时，会先获取更新锁，而不是直接获取排他锁。如果在后续操作中确实需要更新数据，更新锁会升级为排他锁。

• 示例：在执行 SELECT... FOR UPDATE 语句时，会先获取更新锁，当真正执行更新操作时，更新锁会转换为排他锁。

• 兼容性：更新锁与共享锁兼容，但与其他更新锁和排他锁不兼容。

下面演示不使用更新锁出现死锁的情况

T1	T2
set tran isolation level repeatable read; --设置成可重复读，对读取的数据添加的共享锁会在事务结束后才释放	set tran isolation level repeatable read;
BEGIN TRANSACTION;	BEGIN TRANSACTION;
SELECT balance FROM Account WHERE ID = 1; --添加共享锁	SELECT balance FROM Account WHERE ID = 1;
UPDATE Account SET balance = balance + 300 WHERE ID = 1; --不能执行，因为T2给该条记录添加的共享锁还未释放，所以不能将该条记录的共享锁升级为排他锁
	UPDATE Account SET balance = balance + 300 WHERE ID = 1; --同理，不能执行。此时发生死锁，T1和T2都互相等待对方释放该条记录的共享锁。五秒后
COMMIT;	COMMIT;
SQL Server 具备内置的死锁检测和处理机制，死锁检测每隔 5 秒进行一次，当死锁被检测到后，它会挑选一个事务作为“死锁牺牲品”。

下面演示如何使用更新锁避免出现死锁的情况

T1	T2
set tran isolation level repeatable read; --设置成可重复读，对读取的数据添加的共享锁会在事务结束后才释放	set tran isolation level repeatable read;
BEGIN TRANSACTION;	BEGIN TRANSACTION;
SELECT balance FROM Account with(updlock) WHERE ID = 1; --添加更新锁
	SELECT balance FROM Account with(updlock) WHERE ID = 1; --不能添加更新锁，会在这一步处于等待状态，因为更新锁与其他更新锁和排他锁不兼容
UPDATE Orders SET OrderAmount = OrderAmount * 1.1 WHERE OrderID = 1; --更新锁升级为排他锁
COMMIT;
	此刻，上一步成功添加更新锁
	UPDATE Orders SET OrderAmount = OrderAmount * 1.1 WHERE OrderID = 1; --更新锁升级为排他锁
	COMMIT;

意向锁（Intent Locks）

• 用途：用于表明某个事务正在或将要在更低层次的资源上获取共享锁或排他锁。意向锁有三种类型：意向共享锁（IS）、意向排他锁（IX）和共享意向排他锁（SIX）。

• 示例：如果一个事务要在表中的某一行上获取排他锁，它会先在表上放置意向排他锁，以表明后续会在表的某个行上获取排他锁。

• 兼容性：意向锁主要用于提高锁的管理效率，其兼容性规则较为复杂，总体原则是保证不同层次资源上的锁之间不会产生冲突。

示例

begin tran
--对Id='3A47572E-ACC6-8802-BDDA-3A02F9FDC7CD'的行添加排他锁
select * from AppBooks with(xlock) where Id='3A47572E-ACC6-8802-BDDA-3A02F9FDC7CD'

begin tran
select * from AppBooks with(tablock)

理解:

假设session1先执行,session2后执行.session2执行时愈加表锁,数据库系统需要逐条判断table表每行记录是否有排他锁,如果发现其中一条有排他锁就不允许再加表锁,这样逐条判断效率太低了.

实际上数据库不是这样工作的,当session1的select执行时,系统对Id='3A47572E-ACC6-8802-BDDA-3A02F9FDC7CD'的行添加了排他锁,同时还悄悄地对整个表添加了意向排他锁(IX),当session2执行表锁时,只要看到这个表已经有意向排他锁了,就直接等待,而不会对表逐条检查是否有排他锁.

意向锁有三种:意向共享锁(intend shared lock),简称IS锁;意向排他锁(intend exclusive lock IX);共享意向排他锁(shared intend exclusive lock SIX),表示某个事物要读取整个表,并更新其中的某些数据.

Others

共享锁是什么时候释放？
事务隔离级别	释放时机
读未提交	不使用共享锁
读已提交	读取时加锁，读取后立即释放
可重复读	整个事务结束后释放
串行化	整个事务结束后释放

在任何事物隔离级别下，排他锁都是在整个事务结束后释放。

锁的粒度

锁的粒度指的是锁所作用的数据库资源的大小，为了减少锁定的开销,数据库引擎自动将资源锁定在适合任务的级别.锁定在较小粒度(如行)能提高并发度,但开销较高,因为若锁定了很多行,就需要有更多的锁;锁定在较大粒度(如表)会降低并发,因为锁定整个表限制了其他事务对整个表其他部分的访问,但其开销较低,因为需要维护的锁较少。SQL Server 支持多种锁粒度，从大到小依次为：

• 数据库锁：对整个数据库加锁，这种锁粒度最大，会影响到数据库中的所有操作，通常在进行数据库备份等操作时使用。

• 表锁：对整个表加锁，会影响到该表中的所有行和页。当对表进行批量操作时，可能会使用表锁。

• 页锁：对数据库中的一个页（通常为 8KB）加锁，页是数据库存储的基本单位之一。

• 行锁：对表中的某一行数据加锁，这种锁粒度最小，对并发的影响也最小，是最常用的锁粒度之一。

select * from user where username="zzz" 查询是表锁还是行锁 ？

• 有合适索引：如果 username 列上有唯一索引或者非聚集索引，查询优化器可以利用索引快速定位到 username 为 "zzz" 的行，此时通常会使用行锁。
• 无合适索引：当 username 列没有索引时，数据库可能需要进行全表扫描来查找符合条件的行。在全表扫描的过程中，为了保证数据的一致性，可能会使用表锁。

锁的模式

乐观锁

• 原理：乐观锁假设在大多数情况下，多个事务不会同时修改同一数据，因此在读取数据时不会加锁。在更新数据时，会检查数据是否在读取之后被其他事务修改过，如果没有被修改，则允许更新；如果被修改过，则更新失败，需要重试。

• 实现方式：通常通过在表中添加一个版本号或时间戳列来实现。在读取数据时，同时读取版本号或时间戳；在更新数据时，比较当前版本号或时间戳与读取时的值是否相同，如果相同则更新数据并更新版本号或时间戳，否则更新失败。

悲观锁

• 原理：悲观锁假设在大多数情况下，多个事务会同时修改同一数据，因此在读取数据时就会加锁，以防止其他事务对数据进行修改。

• 实现方式：通过在 SQL 语句中使用 WITH (HOLDLOCK) 等提示来显式地加锁，或者让 SQL Server 根据事务的隔离级别自动加锁。

死锁

• 定义：死锁是指两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，导致这些事务都无法继续执行下去。

• 示例：事务 A 持有资源 X 的锁，请求资源 Y 的锁；而事务 B 持有资源 Y 的锁，请求资源 X 的锁，这样就形成了死锁。

• 解决方法：SQL Server 会自动检测死锁，并选择一个事务作为死锁牺牲品，将其回滚，以释放锁资源，让其他事务能够继续执行。为了减少死锁的发生，可以优化事务的执行顺序，尽量缩短事务的执行时间，避免长时间持有锁等。

查看和监控锁

可以使用 SQL Server 提供的动态管理视图（DMV）来查看和监控锁的信息，例如：

• sys.dm_tran_locks：返回有关当前活动锁的详细信息，包括锁的类型、资源、持有锁的事务等。

• sys.dm_os_waiting_tasks：返回有关当前正在等待资源的任务的信息，可用于排查锁等待问题。

以下是一个简单的查询示例，用于查看当前活动的锁信息：

SELECT 
    resource_type,
    resource_database_id,
    resource_description,
    request_mode,
    request_status,
    request_session_id
FROM 
    sys.dm_tran_locks;

通过对 SQL Server 中锁的类型、粒度、模式等方面的了解，可以更好地管理数据库的并发访问，提高数据库的性能和可靠性。

演示

T1	T2
set tran isolation level read committed	set tran isolation level read committed
begin tran select * from Account (holdlock) where id=1 --id未主键，所以此时共享锁的粒度是行锁
	begin tran update Account set balance=500 where id=2 --此更改能够立即执行,因为T1事务仅在id=1的行添加了共享锁
	update Account set balance=500 where id=1 --此更改不能立即执行

T1	T2
set tran isolation level read committed	set tran isolation level read committed
begin tran select * from Account (holdlock) --此时共享锁的粒度是表锁
	update Account set balance=500 where id=1 --也不能立即执行,需要等T1事务执行完成
	update Account set balance=500 where id=2 --也不能立即执行,需要等T1事务执行完成

44.表提示

在 SQL Server 中，表提示（Table Hints）是用于在查询中指定 SQL Server 数据库引擎如何访问表或视图的指令。以下是一些常用的表提示：

锁定提示

1. HOLDLOCK

• 作用：它会在事务期间保持共享锁，防止其他事务修改或删除当前事务读取的数据，从而避免脏读、不可重复读问题。

• 示例：

SELECT *
FROM Orders WITH (HOLDLOCK)
WHERE CustomerID = 123;

2. ROWLOCK

• 作用：指示 SQL Server 使用行级锁而不是页级锁或表级锁，这样可以减少锁的粒度，提高并发性能。

• 示例：

UPDATE Products WITH (ROWLOCK)
SET Price = Price * 1.1
WHERE CategoryID = 5;

当 CategoryID 不是索引列时，SQL Server 在执行 WHERE CategoryID = 5 这个条件的查询时，很可能会使用表扫描（Table Scan）操作。表扫描意味着 SQL Server 会逐行检查表中的每一行数据，以找出满足条件的行。

在表扫描过程中，SQL Server 为了提高效率和减少锁的开销，可能不会按照 ROWLOCK 提示去使用行级锁，而是使用更粗粒度的锁，如页级锁或表级锁。这是因为在表扫描时，如果对每一行都加行级锁，会带来大量的锁管理开销，影响性能。

在 SQL Server 中，ROWLOCK 提示本身并不明确指定是行级的哪种锁，它只是建议数据库引擎使用行级锁，具体使用的锁类型会根据不同的操作和事务隔离级别而有所不同。以下是常见操作下可能涉及的行级锁类型：

读取操作（SELECT）

• 共享锁（Shared Locks，S 锁）

• • 当在 SELECT 语句中使用 ROWLOCK 提示时，通常会对读取的行加共享锁。例如，在默认的 READ COMMITTED 隔离级别下执行如下语句：

SELECT * FROM YourTable WITH (ROWLOCK) WHERE SomeColumn = 'SomeValue';

修改操作（INSERT、UPDATE、DELETE）

• 排他锁（Exclusive Locks，X 锁）

• • 对于 INSERT、UPDATE 和 DELETE 操作，使用 ROWLOCK 提示时，一般会对涉及的行加排他锁。例如：

UPDATE YourTable WITH (ROWLOCK) SET SomeColumn = 'NewValue' WHERE AnotherColumn = 'Condition';

• 更新锁（Update Locks，U 锁）

• • 在某些情况下，特别是在可能会升级为更新操作的读取阶段，会使用更新锁。更新锁是为了防止常见的死锁情况。例如，当一个事务先读取数据，之后可能会对其进行更新时，会先加更新锁。如果后续确实需要更新数据，更新锁会转换为排他锁。在使用 ROWLOCK 提示且存在这种潜在更新操作的场景下，会应用行级的更新锁。例如：

-- 先查询，可能后续要更新
SELECT * FROM YourTable WITH (ROWLOCK, UPDLOCK) WHERE SomeColumn = 'SomeValue';
-- 后续可能的更新操作
UPDATE YourTable SET AnotherColumn = 'NewValue' WHERE SomeColumn = 'SomeValue';

不同的事务隔离级别会影响 ROWLOCK 实际使用的锁类型和锁的持续时间。例如，在 SERIALIZABLE 隔离级别下，除了上述锁类型外，还可能会使用范围锁（Range Locks）来防止幻读，即使使用了 ROWLOCK 提示，锁的行为也会有所不同，会对一定范围内的行进行锁定，保证事务的串行执行。

3. TABLOCK

• 作用：要求 SQL Server 在表上获取共享锁或排他锁，直到包含该语句的事务完成。使用 TABLOCK 会增加锁的粒度，可能会降低并发性能，但可以减少锁的开销。

• 示例：

DELETE FROM Employees WITH (TABLOCK)
WHERE DepartmentID = 10;

4. TABLOCKX

• 作用：获取表的排他锁，防止其他事务读取或修改该表，直到当前事务完成。通常用于需要对整个表进行独占访问的操作。

• 示例：

BEGIN TRANSACTION;
SELECT *
FROM Customers WITH (TABLOCKX)
WHERE Country = 'China';
-- 执行其他操作
COMMIT TRANSACTION;

索引提示

1. INDEX(index_name)

• 作用：强制 SQL Server 使用指定的索引来访问表。这在 SQL Server 优化器选择了不合适的索引时非常有用。

• 示例：

SELECT *
FROM Sales WITH (INDEX(idx_Sales_Date))
WHERE SaleDate BETWEEN '2025-01-01' AND '2025-03-01';

2. FORCESEEK

• 作用：强制 SQL Server 使用索引查找操作来访问表，而不是索引扫描或表扫描。这可以提高查询性能，特别是在处理大型表时。

• 示例：

SELECT ProductName
FROM Products WITH (FORCESEEK)
WHERE ProductID = 50;

其他提示

1. NOLOCK

• 作用：允许在读取数据时不获取共享锁，从而避免阻塞其他事务。但可能会读取到未提交的数据，即脏读。

• 示例：

SELECT *
FROM Inventory WITH (NOLOCK)
WHERE ItemCount < 10;

2. READPAST

• 作用：跳过被其他事务锁定的行，直接读取下一个未锁定的行。这可以提高并发性能，特别是在处理队列或高并发事务时。

• 示例：

SELECT *
FROM Orders WITH (READPAST)
WHERE Status = 'Pending';

3. UPDLOCK

• 作用：在读取数据时获取更新锁，而不是共享锁。更新锁可以防止其他事务对数据进行更新，同时允许其他事务读取数据，从而减少死锁的可能性。

• 示例：

SELECT Quantity
FROM Stock WITH (UPDLOCK)
WHERE ProductID = 789;

需要注意的是，表提示应该谨慎使用，因为不正确的使用可能会导致性能下降或出现数据不一致的问题。在大多数情况下，SQL Server 优化器能够自动选择最优的执行计划，只有在必要时才需要使用表提示。

----------------------------华丽的分割线-----------------------------

SQL Server拓展知识

1.将一个表的结构复制到另一个新表中

select * into newSysUser from sysUser where 1=2
--where 1=2:不复制原表中的数据,只赋值结构

--原表中的约束条件(主键,外键,唯一,check,default)及其索引是没有复制到新表newSysUser 中

2.给列名别名的三种方式

select userno c1,UserNO as c2, c3=UserNO from aaa_user
 

3.exists 和 in 的使用

实例:

select * from aaa_user u 
where exists(select DeptCode from aaa_dept d where d.DeptCode=u.DeptCode)

select * from aaa_user u 
where u.DeptCode in(select DeptCode from aaa_dept d)

--这两个sql语句的执行结果是相同的,执行计划中的实际执行方式也是相同的

上面两个语句的执行计划:

编辑

其中hash match(right semi join)的使用可以高效的解决exists 和 in的子查询.

hash match(right semi join)的具体关联过程不知道博友是否知道,希望能有博友在评论区解释一下,小弟不胜感激!

4.数据库表格标椎化设计

在设计数据库表格之前我们要确保我们的设计是最优的,不存在冗余或者重复的数据,因为重复的数据增加了数据库的大小,而且会使插入,更新和删除操作复杂化. 比如:一个人的名字在许多不同的地方重复出现,然后此人决定改名,我们需要更新几个地方,否则数据会不一致,这就是标椎化意义的所在.
标椎化是审查我们的设计,确保它们遵循一些防止数据重复的预定义规则的过程. 基本上有七条规则,被称为七范式,对于几乎99%的应用场景我们只需要遵循前三条就可以了

第一范式:第一范式要求一行中的每个单元格都应该有单一值,且不能出现重复列. 比如courses表中的tag列就不满足第一范式,因为一个课程的标签可能会有多个,同时也不能建立tag1,tag2,tag3等多个标签列,这样会出现重复列
解决方法:为标签单独建立一个tags表与courses表形成多对多的关系,然而在关系型数据库中并没有多对多的关系,只有一对一,一对多的关系,所以为了实现courses和tags两表的多对多关系我们引入一个新的表(course_tags)被称之为链接表.有了链接表之后我们的设计就满足了第一范式,courses表中没有tag1,tag2,tag3等重复的列,然后在一列中也没有多个值,更改标签名字的时候只需要更改tags表中的一条记录就可以了
相反,之前的设计中,coures表中的每个标签会重复出现好多次,如果更改标签时就要更改多条记录

第二范式:第二范式要求每张表都应该有一个单一目的,换句话说,它只能代表一种且仅一种实体类型,那个表中的每一列都应该是描述这个实体.(第二范式满足的前提是第一范式满足)
根据第二范式如果一个表中有一列不是描述该表所代表的实体,就应该拿掉它并单独放入一张表中
比如现在course表里有course_id,title,price,instructor列,那么这个course表就不满足第二范式,因为这里的讲师列不属于这张表,如果同一个讲师教多门课,他的名字就会在这张表中重复出现,所以我们必须在好多个地方进行更改

第三范式:第三范式表示表中的列不应派生自其它的列(第三范式实现的前提是第二范式必须已经实现)
比如:invoices中有invoice_total,payment_total,balance,如果我们用发票总计(invoice_total)-付款总计(payment_total)=结余(balance),比如:100-20=80 , 当我们改变payment_total为40而忘记了更新结余,我们的数据就不一致了
比如:students表中有first_name,last_name,full_name, 那么full_name(first_name+last_name)就不满足第三范式,应该把他取消掉. 所以第三范式和其他范式一样减少重复,并提高数据的完整性

作者建议:不要刻意记忆这些范式,并刻意关注表的设计不满足哪一个范式,只需要专注于消除冗余就可以了,不需要对每张表每个列逐个应用标准化规则,所以当你看到重复值,且这些值不是像1.2.3这样的外键时,这就意味着你的设计没有标准化, 至于它违背了第几范式并不重要,还是想想如何消除重复数据

• 1NF：确保每一列都是原子的，不可再分。
• 2NF：确保每一列完全依赖于主键，不存在部分依赖。
• 3NF：确保每一列不依赖于其他非主键列，不存在传递依赖

5.系统对象(列,约束,索引,表,视图,触发器,存储过程,函数,事务)

5.1系统内置全局变量

@@error:返回执行最后一个T-SQL语句的错误号(没有错误返回0)

@@rowcount:返回被最后一个T-SQL语句影响的行数(没有影响返回0)

@@identity:返回最后插入的标识列的值(在一个连接中一直没有插入返回null)

上面的全局变量的作用域都是在一个连接中

5.2系统内置存储过程

sp_helptext

exec sp_helptext viewName  --查看创建视图的sql
exec sp_helptext procName  --查看创建存储过程的sql

sp_executesql

实例1:

DECLARE @UserNoVariable NVARCHAR(500)    
DECLARE @SQLString NVARCHAR(500) 
DECLARE @ParmDefinition NVARCHAR(500)  
  
/* Build the SQL string one time.*/  
SET @SQLString =  N'SELECT * FROM usyUser WHERE UserNO=@UserNo';  
SET @ParmDefinition = N'@UserNo NVARCHAR(250)';  
/* Execute the string with the first parameter value. */  
SET @UserNoVariable = 'F2847958';  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @UserNo = @UserNoVariable;  
/* Execute the same string with the second parameter value. */  
SET @UserNoVariable = 'X2000274';  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  @UserNo = @UserNoVariable;

实例2:

DECLARE @UserNoVariable VARCHAR(30)    
DECLARE @SQLString NVARCHAR(500)  
DECLARE @ParmDefinition NVARCHAR(500) 
DECLARE @userNameVariable VARCHAR(30)  
 
SET @SQLString =  N'SELECT @userName=userName FROM usyUser WHERE UserNO=@UserNo'  
SET @ParmDefinition = N'@UserNo NVARCHAR(250),@userName NVARCHAR(250) OUTPUT'  
SET @UserNoVariable = 'F2847958'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @UserNo=@UserNoVariable , @userName=@userNameVariable OUTPUT;  
SELECT @userNameVariable

5.3系统表

select * from sysobjects

5.4 系统内置函数

CHECKSUM : 可以用于比较两条记录是否一致,如下,选出A表在B表中不存在的数据

select * from A
WHERE checksum(*) not in (select checksum(*) from B)

PIVOT

DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParaDefinition NVARCHAR(500);  
DECLARE @Variable NVARCHAR(20);  

SET @SQLString = 'SELECT  * FROM usyUser WHERE UserNO=@UserNo'
SET @ParaDefinition = '@UserNo NVARCHAR(20)'
SET @Variable='F2847958'

EXEC SP_EXECUTESQL @SQLString,@ParaDefinition,@UserNo=@Variable

DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParaDefinition NVARCHAR(500);  
DECLARE @Variable NVARCHAR(20);  
DECLARE @UserName NVARCHAR(20);

SET @SQLString = 'SELECT  @UserNameOut=UserName FROM usyUser WHERE UserNO=@UserNo';
SET @ParaDefinition = '@UserNo NVARCHAR(20),@UserNameOut NVARCHAR(20) OUTPUT';
SET @Variable='F2847958';

EXEC SP_EXECUTESQL @SQLString,@ParaDefinition,@UserNo=@Variable,@UserNameOut=@UserName OUTPUT;
PRINT @UserName

5.运算符

rollup 运算符
with rollup只能应用于聚合函数的列(因为非聚合函数的列加起来没意义),可以对整个聚合函数列求和,并且其运算符要位于group by子句之后,
select salesPersonId,sum(salesVolume) as totalSales from employeePerformanceTable
where salesPersonId>20210101
group by salesPersonId with rollup
having sum(salesVolume)>1000

6.查询优化

30种sql语句优化

推荐阅读:

索引统计信息
索引统计信息 | Microsoft Docs

查询优化建议
查询优化建议 | Microsoft Docs

用于对运行慢的查询进行分析的清单
用于对运行慢的查询进行分析的清单 | Microsoft Docs

逻辑运算符和物理运算符引用
逻辑运算符和物理运算符引用 | Microsoft Docs

认识优化查询中的Merge Join、Nested Loops和Hash Match
 

7.使用通用表表达式（CTE）实现递归查询

WITH RecursiveCTE AS (
    -- 初始查询
    SELECT ID, ParentID, Name
    FROM TableName
    WHERE ParentID IS NULL

    UNION ALL

    -- 递归部分
    SELECT t.ID, t.ParentID, t.Name
    FROM TableName t
    INNER JOIN RecursiveCTE cte ON t.ParentID = cte.ID
)
SELECT * FROM RecursiveCTE;

当使用上面提供的 SQL 查询来实现递归查询时，SQL Server 的执行流程通常如下：

1. 执行初始查询部分：首先，数据库引擎会执行CTE中定义的初始查询部分，即选择满足条件的起始记录（在示例中是 ParentID IS NULL 的记录）作为起点。
2. 执行递归部分：一旦执行完初始查询部分，数据库引擎会按照递归关系继续执行递归部分，即根据已经选定的记录，继续向下选择满足条件的子记录，直到满足递归结束条件。
3. 合并结果集：数据库引擎会将每次迭代得到的结果集合并起来，形成最终的递归查询结果。
4. 返回查询结果：最终，数据库引擎将整个递归查询的结果返回给用户。

需要注意的是，递归查询的效率可能会受到数据量和层级深度的影响，因此在进行复杂的递归查询时，建议对数据表的索引和设计进行优化，以提高查询性能。

Joins

在 SQL Server 中，连接（Joins）是用于将两个或多个表中的行组合起来的操作，常见的连接方式有 Nested Loops Joins，Hash Joins 和 Merge Joins
 

连接方式	适用数据量	连接条件	索引要求	资源消耗
Nested Loops Joins	小数据集	等值连接、范围连接等	内部表有合适索引时性能好	数据量大时磁盘 I/O 和 CPU 消耗高
Hash Joins	大数据集	等值连接	无特殊要求	内存消耗大
Merge Joins	大数据集	等值连接	连接列有序或可快速排序	排序阶段资源消耗大

如何在 SQL Server 的存储过程和函数里进行错误处理（try catch）

-等学完这个sql后再看

sql语句执行顺序

编辑

上图是逻辑执行顺序,实际的物理执行顺序不是这个样子的.

我们在开发过程，或多或少都会遇到锁表情况，特别是数据量大的时候..碰到经理客户都在催的时候，我们就需要尽快找到锁表并且解锁。

方法/步骤 1 ---查看锁表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'

---spid 锁表进程 ---tableName 被锁表名

---解锁语句 declare @spid int Set @spid = 136 --锁表进程 declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)exec(@sql)

WITH result AS的作用是

WITH result AS 是 SQL 查询中用于定义 CTE（Common Table Expression，公用表达式）的语句，其作用是为查询语句创建一个临时的命名结果集，可以在同一查询中多次引用该结果集。

使用 WITH result AS 语法，查询的 FROM 子句中可以使用一个或多个公用表达式，每个公用表达式之间用逗号 (,) 分隔，并将它们与主查询中的其他表组合起来进行查询。

有两种类型的 CTE：递归与非递归。非递归 CTE 对于某些查询尤其有用，可以明确查询的基础数据，并允许我们为简化查询逻辑而创建可重用的代码。递归 CTE 则用于处理树形数据结构的查询。

下面是一个简单的例子，使用 WITH result AS 语法：

WITH result AS (
  SELECT 
    customer_id,
    SUM(amount) as total_spent
  FROM 
    orders 
  GROUP BY 
    customer_id
  HAVING 
    SUM(amount) > 5000
)
SELECT
  customers.name,
  result.total_spent
FROM
  result
JOIN
  customers
ON
  customers.id = result.customer_id;

在这个例子中，首先定义了一个名为 result 的公用表达式，其中使用 orders 表中的数据计算了每个客户的总支出，并筛选出支出超过 5000 的客户。然后，主查询从 result 中检索出 customer_name 和 total_spent 列，并与 customers 表中的数据组合，返回结果集。

通过 WITH result AS 语法，可以简化查询，使代码更易于阅读。同时，如果多个查询使用相同的公用表达式，可以避免重复编写代码，提高代码的可重用性。

CTE递归代码示例

以下是一个 CTE 递归代码的示例，用于处理树形数据结构的查询：

假设有一个名为 Employee 的表，其中包含员工的 ID、姓名和经理的 ID。我们要使用递归 CTE 查询每个员工及其所有下属的层级关系：

-- 创建 Employee 表并插入示例数据
CREATE TABLE Employee (
  ID INT,
  Name VARCHAR(50),
  ManagerID INT
);

INSERT INTO Employee (ID, Name, ManagerID) VALUES
(1, 'John', NULL),
(2, 'Sarah', 1),
(3, 'Michael', 1),
(4, 'Emily', 2),
(5, 'David', 2),
(6, 'Jessica', 3),
(7, 'Daniel', 3),
(8, 'Olivia', 4),
(9, 'Sophia', 4);

-- 使用递归 CTE 查询员工层级关系
WITH RecursiveCTE AS (
  SELECT 
    ID,
    Name,
    ManagerID,
    0 AS Level -- 初始化层级为0
  FROM 
    Employee
  WHERE 
    ManagerID IS NULL -- 根节点

  UNION ALL

  SELECT 
    e.ID,
    e.Name,
    e.ManagerID,
    rc.Level + 1 -- 递增层级
  FROM 
    Employee e
  INNER JOIN 
    RecursiveCTE rc ON e.ManagerID = rc.ID -- 递归查询下属
)
SELECT 
  ID,
  Name,
  ManagerID,
  Level
FROM 
  RecursiveCTE
ORDER BY 
  Level, ID;

通过以上代码，我们使用 RecursiveCTE 作为递归 CTE 的名称，并在其定义中设置了两个部分。第一个部分是初始查询，从根节点开始选择。第二个部分是递归查询，将 CTE 自身与表连接，并递增层级进行递归查询，直到没有下属为止。

最后，我们从 RecursiveCTE 中选择需要的列，并按层级和 ID 进行排序，以展示员工的层级关系。

这个示例演示了如何使用递归 CTE 来查询树形数据结构中的数据，通过不断连接自身进行递归查询，可以方便地获取每个节点及其下属的层级关系。请注意，递归 CTE 的性能可能会受到数据量和层级深度的影响，请根据实际情况进行优化和调整。