討論區 › Database › sql常用語句(網上收集)

JDP 發表於 2006-11-9 11:01:01

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
11,2,3
21,2
31

創建一個合併的函數
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語句來更改用戶的密碼

修改別人的,需要sysadminrole
EXECsp_passwordNULL,'newpassword','User'

如果帳號為SA執行EXECsp_passwordNULL,'newpassword',sa

5. 怎麼判斷出一個表的哪些字段不允許為空？

selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhere
IS_NULLABLE='NO'andTABLE_NAME=tablename

6. 如何在數據庫裡找到含有相同字段的表？
a. 查已知列名的情況
SELECTb.nameasTableName,a.nameascolumnname
Fromsyscolumns    aINNERJOIN    sysobjectsb
ONa.id=b.id
ANDb.type='U'
ANDa.name='你的字段名字'

b. 未知列名查所有在不同表出現過的列名
Selecto.nameAstablename,s1.nameAscolumnname
Fromsyscolumnss1,sysobjectso
Wheres1.id=o.id
   Ando.type='U'
   AndExists(
       Select1Fromsyscolumnss2
       Wheres1.name=s2.name
       Ands1.id<>s2.id
       )

7. 查詢第xxx行數據

假設id是主鍵：
select*
from(selecttopxxx*fromyourtable)aa
wherenotexists(select1from(selecttopxxx-1*from
yourtable)bbwhereaa.id=bb.id)

如果使用游標也是可以的
fetchabsolutefrom
行數為絕對行數

8. SQL Server日期計算
a. 一個月的第一天
SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
b. 本周的星期一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
c. 一年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
d. 季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
e. 上個月的最後一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
f. 去年的最後一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
g. 本月的最後一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
h. 本月的第一個星期一
selectDATEADD(wk,DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())

                         ),0)
i. 本年的最後一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。

Sql Server實用操作小技巧集合
包括安裝時提示有掛起的操作、收縮數據庫、壓縮數據庫、轉移數據庫給新用戶以已存在用戶權限、檢查備份集、修復數據庫等

（一）掛起操作
在安裝Sql或sp補丁的時候系統提示之前有掛起的安裝操作，要求重啟，這裡往往重啟無用，解決辦法：
到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
刪除PendingFileRenameOperations

（二）收縮數據庫
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮數據和日誌
DBCC SHRINKDB
DBCC SHRINKFILE

（三）壓縮數據庫
dbcc shrinkdatabase(dbname)

（四）轉移數據庫給新用戶以已存在用戶權限
exec sp_change_users_login 'update_one','newname','oldname'
go

（五）檢查備份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

（六）修復數據庫
ALTER DATABASE SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE SET MULTI_USER
GO

--CHECKDB 有3個參數:
--REPAIR_ALLOW_DATA_LOSS
-- 執行由 REPAIR_REBUILD
完成的所有修復，包括對行和頁進行分配和取消分配以改正分配錯誤、結構行或頁的錯誤，以及刪除已損壞的文本對象。這些修復可能會導致一些數據丟失。修復操作可以-在用戶事務下完成以允許用戶回滾所做的更改。如果回滾修復，則數據庫仍會含有錯誤，應該從備份進行恢復。如果由於所提供修復等級的緣故遺漏某個錯誤的修復，則將-遺漏任何取決於該修復的修復。修復完成後，備份數據庫。

--REPAIR_FAST
進行小的、不耗時的修復操作，如修復非聚集索引中的附加鍵。這些修復可以很快完成，並且不會有丟失數據的危險。

--REPAIR_REBUILD 執行由 REPAIR_FAST
完成的所有修復，包括需要較長時間的修復（如重建索引）。執行這些修復時不會有丟失數據的危險。

--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

SQL SERVER日誌清除的兩種方法
在使用過程中大家經常碰到數據庫日誌非常大的情況，在這裡介紹了兩種處理方法......

方法一

