SqlServer备忘录

前言

看了一些书，同时网上参考一些文章，整理的关于SqlServer数据库sql的备忘录文章。

系统变量

全局变量的命名格式：@@xx

select @@VERSION    --查看版本信息
select @@IDENTITY   --查看当前的标识，跟在insert语句后面
select @@SERVERNAME --查看服务器名称
select @@ERROR      --返回最后执行的一句语句的错误编号,如果没错误返回0
select @@ROWCOUNT   --查看最后执行的一句语句影响的行数

数据的过滤

基本查询

select name as '姓名',age 年龄, gender from t_user;
select * from t_user where name like '金_' and gender = '男' and age > 18;
select max(age) from t_user;

排序

select * from t_user order by age desc;
select * from t_user order by age desc,id ase;

正则表达式

select * from t_user where name like '_love';
select * from t_user where name like 'love%';
select * from t_user where name like '[张李王]_';
select * from t_user where name like '[^张李]%';

空值检测

select * from t_user where name is null;
select * from t_user where name = '';

不等运算

select * from t_user where age !=18;
select * from t_user where age <>18;

范围查询

select * from t_user where age = 18 or age =19 or age =20;
select * from t_user where age in (18,19,20);
select * from t_user where age between 18 and 20;

字符串处理

字符串的长度

select len('Kimisme')

大小写转换

select lower('JACK');
select upper('rose');

去空格

select ltrim('   love');
select rtrim('love   ');
select ltrim(rtrim('   love   '));

截取

select left('abcdefg',3);--abc
select right('abcdefg',3);--efg
select substring('abcdefg',3,2);--cd

替换

select replace('Hello,World','World','Python');

转义

select ascii('a');--97
select char(97);--a

位置索引

select charindex('m','kimisme');--3

反转

select reverse('abc');--cba

类型转换

select convert(decimal(10,2),'1.234343')
select cast('1.243434' as float)

数学函数

绝对值

select abs(-3.14);

随机数

select rand();--0-1之间
select rand(2);--只产生一次，继续运行不变

四舍五入

select round(1.5555,2);--四舍五入：1.5600
select ceiling(1.1);--舍入到最大整数：2
select floor(1.9);--舍入到最小整数：1

三角函数

select sin(1);--正弦
select cos(1);--余弦
select tan(1);--正切
select asin(1);--反正弦
select acos(1);--反余弦
select atan(1);--反正切
select tan2(1,1),atan(1);--反正切
select cat(1);--余切

圆周率

select pi();

角度制与弧度制

select degrees(1),1*180/pi();--弧度制转角度制：角度制=弧度制*100/π
select radians(1),1*pi()/180;--角度制转弧度制：弧度制=角度制*π/180

求一个数的符号

select sign(-3.14),sign(3.14);大于0返回1；小于0返回-1

商与余数

select 7/3;--2
select 7%3;--1

指数与平方根

select power(2,3);--8
select sqrt(16);--4

日期与时间函数

日期的格式化

select getdate();--2015-08-11 20:25:30.973
select convert(varchar(50),getdate(),101)  --08/11/2015
select convert(varchar(50),getdate(),102)  --2015.08.11
select convert(varchar(50),getdate(),103)  --11/08/2015
select convert(varchar(50),getdate(),104)  --11.08.2015
select convert(varchar(50),getdate(),105) --11-08-2015
select convert(varchar(50),getdate(),106) --11 08 2015
select convert(varchar(50),getdate(),107) --08 11, 2015
select convert(varchar(50),getdate(),108)  --20:26:11
select convert(varchar(50),getdate(),109) --08 11 2015 10:53:15:397PM
select convert(varchar(50),getdate(),110) --08-11-2015
select convert(varchar(50),getdate(),111) --2015/08/11
select convert(varchar(50),getdate(),112)  --20150811
select convert(varchar(50),getdate(),113) --11 08 2015 22:52:17:143
select convert(varchar(50),getdate(),114) --22:53:45:847
select convert(varchar(50),getdate(),120)  --2015-08-11 22:45:34
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') --20150811225416

