-- SQL?Server：
Select ? TOP ?N? * ? From ? TABLE ? Order ? By ? NewID ()?

-- 開頭到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

-- 選擇10從到15的記錄
select ? top ? 5 ? * ? from ?( select ? top ? 15 ? * ? from ? table ? order ? by ?id? asc ) A? order? by ?id? desc ?

-- N到結尾記錄
Select ? Top ?N? * ? From ?表Order? by ?ID? Desc
?

? ?

-- 顯示最后5條記錄,但是顯示的順序必須為5,6,7,8,9,10,而不是10,9,8,7,6,5 如下解決方法:

select top 5 from test where id in ( select top 5 from test order by id desc ) order by id asc


-- 通過這個問題也能總結出4-10條,5-100條這種限定一定范圍內的sql語句的寫法:

select top < 末端ID - 頂端ID + 1 > * from < 表名 > where ID not in ( select top < 頂端ID - 1 > ) ID from < 表名 > )

-- 例如:4-10條就應該寫成
select top 10 - 4 + 1 * from test where id not in ( select top 4 - 1 id from test)

? ?

?上一篇: select top 1 * from [news_table] where [新聞標識列]<當前id號 where ......
?下一篇: select top 1 * from [news_table] where [新聞標識列]>當前id號 where ...... order by [新聞標識列] desc

?

?

?

一個表中的 Id 有多個記錄，把所有這個 id 的記錄查出來，并顯示共有多少條記錄數。
------------------------------------------
select id, Count （ *) from tb group by id having count(*)>1


-- 兩條記錄完全相同，如何刪除其中一條
set ? rowcount = 1 ?
delete ? from ?thetablename? where ?id = @duplicate_id -- @duplicate_id為重復值的id?


-- 模糊查詢
select ? * ?? from ?product? where ?detail? like ? ' %123.jpg% '
-- 替換字段里面部分內容
update ?product? set ?detail = replace ( cast (detail? as ? varchar ( 8000 )), ' abc.jpg ' , ' efg.jpg ' )?

?

-- 日期轉換參數,值得收藏
select ? CONVERT ( varchar ,? getdate (),? 120 ?)
2004 - 09 - 12 ? 11 : 06 : 08 ?

select ? replace ( replace ( replace ( CONVERT ( varchar ,? getdate (),? 120 ?), ' - ' , '' ), ' ? ' , '' ), ' : ' , '' )
20040912110608

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 111 ?)
2004 / 09 / 12

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 112 ?)
20040912

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 102 ?)
2004.09 . 12

?

-- 一個月第一天
SELECT ??? DATEADD (mm,??? DATEDIFF (mm, 0 , getdate ()),??? 0 )?? -- ??2009-06-01?00:00:00.000

-- 當天
select ?? * ? from ?product?? where ? DateDiff ( day ,modiDate, GetDate ()) > 1 ?

-- 如何查詢本日、本月、本年的記錄SQL
本年：
select ? * ? from ?loanInfo? where ? year (date) = year ( getdate ())?
本月：
select ? * ? from ?loanInfo? where ? year (date) = year ( getDate ())? And ? month (date) = month ( getdate ())?
本日：
select ? * ? from ?loanInfo? where ? year (date) = year ( getDate ())? And ? month (date) = month ( getdate ())? and ? Day (date) = Day ( getDate ())???


