SQL学习笔记

数据库基本概念

文件的形式存在，文件和文件组 组成。

数据库文件

（1）主要数据文件：存放数据和数据库的初始化信息。每个数据库有且只能有一个主要数据文件，（.mdf为扩展名）
（2）次要数据文件：存放除了主要数据文件以外的所有数据的文件。（.ndf为扩展名）（不是必须的，可以没有，也可以有一个，也可以为多个）
（3）事务日志文件：存放用于恢复数据可得所有日志信息。每个数据库至少有一个日志文件。（.df为扩展名）

文件组

定义：是数据可文件的一种逻辑管理单位，他将数据库文件分成不同的文件组，方便对文件的分配和管理。分为两种类型：
（1）主文件组 Primary 主要数据文件和没有明确指派给其他文件组的文件。
（2）用户自定义的文件组 Create DataBase或 alter database语句，fileGroup关键字指定的文件组。
设计原则：
（1）文件只能是文件组的成员；
（2）文件或文件组只能有一个数据库使用；
（3）数据和日志信息不能属于同一个文件或文件组；
（4）日志不能作为文件组的一部分；

数据库常用对象

（1）表：包含数据库中所有数据的对象，行列组成，用于组织和存储数据。
（2）字段：表中的列，一个表可以有多个列，拥有的属性：数据类型、大小（长度）
（3）视图：也是一种表（虚拟表：从一张或多张表导出的表），用户查看数据的一种方式，结构和数据是建立在对表的查询基础之上的。
（4）索引：建立的目的是为了给用户一种快速访问数据的途径，索引是依赖于表而建立的，在检索数据时，不用对整个表进行扫描，可以快速找到所需数据。
（5）存储过程：是一组为了完成特定功能的SQL语句的集合（可以有查询、插入、修改、删除），编译后，存储在数据库中，以名称进行调用，当调用执行时，这些操作就会被执行。
（6）触发器：在数据库中，属于用户定义的SQL事务命令集合，针对于表来说，当用户对表执行增删改查时，命令就会自动触发而执行里面的操作。
（7）约束：对数据表中的列，进行的一种限制。可以更好地来规范表中的列。
（8）缺省值：对表中的列可以指定一个默认值，当用户没有对表中该列进行操作，则会自动将默认值填充到表中。

数据类型分类

（1）number类型：
整型：bigint（8字节）、int（4字节）、smallint（2字节）、tinyint（1字节）（存储大小：从大到小）
浮点型：float （表示近似数值，存在精度损失）、real（也表示近似数值，等于float（24））、decimal【精确数值，不存在精度损失，decimal(指定小数最大位数，指定小数点右侧的小数位数)】
货币类型：money、smallmoney

（2）二进制数值类型
bit、binary(n)、varbinary(n)、varbinary(max)、image

（3）字符型：
char(n) 固定长度 n<=8000
varchar(n) 可变长度字符串 n<=8000
varchar(max) 可变长度字符串 n<=1073741824
text 可变长度字符串 2GB

Unicode字符串(含有中文信息时使用下面类型)
nchar(n) 4000
nvarchar(n) 4000
nvarchar(max) varchar长度的一半
ntext

（4）日期类型（常用的）：
datetime 精度更高
datetime2 精度更高 100纳秒
samlldatetime 精度到分钟 时间范围小
date 仅存储日期
time 仅存储时间

（5） 其他类型：
uniqueidentifier guid全球唯一标识符

创建

（1）创建表：列名、数据类型、是否为null、默认值取
一个表中，会存很多条记录，需要一个列来唯一标识一条数据
同一个数据库不能有同名的表

（2）创建主键:
主键：什么样的列可以设置为主键（唯一），该列的值不能重复；创建一个逐渐会自动创建一个聚集索引。
标识列：该列不能手动插入，插入时自动生成；该列不能是带小数的数值型，一般为整型。标识种子，第一条记录标识列的值 100 ，增量 1、2、3；表中若删除数据，再插入，就会出现不连续的情况。（缺点）
联合主键：（唯一标识）

