数据库语句

SQL Server 2000 应用于开发 范例宝典 --明日科技编著 笔记

1、数据库空间的增加与收缩

增加可以再属性中设置，收缩百分比，如0%为收缩后把没有用到的空间释放掉。按百分比增长为为加入数据之后按当前内存的多少来增加数据库的大小。（貌似是这样的）

2、标识列

（1）、列的数据类型为不带小数的数值类型
（2）、在进行插入(Insert)操作时，该列的值是由系统按一定规律生成,不允许空值
（3）、列值不重复，具有标识表中每一行的作用,每个表只能有一个标识列。
由于以上特点，使得标识列在数据库的设计中得到广泛的使用。
2.1 标识列的组成
创建一个标识列，通常要指定三个内容:
（1）、类型（type）
在SQL Server 2000中，标识列类型必须是数值类型，如下：
decimal、int、numeric、smallint、bigint 、tinyint
其中要注意的是，当选择decimal和numeric时，小数位数必须为零
另外还要注意每种数据类型所有表示的数值范围
（2）、种子(seed)
是指派给表中第一行的值,默认为1
(3)、递增量(increment)
相邻两个标识值之间的增量，默认为1。

总结：标识列 一般作为序号列 自动实现递增。

3、对列进行操作

ALTER table 密码错误 add  个数 int NULL

ALTER table 密码错误 drop column 时间 
ALTER table 密码错误 alter column 时间 int

添加自增列，可以设置初始值，以及自增量

ALTER table 密码错误 add nob int identity(1,1) not null

4、设置公式

如设置总金额列=金额*个数，则在设计表界面中，选中总金额->公式   金额*个数自动计算 非常好

手机号   金额   个数 总金额

15013280595    230   2460

5、小数位数

MySQL允许使用非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

6、获取当前时间

select getdate()

7、类型转换

CAST(ListPrice AS int)

CONVERT(int, ListPrice)

都是将ListPrice 这一列转化为int 类型

8、索引

8.1 如果要经常查询一列，而且这一列是唯一的话可以建立唯一索引，同样可以对多个列建立唯一索引。 这样可以提高查询的效率。 但是其缺点是增加需要磁盘空间，降低添加、删除、以及更新行的速度。另外还有（聚集索引？？）

建立索引前：

select 手机号 from 原始账号

结果是：

CPU 时间 = 62 毫秒，耗费时间 = 355 毫秒。

建立索引后：

CPU 时间 = 47 毫秒，耗费时间 = 333 毫秒。

8.2 主键索引

数据库表中通常有一列或列的组合，其值用来唯一标识表中的每一行，该列或列的组合称为表的主键，在视图操作中定义主键将自动设置为主键索引。能加快查询速度。

9、查看语句执行时间以及cpu运行情况，可以看语句的执行效率如何

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 

select * from 原始账号
SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

显示结果如下：

SQL Server 执行时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
（所影响的行数为 292481 行）
表 '原始账号'。扫描计数 1，逻辑读 3362 次，物理读 0 次，预读 0 次。
SQL Server 执行时间: 
   CPU 时间 = 156 毫秒，耗费时间 = 1463 毫秒。

10、关系的建立与维护

关系的建立可以是一对一，也就是表A和表B的主键记录是唯一的，一一对应。也可以是一对多，表A中的一行，在B中有多行记录，但是B的都在A中。

其作用是：

防止在从表中添加主表中没有的ID等，必须主表先加，从表才能加，从表删除，主表才能删除。这样做到了一个关系。可以保护关心的数据，放在两外一个表中。

11、check 约束

这个可以通过check函数来实现，比如说唯一等。

12、sql的注释方式有两种：

--  和/* */

13、数据库操作

drop database name--删除数据库

sp_detach_db @dbname='ZK'--分离数据库
sp_attach_db @dbname=ZK,@filename1='F:\\ZKSQL_data.MDF',--附加数据库
			@filename2='...._Log.Ldf'

其中，分离操作的作用是能够将数据库从服务器移除，不能再使用，但是其数据还存在本地磁盘中，通过附加数据库可以再次使用，然而drop是无法再使用。

同样可以附加单文件数据库，其日志文件将自动添加。

备份与还原，这里就还是用管理器来操作好了。

14、表的一些操作

create table test(
ID int not null,
name varchar(50) null	
)
drop table test --删除表
alter table 密码错误 add  个数 int NULL --添加列，只有这个不要加column