本周的星期一??? SELECT ?? DATEADD (wk,?? DATEDIFF (wk, 0 , getdate ()),?? 0 )??
?
一年的第一天?? SELECT ?? DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 )??
?
季度的第一天??? SELECT ?? DATEADD (qq,?? DATEDIFF (qq, 0 , getdate ()),?? 0 )??
?
當天的半夜??? SELECT ?? DATEADD (dd,?? DATEDIFF (dd, 0 , getdate ()),?? 0 )??
?
上個月的最后一天?
?
???????這是一個計算上個月最后一天的例子。它通過從一個月的最后一天這個例子上減去毫秒來獲得。有一點要記住，在Sql??Server中時間是精確到毫秒。這就是為什么我需要減去毫秒來獲得我要的日期和時間。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (mm, 0 , getdate ()),?? 0 ))??
?
???????計算出來的日期的時間部分包含了一個Sql??Server可以記錄的一天的最后時刻(“: 59 : 59 : 997 ”)的時間。?
?
去年的最后一天?
?
???????連接上面的例子，為了要得到去年的最后一天，你需要在今年的第一天上減去毫秒。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 ))??
?
本月的最后一天?
?
???????現在，為了獲得本月的最后一天，我需要稍微修改一下獲得上個月的最后一天的語句。修改需要給用DATEDIFF比較當前日期和“ - 01 - 01 ”返回的時間間隔上加。通過加個月，我計算出下個月的第一天，然后減去毫秒，這樣就計算出了這個月的最后一天。這是計算本月最后一天的SQL腳本。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (m, 0 , getdate ()) + 1 ,?? 0 ))??
?
本年的最后一天?
?
???????你現在應該掌握這個的做法，這是計算本年最后一天腳本?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()) + 1 ,?? 0 ))。?
?
本月的第一個星期一?
?
???????好了，現在是最后一個例子。這里我要計算這個月的第一個星期一。這是計算的腳本。?
?
????????? select ?? DATEADD (wk,?? DATEDIFF (wk, 0 ,??????????????????????????????????????????????????????????
??????????????????????????????? dateadd (dd, 6 - datepart ( day , getdate ()), getdate ())????????
?????????????????????????????????????????????????????????????????????????????????????????????????),?? 0 )??????????????????????????
?
???????在這個例子里，我使用了“本周的星期一”的腳本，并作了一點點修改。修改的部分是把原來腳本中“ getdate ()”部分替換成計算本月的第天，在計算中用本月的第天來替換當前日期使得計算可以獲得這個月的第一個星期一。?


-- 刪除一個月前,三個月前,?6個月前,一年前的數據?

DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 1
DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 3
DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 6
DELETE ? FROM ?表名WHERE? datediff (YY,?AddTime, GETDATE ())? > ? 1

-- -------------------------------------------------------------??
附錄，其他日期處理方法?
?
1 )去掉時分秒?
declare ??@?? datetime ??
set ??@?? = ?? getdate ()?? -- '2003-7-1??10:00:00'??
SELECT ??@, DATEADD ( day ,?? DATEDIFF ( day , 0 ,@),?? 0 )??
?
2 ）顯示星期幾?
select ?? datename (weekday, getdate ())????
?
3 ）如何取得某個月的天數?
declare ?? @m ?? int ??
set ?? @m = 2 ?? -- 月份?
select ???? datediff ( day , ' 2003- ' + cast ( @m ?? as ?? varchar ) + ' -15 ' ??, ' 2003- ' + cast ( @m + 1 ???? as ?? varchar ) + ' -15 ' )??

另外，取得本月天數?
select ???? datediff ( day , cast ( month ( GetDate ())?? as ?? varchar ) + ' - ' + cast ( month ( GetDate ())?? as ?? varchar ) + ' -15 ' ??, cast ( month ( GetDate ())?? as ?? varchar ) + ' - ' + cast ( month ( GetDate ()) + 1 ???? as ?? varchar ) + ' -15 ' )??

任意月份的最大天數
select ?? day ( dateadd (dd, - 1 , dateadd (mm, 1 , Dateadd (mm, datediff (mm, 0 , getdate ()), 0 ))))?


或者使用計算本月的最后一天的腳本，然后用DAY函數區最后一天?
SELECT ?? Day ( dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (m, 0 , getdate ()) + 1 ,?? 0 )))??
?
4 ）判斷是否閏年：?

