前言
看了一些书,同时网上参考一些文章,整理的关于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