SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 _0w1kqW
(m6EQoW^s+
'Cg V0&@
一、基础 $Ru&>D#stK
1、说明:创建数据库 kPW BDpzN
CREATE DATABASE database-name wXz\NGW
2、说明:删除数据库 Xj%,xm>}!u
drop database dbname )5U!>,fT
3、说明:备份sql server 46ILs1T6
--- 创建 备份数据的 device ;r3}g"D@
USE master Ha?G=X
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ]}.0el{
--- 开始 备份 JT}.F!q6E
BACKUP DATABASE pubs TO testBack Cc2MYm8
4、说明:创建新表 V- /YNRV
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) aFyh,
根据已有的表创建新表: h
5Hr[E1
A:create table tab_new like tab_old (使用旧表创建新表) B&
"RS
B:create table tab_new as select col1,col2... from tab_old definition only ><cU7 ja[^
5、说明:删除新表 >[EBpYi
drop table tabname _p0)vT
6、说明:增加一个列 W8y$Ve8m
Alter table tabname add column col type as(; ]
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 dIvy!d2l
7、说明:添加主键: Alter table tabname add primary key(col) ]Y{,N x
说明:删除主键: Alter table tabname drop primary key(col) B@"J]S
8、说明:创建索引:create [unique] index idxname on tabname(col....) X~Cq
删除索引:drop index idxname qIz}$%!A
注:索引是不可更改的,想更改必须删除重新建。 &f"T,4Oh
9、说明:创建视图:create view viewname as select statement f}2;N
删除视图:drop view viewname St+ "ih%
10、说明:几个简单的基本的sql语句 [3kl^TE
选择:select * from table1 where 范围 (vnoP< 0
插入:insert into table1(field1,field2) values(value1,value2) Y([d;_#P
删除:delete from table1 where 范围 )nOE8y/
更新:update table1 set field1=value1 where 范围 hXc}r6<B
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 7*/J4M N
排序:select * from table1 order by field1,field2 [desc] tvGlp)?.
总数:select count as totalcount from table1 J0sGvj{
求和:select sum(field1) as sumvalue from table1 p31oL{D
平均:select avg(field1) as avgvalue from table1 _4v"")Xe
最大:select max(field1) as maxvalue from table1 T)~!mifX
最小:select min(field1) as minvalue from table1 cJ2PI
l
sr?b
o,*=$/or
U<rI!!#9
11、说明:几个高级查询运算词 P$OUi!"
Z9VR]cf?
<h@]Ri
A: UNION 运算符 GyK(Vb"h6
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 5z Kqb
B: EXCEPT 运算符 R5ZIC4p
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 N2Ssf$
C: INTERSECT 运算符 (hN?:q?'
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 $UC {"0
注:使用运算词的几个查询结果行必须是一致的。 iD714+N(
12、说明:使用外连接 R$d7\nBG
A、left outer join: {OP~8e"
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 ({ k7#1
h8
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 |'$ l7
B:right outer join: i?-Y
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 `*k@4.J{
C:full outer join: (:7a&2/M
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 >f@ G>H)+
CmHyAw(
3[VNsX
二、提升 ,H{={aln
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) b 4OnZ;FI
法一:select * into b from a where 1<>1 .&|L|q}
法二:select top 0 * into b from a #U"1 9@|}
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) nNQ-"t
insert into b(a, b, c) select d,e,f from b; &d,Wy"WPi
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) `0@z"D5c
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 zJCEA
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. f GarUV
4、说明:子查询(表名1:a 表名2:b) 5v|EAjB6o
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) _ZyT3P&
5、说明:显示文章、提交人和最后回复时间 X 8R1a?
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b Hi8Y6|y$D
6、说明:外连接查询(表名1:a 表名2:b) onnugj3
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c FYl3c
7、说明:在线视图查询(表名1:a ) X"<|Z]w
select * from (SELECT a,b,c FROM a) T where t.a > 1; m&/=&S
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 %{'4.
,
select * from table1 where time between time1 and time2 a+
GJVJ
select a,b,c, from table1 where a not between 数值1 and 数值2 OZOb1D
9、说明:in 的使用方法 %l|\of7P2}
select * from table1 where a [not] in ('值1','值2','值4','值6') L7<+LA)s0
10、说明:两张关联表,删除主表中已经在副表中没有的信息 !"`Jqs
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 2+o! o
11、说明:四表联查问题: , !r@9T
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 ..... ovB=Zm
12、说明:日程安排提前五分钟提醒 8-A:k E
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 NtqFnxm/
13、说明:一条sql 语句搞定数据库分页 WX ,p`>n
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 jmSt?M0.xV
14、说明:前10条记录 )U]:9)
select top 10 * form table1 where 范围 )=y.^@UT@
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) MCM/=M'y
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) We\KDU\n
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 C0gfJ~M)
(select a from tableA ) except (select a from tableB) except (select a from tableC) z}iSq$
17、说明:随机取出10条数据 gU~
L@R_D
select top 10 * from tablename order by newid() /q T E
18、说明:随机选择记录 d3og?{i<}&
select newid() xna7kA
19、说明:删除重复记录 U&`6&$]
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) pa>p%
20、说明:列出数据库里所有的表名 :,jPNuOA
select name from sysobjects where type='U' q)NXyy4BT
21、说明:列出表里的所有的 PL9<*.U"=
select name from syscolumns where id=object_id('TableName') 33*^($bE&
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 c$.UE
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 [?A&xqO3
显示结果: :DDO=
type vender pcs L^6"'#
电脑 A 1 #,t2*tM
电脑 A 1 UP}feN
光盘 B 2 BQ).`f";d
光盘 A 2 fDL3:%D
手机 B 3 rFf:A-#l
手机 C 3 ]gb _Nv
23、说明:初始化表table1 bh{E&1sLh
TRUNCATE TABLE table1 <}<#W/
24、说明:选择从10到15的记录 $[)6H7!U)
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc ~azF+}x90N
T@B"BoKU
]^Sd9ba
Y,3z-Pa=@
三、技巧 @P*ylB}?Q
1、1=1,1=2的使用,在SQL语句组合时用的较多 S4;wa6
"where 1=1" 是表示选择全部 "where 1=2"全部不选, AqKHjCI
如: CR%D\I$o
if @strWhere !='' ,jAx%]@,I
begin -b].SG5S
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere g7.7E6%H
end )C'G2RV
else eL<m.06cfY
begin es.jh
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' d1UVvyH
end x*NqA(r
我们可以直接写成 >`<Ued
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere += X).X0K
2、收缩数据库 b\kN_
--重建索引 );0<Odw%.
DBCC REINDEX t.3Ct@wK
DBCC INDEXDEFRAG AQE
eIFH
--收缩数据和日志 kA?X^nj@
DBCC SHRINKDB |Dl*w/n
DBCC SHRINKFILE RpBiE8F4
3、压缩数据库 MDMtOfe|
dbcc shrinkdatabase(dbname) Dm7Y#)%8
4、转移数据库给新用户以已存在用户权限 .}IK}A/-
exec sp_change_users_login 'update_one','newname','oldname' qFvg}}^y
go K<$wz/\
5、检查备份集 L!CX&
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' ;k9
?
6、修复数据库 E"Ya-8d=
ALTER DATABASE [dvbbs] SET SINGLE_USER {Q{lb(6Ba
GO fG:PdIJ7_
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK #pS]k<o%1
GO "6NFe!/Y$*
ALTER DATABASE [dvbbs] SET MULTI_USER =gxgS<bde
GO 6Cz7A
7、日志清除 8QkWgd7y
SET NOCOUNT ON Ha46U6_'h
DECLARE @LogicalFileName sysname, l.__10{
@MaxMinutes INT, @rnp- +kq
@NewSize INT d$2@,
USE tablename -- 要操作的数据库名 }*fW!(*
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 W?*Xy6",JF
@MaxMinutes = 10, -- Limit on time allowed to wrap log. 8>C;
>v
@NewSize = 1 -- 你想设定的日志文件的大小(M) :FX'[7;p
-- Setup / initialize 3v8V*48B$
DECLARE @OriginalSize int >P6^k!R1y
SELECT @OriginalSize = size h(C#\{V
FROM sysfiles M/::`yJQu
WHERE name = @LogicalFileName viW~'}^k7
SELECT 'Original Size of ' + db_name() + ' LOG is ' + sDLVYD
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + tnQR<
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' \Dvl%:8
FROM sysfiles OqfhCNAY
WHERE name = @LogicalFileName nx!qCgo
CREATE TABLE DummyTrans c,v^A+sZu
(DummyColumn char (8000) not null) Nz(c"3T;
DECLARE @Counter INT, p56KS5duI.
@StartTime DATETIME, )@8'k]Glw.
@TruncLog VARCHAR(255) baUEsg[~V
SELECT @StartTime = GETDATE(), SQO>}#qm
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' b
xk'a,!S
DBCC SHRINKFILE (@LogicalFileName, @NewSize) jRv;D#Hp
EXEC (@TruncLog) #}[Sj-Vp
-- Wrap the log if necessary. 8Pr&F
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired TIK/ %T
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) VTy,43<
AND (@OriginalSize * 8 /1024) > @NewSize kcq9p2zKv
BEGIN -- Outer loop. /t$+Af,}
SELECT @Counter = 0 p`E|SNt/W
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ,_"7|z wb
BEGIN -- update WL
IDw@fv
INSERT DummyTrans VALUES ('Fill Log') !#,-
DELETE DummyTrans ;#5-.z
SELECT @Counter = @Counter + 1 TnvHO_P,
END m6~ sKJV
EXEC (@TruncLog) M-&^
END mr{k>Un\
SELECT 'Final Size of ' + db_name() + ' LOG is ' + .NV)hg)|cZ
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + ~cO?S2!W
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 3X-{2R/ 3
FROM sysfiles ND9n1WZ&x
WHERE name = @LogicalFileName sUyCAKebRr
DROP TABLE DummyTrans bGMeBj"R
SET NOCOUNT OFF N[+o[%A
8、说明:更改某个表 ~,1-$#R
exec sp_changeobjectowner 'tablename','dbo' i#@ v_^ q
9、存储更改全部表 )E[
Q
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch t?%}hs\!
@OldOwner as NVARCHAR(128), )9A<fwpN
@NewOwner as NVARCHAR(128) .v
#0cQX+.
AS {L-{Y<fke
DECLARE @Name as NVARCHAR(128) |*]X\UE
DECLARE @Owner as NVARCHAR(128) F'?5V0\he
DECLARE @OwnerName as NVARCHAR(128) GDo)6du
DECLARE curObject CURSOR FOR ;9!yh\\
select 'Name' = name, T(sG.%
'Owner' = user_name(uid) OVQxZ~uQ
from sysobjects ySr091Q
where user_name(uid)=@OldOwner 7N} \1Di5
order by name &|'Kut?8
OPEN curObject AXNszS%4
FETCH NEXT FROM curObject INTO @Name, @Owner IxEQh)J X
WHILE(@@FETCH_STATUS=0) _Ssv:xc,
BEGIN =XRgT1>e
if @Owner=@OldOwner Tg<>B
begin G
+nY}c
set @OwnerName = @OldOwner + '.' + rtrim(@Name) WoClTb>F
exec sp_changeobjectowner @OwnerName, @NewOwner *mK);@pL
end 2t]! {L
-- select @name,@NewOwner,@OldOwner ;8%@Lan
FETCH NEXT FROM curObject INTO @Name, @Owner T_oW)G
END HkfSx rTgQ
close curObject -?%{A%'
deallocate curObject =B o4yN
GO <Hr@~<@~
10、SQL SERVER中直接循环写入数据 0()9vTY+
declare @i int B!E<uVC
set @i=1 <4!&iU+;
while @i<30 soQv?4
begin @|\s$L
insert into test (userid) values(@i) e~2*>5\:
set @i=@i+1 UQji7K }
end +}G>M=t::
小记存储过程中经常用到的本周,本月,本年函数 j_ywG{Jk
Dateadd(wk,datediff(wk,0,getdate()),-1) t#D\*:Xi
Dateadd(wk,datediff(wk,0,getdate()),6) Fb<\(#t
Dateadd(mm,datediff(mm,0,getdate()),0) ("P mB?20
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) -JyODW#j
Dateadd(yy,datediff(yy,0,getdate()),0) whg4o|p
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) nE"b`
上面的SQL代码只是一个时间段 cnUU1Uz>
Dateadd(wk,datediff(wk,0,getdate()),-1) HU'`kimWb
Dateadd(wk,datediff(wk,0,getdate()),6) c&I,eds
就是表示本周时间段. e,j ?_p
下面的SQL的条件部分,就是查询时间段在本周范围内的: -I$txa/"|
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) W%jX-
而在存储过程中 nO.+&kA
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) &<