SELECT ?? case ?? day ( dateadd (mm,?? 2 ,?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 ))))?? when ?? 28 ?? then ?? ' 平年 ' ?? else ?? ' 閏年 ' ?? end ??

或者?
select ?? case ?? datediff ( day , datename ( year , getdate ()) + ' -02-01 ' , dateadd (mm, 1 , datename ( year , getdate ()) + ' -02-01 ' ))??
when ?? 28 ?? then ?? ' 平年 ' ?? else ?? ' 閏年 ' ?? end ??
?
5 ）一個季度多少天?
declare ?? @m ?? tinyint , @time ?? smalldatetime ??
select ?? @m = month ( getdate ())??
select ?? @m = case ?? when ?? @m ?? between ?? 1 ?? and ?? 3 ?? then ?? 1 ??
??????????????????????? when ?? @m ?? between ?? 4 ?? and ?? 6 ?? then ?? 4 ??
??????????????????????? when ?? @m ?? between ?? 7 ?? and ?? 9 ?? then ?? 7 ??
??????????????????????? else ?? 10 ?? end ??
select ?? @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 ' ??
select ?? datediff ( day , @time , dateadd (mm, 3 , @time ))???


?

? 1 、確定某年某月有多少天

實現原理：先利用DATEDIFF取得當前月的第一天，再將月份加一取得下月第一天，然后減去分鐘，再取日期的天數部分，即為當月最大日期，也即當月天數

CREATE ? FUNCTION ?DaysInMonth?(? @date ? datetime ?)? Returns ? int
AS
BEGIN
? RETURN ? Day ( dateadd (mi, - 3 , DATEADD (m,? DATEDIFF (m, 0 , @date ) + 1 , 0 )))
END

調用示例：

select ?dbo.DaysInMonth?( ' 2006-02-03 ' )?

( 2 )計算哪一天是本周的星期一

SELECT ? DATEADD (week,? DATEDIFF (week, ' 1900-01-01 ' , getdate ()),? ' 1900-01-01 ' )?? -- 返回-11-06?00:00:00.000
或
SELECT ? DATEADD (week,? DATEDIFF (week, 0 , getdate ()), 0 )??????

( 3 )當前季度的第一天

SELECT ? DATEADD (quarter,? DATEDIFF (quarter, 0 , getdate ()),? 0 )—返回 - 10 - 01 ? 00 : 00 : 00.000 ?

( 4 )一個季度多少天

declare ? @m ? tinyint , @time ? smalldatetime ?

select ? @m = month ( getdate ())?
select ? @m = case ? when ? @m ? between ? 1 ? and ? 3 ? then ? 1 ?
??????????????????????? when ? @m ? between ? 4 ? and ? 6 ? then ? 4 ?
??????????????????????? when ? @m ? between ? 7 ?? and ? 9 ? then ? 7 ?
??????????????????????? else ? 10 ? end ?

select ? @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 ' ?
select ? datediff ( day , @time , dateadd (mm, 3 , @time ))?—返回



?

1 .按姓氏筆畫排序:?
Select ? * ? From ?TableName? Order ? By ?CustomerName?Collate?Chinese_PRC_Stroke_ci_as?

2 .分頁SQL語句
select ? * ? from ( select ?(row_number()? OVER ?( ORDER ? BY ?tab.ID? Desc ))? as ?rownum,tab. * ? from ?表名As?tab)? As ?t? where ?rownum? between ?起始位置And?結束位置

8 .如何修改數據庫的名稱:

sp_renamedb? ' old_name ' ,? ' new_name ' ?


3 .獲取當前數據庫中的所有用戶表
select ? * ? from ?sysobjects? where ?xtype = ' U ' ? and ?category = 0 ?

4 .獲取某一個表的所有字段
select ?name? from ?syscolumns? where ?id = object_id ( ' 表名 ' )?

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

6 .查看當前數據庫中所有存儲過程
select ?name? as ?存儲過程名稱from?sysobjects? where ?xtype = ' P ' ?

