这段时间,报了浦软培训的.NET,现在整理回顾下,算是个小小总结吧
为了便于操作,我没有在多个数据库间切换数据库实例,以一个总的数据库实例 test_demo为源进行的相关操作,代码的注释根据我的理解,并结合相关文献进行的解说,如果哪位前辈觉得注释的解说不妥,可给予纠正,谢谢。现在不废话了,上代码
1 ---练习
2 use master
3 /***********************************************
4 1、创建数据库
5 ************************************************/
6 ---数据库创建前的检测
7 if exists(select * from sys.sysdatabases where name=‘test_demo‘)
8 drop database test_demo --删除已有的数据库test_demo
9 create database test_demo --开始创建新数据库test_demo
10 on primary --默认就属于primary 主文件组,可省略 ,但必须有on
11 (
12 name="test_demo", --主数据文件的逻辑名
13 filename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_demo.mdf", --主数据文件的物理名
14 size=10mb, --主数据文件的初始大小
15 filegrowth=10% --主数据文件的增长率(可以为百分比,也可为实数)
16 )
17 )
18 log on --以下为日志文件描述,同上
19 (
20 name="test_name",
21 filename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_demo_log.ldf",
22 size=1mb,
23 filegrowth=10%
24 )
25 --检查数据库是否创建成功
26 select * from sys.sysdatabases where name=‘test_demo‘
27
28
29
30 /***********************************************
31 2、创建表
32 ************************************************/
33 use test_demo
34 create table stuInfo --创建学生信息表 stuInfo
35 (
36 stuName varchar(20) not null,
37 stuNo char(6) not null, --学号
38 stuAge int not null,
39 stuId numeric(18,0) null, --身份证号,小数位为0
40 stuSeat smallint identity(1,1), --座位号,自动递增
41 stuAddress text
42 )
43 go
44 create table stuMarks --创建学生成绩表
45 (
46 examNo char(7) not null, --考号
47 stuNo char(6) not null, --学号
48 writtenExam int not null, --笔记成绩
49 labExam int not null --机试成绩
50 )
51 go
52
53 --检测数据库表stuinfo和stumarks的存在情况
54 use studb
55 select * from sys.sysobjects where name= ‘stuinfo‘
56 select * from sys.sysobjects where name = ‘stumarks‘
57
58
59
60 /***********************************************
61 3、为表 stuInfo 和stuMarks添加约束
62 ************************************************/
63 /*
64 语法:
65 alter table tab_name
66 add constraint 约束名 约束类型 具体的约束说明
67 备注:
68 主键约束(Primary Key Constraint):要求主键列数据唯一,并且不允许为空
69 唯一约束(Unique Constraint):要求该列唯一,允许为空,但只能出现一个空值。
70 检查约束(Check Constraint):某列取值范围限制、格式限制等,如有关年龄的约束
71 默认约束(Default Constraint):某列的默认值,如我们的男性学员较多,性别默认为“男”
72 外键约束(Foreign Key Constraint):用于两表间建立关系,需要指定引用主表的那列
73 总结:主键约束和惟一月虽然都强调了数据的惟一性,
74 但主键约束强调的是数据主体,惟一约束则强调的是某一列
75 */
76 alter table stuInfo
77 add constraint pk_stuNo primary key(stuNo) --为学生学号添加主键约束
78
79 alter table stuInfo
80 add constraint uq_stuId unique(stuId) --为学生身份证添加惟一约束
81
82 alter table stuInfo
83 add constraint df_stuAddress default(‘地址不详‘) for stuAddress --为地址添加默认约束,如果地址不详。默认为“地址不详”
84
85 alter table stuInfo
86 add constraint ck_stuAge check(stuAge between 15 and 40) --为学生年龄添加年龄检查约束,要求年龄在15-40
87
88 alter table stuMarks
89 add constraint fk_stuMarks foreign key(stuNo) references stuInfo(stuNo) --为学生成绩添加外键约束(主表stuInfo和从表stuMarks建立关系,关键字段为stuNo)
90
91 exec sp_helpconstraint stuinfo --查看当前表stuinf的约束情况
92 exec sp_helpconstraint stumarks --查看当前表stumarks的约束情况
93
94
95 /***********************************************
96 4、插入相关数据
97 ************************************************/
98 insert into stuinfo values(‘张三‘,001,25,420621198906254567,‘湖北襄阳‘)
99 insert into stuinfo values(‘李四‘,002,25,420621198906121554,‘上海‘)
100 insert into stuinfo values(‘王五‘,003,34,420621198003166548,‘北京‘)
101 insert into stuinfo values(‘赵强‘,004,23,420621199105148756,‘湖北武汉‘)
102 insert into stuinfo values(‘钱海‘,005,22,420621199208154582,‘江苏苏州‘)
103 insert into stuinfo values(‘周国‘,006,30,420621198409265148,‘江苏南京‘)
104 insert into stuinfo values(‘孙坚‘,007,33,420621198304261855,‘‘)
105
106 insert into stumarks values(001,1,50.6,70.9)
107 insert into stumarks values(022,2,64.5,84.5)
108 insert into stumarks values(031,3,46.6,45.9)
109 insert into stumarks values(023,4,95.7,51.9)
110 insert into stumarks values(043,5,52.5,84.9)
111 insert into stumarks values(015,6,94.6,76.9)
112 insert into stumarks values(006,7,86.5,84.6)
113
114 --查询数据插入情况
115 select * from stuinfo
116 select * from stuMarks
117
118 /***********************************************
119 5、为表stuInfo和stuMarks创建视图
120 ************************************************/
121 /*
122 视图是一张虚拟表,可以方便不同用户的查询,提高数据的安全性,筛选特定的数据行。
123 视图的用途:
124 筛选表中的行
125 防止未经许可的用户访问敏感数据
126 降低数据库的复杂程度
127 将多个物理数据库抽象为一个逻辑数据库
128 */
129 if exists(select * from sys.sysobjects where name=‘view_stuInfo_stuMarks‘)
130 drop view view_stuInfo_stuMarks
131 go
132 create view view_stuInfo_stuMarks
133 as
134 select ‘姓名‘=stuName, ‘学号‘=stuInfo.stuNo,‘考试号‘= stuMarks.ExamNo,
135 ‘笔记成绩‘=writtenExam,‘机试成绩‘=labExam,
136 ‘平均分‘=(writtenExam +labExam)/2
137 from stuInfo left join stuMarks
138 on stuInfo.stuNo = stuMarks.stuNo
139 go
140
141 select * from view_stuInfo_stuMarks --使用视图
142
143
144 --使用相关的条件查询
145 --求出平均分数
146 use test_demo
147 --获取每个学员的总成绩
148 select a.stuName as ‘学生‘, a.stuNo as ‘学号‘,b.ExamNo as ‘考号‘, b.writtenExam as ‘笔试成绩‘,b.labExam as ‘机试成绩‘,(b.writtenExam+b.labExam) as ‘总成绩‘
149 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
150 order by ‘总成绩‘ desc
151
152 /*
153 总结:
154 当使用group by条件语句进行筛选时,select项的字段中,除使用聚合函数进行统计的字段外,其他需要select出的字段名必须出现在group by分组条件中
155 */
156 select a.stuName as ‘学生‘, a.stuNo as ‘学号‘,b.ExamNo as ‘考号‘, b.writtenExam as ‘笔试成绩‘,b.labExam as ‘机试成绩‘,sum(b.writtenExam+b.labExam) as ‘总成绩‘,(b.writtenExam+b.labExam)/2 as ‘平均成绩‘
157 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
158 group by a.stuName,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
159 order by ‘总成绩‘ desc
160
161
162 --根据学员总的平均成绩和每个学员的总成绩,判断是否及格,划线为150分
163 declare @Avg1 float
164 --获取当前学员总的平均成绩
165 select @Avg1=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b
166 where a.stuNo=b.stuNo
167
168 select a.stuName as ‘学生‘, a.stuNo as ‘学号‘,b.ExamNo as ‘考号‘, b.writtenExam as ‘笔试成绩‘,b.labExam as ‘机试成绩‘,(b.writtenExam+b.labExam) as ‘总成绩‘, @avg1 as ‘及格分数‘,
169 (
170 case
171 when (b.writtenExam+b.labExam)<@avg1 then ‘不及格‘
172 when (b.writtenExam+b.labExam)>=@avg1 then ‘及格‘
173 end
174 ) as ‘及格情况‘
175 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
176 order by ‘总成绩‘ desc
177
178
179 declare @Avg float --声明变量@AVG用于存储学员总的平均成绩
180 --获取当前学员总的平均成绩
181 select @Avg=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b
182 where a.stuNo=b.stuNo
183
184 select a.stuName as ‘学生‘, a.stuNo as ‘学号‘,b.ExamNo as ‘考号‘, b.writtenExam as ‘笔试成绩‘,b.labExam as ‘机试成绩‘,sum(b.writtenExam+b.labExam) as ‘总成绩‘, @avg as ‘及格分数‘,
185 (
186 case
187 when (b.writtenExam+b.labExam)<@avg then ‘不及格‘
188 when (b.writtenExam+b.labExam)>=@avg then ‘及格‘
189 end
190 ) as ‘及格情况‘
191 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
192 group by a.stuName,a.stuNo,b.ExamNo, b.writtenExam ,b.labExam
193 order by ‘总成绩‘ desc
194
195 /***********************************************
196 6、触发器 triggers
197 触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。
198 触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行。
199
200 说明:模拟银行银行存款事件,用户的取款和存款操作对储蓄用户账目相关信息的更新
201 练习步骤:
202 01、创建bank表和transInfo表
203 02、创建触发器
204 ************************************************/
205 use test_demo
206 --创建表bank和 transInfo
207 create table triggers_bank --创建账户存款表bank
208 (
209 customerName nvarchar(255) not null,
210 cardID varchar(255) not null unique,
211 currentMoney money
212 )
213 create table triggers_transInfo --创建账户交易表transInfo,用户记录账户操作事件日志
214 (
215 transDate datetime not null,
216 cardID varchar(255) not null,
217 transType nvarchar(50) not null,
218 transMoney money
219 )
220
221 --向表中插入相关测试数据
222 insert into triggers_bank values(‘张三‘,‘1001001‘,1000.000)
223 insert into triggers_bank values(‘李四‘,‘1001002‘,1.000)
224 insert into triggers_transinfo values(getDate(),‘1001001‘,‘支取‘,100.00)
225 --查询数据是否插入成功
226 select * from triggers_bank
227 select * from triggers_transInfo
228
229 --开始建立触发器
230 /*
231 为表triggers_transInfo创建insert事件触发器,当用户进行相关存取操作时自动触发并更新账户bank表下的账户信息
232 */
233 if exists(select * from sys.triggers where name=‘trig__insert_transInfo‘)
234 drop trigger trig__insert_transInfo
235 go
236 create trigger trig__insert_transInfo --创建名为 trig__insert_transInfo的触发器
237 on triggers_transInfo --在表triggers_stransInfo上创建触发器
238 for insert --触发器类型为insert 类
239 as
240 --声明变量@type为插入操作类型支取/存入、@outMoney为操作的数额、@cardID为卡号、@balance为当前账户余额
241 declare @type nvarchar(50), @outMoney money, @cardID varchar(255),@balance money
242 select @type=transType ,@outMoney = transMoney, @cardId = cardID from inserted
243 select @balance=currentMoney from triggers_bank where cardId = @cardID
244 if(@type =‘支取‘)
245 begin
246 if(@outMoney >=1000)
247 begin
248 print ‘对不起,单次支取额度不可超过1000,即将返回,请稍等‘
249 rollback tran
250 end
251 else if(@balance<@outMoney)
252 begin
253 print ‘对不起,当前卡中余额不足,即将返回,请稍后‘
254 rollback tran
255 end
256 else
257 begin
258 update triggers_bank set currentMoney= currentMoney - @outMoney
259 where cardID = @cardID
260 end
261 end
262 else
263 update triggers_bank set currentMoney = currentMoney+@outMoney
264 where cardID = @cardID
265 go
266
267 --测试前查询账户信息 triggers_bank和triggers_transInfo
268 select * from triggers_bank
269 select * from triggers_transInfo
270
271 --测试触发器
272 --1.1、在triggers_transInfo表中账户1001001张三先存2000,然后在triggers_bank中查询账户余额变动
273 insert into triggers_transInfo values(getdate(),‘1001001‘,‘存入‘,2000) --存入2000,
274 --1.2、在triggers_transInfo表中账户1001001张三去除1000,此时会提示支取超额,操作失败
275 insert into triggers_transInfo values(getDate(),‘1001001‘,‘支取‘,1000) -- 取出1000,此时将提示失败
276 --2.1、在triggers_transInfo表中账户1001002李四先存2000,然后在triggers_bank中查询账户余额变动
277 insert into triggers_transInfo values(getdate(),‘1001002‘,‘存入‘,1000) --存入2000,
278 --2.2、在triggers_transInfo表中账户1001002李四取出500,此时操作成功,在triggers_bank中查询余额变动
279 insert into triggers_transInfo values(getDate(),‘1001002‘,‘支取‘,500) -- 取出500,成功操作
280 --2.3、在triggers_transInfo表中账户1001002李四取出600,当卡中余额不足时,提示操作失败,
281 insert into triggers_transInfo values(getDate(),‘1001002‘,‘支取‘,600) --取出600,成功失败
282
283 use test_demo
284 --drop trigger trig__insert_transInfo --删除指定的触发器
285 select * from sys.triggers --查询当前的触发器
286
287
288
289 /***********************************************
290 7、存储
291 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
292
293 语法:
294 create proc/procedure 存储过程名
295 [ @参数1 数据类型 = 默认值 OUTPUT,
296 …… ,
297 @参数n 数据类型 = 默认值 OUTPUT
298 ] --可带参数的输出亦可不带参数的输出
299 AS
300 SQL语句
301 GO
302 备注:删除存储过程
303 drop proc 存储过程名
304
305 使用步骤:
306 1、先使用create proc 创建存储过程
307 2、再使用 exec 调用存储过程
308 语法:exec 存储过程名
309 ************************************************/
310 --为表stuInfo和stuMarks创建存储过程
311 select * from stuInfo
312 select * from stuMarks
313
314 --7.0、创建不带参数的存储过程 proc_for_stuInfo******************************
315 create procedure proc_for_stuInfo
316 as
317 declare @writtenAvg float ,@labAvg float
318 select @writtenAvg=avg(writtenexam),@labAvg=avg(labexam) from stuMarks
319 print ‘笔试平均成绩: ‘+convert(varchar(5),@writtenAvg)+‘ 分‘
320 print ‘机试平均成绩: ‘+convert(varchar(5),@labAvg)+‘ 分‘
321 if(@writtenAvg>70 and @labAvg >70)
322 print ‘本次平均成绩:良‘
323 else
324 print ‘本次平均成绩:差‘
325 go
326
327 --调用存储过程proc_for_stuInfo
328 exec proc_for_stuInfo
329
330 --7.1、创建带参数的存储过程 proc_for_stuInfo1******************************
331 create procedure proc_for_stuInfo1
332 @writtenPass float ,-- 预置参数1:人为制定的笔记及格线
333 @labPass float --预置参数1:人为制定的机试及格线
334 as
335 select count(*) as ‘未通过人数‘
336 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
337 where b.writtenexam <@writtenPass or b.labexam <@labPass
338 select a.stuName as ‘未通过学员‘, a.stuNo as ‘学号‘, b.examNo as ‘考号‘, b.writtenexam as ‘笔记成绩‘
339 ,b.labexam as ‘机试成绩‘
340 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
341 where b.writtenexam <@writtenPass or b.labexam <@labPass
342 order by a.stuNo
343 go
344
345 --调用带参数的存储过程 proc_for_stuInfo1
346 /*
347 语法:exec 带参数的存储过程名 参数1,参数2
348 */
349 exec proc_for_stuInfo1 70,70
350
351
352
353 --7.2、带输出参数的存储过程 proc_for_stuInfo2 ******************************
354 create procedure proc_for_stuInfo2
355 @stuSum int output,
356 @notpassSum int output, --输出参数:表示没有通过的人数
357 @writtenPass float ,-- 预置参数1:人为制定的笔记及格线
358 @labPass float --预置参数1:人为制定的机试及格线
359 as
360 select @stuSum =count(*) from stuInfo
361 --统计未通过的人数
362 select @notpassSum=count(*)
363 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
364 where b.writtenexam <@writtenPass or b.labexam <@labPass
365 --列出所有为通过的学员信息
366 select a.stuName as ‘未通过学员‘, a.stuNo as ‘学号‘, b.examNo as ‘考号‘, b.writtenexam as ‘笔记成绩‘
367 ,b.labexam as ‘机试成绩‘
368 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
369 where b.writtenexam <@writtenPass or b.labexam <@labPass
370 order by a.stuNo
371 go
372
373 --调用带有输出参数的存储过程 proc_for_stuInfo2
374 declare @sum int,@stuSum int, @percent float(1),@percentPass float(1)
375 set @percentPass = 0.50
376 exec proc_for_stuInfo2 @stuSum output, @sum output,70,70
377 select @percent=(cast(@sum as float(1)))/(cast(@stuSum as float(1)))
378
379 if(@percent<=@percentPass)
380 select @stuSum as ‘总人数‘, @sum as ‘未通过人数‘,@percent as ‘通过率‘, ‘未通过人数超过‘+cast(@percentPass as varchar(25))+‘@prenc,及格分数还应下调‘ as ‘调控说明‘
381 else
382 select @stuSum as ‘总人数‘, @sum as ‘未通过人数‘, @percent as ‘通过率‘,‘未通过人数已控制在‘+cast(@percentPass as varchar(25))+‘以下,及格分数线适中‘ as ‘调控说明‘
383 go
384
385
386
387 --7.3、带参数且有有显示错误信息的存储过程综合事例 proc_for_stuInfo3 ******************************
388 create procedure proc_for_stuInfo3
389 @stuSum int output,
390 @notpassSum int output, --输出参数:表示没有通过的人数
391 @writtenPass float ,-- 预置参数1:人为制定的笔记及格线
392 @labPass float --预置参数1:人为制定的机试及格线
393 as
394 ---判断指定的分数是否合规,即在指定的分数线内
395 if(not @writtenPass between 0 and 100) or( not @labPass between 0 and 100)
396 begin
397 raiserror(‘及格线错误,请指定0~100之间的分数值,统计中断退出‘,16,1)
398 return --立即返回,退出存储过程
399 end
400
401 select @stuSum =count(*) from stuInfo
402
403 --统计未通过的人数
404 select @notpassSum=count(*)
405 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
406 where b.writtenexam <@writtenPass or b.labexam <@labPass
407 --列出所有为通过的学员信息
408 select a.stuName as ‘未通过学员‘, a.stuNo as ‘学号‘, b.examNo as ‘考号‘, b.writtenexam as ‘笔记成绩‘
409 ,b.labexam as ‘机试成绩‘
410 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo
411 where b.writtenexam <@writtenPass or b.labexam <@labPass
412 order by a.stuNo
413 go
414
415
416 --调用带参数且有显示错误信息的存储过程 proc_for_stuInfo3
417 declare @sum int,@stuSum int, @percent float(1),@percentPass float(1), @error int
418 set @percentPass = 0.50
419 set @error = @@error
420 exec proc_for_stuInfo3 @stuSum output, @sum output,100,100
421 select ‘错误号:‘+ convert(varchar(5),@error)
422
423 if(@error <>0)
424 return --退出批处理,后续语句不再执行
425
426 select @percent=(cast(@sum as float(1)))/(cast(@stuSum as float(1)))
427
428 if(@percent<=@percentPass)
429 select @stuSum as ‘总人数‘, @sum as ‘未通过人数‘,@percent as ‘通过率‘, ‘未通过人数超过‘+cast(@percentPass as varchar(25))+‘@prenc,及格分数还应下调‘ as ‘调控说明‘
430 else
431 select @stuSum as ‘总人数‘, @sum as ‘未通过人数‘, @percent as ‘通过率‘,‘未通过人数已控制在‘+cast(@percentPass as varchar(25))+‘以下,及格分数线适中‘ as ‘调控说明‘
432 go