sql语法积累——不定时更新

1.给空字段赋值

SELECT ISNULL(column ,'无名') FROM table

coalesce(靓号等级,'无号')

2.取当月月初的时间

select convert(varchar,dateadd(day,-day(getdate())+1,getdate()),112) 

3.取当月月末时间

select convert(varchar,dateadd(day,-day(getdate()),dateadd(month,1,getdate())),112) 

4.取当前月份的天数

select datediff(day,dateadd(day,-day(getdate()),getdate()),dateadd(day,-day(getdate()),dateadd(month,1,getdate()))) 

5.取上个月最后一天

SELECT CONVERT(int,convert(varchar(10),dateadd(d,-1,dateadd(m,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))),112))

6.取上个月第一天

select CONVERT(int, CONVERT(varchar(10),DATEADD(m,-1 ,dateadd(dd,-day(getdate())+1,getdate())) ,112))

7.查询当天：   

select * from info where DateDiff(dd,datetime,getdate())=0   

8.查询重复字段的数量

select name from emp group by name having count(*)>1

9.去重

 SELECT Distinct列名称 FROM 表名称

10.sql分组取第一条数据的一个方法：

select * from 
(
select row_number() over(partition by ID order by ID) as rownum , *
from table
) as temp
where temp.rownum = 1

11.sql server 查询字段不为null的所有数据

select * from table where column is not null 

12.创建临时表

select*into 目标表名 from(创建源表) as t

13.系统当前月份-2

DATEDIFF(month,办理时间,GETDATE())=2

14.取分组后的第一条整行数据

select * from 

(
select  
a.住房单元编号,a.号码,a.近三月月均消费,row_number() over(partition by a.住房单元编号 order by a.近三月月均消费 desc) as rn
from a 
) as a where rn<=1 

15.取两个时间段之间

between... and ...

16.多ID判断重复个数

select a.ID,a.name,count(*) as tasks from test1 as a,test2 as b
where CHARINDEX(cast(a.ID as nvarchar),B.tID)>0
group by a.ID,a.name
order by a.ID

 判断是否存在

charindex 

17.拆分字符串

 select a.value from  string_split('1,2,3',',')  a

18.新增列

alter table 表名 add 字段名 varchar(255)

19.算百分比

convert(varchar,Convert(decimal(18,2),tab1.A月净增/(A月净增+B月净增+C月净增)*100))+'%' as 新占

20.计算排名

RANK() OVER( ORDER BY Convert(decimal(18,2),((tab1.A月净增/(A月净增+B月净增+C月净增)*100)-(tab1.上月新占))) desc) 排名01

21.30天内的数据

DATEDIFF(dd,[时间],GETDATE())<=30

22.遇到以零做除数的错误

set ansi_warnings off
SET ARITHABORT off
SET ARITHIGNORE on

23.with ties 查出这一条的所有记录

select top(5) with ties 时间 from 表  group by 时间 order by 时间 desc

24.截取数字末尾带英文的字符

left(带宽,CHARINDEX('M',带宽)-1)

25.截取小数4位 不四舍五入

select substring(cast(1*1.0/3 as varchar),1,4)

26.除法  保留两位小数  四舍五入

round(cast(b1.宽带5月数量 as float)/31,2)

27.比较两个日期（datetime）的年月相差多少个月

DATEDIFF() 函数返回两个日期之间的时间
DATEDIFF(mm,startdate,enddate)

28.截取时间格式的年月日

substring(convert(varchar(102),create_date,23),1,7)

29.取一行数字的最大值

select Guid,(select Max(NewDate) from (values (Date1),(Date2),(Date3)) as #temp(NewDate)) as MaxDate from Demo
select Guid, max(NewDate) as MaxDate from Demo unpivot (NewDate for DateVal in (Date1,Date2,Date3)) as u group by Guid

30.根据姓名排序

select * from table order by name collate Chinese_PRC_CS_AS_KS_WS 

31.分组取前5个号码

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) AS row_num
  FROM your_table
) AS t
WHERE t.row_num <= 5

32.查询触发器

select * from sysobjects where xtype='TR'

33.查询数据库的所有索引、主外键信息

 select  a.name as tabname,a.object_id
    ,h.name as indexName,h.index_id,ic.column_id,c.name colName
    from  sys.objects    as  a
    right join sys.indexes  as h  on  a.object_id=h.object_id
    left join sys.index_columns ic on h.index_id=ic.index_id and ic.object_id=a.object_id
    left join sys.columns c on ic.column_id=c.column_id and c.object_id=a.object_id
    where  a.type<>'s' and a.type='U'
    --and isnull(h.name,'')='' --查看没有索引的表
    and a.is_ms_shipped<>1 --排除 dtproperties

34.建立索引

create unique index 索引名称 on table(编号)

35.视图简单例子

create view 视图_获取名称
as
select 名称 from 表


select * from 视图_获取名称

36.触发器简单例子

--对表进行插入或者更新操作，当价格不大于0时，触发器将激活，中止该操作并提示“价格不能小于等于0！”

create trigger 触发器价格限制 on 表
for insert,update
as
begin
if exists(select*from 表
where 价格<=0)
begin
print'价格不能小于等于0！'
rollback transaction
end
else
begin
print'插入或者更新成功！'
end
end

37.游标遍历循环

DECLARE @variable datatype --声明一个游标变量
DECLARE cursor_name CURSOR FOR
SELECT ...
FROM ...
WHERE ...  --选择需要遍历的数据集

OPEN cursor_name  --打开游标
FETCH NEXT FROM cursor_name INTO @variable  --读取第一行数据

WHILE @@FETCH_STATUS = 0
BEGIN
  --对每一行遍历执行相应的操作
  ...
  
  FETCH NEXT FROM cursor_name INTO @variable  --读取下一行数据
END

CLOSE cursor_name  --关闭游标
DEALLOCATE cursor_name  --释放游标所占用的资源

38.删除表中的列

ALTER TABLE 表名 DROP COLUMN 列名;

39.sqlserver group by有空值时 给空值设置默认的值

SELECT COALESCE(字段名, '默认值') AS column_name, COUNT(*) AS count
FROM table名
GROUP BY COALESCE(段, '默认值')

40.去掉首尾空格 

select ltrim(rtrim(' test '));

  ltrim 去掉字符串开头的空格

  rtrim 去掉字符串结尾处的空格

41.去掉中间空格

 select replace('s sss',' ','')

42.

-- 创建SQL Server登陆帐户

create login [test] with password='123456', default_database=[AccountingSystem] ;

--创建数据库用户

create user [test] for login [test] with default_schema=dbo

-- 授予用户所有权限

exec sp_addrolemember 'db_owner', 'test'

43.查看表字段的类型

SELECT name AS column_name , TYPE_NAME(system_type_id) AS column_type  FROM sys.columns WHERE object_id = OBJECT_ID(N'表名')

select a.name, b.name, c.name
from sysobjects a inner join syscolumns b
on a.id=b.id and a.xtype='U'
inner join systypes c
on b.xtype=c.xusertype
where a.name='表名'and b.name='字段名'

44.修改表名
 

EXEC sp_rename '旧表名', '新表名';

45.截取指定字符串后面的字符

select
op_code,policy_id,
 op_note,
 SUBSTRING(op_note,CHARINDEX('A',op_note),9) 名称   
from XXX