alter table 密码错误 drop column 时间 删除列
alter table 密码错误 alter column 时间 int 更改列

exec sp_rename 'test','testnew'--更改表名
exec sp_rename 'testnew.name','名称'--更改列名

15、select 语句

避免使用not 会让查询变慢

--使用别名

select 密码错误.金额 as 金额统计 from 密码错误
select (金额+1) as 金额 from 密码错误 --在查询结果中添加列，进行多列数据的计算
select sum(金额) as  金额 from 密码错误-- 利用聚合函数 ，同样需要重命名，且需要用group by 进行分组

--模糊匹配  非常强大，正是我需要的，跟LINUX shell 脚本非常像
--select * from 密码错误 where LEFT(手机号,3) like '158' --158开头的手机号 非常好用，这里用的是字符函数，同样可以用数字函数，日期时间函数
--select * from 密码错误 where LEFT(手机号,3) like '15_'  --前3为以15开头的，这里_是匹配一个字符
--select * from 密码错误 where 手机号 like '158%' --%匹配任意多个字符，可以用两个%进行查询，但是建议不要用两个
--select * from 密码错误 where  LEFT(手机号,3) like '1[4-5]_'--范围匹配
--select * from 密码错误 where  LEFT(手机号,3) like '1[^4-5]_'
--and(...or ...)--这种条件值得注意

格式化字段和结果集利用这个可以解决在号码中加-的问题，不用每次都去excel中取做，但是由于sql2000不支持mid ，且号码中含有空格，所以这里用

select LEFT(手机号,3)+ '_'+RIGHT( LEFT(手机号,7),4)+'_'+ RIGHT( LEFT(手机号,11),4) as 手机号 from 密码错误

实现了功能，效果如下：

134_1814_7589
134_1814_9634

16、字符串函数

LTRIM  以及 RTRIM  

ROUND(748.58，-1)   当后面数字为负数为 判断小数点前的位数  为0位判断小数点后一位  为正数为判断后位数

CAST(..  AS  int )

select cast(round(sale/price,0) as int) as 价格

in  not in

17  一些select 语句

use HB
select * from 总中奖
All 为大于所有的，是里面的最大值,any 为最小的，这里不再赘述
select * from 总中奖 where 金额 > all(select 金额 from 总中奖 where 手机号 in('15013376654','13433921643'))
select * from 总中奖 where not (个数 between 2 and 4) --选取个数小于2的
--distinct
--distinct 为不选取重复行，若在sum avg max 等聚合函数中用distinct 函数 那么则先去掉重复再做计算
select distinct * from 密码错误
--这里如果没有对服务密码进行聚合的话 那么group 必须用到该列

select 手机号 ,服务密码,count(手机号) as 手机号个数 from 密码错误 group by 手机号,服务密码 having count(手机号)=1 order by 手机号--多个语句 非常棒
--count(*)为统计每个分组统计的个数

select * from 密码错误 where 手机号 is not null
select top 5 percent * from 密码错误 order by 手机号 desc --这里用到了 percent,默认是升序的
select * from 密码错误 where 手机号 between '13418144500' and '13418147590'-- 字符串的区间判断方式
select * from 密码错误 where month(日期)=8 --这是month 函数的用法

select 所属部门,性别,avg(工资) as 平均工资 from 工资表 group by 所属部门, 性别 with cube--这个能够按部门求出平均工资之后，还能按照部门里面的性别，加出两个分组
select 所属部门,性别，avg(工资) as 平均工资 from 工资表 group by 所属部门,性别 with rollup--好像效果和上面的差不多
select * from 密码错误 compute sum(金额)--这里还是比较方便的
select 金额,count(金额)as 个数 ,sum(金额) as 总金额  from 总中奖 group by 金额 order by 总金额 desc--利用这一句来看所有的分部情况，非常方便
select 编号,销价 as  销价最少 ,利润 as 利润最少 from table1 where 销价 in(select min(销价) from table1)or 利润 in( select min(利润) from table1)
select 手机号, 金额 from 密码错误 where 金额 in （select min(金额) from 密码错误)-- 不能用 where 金额=min(金额)
--一些数学函数
--rand, sin,cos,tan, abs ,sqrt,log,exp,floor为返回不大于所给数的最大整数，ceiling为不小于所给数的最小整数
--日期函数
select dateadd(day,1,getdate()) as tomorrow --返回第二天
select dateadd(year,1,getdate()) as nextyear--返回第二年
select datediff(day,'03/14/2014',getdate()) as daycount--相差天数，其它类似
select day(getdate()) as daynow --返回这个月的第几天，month，year 类似
select datepart(day,getdate()) as daynow--效果和上面的是一样的
select 手机号, +char(64)+ cast( 金额 as varchar(20) ) as 金额 from 密码错误 --输出15013280595         	@230
--下面写一段书上的例子

