1 use master
2 go
3 if(exists (select * from sys.databases where name = 'testHome'))
4 drop database testHome
5 go
6 create database testHome
7 on(
8 name = 'testHome',
9 filename = 'c:\testHome.mdf'
10 )
11 log on(
12 name = 'testHome_Log',
13 filename = 'c:\testHome=Log.ldf'
14 )
15 go
16 if(exists (select * from sys.databases where name = 'testHome'))
17 drop database testHome
18 go
19 create database testHome
20 on primary(
21 name = 'testHome_data',
22 fileName = 'c:\testHome_data.mdf',
23 size = 3mb,
24 maxSize = 50mb,
25 fileGrowth = 10%
26 )
27 log on(
28 name = 'testHome_log',
29 fileName = 'c:\testHome_log.ldf',
30 size = 1mb,
31 fileGrowth = 1mb
32 )
33 go
34 if (exists (select * from sys.databases where name = 'testHome'))
35 drop database testHome
36 go
37 create database testHome
38 on primary(
39 name = 'testHome_data',
40 filename = 'c:\testHome_data.mdf',
41 size = 10mb,
42 maxSize = 50mb,
43 fileGrowth = 10%
44 ),(
45 name = 'testHome2_data',
46 filename = 'c:\testHome2_data.mdf',
47 size = 10mb,
48 maxsize = 50mb,
49 fileGrowth = 10%
50 )log on(
51 name = 'testHome_log',
52 filename = 'c:\testHome_log.log',
53 size = 1mb,
54 fileGrowth = 1mb
55 ),(
56 name = 'testHome2_log',
57 filename = 'c:\testHome2_log.log',
58 size = 1mb,
59 fileGrowth = 1mb
60 )go
61 if (exists (select * from sys.objects where name = 'classes'))
62 print '存在';
63 go
64 if (exists (select * from sys.objects where object_id = object_id('student')))
65 print '存在';
66 go
67 if(object_id('student','U') is not null)
68 print '存在';
69 go
70 if (exists (select * from sys.columns where object_id = object_id('student') and name='idCard'))
71 alter table student drop column idCard
72 go
73 if(exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
74 alter table student drop column tel
75 go
76 if(exists (select * from sys.objects where name = 'classes'))
77 drop table classes
78 go
79 create table classes(
80 id int primary key identity(1,2),
81 name varchar(22) not null,
82 createDate datetime default getDate()
83 )
84 go
85 if(exists (select * from sys.objects where object_id = object_id('dtudent')))
86 drop table student
87 go
88 create table student(
89 id int identity(1,1) not null,
90 name varchar(20),
91 age int,
92 sex bit,
93 cid int
94 )go
95 alter table student add address varchar(50) not null;
96 alter table student alter column address varchar(20);
97 alter table student drop column number;
98
99 alter table student
100 add address varchar(22),
101 tel varchar(11),
102 idCard varchar(3);
103
104 if(exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
105 alter table student drop column idCard
106 go
107 if(exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
108 alter table student drop column tel
109 go
110
111 alter table studet
112 add number varchar(20) null constraint no_uk unique;
113
114 alter table student
115 add constraint pk_id primary key(id);
116
117 alter table student
118 add constraint fk_cid foreign key(cid) references classes(id)
119 go
120
121 alter table student
122 add constraint name_uk unique(name);
123
124 alter table student with nocheck
125 add constraint check_age check (age > 1);
126 alter table student
127 add constraint ck_age check (age >= 15 and age <= 50)
128
129 alter table student
130 add constraint sex_def default 1 for sex;
131
132 alter table student
133 add createDate smalldatetime null
134 constraint createDate_def default getDate() with values;
135
136 alter table student add
137 id int identity constraint id primary key,
138 number int null
139 constraint uNumber references classes(number),
140 createDate decimal(3,3)
141 constraint createDate default 2010-6-1
142 go
143
144 alter table student drop constraint no_uk;
145
146 insert into classes(name) values('1班');
147 insert into classes values('2班','2011-6-15');
148 insert into classes(name) values('3班');
149 insert into classes values('4班',default);
150
151 insert into student values('zhangsan',22,1,1);
152 insert into student values('lisi',25,0,1);
153 insert into student values('wangwu',24,1,3);
154 insert into student values('zhaoliu',23,0,3);
155 insert into student values('mazi',21,1,5);
156 insert into student values('wangmazi',28,0,5);
157 insert into student values('jason',null,0,5);
158 insert into student values(null,null,0,5);
159
160 insert into student
161 select 'bulise' name,age,sex,cid
162 from student
163 where name = 'tony';
164
165 insert into student
166 select 'jack',23,1,5 union
167 select 'tom',24,0,3 union
168 select 'wendy',25,1,3 union
169 select 'tony',26,0,5;
170
171 select * from classes;
172 select * from student;
173 select 8d,'bulise' name,age,sex,cid from student
174 where name = 'tony';
175 select *,(select max(age) from student) from student
176 where name = 'tony';
177
178 update student set name='hoho',sex=1 where id =1;
179
180 delete from student where id=1;
181
182 select * into stu from student;
183 select * into stu1 from (select * from stu) t;
184 select * from stu;
185 select * from stu1;
186
187 exec sp_help student;
188 exec sp_help classes;
189
190 declare @id char(10)
191 declare @age int
192 select @id = 22
193 set @age = 55
194 print convert(char(10),@age) + '#' + @id
195 select @age,@id
196 go
197
198 declare @name varchar(20);
199 declare @result varchar(200);
200 set @name = 'jack';
201 set @result = @name + ' say: hellw world!';
202 select @result
203 go
204
205 declare @id int, @name varchar(20);
206 set @id = 1;
207 select @name = name from student where id = @id;
208 select @name;
209 go
210
211 declare @name varchar(20);
212 select @name = 'jack';
213 select * from student where name = @name;
214 go
215
216 select @@identity;
217 select identity(int,1,1) as id into tab from student;
218 select * from tab;
219 select @@rowcount;
220 select @@cursor_rows;
221 select @@error;
222 select @@procid;
223
224 set datefirst 1;
225 select @@datefirst as '星期的第一天', datepart(dw,getDate()) as '今天是星期';
226 select @@dbts;
227 select @@lock_timeout;
228 select @@max_connections;--同时进行的最大用户链接数
229 select @@max_precision as 'Max Precision';--返回decimal和numeric数据类型所用的精度级别
230 select @@servername;
231 select @@servicename;
232 select @@spid;--当前会话进程Id
233 select @@textSize;
234 select @@version;
235
236 select @@connections;
237 select @@pack_received;
238 select @@cpu_busy;
239 select @@pack_sent;
240 select @@timeticks;
241 select @@idle;
242 select @@total_errors;
243 select @@io_busy;
244 select @@total_read;--读取磁盘次数
245 select @@packet_errors;--发生的网络数据包错误数
246 select @@total_write;--sqlserver执行的磁盘写入次数
247
248 select 1 +2;
249 select @@language;
250 select user_name();
251
252 print 1+2;
253 print @@language;
254 print user_name();
255 --print在输出值不少字符串的情况下，需要用convet转换成字符串才能正常输入，而且字符串的长度在超过8000的字符以后，后面的将不会显示。
256
257 if 2 > 3
258 print '2 > 3';
259 else
260 print '2 < 3';
261
262 if(2 > 3)
263 print '2 > 3';
264 else if(3 > 2)
265 print '3 > 2';
266 else
267 print 'other';
268
269 declare @id char(10),
270 @pid char(20),
271 @name varchar(20);
272 set @name = '广州';
273 select @id = id from ab_area where areaName = @name;
274 select @pid = pid from ab_area where id = @id;
275 print @id + '#' + @pid;
276
277 if @pid > @id
278 begin
279 print @id + '%';
280 select * from ab_area where pid like @id + '%';
281 end
282 else
283 begin
284 print @id + '%';
285 print @id + '#' + @pid;
286 select * from ab_area where pid = @pid;
287 end
288 go
289
290 declare @i int;
291 set @i = 1;
292 while(@i < 11)
293 begin
294 print @i;
295 set @i = @i + 1;
296 end
297 go
298
299 declare @i int;
300 set @i = 1;
301 while(@i < 11)
302 begin
303 if(@i < 5)
304 begin
305 set @i = @i + 1;
306 continue;
307 end
308 print @i;
309 set @i = @i + 1;
310 end
311 go
312
313 declare @i int;
314 set @i = 1;
315 while(1=1)
316 begin
317 print @i;
318 if(@i >= 5)
319 begin
320 set @i = @i + 1;
321 break;
322 end
323 set @i = @i + 1;
324 end
325 go
326
327 select *,
328 case sex
329 when 1 then '男'
330 when 0 then '女'
331 else '火星人'
332 end as '性别'
333 from student;
334
335 select areaName,'区域类型'=case
336 when areaType = '省' then areaName + areaType
337 when areaType = '市' then 'city'
338 when areaType = '区' then 'area'
339 else 'other'
340 end
341 from ab_area;
342
343 use master
344 go
345
346 --延时执行，类似于定时器、休眠等
347 waitfor delay '00:00:03';--定时三秒后执行
348 print '定时三秒后执行';
349
350 select * from student;
351
352 select all sex from student;
353 select distinct sex from student;
354 select count(*) from student;
355 select count(sex) from student;
356 select count(distinct sex) from student;
357
358 select top 3 * from student;
359 select id as 编号,name '名称',sex 性别 from student;
360 select id,name,s.id,s.name from student s;
361
362 select (age + id) col from student;
363 select s.name + '-' + c.name from classes c,student s where s.cid = c.id;
364
365 select * from student where id =2;
366 select * from student where id >7;
367 select * from student where id < 3;
368 select * from student where id<>3;
369 select * from student where id >=3
370 select * from student where id<=5;
371 select * from student where id!>3;
372 select * from student where id!<5;
373 select * from student where id > 2 and sex = 1;
374 select * from student where id =2 or sex =1;
375 select * from student where id between 2 and 5;
376 select * from student where id not between 2 and 5;
377 select * from student where name like '%a%';
378 select * from student where name like '%[a][o]%';
379 select * from student where name not like '%a%';
380 select * from student where name like 'ja%';
381 select * from student where name not like '%[j,n]%';
382 select * from student where name like '%[j,n,a]%';
383 select * from student where name like '%[^ja,as,on]%';
384 select * from student where name like '%[ja_on]%';
385 select * from student where id in (1,2);
386 select * from student where id not in (1,2);
387 select * from student where age is null;
388 select * from student where age is not null;
389 select * from student order by name;
390 select * from student order by name desc;
391 select * from student order by name asc;
392 select count(age),age from student group by age;
393 select count(*),sex from student group by sex;
394 select count(*),sex from student group by sex,age order by age;
395 select count(*),sex from student where id>2 group by sex order by sex;
396 select count(*),(sex*id) new from student where id>2 group by sex*id order by sex*id;
397 select count(*),age from student group by all age;
398 select count(*),age from student group by age having age is not null;
399 select count(*),cid,sex from student group by cid,sex having cid > 1;
400 select count(*),age from student group by age having count(age)>=2;
401 select count(*),cid,sex from student group by cid,sex having cid>1 and max(cid)>2;
402
403 select * from (
404 select id,name from student where sex =1
405 ) t where t.id > 2;
406
407 select *,(select count(*) from student where cid = classes.id) as num
408 from classes order by num;
409 select * from student where cid in(
410 select id from classes where id > 2 and id < 4
411 );
412 select * from student where cid not in(
413 select id from classes where name ='2班'
414 );
415 select * from student where exists(
416 select * from classes where id =student.cid and id=3
417 );
418 select * from student where not exists(
419 select * from classes where id = student.cid
420 );
421 select * from student where cid=5 and age > all (
422 select age from student where cid =3
423 );
424 select * from student where cid =5 and age > any (
425 select age from student where cid =3
426 );
427 select * from student where cid =5 and age > some (
428 select age from student where cid =3
429 );
430 select distinct sex from student;
431 select count(sex),count(distinct sex) from student;
432
433 select age from student
434 where age > 20 order by age compute sum(age) by age;
435
436 select id,sex,age from student
437 where age > 20 order by sex,age compute sum(age) by sex;
438
439 select age from student
440 where age > 20 order by age,id compute sum(age);
441
442 select id,age from student
443 where age > 20 order by age compute sum(age),max(id);
444
445 select count(*),sex from student group by sex with cube;
446 select count(*),age,sum(age) from student where age is not null group by age with cube;
447
448 select s.id,s.name ,cid,c.name,row_number() over(order by c.name) as number
449 from student s,classes c where cid=c.id;
450 select id,name,rank() over(order by cid) as rank from student;
451 select s.id,s.name,cid,c.name,rank() over(order by c.name) as rank
452 from student s,classes c where cid=c.id;
453 select s.id,s.name,cid,c.name,dense_rank() over(order by c.name) as dense
454 from student s,classes c where cid=cid;
455 select s.id,s.name,cid,c.name,row_number() over(partition by c.name order by s.id) as rank
456 from student s,classes c where cid=c.id;
457 select s.id,s.name,cid,c.name,rank() over(partition by c.name order by s.id) as rank
458 from student s,classes c where cid=c.id;
459 select s.id,s.name,cid,c.name,dense_rank() over (partition by c.name order by s.id) as rank
460 from student s,classes c where cid = c.id;
461 select s.id,s.name,cid,c.name,ntile(5) over(order by c.name) as ntile
462 from student s,classes c where cid=c.id;
463
464 select id,name from student where name like 'ja%'
465 union--并集
466 select id,name from student where id=4;
467
468 select * from student where name like 'ja%'
469 union all
470 select * from student;
471
472 select * from student where name like 'ja%'
473 intersect--交集
474 select * from student;
475
476 select * from student where name like 'ja%'
477 except--减集（除相同部分）
478 select * from student where name like 'jas%';
479
480 --公式表表达式（查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性地，不利于理解。那么公示表表达式可以解决这个问题）
481 --我们可以将公示表表达式（CET）视为临时结果集，在select,inset,update,delete或者create view 语句的执行范围内进行定义
482 with statNum(id,num) as(
483 select cid,count(*)
484 from student
485 where id > 0
486 group by cid
487 )
488 select id,num from statNum order by id;
489
490 with statNum(id,num) as(
491 select cid,count(*)
492 from student
493 where id > 0
494 group by cid
495 )
496 select max(id),avg(num) from statNum;
497
498 select s.id,s.name,c.id,c.name from student s,classes c where s.cid = c.id;
499 select s.id,s.name,c.id,c.name from student s left join classes c on s.cid = c.id;
500 select s.id,s.name,c.id,c.name from student s right join classes c on s.cid = c.id;
501 select s.id,s.name,c.id,c.name from student s inner join classes c on s.cid = c.id;
502 select s.id,s.name,c.id,c.name from student s join classes c on s.cid = c.id;
503 select s.id,s.name,c.id,c.name from student s cross join classes c where s.cid=c.id;--交叉链接查询，结果是一个笛卡尔乘积
504 select distinct s.* from student s,student s1 where s.id <> s1.id and s.sex = s1.sex;--自连接
505
506 select
507 max(age) max_age,
508 min(age) min_age,
509 count(age) count_age,
510 avg(age) avg_age,
511 sum(age) sum_age,
512 var(age) var_age
513 from student;
514
515 select dateAdd(day,3,getDate());--加天
516 select dateAdd(year,3,getDate());--加年
517 select dateAdd(hour,3,getDate());--加小时
518 --返回跨两个指定日期的日期边界数和时间边界数
519 select dateDiff(day,'2016-12-19',getDate());
520 select dateDiff(second,'2017-12-19 11:40:00',getDate());--相差秒数
521 select dateDiff(hour,'2017-12-19 08:00:00',getDate());--相差小时数
522 select dateName(month,getDate());--当前月份
523 select dateName(minute,getdate());--当前分钟
524 select dateName(weekday,getDate());--当前星期
525 select dateName(second,getDate());--当前秒数
526 select day(getDate());--返回当前日期天数
527 select day('2011-06-30')
528 select month(getDate())
529 select month('2017-12-19')
530 select year(getDate())
531 select year('2016-11-11')
532 select getdate();
533 select getUTCDate();
534
535 select pi();
536 select rand(100),rand(50),rand(),rand();
537 select round(rand(),3),round(rand(100),5)--精确小数位
538 --精确位数，负数表示小数点前
539 select round(123.456,2),round(254.124,-2);
540 select round(123.4657,1,2);
541
542 select col_name(object_id('student'), 1);--返回列名
543 select col_name(object_id('student'), 2);
544 --该列数据类型长度
545 select col_length('student', col_name(object_id('student'), 2));
546 --该列数据类型长度
547 select col_length('student', col_name(object_id('student'), 1));
548 --返回类型名称、类型id
549 select type_name(type_id('varchar')), type_id('varchar');
550 --返回列类型长度
551 select columnProperty(object_id('student'), 'name', 'PRECISION');
552 --返回列所在索引位置
553 select columnProperty(object_id('student'), 'sex', 'ColumnId');
554
555 select ascii('a');--字符转换ascii值
556 select ascii('A');
557 select char(97);--ascii值转换字符
558 select char(65);
559 select nchar(65);
560 select nchar(45231);
561 select nchar(32993);--unicode转换字符
562 select unicode('A'), unicode('中');--返回unicode编码值
563 select soundex('hello'), soundex('world'), soundex('word');
564 select patindex('%a', 'ta'), patindex('%ac%', 'jack'), patindex('dex%', 'dexjack');--匹配字符索引
565 select 'a' + space(2) + 'b', 'c' + space(5) + 'd';--输出空格
566 select charIndex('o', 'hello world');--查找索引
567 select charIndex('o', 'hello world', 6);--查找索引
568 select quoteName('abc[]def'), quoteName('123]45');
569 --精确数字
570 select str(123.456, 2), str(123.456, 3), str(123.456, 4);
571 select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
572 select difference('hello', 'helloWorld');--比较字符串相同
573 select difference('hello', 'world');
574 select difference('hello', 'llo');
575 select difference('hello', 'hel');
576 select difference('hello', 'hello');
577 select replace('abcedef', 'e', 'E');--替换字符串
578 select stuff('hello world', 3, 4, 'ABC');--指定位置替换字符串
579 select replicate('abc#', 3);--重复字符串
580 select subString('abc', 1, 1), subString('abc', 1, 2), subString('hello Wrold', 7, 5);--截取字符串
581 select len('abc');--返回长度
582 select reverse('sqlServer');--反转字符串
583
584 select left('leftString', 4);--取左边字符串
585 select left('leftString', 7);
586 select right('leftString', 6);--取右边字符串
587 select right('leftString', 3);
588 select lower('aBc'), lower('ABC');--小写
589 select upper('aBc'), upper('abc');--大写
590 --去掉左边空格
591 select ltrim(' abc'), ltrim('# abc#'), ltrim(' abc');
592 --去掉右边空格
593 select rtrim(' abc '), rtrim('# abc# '), rtrim('abc');
594
595 select current_user;
596 select user;
597 select user_id(), user_id('dbo'), user_id('public'), user_id('guest');
598 select user_name(), user_name(1), user_name(0), user_name(2);
599 select session_user;
600 select suser_id('sa');
601 select suser_sid(), suser_sid('sa'), suser_sid('sysadmin'), suser_sid('serveradmin');
602 select is_member('dbo'), is_member('public');
603 select suser_name(), suser_name(1), suser_name(2), suser_name(3);
604 select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
605 select is_srvRoleMember('sysadmin'), is_srvRoleMember('serveradmin');
606 select permissions(object_id('student'));
607 select system_user;
608 select schema_id(), schema_id('dbo'), schema_id('guest');
609 select schema_name(), schema_name(1), schema_name(2), schema_name(3);
610
611 select app_name();--当前会话的应用程序名称
612 select cast(2011 as datetime), cast('10' as money), cast('0' as varbinary);--类型转换
613 select convert(datetime, '2011');--类型转换
614 select coalesce(null, 'a'), coalesce('123', 'a');--返回其参数中第一个非空表达式
615 select collationProperty('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
616 select current_timestamp;--当前时间戳
617 select current_user;
618 select isDate(getDate()), isDate('abc'), isNumeric(1), isNumeric('a');
619 select dataLength('abc');
620 select host_id();
621 select host_name();
622 select db_name();
623 select ident_current('student'), ident_current('classes');--返回主键id的最大值
624 select ident_incr('student'), ident_incr('classes');--id的增量值
625 select ident_seed('student'), ident_seed('classes');
626 select @@identity;--最后一次自增的值
627 select identity(int, 1, 1) as id into tab from student;--将studeng表的烈属，以/1自增形式创建一个tab
628 select * from tab;
629 select @@rowcount;--影响行数
630 select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
631 select @@error;--T-SQL的错误号
632 select @@procid;
633
634 set datefirst 7;--设置每周的第一天，表示周日
635 select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
636 select @@dbts;--返回当前数据库唯一时间戳
637 set language 'Italian';
638 select @@langId as 'Language ID';--返回语言id
639 select @@language as 'Language Name';--返回当前语言名称
640 select @@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）
641 select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
642 select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
643 select @@SERVERNAME;--SQL Server 的本地服务器的名称
644 select @@SERVICENAME;--服务名
645 select @@SPID;--当前会话进程id
646 select @@textSize;
647 select @@version;--当前数据库版本信息
648
649 select @@CONNECTIONS;--连接数
650 select @@PACK_RECEIVED;
651 select @@CPU_BUSY;
652 select @@PACK_SENT;
653 select @@TIMETICKS;
654 select @@IDLE;
655 select @@TOTAL_ERRORS;
656 select @@IO_BUSY;
657 select @@TOTAL_READ;--读取磁盘次数
658 select @@PACKET_ERRORS;--发生的网络数据包错误数
659 select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
660 select patIndex('%soft%', 'microsoft SqlServer');
661 select patIndex('soft%', 'software SqlServer');
662 select patIndex('%soft', 'SqlServer microsoft');
663 select patIndex('%so_gr%', 'Jsonisprogram');
664
665 --查询所有已创建函数
666 select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
667 and type in('fn', 'if', 'tf');
668 --创建函数
669 if (object_id('fun_add','fn') is not null)
670 drop function fun_add
671 go
672 create function fun_add(@num1 int,@num2 int)
673 returns int
674 with execute as caller
675 as
676 begin
677 declare @result int;
678 if(@num1 is null)
679 set @num1 =0;
680 if(@num2 is null)
681 set @num2 = 0;
682 set @result = @num1 + @num2;
683 return @result;
684 end
685 go
686 --调用函数
687 select dbo.fun_add(id, age) from student;
688 --自定义函数，字符串连接
689 if(object_id('fun_append','fn') is not null)
690 drop function fun_append
691 go
692 create function fun_append(@args nvarchar(1024),@args2 nvarchar(1024))
693 returns nvarchar(2048)
694 as
695 begin
696 return @args + @args2;
697 end
698 go
699 select dbo.fun_append(name,'abc') from student;
700 --修改函数
701 alter function fun_append(@args nvarchar(1024),@args2 nvarchar(1024))
702 returns nvarchar(1024)
703 as
704 begin
705 declare @result varchar(1024);
706 --coalesce返回第一个部位null的值
707 set @args = coalesce(@args,'');
708 set @args2 = coalesce(@args,'');
709 set @result = @args + @args;
710 return @result;
711 end
712 go
713 select dbo.fun_append(name,'#abc') from student;
714 --返回table对象函数
715 select name, object_id, type from sys.objects where type in ('fn', 'if', 'tf') or type like '%f%';
716
717 if (exists (select * from sys.objects where type in ('fn', 'if', 'tf') and name = 'fun_find_stuRecord'))
718 drop function fun_find_stuRecord
719 go
720 create function fun_find_stuRecord(@id int)
721 returns table
722 as
723 return (select * from student where id = @id);
724 go
725
726 select * from dbo.fun_find_stuRecord(2);
727
728 --联合索引
729 if (exists (select * from sys.indexes where name = 'idx_uqe_clu_stu_name_age'))
730 drop index student.idx_uqe_clu_stu_name_age
731 go
732 create unique clustered index idx_uqe_clu_stu_name_age
733 on student(name, age);
734
735 if (exists (select * from sys.indexes where name = 'idx_cid'))
736 drop index student.idx_cid
737 go
738
739 if (exists (select * from sys.indexes where name = 'idx_cid'))
740 drop index student.idx_cid
741 go
742
743 --非聚集索引
744 create nonclustered index idx_cid
745 on
746 student (cid)
747 with fillFactor = 30; --填充因子
748
749 --聚集索引
750 if (exists (select * from sys.indexes where name = 'idx_sex'))
751 drop index student.idx_sex
752 go
753 create clustered index idx_sex
754 on
755 student(sex);
756
757 --聚集索引
758 if (exists (select * from sys.indexes where name = 'idx_name'))
759 drop index student.idx_name
760 go
761 create unique index idx_name
762 on
763 student(name);
764 --创建视图
765 if(exists (select * from sys.objects where name = 'v_stu'))
766 drop view v_stu
767 go
768 create view v_stu
769 as
770 select id,name,age,sex from student;
771 go
772 --修改视图
773 alter view v_stu
774 as
775 select id,name,sex from student;
776 go
777 alter view v_stu(编号,名称,性别)
778 as
779 select id,name,sex from student
780 go
781 select * from v_stu;
782 select * from information_schema.views;
783 --加密视图
784 if(exists (select * from sys.objects where name='v_student_info'))
785 drop view v_student_info
786 go
787 create view v_student_info
788 with encryption --加密
789 as
790 select id,name,age from student
791 go
792 select * from information_schema.views
793 where table_name like 'v_stu';
794
795 exec sp_databases;--查看你数据库
796 exec sp_tables; --查看表
797 exec sp_columns student;--查看列
798 exec sp_helpIndex student;--查看索引
799 exec sp_helpConstraint student;--约束
800 exec sp_stored_procedures;
801 exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
802 exec sp_rename student, stuInfo;--修改表、索引、列的名称
803 exec sp_renamedb myTempDB, myDB;--更改数据库名称
804 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
805 exec sp_helpdb;--数据库帮助，查询数据库信息
806 exec sp_helpdb master;
807
808 --表重命名
809 exec sp_rename 'stu', 'stud';
810 select * from stud;
811 --列重命名
812 exec sp_rename 'stud.name', 'sName', 'column';
813 exec sp_help 'stud';
814 --重命名索引
815 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
816 exec sp_help 'student';
817
818 --查询所有存储过程
819 select * from sys.objects where type = 'P';
820 select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
821 --创建存储过程
822 if (exists (select * from sys.objects where name='proc_get_student'))
823 drop proc proc_get_student
824 go
825 create proc proc_get_student
826 as
827 select * from student;
828 go
829 --调用、执行存储过程
830 exec proc_get_student
831 --修改存储过程
832 alter proc proc_get_student
833 as
834 select * from student;
835 go
836 --带参存储过程
837 if(object_id('proc_find_stu','p') is not null)
838 drop proc proc_find_stu
839 go
840 create proc proc_find_stu(@startId int,@endId int)
841 as
842 select * from student where id between @startId and @endId
843 go
844 exec proc_find_stu 2,4;
845 go
846 --带通配符参数存储过程
847 if(object_id('proc_findStudentByName','p') is not null)
848 drop proc proc_findStudentByName
849 go
850 create proc proc_findStudentByName(@name varchar(20) = '%j%',@nextName varchar(20) = '%')
851 as
852 select * from student where name like @name and name like @nextName;
853 go
854 exec proc_findStudentByName;
855 exec proc_findStudentByName '%o%','t%'
856 go
857 --带出入参数存储过程
858 if(object_id('proc_getStudentRecord','p') is not null)
859 drop proc proc_getStudentRecord
860 go
861 create proc proc_getStudentRecord(
862 @id int,--默认输入参数
863 @name varchar(20) out,--输出参数
864 @age varchar(20) output--输入输出参数
865 )
866 as
867 select @name = name, @age = age from student where id = @id and sex =@age;
868 go
869 declare @id int,
870 @name varchar(20),
871 @temp varchar(20);
872 set @id = 7;
873 set @temp =1;
874 exec proc_getStudentRecord @id,@name out, @temp output;
875 select @name,@temp;
876 print @name + '#' + @temp;
877 --不缓存存储过程(with recompile)
878 if(object_id('proc_temp','p') is not null)
879 drop proc proc_temp
880 go
881 create proc proc_temp
882 with recompile
883 as
884 select * from student;
885 go
886 exec proc_temp;
887 --加密存储过程（with encryption）
888 if(object_id('proc_temp_encryption','p') is not null)
889 drop proc proc_temp_encryption
890 go
891 create proc proc_temp_encryption
892 with encryption
893 as
894 select * from student;
895 go
896 exec proc_temp_encryption;
897 exec sp_helptext 'proc_temp';
898 exec sp_helptext 'proc_temp+encryption';
899 --带游标参数存储过程
900 if(object_id('proc_cursor','p') is not null)
901 drop proc proc_cursor
902 go
903 create proc proc_cursor
904 @cur cursor varying output
905 as
906 set @cur = cursor forward_only static for
907 select id,name,age from student;
908 open @cur;
909 go
910 --调用
911 declare @exec_cur cursor;
912 declare @id int,
913 @name varchar(20),
914 @age int
915 exec proc_cursor @cur = @exec_cur output;--调用存储过程
916 fetch next from @exec_cur into @id,@name,@age;
917 while (@@fetch_status = 0)
918 begin
919 fetch nex from @exec_cur into @id,@name,@age;
920 print 'id:'+convert(varchar,@id) + ',name:'+@name+',age:'+convert(char,@age);
921 end
922 close @exec_cur;
923 deallocate @exec_cur;--删除游标
924
925 --存储过程,row_number完成分页
926 if(object_id('pro_page','p') is not null)
927 drop proc proc_cursor
928 go
929 create proc pro_page
930 @startIndex int,
931 @endIndex int
932 as
933 select count(*) from product;
934 select * from(
935 select row_number() over(order by pid) as rowId,* from product
936 )temp
937 where temp.rowId between @startIndex and @endIndex
938 go
939 --drop proc pro_page
940 exec pro_page 1,4
941 --分页村粗过程
942 if(object_id('pro_page','p') is not null)
943 drop proc pro_stu
944 go
945 create procedure pro_stu(
946 @pageIndex int,
947 @pageSize int
948 )
949 as
950 declare @startRow int,@endRow int
951 set @startRow = (@pageIndex - 1) * @pageSize + 1
952 set @endRow = @startRow + @pageSize -1
953 select * from(
954 select *,row_number() over (order by id asc) as number from student
955 )t
956 where t.number between @startRow and @endRow;
957
958 exec pro_stu 2,2;
959
960 --开始事务
961 begin transaction tran_bank;
962 declare @tran_error int;
963 set @tran_error = 0;
964 begin try
965 update bank set totalMoney = totalMoney - 10000 where userName = 'jack';
966 set @tran_error = @tran_error + @@error;
967 update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
968 set @tran_error = @tran_error + @@error;
969 end try
970 begin catch
971 print '出现异常，错误编号：'+convert(varchar,error_number())+',错误信息：'+error_message();
972 set @tran_error = @tran_error + 1;
973 end catch
974 if(@tran_error > 0)
975 begin
976 rollback tran;
977 print '转账失败，取消交易';
978 end
979 else
980 begin
981 commit tran;
982 print '转账成功';
983 end
984 go
985 --错误消息存储过程
986 if(object_id('proc_error_info') is not null)
987 drop procedure proc_error_info
988 go
989 create proc proc_error_info
990 as
991 select
992 error_number() '错误编号',
993 error_message() '错误消息',
994 error_severity() '严重性',
995 error_state() '状态号',
996 error_line() '错误行号',
997 error_procedure() '错误对象（存储过程或触发器）名称';
998 go
999 --简单try catch 示例
1000 begin try
1001 select 1/0;
1002 end try
1003 begin catch
1004 exec proc_error_info;--调用错误消息存储过程
1005 end catch
1006 go
1007 --简单try catch 示例，无法处理错误
1008 begin try
1009 select * * from student;
1010 end try
1011 begin catch
1012 exec proc_error_info
1013 end catch
1014 go
1015 --简单try catch示例，不处理错误(不存在的表对象)
1016 begin try
1017 select * from st;
1018 end try
1019 begin catch
1020 exec proc_error_info
1021 end catch
1022 go
1023 --异常处理，能处理存储过程（触发器）中（不存在表对象）的错误信息
1024 if(object_id('proc_select') is not null)
1025 drop procedure proc_select
1026 go
1027 create proc proc_select
1028 as
1029 select * from st;
1030 go
1031 begin try
1032 exec proc_select;
1033 end try
1034 begin catch
1035 exec proc_error_info;
1036 end catch
1037 go
1038 --无法提交的事务
1039 if(object_id('temp_tab','u') is not null)
1040 drop table temp_tab
1041 go
1042 create table temp_tab(
1043 id int primary key identity(100000,1),
1044 name varchar(200)
1045 )
1046 go
1047 begin try
1048 begin tran;
1049 --没有createTime字段
1050 alter table temp_tab drop column createTime;
1051 commit tran;
1052 end try
1053 begin catch
1054 exec proc_error_info;--显示异常信息
1055 if(xact_state() = -1)
1056 begin
1057 print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
1058 +'会话无法体骄傲事务或回滚到保存点；它只能请求完成回滚事务。'
1059 +'会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
1060 +'事务回滚之后，会话便可执行读写操作并可开始新的事务。';
1061 end
1062 else if(xact_state() = 0)
1063 begin
1064 print '会话没有活动事务。';
1065 end
1066 else if(xact_state() = 1)
1067 begin
1068 print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提及事务。';
1069 end
1070 end catch
1071 go
1072 ---异常、错误信息表
1073 if (object_id('errorLog', 'U') is not null)
1074 drop table errorLog
1075 go
1076 create table errorLog(
1077 errorLogID int primary key identity(100, 1), --ErrorLog 行的主键。
1078 errorTime datetime default getDate(), --发生错误的日期和时间。
1079 userName sysname default current_user, --执行发生错误的批处理的用户。
1080 errorNumber int, --发生的错误的错误号。
1081 errorSeverity int, --发生的错误的严重性。
1082 errorState int, --发生的错误的状态号。
1083 errorProcedure nvarchar(126), --发生错误的存储过程或触发器的名称。
1084 errorLine int, --发生错误的行号。
1085 errorMessage nvarchar(4000)
1086 )
1087 go
1088 --
1089 --存储过程：添加异常日志信息
1090 if (object_id('proc_add_exception_log', 'p') is not null)
1091 drop proc proc_add_exception_log
1092 go
1093 create proc proc_add_exception_log(@logId int = 0 output)
1094 as
1095 begin
1096 set nocount on;
1097 set @logId = 0;
1098 begin try
1099 if (error_number() is null)
1100 return;
1101
1102 if (xact_state() = -1)
1103 begin
1104 print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
1105 + '会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。'
1106 + '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
1107 + '事务回滚之后，会话便可执行读写操作并可开始新的事务。';
1108 end
1109 else if (xact_state() = 0)
1110 begin
1111 print '会话没有活动事务。';
1112 end
1113 else if (xact_state() = 1)
1114 begin
1115 print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提交事务。';
1116 end
1117
1118 --添加日志信息
1119 insert into errorLog values(getDate(),
1120 current_user, error_number(),
1121 error_severity(), error_state(),
1122 error_procedure(),
1123 error_line(), error_message());
1124 --设置自增值
1125 select @logId = @@identity;
1126 end try
1127 begin catch
1128 print '添加异常日志信息出现错误';
1129 exec proc_error_info;--显示错误信息
1130 return -1;
1131 end catch
1132 end
1133 go
1134 --
1135 ---处理异常信息示例
1136 declare @id int;
1137 begin try
1138 begin tran;
1139 --删除带有外键的记录信息
1140 delete classes where id = 1;
1141 commit tran;
1142 end try
1143 begin catch
1144 exec proc_error_info;--显示错误信息
1145 if (xact_state() <> 0)
1146 begin
1147 rollback tran;
1148 end
1149 exec proc_add_exception_log @id output
1150 end catch
1151 select * from errorLog where errorLogID = @id;
1152 go
1153 --创建一个游标
1154 declare cursor_stu cursor scroll for
1155 select id, name, age from student;
1156 --打开游标
1157 open cursor_stu;
1158 --存储读取的值
1159 declare @id int,
1160 @name nvarchar(20),
1161 @age varchar(20);
1162 --读取第一条记录
1163 fetch first from cursor_stu into @id, @name, @age;
1164 --循环读取游标记录
1165 print '读取的数据如下：';
1166 --全局变量
1167 while (@@fetch_status = 0)
1168 begin
1169 print '编号：' + convert(char(5), @id) + ', 名称：' + @name + ', 类型：' + @age;
1170 --继续读取下一条记录
1171 fetch next from cursor_stu into @id, @name, @age;
1172 end
1173 --关闭游标
1174 close area_cursor;
1175
1176 --删除游标
1177 --deallocate area_cursor;
1178
1179 --创建insert插入类型触发器
1180 if (object_id('tgr_classes_insert', 'tr') is not null)
1181 drop trigger tgr_classes_insert
1182 go
1183 create trigger tgr_classes_insert
1184 on classes
1185 for insert --插入触发
1186 as
1187 --定义变量
1188 declare @id int, @name varchar(20), @temp int;
1189 --在inserted表中查询已经插入记录信息
1190 select @id = id, @name = name from inserted;
1191 set @name = @name + convert(varchar, @id);
1192 set @temp = @id / 2;
1193 insert into student values(@name, 18 + @id, @temp, @id);
1194 print '添加学生成功！';
1195 go
1196 --插入数据
1197 insert into classes values('5班', getDate());
1198 --查询数据
1199 select * from classes;
1200 select * from student order by id;
1201
1202 --delete删除类型触发器
1203 if (object_id('tgr_classes_delete', 'TR') is not null)
1204 drop trigger tgr_classes_delete
1205 go
1206 create trigger tgr_classes_delete
1207 on classes
1208 for delete --删除触发
1209 as
1210 print '备份数据中……';
1211 if (object_id('classesBackup', 'U') is not null)
1212 --存在classesBackup，直接插入数据
1213 insert into classesBackup select name, createDate from deleted;
1214 else
1215 --不存在classesBackup创建再插入
1216 select * into classesBackup from deleted;
1217 print '备份数据成功！';
1218 go
1219 --
1220 --不显示影响行数
1221 --set nocount on;
1222 delete classes where name = '5班';
1223 --查询数据
1224 select * from classes;
1225 select * from classesBackup;
1226
1227 --update更新类型触发器
1228 if (object_id('tgr_classes_update', 'TR') is not null)
1229 drop trigger tgr_classes_update
1230 go
1231 create trigger tgr_classes_update
1232 on classes
1233 for update
1234 as
1235 declare @oldName varchar(20), @newName varchar(20);
1236 --更新前的数据
1237 select @oldName = name from deleted;
1238 if (exists (select * from student where name like '%'+ @oldName + '%'))
1239 begin
1240 --更新后的数据
1241 select @newName = name from inserted;
1242 update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
1243 print '级联修改数据成功！';
1244 end
1245 else
1246 print '无需修改student表！';
1247 go
1248 --查询数据
1249 select * from student order by id;
1250 select * from classes;
1251 update classes set name = '五班' where name = '5班';
1252
1253 if (object_id('tgr_classes_update_column', 'TR') is not null)
1254 drop trigger tgr_classes_update_column
1255 go
1256 create trigger tgr_classes_update_column
1257 on classes
1258 for update
1259 as
1260 --列级触发器：是否更新了班级创建时间
1261 if (update(createDate))
1262 begin
1263 raisError('系统提示：班级创建时间不能修改！', 16, 11);
1264 rollback tran;
1265 end
1266 go
1267 --测试
1268 select * from student order by id;
1269 select * from classes;
1270 update classes set createDate = getDate() where id = 3;
1271 update classes set name = '四班' where id = 7;
1272
1273 if (object_id('tgr_classes_inteadOf', 'TR') is not null)
1274 drop trigger tgr_classes_inteadOf
1275 go
1276 create trigger tgr_classes_inteadOf
1277 on classes
1278 instead of delete/*, update, insert*/
1279 as
1280 declare @id int, @name varchar(20);
1281 --查询被删除的信息，病赋值
1282 select @id = id, @name = name from deleted;
1283 print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
1284 --先删除student的信息
1285 delete student where cid = @id;
1286 --再删除classes的信息
1287 delete classes where id = @id;
1288 print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
1289 go
1290 --test
1291 select * from student order by id;
1292 select * from classes;
1293 delete classes where id = 7;
1294
1295 if (object_id('tgr_message', 'TR') is not null)
1296 drop trigger tgr_message
1297 go
1298 create trigger tgr_message
1299 on student
1300 after insert, update
1301 as raisError('tgr_message触发器被触发', 16, 10);
1302 go
1303 --test
1304 insert into student values('lily', 22, 1, 7);
1305 update student set sex = 0 where name = 'lucy';
1306 select * from student order by id;
1307
1308 alter trigger tgr_message
1309 on student
1310 after delete
1311 as raisError('tgr_message触发器被触发', 16, 10);
1312 go
1313 --test
1314 delete from student where name = 'lucy';
1315
1316 --禁用触发器
1317 disable trigger tgr_message on student;
1318 --启用触发器
1319 enable trigger tgr_message on student;
1320
1321 --查询已存在的触发器
1322 select * from sys.triggers;
1323 select * from sys.objects where type = 'TR';
1324
1325 --查看触发器触发事件
1326 select te.* from sys.trigger_events te join sys.triggers t
1327 on t.object_id = te.object_id
1328 where t.parent_class = 0 and t.name = 'tgr_valid_data';
1329
1330 --查看创建触发器语句
1331 exec sp_helptext 'tgr_message';
1332
1333 if ((object_id('tgr_valid_data', 'TR') is not null))
1334 drop trigger tgr_valid_data
1335 go
1336 create trigger tgr_valid_data
1337 on student
1338 after insert
1339 as
1340 declare @age int,
1341 @name varchar(20);
1342 select @name = s.name, @age = s.age from inserted s;
1343 if (@age < 18)
1344 begin
1345 raisError('插入新数据的age有问题', 16, 1);
1346 rollback tran;
1347 end
1348 go
1349 --test
1350 insert into student values('forest', 2, 0, 7);
1351 insert into student values('forest', 22, 0, 7);
1352 select * from student order by id;
1353
1354 if (object_id('log', 'U') is not null)
1355 drop table log
1356 go
1357 create table log(
1358 id int identity(1, 1) primary key,
1359 action varchar(20),
1360 createDate datetime default getDate()
1361 )
1362 go
1363 if (exists (select * from sys.objects where name = 'tgr_student_log'))
1364 drop trigger tgr_student_log
1365 go
1366 create trigger tgr_student_log
1367 on student
1368 after insert, update, delete
1369 as
1370 if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
1371 begin
1372 insert into log(action) values('updated');
1373 end
1374 else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
1375 begin
1376 insert into log(action) values('inserted');
1377 end
1378 else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
1379 begin
1380 insert into log(action) values('deleted');
1381 end
1382 go
1383 --test
1384 insert into student values('king', 22, 1, 7);
1385 update student set sex = 0 where name = 'king';
1386 delete student where name = 'king';
1387 select * from log;
1388 select * from student order by id;
