一、创建表及主外键
1．工具创建表
列 数据类型 是否null
一个表中，会存很多条记录，需要一个列来唯一标识一条数据。
主键：唯一标识一条数据。值 不能重复不能为空
什么样的列可以设置为主键：值不能重复不能为空
什么叫标识列？这个列设置成标识列，它就不能再手动插入，插入时，自动生成的。
标识列：标识种子 第一条记录标识列的值100 增量 3
删除了数据，再插入，就会出现不连续
2．创建主键
联合主键 唯一标识
创建一个主键，同时自动创建了一个聚集索引
3．创建外键
外键：一般在两个表之间要建立关联时候，创建一个列创建为外键，它在另一个表必须是主键
外键：DeptId UserInfos 外键表 DeptInfos主键表
两个表一旦建立外键关系，外键表里的对应的外键列，它的值必须是它对应的主键表里的主键值，不如果你想插入一个不存在的值，你是插入不进去的。
一个表里可以有多个外键，也可以没有，一个表只能有一个主键，也可以没主键，但一般都会设置一个主键。
二、数据库约束
1..约束定义：规定表中的数据规则。如果存在违反约束的数据行为，行为就会被阻止。
在什么时候可以创建约束呢？
（1）使用软件创建，创建表之后，
（2）使用脚本创建表：可以在创建的过程中，也可以在创建后再来建立约束。
2．分类
主键Primary Key约束 唯一性、非空，不能修改
外键 Foreign Key约束，加强两个表的一列或多列数据之间的连接的。先建立主表的主键，然后再定义从表中的外键。只有主表中的主键才能被从表用来作为外键使用。主表限制了从表更新和插入的操作。当删除主表中的某种数据，应该是先删除从表中相关的数据，再删除主表。
Unique约束 唯一性约束，确保表中的一列数据没有相同的值。与主键约束相似，但又不同。主键只能有一个，但一个表中可以定义多个唯一约束。唯一键可以为NULL，但主键不可以。
Check约束：通过逻辑表达式来判断数据的有效性，用来限制输入一列或多列的值的范围。
Default约束：默认值约束。用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果没有设置默认值，系统就会默认为NULL。
主外键关联关系
唯一性约束 ？
Check约束
Default约束：默认值约束
三、数据库脚本
数据库脚本，用于创建数据库对象的语句集合。
T-SOL，Transact-SQL 基于SQL（Structured Query Language）结构化查询语言，用于应用程序和数据库之间沟通的编程语言。Sql Server 支持的，脚本语言。
SQL语言，一种有目的的编程语言，用于存取数据、查询、更新和管理关系数据库，高级的非过程化的编程语言。
作用：可以完成移植，提高数据访问效率，完成对数据的相关处理。
SQL语言分类：
1）DQL 数据查询语言
Select 列 From 表名 where 条件
2）DML 数据操纵语言
Insert 插入 insert into 表名（列，列....）values（对应的值，对应
的值）
Update 更新 update 表名 set 列名＝值，列名＝值 where 条件
Delete 删除 delete from 表名 where 条件
3）DDL 数据定义语言 创建数据库及其对象 Create／Alter／Drop Database／Table／Viem／Proc／Index
4）DCL数据控制语言 用于授予或回收访问数据库某种特权。对数据实行监视等
commit 提交 rollback 回滚 grant 授权
四、脚本创建数据库
1.切换要操作的数据库
use TestBase --选择要操作的数据库
go--批处理命令2.创建数据库
--创建数据库
create database TestNewBase --数据库名称
on primary --主文件组
(
name = 'TestNewBase_data',--数据库主要数据文件的逻辑名
filename = 'E:\9.SQLServer\TestNewBase.mdf', -- 主要数据文件的路径（绝对路径）
size = 5MB,--数据库主要文件的初始大小
filegrowth = 1MB --文件的增量
)
log on --创建日志文件
(
name = 'TestNewBase_log',--数据库日志文件的逻辑名
filename = 'E:\9.SQLServer\TestNewBase_log.ldf', -- 日志文件的路径（绝对路径）
size = 5MB,--数据库主要文件的初始大小
filegrowth = 10% --日志文件的增量
)
go3.删除数据库
--删除数据库
drop database TestNewBase
go五、T-SQL创建表
1.创建表
--创建表
create table ProductInfos
(
Id int identity(1001,1) primary key not null,--标识种子 增量 （primary key 主键，identity 标识列（开始值，增量））
ProNo varchar(50) not null,
ProName nvarchar(50) not null,
TypeId int not null,
Price decimal(18,2) default 0.00 null, --default 0.00 设置默认值为0.00
ProCount int default 0 null
)
go2.删除表
--删除表
drop table ProductInfos
go六、T-SQL修改表
--创建表之后，进行修改
--不删除原来的表的基础上，进行修改
1.添加一列
alter table ProductInfos add ProRemark nvarchar(max) null2.删除一列
alter table ProductInfos drop column ProRemark3.修改一列
alter table ProductInfos alter column ProNo nvarchar(50) null
4.修改列名 一般慎用（存储过程）（一般不要修改列名）
--exec sp_rename 'ProductInfos.ProCount','Count','column'七、T-SQL创建约束
1.建表时创建约束
（1）主键约束
Id int identity(1001,1) primary key not null,--标识种子 增量 （primary key 主键，identity 标识列（开始值，增量））（2）外键约束
TypeId int not null foreign key references ProductType(TypeId), --foreign key references 外键（3）unique约束（唯一约束）
ProNo varchar(50) unique not null（4）check约束
Price decimal(18,2) check(Price<10000) default 0.00 null, --default 0.00 设置默认值为0.00（5）default约束（默认值约束）
Price decimal(18,2) default 0.00 null, --default 0.00 设置默认值为0.00（6）完整代码
create table ProductInfos
(
Id int identity(1001,1) primary key not null,--标识种子 增量 （primary key 主键，identity 标识列（开始值，增量））
ProNo varchar(50) unique not null,
ProName nvarchar(50) not null,
TypeId int not null foreign key references ProductType(TypeId), --foreign key references 外键
Price decimal(18,2) check(Price<10000) default 0.00 null, --default 0.00 设置默认值为0.00
ProCount int default 0 null,
ProRemark nvarchar(max) null
)
go
2.建表后创建约束
（1）主键约束
alter table ProductInfos add constraint PK_ProductInfos primary key(Id)
（2）外键约束
alter table ProductInfos add constraint FK_ProductInfos foreign key(TypeId) references ProductType(TypeId)（3）unique约束（唯一约束）
--unique约束(唯一约束) ProNo
alter table ProductInfos add constraint IX_ProductInfos_ProNo unique(ProNo)--unique约束(唯一约束) ProNo+ProName 联合多个列唯一约束
alter table ProductInfos add constraint IX_ProductInfos_ProNo unique(ProNo,ProName)（4）check约束
alter table ProductInfos add constraint CK_ProductInfos_Price check(Price < 10000)（5）default约束（默认值约束）
alter table ProductInfos add constraint DF_ProCount default(0) for ProCount八、 T-SQL插入数据
1.单条数据 --insert DML
（1）第一种方式 insert (into) 表名 (列名，列名...) values (值,值...)
insert into ProductType(TypeName) values('工具类')（2）第二种方式 insert (into) 表名 (列名，列名...) select 值,值...
insert ProductType(TypeName) select '鞋类'2.一次性插入多条，批量插入操作
（1）第一种方式
insert into ProductType (TypeName) values ('其他类1'),('其他类2'),('其他类3'),('其他类4'),('其他类5')（2）第二种方式 union 去重 union all 允许重复 union all 效率比union高
--insert Test (Id,MName,Age)
--select 1,'admin',23 union
--select 2,'aaaa',25 union
--select 3,'bbbb',24 union
--select 4,'cccc',88
insert Test (MName,Age)
select 'iiiiiiii',23 union all
select 'iiiiiiii',23 3.克隆数据 将一张表的数据复制到另一张表
（1）第一种方式
insert into Test(MName) --目标表
select TypeName from ProductType --源表（2）第二种方式 目标表之前数据库不存在，执行操作时自动创建的
select TypeName into Test2 --目标表
from ProductType --源表 九、T-SQL更新删除数据
1.更新数据
--更新数据 update 几乎都要用到where条件
--主键不可以修改
--如果不加where条件，会把整个表的数据都修改了
update Test set MName = 'sssss6',Age= 30
where Id = 5 -- where(and/or)2.删除数据:
（1）只是删除数据，表还在 （2）连同表一起删除
（1）删除数据 delete from Table 不加条件，会删除整个表数据，几乎都要加where条件 标识列 值还是接着删除前的值而自增，而不是从初始值开始的
--delete语句会造成标识列的值不连续
delete from Test where Id = 18（2）删除数据，让标识列的值恢复到初始值
truncate table Test -- 表数据清空，恢复到初始化，标识列也恢复
--表面上看，delete from Test 效果一样
--truncate效率比 delete from Test高，delete每删除一条数据，都会在日志里记录，truncate不会记录日志，不激活触发器，drop truncate 是即时操作，不能rollback（不允许恢复），delete update insert事务中，可以恢复
--慎用truncate 一旦删除，不能恢复十、T-SQL查询之单表查询
1.查询一个表所有数据
* 所有列 只需要部分列的数据 内存毕竟有限
select * from UserInfos2.查询部分列的数据 推荐使用
select UserId,UserName,Age from UserInfos where Age < 403.给列名别名
数据库里表字段 英文名称、程序中列名--中文
--给列名别名
--(1)列名 as 别名 (2)列名 别名 (3)别名=列名
select UserId as 用户编号, UserName 用户名,年龄 = Age from UserInfos4.排序 order by （desc，asc）
--主键，默认就有排序功能 从小到大 --升序 asc
--降序：从大到小 desc
--不管是否有条件,还是分组，order by 永远放在最后
select UserId,UserName,Age from UserInfos order by UserId desc
select UserId,UserName,Age from UserInfos order by UserId asc, Age desc
十一、SQL查询之模糊查询
1. % 0个或多个 匹配任意类型和长度 效率不高
（1）like '%ad%' 包含于
select * from UserInfos where UserName like '%f%'（2）like '%f' 以匹配字符或字符串结尾
select * from UserInfos where UserName like '%f'（3）like '5%' 以匹配字符或字符串开头
select * from UserInfos where UserName like '5%'2._ 匹配单个字符 限制表达式的字符长度
select * from UserInfos where UserName like '_d_in'
select * from UserInfos where UserName like '_____'3.[] 范围匹配 括号中所有字符中的一个
select * from UserInfos where UserName like 'ad[m|n|d]in[1-20]'
select * from UserInfos where UserName like 'ad[mnd]in'
select * from UserInfos where UserName like 'ad[m-p]in'4.[^] 范围匹配 不在括号内中所有字符之内的单个字符
select * from UserInfos where UserName like 'ad[^abcm]in'十二、SQL查询之范围查询
1.select from table where 子句 条件 --给定范围
（1）比较运算符 > < >= <= <>
select * from UserInfos where Age < 30 and DeptId > 1（2） in(2,3,4) not in(2,3,4) 不在这个范围内
select * from UserInfos where Age in(20, 43,22)
select * from UserInfos where Age not in(20, 43,22)
--子查询
select * from UserInfos where DeptId in
(
select DeptId from DeptInfos where DeptId > 1
)（3）between and （推荐写法） 等价于 >= and <=
select * from UserInfos where Age between 20 and 43 --Age >= 20 and Age <= 33
2.前面多少条 百分比
select top 10 * from UserInfos
select top 50 percent * from UserInfos
十三、SQL查询之聚合函数
聚合函数，对一组值执行计算并返回单一的值
五种聚合函数
1.count 记录个数
--Count 记录数NULL 经常与select语句中group by结合使用。
select count(*) from UserInfos
--select count(1)伪造类 效率比count(*)高
select count(1) as Record from UserInfos --一般统计一个表的记录数
where Age > 402.sum 求和
--sum 求和 相加
select sum(Age) from UserInfos 3.avg 求平均
--agv 求平均
select avg(Age) from UserInfos4.max 最大值
--max 求最大
select max(Age) from UserInfos5. min最小值
--min 求最小
select min(Age) from UserInfos
十四、SQL查询之分组查询
group by 分组查询
结合聚合函数，根据列或者多个列对结果集进行分组
--select ...
--where ...
--group by 列名，列名 结合聚合函数，根据列或者多个列对结果集进行分组
--语法：select ... where... group by ... order by ...
--统计各部门有多少个用户
--select 出现的列名，必须出现在group by之后或包含在聚合函数中
select DeptId,count(1) 用户数 from UserInfos
--where Age < 40
group by DeptId
having DeptId >= 1 --having筛选 分组后的筛选条件
order by DeptId desc
十五、SQL查询之内连接
--连接查询 根据两个或多个表之间的关系，从这些表中查询数据
--目的：实现多表查询
--分类：内连接 外连接 全连接 交叉连接
--内连接：inner join 使用比较运算符 = > < >= <= <>进行表间的比较，查询与条件相匹配的数据。
--等值连接
--结果：相匹配的数据查询出来，显示匹配出来的结果，如果没有匹配上，就没有结果
--显示和隐式连接执行效率是一样的
（1）显式连接 表 inner join 表 on 关联条件 where ...
select UserId,UserName,Age,u.DeptId,DeptName
from UserInfos u
inner join DeptInfos d on d.DeptId = u.DeptId
where Age > 25（2）隐式连接 select ... from 表,表 where 关联条件
select UserId,UserName,Age,u.DeptId,DeptName
from UserInfos u ,DeptInfos d where d.DeptId = u.DeptId and Age > 14
十六、SQL查询之外连接
外连接分类：左外连接 右外连接 全外连接 简称为：左连接 右连接 全连接
（1）左连接（左外连接）
left (outer) join on 返回左表的所有行，右表中没有匹配上，对应的列就显示NULL
select * from UserInfos u
left join DeptInfos d --outer 可以省略
--left outer join DeptInfos d
on u.DeptId = d.DeptId
--结果：左表：所有行 右表：行数与左表相同，没有匹配上，显示NULL（2）右连接（右外连接）
right (outer) join on 与左连接相反，返回的右表的所有行，左表进行匹配，左表中没有匹配上的，对应的列显示NULL
select * from UserInfos u
right join DeptInfos d --outer 可以省略
on u.DeptId = d.DeptId
--结果：右表：所有行 左表：行数与右表相同，没有匹配上的，显示NULL十七、SQL查询之全连接、交叉连接
1.全连接：full (outer) join 全外连接
返回左表和右表中所有行，当某一行在另一个表中没有匹配，另一个表中的列返回NULL
select * from UserInfos u
full outer join DeptInfos d --outer 可以省略
on u.DeptId = d.DeptId
--结果：左表和右表 所有数据都会出来，每一行的数据，如果在另一个表里匹配不上，就是对应显示NULL2.交叉连接 cross join 笛卡尔积
-- 如果不带where子句时，返回被连接的两个表的笛卡尔积，返回的行数是两个表行数的乘积。
-- 带where子句，等价于inner join 返回的是匹配的数据
select * from UserInfos u
cross join DeptInfos d -- outer 可以省略
--3*4=12
where u.DeptId = d.DeptId十八、类型转换函数
--支持 数字+数字 --求和 字符串+字符串--拼接
--字符串+数字
select 1+2 --求和
select 'a'+'b' --拼接（1）convert函数：convert(类型(长度),表达式)
select 'abcd'+ convert(varchar,201) --把数字转换为字符串，拼接
select convert(varchar(10),getdate(),126) --日期格式（2）cast函数：cast(表达式 as 类型)
select 'lgx' + cast(2 as varchar(20)) -- 要转换成的目标数据类型
select cast(getdate() as varchar)
select cast('2022-05-04' as varchar)
select cast('2022-05-04' as datetime)十九、字符串操作函数
字符串操作：从一个字符串里找一个子串，位置
1.返回字符串中指定的子串出现的开始位置
（1）返回字符串中指定的子串出现的开始位置
select CHARINDEX('bc','abcdbc');--2 索引从1开始（2）返回字符串中指定的子串出现的开始位置子串前后必须带%
select PATINDEX('%bc%','abcdbc');--2 索引从1开始2.大小写转换
select LOWER('ABCDEFG') --转小写 abcdefg
select upper('abcdefg') --转大写 ABCDEFG3.取长度
select LEN('ABC'); --34.去空格
（1）去掉左边的空格
select LTRIM(' abcd ');--'abcd '去掉左边的空格（2）去掉右边的空格
select RTRIM(' abcd ')--' abcd'去掉右边的空格5.取子串
select LEFT('sbcssdadadf',4);--sbcs 返回字符串从左边起4个字符
select RIGHT('sbcssdadadf',6);--dadadf 返回字符串从左右边起6个字符
select SUBSTRING('sbcssdadadf',3,4);--cssd SUBSTRING('字符串',开始位置,取的个数); 返回字符串从左边起第三个开始，取4个字符6.字符
（1）将指定字符串重复4次后生成的字符串
select REPLICATE('abc',4);--abcabcabcabc（2）字符串翻转
select REVERSE('abcsdefg');--gfedscba（3）字符串替换
select REPLACE('abcdefg','cd','ss');--abssefg（4）用指定子串替换指定位置指定长度的子串生成的字符串
select STUFF('abcdefg',2,3,'tt');--attefg二十、索引介绍
1.索引的作用：数据的查询 处理速度 --应用系统的成败标准。 最普遍--优化方式
2.索引是什么？ 一本书 目录 查找索引---目录，快速的定位我们要查找的数据，而不必去扫描整个表。
从而可以加快我们查询的数据.---提高系统的性能。
3.索引有缺点：占用存储空间。索引并不是越多越好。索引并不总是能提高系统性能的。4.索引的目的：可以更加快速高效地查询数据，减少系统的响应时间。
5.索引分为聚集索引，非聚集索引
主键 唯一索引
聚集索引：Clustered 逻辑顺序与物理顺序是一致的 最多只能有一个，可以没有
非聚集索引：NonClustered 逻辑顺序与物理顺序是并不一致的。可以有多个，也可以没有
非聚集索引比聚集索引 效率低
如果需要多个列上建立索引，这些列建立组合索引
列：小数据类型的，访问速度特别快
6.索引的作用：索引是看不见的，但是如果你创建了索引，在查询大数据的时候，它的优势就是显而易见的。
二十一、脚本创建索引
默认创建的是非聚集索引
1.聚集 主键索引
create clustered index PK_UserInfos --clustered（聚集） index（索引）
on UserInfos(UserId)
with
(
drop_existing = on --on 先删除原来的，create一个新的 off不删除原有的，提示一个错误，索引已存在
)2.唯一非聚集索引
create unique nonclustered index uq_UserInfos -- unique 唯一 nonclustered（非聚集）index（索引）
on UserInfos(UserName)
with
(
drop_existing = on, --on 先删除原来的，create一个新的 off不删除原有的，提示一个错误，索引已存在
pad_index = on,--设置了fillfactor，pad_index选项才有效
fillfactor=50,--指定创建索引时，每个索引页的数据占索引页大小的百分比
ignore_dup_key = on--唯一键警告 off--错误信息
)
fillfactor
--读写比例：100:1 100
--读小于写：50--70
--读写各一半：80--903.复合索引：多个列上建立的索引
create nonclustered index Index_UserInfos
on UserInfos(UserName,DeptId)
with(
drop_existing = on
)二十二、视图介绍
视图：虚拟表 由一个或多个表通过查询而定义的 将查询定义保存起来，实际不包含数据
与表的区别：表存储数据的地方，视图存储的是查询语句（索引视图除外，具体化了（物理存储））
作用：简化查询 增加数据的保密性 安全性上得到保证
缺点：只是简化查询，并不提高查询速度 增加了维护成本
分类：
（1）标准视图：存储查询定义 没有存储数据
（2）索引视图：（被具体化了的，创建了索引，显著提高查询性能，聚合了很多行的查询，不太适合更新基本数据集）（物理存储的，不是虚拟的数据）
（3）分区视图：一台或多台服务器水平连接一组成员表的分区数据。
1.在数据库中创建视图
二十三、脚本创建视图
1.创建标准视图
（1）创建标准视图
create view vUserInfoNew
as
--T-SQL-查询语句
--select UserId,UserName,u.DeptId,DeptName
--from UserInfos u,DeptInfos d
--where u.DeptId = d.DeptId
select u.*,d.DeptName
from UserInfos u
inner join DeptInfos d
on u.DeptId = d.DeptId
go
（2）使用视图就和使用表一样
select * from vUserInfoNew
where DeptId > 1
order by UserId 2.创建索引视图 适合于聚合多行数据的情况下
（1）创建索引视图
数据是实际存在，删除视图里的数据，基础表里的数据也被删除
索引视图里不要去删除修改数据
-- * 必须写字段名，表名前面要所有者 dbo
create view vUserInfos_Index with schemabinding
as
select UserId,UserName,Age From dbo.UserInfos
go
（2）使用视图
select * from vUserInfos_Index（3）索引视图创建唯一聚集索引
create unique clustered index uq_vUserInfos_Index
on vUserInfos_Index(UserId)3.分区视图
（1）创建分区视图
create view vTestInfos
as
select * from Test
union all
select * from Test3
go
（2）使用视图
select * from vTestInfos标准视图和分区视图都不允许删除修改里面的数据，会影响基础表
索引视图 删除了，对应的基础表数据也被删除了，慎用
二十四、存储过程介绍
存储过程：一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，可以利用存储过程来加速SQL语句的执行。
分类：（1）系统存储过程：master数据库中，其他数据库中可以直接调用，并且不必在前面加上数据库名，创建数据库时，这些系统存储过程在新的数据库中自动创建。
（2）自定义存储过程：用户自己创建，特定功能而创建。可以传入参数，也可以有返回值，表明存储过程执行是否成功，里面可以只是一个操作，也可以包括多个。
执行：execute/exec 存储过程名 参数列表 （多个参数，以逗号隔开）
优点：1.提高应用程序的通用性和可移植性。多次调用，而不必重新再去编写，维护人员可以随时修改。
2.可以更有效的管理数据库权限。
3.提高执行SQL的速度。
4.减轻服务器的负担
缺点：专门维护它，占用数据库空间。
它都有缺点和优点，合理使用。
--语法：
create proc 存储过程名
@userId int,
@userName varchar(50)
as begin
--T-SQL语句
end
go二十五、脚本创建存储过程
（1）创建无参数的存储过程 (create/alter)
--create proc SearchUserInfo --create 创建
alter proc SearchUserInfo --alter修改
as
begin
select UserId,UserName,Age from UserInfos
select * from DeptInfos
end
go
--调用执行 （execute/exec 存储过程名 参数列表 （多个参数，以逗号隔开））
exec SearchUserInfo
--删除存储过程
drop proc SearchUserInfo（2）创建/修改带参数的存储过程
alter proc AddUserInfo -- create/alter
@UserId int,
@UserName varchar(50),
@UserPwd varchar(50),
@Age int,
@DeptId int
as
begin
declare @time datetime --定义变量
set @time ='2019-11-15' --变量赋值
--select可以赋值多个
insert into UserInfos
(UserId,UserName,UserPwd,CreateTime,Age,DeptId)
values(@UserId,@UserName,@UserPwd,@time,@Age,@DeptId);
delete from UserInfos where UserId = 88;
select * from UserInfos;
end
go
--调用
exec AddUserInfo 83,'lingli2','1234',25,3
--删除存储过程
drop proc AddUserInfo 二十六、触发器介绍
触发器：一种特殊的存储过程。不同：存储过程 调用名称执行
触发器的调用 提高事件触发 自动调用执行。
对表进行操作：插入、更新、删除 自动执行 触发器。如果定义对应的触发器。系统就自动调用分类：DDL触发器 Create alter drop
DML触发器 insert update delete
DML触发器分为两种：（1）after触发器 insert update delete 定义操作执行之后触发
（2）instead of 触发器 insert update delete 不执行定义的操作 而执行的是触发器本身
二十七、inserted与deleted详解
inserted deleted：插入表 删除表
逻辑表也是虚表，系统在内存中创建的，不会存储到数据库中，只读的，读取但不能修改数据。结构与操作的表相同。触发器执行过程中存在，并且可以访问，工作完成之后，这两张表会被自动删除。
对表的操作 inserted deleted
insert 存放插入的数据 无
update 存放更新后的数据 存放更新前的数据
delete 无 存放被删除的数据-- inserted 存放插入或更新后的数据
-- deleted 存放更新前或删除的数据--触发器执行过程中，都是可以访问的，提交之前，是不是可以撤销呢---事务的回滚？ 可以
--触发器是一个特殊的事务，一些检查，设置条件，如果不满足，可以回滚。
二十八、after触发器之insert update
（1）after---insert触发器
after触发器:对表操作之后触发
--create trigger teigger_UserInfos_Insert --(创建)
alter trigger teigger_UserInfos_Insert --(修改)
on UserInfos
for insert -- 也可以写成after insert
as
--T-SQL
declare @id int,@name varchar(50)
select @id= UserId,@name = UserName from inserted
print convert(varchar,@id)+','+@name +','+'添加成功!'
delete from UserInfos where UserId = 9
go
--触发 teigger_UserInfos_Insert
insert into UserInfos(UserName,UserPwd,DeptId,Age) values ('hg','123456','2',18)
select * from UserInfos
（2）after--update 触发器
以前我们执行update语句后，能看到只是修改后的数据
把修改前与修改后的数据都显示出来
修改就是先删除原来的，再插入新的一条数据
--create trigger trigger_UserInfos_Update --(创建)
alter trigger trigger_UserInfos_Update --(修改)
on UserInfos
for update
as
declare @id int,@name varchar(50),@age int
select @id = UserId,@name= UserName,@age = Age from deleted
print '修改前：'+convert(varchar,@id)+','+@name+','+convert(varchar,@age);
select @id= UserId,@name = UserName,@age=Age from inserted
print '修改后：'+convert(varchar,@id)+','+@name+','+convert(varchar,@age);
select * into UserInfoBaskup --将更新前的数据备份到新表
from deleted
go
-- 触发trigger_UserInfos_Update
update UserInfos set UserName = 'lhc1' , Age = 28 where UserId =6
select * from UserInfos二十九、delete和instead insert
（1）after delete触发器
after delete触发器：删除后触发deleted 查看删除的数据。特殊的事务 --- 反悔
备份，查询 -- 打印
--create trigger tirgger_UserInfo_Delete --(创建)
alter trigger tirgger_UserInfo_Delete --(修改)
on UserInfos
for delete
as
declare @id int,@name varchar(50),@age int
select @id= UserId,@name = UserName,@age=Age from deleted
print '要删除：' + convert(varchar,@id)+','+@name + ','+convert(varchar,@age)
--备份
if(OBJECT_ID('UserInfoBackup','U') is not null)
--目标表存在的
insert into UserInfoBackup select UserId,UserName,UserPwd,CreateTime,DeptId,Age from deleted
else
--目标表不存在的
--select UserName,UserPwd,CreateTime,DeptId,Age into UserInfoBackup from deleted
select UserId,UserName,UserPwd,CreateTime,DeptId,Age into UserInfoBackup from deleted
print('备份成功！')
go
--标识列的缺点：不能显示插入标识列
--触发tirgger_UserInfo_Delet
delete from UserInfos where UserId = 13
select * from UserInfos
（2） instead of insert触发器(insert操作之前触发)
instead of insert触发器：不执行触发器操作，执行的是定义本身
--create trigger trigger_InsteadOf_Insert
alter trigger trigger_InsteadOf_Insert
on UserInfos
instead of insert
as
declare @id int,@name varchar(50),@age int,@DeptId int
select @id = UserId,@name= UserName,@age=Age,@DeptId =DeptId from inserted
print '要添加的数据' + convert(varchar,@id)+','+@name + ','+convert(varchar,@age)+','+convert(varchar,@DeptId)
go
--触发trigger_InsteadOf_Insert
insert into UserInfos(UserName,UserPwd,DeptId,Age)
values('liming2','22223',1,18)
--after insert 在触发器中,标识列已经自动生成 有值
--instead of insert 在触发器中，标识列并未生成，没有值 执行完成，要插入的数据却并没有插入
select * from UserInfos三十、instead update delete
（1）instead of update(update操作之前触发)
--create trigger trigger_InsetedOf_Update
alter trigger trigger_InsetedOf_Update
on UserInfos
instead of update
as
declare @id int,@name varchar(50),@age int
select @id = UserId,@name= UserName,@age = Age from deleted
print '更新前的数据'+','+convert(varchar,@id)+','+@name+','+convert(varchar,@age)
select @id = UserId,@name= UserName,@age = Age from inserted
print '更新后的数据'+','+convert(varchar,@id)+','+@name+','+convert(varchar,@age)
update UserInfos set Age = 54 where UserId = 12
go
--触发 trigger_InsetedOf_Update
update UserInfos set Age = 38 where UserId = 12
--本来操作的结果没有发生，但操作更新的数据进入inserted临时表（2）instead of delete(delete操作之前触发)
--instead of delete(delete操作之前触发)
create trigger trigger_Instead_Delete
on UserInfos
instead of delete
as
declare @id int,@name varchar(50),@age int
select @id = UserId, @name =UserName,@age = Age from deleted
print '要删除的数据' + ',' + convert(varchar,@id)+',' + @name + convert(varchar,@age)
--备份
insert into UserInfoBackup select
UserId,UserName,UserPwd,CreateTime,DeptId,Age from deleted
--触发 trigger_Instead_Delete
delete from UserInfos where UserId = 3触发器并不是越多越好，对于一个数据表，如果操作数据次数多，定义触发器反而不是好事。
如果操作不多，想通过触发器做一些额外的工作，可以合理使用。
三十一、游标介绍及分类
游标是什么？对数据查询结果集的一种访问机制。用户对结果集进行逐条访问， 单条数据
对象：结果集
定义在特定结果集上的指针，控制这个指针，遍历数据集或指定特定的行游标的作用：定位到结果集中的某一行，对当前位置的数据进行读写
缺点：数据读取出来放到TempDB里，放到内存。--内存空间有限，不可以太大，内存空间不足
适用于：数据量小的情况
游标分类：静态，动态，只进，键集驱动游标
静态：结果集，对数据库如何操作，结果集都不会变。支持滚动。结果集与操作之后的数据一致--关闭游标，在重新打开
动态：与静态相对，前后滚动，结果集获取所以的改变，提取时，行数据，顺序，成员都会发生变化。对数据库的操作都通过游标可见。api函数或t-sql where current of 子句通过游标进行更新。游标外部所做的更新直到提交时才可见。
只进：不支持滚动，从头读到尾，对数据库所做的更改在提取时是可见的。只进不退的。提取后所做的更改是不可见的。
只进游标 不支持滚动，检测不到表数据的变化，消耗的资源相对比较少。 --只读取数据
静态游标 在滚动时，检测不到表数据的变化，消耗的资源相对比较少。 --选择
动态游标 在滚动时，检测到表数据的变化，消耗的资源相对比较多。 --性能低游标的生命周期：声明--打开--读取数据--关闭游标--释放游标
三十二、创建只进游标
创建只进游标 不支持滚动，提取时可见，提取后不可见
--1.定义游标
declare user_cursor cursor local --global 全局游标
for
--查询T-SQL
select UserId,UserName,Age from UserInfos
where UserId>10
--2.打开
open user_cursor
--
declare @varUser cursor,@uId int ,@uName varchar(50),@age int set @varUser = user_cursor
--3.提取数据 next向下 prior向上 first 第一个 last 最后一个
--absolute n 绝对位置 relative n 从当前位置开始第n个
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
while @@FETCH_STATUS = 0 ---判断是否执行成功
begin
print cast(@uId as varchar) + ',' + @uName + ',' + convert(varchar,@age)
if @uId = 11
update UserInfos set Age = 25 where UserId = 15
if @uId = 15
update UserInfos set Age = 30 where UserId = 11
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
end
--4.关闭游标
close user_cursor
--5.释放游标
deallocate user_cursor三十三、创建静态、动态游标
（1）静态游标
静态游标：结果集--- 不变，对数据库的改变是不可见的,除非重新打开游标
--1.定义游标 静态游标：结果集--- 不变，对数据库的改变是不可见的,除非重新打开游标
declare user_cursor_static cursor static local --global 全局游标
for
--查询T-SQL
select UserId,UserName,Age from UserInfos
where UserId>10
--2.打开
open user_cursor_static
--
declare @varUser cursor,@uId int ,@uName varchar(50),@age int
set @varUser = user_cursor_static
--3.提取数据 next向下 prior向上 first 第一个 last 最后一个
--absolute n 绝对位置 relative n 从当前位置开始第n个
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
while @@FETCH_STATUS = 0 ---判断是否执行成功
begin
print cast(@uId as varchar) + ',' + @uName + ',' + convert(varchar,@age)
if @uId = 11
update UserInfos set Age = 25 where UserId = 15
if @uId = 15
update UserInfos set Age = 30 where UserId = 11
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
end
--4.关闭游标
close user_cursor_static
--5.释放游标
deallocate user_cursor_static
（2）动态游标
动态游标：结果集--- 变化的，对数据库的改变在提取时可见的
--1.定义游标 动态游标：结果集--- 变化的，对数据库的改变在提取时可见的
declare user_cursor_dynamic cursor dynamic local --global 全局游标
for
--查询T-SQL
select UserId,UserName,Age from UserInfos
where UserId>10
--2.打开
open user_cursor_dynamic
--
declare @varUser cursor,@uId int ,@uName varchar(50),@age int
set @varUser = user_cursor_dynamic
--3.提取数据 next向下 prior向上 first 第一个 last 最后一个
--absolute n 绝对位置 relative n 从当前位置开始第n个
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
while @@FETCH_STATUS = 0 ---判断是否执行成功
begin
print cast(@uId as varchar) + ',' + @uName + ',' + convert(varchar,@age)
if @uId = 11
update UserInfos set Age = 25 where UserId = 15
if @uId = 15
begin
--update UserInfos set Age = 210 where UserId = 7
update UserInfos set Age = 210 where current of user_cursor_dynamic
--fetch first from @varUser into @uId,@uName,@age
end
fetch next from @varUser into @uId,@uName,@age --游标前进into赋值
end
--4.关闭游标
close user_cursor_dynamic
--5.释放游标
deallocate user_cursor_dynamic工作中应用：如果游标，调用--游标放到存储过程中
三十四、自定义函数之标量函数
字符串处理 类型转换函数
自定义函数：根据自己的需要，自定义一些函数。
三类
（1）标量函数 对单一值的操作，返回单一值。 begin end
（2）内嵌表值函数：参数化的视图。返回的一个表 没有begin end包括起来。
（3）多声明表值函数：返回的也是一个表，begin end 函数体。返回的表数据由函数体中的语句插入的。多次查询，甚至可以筛选和合并，弥补了内嵌表值函数的不足。
（1）标量函数
--标量函数：返回单一值
--create function [dbo].GetUserAge(@userId int)
alter function [dbo].GetUserAge(@userId int = 15)
returns int --返回值类型
--with encryption
as
begin
declare @age int
select @age = Age from UserInfos where UserId = @userId
return @age --表达式/值
--begin end中的T-SQL语句（不可以有修改数据的操作）
end
go
--函数调用
select dbo.GetUserAge(11) as 年龄
select dbo.GetUserAge(default) as 年龄 --读取default参数默认值注意点：
1.创建的时候，指定了函数所有者，调用时必须指定函数所有者
2.调用时，如果函数中指定了默认值，调用时，使用默认值，就直接参数就直接用default代替，如果没有默认值，返回就是null
3.语法上 returns 返回值类型 begin end return 表达式/值
三十五、自定义函数之内嵌表值函数
returns table
as
return (
select UserId,UserName,Age,DeptId from UserInfos
where UserId = @userId
)
go
--调用
select * from dbo.GetUserInfo(6)1.概念：功能上相当一个参数化的视图。返回表 没有begin end
2.注意点：
（1）returns 返回类型：只能是table
（2）as后面是没有begin end 只能是return(select语句)
三十六、自定义函数之多声明表值函数
（3）多声明表值函数
概念：多语句表值函数 begin end 返回 表 定义表名 定义表的结构 可以说标量函数与内嵌表值函数的结合，弥补了内嵌表值函数的不足。
create function[dbo].SearchUsers(@uName varchar(50))
returns @users table(
Id int not null primary key,
UName varchar(50) not null,
UAge int null
)
as
begin
--函数体
--insert 插入
insert into @users(Id,UName,UAge)
select UserId,UserName,Age from UserInfos
where UserName like '%' + @uName + '%'
return --后面什么也不跟
end
go
--调用
select * from dbo.SearchUsers('h')比内嵌表值函数 定义上更完善
注意点：函数一般做查询使用，不能用数据修改。
[函数所有者]定义时写了，调用时就必须写，一般是dbo
returns @user table (表结构定义)
begin
函数体
Insert into @user(...) select ...
return --后面什么也不跟
end
应用：使用比较灵活，可以select中使用，被直接引用，通过递归方法得到，使用函数
如果能够用内嵌表值函数就能满足，就不要用多声明表值函数。三种自定义函数对比
（1）标量函数：returns返回值类型 begin end return 表达式/变量
（2）自定义函数：returns table as return(select 语句)
（3）多声明表值函数：returns @user table(表结构定义) begin end return
三十七、事务的介绍
事务是什么？--系统T-SQL语句构成了逻辑工作单元，为了完成一定的业务逻辑，操作封装起来，边界，相对独立的工作单元。
一系列操作当做一个操作。事务应用：多个表进行操作。
处理过程中，出现了某些错误或系统死机或断电，数据并没有保存到数据库。事务处理结果：
1.并未发生任何错误，全部被提交
2.如果有某一个操作发生错误或故障，所以操作都会被回滚到最初状态。
作用：通过事务提高数据的安全性。增强了数据的处理效率。维护数据库的完整性。事务的特性：4个特性 ACID
1.原子性：Atomic 一个工作单元 一个整体提交或回滚，各个元素是不可分的，一个操作。
2.一致性：ConDemoltent 数据必须保持一致，数据状态一致
3.隔离性：Isolated 多个事务之间是彼此隔离的。独立性。
4.持久性：Durabiliy 事务提交后，对数据库的更改是永久保存的，即使系统出现故障，也会保留。真实的修改了数据库。
三十八、数据库事务创建
--开启事务
begin try
begin tran
---set imolicit_transactions on; --开启一个隐式事务
--一系列的操作 T-SQL insert update delete
insert into UserInfos(UserName,UserPwd,Age,DeptId) values('ryy','abc',17,1)
update UserInfos set Age = 66 where UserId = 12
delete from UserInfos where UserId = 5
commit tran --事务提交
end try
begin catch
rollback tran --事务回滚
print '出现异常'
end catch
--set implicit_transactions off --关闭隐式事务体现 事务一致提交或回滚
如何调用事务？一般放在存储过程里。
显式事务 隐式事务
开启事务 begin tran set imolicit_transactions on
提交事务 commit tran
回滚事务 rollback rollback set imolicit_transactions off
三十九、事务并发问题
如何发生的？多个用户对同一条数据进行访问。
事务并发问题：
1.丢失或覆盖更新：同一行，a b a 修改 Age= 25 b修改 26 ,一个事务在不知道其他事务的情况下，对数据进行修改，而造成的数据丢失。
2.脏读：一个事务读取另一个事务并未提交的更新。
3.不可重复读：多个事务访问同一条数据，每次读取的数据不同，不可重复读。
4.幻象读：第一次读取的数据行与第二次读取的不一致，第二次读取到的数据是第一次读取时不存在的。（A在B读的过程中又新增了一条数据，B在第一次查询时看不到，重新查询时发现多了一条数据）
解决：设置事务隔离级别：事务接受不一致数据访问级别
隔离级别--低--造成很多并发问题
高--降低并发问题
1.read committed 默认的级别 避免脏读。产生不可重复读、幻象读。
2.read uncommitted 0级 上面4种并发问题都会产生
3.repeatable read 避免脏读，不可重复读。但是会产生幻象读
4.serializable 避免避免脏读，不可重复读，幻象读。
必要时才会用到 3、4
--开启事务
begin try
--set tran isolation level read committed; --（避免脏读）
set tran isolation level repeatable read; --（避免脏读，不可重复读）
--set tran isolation level read uncommitted; --（0级 4种并发问题都会产生）
--set tran isolation level serializabl; --（避免避免脏读，不可重复读，幻象读 4种并发问题）
begin tran
---set imolicit_transactions on; --开启一个隐式事务
--一系列的操作 T-SQL insert update delete
insert into UserInfos(UserName,UserPwd,Age,DeptId) values('ryy','abc',17,1)
update UserInfos set Age = 66 where UserId = 12
delete from UserInfos where UserId = 5
commit tran --事务提交
end try
begin catch
rollback tran --事务回滚
print '出现异常'
end catch
--set implicit_transactions off --关闭隐式事务四十、临时表
1.临时表定义
SQL Server临时表是临时对象的一种，临时对象是以#或者##为前缀的， 诸如临时存储过程、临时函数等都是临时对象，临时对象都存储在tempdb数据库中。 当我们关闭数据库时，临时表会自动删除。
2.临时表分类
临时表分为本地临时表和全局临时表，它们在名称、可见性以及可用性上有区别。
以一个#号为前缀的临时表是本地的，只有当前用户会话才可以访问，以两个#号
也就是##为前缀的临时表是全局的。
3.本地临时表
本地临时表不支持跨连接查询。只能在当前连接(或者当前查询页面)访问。
本地临时表的名称以单个数字符号"#" 打头；它们仅对当前的用户连接（也就是创建本地临时表的connection）是可见的；当用户从 SQL Server 实例断开连接时被删除。
SELECT * INTO #Customers FROM UserInfosselect * from #Customers4.全局临时表
全局临时表的名称以两个数字符号 "##"打头，创建后对任何数据库连接都是可见的，当所有引用该表的数据库连接从 SQL Server 断开时被删除。
SELECT * INTO ##Customers FROM UserInfos执行完上面的查询语句后，我们关掉查询页面，再重新开一个页面查询##Customers中的内容
SELECT * FROM ##Customers再重新开一个页面查询，#Customers是本地临时表，打开会报错，而##Customers全局临时表不会，可以查询出来
5.临时表的用途
临时表的优化一般使用在子查询较多的情况下，也称为嵌套查询。我们写如下子查询：
SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN
(SELECT SalesOrderDetailID FROM sales.SalesOrderDetail
WHERE UnitPrice IN
(SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)
)
这是一个比较简单的两层嵌套子查询，我们看一下执行情况：
可以看到这里的逻辑读取是比较高的。
我们用临时表重新来看下执行情况如何，我们将第一二层的查询结果插入到#temp中，然后从临时表中查询结果。
SELECT SalesOrderDetailID INTO #temp FROM sales.SalesOrderDetail
WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)
SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN
(SELECT SalesOrderDetailID FROM #temp)
执行情况如下：
相比上一次的逻辑读，成倍的减少了逻辑读取次数。在对查询的性能进行调节时，如果逻辑读值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果逻辑读值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。
因此我们可以看出临时表在比较复杂的嵌套查询中是可以提高查询效率的。
批注
临时表不管是在SQL Server还是其他平台都有使用，其在查询优化方面可以极大的提高查询效率，而SQL Server平台的临时表相比其他平台更容易创建和使用，其优越性不言而喻。所以如果平时工作或学习过程中，临时表可以作为一个必备技能经常使用。
8936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
