SQL Server

发布时间：2023-06-18 作者：admin 来源：文学

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创建表

产品信表:

标识列 int

编号 varchar(50)

名称 nvarchar(50)

产品类型 TypeId int

价格 decimal(18,2)

数量 int

产品类型表:

编号 TypeId int

名称 nvarchar(50)

tips:

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

2.只有数据类型是整形的字段才能够被设置标识符

3.标识列和编号都可以被设置为主键,如果想要检索速度更快,将标识列设置成主键.因为标识列是整形设置成主键时创建的索引是聚集索引,而编号的数据类型是字符串类型,设置成主键时创建的索引是非聚集索引(检索时聚集索引要比非聚集索引性能更好)

create table aaasong ( id int primary key, num varchar(250) ) create table aaasong2 ( id int , num varchar(250) primary key ) , 这两个表创建的都是聚集索引呀?所以这个观点就是错误的.不管整形还是字符类型的列都可以创建成聚集索引,在插入数据时会根据聚集索引列数据的排序决定其在物理存储位置的顺序

4.价格数据类型可以用decimal,也可以用money(精确到万分位),都是精确值

创建表:

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个以上非聚集索引

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

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

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

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 on后,我们再执行sql命令时系统就不会为我们自动创建事务了

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

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

事务不隔离导致的问题

以上介绍完事务的四大特性(简称ACID)，现在重点来说明下事务的隔离性，

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，

在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

更新丢失（Lost update）、脏读（Dirty Reads）、不可重复读（Non-repeatable Reads）

1、丢失更新（Lost update）

丢失更新就是两个不同的事务（或者Java程序线程）在某一时刻对同一数据进行读取后，先后进行修改。导致第一次操作数据丢失。

示例如下:

2、脏读（Dirty Reads）

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，这就是脏读。

3、不可重复读（Non-repeatable Reads）

不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。

（1）虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

（2）幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。

事务的隔离级别

为了避免上面出现的几种情况，在标准SQL规范中，定义了4+2个事务隔离级别，不同的隔离级别对事务的处理不同

1、未提交读取（相当于with(nolock)）：第一级别

　　允许脏读取，但不允许丢失更新。

如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。

　　该隔离级别可以通过“排他写锁”实现。

　　缺点：会产生脏读、不可重复读、幻读

　　案例解读：以前交易所炒股的时候，股民老王购买了5000股，操作员操作录入（此时开启事务），操作时手误，多输入了一个0，数据保存但是未提交。

此时老王查询自己的持股信，发现自己居然有50000股，瞬间血压升高，昏倒在地。

然后操作员发现自己录入错误，修改成正确的信，录入完成（事务结束）。

老王被救醒后，哆嗦这查询自己的持股，发现只有5000，没有增减，他之前看到的就是脏读数据。

　　解决方案：采用更高级的隔离机制，如提交读。

2、提交读取（Oracle和SQLServer默认的）：第二级别

　　这是大多数数据库系统的默认隔离级别（Oracle和SQLServer默认的）。

　　也称为授权读取：允许不可重复读取，但不允许脏读取。

这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

　　缺点：会产生不可重复读、幻读。

　　案例解读：股市升高后，老王查看自己持有5000股，就想卖掉4000股，在老王卖股票的时候，老王的老婆看股市太高，就登录老王的账号，卖掉3000股。

当老王想卖股票时，发现自己只有2000股，不是之前看到的5000股，这就是不可重复读问题。

　　解决方案：采用更高级的隔离机制，如可重复读。

上面举的例子难以理解,可以看下面的例子

一个顾客到小王这里来预定四间房，小王将这四间房预定了出去，该事务还没提交的时候另一个顾客到小李这里来预定房间，小李查询系统发现还有四间房，刚想预定的时候小王的事务提交了，小李的系统立马呈现0间房。这就是不可重复读造成的影响。

3、可重复读取（相当于(HOLDLOCK)）：第三级别

　　MySQL的默认事务隔离级别。

　　可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。

这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

　　缺点：会产生幻读。

　　问题解读：股市忽涨忽跌，老王焦虑不安，按捺不住，想把持有的多种股票全部抛掉。与此同时，老王老婆听信专家所言，使用老王的账号买了某只神股。

老王抛掉所有股票后，查看自己的持股，猛然发现自己居然还持有一只股票，瞬间觉得一脸懵逼，这就是幻读导致。

　　解决方案：采用更高级的隔离机制，序列化。

4、序列化（这是最高的隔离级别）：第四级别

　　序列化（Serializable）：提供严格的事务隔离。

它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。

仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

　　缺点：可以解决并发事务的所有问题。但是效率地下，消耗数据库性能，一般不使用。

　　隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。

尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

5、快照

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

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

6、已提交读快照

READ COMMITTED SNAPSHOT也是基于行版本控制，但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本，

而不是事务前的已提交版本，有点类似前面的READ COMMITTED能保证已提交读，但是不能保证可重复读，不能避免幻读，

但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据。

事务的隔离级别设置示例

　　SQL Server通过在锁资源上使用不同类型的锁来隔离事务。

为了开发安全的事务，定义事务内容以及应在何种情况下回滚至关重要，定义如何以及在多长时间内在事务中保持锁定也同等重要。

这由隔离级别决定，应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下：

获取事务隔离级别(isolation level)

DBCC USEROPTIONS

1、未提交读取

新建回话并将学生编号为100001的成绩+1；

BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001'

