SQL server高级应用 收藏版

**什么是触发器:
触发器是在对表实行插队,更新或删除操作时自动执行的仓库储存进度。
触发器的品种
INSERT触发器:当向表中插入数据时接触,自动推行触发器所定义的SQL语句。
UPDATE触发器:当更新表中某列、多列时触发,自动试行触发器所定义的SQL语句。
DELETE触发器:当删除表中记录时接触,自动推行触发器所定义的SQL语句。
Deleted表:用于存储DELETE和UPDATE语句所影响的行的别本,即在DELETED表中权且保存了被删去或被更新前的记录行。在实施DELETE或UPDATE语句
,行从触发器表中删除,并传导到DELETED表中。因而大家得以从DELETED表中检查删除的数据行是不是能去除。借使无法,就足以回滚裁撤此操作,因为触发器自个儿正是四个出奇的事体单元。
Inserted表:用于存款和储蓄INSERT和UPDATE语句所影响的行的别本,即在inserted表中一时半刻保存了被插入或被更新后的记录行。在举行INSERT或UPDATE语句时,新加行被同时加上到INSERT表和触发器表中。因此我们得以从INSERTED检查插入数据是还是不是知足专门的工作须求。要是不满足,就足以向用户告知错误音信,并回滚裁撤操作。

Declare @errorsum int
Set @errorsum=0
Update ……………………….
Set @errorsum=@errorsum+@@error
Update………………………..
Set @errorsum=@errorsum+@@error
If @errorsum<>0
Begin
Rollback transaction–回滚事务
End
Else
Begin
Commit transaction–提交事务
End
Go

3.高端查询
3.1子查询.
Select … from 表1 where 字段1>(子查询)
3.2IN和NOT IN子查询
Select … from 表1 where 字段一 NOT IN (子查询)
Select … from 表2 where 字段二 IN (子查询)
3.3Exists和Not Exists子查询
If exists(子查询)
语句
If not exists(子查询)
语句

Create trigger trigger_name
On tablae_name
[with encryption]
For(insert,update,delete)
As
SQL 语句
Go

变量

含义

@@error

最后一个T-SQL错误的错误号

@@identity

最后一次插入的标识值

@@language

当前使用的语言名称

@@max_connections

可以创建同时连接的最大数目

@@rowcount

受上一个SQL语句影响的行数

@@servername

本地服务器的名称

@@servicename

该计算机上的SQL服务的名称

@@timeticks

当前计算机上每刻度的微秒数

@@transcount

当前连接打开的事务数

@@version

SQL server的版本信息

概念触发器

Create proc procedureName
@number int output,
@name char(20)
As
SQL 语句
Set @number=1000
Go
调用语法
Declare @dd int
Exec procedureName @dd output,’lyh’

复制代码 代码如下:

1.2建表.

调用语法

Alter table stuInfo add constraint PK_stuNo primary key(stuNo)—主键
alter table stuMarks add constraint FK_stuNo foreign key(stuNo)
references stuInfo(stuNo)—外键
alter table stuInfo add constraint UQ_stuID unique(stuID)—唯1约束
alter table stuInfo add constraint df_stuAddress default(‘地址不详’)
for stuAddres—默许约束
alter table stuMarks add constraint ck_stuAge check(stuAge between 15and 40)—检查约束
alter table stuMarks drop constraint ck_stuAge—删除约束

Create proc procedureName
@number int =默认值,
@n varchar(20)
As
SQL 语句
Go
调用语法:
Exec procedureName 200,’lyh’
Exec procedureName @n=’lyh’

复制代码 代码如下:

4.1事务
² 起首业务:begin transaction
² 提交业务:commit transaction
² 回滚事务:rollback transaction
作业分类
² 显式事务:用begin transaction 分明钦命职业的起头。
² 隐性事务:通过安装set implicit_transactions
on语句,将陷性事务格局设置为开发。当以隐性事务操作时,SQL
server就要交付或回滚事务后扑克动运维新专门的职业。十分小概描述事务的始发,只需提交或回滚每一个事务.
² 自动提交业务:这是SQL
server的暗中同意方式,它将每条单独的T-SQL语句视为3个作业。假若成功施行,则自动提交。若是不当,则自动回滚。
示例:
Begin transaction–伊始作业

