sql 复习知识大全2
2012-06-21 11:18 周六一 阅读(737) 评论(0) 编辑 收藏 举报 1 --触发器trigger
2 disable trigger tri_t1 on database --禁用触发器
3 enable trigger tri_t1 on database --开启触发器
4
5 use ReviewDatabase
6 go
7 --1.For
8 create trigger tri_t1 --修改触发器时,可以将create改为alter,再修改相应语句
9 on dbo.t1 --可以是视图
10 --with encryption --表进行加密(可省)
11 for insert,update,delete --阻止删除时,可以用ROLLBACK回滚
12 --not for replication --表明当复制处理修改与触发器相关联的表时，触发器不能被执行(可省)
13 as
14 select * from t1,t2 where t1.id2=t2.id2
15 go
16
17 drop trigger tri_t1 --删除触发器
18 --2.after 和for一样 先会检测约束,这里会报错
19 use ReviewDatabase
20 go
21 create trigger tri_t2
22 on dbo.t2
23 after insert,update,delete
24 as
25 begin
26 select * from inserted
27 select * from updated
28 select * from deleted
29 end
30
31 insert t2(id1,id2) values(9,9)--这里会报错
32 drop trigger tri_t2
33 --2.after of --先会执行触发器再检测约束
34 use ReviewDatabase
35 go
36 alter trigger tri_t2_of
37 on dbo.t2
38 after insert,update,delete
39 as
40 begin
41 print 'inserted'
42 select * from inserted
43 print 'deleted'
44 select * from deleted
45 end
46
47 delete t2 where id1=9
48 insert t2(id1,id2) values(9,9)
49 update t2 set id3=10 where id1=9
50
51 select * from t2 where id1=9
52 /*-----------------------------------------------------------------------------------------------------*/
53 --视图
54 use ReviewDatabase
55 go
56 create view vi_t2
57 with encryption
58 as
59 select * from t1
60 go
61 select * from vi_t2
62 drop view vi_t2
63 /*-----------------------------------------------------------------------------------------------------*/
64 --储存过程procedure
65 --常用系统存储过程有
66 exec sp_databases; --查看数据库
67 exec sp_tables;--查看表
68 exec sp_columns t2;--查看列
69 exec sp_helpIndex t1;--查看索引
70 exec sp_helpConstraint t2;--约束
71 exec sp_stored_procedures;
72 exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
73 exec sp_rename t1, stuInfo;--修改表、索引、列的名称
74 exec sp_renamedb myTempDB, myDB;--更改数据库名称
75 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
76 exec sp_helpdb;--数据库帮助，查询数据库信息
77 exec sp_helpdb ReviewDatabase;--查询数据库信息
78
79 --表重命名
80 exec sp_rename 'test_sub', 'stu';
81 select * from test_sub;
82 --列重命名
83 exec sp_rename 'stud.name', 'sName', 'column';
84 exec sp_help 'stud';
85 --重命名索引
86 exec sp_rename N't1.idx_cid', N'idx_cidd', N'index';
87 exec sp_help 't1';
88 --查询所有存储过程
89 select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
90
91 --自定义存储过程
92 --1、 创建语法
93 /*
94 create proc | procedure pro_name
95 [{@参数数据类型} [=默认值] [output],
96 {@参数数据类型} [=默认值] [output],
97 ....
98 ]
99 as
100 SQL_语句
101 */
102 --2、 创建不带参数存储过程
103 --创建存储过程
104 if (exists (select * from sys.objects where name = 'proc_get_t1'))
105 drop proc proc_get_t1
106 go
107 create proc proc_get_t1
108 as
109 select * from t1;
110 --调用、执行存储过程
111 exec proc_get_t1;
112
113 --3、 修改存储过程
114 --修改存储过程
115 alter proc proc_get_t1
116 as
117 select * from t1;
118
119 --4、 带参存储过程
120 --带参存储过程
121 if (object_id('proc_find_stu', 'P') is not null)
122 drop proc proc_find_t1
123 go
124 create proc proc_find_t1(@startId int, @endId int)
125 as
126 select * from t1 where id1 between @startId and @endId
127 go
128
129 exec proc_find_t1 2, 4; --不可以打乱顺序
130 exec proc_find_t1 @startId=2,@endId=4 --可以打乱顺序
131
132 --5、 带通配符参数存储过程
133 --带通配符参数存储过程
134 if (object_id('proc_findStudentByName', 'P') is not null)
135 drop proc proc_findt1ByName
136 go
137 create proc proc_findt1ByName(@name varchar(10) = '%j%')
138 as
139 select * from t1 where name like @name;
140 go
141
142 exec proc_findt1ByName;
143 exec proc_findt1ByName '%a%';
144
145 --6、 带输出参数存储过程
146 if (object_id('proc_gett1Record', 'P') is not null)
147 drop proc proc_gett1Record
148 go
149 create proc proc_gett1Record(
150 @id1 int, --默认输入参数
151 @id2 int out, --输出参数
152 @id3 int output--输入输出参数
153 )
154 as
155 update t1 set id2 = @id2, id3 = @id3 where id1 = @id1;
156 set @id2=@id2+1
157 set @id3=@id3+1
158 go
159
160 declare @id1 int=18,@id2 int,@id3 int;
161 set @id2 = 18;
162 select @id3 = 18;
163 exec proc_gett1Record @id1, @id2 out, @id3 output;
164 select @id2, @id3;
165 print @id2 + ' ' + @id3;
166
167
168 --7、 不缓存存储过程
169 --WITH RECOMPILE 不缓存
170 if (object_id('proc_temp', 'P') is not null)
171 drop proc proc_temp
172 go
173 create proc proc_temp
174 with recompile
175 as
176 select * from t1;
177 go
178
179 exec proc_temp;
180
181 --8、 加密存储过程
182 --加密WITH ENCRYPTION
183 if (object_id('proc_temp_encryption', 'P') is not null)
184 drop proc proc_temp_encryption
185 go
186 create proc proc_temp_encryption
187 with encryption
188 as
189 select * from t1;
190 go
191
192 exec proc_temp_encryption;
193 exec sp_helptext 'proc_temp';
194 exec sp_helptext 'proc_temp_encryption';
195
196 --9、 带游标参数存储过程
197 if (object_id('proc_cursor', 'P') is not null)
198 drop proc proc_cursor
199 go
200 create proc proc_cursor
201 @cur cursor varying output
202 as
203 set @cur = cursor forward_only static for
204 select id1, id2, id3 from t1;
205 open @cur;
206 go
207 --调用
208 declare @exec_cur cursor;
209 declare @id1 int,
210 @id2 int,
211 @id3 int;
212 exec proc_cursor @cur = @exec_cur output;--调用存储过程
213 fetch next from @exec_cur into @id1, @id2, @id3;
214 while (@@fetch_status = 0)
215 begin
216 fetch next from @exec_cur into @id1, @id2, @id3;
217 print @id1 + @id2 + @id3;
218 end
219 close @exec_cur;
220 deallocate @exec_cur;--删除游标
2 disable trigger tri_t1 on database --禁用触发器
3 enable trigger tri_t1 on database --开启触发器
4
5 use ReviewDatabase
6 go
7 --1.For
8 create trigger tri_t1 --修改触发器时,可以将create改为alter,再修改相应语句
9 on dbo.t1 --可以是视图
10 --with encryption --表进行加密(可省)
11 for insert,update,delete --阻止删除时,可以用ROLLBACK回滚
12 --not for replication --表明当复制处理修改与触发器相关联的表时，触发器不能被执行(可省)
13 as
14 select * from t1,t2 where t1.id2=t2.id2
15 go
16
17 drop trigger tri_t1 --删除触发器
18 --2.after 和for一样 先会检测约束,这里会报错
19 use ReviewDatabase
20 go
21 create trigger tri_t2
22 on dbo.t2
23 after insert,update,delete
24 as
25 begin
26 select * from inserted
27 select * from updated
28 select * from deleted
29 end
30
31 insert t2(id1,id2) values(9,9)--这里会报错
32 drop trigger tri_t2
33 --2.after of --先会执行触发器再检测约束
34 use ReviewDatabase
35 go
36 alter trigger tri_t2_of
37 on dbo.t2
38 after insert,update,delete
39 as
40 begin
41 print 'inserted'
42 select * from inserted
43 print 'deleted'
44 select * from deleted
45 end
46
47 delete t2 where id1=9
48 insert t2(id1,id2) values(9,9)
49 update t2 set id3=10 where id1=9
50
51 select * from t2 where id1=9
52 /*-----------------------------------------------------------------------------------------------------*/
53 --视图
54 use ReviewDatabase
55 go
56 create view vi_t2
57 with encryption
58 as
59 select * from t1
60 go
61 select * from vi_t2
62 drop view vi_t2
63 /*-----------------------------------------------------------------------------------------------------*/
64 --储存过程procedure
65 --常用系统存储过程有
66 exec sp_databases; --查看数据库
67 exec sp_tables;--查看表
68 exec sp_columns t2;--查看列
69 exec sp_helpIndex t1;--查看索引
70 exec sp_helpConstraint t2;--约束
71 exec sp_stored_procedures;
72 exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
73 exec sp_rename t1, stuInfo;--修改表、索引、列的名称
74 exec sp_renamedb myTempDB, myDB;--更改数据库名称
75 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
76 exec sp_helpdb;--数据库帮助，查询数据库信息
77 exec sp_helpdb ReviewDatabase;--查询数据库信息
78
79 --表重命名
80 exec sp_rename 'test_sub', 'stu';
81 select * from test_sub;
82 --列重命名
83 exec sp_rename 'stud.name', 'sName', 'column';
84 exec sp_help 'stud';
85 --重命名索引
86 exec sp_rename N't1.idx_cid', N'idx_cidd', N'index';
87 exec sp_help 't1';
88 --查询所有存储过程
89 select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
90
91 --自定义存储过程
92 --1、 创建语法
93 /*
94 create proc | procedure pro_name
95 [{@参数数据类型} [=默认值] [output],
96 {@参数数据类型} [=默认值] [output],
97 ....
98 ]
99 as
100 SQL_语句
101 */
102 --2、 创建不带参数存储过程
103 --创建存储过程
104 if (exists (select * from sys.objects where name = 'proc_get_t1'))
105 drop proc proc_get_t1
106 go
107 create proc proc_get_t1
108 as
109 select * from t1;
110 --调用、执行存储过程
111 exec proc_get_t1;
112
113 --3、 修改存储过程
114 --修改存储过程
115 alter proc proc_get_t1
116 as
117 select * from t1;
118
119 --4、 带参存储过程
120 --带参存储过程
121 if (object_id('proc_find_stu', 'P') is not null)
122 drop proc proc_find_t1
123 go
124 create proc proc_find_t1(@startId int, @endId int)
125 as
126 select * from t1 where id1 between @startId and @endId
127 go
128
129 exec proc_find_t1 2, 4; --不可以打乱顺序
130 exec proc_find_t1 @startId=2,@endId=4 --可以打乱顺序
131
132 --5、 带通配符参数存储过程
133 --带通配符参数存储过程
134 if (object_id('proc_findStudentByName', 'P') is not null)
135 drop proc proc_findt1ByName
136 go
137 create proc proc_findt1ByName(@name varchar(10) = '%j%')
138 as
139 select * from t1 where name like @name;
140 go
141
142 exec proc_findt1ByName;
143 exec proc_findt1ByName '%a%';
144
145 --6、 带输出参数存储过程
146 if (object_id('proc_gett1Record', 'P') is not null)
147 drop proc proc_gett1Record
148 go
149 create proc proc_gett1Record(
150 @id1 int, --默认输入参数
151 @id2 int out, --输出参数
152 @id3 int output--输入输出参数
153 )
154 as
155 update t1 set id2 = @id2, id3 = @id3 where id1 = @id1;
156 set @id2=@id2+1
157 set @id3=@id3+1
158 go
159
160 declare @id1 int=18,@id2 int,@id3 int;
161 set @id2 = 18;
162 select @id3 = 18;
163 exec proc_gett1Record @id1, @id2 out, @id3 output;
164 select @id2, @id3;
165 print @id2 + ' ' + @id3;
166
167
168 --7、 不缓存存储过程
169 --WITH RECOMPILE 不缓存
170 if (object_id('proc_temp', 'P') is not null)
171 drop proc proc_temp
172 go
173 create proc proc_temp
174 with recompile
175 as
176 select * from t1;
177 go
178
179 exec proc_temp;
180
181 --8、 加密存储过程
182 --加密WITH ENCRYPTION
183 if (object_id('proc_temp_encryption', 'P') is not null)
184 drop proc proc_temp_encryption
185 go
186 create proc proc_temp_encryption
187 with encryption
188 as
189 select * from t1;
190 go
191
192 exec proc_temp_encryption;
193 exec sp_helptext 'proc_temp';
194 exec sp_helptext 'proc_temp_encryption';
195
196 --9、 带游标参数存储过程
197 if (object_id('proc_cursor', 'P') is not null)
198 drop proc proc_cursor
199 go
200 create proc proc_cursor
201 @cur cursor varying output
202 as
203 set @cur = cursor forward_only static for
204 select id1, id2, id3 from t1;
205 open @cur;
206 go
207 --调用
208 declare @exec_cur cursor;
209 declare @id1 int,
210 @id2 int,
211 @id3 int;
212 exec proc_cursor @cur = @exec_cur output;--调用存储过程
213 fetch next from @exec_cur into @id1, @id2, @id3;
214 while (@@fetch_status = 0)
215 begin
216 fetch next from @exec_cur into @id1, @id2, @id3;
217 print @id1 + @id2 + @id3;
218 end
219 close @exec_cur;
220 deallocate @exec_cur;--删除游标