declare @pos int,@s varchar(50)
set @pos=1
set @s='i want it'
while @pos<datalength(@s)
begin
	select substring(@s,@pos,1)
	set @pos=@pos+1
end

--上面的例子告诉了我  可以用substring 来取代left right

replicate 函数，为重复多少遍 如 replicate (手机号,1)为重复一遍
upper()转化为大写
space(1)一个空格
substring
left(@s,3)等



--多表查询和使用表的别名这个已经会了，就不再赘述
--union

--select 手机号 ,金额 from 总中奖 union  select 手机号,金额 from 密码错误 order by 金额 desc
--select 金额 ,sum(金额) as 总金额,count(金额) as 个数 from 总中奖 group by 金额 union select 金额 ,sum(金额) as 总金额 ,count(金额) as 个数 from 密码错误 group by 金额 order by 金额 desc

--注意，可以合并两个类似的表，但是单一的select 字句不能有order 或者 compute 字句，只能在最后加上
--另外union 会自动删除重复的行，但是如果加上 union 会保留重复的行
--使用多个嵌套union 这里会按照union 的嵌套顺序以及是否使用all 来进行去掉重复操作

--in,exists
--这个也已经用过了,这里比较其区别
--select * from 原始账号 where 手机号 in(select 手机号 from 密码错误)
--select * from 原始账号 where 手机号 =any(select 手机号 from 密码错误)
--select * from 原始账号 where exists(select * from 密码错误 where 手机号=原始账号.手机号)

--上面三条语句的功能是一样的，但是exists只需要判断后面查询有结果就可以了，而=any,some 和in 的作用是一样的
--同样可以用notin 等
--aome,any, all
--all 必须是所有的，some 和any 是一样的


--在select 子句中的子查询，这个就是不断的嵌套用as  from 等来做，或者聚集函数 特别要注意 这个as 是可以去掉的

--delete ... update ...set ..=..


--内连接查询
--这个是可以用a.  b.什么来实现的，
select a.手机号,a.个数,b.金额 from  总中奖 as a inner join  密码错误 as b on a.手机号=b.手机号
select a.手机号,a.个数,b.金额 from 总中奖 as a ,密码错误 as b where a.手机号=b.手机号
select * from ...显示两个表的所有列
--上面两句的效果是一样的

--自身连接
--自身连接其实也是有作用的，两个结果都来自同一个表
select a.手机号,a.金额 ,b.金额, (a.金额-b.金额) as 差额 from 密码错误 a inner join 密码错误 b on a.金额>b.金额 order by 差额 desc

--外连接
--内连接          只连接匹配的行
--左外连接          包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
--右外连接          包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
--全外连接          包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
--(H)(theta)连接          使用等值以外的条件来匹配左、右两个表中的行
--交叉连接          生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
--上面是看到的关于这几种连接的区别
--同时可以利用内连接、外链接进行多个一起使用，三表一起进行组合
select a.员工编号,b.基本工资,c.请假天数 from (a left inner join b on a.员工编号=b.员工编号) left inner join c on b.员工姓名=c.员工姓名 
--感觉还是很好用的样子

--case ,查询数据

select *,状态= case 
		when 金额<50 then '太少'
		when 金额>=50 then '满足要求'
		end
from 密码错误

--case ,更新列

update 密码错误 set 状态 = case 
		when 金额<50 then 0
		when 金额>=50 then 1
		end


alter table 密码错误  drop column  状态  
select * from 密码错误

--交叉查询
--静态交叉表
--静态交叉表可以通过select 语句来实现，加上case 等就可以了
--select 员工姓名, sum( case 所在部门 when '食品部' then 销售业绩 else null end) as 食品部业绩,sum(...) from 销售 group by 员工姓名
--上面这个例子，可以以员工为基础，若在一个部门下有业绩，则写出，否则为null

--动态交叉
--动态交叉表就是列表可以根据表中数据的情况动态创建列，不能用select 来实现，但是可以用存储过程来实现

--以后遇到再深入吧