一般情況下，SQL數據庫的收縮並不能很大程度上減小數據庫大小，其主要作用是收縮日誌大小，應當定期進行此操作以免數據庫日誌過大
1、設置數據庫模式為簡單模式：打開SQL企業管理器，在控制台根目錄中依次點開Microsoft
SQL Server-->SQL
Server組-->雙擊打開你的服務器-->雙擊打開數據庫目錄-->選擇你的數據庫名稱（如論壇數據庫Forum）-->然後點擊右鍵選擇屬性-->選擇選-項-->在故障還原的模式中選擇"簡單"，然後按確定保存
2、在當前數據庫上點右鍵，看所有任務中的收縮數據庫，一般裡面的默認設置不用調整，直接點確定
3、收縮數據庫完成後，建議將您的數據庫屬性重新設置為標準模式，操作方法同第一點，因為日誌在一些異常情況下往往是恢復數據庫的重要依據

方法二

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE tablename -- 要操作的數據庫名
SELECT @LogicalFileName = 'tablename_log', -- 日誌文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想設定的日誌文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has
not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

刪除數據庫中重複數據的幾個方法
數據庫的使用過程中由於程序方面的問題有時候會碰到重複數據，重複數據導致了數據庫部分設置不能正確設置......

方法一

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名
group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二

有兩個意義上的重複記錄，一是完全重複的記錄，也即所有字段均重複的記錄，二是部分關鍵字段重複的記錄，比如Name字段重複，而其他字段不一定重複或都重複可-以忽略。
1、對於第一種重複，比較容易解決，使用
select distinct * from tableName
就可以得到無重複記錄的結果集。
如果該表需要刪除重複的記錄（重複記錄保留1條），可以按以下方法刪除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
發生這種重複的原因是表設計不周產生的，增加唯一索引列即可解決。

2、這類重複問題通常要求保留重複記錄中的第一條記錄，操作方法如下
假設有重複的字段為Name,Address，要求得到這兩個字段唯一的結果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2) ...
閱讀更多 »
回覆   給這篇留言評等：Text for clearing space

寄件人：ncn...@gmail.com - 檢視個人簡介
日期：2006年4月17日(星期一) 上午11時19分
電郵地址：   ncn...@gmail.com
尚未評比評等：   
顯示選項
回覆 | 回覆作者 | 轉寄 | 列印 | 顯示個別留言 | 顯示原始檔 | 舉報濫用行為 | 尋找此作者的訊息

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
11,2,3
21,2
31

創建一個合併的函數
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語句來更改用戶的密碼

修改別人的,需要sysadminrole
EXECsp_passwordNULL,'newpassword','User'

如果帳號為SA執行EXECsp_passwordNULL,'newpassword',sa

5. 怎麼判斷出一個表的哪些字段不允許為空？

selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhere
IS_NULLABLE='NO'andTABLE_NAME=tablename

6. 如何在數據庫裡找到含有相同字段的表？
a. 查已知列名的情況
SELECTb.nameasTableName,a.nameascolumnname
Fromsyscolumns    aINNERJOIN    sysobjectsb
ONa.id=b.id
ANDb.type='U'
ANDa.name='你的字段名字'

b. 未知列名查所有在不同表出現過的列名
Selecto.nameAstablename,s1.nameAscolumnname
Fromsyscolumnss1,sysobjectso
Wheres1.id=o.id
   Ando.type='U'
   AndExists(
       Select1Fromsyscolumnss2
       Wheres1.name=s2.name
       Ands1.id<>s2.id
       )

7. 查詢第xxx行數據

假設id是主鍵：
select*
from(selecttopxxx*fromyourtable)aa
wherenotexists(select1from(selecttopxxx-1*from
yourtable)bbwhereaa.id=bb.id)

如果使用游標也是可以的
fetchabsolutefrom
行數為絕對行數

8. SQL Server日期計算
a. 一個月的第一天
SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
b. 本周的星期一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
c. 一年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
d. 季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
e. 上個月的最後一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
f. 去年的最後一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
g. 本月的最後一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
h. 本月的第一個星期一
selectDATEADD(wk,DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())

                         ),0)
i. 本年的最後一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。

Sql Server實用操作小技巧集合
包括安裝時提示有掛起的操作、收縮數據庫、壓縮數據庫、轉移數據庫給新用戶以已存在用戶權限、檢查備份集、修復數據庫等