Exec procedureName

一. 建库,建表,加约束.
1.1建库

选用视图
Select * from myview
伍.存款和储蓄进度
5.1系统存款和储蓄进度
常用系统存款和储蓄进程
Sp_datadases 列出服务器上的全部数据库
Sp_helpdb 报告关于钦点数据库或持有数据库的新闻
Sp_renamedb 改换数据库的名目
Sp_tables 再次回到当前条件下可查询的对象的列表
Sp_columns 重临有些表列的音信
Sp_help 查看有个别表的全体消息
Sq_helpconstraint 查看某些表的封锁
Sq_helpindex 查看有些表的目录
Sq_stored_procedures 列出脚下情况中的全数存款和储蓄进度
Sp_password 增多或改变登六帐户的密码
Sp_helptext
展现默许值、未加密的贮存进程、用户定义的积累进程、触发器或视图的实际上文本
伍.二自定义存款和储蓄进程
Ø 不带参存款和储蓄进度
Ø 带输入参数的储存进度
Ø 带输出参数的仓库储存进度

复制代码 代码如下:

If exists (select * from sysobjects where name=’Mytable’)
drop table Mytable
go
create table Mytable
(
ID int not null identity(1,一) primary
key,–标志种子一,标识增量一,设该列为主键
name nchar(十) not null,–不可为空
degree numeric(18,0)—身份证,numeric(1八,0)代表111位数字,小数位数为0
)
Go

复制代码 代码如下:

If exists (select name from sysindexes where name=’myindex’)
Drop index 表名.myindex
Create nonclustered index myindex
on
student(id) with fillfactor=30
go

二.二输出语句.
Print局部变量或字符串,以字符串情势打字与印刷数据 。
Select 局地变量AS自定义列名,以表格方式打字与印刷数据。
二.3逻辑调控语句.
2.3.1.If-else
If(表达式)
Begin
语句1
语句2
End
Else
Begin
语句1
语句2
End
2.3.2.case
Case
When 条件一 then 结果二
When 条件二 then 结果二
End

exec sp_grantlogin ‘jbtraining/s26301’
–windows用户为jbtraining/s26301,jbtraining 表示域
exec sp_addlogin ‘admin’,’053313壹’–SQL登入帐户,帐户:
‘admin’,密码:0533131.

If exists (select * from sysobjects where name-‘myview’)
Drop view myview
Go
Create view myview
As
Select 姓名=stuName,学员=sutInfo from stuInfo left join stuMarks
on stuInfo.stuNO=stuMarks.stuNo
Go

4.事务,索引和视图.

exec sp_grantdbaccess
‘jbtraining/s26301′,’s26301dbuser’–s2630壹dbuser为数据库用户名
exec sp_grantdbaccess ‘admin’, ‘s2630壹dbuser’–s26301dbuser为数据库名

复制代码 代码如下:

Trigger_name:是触发器的名称。触发器名称必须符合标志符规则,并且在数据库中务必唯一,。能够选用是不是钦命触发器全数者名称。
Table_name:是在其上执行触发器的表或视图
With encryption:加密syscomments表中包含create
trigger语句文本的条规。使用with
encryption可幸免将触发器作为SQLserver复制的一对发表.
Create trigger:必须是批管理中的第2条语句,并且不得不接纳到3个表中。
触发器只可以在时下的数据库中创设,但是触发器可以引用当前数据库的表面临象。

复制代码 代码如下:

复制代码 代码如下:

5.二.2带输入参数的积攒进度

Create proc procedureName
As
SQL 语句
Go

复制代码 代码如下:

复制代码 代码如下:

4.2索引
目录是SQL server编排数据的里边方法。它为SQL
server提供1种情势来编排查询数据的路由
索引页:
数据库中的存款和储蓄索引的数据页。索引页存放检索数据行的入眼字页以及该数据行的地方指针。索引页类似于国文字典中按拼音或笔画排序的目录页。
唯一索引:
唯一索引不相同意两行兼备同样的目录值.
主键索引:
在数据库关系图中为表定义四个主键将电动成立主键索引,主键索引是唯一索引的例外类型。主键索引必要主键中的每个值是唯1的。当在查询中动用主键索引时,它还同意快速访问数据。
集中索引:
在聚焦索引中,表中各行的情理顺序与键值的逻辑(索引)顺序同样。表只可以包涵3个集中索引。
创办索引