以上这些语句，解决了所有sql 中的查询问题，也终于明白了内连接与外链接 以及union的用法，知识对于交叉连接的动态交叉连接，以后有时间再看吧

18、一些更新操作

use HB
select * from 密码错误

insert into 密码错误 (手机号,服务密码,登录密码,支付密码,金额) values(15,33,33,33,1)

insert 密码错误 select '1','2','3','4',1
union all select '1','2','3','4',2

--这一段和上面的区别是适合于批量插入，这个技能太强大了
update 密码错误 set 手机号='16' where 手机号='15'
delete from 密码错误 where 手机号='16'

--go 的用法
--当语句过长时，或者当一些语句作为批处理，那么就用go来分开，是批处理的标志

go
begin tran Update_data
	update 密码错误 set 手机号='2' where 手机号='1'
commit tran Update_data

19、流程控制语句

use HB

if (select count(*) from 密码错误 where 金额>30)>0
begin
	print 'ok'
end
else
begin
	print 'fail'
end


--检测语句
--可以将if 后面的改为
if exists (select * from 密码错误 where 金额>30)
--感觉好用多了

while @a<10
begin
continue
end


--case

case 金额
	when 1 then 0
end


go
create procedure person 
as
if exists (select * from 密码错误 where 金额>30)
begin
	return 1
end 
go
declare @r int

exec @r=person

print @r


--延期执行语句

begin
	waitfor time '21:00'
	select * from 密码错误 
end

--这里的话 又不是作业，不是事务之类的，怎么会延时，又怎么取消呢


--跳转语句
--通过设置标签，Label2:
--然后用 goto Label2来跳转

--功能性语句
print



--错误处理语句
if @@error<>0  --这一句用起来要谨慎，有的时候虽然可以由结果，但是还是会有这样的报错

20、索引

http://www.cnblogs.com/ericwen/archive/2008/09/04/1283998.html

以上是全文索引的链接，没事的时候可以看一下 ，现在还不太懂

21、视图

创建视图的作用是可以用来保存临时表的数据，这样就不需要导入导出多次，解决了我以前不断导入导出保存数据的问题，真的是非常好。

GO
create view r1 as
select * from 总中奖 where exists(select * from 密码错误 where 手机号 = 总中奖.手机号)
go
exec sp_helptext 'r1'
GO
select * from r1 
GO

--修改视图
go
alter view r1 as
select * from 密码错误 where 金额>50
go

--select * from r1 

--对视图文本进行加密
go
create view r2 with encryption as
select * from 密码错误 where 金额<=50
go
这样的话 在企业管理器视图一览是看不到r2的

select * from r2


--删除视图
drop view r2

--操作视图
insert into r1(手机号,金额,个数) values('1',20,10) 这里插入失败
delete r1 where 个数=1
update r1 set 个数=3 where 金额>0
select * from r1

22、存储过程

存储过程最多可以有2100个承诺书，参数名称必须符合标识符的规则，每个过程的参数仅用于该过程本身，对于可以是cursor数据类型的输出参数，没有最大数目的限制。

use HB

<pre name="code" class="sql">--创建存储过程

go--这里需要用到动态sql 语句create procedure p1 @表 varchar(20),@ot varchar(20) outputasbegin--declare @sql varchar(50)--set @sql='select * from 密码错误'--exec (@sql) --这两种方法都是可以的，下面这种加上了n,但是这里由于这里用到外面的表 ，所以要用动态语句，只能用下面的declare @sql nvarchar(100)set @sql=N'select * from '+@表exec sp_executesql @sqlset @ot='存在表'+@表--print @otreturn 1endgo--修改存储过程--查看存储过程exec sp_helptext p1 --查看相关定义的代码exec sp_help p1 --查看创建时间等信息--重命名存储过程exec sp_rename p1,pexec sp_helptext p--删除存储过程drop procedure p--执行存储过程godeclare @ret int,@ot varchar(20)exec @ret=p1 '密码错误',@ot output --切记这里要加上outputprint @retprint @otgo--扩展存储过程sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL12.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';--这里xp-hello 为扩展存储过程的函数名，后面的为路径--对存储过程的定义文本进行加密--和视图一样 加上 with encryption 即可--应用存储过程sp_monitor 和sp_spaceused--具体怎么用还不是很清楚--自动执行存储过程exec sp_procoption 'p1','startup','on'

23、触发器

触发器是一种特殊类型的存储过程，与表格紧密相连，当用户修改表中数据时，触发器将自动执行。触发器可以使用语句进行复杂的逻辑处理，它基于一个表创建，但是可以对多个表进行操作。常常用于复杂的业务规则。