7 .查詢用戶創建的所有數據庫
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 ?

8 .查詢某一個表的字段和數據類型
select ?column_name,data_type? from ?information_schema.columns? where ?table_name? = ? ' 表名 ' ?

?

?

9 .使用事務
在使用一些對數據庫表的臨時的SQL語句操作時，可以采用SQL?SERVER事務處理，防止對數據操作后發現誤操作問題

開始事務
Begin ? tran ?
Insert ? Into ?TableName? Values (…)?
SQL語句操作不正常，則回滾事務。

回滾事務
Rollback ? tran ?
SQL語句操作正常，則提交事務，數據提交至數據庫。

提交事務
Commit ? tran ?


計算執行SQL語句查詢時間

declare ? @d ? datetime ?
set ? @d = getdate ()?
select ? * ? from ?SYS_ColumnProperties? select ? [ 語句執行花費時間(毫秒) ] = datediff (ms, @d , getdate ())?



【關閉SQL?Server?數據庫所有使用連接】

use ??master?
go ?
create ?? proc ??KillSpByDbName( @dbname ?? varchar ( 20 ))??
as ??
begin ??
declare ?? @sql ?? nvarchar ( 500 ), @temp ? varchar ( 1000 )?
declare ?? @spid ?? int ??
set ?? @sql = ' declare??getspid??cursor??for????
select??spid??from??sysprocesses??where??dbid=db_id( ''' + @dbname + ''' ) ' ??
exec ??( @sql )??
open ??getspid??
fetch ?? next ?? from ??getspid?? into ?? @spid ??
while ?? @@fetch_status ? <>- 1 ??
begin ??
?? set ? @temp = ' kill?? ' + rtrim ( @spid )?
?? exec ( @temp )?
fetch ?? next ?? from ??getspid?? into ?? @spid ??
end ??
close ??getspid??
deallocate ??getspid??
end ??

-- 舉例使用，關閉數據庫下的所有連接操作
Use ??master??
Exec ??KillSpByDbName?? ' 數據庫名稱 ' ?


（一）掛起操作
在安裝Sql或sp補丁的時候系統提示之前有掛起的安裝操作，要求重啟，這里往往重啟無用，解決辦法：
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession?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 = Evbbs.bak

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

?

?


select ? top ?m? * ? from ?tablename? where ?id? not ? in ?( select ? top ?n?id? from ?tablename)?


select ? top ?m? * ? into ?臨時表(或表變量)? from ?tablename? order ? by ?columnname? -- ?將top?m筆插入
set ? rowcount ?n?
select ? * ? from ?表變量order? by ?columnname? desc ?


select ? top ?n? * ? from ?
( select ? top ?m? * ? from ?tablename? order ? by ?columnname)?a?
order ? by ?columnname? desc ?





復制表(只復制結構,源表名：a?新表名：b)?(Access可用)?
法一： select ? * ? into ?b? from ?a? where ? 1 ? <> 1 ?
法二： select ? top ? 0 ? * ? into ?b? from ?a?

拷貝表(拷貝數據,源表名：a?目標表名：b)?(Access可用)?
insert ? into ?b(a,?b,?c)? select ?d,e,f? from ?b;?

跨數據庫之間表的拷貝(具體數據使用絕對路徑)?(Access可用)?
insert ? into ?b(a,?b,?c)? select ?d,e,f? from ?b? in ?‘具體數據庫’ where ?條件
例子：.. from ?b? in ? ' "&Server.MapPath(".")&"/data.mdb"?&" ' ? where ..?

子查詢(表名：a?表名：b)?
select ?a,b,c? from ?a? where ?a? IN ?( select ?d? from ?b?)?或者:? select ?a,b,c? from ?a? where ?a? IN ?( 1 , 2 , 3 )?

顯示文章、提交人和最后回復時間
select ?a.title,a.username,b.adddate? from ? table ?a,( select ? max (adddate)?adddate? from ? table ? where ? table .title = a.title)?b?

