SQLserver

学习路径：该路径引自《Sql Server编程自学与面试指南》最佳学习路线图（2020最新版）_sqlserver学习路线-优快云博客

01、SqlServer概述 

02、环境搭建

03、创建库和使用数据库

04、创建表和使用表

05、数据类型详解

06、主键，外键，约束

07、Transact-SQL

08、高级查询

09、常量，变量，函数

10、游标

11、事务

12、索引

13、视图

14、存储过程

15、触发器

16、SQL性能优化

三、创建库和使用数据库

引用​​​​​​SQL Server数据库 -- 库的创建与管理_sqlserver创建数据库-优快云博客，看前三章即可，讲的很详细，这里不再赘述

补充：

1、文件类型

mdf主文件、ndf次要文件、ldf日志文件

2、查看数据库

exec sp_helpdb Student;

3、备份与还原（以防删库跑路）（比如测试环境需要发布到生产环境）

备份：backup database 数据库名称 to disk = 'D:\文件名.bak';

backup database Student to disk = 'D:\Student_bak.bak';

此时如果把数据库删了，则可以通过还原

还原：restore database 数据库名称 from disk = 'D:\文件名.bak';//with replace如果数据库已存在，则替换

restore database Student from disk = 'D:\文件名.bak' with replace;

4、附加和分离

分离：不删除数据库文件，但是可以把数据库从DBMS中移除掉

exec sp_detach_db 'Student';

附加：把数据重新添加到DBMS中  

exec sp_attach_db 'Student','D:\Student.mdf';    --D:\Student.mdf，从这个文件附加回去

四、创建表和使用表

引用SQLServer 数据表的创建、修改和删除（含实例）_sqlserver删除表-优快云博客

补充：

1、表的操作

创建表

create table dbo.teacher(
		teach_id char(9) primary key,
		teach_name varchar(20) not null,
		teach_sex char(2),
		teach_rank varchar(10),
		grade smallint check(grade>=0 and grade<=100),
		dept_id char(4) foreign key references department(dept_id) 
)

删除表

drop table student

修改表名

exec sp_rename 'student','student1'

2、字段的操作

添加字段 ALTER TABLE 表名 ADD 列名 数据类型 [约束];

alter table student add test1 varchar(20)

删除字段 ALTER TABLE 表名 DROP COLUMN 列名;

alter table student drop column test2

修改字段名 EXEC sp_rename '表名.原列名', '新列名', 'COLUMN';

exec sp_rename 'student.test1','test2','column'

修改字段数据类型  ALTER TABLE 表名 ALTER COLUMN 列名 新数据类型;

alter table student alter column test2 char(10) not null

五、数据类型详解

引自 https://wenku.youkuaiyun.com/column/38h2d78dok?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522171420391716800225538410%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=171420391716800225538410&biz_id=&utm_medium=distribute.pc_search_result.none-task-wk_aigc_column-2~all~first_rank_ecpm_v1~rank_v31_ecpm-3-38h2d78dok-null-null.142^v100^pc_search_result_base6&utm_term=sqlserver%20%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B%E8%AF%A6%E8%A7%A3&spm=1018.2226.3001.4187讲解的很详细

六、约束

引自 SQL sever中的约束_sql server 约束-优快云博客

补充：

1、级联删除、级联修改

一般搭配外键，删除外键表的数据时也会删除基础表中的外键的数据。在创建外键约束时指定ON DELETE CASCADE选项和ON UPDATE CASCADE

