15个常用的SQLServer高级语法

转自：http://www.codeceo.com/article/15-sql-server-grammer.html

自从用了EF后很少写sql我今天需要写一份更复杂的报告。我检索了我以前的笔记作为参考，觉得它们仍然非常有用。因此，我把它们送出去与大家分享。

1、case…end （具体值）

case它后面有一个值，相当于c#中的switch case

注意：case在那之后，必须有条件，并且when以下内容必须是值，不能是条件。

-----------------case--end---语法结构---------------------

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后部非根部疾病

------------------case---end-------------------------------- 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、子查询

将查询语句用作用于其他目的的结果集SQL语句用法与使用常规表一样，在作为结果集查看时称为子查询。几乎所有可以使用表的地方都可以用子查询替换。

select from (select from student where sAge<30) as t --要查询的子表必须具有别名 where t.sSex =男 --筛选子表中的列

转换为小数点后两位：CONVERT(numeric(10,2), AVG(english))

只有仅返回一行或一列数据的子查询才能被视为单值子查询。

select 平均成绩, (select AVG(english) from Score) --可以成功执行 select 姓名, (select sName from student) --错误，因为“name”只有一行，并且子表中有多个名称行

select * from student where sClassId in(select cid from Class where cName IN(高一一班,高二一班)) --在子查询具有多个值时使用in

7、分页

--分页1 select top 3 from student where [sId] not in (select top (3(4-1)) [sid] from student)--4表示页数

select *, row_number() over(order by [sage] desc ) from student-- row_number() over (order by..)获取行号

--分页2 select from (select , row_number() over(order by [sid] desc ) as num from student)as t where num between (Y-1)T+1 and YT order by [sid] desc

--分页3 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 --要查询的开始和结束条目数

8、连接

select sName,sAge, case when english <60 then 不及格 when english IS null then 缺考 else CONVERT(nvarchar, english) end as英语成绩 from student as s left join Score as c on s.sid =c.sid

内连接 inner join...on... 查询满足on后续条件的数据 外连接
左连接 left join...on... 首先，检查左表中的所有数据 再使用on以下条件筛选数据 右连接 right join...on... 首先，检查右表中的所有数据 再使用on以下条件筛选数据 全连接 full join ...on...

（*）交叉连接 
    cross join 没有on
        将第一个表的每一行连接到下表的每一行
        没有条件。它是其他连接的基础

9.视图

优点：

• 筛选表中的行
• 防止未经授权的用户访问敏感数据
• 降低数据库复杂性

创建视图

create view v_Demo as select ......

10局部变量

---------------------------------局部变量-------------------------- --声明变量:使用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

11全局变量

--------------------------全局变量（系统变量）---------------------------------- select * from student0 select @@error --最后一个T-SQL错误的错误号 select @@max_connections--获取创建的最大同时连接数 select @@identity --返回最近插入的数字

12、事务

交易：共存与共同死亡

访问并可能更新数据库中各种数据项的程序执行单元(unit)- 即多个sql语句组成，必须作为一个整体执行

这些sql该语句将作为一个整体提交给系统，执行或不执行

语法步骤：

• 开始交易：BEGIN TRANSACTION
• 事务提交：COMMIT TRANSACTION
• 事务回滚：ROLLBACK TRANSACTION

确定执行语句时是否存在错误：

全局变量@@ERROR；

@@ERROR只能判断当前的T-SQL语句执行中是否存在错误，以确定所有T-SQL语句中是否存在错误，我们需要累积错误;

---------------------------模拟转账---------------------------- declare @sumError int=0 --声明变量

begin tran update bank set balance=balance-1000 where cId=0001 set @sumError=@sumError+@@error update bank set balance=balance+1000 where cId=0002 set @sumError=@sumError+@@error

if (@sumError=0) commit tran --提交成功,提交事务 else rollback tran --提交失败,回滚事务

13存储过程

存储过程 - 就像在数据库中运行方法一样(函数)

和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

14常用函数

1）ISNULL(expression,value)     如果expression不为null返回expression表达式的值，否则返回value的值

2）聚合函数

avg() -- 平均值 注意统计null将不计算在内，需要添加isnull(列名，0) sum() -- 求和 count() -- 求行数 min() -- 求最小值 max() -- 求最大值

3）字符串操作函数

LEN() --计算字符串长度 LOWER() --转小写 UPPER () --大写 LTRIM() --删除字符串左侧的空格 RTRIM () --删除字符串右侧的空格 LTRIM(RTRIM( bb )) LEFT()、RIGHT() -- 截距字符串 SUBSTRING(string,start_position,length) -- 参数string主字符串，start_position子字符串在主字符串中的起始位置（从1开始），length子字符串的最大长度。

SELECT SUBSTRING(abcdef111,2,3) REPLACE(string,oldstr,newstr)

Convert(decimal(18,2),num)--保留两位小数

4）日期相关功能

GETDATE() --获取当前日期和时间 DATEADD (datepart , number, date )--计算添加后的日期。参数date是要计算的日期;参数number增量;参数datepart是度量单位，可选值可以在备注中找到。DATEADD(DAY, 3,date)计算日期date的3日期后的几天，以及DATEADD(MONTH ,-8,date)计算日期date的8日期在几个月之前 DATEDIFF ( datepart , startdate , enddate ) --计算两个日期之间的差额。 datepart 它是一种计量单位，可以作为参考DateAdd。

-- 获取日期的某一部分 ： DATEPART (datepart,date)--返回日期的特定部分 整数 DATENAME(datepart,date)--返回日期的指定部分 字符串 YEAR() MONTH() DAY()

15、sql语句执行顺序

5>…Select 5-1>选择列,5-2>distinct,5-3>top 1>…From 表 2>…Where 条件 3>…Group by 列 4>…Having 筛选条件 6>…Order by 列