外連接查詢(表名：a?表名：b)?
select ?a.a,?a.b,?a.c,?b.c,?b.d,?b.f? from ?a? LEFT ?OUT? JOIN ?b? ON ?a.a? = ?b.c?

在線視圖查詢(表名：a?)?
select ? * ? from ?( SELECT ?a,b,c? FROM ?a)?T? where ?t.a? > ? 1 ;?

between的用法,between限制查詢數據范圍時包括了邊界值, not ?between不包括
select ? * ? from ?table1? where ?time? between ?time1? and ?time2?
select ?a,b,c,? from ?table1? where ?a? not ? between ?數值and?數值

in ?的使用方法
select ? * ? from ?table1? where ?a? [ not ] ? in ?(‘值’,’值’,’值’,’值’)?

兩張關聯表，刪除主表中已經在副表中沒有的信息
delete ? from ?table1? where ? not ? exists ?(? select ? * ? from ?table2? where ?table1.field1 = table2.field1?)?

四表聯查問題：
select ? * ? from ?a? left ? inner ? join ?b? on ?a.a = b.b? right ? inner ? join ?c? on ?a.a = c.c? inner ? join ?d? on ?a.a = d.d? where ?..?

日程安排提前五分鐘提醒
SQL:? select ? * ? from ?日程安排where? datediff ( ' minute ' ,f開始時間, getdate ()) > 5 ?

一條sql?語句搞定數據庫分頁
select ? top ? 10 ?b. * ? from ?( select ? top ? 20 ?主鍵字段,排序字段from?表名order? by ?排序字段desc)?a,表名b? where ?b.主鍵字段 = ?a.主鍵字段order? by ?a.排序字段


選擇在每一組b值相同的數據中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產品分析,按科目成績排名,等等.)?
select ?a,b,c? from ?tablename?ta? where ?a = ( select ? max (a)? from ?tablename?tb? where ?tb.b = ta.b)?

包括所有在TableA?中但不在TableB和TableC?中的行并消除所有重復行而派生出一個結果表
( select ?a? from ?tableA?)? except ?( select ?a? from ?tableB)? except ?( select ?a? from ?tableC)?

隨機取出條數據
select ? top ? 10 ? * ? from ?tablename? order ? by ? newid ()?

隨機選擇記錄
select ? newid ()?

?

? ?

刪除重復記錄
Delete ? from ?tablename? where ?id? not ? in ?( select ? max (id)? from ?tablename? group ? by ?col1,col2,)?

select ? distinct ? * ? into ?#Tmp? from ?TB
drop ? table ?TB
select ? * ? into ?TB? from ?#Tmp
drop ? table ?#Tmp

?

在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復的呢?
1 、查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷???
select ??? * ??? from ???people???
where ???peopleId??? in ???( select ?????peopleId????? from ?????people????? group ????? by ?????peopleId????? having ????? count (peopleId)??? > ??? 1 )???
????
2 、刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄???
delete ??? from ???people?????
where ???peopleId????? in ???( select ?????peopleId????? from ???people????? group ????? by ?????peopleId??????? having ????? count (peopleId)??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ?????people????? group ??? by ???peopleId????? having ??? count (peopleId???) > 1 )???
????
3 、查找表中多余的重復記錄（多個字段）?????
select ??? * ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq????? having ??? count ( * )??? > ??? 1 )???
????
4 、刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄???
delete ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * )??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * ) > 1 )???
????
5 、查找表中多余的重復記錄（多個字段），不包含rowid最小的記錄???
select ??? * ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * )??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * ) > 1 )?




-- float字段保留一位小數，四舍五入

SELECT ? CONVERT ( DECIMAL ( 18 , 1 ), 1024.791454 )? -- -----?1024.8?（所影響的行數為1?行）
? <% #?Eval("字段") == null ?"":Eval("字段").toString(" 0.0 ")? %>