（一）掛起操作
在安裝Sql或sp補丁的時候系統提示之前有掛起的安裝操作，要求重啟，這裡往往重啟無用，解決辦法：
到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
刪除PendingFileRenameOperations

（二）收縮數據庫
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮數據和日誌
DBCC SHRINKDB
DBCC SHRINKFILE

（三）壓縮數據庫
dbcc shrinkdatabase(dbname)

（四）轉移數據庫給新用戶以已存在用戶權限
exec sp_change_users_login 'update_one','newname','oldname'
go

（五）檢查備份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

（六）修復數據庫
ALTER DATABASE SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE SET MULTI_USER
GO

--CHECKDB 有3個參數:
--REPAIR_ALLOW_DATA_LOSS
-- 執行由 REPAIR_REBUILD
完成的所有修復，包括對行和頁進行分配和取消分配以改正分配錯誤、結構行或頁的錯誤，以及刪除已損壞的文本對象。這些修復可能會導致一些數據丟失。修復操作可以-在用戶事務下完成以允許用戶回滾所做的更改。如果回滾修復，則數據庫仍會含有錯誤，應該從備份進行恢復。如果由於所提供修復等級的緣故遺漏某個錯誤的修復，則將-遺漏任何取決於該修復的修復。修復完成後，備份數據庫。

--REPAIR_FAST
進行小的、不耗時的修復操作，如修復非聚集索引中的附加鍵。這些修復可以很快完成，並且不會有丟失數據的危險。

--REPAIR_REBUILD 執行由 REPAIR_FAST
完成的所有修復，包括需要較長時間的修復（如重建索引）。執行這些修復時不會有丟失數據的危險。

--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

SQL SERVER日誌清除的兩種方法
在使用過程中大家經常碰到數據庫日誌非常大的情況，在這裡介紹了兩種處理方法......

方法一

一般情況下，SQL數據庫的收縮並不能很大程度上減小數據庫大小，其主要作用是收縮日誌大小，應當定期進行此操作以免數據庫日誌過大
1、設置數據庫模式為簡單模式：打開SQL企業管理器，在控制台根目錄中依次點開Microsoft
SQL Server-->SQL
Server組-->雙擊打開你的服務器-->雙擊打開數據庫目錄-->選擇你的數據庫名稱（如論壇數據庫Forum）-->然後點擊右鍵選擇屬性-->選擇選-項-->在故障還原的模式中選擇"簡單"，然後按確定保存
2、在當前數據庫上點右鍵，看所有任務中的收縮數據庫，一般裡面的默認設置不用調整，直接點確定
3、收縮數據庫完成後，建議將您的數據庫屬性重新設置為標準模式，操作方法同第一點，因為日誌在一些異常情況下往往是恢復數據庫的重要依據

方法二

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE tablename -- 要操作的數據庫名
SELECT @LogicalFileName = 'tablename_log', -- 日誌文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想設定的日誌文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has
not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

刪除數據庫中重複數據的幾個方法
數據庫的使用過程中由於程序方面的問題有時候會碰到重複數據，重複數據導致了數據庫部分設置不能正確設置......

方法一

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名
group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二

有兩個意義上的重複記錄，一是完全重複的記錄，也即所有字段均重複的記錄，二是部分關鍵字段重複的記錄，比如Name字段重複，而其他字段不一定重複或都重複可-以忽略。
1、對於第一種重複，比較容易解決，使用
select distinct * from tableName
就可以得到無重複記錄的結果集。
如果該表需要刪除重複的記錄（重複記錄保留1條），可以按以下方法刪除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
發生這種重複的原因是表設計不周產生的，增加唯一索引列即可解決。

2、這類重複問題通常要求保留重複記錄中的第一條記錄，操作方法如下
假設有重複的字段為Name,Address，要求得到這兩個字段唯一的結果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2) ...

Reference: http://groups.google.com/group/procedure/browse_thread/thread/3b75b37280cd7f99/b8fd55f63ee86a1c?lnk=st&q=varchar%E8%BD%89int+sql+server&rnum=9&hl=zh-TW#b8fd55f63ee86a1c

查看完整版本: sql常用語句(網上收集)