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;
