經典SQL語句收藏

[轉]經典SQL語句--收藏 http://blog.ourtw.com/article.php?tid_600.html
[個人收藏]經典SQL語句.值得收藏
精典的SQL語句，推薦收藏
在網上經常轉,常常看到有些人為了求得某些SQL語句而焦頭爛額,現在我特別把自己收藏的一些比較精典的SQL拿出來和大家分享一下

1. 行列轉換--普通

假設有張學產生績表(CJ)如下
Name   Subject   Result
張三   語文     80
張三   數學     90
張三   物理     85
李四   語文     85
李四   數學     92
李四   物理     82

想變成  
姓名   語文   數學   物理
張三   80   90   85
李四   85   92   82

declare @sql varchar(4000)
set @sql = ''select Name''
select @sql = @sql + '',sum(case Subject when ''''''+Subject+'''''' then Result end) [''+Subject+'']''
from (select distinct Subject from CJ) as a
select @sql = @sql+'' from test group by name''
exec(@sql)

2. 行列轉換--合并

有表A,
id pid
1   1
1   2
1   3
2   1
2   2
3   1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1

建立一個合并的函數
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''''
select @str=@str+'',''+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go

--調用自訂函數得到結果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一個資料表的所有列名

方法如下：先從SYSTEMOBJECT系統資料表中取得資料表的SYSTEMID,然後再SYSCOLUMN表中取得該資料表的所有列名。
SQL語句如下：
declare @objid int,@objname char(40)
set @objname = ''tablename''
select @objid = id from sysobjects where id = object_id(@objname)
select ''Column_name'' = name from syscolumns where id = @objid order by colid

是不是太簡單了？ 呵呵 不過經常用阿.

4. 通過SQL語句來更改使用者的密碼

修改別人的,需要sysadmin role  
EXEC sp_password NULL, ''newpassword'', ''User''

如果帳號為SA執行EXEC sp_password NULL, ''newpassword'', sa

5. 怎麼判斷出一個表的哪些欄位不允許為空白？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename

6. 如何在資料庫裡找到含有相同欄位的表？
a. 查已知列名的情況
SELECT b.name as TableName,a.name as columnname
From syscolumns   a INNER JOIN   sysobjects b  
ON a.id=b.id  
AND b.type=''U''  
AND a.name=''你的欄位名字''

b. 未知列名查所有在不同表出現過的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
  And o.type = ''U''
  And Exists (
    Select 1 From syscolumns s2  
    Where s1.name = s2.name  
    And s1.id <> s2.id
    )

7. 查詢第xxx行資料

假設id是主鍵：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用遊標也是可以的
fetch absolute [number] from [cursor_name]
行數為絕對行數

8. SQL Server日期計算
a. 一個月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上個月的最後一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最後一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最後一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一個星期一
select DATEADD(wk, DATEDIFF(wk,0,                                      
                    dateadd(dd,6-datepart(day,getdate()),getdate())    
                                                                ), 0)    
i. 本年的最後一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
-----------------------------------------------------------------------
1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2.資料庫加密:
select encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密後密碼'') = 1--相同；否則不相同 encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密後密碼'') = 1--相同；否則不相同

3.取回表中欄位:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A''
set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A''
exec (@sql)

4.查看硬碟分區:
EXEC master..xp_fixeddrives

5.比較A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
  =
  (select checksum_agg(binary_checksum(*)) from B)
print ''相等''
else
print ''不相等''

6.殺掉所有的事件探察器進程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ''kill ''+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(''SQL profiler'',N''SQL 事件探查器'')
EXEC sp_msforeach_worker ''?''

7.記錄搜尋:
開頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到結尾記錄
Select Top N * From 表 Order by ID Desc

8.如何修改資料庫的名稱:
sp_renamedb ''old_name'', ''new_name''

9：擷取當前資料庫中的所有使用者表
select Name from sysobjects where xtype=''u'' and status>=0

10：擷取某一個表的所有欄位
select name from syscolumns where id=object_id(''表名'')

11：查看與某一個表相關的視圖、預存程序、函數
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ''%表名%''

12：查看當前資料庫中所有預存程序
select name as 預存程序名稱 from sysobjects where xtype=''P''

13：查詢使用者建立的所有資料庫
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=''sa'')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14：查詢某一個表的欄位和資料類型
select column_name,data_type from information_schema.columns
where table_name = ''表名''

[n].[標題]:
Select * From TableName Order By CustomerName

[n].[標題]:
Select * From TableName Order By CustomerName