注意：

其他的一样，create trigger 语句必须是批处理的第一个语句。

这里用到别人的语句

SQL Server 触发器

触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。

Ø 什么是触发器

    触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。

 

    DML触发器分为：

    1、 after触发器（之后触发）

        a、 insert触发器

        b、 update触发器

        c、 delete触发器

 

    2、 instead of 触发器 （之前触发）

 

    其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

 

    触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

 

对表的操作	Inserted逻辑表	Deleted逻辑表
增加记录（insert）	存放增加的记录	无
删除记录（delete）	无	存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

    Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

 

Ø 创建触发器

    语法

create trigger tgr_name
on table_name
with encrypion –加密触发器
    for update...
as
    Transact-SQL

    # 创建insert类型触发器

--创建insert插入类型触发器
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @temp int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;    
    insert into student values(@name, 18 + @id, @temp, @id);
    print '添加学生成功！';
go
--插入数据
insert into classes values('5班', getDate());
--查询数据
select * from classes;
select * from student order by id;

     insert触发器，会在inserted表中添加一条刚插入的记录。

 

    # 创建delete类型触发器

--delete删除类型触发器
if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on classes
    for delete --删除触发
as
    print '备份数据中……';    
    if (object_id('classesBackup', 'U') is not null)
        --存在classesBackup，直接插入数据
        insert into classesBackup select name, createDate from deleted;
    else
        --不存在classesBackup创建再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go
--
--不显示影响行数
--set nocount on;
delete classes where name = '5班';
--查询数据
select * from classes;
select * from classesBackup;

   delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。

 

    # 创建update类型触发器

--update更新类型触发器
if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的数据
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的数据
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print '级联修改数据成功！';
        end
    else
        print '无需修改student表！';
go
--查询数据
select * from student order by id;
select * from classes;
update classes set name = '五班' where name = '5班';

     update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。

 

    # update更新列级触发器