一.四帐户管理.
一.4.一创制登入帐户.

5.2.三带输出参数的积攒过程

1.3加约束.

**六.SQL server触发器

伍.二.一不带参数的仓库储存进度

use master
go
if exists (select * from sysdatabases where
name=’MyDatabase’)—判别master数据库sysdatagbases表中是不是留存将在创造的数量库名
drop database MyDatabase—假设sysdatabases表中设有该数据库名,则将它删除
go
exec xp_cmdshell ‘md
D:/MyDatabases’—利用存款和储蓄进程成立3个文书夹用于存款和储蓄数据物理文件(数据文件,日志文件),DOS命令(mkdir=md)
go
create database MyDatabase—成立数据库
on
(
name=’MyDatabase_data’,–钦命逻辑文件名
filename=’D:/MyDatabases/MyDatabase_data.mdf’,–钦命物理文件名
size=5mb,–开头大小
maxsize=50mb,–钦命物理文件最大容积,可选
filegrowth=20%–增长量
)
log on
(
name=’MyDatabase_log’,–钦定逻辑日志文件名
filename=’D:/MyDatabases/MyDatabase_log.ldf’,–钦点日志物理文件名
size=5mb,–开始大小
maxsize=50mb,–内定日志物理文件最大容积,可选
filegrowth=20%–增长量
)
go
use MyDatabase
go

1.四.二创立数据库用户.

1.4.三向数据库用户授权.

myindex为索引名,with fillfactor=30,钦定填充因子为30%
应用索引
Select * from stumarks (myindex) where writtenExam between 60 and 90
Stumarks为表名,myindex为索引名,writtenexam为列名,between 60 and 90
钦定询问出writtenexam字段60至90里面包车型地铁值
确立目录的尺度
Ø 该列用于频仍寻觅
Ø 该列用于对数据开始展览排序
明确命令禁止利用索引的情形
Ø 列中仅包罗多少个不等的值
Ø 表中多少仅包括几行,为小型表创建索引大概不太划算,因为SQL
server在索引中追寻数据所花的时日比在表中逐行寻觅所花的日子更加长
4.3视图
视图的用处
Ø 筛选表中的行
Ø 幸免未经许可的用户访问敏感数据
Ø 下跌数据库的复杂程度
Ø 将多少个概略数据表抽象为三个逻辑数据表
利用视图的好处
ü 对最终用户的裨益
l
结果更便于掌握。创立视图时,能够将列名改为有意义的称谓,使用户更便于驾驭列所表示的内容。在视图中期维修改列名不会潜移默化基表的列名
l
获得数据更便于。多数个人对SQL不太精晓,因而对他们的话成立对七个表的繁杂查询很劳顿。因此能够透过创造视图来便于用户访问多个表中的多少。
ü 对开拓职员的功利
l
限制数据检索更易于。开采职员不常必要隐藏某个行或列中的音信。通过运用视图,用户能够灵活地拜会他们须求的数量,同时确认保证同一个表或任何表的别的数据库的安全性。要促成这一目的,能够在开立视图时就要对用户保密码的列排除在外。
l
维护应用程序更便宜。调节和测试视图比调节和测试查询更易于。追踪视图中经过的种种步骤中的错误越来越轻易,那是因为兼具的步骤都以视图的组成都部队分。
始建视图

复制代码 代码如下:

/*为s2630一dbuser分配对表mytable的select,insert,updata,delete权限*/
grant select,insert,update,delete on mytable to s26301dbuser
/*为s2630一dbuser用户分配创造表的权位
grant create table to s26301dbuser

二.T-SQL编程
2.1变量.
有些变量的称谓必须以标识@作为前缀:
Declare @name varchar(捌)–证明变量。
Declare @name varchar(8)=value–初始值。
Set @name=value– 赋值。
Select @name=value–赋值。
全局变量
SQL server中的全数全局变量都采用五个@标识作为前缀:

复制代码 代码如下:

复制代码 代码如下:

相关文章