Sql Server总结
时间:2022-03-10 17:47
主键
主键就是数据行的唯一标识。不会重复的列,才能当主键。一个表可以没有主键,但是会非常难以处理,因此没有特殊理由表都要设定主键。主键有两种选用策略:业务主键和逻辑主键。
业务主键是使用有业务意义的字段做主键,比如身份证号、银行账号等;
逻辑主键是使用没有任何业务意义的字段做主键,完全给程序看的,业务人员不会看的数据。因为很难保证业务主键不会重复(身份证号重复)、不会变化(帐号升位),因此推荐用逻辑主键(自增主键)。
主键注意的地方:
1.主键的作用:唯一标识表中的一条记录。
2.选择多列同时作为一个主键→组合主键(复合主键).(一般不建议采用)
3.尽量选择单列作为主键:
1>必须唯一(不能有重复),
2>该列不能为空值。
3>比较稳定的列(不经常更新的,最好是建好以后再也不更新。)
4>选择主键列的时候,推荐使用“逻辑主键”(例如:自动编号(bigInt)、guid(数据类型:uniqueidentifier)等。),不推荐“业务主键”(选择有实际意义的列作为主键(例如:身份证号,工号、学号等。))
自增主键注意事项
自动编号只会向前增长,不会倒退。如果插入数据出错,自动编号也会增长,下次插入时,编号会“跳过”。自动编号列是不能人为修改的。(一般情况)。这也间接说明了,sql语句操作本身就是存储过程,只是sqlServer帮我们自动提交和回滚。
自增主键再设计时将自增功能关闭,那么就可以人为的设置主键大小,下次再插入数据时,自增主键会在你设置的大小上进行累加。打开和关闭自增主键的自增功能代码:set IDentity_insertTblTacheron|off。
若想主键恢复到开始时(零),那么需要用删除数据:truncate table t1将数据全部清空,此时自增主键将变为1。
外键
作用:消除数据冗余。
当主键更新时,外键也会随之而更新。
条件:选用的外键必须是主键
注意:当创建了主外键关系后,如果在外键表中有任何记录引用了主键表中的某条记录,则在主键表中不能删除该记录。同时主键表也不能删除。
新建一张表:员工信息表
约束
用来控制数据的类型,大小等。
主键、外键、约束的用法:
数据库的设计中关系中建立。
数据库的设计中列的属性。
自己写sql语句关键字确定。
数据类型
二进制数据类型
Image存储非子符和文本的数据可用来存储图像
二进制数据占用的空间都比较大,一般不使用。
文本数据类型:字符数据包括任意字母、符号或数字字符的组合
Char 最大8000
固定长度的非 Unicode 字符数据。固定长度的字符串相对于可变长度的字符串来说效率要高一些,在数据长度固定的情况下优先选用固定长度,省去了计算长度的过程,提高效率。例如:Char(10),10表示10个字符。能存储10个ASCII字符和5个Unicode字符。
Nchar最大4000
固定长度的Unicode 数据。
Varchar最大8000
可变长度非Unicode 数据。
Nvarchar最大4000
可变长度Unicode 数据。
Ps:代N的一般都是Unicode编码格式,带var是可变长度。
Text和varchar(max)
存储长文本信息(指针,2G)varchar(max),大字符串类型可以保存非常多的字符,但是对于这种类型的数据DBMS经常将它们保存到单独的空间中,这就导致了数据的保存和加载速度比较慢,因此除非必要,否则不要使用。Ntext和nvarchar(max)可变长度。
日期和时间
Datetime日期和时间,日期和时间在单引号内输入
数字数据
该数据仅包含数字,包括正数、负数以及分数。Int、smallint整数;Float、real数字;
货币数据类型
Money用于十进制货币值,money 和smallmoney数据类型精确到它们所代表的货币单位的万分之一。
Bit数据类型
Bit存储布尔数据类型,表示是/否的数据。Bit类型在管理器上只能用true和false,在写程序代码的时候只能用1和0。
数据类型的意义:
1>提高效率。(减少空间,提高访问效率,‘1234567’如果用字符串存储占7个字节,如果用整型则占4个字节。);
2>能对数据进行正确的计算1+1,如果是整型则为2,如果为字符串则为11。
不同的数据类型占用空间不尽相同,删除数据后又有的空间还被占用并没有释放。此时可收缩数据库,来回收没有用的数据空间。
Sql语句:
SQL 全名是结构化查询语言(Structured Query Language),是关系数据库管理系统的标准语言。SQL语句是和DBMS“交谈”专用的语句,不同DBMS都认SQL语法;SQL语句中字符串用单引号、单等号(在数据库中两个‘表示一个‘也就是 ‘‘转义‘);SQL语句是大小写不敏感的,不敏感指的是SQL关键字,字符串值还是大小写敏感的;
SQL主要分DDL(数据定义语言,建表、建库等语句。)、DML(数据操作语言)和DCL (数据库控制语言)。Create Table、Drop Table、Alter Table等属于DDL,Select、Insert、Update、Delete等属于DML, GRANT 授权、REVOKE 取消授权属于DCL 。
创建数据库例子:
空值处理
数据库中,一个列如果没有指定值,那么值就为null,数据库中的null表示“不知道”,而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。SQL中使用is null、is not null来进行空值判断,可以用IsNull来对null数据进行转换。
数据排序
ORDER BY子句位于SELECT语句的末尾,它允许指定按照一个列或者多个列进行排序,还可以指定排序方式是升序(从小到大排列,ASC)还是降序(从大到小排列,DESC)。Order by 语句一般要放到所有语句的后面,就是先让其他语句进行筛选,全部筛选完成后,最后排序一下。
表中数据是集合,集合是没有顺序的。Order by 返回的数据是有顺序的,故此我们把order by 以后返回的数据集合叫“游标”。
数据分组
在使用select查询的时候,有时需要对数据进行分组汇总,这时就需要用到group by语句。select 语句中可以使用group by 子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息。//分组一般都和聚合函数连用。
GROUP BY子句必须放到WHERE语句的之后 ,Group By与Order By都是对筛选后的数据进行处理,而Where是用来筛选数据的。没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的(聚合函数中除外)。
Having语句
对表中的数据分组后,会得到一个分组后的结果集,如何对该结果集在进行筛选?having
注意Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。
Having 是Group By的条件对分组后的数据进行筛选(与Where类似,都是筛选,只不过having是用来筛选分组后的组的。)
在Where中不能使用聚合函数,必须使用Having,Having要位于Group By之后。
Having的使用几乎是与where一样的,也可以用in。
Sql语句的执行顺序:
5>…Select 5-1>选择列,5-2>distinct,5-3>top
1>…From表
2>…Where 条件
3>…Group by列
4>…Having筛选条件
6>…Order by列
类型转换函数
CAST ( expression AS data_type)
CONVERT ( data_type, expression,[style])
CAST(RIGHT(sNo,3) ASINTEGER) as后三位的整数形式,
类型转换只是转换查询出来的数据类型,数据库中真实的数据类型并没有转换。
联合结果集union(集合运算符)
集合运算符是对两个集合操作的,两个集合必须具有相同的列数,列具有相同的数据类型(至少能隐式转换的),最终输出的集合的列名由第一个集合的列名来确定。(可以用来连接多个结果)
联合(union)与连接(join)不一样。(join说的是left join等)
基本的原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容。
联合:将多个结果集合并成一个结果集。union(去除重复,相当于默认应用了distinct)、union all
切记:在使用union连接数据的时候,必须保证对应的列的数据类型是一致的,如果数据类型不一致,则报错。若数据类型不一致时,此时可以使用类型转换:cast() convert()。
一次插入多条数据
把现有表的数据插入到新表(表不能存在),例如,为表建备份。
select * into newStudent from student(newStudent表在select查询的同时自动建立。)--通过这种方式复制,只能复制表中的数据,以及列的名字和数据类型。对于约束,不会复制过来。
Select * into newTbl from oldTbl where 1<>1,这样做可以只复制表结构,但效率并不高。建议:select top 0 * into newTbl from oldTbl
如果表已经存在的话:insert into backupStudent select * from students(backupStudent表必须提前建好)
插入新数据时比较特殊的用法:
insert into Score(studentId,english,math)
select 1,80,100 union
select 1,80,100 union
select 3,50,59 union all
select 4,66,89 union
select 5,59,100
此处如果用union all同样会去除重复数据。
字符串函数:
LEN() :计算字符串长度(字符的个数。)
datalength();//计算字符串所占用的字节数,不属于字符串函数。
LOWER() 、UPPER () :转小写、大写
LTRIM():字符串左侧的空格去掉
RTRIM () :字符串右侧的空格去掉
LTRIM(RTRIM(‘ bb ‘))
LEFT()、RIGHT() 截取取字符串SELECT LEFT(‘abcdefg‘,2)
SUBSTRING(string,start_position,length),索引从1开始。参数string为主字符串,start_position为子字符串在主字符串中的起始位置,length为子字符串的最大长度。SELECT SUBSTRING(‘abcdef111‘,2,3)
日期函数:
GETDATE():取得当前日期时间
DATEADD (datepart , number, date ),计算增加以后的日期。参数date为待计算的日期;参数number为增量;DATEADD(DAY, 3,date)为计算日期date的3天后的日期,而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期。
DATEDIFF ( datepart , startdate , enddate ) :计算两个日期之间的差额。datepart为计量单位,可取值参考DateAdd。例如,统计不同入学年数的学生个数:select DateDiff(year,sInDate,getdate()),count(*) from student Group by DateDiff(year,sInDate,getdate())
DATEPART (datepart,date):返回一个日期的特定部分。也可以用Month()、year()、day()来代替。
Datepart可选值
取值 别名 说明
year yy,yyyy 年份
quarter qq,q 季度
month mm,m 月份
dayofyear dy,y 当年度的第几天
day dd,d 日
week wk,ww 当年度的第几周
weekday dw,w 星期几
hour hh 小时
minute mi,n 分
second ss,s 秒
millisecond ms 毫秒
Case函数用法
(1)
等值判断,相当于switch case
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
ELSE defaultreturnvalue
END
在Expression中可以添加判断条件。例如某列>1等。
(2)
CASE
WHEN condition1 THEN returnvalue1
WHEN condition 2 THEN returnvalue2
WHEN condition 3 THEN returnvalue3
ELSE defaultreturnvalue
END
相当于if…else if…else….(可以进行区间判断)
索引
全表扫描:对数据进行检索(select)效率最差的是全表扫描,就是一条条的找。如果没有目录,查汉语字典就要一页页的翻,而有了目录只要查询目录即可。为了提高检索的速度,可以为经常进行检索的列添加索引,相当于创建目录。
索引:相当于字典中的目录;加快查询速度;执行增删改的时候降低了速度;
索引分类:聚集索引、非聚集索引、是否为唯一索引。
聚集索引
相当于字典中拼音目录
拼音目录的顺序和数据的顺序是一致的
一个表中只能有一个聚集索引。
索引的排序顺序与表中数据的物理存储位置是一致的,一般新建主键列后回自动生成一个聚集索引
非聚集索引(逻辑上的排序。)
一个表中可以有多个非聚集索引。
相当于字典中笔画目录
笔画目录的顺序和数据是无关的
建索引的目的是为了加快查询速度。索引之所以能加快查询速度是因为索引对数据进行了排序。建索引应该建在某个列上,就是说要对某个列排序。这是,如果用用户执行一条查询语句,where条件中包含了建索引的那列,那么这时,采用用到索引,否则,不会使用索引。Name=数据(用索引),name like ‘%aa%’(不用索引)
创建索引的方式,在表设计器中点击右键,选择“索引/键”→添加→在列中选择索引包含的列。使用索引能提高查询效率,但是索引也是占据空间的,而且添加、更新、删除数据的时候也需要同步更新索引,因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。但是,即使创建了索引,仍然有可能全表扫描,比如like、函数、类型转换等。--删除索引drop index T8.IX_T8_tage
=======非聚集索引=============
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson(SalesQuota, SalesYTD); GO
====创建唯一非聚集索引=============
CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO
=======创建聚集索引=================
CREATE TABLE t1 (a int, b int, c AS a/b); CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c); INSERT INTO t1 VALUES (1, 0);
子查询
把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)。就像使用普通的表一样,被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。说白了就是:就是把一个查询的结果作为另一个查询的数据源。SELECT * FROM (SELECT * FROM student where sAge<30) as t
只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。
select * from student where sClassId = (select cId from class where cName=‘高二二班‘)
子查询返回的值不止一个。当子查询跟随在=、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。子查询基本分类:独立子查询(子查询可以独立运行)、相关子查询(子查询中引用了父查询中的结果)。
常用的判断:In、exists、not in、not exists
一些标示符的用法:
Col1 in (1,2,3) 等价于col1=1 or col1=2 or col1=3
Col1 >= any(1,2,3) 等价于col1 >=1 or col1>=2 or col1>=3
Col1>= all(1,2,3)等价于col1>=1 and col1>=2 and col1>=3
连接两个表的查询:
select * from TblStudent where exists
( --子查询的结果,要依赖于当前父查询中当前行的tsClassid的结果。
select * from TblClass
where tClassName=‘计算机软件班‘ andtClassId=TblStudent.tsClassId)
分页
以前分页可以使用Top语句来进行。SQLServer2005后增加了Row_Number函数简化实现。
Over()就是传说中的”开窗函数”,本身聚合函数只会计算一次,开窗以后就可以为每条记录都计算一次聚合了。Over子句可以为每一行计算表达式而不是只为一行,并且over可以单独定义窗口中的排序方式,而不影响最终结果集。
例如:select *,row_number() over(order by id asc) as hanghao from callrecords order by id desc。
两种分页比较:
select*from(selectrow_number()over(orderbycustomeridasc)asrnumber,*from customers)astblwheretbl.rnumberbetween 15 and 20
select top 5 * from student where sId not in (select top (5*(2-1)) sId from student order by sIddesc)order by sIddesc
sql中的over函数和row_numbert()函数配合使用,可生成行号。over里的order只能查查询里的原始数据进行操作,不会对计算出的新值或新字段起作用。
表连接Join
联接条件可在FROM或WHERE子句中指定,建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件,以进一步筛选联接条件所选的行。
联接可分为以下几类:
1、内联接(典型的联接运算,使用像 = 或<>之类的比较运算符)。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students和courses表中学生标识号相同的所有行。
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
1)LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
2)RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
3、自连接
select ts.tId,ts.tName as 子分类,td.tName as 父分类from Category as ts inner join (select tId,tName from Category) as td on ts.tParentId=td.tId
临时表
局部临时表
create table #tbName(列信息);--表名前缀#
只在当前会话中有效,不能跨连接访问。作用域范围类似C#:如果直接在连接会话中创建的,则当前连接断开后删除,如果是在存储过程中创建的则当前存储过程执行完毕后删除
全局临时表
create table ##tbName(列信息);--表名前缀##
多个会话可共享全局临时表。当创建全局临时表的会话断开,并且没有用户正在访问(事务正在进行中)全局临时表时删除
表变量:declare @varT1 table(col1 int,col2 char(2));//存储更小量的数据,比临时表有更多的限制。
注意:临时数据都存储在tempdb,当服务重新启动的时候,会重建tempdb。
临时表的应用:
在对大数据量的表做复杂子查询的时候为了提高执行效率,降低内存消耗可以使用临时表。
Ps:数据库中的临时表有很多。例如上面nsert into class(cName,cDescription) output inserted.classId values(‘高三一班’,‘描述’),就是利用的Inserted临时表。
数据表中的数据中的数据过百万,如何优化查询?
大数据时可以考虑使用临时表,临时表在内存中的,速度比较快。可以使用临时表。还可以分化在多个表中,以便于数据的优化。
视图概述
视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上。视图在操作上和数据表没有什么区别,但两者的差异是其本质是不同:数据表是实际存储记录的地方,然而视图并不保存任何记录。相同的数据表,根据不同用户的不同需求,可以创建不同的视图(不同的查询语句)。视图的目的是方便查询,所以一般情况下不能对视图进行增删改。
优点:
筛选表中的行\降低数据库的复杂程度
防止未经许可的用户访问敏感数据
普通视图:并不存储数据(虚拟表),访问的是真实表中的数据。
使用视图注意事项:
1.视图中的查询不能使用order by ,除非指定了top语句。
视图被认为是一个虚拟表,表是一个集合,是不能有顺序的。而order by 则返回的是一个有顺序的,是一个游标。但可以在视图中使用select top percent + order by 。所有查询的列,必须有列名,且列名必须唯一。create view vw_name as 后不能跟begin end。
例如:create view vw_tab as select top 100 percent * from tab order by col1 desc
以上语句执行完毕以后查询select * from vw_tab也不会排序,与select * from vw_tab order by col1 desc不一样。top已经取得了上次order by 的结果前几个结果,并且top输出的结果没有再次排序,所以无法保证输出的结果是desc排序后的结果。视图中不允许使用order by,尽量不要用。
索引视图:在视图上创建唯一聚集索引。数据会保存在数据库中而不是引用表中的数据。
例如:
createview vw_ix_T1WITHSCHEMABINDING As selectautoid,uname,usrIdfrom dbo.T1 createuniqueclusteredindex ix_vw_t1 on vw_ix_T1(autoId)
局部变量
(1).先声明再赋值:
声明局部变量
DECLARE @变量名数据类型。例如:DECLARE @name varchar(20)
PS:声明变量时,可以直接赋值。此时赋的值称为做默认值。
赋值
SET @变量名 =值 --set用于普通的赋值
SELECT @变量名 = 值 --用于从表中查询数据并赋值
输出变量的值
SELECT 以表格的方式输出,可以同时输出多个变量。
PRINT 以文本的方式输出,一次只能输出一个变量的值。
变量的种类
局部变量:
局部变量必须以标记@作为前缀,如@Age int。
局部变量:先声明,再赋值。
全局变量(系统变量):
全局变量必须以标记@@作为前缀,如@@version。
全局变量由系统定义和维护,我们只能读取,不能修改全局变量的值。
强调:
1.局部变量标志:一个@;
2.全局变量标志:两个@。
3.我们可以定义局部变量,但不能定义全局系统变量,但我们能读取全局系统变量的值。
全局变量
@@ERROR 最后一个T-SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息
@@error变量,在每次执行完SQL语句后,都会为@@error变量赋值,如果上次执行的SQL语句有错,则将@@errro赋值为一个不为0的值,否则(执行没错),则将@@error赋值为0。
IF ELSE
IF(条件表达式) BEGIN --相当于C#里的{ 语句1 …… END --相当于C#里的}
ELSE BEGIN语句1 …… END
While循环
WHILE(条件表达式) BEGIN--相当于C#里的{ 语句 …… continue BREAK END --相当于C#里的}
事务
事务:同生共死
指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成,必须作为一个整体执行。这些sql语句作为一个整体一起向系统提交,要么都执行、要么都不执行。
语法步骤:
开始事务:BEGIN TRANSACTION
事务提交:COMMIT TRANSACTION
事务回滚:ROLLBACK TRANSACTION
判断某条语句执行是否出错:全局变量@@ERROR;@@ERROR只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计:SET @errorSum=@errorSum+@@error
数据库中使用事务的例子:
begin transaction
declare @error int
set @error = 0
update bank set balance=balance-1000 where cid=‘0001‘
set @error = @error + @@error
update bank set balance=balance + 1000 where cid=‘0002‘
set @error = @error + @@error
if @error != 0
rollback transaction
else
commit transaction
go
存储过程
存储过程---就像数据库中运行方法(函数),和C#里的方法一样,由存储过程名/存储过程参数组成/可以有返回结果。前面学的if else/while/变量/insert/select 等,都可以在存储过程中使用。
优点:
执行速度更快–在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计–类似方法的复用
提高系统安全性–防止SQL注入
减少网络流通量–只要传输存储过程的名称
系统存储过程
由系统定义,存放在master数据库中,名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。可以参考:
系统存储过程
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录帐户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
创建存储过程(自定义存储过程)
定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
参数说明:参数可选;参数分为输入参数、输出参数;输入参数允许有默认值
EXEC 过程名 [参数]
存储过程中使用输出参数
输出参数关键字:OUTPUT。存储过程的参数可以是默认值(在创建时赋值)。传参时也可以颠倒参数的传入顺序,但需要指定变量的名称。
执行存储过程
用ExecuteNonQuery可以;用ExecuteSalar还可以;用ExecuteReader照样可以(记得用IsDBNull判断空值)。
数据库中的默认事务
默认情况下执行的sql语句都是隐式事务。可用set Implicit_Transactions on|off 打开或关闭隐式事务
参数的作用范围
参数一般用在where语句后面,或者赋值时,不能把表名、列名等用参数来代替。
触发器
触发器的作用:自动化操作,减少了手动操作以及出错的几率。
触发器是一种特殊类型的存储过程,它不同于前面介绍过的一般的存储过程。【在SQL内部把触发器看做是存储过程但是不能传递参数】
一般的存储过程通过存储过程名称被直接调用,而触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具,在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查,还可以完成难以用普通约束实现的复杂功能。
那究竟何为触发器?
在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程。
常见的触发器
DML触发器:Insert、delete、update(不支持select);after触发器(for)、instead of触发器(不支持before触发器);
DDL触发器:Create table、create database、alter、drop…。
触发器触发时会存在inserted和deleted两个临时表,但执行完毕这些会被立即删除。
inserted表与deleted表
inserted表与deleted表是干什么的?
inserted表包含新数据:insert、update触发器会用到;deleted表包含旧数据:delete、update触发器会用到。
deleted表
deleted表存放由于执行delete或update语句而要从表中删除的所有行。在执行delete或update操作时,被删除的行从激活触发器的表中被移动(move)到deleted 表,这两个表不会有共同的行。
inserted表
inserted表存放由于执行insert或update语句而要向表中插入的所有行。在执行insert或update事物时,新的行同时添加到激活触发器的表中和inserted表中, inserted表的内容是激活触发器的表中新行的拷贝。
说明:update事务可以看作是先执行一个delete操作,再执行一个insert操作,旧的行首先被移动到deleted表,让后新行同时添加到激活触发器的表中和inserted表中。
不能对视图定义 AFTER 触发器。
After触发器:
在语句执行完毕之后触发
按语句触发,而不是所影响的行数,无论所影响为多少行,只触发一次。
只能建立在常规表上,不能建立在视图和临时表上。(*)
可以递归触发,最高可达32级。
instead of触发器
用来替换原本的操作
不会递归触发
可以在约束被检查之前触发
可以建在表和视图上(*)
语法:
CREATE TRIGGER triggerName ON 表名
after(for)(for与after都表示after触发器) | instead of
UPDATE|INSERT|DELETE(insert,update,delete)
AS
begin
…
end
触发器例子:
插入
CREATE TRIGGER tr_updateStudent ON score
for INSERT
AS
Begin
declare @sidint,@scoreidint
select @sid = studentId,@ scoreid=sid from inserted
if exists(select * from student where sid=@sid)
print ‘插入成功’
else
delete from score where sid = @scoreId
End
Insert into score (studentId,english) values(100,100)
删除:
CREATE TRIGGER tr_deleteStudent ON student
for delete
AS
begin
insert into backupStudent select * from deleted
End
Delete from student where sId=1
当然触发器也可以变成多种操作(for 后面用逗号隔开即可)。sql Server中的触发器是表级触发器,无论删除多少行或者插入多少行,只触发一次。
触发器使用建议:
尽量避免在触发器中执行耗时操作,因为触发器会与SQL语句认为在同一个事务中。(事务不结束,就无法释放锁。)
避免在触发器中做复杂操作,影响触发器性能的因素比较多(如:产品版本、所使用架构等等),要想编写高效的触发器考虑因素比较多(编写触发器容易,编写复杂的高性能触发器难!)。触发器编写时注意对多行触发时的处理。(一般不建议使用游标,性能问题!)。
游标
SQL语句是把结果集作为一个整体(整个作为一个单元)来操作的,但有时候我们需要针对结果集中的每条记录(或某部分记录)进行特殊操作,这时就需要用到游标。
Update更新语句将某列更新为指定的值,但是如果每行的该列要更新的值都不一样呢?可以用游标和sql语句。当然尽量用sql语句。
游标是过程化思维,而SQL本身是集合化思维。 比如:把所有人(或某些人)的年龄都加1岁。(是对一个集合做的整体的操作“都加1岁”,基于集合的思维,集合中每个元素的操作都一样)针对表中的每一行数据内容调用某个存储过程,或者为每个用户的手机发一条短信。(这个是针对表中每条记录都要做的事情,类似于集合遍历,并且有可能对每条数据执行的操作都不一样)。
多数情况下使用查询的性能要比使用游标高效,但并不是任何情况下使用查询性能都高于使用游标。
游标为什么性能低下?
1.读取次数本身变多了;
2.没有应用比较好的查询优化,数据库会多sql语句执行多种优化,并选择最优的一种,而对于游标则没有更多的选择;
定义游标基本语法
1.delcare 游标名 cursor [local | global][游标选项] for 查询语句
2.open 游标名
3.fetch [next] from 游标名 into @v1,@v2…
4.判断@@fetch_status状态,继续fetch
5.close 游标名–关闭游标
6.deallocate 游标名–删除游标中数据库中保存的数据结构(彻底释放资源)
游标的例子:
将每个老师的工资更新为原来的工资+奖金
定义两个变量,用来存储id于奖金
declare @tid int
declare @reward money
1.建立一个基于奖金的游标:
declare cur_reward cursor fast_forward for select ttid,reward
2.打开游标
open cur_reward
--通过游标读取数据 into给声明的变量赋值
fetch next from cur_reward into @tid,@reward
while @@fetch_status=0
begin
--更新工资
update TblTeacher set ttsalary=ttsalary+@reward where ttid=@tid
fetch next from cur_reward into @tid,@reward
end
--3关闭游标
close cur_reward
--4释放资源
deallocate cur_reward
Sql语句更新:
--将每个人的工资在原来的基础上减去根据奖金表中的金额(update更新一张表中的数据,而连接的是两张表)
update TblTeacher set ttsalary=ttsalary-(select reward from TbTeacherSalary where TblTeacherSalary.ttid=TblTeacher.ttid)
sql语句的另外一种写法
update TblTeacher set tTSalary=tTSalary+b.reward from TblTeachersalary b where TblTeacher.ttid=b.ttid(注意这里的有from和它的更新语句)。
动态sql语句
在sqlServer动态sql语句避免攻击问题:用动态sql非常灵活,不可避免的存在注入攻击,只能减小危害,不能避免。由于在SqlServer端,所以处理的方法也多种多样,常见的是:
替换关键字;设置权限,只能查询;限制字符长度;
危害语句如:select * from sysobjects;exec sp_databases;
可以将变量带进动态sql语句,只是需要将变量的类型转换成字符串进行拼接。
动态sql中的输出变量问题:
只看最后一句:exec sp_executesql @sql,N‘@Cnt int output‘,@HasInsert output;可参考:和
可以借用临时表:create table #rdCount (rsCount int)
set @sql=‘declare @count int;selcet @count=count(*) from TblStudent ;insert into #rdCount values(@count);‘
exec(@sql)
select * from #rdCount
set statistics time on –SQL语句执行时间。
Sql Server总结,布布扣,bubuko.com