ms sqlserver常用sql语句

获取SqlServer中表结构

 SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length 
FROM syscolumns, systypes 
WHERE syscolumns.xusertype = systypes.xusertype 
AND syscolumns.id = object_id('你的表名')
 
-- 单独查询表递增字段
select [name] from syscolumns where 
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1

-- 获取表主外键约束
exec sp_helpconstraint   '你的表名'

自定义函数递归调用

此方法适用于无限级分类情况下取出所有的父分类数据

CREATE function sp_GetAllParentByClassID
(  
   @classID int --参数
)
returns varchar(500)
as
begin
declare @ParentClassID varchar(15) --变量父ID
declare @result varchar(500)    --变量@result
set  @result = '' 
--首先根据传入的ClassID获取其父ID，ParentClassID
 SELECT    @ParentClassID = ParentClassID  FROM tClass 
    where  ClassID = @classID
if (@ParentClassID <> 0)--如果不是根节点
begin            
    -- 再将@ParentClassID父ID作为ClassID传入 进行自调用
     set @result =  dbo.sp_GetAllParentByClassID(@ParentClassID) + @ParentClassID+'_' 
    
end
    return  @result
end

有的SQL版本运行上述SQL语句在dbo.sp_GetAllParentByClassID(@ParentClassID)会报错。
原因是，此时正的创建dbo.sp_GetAllParentByClassID函数，而还未创建，
在又在此处调用dbo.sp_GetAllParentByClassID(@ParentClassID)，因此会提示没有这个对象，
解决办法是先去掉dbo.sp_GetAllParentByClassID(@ParentClassID)创建后再Alter修改 即可！

-- 如何将exec执行结果放入变量 num 中(自定义sql语句传出参数)

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num

统计类

SELECT ZIP FROM Customers WHERE State = ''KY''  GROUP BY ALL ZIP
SELECT ZIP FROM Customers WHERE State = ''KY''  GROUP BY ZIP
SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1
SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY OrderID
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

-----------------------------------
当CUBE的结果令人迷惑时（它经常是这样），可以添加一个GROUPING函数，如下所示：

SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

结果中每行包含两个额外的值：
值1表示左边的值是一个统计值，是ROLLUP或CUBE的操作符。
值0表示左边的值是一条由最初的GROUP BY语句产生的详细记录。
----------------

SELECT region, SUM(population), SUM(area)FROM bbc GROUP BY region HAVING SUM(area)>1000000

-------------------------------------

Select     
  课程名,   
  [成绩>=85]=SUM(Case   When   成绩>=85   Then   1   Else   0   End)   ,   
  [85>成绩>70]=SUM(Case   When   成绩>=70   And   成绩<85   Then   1   Else   0   End)   ,  
  [成绩<60]=SUM(Case   When   成绩<60   Then   1   Else   0   End)   ,   
  总人数=Count(1)   
  from   TableName   
  Group   By   课程名   
  Order   By   课程名

分组分段统计

select
    t.fromaccountid,
    onglogtimes=sum(case when t.logtimes1>0 then 1 else 0 end),
    twologtimes=sum(case when t.logtimes2=2 then 1 else 0 end),
    regnum     =sum(t.regStatus),
    t.regdate
from
(select
        a.fromaccountid,
        count(1) as logtimes1,                                                         --当日有登录记录的统计
        sum(case when datediff(mm,regdate,datetime)=0 then 1 else 0 end) as logtimes2,  --在注册当月登录的统计
        max(case when datediff(dd,regdate,datetime)=0 then 1 else 0 end) as regStatus,  --当日为注册操作的统计
        convert(char(10),a.regdate,120) as regdate
     from
         vgameuser a,loginlog b
     where
         a.accountid=b.playerid
     group by
         a.fromaccountid,convert(char(10),a.regdate,120))t
group by
    t.fromaccountid,t.regdate

活跃时段统计

