SqlServer备忘录

前言

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

系统变量

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

select @@VERSION    --查看版本信息select @@IDENTITY   --查看当前的标识，跟在insert语句后面select @@SERVERNAME --查看服务器名称select @@ERROR      --返回最后执行的一句语句的错误编号,如果没错误返回0select @@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);--abcselect right('abcdefg',3);--efgselect substring('abcdefg',3,2);--cd

替换

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

转义

select ascii('a');--97select 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.5600select ceiling(1.1);--舍入到最大整数：2select 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;--2select 7%3;--1

指数与平方根

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

日期与时间函数

日期的格式化

select getdate();--2015-08-11 20:25:30.973select convert(varchar(50),getdate(),101)  --08/11/2015select convert(varchar(50),getdate(),102)  --2015.08.11select convert(varchar(50),getdate(),103)  --11/08/2015select convert(varchar(50),getdate(),104)  --11.08.2015select convert(varchar(50),getdate(),105) --11-08-2015select convert(varchar(50),getdate(),106) --11 08 2015select convert(varchar(50),getdate(),107) --08 11, 2015select convert(varchar(50),getdate(),108)  --20:26:11select convert(varchar(50),getdate(),109) --08 11 2015 10:53:15:397PMselect convert(varchar(50),getdate(),110) --08-11-2015select convert(varchar(50),getdate(),111) --2015/08/11select convert(varchar(50),getdate(),112)  --20150811select convert(varchar(50),getdate(),113) --11 08 2015 22:52:17:143select convert(varchar(50),getdate(),114) --22:53:45:847select convert(varchar(50),getdate(),120)  --2015-08-11 22:45:34select 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') – 2015select datepart(month,'2015-08-11') – 8select 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 bhwhere 1 =1 ' +@strWhereexec(@strMain)

其他操作

try…catch…

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

select * into #temp_metercard from T_MeterCardbegin try    drop table #temp_metercardend trybegin catch    print '删除表失败'end catch

if…else…

if exists(select * from T_MeterCard where ID =0)    begin       print '存在'    endelse    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 =1declare @sum int =0while(@i<=10)begin    set @sum += @i    set @i += 1endprint @sum

事务

declare @ErrorCount int = 0begin tranupdate T_MeterCard set Note = 1/1 where ID =1set @ErrorCount+=@@ERRORupdate T_MeterCard set Note = 1/0 where ID =2set @ErrorCount+=@@ERRORif(@ErrorCount>0)    begin       rollback tran    endelse    begin       commit tran    end

存储过程

无返回值的存储过程

--创建存储过程create proc proc_sum1@num1 int,@num2 intasdeclare @result intset @result = @num1 + @num2print @result--调用存储过程exec proc_sum1 1,2

带返回值的存储过程

--创建存储过程create proc proc_sum2@num1 int,@num2 int,@result int outputasset @result = @num1 + @num2--调用存储过程declare @r intexec proc_sum 1,2,@r outputprint @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