初级进阶版SQL语句总结（1）

我不是专业的DBA，这里记录的都是一些常用的SQL技能点，为的是熟练掌握这些来提高我们的工作效率，如果想系统学习SQL的话，那恐怕要让您失望了。

1、case项作为排序条件

case常常被用来对字段内容进行转换，除此之外还能用作排序条件，这种用法我之前用的不多，记录下来希望对大家有帮助。

select * from Sys_Module where ModuleCode=2589 or ParentCode=2589
order by case ModuleCode when 2595 then 1 else 0 end desc,ModuleCode desc

除了通过case单列进行排序外，还可以按照多列进行混合排序

select * from Sys_Module where ModuleCode=2589 or ParentCode=2589
order by case when ParentCode=30 then 2 when ModuleCode=2595 then 1 else 0 end desc,ModuleCode desc

2、CharIndex函数

在一个表达式中搜索另一个表达式，如果找到则返回其起始位置，如果找不到则返回0。

注意：返回的起始位置从 1 开始，而不是从 0 开始。
语法：
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
expressionToFind
是字符表达式，其中包含要查找的序列。 expressionToFind限制为 8000 个字符。
expressionToSearch
是要搜索的字符表达式。
start_location
是整数或bigint开始搜索的表达式。 如果start_location未指定、 为负数，或为 0，则从expressionToSearch首字母处开始搜索。

select *,CHARINDEX(',6661,',','+StoreNO+',') as CharIndex from StoreInfo
select *,CHARINDEX(',6661,',','+StoreNO+',',0) as CharIndex from StoreInfo
select *,CHARINDEX(',6661,',','+StoreNO+',',1) as CharIndex from StoreInfo

运行截图如下：

select *,CHARINDEX(',6661,',','+StoreNO+',',2) as CharIndex from StoreInfo

运行截图如下：

3、PATINDEX函数

4、CONVERT函数

在做查询页的时候，有时会需要查询某天的内容，比如查询2017/11/21这一天的信息，我们可以使用date>2017/11/21 00:00:00 and date<=2017/11/22 00:00:00，我觉得这个有点麻烦想找个一句话就能搞定的方法，就有了下边的知识点。

select Id,UserName,CreateDate,CONVERT(nvarchar(10),CreateDate,120) from Sys_User
where CONVERT(nvarchar(10),CreateDate,120)='2017-11-16'

截图如下：

select Id,UserName,CreateDate,CONVERT(nvarchar(10),CreateDate,111) from Sys_User
where CONVERT(nvarchar(10),CreateDate,111)='2017/11/16'

截图如下：

如果只想以时分秒为条件进行查询，不考虑年月日的话，可以使用下面的方法：

select Information,InformTime,IsRead,CONVERT(nvarchar(12),GETDATE(),114) from Ser_Inform 
where CONVERT(nvarchar(12),GETDATE(),114) < CONVERT(nvarchar(12),InformTime,114)

截图如下：

5、DATEADD()

DATEADD() 函数可以在日期中添加或减去指定的时间间隔。

select UserName,UserPwd,Sex,CreateDate,DATEADD(YEAR,1,CreateDate) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEADD(MONTH,1,CreateDate) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEADD(DAY,1,CreateDate) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEADD(HOUR,-1,CreateDate) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEADD(MINUTE,-1,CreateDate) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEADD(SECOND,-1,CreateDate) from Sys_User

DATEADD(YEAR,1,CreateDate)截图如下：

DATEADD(HOUR,-1,CreateDate)截图如下：

6、DATEDIFF()

DATEDIFF() 函数返回两个日期之间的时间差。 语法：DATEDIFF(datepart,startdate,enddate) 时间差：enddate-startdate

select UserName,UserPwd,Sex,CreateDate,DATEDIFF(YEAR,CreateDate,GETDATE()) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEDIFF(MONTH,CreateDate,GETDATE()) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEDIFF(DAY,CreateDate,GETDATE()) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEDIFF(HOUR,CreateDate,GETDATE()) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEDIFF(MINUTE,CreateDate,GETDATE()) from Sys_User
select UserName,UserPwd,Sex,CreateDate,DATEDIFF(SECOND,CreateDate,GETDATE()) from Sys_User

DATEDIFF(DAY,CreateDate,GETDATE())截图如下：

DATEDIFF(HOUR,CreateDate,GETDATE())截图如下：

7、字段值补齐8位

--虽然想补齐到8位，但最多能补3个'0'（right表示从右边开始截取）
select top 5 Id, right(replicate('0',3)+ltrim(Name),8),Phone from Sys_User

--虽然最多能补10个'0'，但只需补齐到8位（right表示从右边开始截取）
select top 5 Id, right(replicate('0',10)+ltrim(Name),8),Phone from Sys_User

8、查询数据库中有多少个表/视图

--查询用户表数量
select count(1) from sysobjects where xtype='U'
--查询用户视图数量
select count(1) from sysobjects where xtype='V'
--查询用户存储过程数量
select count(1) from sysobjects where xtype='P'

sysobjects表中xtype列各字段的意思：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
PK = PRIMARY KEY 约束（类型是 K）
UQ = UNIQUE 约束（类型是 K）
RF = 复制筛选存储过程
TF = 表函数
TR = 触发器
S = 系统表
U = 用户表
V = 视图
P = 存储过程
X = 扩展存储过程

9、update...join的用法

我们常用的单表更新语句是update...where，如果需要多表关联进行更新的话就用到update...join了，本例所用的Sys_User表、Sys_Dict表中的初始数据如下：

update Sys_User set Password='update' from Sys_User as t1
inner join Sys_Dict as t2 on t1.DictCode=t2.DictCode
where t1.Id>1

Sql语句执行后Sys_User中的数据修改成下面这样：

10、stuff()的用法

11、index+nolock

12、处理特殊字符

13、不加括号时SqlServer中and和or的执行顺序

14、游标的简单使用

--创建一个游标
declare my_cursor cursor for     --my_cursor为游标的名称，随便起
select id,name from my_user      --这是游标my_cursor的值，这里随便发挥看业务场景
--打开游标
open my_cursor                  --没什么好说的
--变量
declare   @id int               --声明变量  ‘declare’为声明变量 ‘@name’为变量名称 后面为变量类型
declare   @name varchar(50)     --这里是两个变量用来接收游标的值
--循环游标
fetch next from my_cursor into @id,@name  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
while @@FETCH_STATUS=0 --假如检索到了数据继续执行
begin
print(@name) --print()打印变量 随便发挥
select * from my_user where id=@id --这里是具体业务了，随便发挥。而我这是又执行了一次查询 
fetch next from my_cursor into @id,@name --获取下一条数据并赋值给变量
end--关闭释放游标
close my_cursor
deallocate my_cursor

15、待发现