（3）创建外键：
一般要在两个表之间要建立关联的时候时创建；
一个列创建为外键，它在另一个表中必须是主键；
两个表一旦建立外键关系，外键表里对应的外键列，他的值必须是它对应的主键表里的主键值，不能对应一个不存在的值；
一个表可以有多个外键，也可以没有，一个表只能有一个主键，也可以没有，但一般会有一个主键。
注意：

约束

（1）定义：规定表中的数据规则，如果存在违反约束的数据行为，行为会被阻止。
（2）创建时间：①使用软件创建是在创建表之后；②使用脚本创建表时，可以在创建过程中，也可以在创建后再来创建约束。
（3）分类：
①主键约束：唯一性，费控，不能修改；
②外键约束：加强两个表之间连接。先建立主表中的主键，然后在建立从表中的外键（创建时，只有从表创建了主键，主表才能找到连接，建立外键；另外删除时，先删除从表中的外键，才能删除主表中的主键。而且从表中设置外键的列的值必须是在主表中对应列中选，不能另外自定义，否则会出错。）；
③Unique约束：确保表中的一列数据没有相同的值，与逐渐约束相似，但该约束可以定义多个Unique约束，主键约束只能有一个；
④Check约束：通过逻辑表达式来判断数据的有效性，用来限制输入一类或多列的值的范围。
⑤Default约束：默认值约束。

数据库脚本T-SQL分类

定义：用于创建数据库对象的语句集合。

T-SQL：transact-SQL 基于SQL（structured querylanguage）结构化查询语言，用于应用程序和数据库之间沟通的编程语言。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 （数据定义语言）：创建数据库及其对象 CreateDatabase/Table/View/Proc/Index
（4）DCL （数据控制语言）：用于授予或回收访问数据库的某种特权 commit提交 rollback回滚 grant授权

使用脚本创建数据库 DDL

代码：
use xxxx 选择要操作的数据库 eg:use master --选择系统数据库
go – 批处理命令
create database XXXX --创建数据库（必须在系统数据库中操作）

on primary --创建主文件组
（
name=‘XXXX’,–数据库主要数据文件的逻辑名(eg:newBase)
filename=‘路径’,–主文件组的路径，绝对路径 (eg:E:\MyDataBase\newBase.mdf)
size=XXXXMB,–数据库主要文件的初始大小
filegrowth=XXXXMB–文件的增量
）

log on --创建日志文件
（
name=‘XXXX’,–数据库日志文件的逻辑名(newBase_log)
filename=‘路径’,–日志文件的路径，绝对路径 (eg:E:\MyDataBase\newBase_log.ldf)
size=XXXXMB,–数据库日志文件的初始大小
filegrowth=XXXXMB–日志文件的增量
）
go

T-SQL具体应用

T-SQL创建表 DDL

（准备）产口信息表：标识列（Id），int
编号（ProNo），varchar(50)
名称（ProName），nvarchar(50)
产品类型（TypeId），TypeId int
价格（Price），decimal(18，2)
数量（ProConut），int
产品类型表：编号 TypeId int

代码：
create table 表名
(
Id int identity(1001,1) primary key not null, --给Id设置为int型，设置为标识列，表示为1001，增量为1；并设置为主键，不允许为null(从第一个参数为标识种子，第二个为增量)
ProNo varchar(50) not null,
ProName nvarchar(50) not null,
TypeId int not null, --给TypeId设置为int型，不允许值为null
Price decimal(18,2) null, --设置为小数型，总数为18位，小数位有2两位
ProConut int default (0) null --给ProCount设置为int型，默认值为0，允许值为null
)
go

drop table 表名 --删除表

T-SQL修改表

(1)直接在原来的脚本基础上进行修改：先把脚本代码进行修改，再删除原来的表，然后再执行创建表的脚本代码。(一般不使用，容易造成严重后果，除非表中仅为测试数据)

