SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 m#uutomi0
dG1qrh9_-
(q)W<GYP
一、基础 @ ~PL|Pp_
1、说明:创建数据库 xMe[/7)4
CREATE DATABASE database-name &4DWLI
2、说明:删除数据库 ~U`aH~R
drop database dbname gX[6WB"p
3、说明:备份sql server y<)x`&pcD
--- 创建 备份数据的 device f+rBIE
USE master wEdXaOEB5
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' /gxwp:&lY
--- 开始 备份 Zvc{o8^z
BACKUP DATABASE pubs TO testBack \hg12],#:@
4、说明:创建新表 xk#/J]j
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) !aLL|}S
根据已有的表创建新表: T7[ItLZ
A:create table tab_new like tab_old (使用旧表创建新表) 4]Krx
m`8
B:create table tab_new as select col1,col2... from tab_old definition only $N~8^6
5、说明:删除新表 )F:hv[iv
drop table tabname TtHqdKL
6、说明:增加一个列 K1Uur>Pk%
Alter table tabname add column col type 1g
*4e
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 J
9z\ qTI
7、说明:添加主键: Alter table tabname add primary key(col) 0 ~VniF^
说明:删除主键: Alter table tabname drop primary key(col) ^*Sb)tu\ W
8、说明:创建索引:create [unique] index idxname on tabname(col....) j#29L"
删除索引:drop index idxname ^X^4R1V)
注:索引是不可更改的,想更改必须删除重新建。 X[R/j*K
9、说明:创建视图:create view viewname as select statement DEs/?JZG
删除视图:drop view viewname >XBLm`a
10、说明:几个简单的基本的sql语句 $cjidBi`):
选择:select * from table1 where 范围 zI&oZH^vn
插入:insert into table1(field1,field2) values(value1,value2) Nx~8]h1(
删除:delete from table1 where 范围 YqYCW}$
更新:update table1 set field1=value1 where 范围 Iu=iC.50}
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! *f1MgP*GKF
排序:select * from table1 order by field1,field2 [desc] tip\vS)
总数:select count as totalcount from table1 n<?:!f`
求和:select sum(field1) as sumvalue from table1 -FwOX~s/'
平均:select avg(field1) as avgvalue from table1 t|1?mH9
最大:select max(field1) as maxvalue from table1 >=wlS\:"
最小:select min(field1) as minvalue from table1 NT:p6(s^
TeQpmhN
geua8;
^MuO;<<,.
11、说明:几个高级查询运算词 H.*XoktC]
op;OPf,
>-f`mT
A: UNION 运算符 '(;`t1V8k
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 rlgp1>89
B: EXCEPT 运算符 -Zkl\A$>
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 Mc9% s$MT
C: INTERSECT 运算符 c{zQX0
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 >a[)F
注:使用运算词的几个查询结果行必须是一致的。 q'[5h>Pa
12、说明:使用外连接 4&}LYSZl
A、left outer join: G;MmD?VJ g
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 0X.pI1jCO
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Yz4Q!tL
B:right outer join: S-*4HV_l
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 tAefBFu
C:full outer join: aH*)W'N?
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 $0
eyp]XC\
3V2"1Ic
,@Xl?
二、提升 ?(H/a-(:v}
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) fM6Pw6k
法一:select * into b from a where 1<>1 tRFj<yuaq
法二:select top 0 * into b from a jUYb8:B
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) #2s$dI
insert into b(a, b, c) select d,e,f from b; K08xiMjl
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 5$/ED3mcK
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 ,,OO2EgZ`
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. pri=;I(2A
4、说明:子查询(表名1:a 表名2:b) -r7*C:E
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) K}LmU{/t/
5、说明:显示文章、提交人和最后回复时间 Pd6 p)zj
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b WL:CBE#
6、说明:外连接查询(表名1:a 表名2:b)
pO[ @2tF
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c x[zt(kC0+
7、说明:在线视图查询(表名1:a ) D:4Iex9$F"
select * from (SELECT a,b,c FROM a) T where t.a > 1; (w}iEm\b
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 )[i0~o[
select * from table1 where time between time1 and time2 W$=Ad *
select a,b,c, from table1 where a not between 数值1 and 数值2 r>+\9q1
9、说明:in 的使用方法 1^jGSB.%A
select * from table1 where a [not] in ('值1','值2','值4','值6') @lRTp
10、说明:两张关联表,删除主表中已经在副表中没有的信息 9ePG-=5I
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) %We~k'2f
11、说明:四表联查问题: cia'h_w
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 ..... 9Ra*bP ]1
12、说明:日程安排提前五分钟提醒 EBc_RpC/Z
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 V4PI~"4q#1
13、说明:一条sql 语句搞定数据库分页 hCS|(8g
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 g1UP/hNJ\8
14、说明:前10条记录 e0Zwhz,
select top 10 * form table1 where 范围 ihS;q6ln
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) @i U@JE`C
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) ge
%ytrst
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 /}t>o*
x
(select a from tableA ) except (select a from tableB) except (select a from tableC) p~Di\AQ/
17、说明:随机取出10条数据 j51Wod<[
select top 10 * from tablename order by newid() >+Z BQ]~
18、说明:随机选择记录 }8`W%_Yk
select newid()
[uqe|< :
19、说明:删除重复记录 Q8OA{EUtq
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) >$Sc}a3
20、说明:列出数据库里所有的表名 :s DE'o
select name from sysobjects where type='U' 9$U@h7|Q`
21、说明:列出表里的所有的 TrD2:N}dI
select name from syscolumns where id=object_id('TableName') Er509zZ,[
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 D+.<
kY.
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type dNK Q&TC
显示结果: $R6iG\V5
type vender pcs o}O"
电脑 A 1 oe$&X&
电脑 A 1 ?tx%KU\3
光盘 B 2 ;aXu
光盘 A 2 $=3&qg"!
手机 B 3 #SyF-QZ[1
手机 C 3 #e)A
23、说明:初始化表table1 lOB*M!8
TRUNCATE TABLE table1 }81eef4$S
24、说明:选择从10到15的记录 wiHGTaR
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 8$9Q=M
M uz+j.0
Z1Y/2MVSb
!'scOWWn
三、技巧 ~0/tU#&
1、1=1,1=2的使用,在SQL语句组合时用的较多 {<$ D|<S
"where 1=1" 是表示选择全部 "where 1=2"全部不选, KT'Ebb]
如: K=lm9K
if @strWhere !='' 0oR'"Vo
begin ^K[WFi N}
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere vfBIQfH
end v_=xN^R
else }#'I,?_k
begin 1V?)T
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' q+<<Ku(20
end n/]w!
我们可以直接写成 Em6P6D>S>,
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere vl}fC@%WRI
2、收缩数据库 TEB<ia3+
--重建索引 }7Lo}}
DBCC REINDEX d6RO2^
DBCC INDEXDEFRAG Z!#n55|
--收缩数据和日志 zt,Tda4Y
DBCC SHRINKDB kD"BsL*6!
DBCC SHRINKFILE Qk`ykTS!
3、压缩数据库 "^gV.
dbcc shrinkdatabase(dbname) hv.33l
4、转移数据库给新用户以已存在用户权限 !W\Zq+^^J3
exec sp_change_users_login 'update_one','newname','oldname' cl\Gh
go pX 4:WV
5、检查备份集 ,EsPm'`?A/
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 7
k:w3M
6、修复数据库 U-h'a:
K
ALTER DATABASE [dvbbs] SET SINGLE_USER Ebk9[=
GO KkD.n#A
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK "Sx}7?8AB
GO WC0gJy
ALTER DATABASE [dvbbs] SET MULTI_USER oY
NIJXln
GO }253Q!f
7、日志清除 g<b(q|
SET NOCOUNT ON [- Xz:
DECLARE @LogicalFileName sysname, Uw`YlUT\
@MaxMinutes INT, J)kH$!csi
@NewSize INT yLFZo"r
USE tablename -- 要操作的数据库名 cpY'::5.%
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 {:@MBA34
@MaxMinutes = 10, -- Limit on time allowed to wrap log. gC/~@Z8W]
@NewSize = 1 -- 你想设定的日志文件的大小(M) S2APqRg*
-- Setup / initialize TK! D=M
DECLARE @OriginalSize int uGo tX b
SELECT @OriginalSize = size C4,;l^?=%
FROM sysfiles NI<;L m
WHERE name = @LogicalFileName &<Iyb}tA?
SELECT 'Original Size of ' + db_name() + ' LOG is ' + lhk=yVG3
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 8?yRa{'"
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' WSi`KNX
FROM sysfiles :NCY6?
[Dz
WHERE name = @LogicalFileName ?v5OUmFM
CREATE TABLE DummyTrans OCX>LK!K
(DummyColumn char (8000) not null) J`I^F:y*
DECLARE @Counter INT, \Ei(HmEU
@StartTime DATETIME, bY@ S[
@TruncLog VARCHAR(255) 4hQ.RO
SELECT @StartTime = GETDATE(), JkfVsmc<{h
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' j:Y1
DBCC SHRINKFILE (@LogicalFileName, @NewSize) JXhHitUD
EXEC (@TruncLog) jWUpzf)q=T
-- Wrap the log if necessary. K-<kp!v
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired ^Fop/\E
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) GS*Mv{JJ
AND (@OriginalSize * 8 /1024) > @NewSize ,)svSzR
BEGIN -- Outer loop. ezz;NH
SELECT @Counter = 0 b'5]o
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) dRhsnT+KX
BEGIN -- update g %ZKn
INSERT DummyTrans VALUES ('Fill Log') 0`H)c)
pP
DELETE DummyTrans s:p6oEQ=J
SELECT @Counter = @Counter + 1 kO)+%'L!8
END W]TO%x{
EXEC (@TruncLog) Id(wY$C&>
END HNMVs]/e
SELECT 'Final Size of ' + db_name() + ' LOG is ' + S7(Vc H
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + {J[5 {]Je[
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' bdxmJ9a:R
FROM sysfiles L/+KY_b:*
WHERE name = @LogicalFileName e5z U`R
DROP TABLE DummyTrans B*
hW
SET NOCOUNT OFF I
k[{,p
8、说明:更改某个表 RJ63"F $
exec sp_changeobjectowner 'tablename','dbo' d*cAm$
9、存储更改全部表 .[Hv/?L
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch <+r<3ZBA
@OldOwner as NVARCHAR(128), g~/@`Z2Y
@NewOwner as NVARCHAR(128) $D%[}[2
AS 12olVTuw
DECLARE @Name as NVARCHAR(128) Cg]Iz<<bE
DECLARE @Owner as NVARCHAR(128)
MYk%p'
DECLARE @OwnerName as NVARCHAR(128) Nn:>c<[
DECLARE curObject CURSOR FOR :~PzTUz
select 'Name' = name, x$gVEh*k
'Owner' = user_name(uid) lFZ}.
from sysobjects ~N!-4-~p
where user_name(uid)=@OldOwner WGC'k
s ^
order by name %~{G*%:
OPEN curObject 3W#f
Fy
FETCH NEXT FROM curObject INTO @Name, @Owner ",Ge:\TR=
WHILE(@@FETCH_STATUS=0) uG:xd0X+W
BEGIN l,w$!FnmR
if @Owner=@OldOwner 9$iDK$%
begin Vmb `%k20'
set @OwnerName = @OldOwner + '.' + rtrim(@Name) p$+.]
exec sp_changeobjectowner @OwnerName, @NewOwner naaww
end IPTEOA<M[
-- select @name,@NewOwner,@OldOwner q\I2lZ
FETCH NEXT FROM curObject INTO @Name, @Owner 9FKowF_8
END W]aX}>0
close curObject jn:9Cr,o;g
deallocate curObject ^6?)EM#
GO J|gRG0O9Ya
10、SQL SERVER中直接循环写入数据 sfUKH;xC
declare @i int >P_/a,O8
set @i=1 [m+):q^
while @i<30 $TK<~3`
begin ? 3'O
insert into test (userid) values(@i) W&'[Xj
set @i=@i+1 ;5.S"
end M~SbIk<#a<
小记存储过程中经常用到的本周,本月,本年函数 wVMR&R<t
Dateadd(wk,datediff(wk,0,getdate()),-1) @TqqF:c7
Dateadd(wk,datediff(wk,0,getdate()),6) ]hC6PKJU
Dateadd(mm,datediff(mm,0,getdate()),0) 1 Vq)& N
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) MEled:i
Dateadd(yy,datediff(yy,0,getdate()),0) >`S $(f
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) m,1Hlp
上面的SQL代码只是一个时间段 mu/GOEZ5
Dateadd(wk,datediff(wk,0,getdate()),-1) D>>?8a
Dateadd(wk,datediff(wk,0,getdate()),6) rd\:.
就是表示本周时间段. iQ7S*s+l5O
下面的SQL的条件部分,就是查询时间段在本周范围内的: &X`zk
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) LagHzCB
而在存储过程中 ,+mH1#-3
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) rq]zt2
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6)
#l<un<