DECLARE @BeginDate7 DATETIME
	DECLARE @BeginDate365 DATETIME
	DECLARE @EndDate DATETIME
	SET @BeginDate7=dateadd(day,datediff(day,6,getdate()),0)
	SET @BeginDate365=DATEADD(MONTH,-6,GETDATE())
	SET @EndDate =GETDATE()
	

	--最近7日每日注册数
	SELECT COUNT(1) AS Nums,CONVERT(VARCHAR(10),RegTime,120) AS CountDate FROM dbo.T_User WHERE RegTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(10),RegTime,120) 

	--最近1年每月注册数
	SELECT COUNT(1) AS Nums,CONVERT(VARCHAR(7),RegTime,120) AS CountDate FROM dbo.T_User WHERE RegTime BETWEEN @BeginDate365 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(7),RegTime,120) 

	--最近7日每日活跃数,按有登录算活跃
	SELECT COUNT(DISTINCT UserId) AS Nums,CONVERT(VARCHAR(10),InitTime,120) AS CountDate FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(10),InitTime,120) 
	 
	 --最近7日每日活跃时段 用with优化
	; --这个分号一定要，除非这句是第一句
	WITH temp AS 
	( 
	SELECT CONVERT(VARCHAR(13),InitTime,120) AS CountDate,COUNT(DISTINCT UserId) AS Nums FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(13),InitTime,120)
	)

	SELECT * FROM 
	 temp a WHERE CountDate IN 
	 (SELECT TOP 1 CountDate FROM temp b WHERE LEFT(CountDate,10)=LEFT(a.CountDate,10) ORDER BY Nums desc)
	 
	 
	--最近7日每日活跃时段 写法二 此sql待优化 每天只有一条记录，
	 SELECT * FROM 
	 (SELECT CONVERT(VARCHAR(13),InitTime,120) AS CountDate,COUNT(DISTINCT UserId) AS Nums FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(13),InitTime,120))a WHERE CountDate IN 
	 (SELECT TOP 1 CountDate FROM (SELECT CONVERT(VARCHAR(13),InitTime,120) AS CountDate,COUNT(DISTINCT UserId) AS Nums FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(13),InitTime,120))b WHERE LEFT(CountDate,10)=LEFT(a.CountDate,10) ORDER BY Nums desc)
	 

	 
	--最近7日每日活跃时段 写法三 此sql待优化 此写法对于某天有多个时段活跃用户数相同时会有N条记录，而不是每天一条
	SELECT TOP 30 b.CountDate,b.Nums FROM (
SELECT CONVERT(VARCHAR(10),CountDate,120) AS CountDate,MAX(Nums) AS Nums FROM(
SELECT CONVERT(VARCHAR(13),InitTime,120) AS CountDate,COUNT(DISTINCT UserId) AS Nums FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(13),InitTime,120))a GROUP BY CONVERT(VARCHAR(10),CountDate,120))a INNER JOIN (
SELECT CONVERT(VARCHAR(13),InitTime,120) AS CountDate,COUNT(DISTINCT UserId) AS Nums FROM dbo.T_UserLog WHERE InitTime BETWEEN @BeginDate7 AND @EndDate
	 GROUP BY CONVERT(VARCHAR(13),InitTime,120))b ON a.Nums=b.Nums AND a.CountDate=CONVERT(VARCHAR(10),b.CountDate,120)
	 ORDER BY b.CountDate

表数据去重

select * from T_Activity a inner join (
select name,address,max(id) as id from T_Activity group by name,address having count(1)>1)b on a.name=b.name and a.address=b.address where a.id<b.id

delete T_Activity from T_Activity a inner join (
select name,address,max(id) as id from T_Activity group by name,address having count(1)>1)b on a.name=b.name and a.address=b.address where a.id<b.id

修改表结构，增加，修改，删除字段

sqlserver 修改表，修改表字段
-- 修改表名

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

-- 修改字段名

EXEC sp_rename '[表名].[字段名]','新字段名' ;


-- 添加字段

alter table 表名 add 列名 字段类型;

// 如果字段不为空，需要先添加一个默认值

alter table 表名 add 列名 字段类型 not null default 0


-- 修改字段类型

alter table 表名 alter column 字段名 字段类型;

 

-- 删除字段

alter table 表名 drop column 字段名;

产生连续的时间表，如连续年，月，日等

-- 按年产生连续的
SELECT
 substring(CONVERT(NVARCHAR(10), DateAdd(YEAR, number, '2018-01-01'),120),1,4) AS GroupDay,type 
FROM
 master..spt_values 
WHERE type = 'p' AND number <= DateDiff(YEAR, '2018-01-01', getdate()) 
-- 按月产生连续的
SELECT
 substring(CONVERT(NVARCHAR(10), DateAdd(MONTH, number, '2019-01-01'),120),1,7) AS GroupDay,type 
FROM
 master..spt_values 
WHERE type = 'p' AND number <= DateDiff(MONTH, '2018-01-01', '2019-01-01') 
-- 按天产生连续的
SELECT
 CONVERT(NVARCHAR(10), DateAdd(day, number, '2019-01-01'),120) AS GroupDay,type 
FROM
 master..spt_values 
WHERE type = 'p' AND number <= DateDiff(day, '2019-01-01', getdate()) 
-- 按小时产生连续的
SELECT
 substring(convert(char(32),DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),120),1,16) AS GroupDay,type 
FROM
 master..spt_values 
WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),CONCAT('2019-01-18',' ', '23:00'))>=0

查询库，表信息

---1.查询数据库中的所有数据库名：
SELECT Name FROM Master..SysDatabases ORDER BY Name  

---2.查询某个数据库中所有的表名：
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

--3.查询表结构信息，表说明，字段信息
SELECT 表名称= d.name,
       表说明= isnull(f.value,''),
       排序号= a.colorder,
       列名称= a.name,
       列说明= isnull(g.[value],''),
       标识列= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end,
       主键列= case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
       列类型= b.name,
       字节数= a.length,
       列长度= COLUMNPROPERTY(a.id,a.name,'PRECISION'),
       小数位= isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
       允许空= case when a.isnullable=1 then 1 else 0 end,
       默认值= isnull(e.text,'')
FROM  syscolumns a
LEFT JOIN  systypes b on a.xusertype=b.xusertype
INNER JOIN sysobjects d on  a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
LEFT JOIN  syscomments e on  a.cdefault=e.id
LEFT JOIN  sys.extended_properties g on  a.id=G.major_id and a.colid=g.minor_id  
LEFT JOIN  sys.extended_properties f on  d.id=f.major_id and f.minor_id=0
--where d.name='表名'
ORDER BY 表名称 ASC,排序号 ASC

--4.查询表的行记录数，按大小排序
SELECT a.name,b.rows
FROM sysobjects AS a INNER JOIN 
sysindexes AS b ON a.id = b.id 
WHERE (a.type = 'u') AND (b.indid IN (0, 1)) 
ORDER BY b.rows DESC

--- end ---