(2)在原本脚本的基础上进行修改：alter table
语法：
添加一列 alter table 表名 add 列名 数据类型 是否为空
删除一列 alter table 表名 drop column 列名
修改一列 alter table 表名 alter column 列名 数据类型 是否为空
修改列名 (一般不用，很可能会破坏脚本和数据库存储过程)

T-SQL创建约束

(1)在创建表过程中创建约束：
①主键约束：加primary key （如：TypeId int not null primary key）
②外键约束：加foreign key reference 主表名(主键列名)
③unique约束：加unique
④default约束：加default

(2)在创建表完成后在建立约束 add constraint
①主键约束：alter table 表名 add constraint 约束名 primary key(要设置主键的列名)
②外键约束：alter table 表名 add constraint 约束名 foreign key(要设置从表的列名) references 主表(外键对应的主键名)
③unique约束：alter table 表名 add constraint 约束名 unique(列名[可多个])
④check约束：alter table 表名 add constraint 约束名 check(逻辑表达式)
⑤default约束：alter table 表名 add constraint 约束名 default(默认值) for 列名

T-SQL插入数据 DML

(1)单条（行）数据插入

• insert into 表名(列名，列名…) values (‘值,值…’) //插入一条（行）数据
• insert 表名(列名，列名…) select ‘值,值…’

(2)多条（行）数据的插入

• insert into 表名(列名，列名…) values (‘第一行:值,值…’)，(‘第二行：值,值…’),… //插入多条（行）数据
• insert 表名(列名，列名…) select ‘值,值…’ union select ‘值,值…’ //注意最后一个不写union，另外union有去重操作，union all允许重复

(3)克隆表的数据
将原表某列的数据复制到目标表

• insert into 目标表(目标表列) select 原表名 from 列名 //目标表中存在对应列
• select 原表 into 目标表 from 原表列 //目标表中不存在对应列，克隆时自动生成列

T-SQL更新删除数据

更新数据：

注意(主键不可修改；如果不加where条件，会对整张表的数据进行修改；)
update 表名 set 列名=‘’,列名=‘’ where 条件（多个条件用and、or连接）

删除数据

注意(①不加where条件，会删除整张表数据；②标识列的值还是接着删除前的值序号开始自增，不是从初始值序号开始，即删除语句会造成标识列的值不连续；)
(1)只删除数据，保留表；
delete from 表名 where 条件

(2)清空表数据，并且让标识列的值从初始值序号开始
truncate table 表名
(该方法效率比delete效率高，并且不会记录到日志中，但delete每个操作会记录到日志中；
truncate不会记录日志，不激活触发器；一旦删除，不能恢复；
drop truncate是及时操作，不能roolback,delete、update、insert事务中，可恢复)

删除表

drop table 表名

T-SQL数据查询

聚合函数(又称统计函数)

定义：对一组值执行计算并返回单一的值。
count() 统计表中的记录数，（）中写筛选条件，若表中没有记录则返回null；经常与select语句中的group by结合使用。

五种聚合函数：
count 负责记录个数
sum 负责求和
svg 负责求平均
max 负责求最大值
min 负责求最小值

select count(1) Record from 表名 --用于统计表中的记录数，count(1)与count(*)功能一样，而且效率更高
select sun(列名) from 表名 --对某列值求和
select agv(列名) from 表名 --对某列值求平均值
select max(列名) from 表名 --对某列值求最大值
select min(列名) from 表名 --对某列值求最小值

单表查询

select * from 表名 //查询该表所有数据,*代表所有列
select 列名(…) from 表名 //查询部分列的数据
select 列名(…) from 表名 where 条件 order by 列名 aesc/desc

(命别名方法：①列名 as 别名 ②列名 别名 ③别名=列名)
(查询的结果数据以升/降排序呈现：order by 列名 aesc(升序)/desc(降序) 该语句永远放在最后；主键，默认就有排序的功能，从小到大；)