日期的差额

select dateadd(year,3,'2015-08-11 20:25:30.973') --2018-08-11 20:25:30.973 --年
select dateadd(quarter,1,'2015-08-11 20:25:30.973') --2015-11-11 20:25:30.973--季度
select dateadd(month,3,'2015-08-11 20:25:30.973') --2015-11-11 20:25:30.973--月份
select dateadd(day,3,'2015-08-11 20:25:30.973') --2015-08-14 20:25:30.973 -- 日
select dateadd(hour,3,'2015-08-11 20:25:30.973') --2015-08-11 23:25:30.973 -- 小时
select dateadd(minute,3,'2015-08-11 20:25:30.973') --2015-08-11 20:28:30.973 --分
select dateadd(second,3,'2015-08-11 20:25:30.973') --2015-08-11 20:25:33.973 --秒
select dateadd(millisecond,3,'2015-08-11 20:25:30.973') --2015-08-11 20:25:30.977 --毫秒

计算两个日期相差几天

select datediff(day,'2015-08-11','2015-08-14') -- 3

计算一个日期是星期几

select datename(weekday,'2015-08-11') --星期二

获取日期的指定部分

select datepart(year,'2015-08-11') – 2015
select datepart(month,'2015-08-11') – 8
select datepart(day,'2015-08-11') – 11

动态执行sql语句

declare @busshallID nvarchar(255)='2,3'
declare @strWhere nvarchar(255)=''
declare @strMain nvarchar(255)

if(@bussHallId is not  null)
    set @strWhere = @strWhere + ' and bh.ID in ('+@bussHallId+')'

set @strMain = ' select bh.Name as ''名称'',bh.CreateTime from T_BussHall bh
where 1 =1 ' +@strWhere

exec(@strMain)

其他操作

try…catch…

尽量少用，可以通过@@ERROR来查看上一条语句是否发生了错误

select * into #temp_metercard from T_MeterCard

begin try
    drop table #temp_metercard
end try
begin catch
    print '删除表失败'
end catch

if…else…

if exists(select * from T_MeterCard where ID =0)
    begin
       print '存在'
    end
else
    begin
       print '不存在'
    end

case…when…

select 
(case 
    when mc.TypeDictID =17 then '居民户'
    when mc.TypeDictID = 18 then '商业户'
    when mc.TypeDictID = 19 then '工业户'
    else '工福户' 
 end) as '户主类型',

(case mc.TypeDictID 
    when 17 then '居民户'
    when 18 then '商业户'
    when 19 then '工业户'
    else '工福户' 
 end) as '户主类型2' 
from T_MeterCard mc

while

declare @i int =1
declare @sum int =0
while(@i<=10)
begin
    set @sum += @i
    set @i += 1
end
print @sum

事务

declare @ErrorCount int = 0
begin tran
update T_MeterCard set Note = 1/1 where ID =1
set @ErrorCount+=@@ERROR

update T_MeterCard set Note = 1/0 where ID =2
set @ErrorCount+=@@ERROR

if(@ErrorCount>0)
    begin
       rollback tran
    end
else
    begin
       commit tran
    end

存储过程

无返回值的存储过程

--创建存储过程
create proc proc_sum1
@num1 int,
@num2 int
as
declare @result int
set @result = @num1 + @num2
print @result

--调用存储过程
exec proc_sum1 1,2

带返回值的存储过程

--创建存储过程
create proc proc_sum2
@num1 int,
@num2 int,
@result int output
as
set @result = @num1 + @num2

--调用存储过程
declare @r int
exec proc_sum 1,2,@r output
print @r

索引

• 创建表

create table T_User
(
    Id int primary key identity(1,1),
    UNo nvarchar(10),
    UName nvarchar(10),
    UAge int
)

• 创建索引

create index idx_user_name on T_User(UName)

• 删除索引

drop index idx_user_name on T_User