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