模糊查询

select 列名(列名…) from 表名 where 列名 like …

• like 中SQL中提供4种匹配模式
  ①：% 0个或多个 匹配任意类型和长度
  1）like ‘%ab%’ 匹配包含ab的字符
  2）like ‘%in’ 匹配以in结尾的字符
  3）like ‘w%’ 匹配以w开头的字符
  ②：_ 匹配单个字符
  _代表一个位置，限制字符串的长度
  ③：[] 范围匹配 括号中所有字符中的一个
  ④：[^] 不在括号中所有字符之内的单个字符

范围查询

select 列名 from 表名 where 子句条件 --给定范围

• 子句条件种类：
  1）比较运算符 > < >= <>
  select * from XXX where XX<30 and XX>1

2)in(2,3,4) 在这个范围 not in(2,3,4) 不在这个范围
0；子查询（）中也可写查询语句，如（select XX from XX where XX>1）
where XXX in(2,3,4)

3）between and 等价于 >= and <= ,即规定一个范围
where XX between 20 and 30

分组查询

group by order by having子句
（对某列中值的类别分组筛选）
select… where… group by 列名，列名 order by … --结合聚合函数，根据一列或者多列对结果进行分组group by,排序order by，还有分组后的筛选 如加having 列名>1

高级查询

表连接

根据两个或多个表之间的关系，从这些表中查询数据
分类：内连接、外连接、全连接、交叉连接

内连接 inner join

inner join 使用比较运算符 => < <= > <> 进行表之间的比较，查询与条件相匹配的数据等值连接。

等值连接分为：（两种连接查询方式结果一样）
显示连接：select 列名 from 表一 inner join 表二 on 条件
隐式连接：select 列名 from 表一，表二 where 条件一 and 条件二

外连接

分类：左外连接、右外连接、全外连接（又叫左连接、右连接、全连接）

• 左连接：表一 left (outer) join 表二 on 条件

交叉连接

cross join  迪卡尔积
结果：如果不带where子句时，返回被连接的两个表的迪卡尔积，返回的行数是两个表行数的乘机。如果带where子句时，等价于inner join 返回的是匹配的行数。

类型转换

• convert(目标类型,要转换的对象)
• cast(要转换的对象 as 目标类型) --注意：该函数不能转换日期

求和：数字+数字
字符串拼接：字符串+字符串、（切记不能用 字符串+数字，与JavaScript不同）

字符串操作函数

①在一个字符串中查找一个子串，并返回其位置；
select CHARINDEX(‘子串’，‘主串’) --注意索引从1开始
select PATINDEX(‘%子串%’，‘主串’)

②取长度；
select len(‘子串’)

③大小写转换；
select LOWER(‘子串’)
select UPPER(‘子串’)

④去除空格；
select LTRIM(‘’) --去左边空格
select RTRIM(‘’) --去右边空格

⑤克隆；
select REPLICATE(‘’,重复次数)

⑥逆置；
select REVERSE(‘’)

⑦替换；
select REPLACE(‘主串’,‘被替换的字符串’,‘替换的字符串’)
select STUFF(‘主串’,指定位置,指定长度,‘替换的字符串’)

⑧取子串；
select LEFT(‘’,取字符的个数) --从左边开始数起
select RIGHT(‘’,取字符的个数) --从右边开始数起
select SUBSTART(‘’,开始位置,取的个数) --从左边开始数起

索引

基本概念

• 作用：优化数据的查询、处理速度，提高数据库的性能。
• 定义：相当于目录，能快速的定位。
• 分类：唯一索引、主键索引、聚集索引、非聚集索引

