--1、while循环declare @sum intdeclare @i intset @i=1set @sum=0while(@i<101)begin set @sum =@sum+@i set @i=@i+1   if(@i>90)   print @iendprint @sum--2、goto语句declare @num intset @num=100flag:print @numselect @num=@num+1while(@num<106)goto flagprint ‘------------------‘print @num--3、表变量declare @mytable table(nam nvarchar(50),num nvarchar(50))insert into @mytable select ClassName,ClassNum  from dbo.tb_Classselect * from @mytable--4、局部临时表，全局临时表--局部临时表create table #temp_tb( id int, pwd nvarchar(50))--全局临时表create table ##temp_tb( id int, pwd nvarchar(50))--5、批量导入数据并创建临时表select ClassName,ClassNum into #temp from dbo.tb_Classselect * from #temp--6、区间查询 between and （not between and）select * from tb_class where classnum between 1200 and 1500select * from tb_class where classnum not between 1200 and 1500--7、in变量,is null is not null,去重复distinct--8、随机查询、子查询select top 1 * from tb_class order by newid()--9、行号select ‘行号‘=identity(int,1,1),classname,classnum into #temp from tb_classselect * from #temp--模糊查询 like not like "% _ []"通配符select * from tb_class where className like ‘高%[^三]‘select * from tb_class where des like ‘[a-z]%‘select * from tb_class where ClassNum like ‘[^1]%‘select * from tb_class where des like ‘%100/%%‘ escape ‘/‘select * from tb_class where des+ClassName like ‘%[二在]%‘--case 语句、类型转换 cast显示转换select *,班级=case when ClassNum>1500 then ‘大班‘when ClassNum=1500 then ‘中班‘when ClassNum<1500 then cast(ClassNum as nvarchar(20))endfrom tb_class--类型转换 1,cast 2,Convertselect convert(nvarchar(20),getdate(),111)as 时间格式--去除空格 rtrim ltrimselect ltrim(‘       中国    人         ‘)as title--截取字符串substringselect substring(‘中华人民共和国‘,3,2) as title--字符插入sutffselect stuff(‘中华共和国‘,3,0,‘人民‘) as title--计算字符长度 lenselect len(‘中华人民共和国‘)--字符串大小写lower upper--字符串替换replaceselect replace(‘中*华*人*民*共*和*国‘,‘*‘,‘-‘)--获取字符所在位置select substring(‘010-99998888‘,0,charindex(‘-‘,‘010-99998888‘))--日期函数select year(getdate())select month(getdate())select day(getdate())select datepart(year,getdate())select datepart(hh,getdate())select datename(dw,getdate()) as 今天星期几select datename(ww,getdate()) as 本周是一年的第几周select datename(yy,getdate()) as 年份select datename(dy,getdate()) as 今天是一年中的第几天select datediff(d,‘2012-11-27‘,‘2012-11-30‘) as 相差天数select datediff(hh,‘2012-11-27‘,‘2012-11-30‘) as 相差小时select getdate(),dateadd(d,3,getdate()) as 增加三天select getdate(),dateadd(hh,3,dateadd(d,3,getdate())) as 增加三天在增加三小时--排序order by  笔画排序：Chinese_prc_stroke_cs_as_ks_ws,音序排序：chinese_prc_cs_asselect * from tb_class order by des collate Chinese_prc_stroke_cs_as_ks_wsselect * from tb_class order by des collate Chinese_prc_cs_as--动态排序declare @myorder intset @myorder=2select * from tb_class order by case @myorderwhen 1 then classnumwhen 2 then idenddesc--聚合函数,where（作用单行） 和 having（作用多行）区别select count(distinct classname) from tb_class--游标  sql语句面向一个集合操作，游标面向逐条记录的操作declare cur_s cursor for select * from tb_classfor read only --只读游标for update of classname--更新游标declare @cur_ss cursor--游标变量open cur_sfetch next from cur_swhile @@FETCH_STATUS=0beginfetch next from cur_sendclose cur_s--释放游标deallocate cur_s--存储过程是一组为了完成特定功能的SQL语句集合,创建Create、修改Alter、删除Dropcreate procedure GetClassInfo(@id int,@ClassName nvarchar(50),@Sum int output--存储过程返回值一种情况)asbegin select @Sum=sum(ClassNum)from tb_Class where id>@id and ClassName=@ClassNamedeclare @AllSum intselect @AllSum=sum(ClassNum)from tb_Classreturn @AllSum--存储过程返回值另一种情况endgodeclare @sum intdeclare @AllSum intEXEC @AllSum=GetClassInfo 1,‘高三‘,@sum outputselect @sum as 总数,@AllSum as 全部总数--重命名存储过程exec sp_rename ‘GetClassInfo‘,‘ReNameGetInfo‘--监视存储过程exec sp_monitor--返回参数含义--   *last_run: 上次运行时间----      *current_run：本次运行的时间----      *seconds: 自动执行存储过程后所经过的时间----      *cpu_busy:计算机CPU处理该存储过程所使用的时间----      *io_busy:在输入和输出操作上花费的时间----       *idle：SQL Server已经空闲的时间----       *packets_received：SQL Server读取的输入数据包数----       *packets_sent：SQL Server写入的输出数据包数----        *packets_error:SQL Server在写入和读取数据包时遇到的错误数----        *total_read: SQL Server读取的次数----         *total_write: SQLServer写入的次数----         *total_errors: SQL Server在写入和读取时遇到的错误数----          *connections:登录或尝试登录SQL Server的次数 --自动执行存储过程--sp_procoption [@procName=] ‘procedure‘, [@optionName=] ‘option‘, [@optionValue=http://www.mamicode.com/] ‘value‘--         [@procName=] ‘procedure‘： 即自动执行的存储过程----         [@optionName=] ‘option‘：其值是startup，即自动执行存储过程----         [@optionValue=http://www.mamicode.com/] ‘value‘：表示自动执行是开(true)或是关(false)exec sp_procoption @procName=‘masterproc‘, @optionName=‘startup‘, @optionValue=‘true‘ --自定义函数--标量函数create function myfunAdd(@a int,@b int)returns intasbegindeclare @c intset @c=@a+@breturn @cendgodeclare @a1 int,        @b1 int,        @c1 intset @a1=8set @b1=7set @c1=School.dbo.myfunAdd(@a1,@b1)print @c1--表值函数create function GetTable()returns tableasreturn (select * from tb_class)select * from School.dbo.GetTable()--触发器，特殊的存储过程,嵌套触发器、递归触发器create trigger mytriggeron tb_Classfor update,delete,insert--三种触发器asupdate tb_student set age=age+1 where id =1update tb_Class set des=‘???‘ where id =1delete from tb_Class where id=6--事务begin trybegin transactioninsert into tb_class values(‘特色班‘,100,‘描述‘)insert into tb_student([name],sex,age,classid) values(‘小刘‘,1,22,5)commit transactionend trybegin catchrollbackend catch--保存事务begin transactioninsert into tb_class values(‘特色二班‘,100,‘描述‘)insert into tb_student([name],sex,age,classid) values(‘小二‘,1,22,5)save transaction save1insert into tb_class values(‘特色三班‘,100,‘描述‘)insert into tb_student([name],sex,age,classid) values(‘小叁‘,1,22,5)rollback transaction save1commit transaction--事务并发控制begin transelect * from dbo.tb_Class with(holdlock)waitfor delay ‘00:00:10‘commit tranupdate tb_Class set ClassNum=200 where id=12select * from tb_Class--视图--创建约束，主键约束、外键约束、唯一约束、check约束、default约束--创建表设置字段create table ss( id int identity(1,1) not null constraint pk_id primary key)--修改表设置字段alter table dbo.tb_Classadd constraint pk_id primary key(id)-- drop constraint pk_id --删除主键--add constraint un_ss unique(id)--唯一约束add constraint ch_sex check (like ‘[0-1]‘)add constraint de_sex default 1 for sexalter table dbo.tb_studentadd constraint fk_classidforeign key(classid)references tb_Class (id)