SQLServerT-SQL高级查询
高级查询在数据库中用得是最频繁的，也是应用最广泛的。?基本常用查询--selectselect*fromstudent;--all查询所有selectallsexfromstudent;--distinct过滤重复selectdistinctsexfromstudent;--count统计selectcount(*)fromstudent;selectcount(sex)fromstudent;selectcount(distinctsex)fromstudent;--top取前N条记录selecttop3*fromstudent;--aliascolumnname列重命名selectidas编号,name'名称',sex性别fromstudent;--aliastablename表重命名selectid,name,s.id,s.namefromstudents;--column列运算select(age+id)colfromstudent;selects.name+'-'+c.namefromclassesc,studentswheres.cid=c.id;--where条件select*fromstudentwhereid=2;select*fromstudentwhereid>7;select*fromstudentwhereid<3;select*fromstudentwhereid<>3;select*fromstudentwhereid>=3;select*fromstudentwhereid<=5;select*fromstudentwhereid!>3;select*fromstudentwhereid!<5;--and并且select*fromstudentwhereid>2andsex=1;
--or或者select*fromstudentwhereid=2orsex=1;--between...and...相当于并且select*fromstudentwhereidbetween2and5;select*fromstudentwhereidnotbetween2and5;--like模糊查询select*fromstudentwherenamelikeselect*fromstudentwherenamelike
'%a%';'%[a][o]%';'%a%';
select*fromstudentwherenamenotlike
select*fromstudentwherenamelike'ja%';select*fromstudentwherenamenotlike'%[j,n]%';select*fromstudentwherenamelike'%[j,n,a]%';select*fromstudentwherenamelike'%[^ja,as,on]%';select*fromstudentwherenamelike'%[ja_on]%';--in子查询select*fromstudentwhereidin(1,2);--notin不在其中select*fromstudentwhereidnotin(1,2);--isnull是空select*fromstudentwhereageisnull;--isnotnull不为空select*fromstudentwhereageisnotnull;--orderby排序select*fromstudentorderbyname;select*fromstudentorderbynamedesc;select*fromstudentorderbynameasc;--groupby分组按照年龄进行分组统计selectcount(age),agefromstudentgroupbyage;按照性别进行分组统计selectcount(*),sexfromstudentgroupbysex;按照年龄和性别组合分组统计，并排序selectcount(*),sexfromstudentgroupbysex,ageorderbyage;按照性别分组，并且是id大于2的记录最后按照性别排序selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;
查询id大于2的数据，并完成运算后的结果进行分组和排序selectcount(*),(sex*id)newfromstudentwhereid>2groupbysex*idorderbysex*id;--groupbyall所有分组按照年龄分组，是所有的年龄selectcount(*),agefromstudentgroupbyallage;--having分组过滤条件按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息selectcount(*),agefromstudentgroupbyagehavingageisnotnull;按照年龄和cid组合分组，过滤条件是cid大于1的记录selectcount(*),cid,sexfromstudentgroupbycid,sexhavingcid>1;按照年龄分组，过滤条件是分组后的记录条数大于等于2selectcount(*),agefromstudentgroupbyagehavingcount(age)>=2;按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2selectcount(*),cid,sexfromstudentgroupbycid,sexhavingcid>1andmax(cid)>2;?嵌套子查询子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。
#from(select…table)示例将一个table的查询结果当做一个新表进行查询select*from(selectid,namefromstudentwheresex=1)twheret.id>2;上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句：1、包含常规选择列表组件的常规select查询2、包含一个或多个表或视图名称的常规from语句3、可选的where子句
4、可选的groupby子句5、可选的having子句
#示例查询班级信息，统计班级学生人生select*,(selectcount(*)fromstudentwherecid=classes.id)asnumfromclassesorderbynum;
#in,notin子句查询示例查询班级id大于小于的这些班级的学生信息select*fromstudentwherecidin(selectidfromclasseswhereid>2andid<4);查询不是班的学生信息select*fromstudentwherecidnotin(selectidfromclasseswherename='2班')in、notin后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id；
#exists和notexists子句查询示例查询存在班级id为的学生信息select*fromstudentwhereexists(select*fromclasseswhereid=student.cidandid=3);查询没有分配班级的学生信息select*fromstudentwherenotexists(select*fromclasseswhereid=student.cid);exists和notexists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id；
#some、any、all子句查询示例查询班级的学生年龄大于班级的学生的年龄的信息select*fromstudentwherecid=5andage>all(selectagefromstudentwherecid=3);select*fromstudentwherecid=5andage>any(selectagefromstudentwherecid=3);select*fromstudentwherecid=5andage>some(selectagefromstudentwherecid=3);
?聚合查询1、distinct去掉重复数据selectdistinctsexfromstudent;selectcount(sex),count(distinctsex)fromstudent;
2、compute和computeby汇总查询对年龄大于的进行汇总selectagefromstudentwhereage>20orderbyagecomputesum(age)byage;对年龄大于的按照性别进行分组汇总年龄信息selectid,sex,agefromstudentwhereage>20orderbysex,agecomputesum(age)bysex;按照年龄分组汇总selectagefromstudentwhereage>20orderbyage,idcomputesum(age);按照年龄分组，年龄汇总，id找最大值selectid,agefromstudentwhereage>20orderbyagecomputesum(age),max(id);compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下：a、可选by关键字。它是每一列计算指定的行聚合
b、行聚合函数名称。包括sum、avg、min、max、count等c、要对其执行聚合函数的列computeby适合做先分组后汇总的业务。computeby后面的列一定要是orderby中出现的列。
3、cube汇总cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。selectcount(*),sexfromstudentgroupbysexwithcube;selectcount(*),age,sum(age)fromstudentwhereageisnotnullgroupbyagewithcube;cube要结合groupby语句完成分组汇总
?排序函数排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如：1、对某张表进行排序，序号需要递增不重复的2、对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的3、在某些排序的情况下，需要跳空序号，虽然是并列基本语法排序函数over([分组语句]排序子句[desc][asc])排序子句orderby列名,列名分组子句partitionby分组列,分组列
#row_number函数根据排序子句给出递增连续序号按照名称排序的顺序递增selects.id,s.name,cid,c.name,row_number()over(orderbyc.name)asnumberfromstudents,classescwherecid=c.id;
#rank函数函数
根据排序子句给出递增的序号，但是存在并列并且跳空顺序递增selectid,name,rank()over(orderbycid)asrankfromstudent;跳过相同递增selects.id,s.name,cid,c.name,rank()over(orderbyc.name)asrankfromstudents,classescwherecid=c.id;
#dense_rank函数根据排序子句给出递增的序号，但是存在并列不跳空不跳过，直接递增selects.id,s.name,cid,c.name,dense_rank()over(orderbyc.name)asdensefromstudents,classescwherecid=c.id;
#partitionby分组子句可以完成对分组的数据进行增加排序，partitionby可以与以上三个函数联合使用。利用partitionby按照班级名称分组，学生id排序selects.id,s.name,cid,c.name,row_number()over(partitionbyc.nameorderbys.id)asrankfromstudents,classescwherecid=c.id;selects.id,s.name,cid,c.name,rank()over(partitionbyc.nameorderbys.id)asrankfromstudents,classescwherecid=c.id;selects.id,s.name,cid,c.name,dense_rank()over(partitionbyc.nameorderbys.id)asrankfromstudents,classescwherecid=c.id;
#ntile平均排序函数将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。selects.id,s.name,cid,c.name,ntile(5)over(orderbyc.name)asntilefromstudents,classescwherecid=c.id;
?集合运算操作两组查询结果，进行交集、并集、减集运算1、union和unionall进行并集运算--union并集、不重复selectid,namefromstudentwherenamelike'ja%'unionselectid,namefromstudentwhereid=4;--并集、重复select*fromstudentwherenamelike'ja%'unionallselect*fromstudent;
2、intersect进行交集运算--交集（相同部分）select*fromstudentwherenamelike'ja%'intersectselect*fromstudent;
3、except进行减集运算--减集（除相同部分）select*fromstudentwherenamelike'ja%'exceptselect*fromstudentwherenamelike'jas%';
?公式表表达式查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是createview语句的执行范围内进行定义。--表达式withstatNum(id,num)as(selectcid,count(*)fromstudent
whereid>0groupbycid)selectid,numfromstatNumorderbyid;withstatNum(id,num)as(selectcid,count(*)fromstudentwhereid>0groupbycid)selectmax(id),avg(num)fromstatNum;
?连接查询1、简化连接查询--简化联接查询selects.id,s.name,c.id,c.namefromstudents,classescwheres.cid=c.id;
2、leftjoin左连接--左连接selects.id,s.name,c.id,c.namefromstudentsleftjoinclassescons.cid=c.id;
3、rightjoin右连接--右连接selects.id,s.name,c.id,c.namefromstudentsrightjoinclassescons.cid=c.id;
4、innerjoin内连接--内连接selects.id,s.name,c.id,c.namefromstudentsinnerjoinclassescons.cid=c.id;--inner可以省略
selects.id,s.name,c.id,c.namefromstudentsjoinclassescons.cid=c.id;
5、crossjoin交叉连接--交叉联接查询，结果是一个笛卡儿乘积selects.id,s.name,c.id,c.namefromstudentscrossjoinclassesc--wheres.cid=c.id;
6、自连接（同一张表进行连接查询）--自连接selectdistincts.*fromstudents,students1wheres.id<>s1.idands.sex=s1.sex;
?函数1、聚合函数max最大值、min最小值、count统计、avg平均值、sum求和、var求方差selectmax(age)max_age,min(age)min_age,count(age)count_age,avg(age)avg_age,sum(age)sum_age,var(age)var_agefromstudent;
2、日期时间函数selectdateAdd(day,3,getDate());--加天selectdateAdd(year,3,getDate());--加年selectdateAdd(hour,3,getDate());--加小时--返回跨两个指定日期的日期边界数和时间边界数selectdateDiff(day,'2011-06-20',getDate());--相差秒数selectdateDiff(second,'2011-06-2211:00:00',getDate());--相差小时数selectdateDiff(hour,'2011-06-2210:00:00',getDate());selectdateName(month,getDate());--当前月份
selectdateName(minute,getDate());--当前分钟selectdateName(weekday,getDate());--当前星期selectdatePart(month,getDate());--当前月份selectdatePart(weekday,getDate());--当前星期selectdatePart(second,getDate());--当前秒数selectday(getDate());--返回当前日期天数selectday('2011-06-30');--返回当前日期天数selectmonth(getDate());--返回当前日期月份selectmonth('2011-11-10');selectyear(getDate());--返回当前日期年份selectyear('2010-11-10');selectgetDate();--当前系统日期selectgetUTCDate();--utc日期
3、数学函数selectpi();--PI函数selectrand(100),rand(50),rand(),rand();--随机数selectround(rand(),3),round(rand(100),5);--精确小数位--精确位数，负数表示小数点前selectround(123.456,2),round(254.124,-2);selectround(123.4567,1,2);
4、元数据selectcol_name(object_id('student'),1);--返回列名selectcol_name(object_id('student'),2);--该列数据类型长度selectcol_length('student',col_name(object_id('student'),2));--该列数据类型长度selectcol_length('student',col_name(object_id('student'),1));--返回类型名称、类型idselecttype_name(type_id('varchar')),type_id('varchar');--返回列类型长度selectcolumnProperty(object_id('student'),'name','PRECISION');--返回列所在索引位置selectcolumnProperty(object_id('student'),'sex','ColumnId');
5、字符串函数selectascii('a');--字符转换ascii值selectascii('A');
selectchar(97);--ascii值转换字符selectchar(65);selectnchar(65);selectnchar(45231);selectnchar(32993);--unicode转换字符selectunicode('A'),unicode('中');--返回unicode编码值selectsoundex('hello'),soundex('world'),soundex('word');selectpatindex('%a','ta'),patindex('%ac%','jack'),patindex('dex%','dexjack');--匹配字符索引select'a'+space(2)+'b','c'+space(5)+'d';--输出空格selectcharIndex('o','helloworld');--查找索引selectcharIndex('o','helloworld',6);--查找索引selectquoteName('abc[]def'),quoteName('123]45');--精确数字selectstr(123.456,2),str(123.456,3),str(123.456,4);selectstr(123.456,9,2),str(123.456,9,3),str(123.456,6,1),str(123.456,9,6);selectdifference('hello','helloWorld');--比较字符串相同selectdifference('hello','world');selectdifference('hello','llo');selectdifference('hello','hel');selectdifference('hello','hello');selectreplace('abcedef','e','E');--替换字符串selectstuff('helloworld',3,4,'ABC');--指定位置替换字符串selectreplicate('abc#',3);--重复字符串selectsubString('abc',1,1),subString('abc',1,2),subString('helloWrold',7,5);--截取字符串selectlen('abc');--返回长度selectreverse('sqlServer');--反转字符串selectleft('leftString',4);--取左边字符串selectleft('leftString',7);selectright('leftString',6);--取右边字符串selectright('leftString',3);selectlower('aBc'),lower('ABC');--小写selectupper('aBc'),upper('abc');--大写--去掉左边空格selectltrim('abc'),ltrim('#abc#'),ltrim('--去掉右边空格selectrtrim('abc'),rtrim('#abc#abc');
'),rtrim('abc');
6、安全函数selectcurrent_user;
selectuser;selectuser_id(),user_id('dbo'),user_id('public'),user_id('guest');selectuser_name(),user_name(1),user_name(0),user_name(2);selectsession_user;selectsuser_id('sa');selectsuser_sid(),suser_sid('sa'),suser_sid('sysadmin'),suser_sid('serveradmin');selectis_member('dbo'),is_member('public');selectsuser_name(),suser_name(1),suser_name(2),suser_name(3);selectsuser_sname(),suser_sname(0x01),suser_sname(0x02),suser_sname(0x03);selectis_srvRoleMember('sysadmin'),is_srvRoleMember('serveradmin');selectpermissions(object_id('student'));selectsystem_user;selectschema_id(),schema_id('dbo'),schema_id('guest');selectschema_name(),schema_name(1),schema_name(2),schema_name(3);
7、系统函数selectapp_name();--当前会话的应用程序名称selectcast(2011asdatetime),cast('10'asmoney),cast('0'asvarbinary);--类型转换selectconvert(datetime,'2011');--类型转换selectcoalesce(null,'a'),coalesce('123','a');--返回其参数中第一个非空表达式selectcollationProperty('Traditional_Spanish_CS_AS_KS_WS','CodePage');selectcurrent_timestamp;--当前时间戳selectcurrent_user;selectisDate(getDate()),isDate('abc'),isNumeric(1),isNumeric('a');selectdataLength('abc');selecthost_id();selecthost_name();selectdb_name();selectident_current('student'),ident_current('classes');--返回主键id的最大值selectident_incr('student'),ident_incr('classes');--id的增量值selectident_seed('student'),ident_seed('classes');select@@identity;--最后一次自增的值selectidentity(int,1,1)asidintotabfromstudent;--将studeng表的烈属，以/1自增形式创建一个tabselect*fromtab;select@@rowcount;--影响行数select@@cursor_rows;--返回连接上打开的游标的当前限定行的数目
select@@error;--T-SQL的错误号select@@procid;
8、配置函数setdatefirst7;--设置每周的第一天，表示周日select@@datefirstas'星期的第一天',datepart(dw,getDate())AS'今天是星期';select@@dbts;--返回当前数据库唯一时间戳setlanguage'Italian';select@@langIdas'LanguageID';--返回语言idselect@@languageas'LanguageName';--返回当前语言名称select@@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）select@@max_connections;--返回SQLServer实例允许同时进行的最大用户连接数select@@MAX_PRECISIONAS'MaxPrecision';--返回decimal和numeric数据类型所用的精度级别select@@SERVERNAME;--SQLServer的本地服务器的名称select@@SERVICENAME;--服务名select@@SPID;--当前会话进程idselect@@textSize;select@@version;--当前数据库版本信息
9、系统统计函数select@@CONNECTIONS;--连接数select@@PACK_RECEIVED;select@@CPU_BUSY;select@@PACK_SENT;select@@TIMETICKS;select@@IDLE;select@@TOTAL_ERRORS;select@@IO_BUSY;select@@TOTAL_READ;--读取磁盘次数select@@PACKET_ERRORS;--发生的网络数据包错误数select@@TOTAL_WRITE;--sqlserver执行的磁盘写入次数selectpatIndex('%soft%','microsoftSqlServer');selectpatIndex('soft%','softwareSqlServer');selectpatIndex('%soft','SqlServermicrosoft');selectpatIndex('%so_gr%','Jsonisprogram');
10、用户自定义函数
#查看当前数据库所有函数--查询所有已创建函数selectdefinition,*fromsys.sql_modulesmjoinsys.objectsoonm.object_id=o.object_idandtypein('fn','if','tf');
#创建函数if(object_id('fun_add','fn')isnotnull)dropfunctionfun_addgocreatefunctionfun_add(@num1int,@num2int)returnsintwithexecuteascallerasbegindeclare@resultint;if(@num1isnull)set@num1=0;if(@num2isnull)set@num2=0;set@result=@num1+@num2;return@result;endgo调用函数selectdbo.fun_add(id,age)fromstudent;--自定义函数，字符串连接if(object_id('fun_append','fn')isnotnull)dropfunctionfun_appendgocreatefunctionfun_append(@argsnvarchar(1024),@args2nvarchar(1024))returnsnvarchar(2048)asbeginreturn@args+@args2;endgoselectdbo.fun_append(name,'abc')fromstudent;
#修改函数alterfunctionfun_append(@argsnvarchar(1024),@args2nvarchar(1024))returnsnvarchar(1024)asbegindeclare@resultvarchar(1024);--coalesce返回第一个不为null的值set@args=coalesce(@args,'');set@args2=coalesce(@args2,'');;set@result=@args+@args2;return@result;endgoselectdbo.fun_append(name,'#abc')fromstudent;
#返回table类型函数--返回table对象函数selectname,object_id,typefromsys.objectswheretypein('fn','if','tf')ortypelike'%f%';if(exists(select*fromsys.objectswheretypein('fn','if','tf')andname='fun_find_stuRecord'))dropfunctionfun_find_stuRecordgocreatefunctionfun_find_stuRecord(@idint)returnstableasreturn(select*fromstudentwhereid=@id);goselect*fromdbo.fun_find_stuRecord(2);
SQLServerTransact-SQL编程
T-SQL语句用于管理SQLServer数据库引擎实例，创建和管理数据库对象，以及查询、插入、修改和删除数据。?变量1、局部变量（LocalVariable）局部变量是用户可以自定义的变量，它的作用范围是仅在程序内部，在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以@开头，而且必须用declare命令后才能使用。
基本语法：声明变量declare@变量名变量类型[@变量名变量类型]为变量赋值set@变量名=变量值;select@变量名=变量值;
示例：--局部变量declare@idchar(10)--声明一个长度的变量iddeclare@ageintselect@id=22--声明一个int类型变量age--赋值操作
set@age=55--赋值操作printconvert(char(10),@age)+'#'+@idselect@age,@idgo简单helloworld示例declare@namevarchar(20);declare@resultvarchar(200);set@name='jack';set@result=@name+'say:helloworld!';select@result;查询数据示例declare@idint,@namevarchar(20);set@id=1;
select@name=namefromstudentwhereid=@id;select@name;select赋值declare@namevarchar(20);select@name='jack';select*fromstudentwherename=@name;从上面的示例可以看出，局部变量可用于程序中保存临时数据、传递数据。Set赋值一般用于赋值指定的常量个变量。而select多用于查询的结果进行赋值，当然select也可以将常量赋值给变量。注意：在使用select进行赋值的时候，如果查询的结果是多条的情况下，会利用最后一条数据进行赋值，前面的赋值结果将会被覆盖。
2、全局变量（GlobalVariable）全局变量是系统内部使用的变量，其作用范围并不局限于某一程序而是任何程序均可随时调用的。全局变量一般存储一些系统的配置设定值、统计数据。全局变量select@@identity;--最后一次自增的值selectidentity(int,1,1)asidintotabfromstudent;--将studeng表的烈属，以/1自增形式创建一个tabselect*fromtab;select@@rowcount;--影响行数select@@cursor_rows;--返回连接上打开的游标的当前限定行的数目select@@error;--T-SQL的错误号select@@procid;--配置函数setdatefirst7;--设置每周的第一天，表示周日select@@datefirstas'星期的第一天',datepart(dw,getDate())AS'今天是星期';select@@dbts;--返回当前数据库唯一时间戳setlanguage'Italian';select@@langIdas'LanguageID';--返回语言idselect@@languageas'LanguageName';--返回当前语言名称select@@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）select@@max_connections;--返回SQLServer实例允许同时进行的最大用户连接数select@@MAX_PRECISIONAS'MaxPrecision';--返回decimal和numeric数据类型所用的精度级别
select@@SERVERNAME;--SQLServer的本地服务器的名称select@@SERVICENAME;--服务名select@@SPID;--当前会话进程idselect@@textSize;select@@version;--当前数据库版本信息--系统统计函数select@@CONNECTIONS;--连接数select@@PACK_RECEIVED;select@@CPU_BUSY;select@@PACK_SENT;select@@TIMETICKS;select@@IDLE;select@@TOTAL_ERRORS;select@@IO_BUSY;select@@TOTAL_READ;--读取磁盘次数select@@PACKET_ERRORS;--发生的网络数据包错误数select@@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
?输出语句T-SQL支持输出语句，用于显示结果。常用输出语句有两种：基本语法print变量或表达式select变量或表达式
示例select1+2;select@@language;selectuser_name();print1+2;print@@language;printuser_name();print在输出值不少字符串的情况下，需要用convert转换成字符串才能正常输出，而且字符串的长度在超过8000的字符以后，后面的将不会显示。
?逻辑控制语句
1、if-else判断语句语法if<表达式><命令行或程序块>elseif<表达式><命令行或程序块>else<命令行或程序块>示例if简单示例if2>3print'2>3';elseprint'2<3';if(2>3)print'2>3';elseif(3>2)print'3>2';elseprint'other';简单查询判断declare@idchar(10),@pidchar(20),@namevarchar(20);set@name='广州';select@id=idfromab_areawhereareaName=@name;select@pid=pidfromab_areawhereid=@id;print@id+'#'+@pid;if@pid>@idbeginprint@id+'%';select*fromab_areawherepidlike@id+'%';endelsebeginprint@id+'%';print@id+'#'+@pid;select*fromab_areawherepid=@pid;end
go
2、while…continue…break循环语句基本语法while<表达式>begin<命令行或程序块>[break][continue]<命令行或程序块>end示例--while循环输出到declare@iint;set@i=1;while(@i<11)beginprint@i;set@i=@i+1;endgo--whilecontinue输出到declare@iint;set@i=1;while(@i<11)beginif(@i<5)beginset@i=@i+1;continue;endprint@i;set@i=@i+1;endgo--whilebreak输出到declare@iint;set@i=1;while(1=1)
beginprint@i;if(@i>=5)beginset@i=@i+1;break;endset@i=@i+1;endgo
3、case基本语法casewhen<条件表达式>then<运算式>when<条件表达式>then<运算式>when<条件表达式>then<运算式>[else<运算式>]end示例select*,casesexwhen1then'男'when0then'女'else'火星人'endas'性别'fromstudent;selectareaName,'区域类型'=casewhenareaType='省'thenareaName+areaTypewhenareaType='市'then'city'whenareaType='区'then'area'else'other'endfromab_area;
4、其他语句批处理语句goUsemaster
Go延时执行，类似于定时器、休眠等waitfordelay'00:00:03';--定时三秒后执行print'定时三秒后执行';
SQLServer触发器
触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。?什么是触发器触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQLServer2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。
DML触发器分为：1、after触发器（之后触发）a、insert触发器b、update触发器c、delete触发器
2、insteadof触发器（之前触发）
其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。insteadof触发器表示并不执行其定义的操作而（insert、update、delete）而仅是执行触发器本身。既可以在表上定义insteadof触发器，也可以在视图上定义。
触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。
对表的操作
Inserted逻辑表
Deleted逻辑表
增加记录（insert）
存放增加的记录
无
删除记录（delete）
无
存放被删除的记录
修改记录（update）
存放更新后的记录
存放更新前的记录
Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。
?创建触发器语法createtriggertgr_nameontable_namewithencrypion–加密触发器forupdate...asTransact-SQL
#创建insert类型触发器
--创建insert插入类型触发器if(object_id('tgr_classes_insert','tr')isnotnull)droptriggertgr_classes_insertgocreatetriggertgr_classes_insertonclassesforinsert--插入触发as--定义变量declare@idint,@namevarchar(20),@tempint;--在inserted表中查询已经插入记录信息select@id=id,@name=namefrominserted;set@name=@name+convert(varchar,@id);set@temp=@id/2;insertintostudentvalues(@name,18+@id,@temp,@id);print'添加学生成功！';go--插入数据insertintoclassesvalues('5班',getDate());--查询数据select*fromclasses;select*fromstudentorderbyid;insert触发器，会在inserted表中添加一条刚插入的记录。
#创建delete类型触发器--delete删除类型触发器if(object_id('tgr_classes_delete','TR')isnotnull)droptriggertgr_classes_deletegocreatetriggertgr_classes_deleteonclassesfordelete--删除触发asprint'备份数据中……';if(object_id('classesBackup','U')isnotnull)--存在classesBackup，直接插入数据insertintoclassesBackupselectname,createDatefromdeleted;else--不存在classesBackup创建再插入select*intoclassesBackupfromdeleted;print'备份数据成功！';go
---不显示影响行数--setnocounton;deleteclasseswherename='5班';--查询数据select*fromclasses;select*fromclassesBackup;delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。
#创建update类型触发器--update更新类型触发器if(object_id('tgr_classes_update','TR')isnotnull)droptriggertgr_classes_updategocreatetriggertgr_classes_updateonclassesforupdateasdeclare@oldNamevarchar(20),@newNamevarchar(20);--更新前的数据select@oldName=namefromdeleted;if(exists(select*fromstudentwherenamelike'%'+@oldName+'%'))begin--更新后的数据select@newName=namefrominserted;updatestudentsetname=replace(name,@oldName,@newName)wherenamelike'%'+@oldName+'%';print'级联修改数据成功！';endelseprint'无需修改student表！';go--查询数据select*fromstudentorderbyid;select*fromclasses;updateclassessetname='五班'wherename='5班';update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。
#update更新列级触发器if(object_id('tgr_classes_update_column','TR')isnotnull)droptriggertgr_classes_update_columngocreatetriggertgr_classes_update_columnonclassesforupdateas--列级触发器：是否更新了班级创建时间if(update(createDate))beginraisError('系统提示：班级创建时间不能修改！',16,11);rollbacktran;endgo--测试select*fromstudentorderbyid;select*fromclasses;updateclassessetcreateDate=getDate()whereid=3;updateclassessetname='四班'whereid=7;更新列级触发器可以用update是否判断更新列记录；
#insteadof类型触发器insteadof触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容。创建语法createtriggertgr_nameontable_namewithencryptioninsteadofupdate...asT-SQL
#创建insteadof触发器if(object_id('tgr_classes_inteadOf','TR')isnotnull)droptriggertgr_classes_inteadOfgo
createtriggertgr_classes_inteadOfonclassesinsteadofdelete/*,update,insert*/asdeclare@idint,@namevarchar(20);--查询被删除的信息，病赋值select@id=id,@name=namefromdeleted;print'id:'+convert(varchar,@id)+',name:'+@name;--先删除student的信息deletestudentwherecid=@id;--再删除classes的信息deleteclasseswhereid=@id;print'删除[id:'+convert(varchar,@id)+',name:'+@name+']的信息成功！';go--testselect*fromstudentorderbyid;select*fromclasses;deleteclasseswhereid=7;
#显示自定义消息raiserrorif(object_id('tgr_message','TR')isnotnull)droptriggertgr_messagegocreatetriggertgr_messageonstudentafterinsert,updateasraisError('tgr_message触发器被触发',16,10);go--testinsertintostudentvalues('lily',22,1,7);updatestudentsetsex=0wherename='lucy';select*fromstudentorderbyid;
#修改触发器altertriggertgr_messageonstudentafterdeleteasraisError('tgr_message触发器被触发',16,10);go--testdeletefromstudentwherename='lucy';
#启用、禁用触发器--禁用触发器disabletriggertgr_messageonstudent;--启用触发器enabletriggertgr_messageonstudent;
#查询创建的触发器信息--查询已存在的触发器select*fromsys.triggers;select*fromsys.objectswheretype='TR';--查看触发器触发事件selectte.*fromsys.trigger_eventstejoinsys.triggerstont.object_id=te.object_idwheret.parent_class=0andt.name='tgr_valid_data';--查看创建触发器语句execsp_helptext'tgr_message';
#示例，验证插入数据if((object_id('tgr_valid_data','TR')isnotnull))droptriggertgr_valid_datagocreatetriggertgr_valid_dataonstudentafterinsertasdeclare@ageint,@namevarchar(20);select@name=s.name,@age=s.agefrominserteds;if(@age<18)beginraisError('插入新数据的age有问题',16,1);rollbacktran;endgo--testinsertintostudentvalues('forest',2,0,7);insertintostudentvalues('forest',22,0,7);select*fromstudentorderbyid;
#示例，操作日志if(object_id('log','U')isnotnull)droptableloggocreatetablelog(idintidentity(1,1)primarykey,actionvarchar(20),createDatedatetimedefaultgetDate())goif(exists(select*fromsys.objectswherename='tgr_student_log'))droptriggertgr_student_loggocreatetriggertgr_student_logonstudentafterinsert,update,deleteasif((exists(select1frominserted))and(exists(select1fromdeleted)))begininsertintolog(action)values('updated');endelseif(exists(select1frominserted)andnotexists(select1fromdeleted))begininsertintolog(action)values('inserted');endelseif(notexists(select1frominserted)andexists(select1fromdeleted))begininsertintolog(action)values('deleted');endgo--testinsertintostudentvalues('king',22,1,7);updatestudentsetsex=0wherename='king';deletestudentwherename='king';select*fromlog;select*fromstudentorderbyid;
SQLServer存储过程
Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。?存储过程的概念存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。
1、存储过程的优点A、存储过程允许标准组件式编程存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。B、存储过程能够实现较快的执行速度如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。C、存储过程减轻网络流量
对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。D、存储过程可被作为一种安全机制来充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。
?系统存储过程系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。常用系统存储过程有：execsp_databases;--查看数据库execsp_tables;--查看表execsp_columnsstudent;--查看列execsp_helpIndexstudent;--查看索引execsp_helpConstraintstudent;--约束execsp_stored_procedures;execsp_helptext'sp_stored_procedures';--查看存储过程创建、定义语句execsp_renamestudent,stuInfo;--修改表、索引、列的名称execsp_renamedbmyTempDB,myDB;--更改数据库名称execsp_defaultdb'master','myDB';--更改登录名的默认数据库execsp_helpdb;--数据库帮助，查询数据库信息execsp_helpdbmaster;系统存储过程示例：--表重命名execsp_rename'stu','stud';select*fromstud;--列重命名execsp_rename'stud.name','sName','column';execsp_help'stud';--重命名索引execsp_renameN'student.idx_cid',N'idx_cidd',N'index';
execsp_help'student';--查询所有存储过程select*fromsys.objectswheretype='P';select*fromsys.objectswheretype_desclike'%pro%'andnamelike'sp%';
?用户自定义存储过程1、创建语法createproc|procedurepro_name[{@参数数据类型}[=默认值][output],{@参数数据类型}[=默认值][output],....]asSQL_statements
2、创建不带参数存储过程--创建存储过程if(exists(select*fromsys.objectswherename='proc_get_student'))dropprocproc_get_studentgocreateprocproc_get_studentasselect*fromstudent;--调用、执行存储过程execproc_get_student;
3、修改存储过程--修改存储过程alterprocproc_get_studentasselect*fromstudent;
4、带参存储过程--带参存储过程if(object_id('proc_find_stu','P')isnotnull)dropprocproc_find_stu
gocreateprocproc_find_stu(@startIdint,@endIdint)asselect*fromstudentwhereidbetween@startIdand@endIdgoexecproc_find_stu2,4;
5、带通配符参数存储过程--带通配符参数存储过程if(object_id('proc_findStudentByName','P')isnotnull)dropprocproc_findStudentByNamegocreateprocproc_findStudentByName(@namevarchar(20)='%j%',@nextNamevarchar(20)='%')asselect*fromstudentwherenamelike@nameandnamelike@nextName;goexecproc_findStudentByName;execproc_findStudentByName'%o%','t%';
6、带输出参数存储过程if(object_id('proc_getStudentRecord','P')isnotnull)dropprocproc_getStudentRecordgocreateprocproc_getStudentRecord(@idint,--默认输入参数@namevarchar(20)out,--输出参数@agevarchar(20)output--输入输出参数)asselect@name=name,@age=age=@age;go-declare@idint,@namevarchar(20),@tempvarchar(20);set@id=7;set@temp=1;fromstudentwhereid=@idandsex
execproc_getStudentRecord@id,@nameout,@tempoutput;select@name,@temp;print@name+'#'+@temp;
7、不缓存存储过程--WITHRECOMPILE不缓存if(object_id('proc_temp','P')isnotnull)dropprocproc_tempgocreateprocproc_tempwithrecompileasselect*fromstudent;goexecproc_temp;
8、加密存储过程--加密WITHENCRYPTIONif(object_id('proc_temp_encryption','P')isnotnull)dropprocproc_temp_encryptiongocreateprocproc_temp_encryptionwithencryptionasselect*fromstudent;goexecproc_temp_encryption;execsp_helptext'proc_temp';execsp_helptext'proc_temp_encryption';
9、带游标参数存储过程if(object_id('proc_cursor','P')isnotnull)dropprocproc_cursorgocreateprocproc_cursor@curcursorvaryingoutputasset@cur=cursorforward_onlystaticfor
selectid,name,agefromstudent;open@cur;go--调用declare@exec_curcursor;declare@idint,@namevarchar(20),@ageint;execproc_cursor@cur=@exec_curoutput;--调用存储过程fetchnextfrom@exec_curinto@id,@name,@age;while(@@fetch_status=0)beginfetchnextfrom@exec_curinto@id,@name,@age;print'id:'+convert(varchar,@id)+',name:'+@name+',age:'+convert(char,@age);endclose@exec_cur;deallocate@exec_cur;--删除游标
10、分页存储过程---存储过程、row_number完成分页if(object_id('pro_page','P')isnotnull)dropprocproc_cursorgocreateprocpro_page@startIndexint,@endIndexintasselectcount(*)fromproduct;select*from(selectrow_number()over(orderbypid)asrowId,*fromproduct)tempwheretemp.rowIdbetween@startIndexand@endIndexgo--dropprocpro_pageexecpro_page1,4---分页存储过程if(object_id('pro_page','P')isnotnull)dropprocpro_stugo
createprocedurepro_stu(@pageIndexint,@pageSizeint)asdeclare@startRowint,@endRowintset@startRow=(@pageIndex-1)*@pageSize+1set@endRow=@startRow+@pageSize-1select*from(select*,row_number()over(orderbyidasc)asnumberfromstudent)twheret.numberbetween@startRowand@endRow;execpro_stu2,2;
?RaiserrorRaiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。语法如下：Raiserror({msg_id|msg_str|@local_variable}{,severity,state}[,argument[,…n]][withoption[,…n]])
#msg_id:在sysmessages系统表中指定的用户定义错误信息#msg_str:用户定义的信息，信息最大长度在2047个字符。#severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有altertrace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用withlog选项。#state：介于1至127直接的任何整数。State默认值是1。raiserror('iserror',16,1);select*fromsys.messages;--使用sysmessages中定义的消息
raiserror(33003,16,1);raiserror(33006,16,1);