1）唯一索引（UNIQUE）：唯一索引不允许两行具有相同的索引值；
2）主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空；
3）聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表最多只能有一个；
4）非聚集索引(NonClustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个。效率比聚集索引要低些。

（如果需要多个列上建立索引，这些列建立组合索引）
（一般用数据类型做索引）
（索引在表中是看不见的，但在外面可以看到）

脚本创建索引

//当不指定时，即不写clustered和nonclustered，默认建立非聚集索引。
语法：

• 创建聚集索引 主键
  create clustered index 索引名称 --若要创建唯一索引要加unique，否则不需要
  on 表名(列名)
  with
  (
  drop_existing=on, --一般为on，当为on时先删除原来的，再创建一个新的；若 为off则不删除原有的，但会提示错误，显示索引已存在。

  )

• 创建非聚集索引
  create unique nonclustered index 索引名称 --若要创建唯一索引要加unique，否则不需要
  on 表名(列名)
  with
  (
  drop_existing=on,
  pad_index=on, --要设置填充因子，要设置该值为on，填充因子才能生效
  fillfactor=50, --指定创建索引时，每个索引页的数据占索引页大小的百分比
  ignore_dup_key=on
  )

• 创建复合索引
  create nonclustered index 索引名称 --要创建唯一索引要加unique
  on 表名(列名)
  with
  (
  drop_existing=on, --一般为on，当为on时先删除原来的，再创建一个新的；若 为off则不删除原有的，但会提示错误，显示索引已存在。

  )

视图

基本概念

• 定义：是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上的，实际视图不存放数据，而是存放在视图所引用的原始表中。
• 与表的区别：表是存储数据的地方，视图存储的是查询语句（注意：索引视图除外，因为索引视图被具体化了，实际占据了存储空间。）
• 作用：简化查询，增加数据的保密性，提高安全性。
• 缺点：只是简化查询，但不提高查询速度，还会增加维护成本。
• 分类：
  • 标准视图：（用的最多）存储了查询定义，没有存储数据。
  • 索引视图：被具体化的视图，创建了索引，显著提高查询性能，聚合了多行的查询，不适合经常更新的基本数据集，索引视图被具体化了，实际占据了存储空间。
  • 分区视图：一台或多台服务器之间通过 union all 水平连接一组成员表的分区数据，没有存储数据。

其他注意事项：
基表：视图中引用到的原始表；
修改基表中的数据会影响视图的数据；
修改视图中的数据也会影响基表中的数据；
在视图的基础上可以再建立视图；
视图中，在更新数据时不能使用聚合函数；

脚本创建视图

• 标准视图
  create view 视图名称
  as
  –T-SQL语句
  select 列名，列名… from 表一 --select中可以使用*号
  inner join 表二 on 条件语句
  go

• 索引视图
  create view 视图名称 with schemabinding
  as
  select 列名1，列名2… from dbo.表名
  go

  注意：其一，在索引视图中，select中不能出现*号，必须要写具体的列号；其二表名前面必须使用表的所有者 dbo

• 分区视图
  create view 视图名称
  as
  select * from 表1 –可以用具体列名代替，–select中可以使用号
  union all --允许重复
  select * from 表2
  go

其他注意事项
– 使用视图的语法与使用表一样
– 可以使用视图建立索引
– 在原本脚本的基础上对视图进行修改：alter view （不要对索引视图进行修改，因为会对原始表产生影响）
– 标准视图和分区视图都不允许删除修改里面的数据，会影响基础表
语法：
alter view 视图名称
as
–T-SQL语句
select 列名，列名… from 表一 inner injoin 表二 on 条件语句
go

存储过程

基本概念

• 定义：
  一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，用存储过程来加速SQL语句的执行。调用名称，传入参数，执行完成特定功能。

• 分类

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

  • 自定义存储过程
    用户自己创建，创建特定功能，可以传入参数；也可以有返回值，来表明存储过程执行是否成功；里面可以有一个操作，也可以有多个操作。

  执行语法：execute/exec 存储过程名称 参数列表（多个参数，以逗号隔开）

• 优点：
  1、提高应该用程序的通过性和移植性。多次调用，不需要重新在编写，维护人员可以随时修改。如：数据库位置发生改变，只要将存储过程备份过去，重新运行即可。
  2、可以有效地管理数据库的权限。
  3、可以提高SQL的效率。
  4、减轻服务器的负担。

• 缺点
  需要专门人员维护；占用数据库空间，造成浪费、

脚本创建存储过程

• 基本语法
  create proc 存储过程名称
  参数列表
  as
  begin
  T-SQL语句
  end
  go

• 创建create/修改alter无参数存储过程
  create/alter proc 存储过程名
  as
  begin
  select 列名，列名… from 表名
  select * from 表
  end
  go

  exec 存储过程名 --调用执行
  drop proc 存储过程名 --删除存储过程

• 创建create/修改alter有参数存储过程
  create/alter proc 存储过程名
  @参数1 –参数一定要带@
  @参数2
  @参数3
  as
  begin
  declare @time datetime --用declare定义一个局部变量@time
  set @time=getdate() --select可以为多个变量赋值，set只能为一个变量赋值
  insert into 表名(列名1，列名2，列名3，列名4)
  value (@参数1,@参数2,@参数3,@time)
  select * from 表名
  end
  go

  exec 存储过程名 实参1，实参2，实参3，实参4 --调用执行
  drop proc 存储过程名 --删除存储过程

触发器

基本概念

• 定义：一种特殊类型的存储过程.不同之处在于：存储过程人为调用名称执行；触发器通过事件触发，自动来调用执行。

• 操作：插入、更新、删除、自动执行触发器。

• 分类：

  • DDL触发器：create alter drop
  • DML触发器：insert update delete
    - DML又分为两种：
    1）after触发器 insert update delete --操作后触发
    2）instead of触发器 insert update delete --不执行定义操作，而是执行触发器本身

• 两种特殊的表
  inserted 插入表
  deleted 删除表
  上面两种表既是逻辑表，也是虚表，是系统在内存中创建的，不会存储到数据库 中，且只读（不能修改数据）；其结构与之前操作的表相同；这两种表在触发器执行 过程中存在，并且可以访问；触发器执行完成后，这两张表会被自动删除。

    对表的操作			inserted表			deleted表
     insert			存放插入的数据				无
     update			存放更新后的数据		存放更新前的数据
     delete 		 	无			  	   存放被删除的数据
  

  inserted 只会存放插入或更新后的数据
  deleted 只会存放更新前或删除的数据

  触发器的执行过程中，这两张表都可以访问，提交之前，可以撤销—即事务的回滚
  触发器是一个特殊的事务，一些检查，设置条件，若不满足结果，可以回滚。

after触发器的insert update delete

after触发器：在对表操作后触发

after insert

• 定义触发器
  create/alter trigger 触发器名称 --alter 为修改操作
  on 表名
  for insert --可以写after insert
  as
  T-SQL语言 --可以查询。显示inserted表中的值，但不可修改
  go

• 触发：当执行任何的insert操作后，它都会自动触发

after update

• 定义触发器
  create/alter trigger 触发器名称 --alter 为修改操作
  on 表名
  for update --可以写after insert
  as
  T-SQL语言 --可以查询。显示inserted，deleted表中的值，但不可修改
  go

• 触发：当执行任何的update操作后，它都会自动触发

after delete

• 定义触发器
  create/alter trigger 触发器名称 --alter 为修改操作
  on 表名
  for delete --可以写after delete
  as
  T-SQL语言 --可以查询。显示deleted表中的值，可以将其放到备份表中，防止误删后可以恢复，但不可直接修改
  go

• 触发：当执行任何的delete操作后，它都会自动触发

instead of insert

不执行原本定义操作，转而去执行触发器中的操作
定义触发器
create/alter trigger 触发器名称 --alter 为修改操作
on 表名
instead of insert
as
T-SQL语言 --可以查询。显示deleted表中的值，可以将其放到备份表中，防止误删后可以恢复，但不可直接修改
go

after 与instead of的区别：

after insert 在触发器中，标识列已经自动生成，有值
instead of insert 在触发器中，标识列并未生成，而且没有值 ，执行完毕后，数据并不会插入到目标表中

所以after与instead of触发器不要同时定义。

游标

基本概念

• 定义：实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。提供了一种对从表中检索出的数据进行操作的灵活手段。

• 作用：定位到结果集中的某一行，对当前位置的数据进行读写。

• 缺点：不适用于数据量大的情况使用。因为：读取处的数据放到tempDB（临时库）中，存放在内存，内存空间有限，不能存放大量数据。

• 游标的分类：
  根据游标中的数据是否会随源数据变化分为【静态游标】和【动态游标】
  根据游标是否可以回滚可以分为【只进游标】和【滚动游标】

  • 静态游标：静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。

  • 动态游标：这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。

  • 只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时对数据的操作是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。

  • 键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

游标生命周期：声明/定义–打开–读取数据–关闭游标–释放游标

创建

只进游标的创建

declare 游标名 cursor local   --定义游标 ，global为全局、local为局部游标
for 
select 列1，列2，列3 from 表名 where 条件子句	 
open 游标名					 --打开游标
declare ...				    --定义变量
set @XXXX=游标名	 		--给游标变量赋值
fetch next from @XXXX into 变量  --提取数据
while @@FETCH_STATUS=0  	--判断是否执行成功
begin 
	print cast()			--这里面操作可以自定义
end
close 游标名    --关闭游标
deadlocal 游标名  --释放游标

静态游标的创建

declare 游标名 cursor static local   --定义静态游标 ，global为全局、local为局部游标
for 
select 列1，列2，列3 from 表名 where 条件子句	 
open 游标名					 --打开游标
declare ...				    --定义变量
set @XXXX=游标名	 		--给游标变量赋值
fetch next from @XXXX into 变量  --提取数据
while @@FETCH_STATUS=0  	--判断是否执行成功
begin 
	print cast()			--这里面操作可以自定义
end
close 游标名    --关闭游标
deadlocal 游标名  --释放游标

动态游标的创建

declare 游标名 cursor dynamic local   --定义动态游标 ，global为全局、local为局部游标
for 
select 列1，列2，列3 from 表名 where 条件子句	 
open 游标名					 --打开游标
declare ...				    --定义变量
set @XXXX=游标名	 		--给游标变量赋值
fetch next from @XXXX into 变量  --提取数据
while @@FETCH_STATUS=0  	--判断是否执行成功
begin 
	print cast()			--这里面操作可以自定义
end
close 游标名    --关闭游标
deadlocal 游标名  --释放游标

自定义函数

• 概念：
  根据自己的需要，自定义一些函数
  分类：标量函数、内嵌表值函数、多声明表值函数

标量函数

对单一值的操作，返回单个数据值。

• 语法：
  定义：
  create function [dbo].函数名(@参数名 类型) --[dbo]为函数所有者、(@参数名 类型=默认值)
  returns 返回值的类型 --with encryption
  as
  begin --begin end内是函数体，自定义内容T-SQL语句，不能有修改数据的操作
  declare @定义的变量 类型
  select @变量赋值 from 表名 where 条件语句
  return @返回的变量

  end
  go

函数调用：
select dbo.函数名(参数) as 年龄

• 注意点：
  –如果函数所有者dbo在定义时有写，那么调用的时候一定要写。
  –在调用的时候，如果函数中指定了默认值，调用的时候，就直接使用default代替参数，如果没有指定默认值时，调用时使用default，则返回null。
  –注意returns 后面写的是返回值类型，return 后面写的是返回值

内嵌表值函数

以表的形式返回一个返回值，即它返回的是一个表。(没有begin end的语句括起来。)

• 语法：
  定义：
  create function [dbo].函数名(@参数名 类型) --[dbo]为函数所有者、(@参数名 类型=默认值)
  returns table --with encryption
  as
  return(
  select 列名… from 表名 where 条件语句
  )
  go

函数调用：
select * from dbo.函数名(参数) where 条件语句 --因为返回的是一个表，可以当做一个表来使用。

• 注意点：
  –returns 返回值类型只能是table
  –as 后面没有begin end 只能是return(select语句)

多声明表值函数

返回一个表，有begin end的语句函数体括起来。返回的表可以定义表名和表的结构。是前两种函数的结合体。
其返回的表数据是有结构的，由函数体中的语句插入的。可以多次查询，多次筛选与合并。

• 语法：
  定义：
  create function [dbo].函数名(@参数名 类型) --[dbo]为函数所有者、(@参数名 类型=默认值)
  returns @定义的返回表名 table( --定义表的结构
  Id in not null primary key,
  Uname varchar(50) not null,
  Uage int null
  )
  as
  begin --begin end内是函数体，自定义内容T-SQL语句，不能有修改数据的操作
  insert into 定义的表名(要插入的列)
  select UserId，UserName，Age from UserInfos
  where UserName like ‘%’+@uName+‘%’ --insert into… select…where该语句是将查找到的信息插入我定义要返回的表中。
  return --注意return后面什么也不跟
  end
  go

函数调用：
select * from dbo.函数名(参数)

• 注意点：
  –多声明表值函数一般当做查询使用，不能对数据进行修改
  –若内嵌表值函数满足使用需求，则尽量不适用多声明表值函数
  –注意return后面什么也不写

事务

基本概念

• 定义：

  • 数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行（即没有发生任何错误，全部提交）,要么全部不执行（只要出现一个错误，都会回滚到最初状态），这些操作是聚集在一起的，是一个不可分割的工作单位，而且对于不同的事务来说，每一个事务都是唯一的。 事务 由事务开始与事务结束之间执行的全部数据库操作组成。

• 作用：
  1）在一个事务中对多个表进行操作。
  2）为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  3）当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

• 特性：
  1）原子性：atomic 事务是一个整体，一个工作单元，它们都是一个整体提交或回滚，各个元素不可分割。
  2）一致性：数据必须保持一致，数据状态保持一致。
  3）隔离性：多个事务之间是彼此隔离的。
  4）持久性：事务提交后，对数据库的修改是永久保存的，即使系统出现故障。

• 事务的分类：显性事务、隐式事务

事务的创建

显、隐式事务创建

• 语法：
  begin try
  set tran isolation level read committed --设置隔离级别，默认该语句不用写
  begin tran --开启事务
  set implicit_transactions on --开启隐式事务加此语句，不加则为显性事务

    -- 一系列的操作 T-SQL  insert update  delete
  

  commit tran --事务提交
  end try

  begin catch --上面操作出现错误时执行下列代码
  rollback tran --事务回滚
  end catch

  set Implicit_transactions off; --关闭隐式事务，显性事务不需要写该语句

事务并发

当多个用户对同一条数据进行访问时，就会造成事务并发

• 导致的问题：
  1）丢失或覆盖更新
  2）脏读（一个事务读取了另一个事务并未提交的更新数据）
  3）不可重复读（多个事务访问同一条数据，但每次读取的数据不同）
  4）幻想读（与不可重复读类似，即第一次读取的数据与第二次读取的数据不同）

• 解决方法：设置事务隔离级别（即设置事务接受的不一致数据访问级别）

• 隔离级别：

  • read committed 默认的级别，避免脏读，会产生不可重复度、幻想读
  • read uncommitted 0级，上面4种问题都会发生
  • repeatable read 可避免脏读、不可重复读，但会产生幻想读
  • serializable 可避免脏读、不可重复读、幻想读

设置隔离级别：set tran isolation level read committed --在begin tran语句前书写