★說明：復制表(只復制結構,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
★說明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
★說明：顯示文章、提交人和最后回復時間
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
★說明：外連接查詢(表名1：a 表名2：b)
SQL: select a.f1, a.f2, a.f3, b.f3, b.f4, b.f5 from a left OUT JOIN b ON a.f1 = b.f3 (左連接)
SQL: select a.f1, b.f2 from a FULL OUT JOIN b ON a.f1 = b.f1 (全連接)
★說明：日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5
★說明：兩張關聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
★說明：四表聯(lián)查問題：
select f1, (select min(f)-1 from t where f>f1) as f2 from
(select f 1 as f1 from t where f 1 not in (select f from t) and f <(select max(f) from t)) as cc
★說明：--
SQL:
select A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
from TABLE1,
(select X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
from (select NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
from TABLE2
where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(select NUM, UPD_DATE, STOCK_ONHAND
from TABLE2
where TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
where X.NUM = Y.NUM （ ）
and X.INBOUND_QTY NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
where A.NUM = B.NUM
★說明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業(yè)名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績
★說明：
從數(shù)據(jù)庫中去一年的各單位電話費統(tǒng)計(電話費定額賀電化肥清單兩個表來源）
SQL:
select a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') as telyear,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) as JAN,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) as FRI,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) as MAR,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) as APR,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) as MAY,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) as JUE,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) as JUL,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) as AGU,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) as SEP,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) as OCT,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) as NOV,
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) as DEC
from (select a.userper, a.tel, a.standfee, b.telfeedate, b.factration
from TELFEESTAND a, TELFEE b
where a.tel = b.telfax) a
group by a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
1.把某個字段重新生起序列(從1到n):
set IDENTITY_INSERT Table1 ON
declare @i int
set @i = 0
update Table1 set @i = @i 1,Field1 = @i
set IDENTITY_INSERT Table1 off
2.按成績排名次
update 成績表
set a.名次 = (
select count(*) 1
from 成績表 b
where a.總成績 < b.總成績
)
from 成績表 a
3.查詢外部數(shù)據(jù)庫
select a.*
from OpenRowSet('Microsoft.Jet.OLEDB.4.0','c:\test.mdb';'admin';'',Table1) a
4.查詢Excel文件
select *
from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";user id=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
5.在查詢中指定排序規(guī)則
select * from Table1 order by Field1 COLLATE Chinese_PRC_BIN
為什么要指定排序規(guī)則呢?參見:
http://www.zahui.com/html/8/15480.htm
例,檢查數(shù)據(jù)庫中的Pub_Users表中是否存在指定的用戶:
select count(*) from Pub_Users where [UserName]='admin' and [PassWord]='aaa' COLLATE Chinese_PRC_BIN
默認比較是不區(qū)分大小寫的,如果不加COLLATE Chinese_PRC_BIN,那么密碼aaa與AAA是等效的,這當然與實際不符.注意的是,每個條件都要指定排序規(guī)則,上例中用戶名就不區(qū)分大小寫.
6.order by的一個小技巧
order by可以指定列序而不用指定列名,在下面的例子里說明它的用處(注意,第三列未指定別名)
select a.id,a.Name,(select count(*) from TableB b where a.id=b.PID) from TableA a order by 3
7.SQL簡單分頁的存儲過程 ◆常用◆◆常用◆◆常用◆◆常用◆◆常用◆
/*
create proc recordpages
@nowpage int,
@per int
as
declare @s nvarchar(255)
if @nowpage<1 set @nowpage=1
if @per<1 set @per=1
set @s=N'declare @k int select top ' convert(varchar(10),(@nowpage-1)*@per) ' @k=id from table1
select top ' convert(varchar(10),@per) ' * from table1 where id>@k'
exec sp_executesql @s
go
*/
exec recordpages 3,10
8：得到表中最小的未使用的id號
SQL: 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 .....
9.得到表中自動編號列精心策劃號的起始位置
SQL:
select (case when exists(select * from Handle b where b.HandleID = 1) then MIN(HandleID) + 1 else 1 end) as HandleID
from Handle
where NOT HandleID in (select a.HandleID - 1 from Handle a)