SQL Server 常用高级语法

1、case...end （具体的值）

case后面有值，相当于c#中的switch case
注意：case后必须有条件，并且when后面必须是值不能为条件。

select name , --注意逗号
   case level --case后跟条件
       when 1 then '骨灰'
       when 2 then '大虾'
       when 3 then'菜鸟'  
   end as'头衔'
from [user]

2、case...end （范围）

case 后面无值，相当于c#中的if...else if...else....
注意：case后不跟条件

select studentId,
    case
       when english between 80 and 90  then '优'
       when english between 60 and 79  then '良'
       else '差'
    end
 from Score
------------------case---end--------------------------------
select studentId,
    case
       when english >=80  then '优'
       when english >=60  then '良'
       else '差'
    end
 from Score
-----------------------------------------------------
select *,
   case
         when english>=60 and math >=60 then '及格'
         else '不及格'
   end
from Score
3、if...eles
IF(条件表达式)
  BEGIN --相当于C#里的{
    语句1
  ……
  END --相当于C#里的}
ELSE
 BEGIN
    语句1
    ……
  END
--计算平均分数并输出，如果平均分数超过分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @avg int --定义变量
select @avg= AVG(english) from Score  --为变量赋值
select '平均成绩'+CONVERT(varchar,@avg) --打印变量的值
    if @avg<60 
          begin 
              select '前三名'
              select top 3 * from Score order by english desc
          end
    else
         begin
             select '后三名'
             select top 3 * from Score order by english  
         end

4、while循环

WHILE(条件表达式)
  BEGIN --相当于C#里的{
    语句
    ……
    BREAK
  END --相当于C#里的}

--如果不及格的人超过半数(考试题出难了)，则给每个人增加分
select * from Score
declare @conut int,@failcount int,@i int=0  --定义变量
select @conut =COUNT(*) from Score --统计总人数
select @failcount =COUNT(*) from Score where english<100 --统计未及格的人数
while (@failcount>@conut/2)
   begin
       update Score set english=english+1
       select @failcount=COUNT(*) from Score where english<100
       set @i=@i+1
   end
select @i
update Score set english=100 where english >100
5、索引
使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。

• 1）聚集索引：索引目录中的和目录中对应的数据都是有顺序的。
• 2）非聚集索引：索引目录有顺序但存储的数据是没有顺序的。

--创建非聚集索引 CREATE NONCLUSTERED INDEX [IX_Student_sNo] ON student ( [sNo] ASC )

6.分页

select * from (select ROW_NUMBER() over( order by [UnitPrice] asc) as num,* from [Books] where [publisherid]=1 )as t where t.num between 1 and 20 --要查询的开始条数和结束条数

7.视图

优点：

• 筛选表中的行
• 防止未经许可的用户访问敏感数据
• 降低数据库的复杂程度

create view v_Demo
as
select ......

8.局部变量

--声明变量:使用declare关键字，并且变量名已@开头，@直接连接变量名,中间没有空格。必须指明变量的类型，同时还可以声明多个不同类型的变量。
declare @name nvarchar(30) ,@age int

--变量赋值：
--1、使用set 给变量赋值,只能给一个变量赋值
set @age=18
set @name ='Tianjia'

select @age,@name  --输出变量的值

--2、使用select 可以同时为多个变量赋值
select @age=19,@name='Laoniu'

--3、在查询语句中为变量赋值
declare @sum int =18 --为变量赋初值
select @sum= SUM(english) from Score --查询语句中赋值
select @sum --输出变量值

--4、变量作为条件使用

declare @sname nvarchar(10)='张三' 
declare @sage int
select @sage=sage from student where sName=@sname
select @sage

--5、使用print输出变量值，一次只能输出一个变量的值,输出为文本形式
print @sage

9.存储过程

存储过程---就像数据库中运行方法(函数)
和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用。

优点：

• 执行速度更快 - 在数据库中保存的存储过程语句都是编译过的
• 允许模块化程序设计 - 类似方法的复用
• 提高系统安全性 - 防止SQL注入
• 减少网络流通量 - 只要传输 存储过程的名称

系统存储过程

• 由系统定义，存放在master数据库中
• 名称以“sp”开头或”xp”开头

定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名 
    @参数1  数据类型 = 默认值 OUTPUT,
    @参数n  数据类型 = 默认值 OUTPUT
    AS
      SQL语句
参数说明：
参数可选
参数分为输入参数、输出参数 
输入参数允许有默认值
EXEC  过程名  [参数]
----------------------例--------------------------
if exists (select * from sys.objects where name='usp_GroupMainlist1')
drop proc usp_GroupMainlist1
go
create proc usp_GroupMainlist1
   @pageIndex int, --页数
   @pageSize int, --条数
   @pageCount int output--输出共多少页
as 
   declare @count int --共多少条数据
   select @count =count(*) from [mainlist] --获取此表的总条数
   set @pageCount=ceiling(@count*1.0/@pageSize) 

   select * from 
   (select *,row_number() over(order by [date of booking] desc) as 'num' from [mainlist]) as t
   where num between(@pageSize*(@pageIndex-1)+1) and @pageSize*@pageIndex
   order by [date of booking] desc
-------------------------------------------------------------------------------------------
--调用   
declare @page int
exec usp_GroupMainlist1 1,100,@page output
select @page

10.临时表

 方法一：
  create table #临时表名(字段1 约束条件,
                      字段2 约束条件,
                  .....)
  create table ##临时表名(字段1 约束条件,
                          字段2 约束条件,
                      .....)
  方法二：
  select * into #临时表名 from 你的表;
  select * into ##临时表名 from 你的表;

注：以上的#代表局部临时表，##代表全局临时表

drop table #Tmp   --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    WokNo                varchar(50),   
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);
Select * from #Tmp    --查询临时表的数据
truncate table #Tmp --清空临时表的所有数据和约束