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

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

ms sqlserver常用sql語句

擷取SqlServer中表結構

自定義函數遞歸調用

統計類

分組分段統計

活躍時段統計

表資料去重

修改表結構，增加，修改，删除字段

産生連續的時間表，如連續年，月，日等

查詢庫，表資訊

繼續閱讀

（C# 程式設計指南）

使用Windbg調試.Net應用程式

解決"T-SQL無法附加到程序"錯誤的補充,T-SQLcan not attach to process

Oracle的基本操作

可變參數宏， Variadic Macros

c寫檔案

不用iconv函數實作UTF-8編碼轉換GB2312的PHP函數

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

C++ 第十五周報告1--《冒泡法排序》

無元件上傳圖檔到資料庫中，最完整解決方案

neo4j之cypher使用文檔

GridView終極用法(一)

sqlServer根據經緯查距離