SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 a\-AGG{2/X
\)$:
Znw3P|>B
一、基础 8+i=u"<
1、说明:创建数据库 fHK.q({Qc
CREATE DATABASE database-name &R5zt]4d&
2、说明:删除数据库 A=W:}szt]
drop database dbname _mWVZ1P
3、说明:备份sql server ]*?lgwE
--- 创建 备份数据的 device &&% oazR=
USE master k,eo+qH.Hz
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' }ChS cY
--- 开始 备份 |
|"W=E
BACKUP DATABASE pubs TO testBack 3iM7c.f*/
4、说明:创建新表 Vx z`
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) hT`fAn_
根据已有的表创建新表: tm&,u*6$W?
A:create table tab_new like tab_old (使用旧表创建新表) J6J">
B:create table tab_new as select col1,col2... from tab_old definition only ?wP/l
5、说明:删除新表 \VpN:RI
drop table tabname }7*|s+F(f
6、说明:增加一个列 S=}1k,I
Alter table tabname add column col type @r&*Qsf|
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 8
XQo
7、说明:添加主键: Alter table tabname add primary key(col) N TcojA{V$
说明:删除主键: Alter table tabname drop primary key(col) \5|MW)x
8、说明:创建索引:create [unique] index idxname on tabname(col....) 5Q;Q
删除索引:drop index idxname =(+]ee!Ti
注:索引是不可更改的,想更改必须删除重新建。 8Kw,
1O:
9、说明:创建视图:create view viewname as select statement !\VzX
删除视图:drop view viewname WEYZ(a|
10、说明:几个简单的基本的sql语句 v%rmfI U
选择:select * from table1 where 范围 |'Z+`HI
插入:insert into table1(field1,field2) values(value1,value2) qv^P
删除:delete from table1 where 范围 nW)?cQ
I
更新:update table1 set field1=value1 where 范围 4< +f|(fIA
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! dGgltY
排序:select * from table1 order by field1,field2 [desc] 8WE@ X)e
总数:select count as totalcount from table1 +T\<oj%}2
求和:select sum(field1) as sumvalue from table1 ,wf:Fr
平均:select avg(field1) as avgvalue from table1 Fr~\ZL
最大:select max(field1) as maxvalue from table1 5S<Rz) 1r
最小:select min(field1) as minvalue from table1 #_eXybUV
L{&>,ww
AJ+\Qs(0
N5c*#lHI
11、说明:几个高级查询运算词 jG~-V<&
:i4AkBNK
2vTO>*t
A: UNION 运算符 2?Y8hm
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 $l2`@ia"
B: EXCEPT 运算符 9a[1s|>w-
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 0W0GSDx
C: INTERSECT 运算符 vw 6$v
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 `dw">z,
注:使用运算词的几个查询结果行必须是一致的。 egK~w8`W%
12、说明:使用外连接 "cyRzQ6EH
A、left outer join: (gnN</%
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 Atb`Q'Yrw
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 K@<*m!%<2
B:right outer join: _TLspqi
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 Nw9@E R
C:full outer join: E[WU
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 #.rkvoB0N
kebk f,`p
W[I$([
二、提升 i=L 86Ks
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) p5jR;nOZ%l
法一:select * into b from a where 1<>1 !E&l=*lM.
法二:select top 0 * into b from a F?$Vx)HI
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) vf zC2
insert into b(a, b, c) select d,e,f from b; =;+gge!?bB
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) O|S,="h"}
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 L(bDk'zi
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. v4Wq0>o
4、说明:子查询(表名1:a 表名2:b) _CPj]m{
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) cRH(@b
Xr
5、说明:显示文章、提交人和最后回复时间 wo+`WnDh
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b z
.Z
6、说明:外连接查询(表名1:a 表名2:b) L pq)TE#
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 43E)ltR=]
7、说明:在线视图查询(表名1:a ) 9Nps<+K
select * from (SELECT a,b,c FROM a) T where t.a > 1; 1.M<u)1GU
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 m62Zta
select * from table1 where time between time1 and time2 w[F})u]E
select a,b,c, from table1 where a not between 数值1 and 数值2 v-N4&9)%9
9、说明:in 的使用方法 O}%ES AB
select * from table1 where a [not] in ('值1','值2','值4','值6') s>:gL,%c
10、说明:两张关联表,删除主表中已经在副表中没有的信息 /Yb8= eM
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) tmOy"mq67
11、说明:四表联查问题: *xJ ]e.
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 ..... `v@Z|rv,
12、说明:日程安排提前五分钟提醒 X&HYWH'@,
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 -. o,bg
13、说明:一条sql 语句搞定数据库分页 Rz&`L8Bz
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 Zr1"'+-
14、说明:前10条记录 :1Nc6G
select top 10 * form table1 where 范围 etT9}RbQ
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) \?oT.z5VG&
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) k;jl3GV
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 yKuZJXGVo
(select a from tableA ) except (select a from tableB) except (select a from tableC) '$Z@oCY#
17、说明:随机取出10条数据 [ )
0JI6
select top 10 * from tablename order by newid() |||m5(`S
18、说明:随机选择记录 i3mw.`7
select newid() _YG@P1
19、说明:删除重复记录 )Nqx=ms[(!
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) |{(JUXo6K
20、说明:列出数据库里所有的表名 GZWqPM4S\
select name from sysobjects where type='U' epKr6
xq
21、说明:列出表里的所有的 @sG*u >
select name from syscolumns where id=object_id('TableName') t{yj`Vg
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 akk*f+TD`
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 FAL#p$y}
显示结果: 2*^=)5Gj-h
type vender pcs |JR`" nF`
电脑 A 1 `k>C%6FG$#
电脑 A 1 g)\ Tex<
光盘 B 2 Op8Gj
`
光盘 A 2 b+q'xnA=>
手机 B 3 *^Zt)U1$|
手机 C 3 Kp*3:XK
23、说明:初始化表table1 f[D%(
TRUNCATE TABLE table1 X3 1%T"
24、说明:选择从10到15的记录 0C.5Qx
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 4CchE15
\pkK
>R
cuH5f }oc
ppRA%mhZ
三、技巧
5Pq6X
1、1=1,1=2的使用,在SQL语句组合时用的较多 9od c :
"where 1=1" 是表示选择全部 "where 1=2"全部不选, N<@K(?'
如: `q\F C[W
if @strWhere !='' x1Y/^ks@2
begin @I|kY5' c
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 4[#)p}V
end @67GVPcxl
else 0LXu!iix
begin 9mp`LT
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ~CHcbEWk)W
end |EdEV*.ej
我们可以直接写成 n:B){'S
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere jbq x7x
2、收缩数据库 <mki@{ ;|
--重建索引 @{{L1[~:0
DBCC REINDEX WV'u}-v^
DBCC INDEXDEFRAG +|b#|>6
--收缩数据和日志 JL!^R_b&c
DBCC SHRINKDB FWU>WHX
DBCC SHRINKFILE -(e=S^36
3、压缩数据库 ^wc:qll
dbcc shrinkdatabase(dbname) @=Pc{xp
4、转移数据库给新用户以已存在用户权限 v FQ]>nX
exec sp_change_users_login 'update_one','newname','oldname' .SmG) 5U]
go 88<d<)7t
5、检查备份集 yPT o,,ca=
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 5D=U.UdR
6、修复数据库 ]@cI _n
ALTER DATABASE [dvbbs] SET SINGLE_USER ZvQZD=,F
GO r_ +!3
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK uH?4d!G
GO #g@4c3um|
ALTER DATABASE [dvbbs] SET MULTI_USER ~3Pp}eO~V
GO <,it<$f#
7、日志清除 = 03G~7B>
SET NOCOUNT ON cUP1Uolvn
DECLARE @LogicalFileName sysname, o\ ce|Dzt
@MaxMinutes INT, ?Fl O,|
@NewSize INT 9{geU9&Z
USE tablename -- 要操作的数据库名 U[Sh){4j
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 <+r~?X_
@MaxMinutes = 10, -- Limit on time allowed to wrap log. p5OoDo
@NewSize = 1 -- 你想设定的日志文件的大小(M) `Ix`/k}
-- Setup / initialize K@DFu5
DECLARE @OriginalSize int
<&`Rf6
SELECT @OriginalSize = size &hI!0DixX
FROM sysfiles ~|, "w90
WHERE name = @LogicalFileName 6Ad UlPM
SELECT 'Original Size of ' + db_name() + ' LOG is ' + x5xMr.vm
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + Pzd!"Gl9
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' A' uaR?
FROM sysfiles /=l!F'
WHERE name = @LogicalFileName l&e{GHz
CREATE TABLE DummyTrans O(-6Zqk8Q
(DummyColumn char (8000) not null) ^8bc<c:P
DECLARE @Counter INT, jj;TS%
@StartTime DATETIME, 3!cenyE
@TruncLog VARCHAR(255) "x.iD,>k
SELECT @StartTime = GETDATE(), kI04<!
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' Het>G{
DBCC SHRINKFILE (@LogicalFileName, @NewSize) k,'MmAz
EXEC (@TruncLog) <\uDtbK
-- Wrap the log if necessary. S&y${f
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired /qwY/^
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) !mWm@}Ujg
AND (@OriginalSize * 8 /1024) > @NewSize _<2{8>EVf
BEGIN -- Outer loop. AB0}6g^O
SELECT @Counter = 0 ~.J*_0~Ze
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 6vTnm4
BEGIN -- update gaNe\
INSERT DummyTrans VALUES ('Fill Log') 8"NPj0
DELETE DummyTrans {/N8[?zML
SELECT @Counter = @Counter + 1 )mI>2<Z!
END Wi5Dl=
EXEC (@TruncLog) Isvb;VT9L
END pbqk
SELECT 'Final Size of ' + db_name() + ' LOG is ' + T*Ge67
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + =
=Q*|L-g
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 9 `bLQd
FROM sysfiles -OmpUv-O"
WHERE name = @LogicalFileName 7BqP3T=&_
DROP TABLE DummyTrans )+Z.J]$O-
SET NOCOUNT OFF J4j:nd
8、说明:更改某个表 +\dKe[j{g
exec sp_changeobjectowner 'tablename','dbo' yKK9b
9、存储更改全部表 ndT_;==
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch E@)'Z6r1
@OldOwner as NVARCHAR(128), vaHtWz!P
@NewOwner as NVARCHAR(128) Uc,..
AS a{}#t}
DECLARE @Name as NVARCHAR(128) ps8tr:T^=
DECLARE @Owner as NVARCHAR(128) 'r_Fi5[q
DECLARE @OwnerName as NVARCHAR(128) 7@e}rh?N-|
DECLARE curObject CURSOR FOR ;o;ak.dTt
select 'Name' = name, ~,)D
n
'Owner' = user_name(uid) 9mn~57`y
from sysobjects 1 |)CQ
where user_name(uid)=@OldOwner l O*
order by name tQxxm=>
OPEN curObject $_eJ@L#
FETCH NEXT FROM curObject INTO @Name, @Owner &Qj1uf92.
WHILE(@@FETCH_STATUS=0) Ma(Q~G
.
BEGIN 91yYR*
if @Owner=@OldOwner `HYj:4v'
begin 2?:OsA}
set @OwnerName = @OldOwner + '.' + rtrim(@Name) |/ 8!PKm
exec sp_changeobjectowner @OwnerName, @NewOwner MT)q?NcG
end ^r(]S%
-- select @name,@NewOwner,@OldOwner 8KkN
"4'
FETCH NEXT FROM curObject INTO @Name, @Owner (Rq6m`M2
END |%#NA!e4wA
close curObject Z 5P4 H
deallocate curObject =TzJgx
GO {(asy}a9K
10、SQL SERVER中直接循环写入数据 #j+cl'
declare @i int .!lLj1?p
set @i=1 ,!,M'<?"
while @i<30 =oiz@Q @H
begin y0?HZ Xq
insert into test (userid) values(@i) (|<+yQ,@>
set @i=@i+1 cH:&S=>h
end iPG:w+G
小记存储过程中经常用到的本周,本月,本年函数 'L9hM.+
Dateadd(wk,datediff(wk,0,getdate()),-1) +eKLwM
Dateadd(wk,datediff(wk,0,getdate()),6) +R;LHRS%
Dateadd(mm,datediff(mm,0,getdate()),0) *:un+k
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) *<[\|L:#]Z
Dateadd(yy,datediff(yy,0,getdate()),0) TXV^f*
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) aMkuyqPf{
上面的SQL代码只是一个时间段 ySDo(EI4
Dateadd(wk,datediff(wk,0,getdate()),-1) 8:*ZuR|~
Dateadd(wk,datediff(wk,0,getdate()),6) 7)2Q
就是表示本周时间段. Rg46V-"d,@
下面的SQL的条件部分,就是查询时间段在本周范围内的: Ly2!(,FB.
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ]BRwJ2< x
而在存储过程中 :9x]5;ma
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) *uccY_
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 2~ETu&R: