SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 lQKq{WLFx.
$A^OP{
?.Lq`~T`
一、基础 }s@vN8C
1、说明:创建数据库 A;Av0@w
CREATE DATABASE database-name wu~ ?P `
2、说明:删除数据库 U0@Qc}y
drop database dbname g]Z@_
3、说明:备份sql server 6H^=\
--- 创建 备份数据的 device d7P'c!@+
USE master ^8V8,C)
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ;D|g5$OE&
--- 开始 备份 YckLz01jh
BACKUP DATABASE pubs TO testBack f^|r*@o
4、说明:创建新表 j]'ybpMT"
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) l]~mB~
根据已有的表创建新表: 71G\b|5
A:create table tab_new like tab_old (使用旧表创建新表) ^*'fDP*
B:create table tab_new as select col1,col2... from tab_old definition only 0JU+v:J[=
5、说明:删除新表 $ #bWh
drop table tabname iq<nuO
6、说明:增加一个列 4,.[B7irR
Alter table tabname add column col type c"oJcp
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 e)f!2'LL
7、说明:添加主键: Alter table tabname add primary key(col) S<81r2LT
说明:删除主键: Alter table tabname drop primary key(col) @_H
L{q%h
8、说明:创建索引:create [unique] index idxname on tabname(col....) qZYh^\
删除索引:drop index idxname a\*_b2 ^n
注:索引是不可更改的,想更改必须删除重新建。 (d*~Qpi{7
9、说明:创建视图:create view viewname as select statement %
8P8h%%Z
删除视图:drop view viewname C`["4
10、说明:几个简单的基本的sql语句 Qb#iT}!p%
选择:select * from table1 where 范围 +o|I@7f
插入:insert into table1(field1,field2) values(value1,value2) Xk`' m[
删除:delete from table1 where 范围 {xRO.699
更新:update table1 set field1=value1 where 范围 Q?V'3ZZF!
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! tqXCj}mR
排序:select * from table1 order by field1,field2 [desc] >~*}9y0$
总数:select count as totalcount from table1 v~:'t\n
求和:select sum(field1) as sumvalue from table1 j2s{rQQ
平均:select avg(field1) as avgvalue from table1 eOZ"kw"uHu
最大:select max(field1) as maxvalue from table1 _j2q
最小:select min(field1) as minvalue from table1 JYrOE"!h
HQGH7<=Om
TT^L)d
KJi8LM
11、说明:几个高级查询运算词 \[L|
"L+NN|
b8&z~'ieR
A: UNION 运算符 h~5gHx/a
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 r1[#_A`Yn
B: EXCEPT 运算符 !|~yf3
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 A`nzqe#(1
C: INTERSECT 运算符 u?SxaGEa
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 '}9 %12\^h
注:使用运算词的几个查询结果行必须是一致的。 Q.g44>
12、说明:使用外连接 *T2kxN,Ik
A、left outer join: 09J,!NN
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 e4<St`K
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 +2,EK
B:right outer join: t#2szr+
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 `>u^Pm
C:full outer join: m[<z/D
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 G2w0r,[
-u~AY#*
n!h952"
二、提升 .5!Q(
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) `<(o;*&Gd
法一:select * into b from a where 1<>1 VJS1{n=;k
法二:select top 0 * into b from a "0m\y+%8
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) $GQ{Ai:VwF
insert into b(a, b, c) select d,e,f from b; />O.U?
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) i QvqifDmh
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 M3s:B& /
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ,U.|+i{
4、说明:子查询(表名1:a 表名2:b) <~
?LU^
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) x.>&|Ej
5、说明:显示文章、提交人和最后回复时间 UV\&9>@L
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b HXgf=R/$
6、说明:外连接查询(表名1:a 表名2:b) z6Zd/mt~x
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c P\&n0C~
7、说明:在线视图查询(表名1:a ) >:|jds#
select * from (SELECT a,b,c FROM a) T where t.a > 1; 7~H"m/;U&
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 a0PClbf2.
select * from table1 where time between time1 and time2 8gW$\
select a,b,c, from table1 where a not between 数值1 and 数值2 JfzfxfM
9、说明:in 的使用方法 $KPf[JvQ
select * from table1 where a [not] in ('值1','值2','值4','值6') +r$VrNVs
10、说明:两张关联表,删除主表中已经在副表中没有的信息 /2Bf6
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) [Q[ac 6f
11、说明:四表联查问题: @5{h+ ^
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 ..... D
4<,YBvV
12、说明:日程安排提前五分钟提醒 @+hO,WXN
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 6\RZ[gA?
13、说明:一条sql 语句搞定数据库分页 w_*$wVl
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 &{S@v9~IT
14、说明:前10条记录 b
q8nV
select top 10 * form table1 where 范围 ,"Nb;Yhg
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) wLKC6@
W
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 6c*QBzNL
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 /J!~0~F
(select a from tableA ) except (select a from tableB) except (select a from tableC) {4r } jH
17、说明:随机取出10条数据 OQ+kOE&
select top 10 * from tablename order by newid() 3^]Kd
18、说明:随机选择记录 smPZ%P}P+c
select newid() h%&2M58:
19、说明:删除重复记录 oiItQ4{<
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) PDb7 h
20、说明:列出数据库里所有的表名 8xx2+
select name from sysobjects where type='U' p{;FO?
21、说明:列出表里的所有的 ?|{tWR,Vb
select name from syscolumns where id=object_id('TableName') T1uOp5_]B
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 LT:8/&\
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 Fr hI[D
显示结果: 86W.z6
type vender pcs A>rN.XW
电脑 A 1 3-_`x9u*
电脑 A 1 ,@aF#
光盘 B 2 ad`7[fI
光盘 A 2 'h>CgR^NM1
手机 B 3 =W"9a\m
手机 C 3 cD9.L
23、说明:初始化表table1 qjH/E6GGg
TRUNCATE TABLE table1 HJ!P]X_J1
24、说明:选择从10到15的记录 WnQ+
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc :U6Q==B$_
8>'vzc/*>
7*@BCu6
i .''\
三、技巧 +m1*ou'K
1、1=1,1=2的使用,在SQL语句组合时用的较多 ^\w!D{Y7Q
"where 1=1" 是表示选择全部 "where 1=2"全部不选, ye`-U?7.
如: 4#ZZwa]y
if @strWhere !='' {
P @mAw
begin 6f&qtJQ<A
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
\1?:
end ?{r -z3@ N
else 5$c*r$t_RK
begin ]f*.C9Y
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 3u4P
[
end bEb+oRI
我们可以直接写成 IhXP~C6
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere )odz/\9n3c
2、收缩数据库 |\N))K-2D
--重建索引 ;&
zBNj
DBCC REINDEX ?;DzWCL~9
DBCC INDEXDEFRAG hz rS_v
--收缩数据和日志 l:j>d^V*&x
DBCC SHRINKDB B1 xlWdm
DBCC SHRINKFILE ?'^yw C`
3、压缩数据库 U\6Ee-1#_
dbcc shrinkdatabase(dbname) )pw53,7>aN
4、转移数据库给新用户以已存在用户权限 uwu`ms7z 2
exec sp_change_users_login 'update_one','newname','oldname' `}#n#C)
go }h=3[pe}
5、检查备份集 4x_#
1 -
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' u=ZZ;%Rvd
6、修复数据库 xvW# ~T]
ALTER DATABASE [dvbbs] SET SINGLE_USER PF:'dv
GO E]ZIm
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 7%i6zP/a
GO 8)="Ee
ALTER DATABASE [dvbbs] SET MULTI_USER Cf3<;Mp<
GO -o YJ&r
7、日志清除 9O-*iK
SET NOCOUNT ON Rzxkz
DECLARE @LogicalFileName sysname, @Wd1+Yky
@MaxMinutes INT, =HHb ]JE
@NewSize INT TJs ~}&L
USE tablename -- 要操作的数据库名 Fr1OzS^&(
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 gk4DoO j#P
@MaxMinutes = 10, -- Limit on time allowed to wrap log. .}3K9.hkr
@NewSize = 1 -- 你想设定的日志文件的大小(M) z/|tsVK
-- Setup / initialize >C -N0H
DECLARE @OriginalSize int R?}<CjI
SELECT @OriginalSize = size S{zl<>+
FROM sysfiles xDIl
WHERE name = @LogicalFileName L4{+@T1A[
SELECT 'Original Size of ' + db_name() + ' LOG is ' + F*=}}H/
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 8s>OO&
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' fi'\{!!3m^
FROM sysfiles VX e7b
WHERE name = @LogicalFileName P*kC>lvSv
CREATE TABLE DummyTrans .>@]Im
(DummyColumn char (8000) not null) [#/@v/`
DECLARE @Counter INT, p0C|ECH
@StartTime DATETIME, / a$B8,
@TruncLog VARCHAR(255) L!^^3vn
SELECT @StartTime = GETDATE(), lfte
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' HQV#8G#B
DBCC SHRINKFILE (@LogicalFileName, @NewSize) /qweozW_+
EXEC (@TruncLog) ;% 4N@Z
-- Wrap the log if necessary. "PC9[i
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 77;|PKE /
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) VP:9&?>G
AND (@OriginalSize * 8 /1024) > @NewSize EN)0b,ax
BEGIN -- Outer loop. G/&Wc2k
SELECT @Counter = 0 =7-@&S=?s
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) gU@BEn}
BEGIN -- update K"VRHIhfg
INSERT DummyTrans VALUES ('Fill Log') g
I4Rku
DELETE DummyTrans F"t.ND
SELECT @Counter = @Counter + 1 =xM:8
hm
END Il$Jj-)
EXEC (@TruncLog) }M~[8f
]
END N6!$V7oT
SELECT 'Final Size of ' + db_name() + ' LOG is ' + !k8j8v&
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + DQ= /Jr~
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' Z1oUAzpj4
FROM sysfiles +D|E8sz8
WHERE name = @LogicalFileName -h{| u{t
DROP TABLE DummyTrans >:f&@vwm
SET NOCOUNT OFF Uw->5
8、说明:更改某个表 $ cYKVhf
exec sp_changeobjectowner 'tablename','dbo' S &F
9、存储更改全部表
@+!u{
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch w7yz4_:x^
@OldOwner as NVARCHAR(128), %#@5(_'
@NewOwner as NVARCHAR(128) h3P ^W(=&
AS C7_#D O6"
DECLARE @Name as NVARCHAR(128) 8o!LgT5
DECLARE @Owner as NVARCHAR(128) "%K[kA6
DECLARE @OwnerName as NVARCHAR(128) FuFA/R=x/
DECLARE curObject CURSOR FOR 9v(k<('_
select 'Name' = name, 01vKx)f
'Owner' = user_name(uid) <6!/B[!O=
from sysobjects X5c)T}pyv
where user_name(uid)=@OldOwner *."50o=T
order by name <{1=4PA
OPEN curObject 48n 7<M;I
FETCH NEXT FROM curObject INTO @Name, @Owner 1ika'
WHILE(@@FETCH_STATUS=0) 0-Vx!(
BEGIN !Bn,f2
if @Owner=@OldOwner y/!jC]!+c
begin &\AW}xp
set @OwnerName = @OldOwner + '.' + rtrim(@Name) L'<.#(|
exec sp_changeobjectowner @OwnerName, @NewOwner d`4F
end U t.#h="
-- select @name,@NewOwner,@OldOwner 'Sjt*2blq
FETCH NEXT FROM curObject INTO @Name, @Owner Y%@a~|
END vABUUAo!Jr
close curObject zfm#yDf
deallocate curObject &``nYI g/
GO T#-U\C~o
10、SQL SERVER中直接循环写入数据 2G)q?_Q4S
declare @i int h"On9
set @i=1 ')1p
while @i<30 yo_;j@BGR
begin 4,?ZNyl
insert into test (userid) values(@i) 3nX={72<b
set @i=@i+1 -)p| i~j^A
end ]rc=oP;
小记存储过程中经常用到的本周,本月,本年函数 '+E\-X
Dateadd(wk,datediff(wk,0,getdate()),-1) 4'`y5E
Dateadd(wk,datediff(wk,0,getdate()),6) [K"&1h<>
Dateadd(mm,datediff(mm,0,getdate()),0) 8d8GYTl b)
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ZA'Qw2fF0
Dateadd(yy,datediff(yy,0,getdate()),0) lW3wmSWn%
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) d @>1m:p
上面的SQL代码只是一个时间段 0M|Jvw'n|
Dateadd(wk,datediff(wk,0,getdate()),-1) )P
#MUC
Dateadd(wk,datediff(wk,0,getdate()),6) eWTbHF
就是表示本周时间段. vJ0Zv>
n-
下面的SQL的条件部分,就是查询时间段在本周范围内的: fkJE lO-F
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) TtP2>eh-
而在存储过程中 5FwVR3,
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) FP9FE `x
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) D~7L~Q]xI