然后执行错误的回话，进行回滚

BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+'' WHERE Number='100001'

然后在查询数据

--首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁，所以查询一直在等待锁释放*/ SELECT * FROM a_StudentsScore WHERE Number='100001' ---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读，读操作之前不请求共享锁。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM a_StudentsScore WHERE Number='100001' --当然也可以使用表隔离,效果是一样的 SELECT * FROM a_StudentsScore WITH (NOLOCK) WHERE Number='100001'

我们发现执行两个事务回话，第一个执行成了，但是第二个执行失败了进行回滚，最后查询的数据是第一个执行前的数据，没有任何改变。

2、提交读取

新建回话1并将学生编号为100001的成绩+1，此时回话的排他锁锁住了学生编号为100001的成绩

BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001'

在回话2中执行查询，将隔离级别设置为READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM a_StudentsScore WHERE Number='100001' --由于READ COMMITTED需要申请获得共享锁，而锁与回话1的排他锁冲突，回话被堵塞， --在回话1中执行事务提交 COMMIT TRANSACTION --由于回话1事务提交，释放了学生100001的排他锁，此时回话2申请共享锁成功查到学生100001的C#成绩为修改后的成绩81，READ COMMITTED由于是已提交读隔离级别，所以不会读脏数据.

重置数据

UPDATE a_StudentsScore SET C#=80 WHERE Number='100001'

注意：但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,

也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

3、可重复读取

在回话1中查询学生编号为100001的成绩，,将回话级别设置为REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'

新建回话2修改学生编号为100001的成绩

UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' ---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束，所以回话2无法获取排他锁，处于等待状态

在回话1中执行下面语句，然后提交事务

SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION

回话1的两次查询得到的结果一致，前面的两个隔离级别无法得到一致的数据，此时事务已提交同时释放共享锁，回话2申请排他锁成功，对行执行更新

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致，同时保证了丢失更新丢失更新：两个事务同时读取了同一个值然后基于最初的值进行计算，接着再更新，就会导致两个事务的更新相互覆盖。例如酒店订房例子，两个人同时预定同一酒店的房间，首先两个人同时查询到还有一间房间可以预定，然后两个人同时提交预定操作，事务1执行number=1-0，同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖，REPEATABLE READ隔离级别就能避免这种丢失更新的现象，当事务1查询房间时事务就一直保持共享锁直到事务提交，而不是像前面的几个隔离级别查询完就是否共享锁，就能避免其他事务获取排他锁。

4、序列化

SERIALIZABLE(可序列化)，对于前面的REPEATABLE READ能保证事务可重复读，但是事务只锁定查询第一次运行时获取的数据资源（数据行），

而不能锁定查询结果之外的行，就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间，有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时，

那么在第二次查询的结果中就会存在这些新插入的数据，使两次查询结果不一致，这种读操作称之为幻读。

为了避免幻读需要将隔离级别设置为SERIALIZABLE。

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'

在回话2中执行修改操作

UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001'

返回回话1重新执行查询操作并提交事务

SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION

结果回话1中第二次查询到的数据包含了回话2新修改的数据，两次查询结果不一致（验证之前的隔离级别不能保证幻读）

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001'

在回话2中执行修改操作

UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001'

返回回话1重新执行查询操作并提交事务

SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION

两次执行的查询结果相同

重置所有打开回话的默认隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

5、快照

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

在打开的所有查询窗口中执行以下操作

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

--在回话1中打开事务,将学生100001的成绩加1,并查询跟新后的成绩 BEGIN TRANSACTION UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' SELECT * FROM a_StudentsScore WHERE Number='100001' ---查询到更新后的成绩为81 ---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' ---查询到的结果还是回话1修改前的成绩,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中 --在SNAPSHOT级别启动事务会请求行版本 ---现在在回话1中执行提交事务,此时学生100001的成绩为81 COMMIT TRANSACTION ---再次在回话2中查询学生100001的成绩并提交事务,结果还是80,因为事务要保证两次查询的结果相同 SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION ---此时如果在回话2中重新打开一个事务,查询到的学生100001的成绩为81 BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION --SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以学生100001的最后提交版本还是修改前的成绩80, --所以回话2读取到的成绩是回话2事务开始前的已提交版本成绩80,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的成绩已经是81了， --所以查询到的成绩是81，同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别

6、已提交读快照

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本，

有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项，

在回话1，回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接，可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)。

--在回话1中打开事务,将学生100001的成绩加1,并查询跟新后的成绩,并保持事务一直处于打开状态 UPDATE a_StudentsScore SET C#=C#+1 WHERE Number='100001' --查询到的成绩是81 SELECT * FROM a_StudentsScore WHERE Number='100001' --在回话2中打开事务查询学生100001并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本) BEGIN TRANSACTION SELECT * FROM a_StudentsScore WHERE Number='100001' --查询到的成绩还是80 --在回话1中提交事务 COMMIT TRANSACTION --在回话2中再次执行查询学生100001的成绩,并提交事务 SELECT * FROM a_StudentsScore WHERE Number='100001' COMMIT TRANSACTION --此时的成绩为回话1修改后的成绩81,而不是事务之前已提交版本的成绩,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项。

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF; ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

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

如果你已经将上面的全部掌握,那么就可以开始进行下面知识的学习了!

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这样的外键时,这就意味着你的设计没有标准化, 至于它违背了第几范式并不重要,还是想想如何消除重复数据

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语句优化