if (object_id('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on classes
    for update
as
    --列级触发器：是否更新了班级创建时间
    if (update(createDate))
    begin
        raisError('系统提示：班级创建时间不能修改！', 16, 11);
        rollback tran;
    end
go
--测试
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = '四班' where id = 7;

     更新列级触发器可以用update是否判断更新列记录；

 

    # instead of类型触发器

       instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容。

       创建语法

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    T-SQL

   

      # 创建instead of触发器

if (object_id('tgr_classes_inteadOf', 'TR') is not null)
    drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_inteadOf
on classes
    instead of delete/*, update, insert*/
as
    declare @id int, @name varchar(20);
    --查询被删除的信息，病赋值
    select @id = id, @name = name from deleted;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
    --先删除student的信息
    delete student where cid = @id;
    --再删除classes的信息
    delete classes where id = @id;
    print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;

   

      # 显示自定义消息raiserror

if (object_id('tgr_message', 'TR') is not null)
    drop trigger tgr_message
go
create trigger tgr_message
on student
    after insert, update
as raisError('tgr_message触发器被触发', 16, 10);
go
--test
insert into student values('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;

    # 修改触发器

alter trigger tgr_message
on student
after delete
as raisError('tgr_message触发器被触发', 16, 10);
go
--test
delete from student where name = 'lucy';

    # 启用、禁用触发器

--禁用触发器
disable trigger tgr_message on student;
--启用触发器
enable trigger tgr_message on student;

    # 查询创建的触发器信息

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';

--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

--查看创建触发器语句
exec sp_helptext 'tgr_message';

    # 示例，验证插入数据

if ((object_id('tgr_valid_data', 'TR') is not null))
    drop trigger tgr_valid_data
go
create trigger tgr_valid_data
on student
after insert
as
    declare @age int,
            @name varchar(20);
    select @name = s.name, @age = s.age from inserted s;
    if (@age < 18)
    begin
        raisError('插入新数据的age有问题', 16, 1);
        rollback tran;
    end
go
--test
insert into student values('forest', 2, 0, 7);
insert into student values('forest', 22, 0, 7);
select * from student order by id;

    # 示例，操作日志

if (object_id('log', 'U') is not null)
    drop table log
go
create table log(
    id int identity(1, 1) primary key,
    action varchar(20),
    createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
    drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log(action) values('updated');
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log(action) values('inserted');
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log(action) values('deleted');
    end
go
--test
insert into student values('king', 22, 1, 7);
update student set sex = 0 where name = 'king';
delete student where name = 'king';
select * from log;
select * from student order by id;

24、游标的使用

游标提供了一种对从表中检索出的数据进行操作的灵活手段

其必须有4个组成部分

声明游标

打开游标

从一个游标中查找信息

关闭游标

use HB

--创建游标
GO
declare cur cursor for
select * from 密码错误 order by 手机号 desc
--for read only  --创建只读游标
--for update --更新游标
open cur--打开游标
fetch next from cur
while @@fetch_status=0
begin
	fetch next from cur
end
close cur--关闭游标
deallocate cur--释放游标



--将查询到的数据fetch 进变量
GO
declare cur cursor for
select * from 密码错误 order by 手机号 desc
open cur--打开游标
declare @手机号 varchar(20),@服务密码 varchar(20),@登录密码 varchar(20),@支付密码 varchar(20),@金额 int,@个数 int,@总金额 int
fetch next from cur into @手机号,@服务密码,@登录密码,@支付密码,@金额,@个数,@总金额
while @@fetch_status=0
begin
	print @手机号
	fetch next from cur into @手机号,@服务密码,@登录密码,@支付密码,@金额,@个数,@总金额

end
close cur--关闭游标
deallocate cur--释放游标



--设计带滚动的数据查询
GO
declare cur scroll cursor for
select * from 密码错误 order by 手机号 desc
open cur--打开游标
fetch next from cur
fetch prior from cur --前一行
fetch first from  cur
fetch last from cur--最后一行
fetch absolute 2 from cur-- 绝对排序的第二行
fetch relative 2 from cur --从当前游标数下面第2行，同样有-2之类的
close cur--关闭游标
deallocate cur--释放游标



--使用游标更新数据
--利用 where current of cur 来操作当前行
GO
declare cur scroll cursor for
select * from 密码错误 order by 手机号 desc
for update of 个数 --只允许更新个数
open cur--打开游标
fetch next from cur
update 密码错误 set 个数=3 where current of cur  --同样可以用 delete 
fetch first from  cur
close cur--关闭游标
deallocate cur--释放游标


--利用索引改变游标中行的顺序,利用索引先排好序，再操作
--建立索引
create index index1
on 密码错误(手机号 desc)
GO
declare cur scroll cursor for
select * from 密码错误  with (index(index1))--那么里面就不能再用排序了
open cur--打开游标
fetch next from cur
close cur--关闭游标
deallocate cur--释放游标

25、分布式查询

分布式查询可以访问来自多种异类数据源的数据，而这些数据可存储在相同或不同的计算机上。链接服务器要做的工作是：配置好连接服务器的名字、相应的位置和登录信息，以便SQL SERVER 可以存取链接服务器上的数据。

use HB
/*
--建立链接服务器
exec sp_addlinkedserver
@server='hblk',--注意，
@srvproduct='SQL Server'
*/

--查看链接服务器
--exec sp_linkedservers

--删除链接服务器，这里和删除视图等不一样
--exec sp_dropserver 'hblk'
--建立映射登录
/*
go
sp_addlinkedsrvlogin
'aaa',--远程服务器名字
'false',
'sa', --本地登录
'ruser',--远程登录用户名
'rpaw'--密码
*/


--查询映射登录
--exec sp_helplinkedsrvlogin

--删除映射登录
--exec sp_droplinkedsrvlogin
--'远程服务器名',
--'sa'


--设置链接选项,这里是设置数据访问属性为true,其余的同样可以通过后两位来进行设置
/*
go 
exec sp_serveroption
'hblk',
'data access',
true
*/

建立好了与外部数据源的链接后，SQL Server 就可以在查询中引用外部数据了。其和本地查询类似

这个功能，我以前建立链接服务器，用作业来操作数据的时候，是有用过的。

use HB

--简单查询
--select * from [服务器名].[数据库名].[所有者名].[表名]

--利用OpenDatasource()查询
--利用它来代替服务器名，当然还有其它用途，这个还不知道

--用OpenQuery查询
--select * from openquery(hblk,'select * from [数据库名].[所有者名].[表名]')

--OpenRowSet()
--select * from openrowset('sqloledb','hblk';'sa''....)不确定怎么用

--分布式事务处理
set xact_Abort on
begin distributed transaction --开始事务
update 密码错误 set 金额=10 
update [服务器名].[数据库名].[所有者名].[表名] set ...
commit transaction	--提交事务