1、CREATE TABLE Orders (  
    OrderID INT PRIMARY KEY,  
    CustomerID INT,  
    OrderDate DATE,  
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE  
);
2、
ALTER TABLE Orders  
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)   
REFERENCES Customers(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

七、查询

1、分页查询

        row_numebr() over(order by )

        或者

        筛选出第3-6位的数据

select * 
from student 
order by id 
offset 2 rows fetch next 4 rows only

八、索引

正式：

1、常见数据结构

1.1、链表

链表类型

单向链表 

• 每个节点有一个数据部分和一个指向下一个节点的指针。
• 没有指向前一个节点的指针，所以遍历只能从头节点到尾节点。

双向链表 

• 每个节点有一个数据部分、一个指向下一个节点的指针和一个指向前一个节点的指针。
• 可以在任意位置进行双向遍历。

循环链表 

• 与单向链表类似，但尾节点的指针指向头节点，形成一个环。
• 可以从头节点或尾节点开始遍历。

基本操作：

插入：在链表的特定位置（头部、尾部或指定位置）添加一个新节点（速度快）

删除：从链表中删除特定位置的节点（速度快）

查找：遍历链表以查找具有特定数据的节点

遍历：从头节点开始，访问链表中的每个节点，直到尾节点

1.2、二叉搜索树

二叉树中的一种，存入的数据以第一条数据为基准，小于放左、大于放右

1.3、平衡二叉树

定义：任意节点的左右子树高度差不大于1

目的：平衡二叉树的设计目的是为了解决普通二叉搜索树在插入、删除等操作时产生的不平衡问题，这些问题可能导致树的高度过高，从而影响搜索的效率

二叉树练习网址：https://www.cs.usfca.edu/～galles/visualization/AVLtree.html

1.4、红黑树

红黑树是平衡二叉树的变种，它的左右子树高差有可能大于1

参考（讲的很细，只看三、红黑树的特性 即可） 【数据结构】史上最好理解的红黑树讲解，让你彻底搞懂红黑树-优快云博客

1.5、B树

也叫B-树，B（balence），平衡树

参考 二叉排序树、平衡二叉树、红黑树、B树、B+树_二叉树 平衡二叉树 红黑树-优快云博客

1.6、B+树

索引用B+树

2、索引

2.1 索引类型

聚合索引，非聚合索引，（不常用的还有空间索引、筛选索引、XML索引）

由于聚集索引，类似新华字典的字母排序，物理顺序和逻辑顺序一样（比如自增的主键），所以存的是真实的数据值

非聚合索引类似新华字典的偏旁部首查询，存的是指向数据的指针，然后通过指针找数据

2.2 索引设计原则

问题一：是不是索引越多越好？肯定不是，一般两三个足矣

索引也是需要空间存储的，索引太多意味着占用的空间越多

索引页也需要系统维护，在增删改数据时索引需要重新编排

索引堆积久了，由于维护数据过程中会产生过多的索引碎片，反而不利于数据查询

问题二：什么情况可以建立索引？

主键和外键一定要建、经常查询的列、经常用作查询条件的列、经常跟在distinct、group by、order by后面的列

重复值多的列不要建、对于text、image、bit这些类型列不能建索引、经常存取的列不要建

补充：

1、 索引类型

• 聚集索引：每个表只能有一个聚集索引（如果有，一般是主键）。它决定了表中数据的物理排序顺序。

CREATE CLUSTERED INDEX idx_users_email ON users (email);

• 非聚集索引：一个表可以有多个非聚集索引。它不与数据的物理排序顺序相关，但包含指向表中数据的指针。也可以不写NONCLUSTERED ，默认就是非聚集索引

CREATE NONCLUSTERED INDEX idx_users_email ON users (email);

• 唯一索引：确保索引列中的值唯一。主键和唯一约束会自动创建唯一索引。

CREATE UNIQUE INDEX idx_unique_email ON users (email);

• 复合索引：基于多个列创建的索引，可以加快多个列的查询速度。

CREATE INDEX idx_unique_email ON users (user,email);

 2、索引操作

1、创建索引

CREATE INDEX index_name   
ON table_name (column1, column2, ...);

ALTER TABLE table_name   
ADD INDEX index_name (column1, column2, ...);

2、删除索引

DROP INDEX idx_unique_email ON users;

3、索引维护

随着时间的推移，索引可能会变得碎片化，导致查询性能下降。此时，我们可以使用ALTER INDEX语句来重新组织或重建索引。

重新组织索引

ALTER INDEX idx_users_email ON users REORGANIZE;

重建索引

ALTER INDEX idx_users_email ON users REBUILD;

4、查看索引

exec sp_helpindex 'table_name'

5、重命名索引

exec sp_rename 'table_name.index_name','new_index_name','index'

九、视图

只存放SQL的定义，不存放对应的数据

1、创建视图

create view v_view
as
select * from student

2、修改视图

alter view v_view
as
select * from student

3、删除视图

drop view v_view

4、视图加密与解密（保护我的视图不被别人看到源码）

以修改为例

alter view v_view
with encryption
as
select * from student

解密则把with encryption去掉即可

 （基础）引自 SQL Server 数据库之视图_sql server视图-优快云博客

（简单练习）引自 【SQL Server中的视图】_sqlserver创建视图-优快云博客

十、事务

1、事务简介

1.1、事务创建及成功提交

  begin transaction			--开启事务
  update student set name = 'kawa2' where id = 2
  update student set name = 'kawa3' where id = 3
  commit					--提交

1.2、事务创建及失败提交需回滚

begin transaction				--开启事务
begin try 	  
	  update student set name = 'kawa2' where id = 2
	  update student set name = 'kawa3' where id = 3
	  commit					--提交
end try
begin catch
	   rollback					--如果失败则回滚
end catch

2、事务的四大特性（ACID）

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改要么全都执行，要么全都不执行。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。也就是说，一个事务执行之前和执行之后都必须处于一致性状态。
• 隔离性（Isolation）：在事务开始和完成时，中间过程对系统其他事务是不可见的。当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）：一旦事务提交，则其结果就是永久性的，即使系统发生故障也不会丢失。

3、事务的隔离

3.1、事务不隔离导致的问题

3.1.1、更新丢失

• 问题描述：一个事务的更新覆盖了另一个事务的更新。这本质上是一种写操作的冲突。
• 示例：两个事务同时读取同一数据项的值，然后基于该值进行计算并尝试更新。如果第一个事务的更新在第二个事务的读取和更新之间提交，那么第二个事务的更新将覆盖第一个事务的更新。
• 解决办法：确保事务以正确的顺序执行，或者使用锁来防止并发更新

3.1.2、脏读

• 问题描述：一个事务读取了另一个事务未提交的数据。
• 示例：事务A修改了某个数据项的值但尚未提交，此时事务B读取了这个未提交的值。如果事务A由于某种原因（如错误）而回滚，那么事务B读取到的数据就是“脏”的，因为它从未真正存在于数据库中。
• 解决办法：使用较高的隔离级别（如Read Committed）来确保只读取已提交的数据。

3.1.3、不可重复读

• 问题描述：一个事务两次读取同一个数据，但两次读取的数据不一致。
• 示例：事务A在事务B两次读取之间修改了某个数据项的值。因此，事务B在第一次和第二次读取时得到了不同的值。
• 解决办法：使用较高的隔离级别（如Repeatable Read或Serializable）来确保在事务执行期间数据的一致性。

3.1.4、幻读

• 问题描述：一个事务两次读取一个范围的记录，但两次读取的记录数不一致。
• 示例：事务A在事务B两次读取之间插入了新的记录，这些新记录符合事务B的查询条件。因此，事务B在第二次读取时看到了“幻影”般的记录。
• 解决办法：使用Serializable隔离级别来防止幻读。

3.2、事务的隔离级别

“是”表示会出现这种情况，一般不用1、4

级别	名称	隔离级别	更新丢失	脏读	不可重复读	幻读
1	读未提交	read uncommitted	否	是	是	是
2	读已提交	read committed	否	否	是	是
3	可重复读取	repeatable read	否	否	否	是
4	序列化	serializable	否	否	否	否
5	快照	snapshot	否	否	否	否
6	已提交读快照	read committed snapshot

隔离级别越高，性能越差。安全性越高

3.2.1

每一个单独事务定义与其他事务的隔离程度，即每一个事务可以自定义隔离级别

查看数据库的事务隔离级别

dbcc useroptions

设置隔离级别为可重复读

set transaction isolation level repeatable read

3.3、实例SQL演示

3.3.1、脏读

案例：张三李四各1000块，张三欠李四200块钱，现在还钱

由于SQLserver数据库默认隔离级别是read committed，所以要先改成read uncommitted

过程：张三钱减去200，李四钱加200，执行事务SQL但不提交，然后查询，发现张三钱是800

李四是1200，于是结束。但是事后张三不讲武德，执行了rollback语句，俩人钱又都是1000了

3.3.2、不可重复读

案例：现在要查询张三李四有多少钱

begin transaction
select * from m_money
waitfor delay '00:00:10'	--delay20s，把第一次查询和第二次查询分隔开，一般工作中不用这玩意
select * from m_money
commit

将隔离级别改成read committed，先执行上面SQL，十秒钟之内再执行修改操作，把二人的钱+200，查询结果如下

3.3.3、快照

在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交的版本。

同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制。

使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

3.3.4、已提交读快照

read committed snapshot也是基于行版本控制，但是read committed snapshot的隔离级别是读操作之前的最后已提交版本，而不是事务前的已提交版本。类似前面的read committed能保证已提交读，但是不保证可重复读、不能避免幻读，但是又比read committed隔离级别多出了不需要获取共享锁就可以读取数据

具体参考 SqlServer事务详解（事务隔离性和隔离级别详解）_sqlserver事务隔离级别-优快云博客

B站博主誉尚学的估计也是从这里找的

4、锁

事务的隔离机制用的就是锁

4.1、锁的分类

4.1.1、锁的应用场景

• 共享锁：常用于只读操作，如SELECT语句。
• 排他锁：常用于修改数据的操作，如UPDATE、DELETE和INSERT语句。
• 更新锁：在某些情况下，如使用乐观并发控制时，可以使用更新锁来减少锁的持有时间。

4.1.2、悲观锁和乐观锁

悲观锁：总是假设最坏的情况，每次操作数据都会上锁，别人想操作数据就只能等我执行完释放锁了，才能操作。

乐观锁：乐观锁认为在数据处理过程中，数据冲突是很少发生的，因此它不会在数据开始处理前对数据进行加锁。相反，它会在数据提交更新时检查数据是否已被其他事务修改过。如果数据已被其他事务修改过，则当前事务的更新操作会失败，并需要重新读取数据进行处理。一般给表加一个栏位version，代表是否被修改过，通过版本控制来实现。

4.1.3、

行锁：只有当我这条数据访问完了之后，别人才能访问这行数据

select * from student rowlock where id = 1

表锁：只有当我这条数据访问完了这个表之后，别人才能访问这个表的数据

select * from student tablelockx where id = 1

共享锁：共享锁允许多个事务并发地读取同一资源，但阻止其他事务修改该资源。

select * from student with(holdlock) where id = 1

排他锁：排他锁是一种最严格的锁，也叫独占锁，它阻止其他事务对同一资源（如表、行或页）进行任何类型的访问，无论是读取还是写入(不确定能不能读取)。当一个事务对某个资源加上排他锁时，其他事务必须等待该锁被释放后才能继续访问该资源

update student with(updlock) set name = 'kawa2' where id = 1 

更新锁：更新锁是一种中间类型的锁，它介于共享锁和排他锁之间。执行update语句的时候，系统会自带更新锁，在更新数据时，会将更新锁自动转换为排他锁（别人只能读）。所以，3.1.1的更新丢失一般不会存在。

4.1.4、死锁

每个任务都锁定了其他任务尝试锁定的资源，就会发生死锁。比如吃饭一副碗筷，A拿了个碗，B拿了筷子，它俩互相占了对方的东西。实际开发中要避免死锁。

十一、T-SQL

1、全局变量

数据库自带的定义好的变量，不常用

全局变量	说明	例子
@@error	上一条SQL语句报告的错误号	0代表没错，非0表示有错
@@identity	获取最后插入的标识值	一般为自增的id
@@language	获取当前所用的语言	比如：简体中文
@@max_connections	当前数据库的最大连接数
@@rowcount	上一条SQL语句受影响的行数
@@servername	返回本地服务器名称	KSLZ28OF4793\MSSQLSERVER1
@@servicename	返回当前正在运行的注册表项名称	MSSQLSERVER1
@@trancount	返回当前正在运行的事务的数量
@@version	获取当前数据库的版本

2、局部变量

2.1、定义

定义变量并初始化

declare @name varchar(20) = 'kawa';

打印（这俩都行）

print @name;
select @name;

设置变量值

set @name = 'kawa1'

select @name = 'kawa2'

例：查询高原的年龄，如果1-10岁，输出儿童，10-25少年，25-40青年，其他老年

declare @age int
select @age = age from person where name = '高原'		--通过查询语句给@age变量赋值
if(@age between 1 and 10)
begin 
	print '儿童';
end
else if(@age between 11 and 25)
begin 
	print '少年'
end
else if(@age>26 and @age <40)
begin 
	print '青年'
end
else
begin 
	print '老年'
end

3、运算符

【SqlServer】T-SQL的简介及基本用法-优快云博客

补充：

select '10'+'10'   --1010
select '10'+10		--20

案例：

查询出人员表中的记录数并赋值给局部变量@mycount并输出

declare @mycount int
select @mycount = count(0) from person
select @mycount

求出高原和王丽的年龄总和

declare @age1 int,@age2 int
select @age1 = age from person where name = '高原'
select @age2 = age from person where name = '王丽'
select @age1+@age2

十二、流程控制和函数

1、选择结构

1.1、if else

if(1=1)
	print 'haha'  --只有一条语句的话，可以省略begin和end

case 结构（略）

2、循环结构

只有while循环

案例：求1-100的和，碰到3的倍数跳过，如果和大于100则跳出循环

declare @i int = 1
declare @sum int = 0
while(@i<=100)
begin 
	if(@i%3=0)
	begin			
		set @i += 1;
		continue;
	end
	else
	begin
		set @sum += @i;
		if(@sum >100)
			break;
	end
	set @i += 1;	
end
select @sum,@i

waitfor delay ‘00:00:10’    --等待10秒

3、函数

3.1、系统函数

left(字符串，字符数量)        --下标从1开始，从左开始数，返回指定数量的字符串

select left('kawa come on',6)    --kawa c

str(数字，转换后的字符总长度，小数位数)                --将数字转换为字符串

select str(3.1415926)		--3
select str(3.1415926,4,2)	--3.14

substring(字符串，从哪个开始，截取多少位)

select substring('kawa come on',1,6)    --kawa c

replace(字符窜，要替换的字符串，替换后的字符串)

select replace('kawa come on kawa','kawa','程文言')    --程文言 come on 程文言

round(数字，保留小位数)

trim(字符串)        --去除两边空格

lower()        --转换为小写

upper()        --转换为大写

concat()        --字符串拼接

len()        --长度

abs()        --绝对值

sqrt()        --平方根

rand()        --随机返回一个0-1的浮点数

getdate()         --获取当前时间

year()        --获取年

month()        --月

day()        --日

dateDiff(要相减的日期类型，开始日期，结束日期)        --时间差

select datediff(yyyy,'2000-01-04','2025-05-04')	--25
select datediff(mm,'2025-01-04','2025-05-04')	--4
select datediff(dd,'2025-05-01','2025-05-04')	--3
select datediff(hh,'2025-05-04 00:00:00','2025-05-04 10:00:00')	--10
select datediff(mi,'2025-05-04 00:00:00','2025-05-04 10:00:00')	--600
select datediff(ss,'2025-05-04 00:00:00','2025-05-04 10:00:00')	--36000

dateAdd(日期类型，增加多少，日期)        --增加指定的日期

select dateadd(yyyy,3,'2022-01-01')	--2025-01-01 00:00:00.000
select dateadd(year,3,'2022-01-01')	--2025-01-01 00:00:00.000

3.2、自定义函数

案例1：求1-100的和（标量值函数）

--求1-100的和
--创建函数
create function f_sum(@max int) returns int
begin
	declare @i int = 1;
	declare @sum int = 0;
	while(@i<=@max)
	begin
		set @sum += @i;
		set @i += 1;
	end

	return @sum
end 

--调用函数
select dbo.f_sum(100)	--5050

--修改函数，只需要把创建函数的create改成alter即可

--删除函数
drop function dbo.f_sum

案例2：结合select语句定义函数，求每个班级的学生的年龄和（标量值函数）

--结合select语句定义函数
--求每个班级的学生的年龄和
create function f_sumAge(@classId int) returns int
begin
	declare @sum int = 0;
	select @sum = sum(age) from student where classId = @classId;
	return @sum;
end

select dbo.f_sumAge(2)

案例3、案例1和案例2都是标量值函数，案例3是表值函数（返回值就是一张表）

create function f_Select(@id int) returns table
as
--begin 
	return (select * from person where id =@id)
--end

select * from f_Select(1)

注：要多加练习才能掌握

十三、触发器

1、概述

触发器实际用的并不多

触发器为特殊的存储过程，可在执行SQL语句(insert,update,delete)时自动执行

比如现实生活中：当烟雾过大时，会触发喷水装置。非法闯入时，会触发报警装置

在SQLserver中比如：当余额发生变动时，自动发送短信通知。生成新订单时自动减去对应的商品库存。等等

创建触发器

--创建触发器
create trigger 触发器名 on 表名
{[after]|[for]}    --after触发器|替代触发器
{[insert][,][update][,][delete]}    --在什么操作之后触发
as
begin
--t-sql语句，需要触发的动作
end

--修改触发器
把create改成alter即可

--删除触发器
drop trigger 触发器名

-- 禁用触发器  
DISABLE TRIGGER 触发器名 ON Orders;  
  
-- 启用触发器  
ENABLE TRIGGER 触发器名ON Orders;

--查看触发器
EXEC sp_helptext '触发器名'; -- 替换为你要查看的触发器的名称

2、分类

2.1、after触发器（事后触发器）

        在执行某一操作(insert,update,delete)后触发。

案例：

--创建触发器
create trigger tr_person_after_insert on person
after insert
as
begin
	print '你刚刚添加了一条数据到person表';
end

--添加数据
insert into person values('kawa','25')	--添加完这条数据之后，会打印你刚刚添加了一条数据到person表

2.2、instead of触发器（替代触发器）

        用触发器本身代替对应的操作(insert,update,delete)

create trigger tr_person_insteadof_insert on person
instead of insert
as
begin
	print '并没有添加数据，只是打印了这句话';
end

insert into person values('kawa','25')	--打印'并没有添加数据，只是打印了这句话'，其实并没有inset

3、触发器的两张临时表

inserted表和deleted表

操作	inserted表	deleted表
insert	存放新增的记录	-
delete	-	存放被删除的记录
update	存放修改后的记录	存放修改前的记录

这两张表特点：

        1、系统在内存中自动创建，当触发器完成工作后(也就是在end后)，这两张表就会被删除

        2、这两张表和触发器锁引用的表具有完全相同的表结构，且都是只读的

3.1、案例

3.1.1、案例1：添加订单时，产品库存自动减少

create trigger tr_orders_insert on orders 
after insert
as
begin
	declare @goodid int, @quantity int;
	select @goodid = goodId, @quantity = quantity from inserted;
	update goods set stock = stock - @quantity where Id = @goodid
end

--goodId、quantity是订单表中的字段，代表商品id和购买数量
--stock是商品表的字段，代表商品的库存量

3.1.2、案例2：实现假删除（软删除）

比如用户误删除了一个商品，但又想把它复原。则可以多加一个字段用来判断是不是被删除，类似flag（true，false）

create trigger tr_good_insteadof_delete on good
instead of delete
as
begin
	update good set enabled = 0 where id in (select id from deleted)
end