SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 }#Qc \eud
aW`Lec{.
c;n *AK
一、基础 '-"/ =j&d[
1、说明:创建数据库 j"'(sW-
CREATE DATABASE database-name 6Qy@UfB
2、说明:删除数据库 !=:$lzS^
drop database dbname UmclTGn
3、说明:备份sql server +i2}/s@JJ
--- 创建 备份数据的 device @>)r}b
USE master ^]a #7/]o
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' P:aJ#
--- 开始 备份 "0cID3A$
BACKUP DATABASE pubs TO testBack ek}a}.3 {
4、说明:创建新表 Wu_kx2h
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 9)gC6IiW
根据已有的表创建新表: L G1r]2
A:create table tab_new like tab_old (使用旧表创建新表) \8 h;K>=h
B:create table tab_new as select col1,col2... from tab_old definition only eK!V
);
5、说明:删除新表 ktdW`R\+
drop table tabname }XSfst5-H
6、说明:增加一个列 kT!9`S\
Alter table tabname add column col type pFHz"]
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 9uBM<
7、说明:添加主键: Alter table tabname add primary key(col) ~(IB0=A{v
说明:删除主键: Alter table tabname drop primary key(col) i2&ed_h<?
8、说明:创建索引:create [unique] index idxname on tabname(col....) t{WzKy
删除索引:drop index idxname O2BDL1o
注:索引是不可更改的,想更改必须删除重新建。 iIq)~e/ Z
9、说明:创建视图:create view viewname as select statement vc+A RgvH+
删除视图:drop view viewname 8qEVOZjV&
10、说明:几个简单的基本的sql语句 Ts ^"xlK
选择:select * from table1 where 范围 P}TI
q#
插入:insert into table1(field1,field2) values(value1,value2) \u>"s
删除:delete from table1 where 范围 :E@3Vl#U
更新:update table1 set field1=value1 where 范围 cvfr)K[0
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! %ve:hym*
排序:select * from table1 order by field1,field2 [desc] :9_L6
总数:select count as totalcount from table1 |Clut~G
求和:select sum(field1) as sumvalue from table1 'Ub
g0"F(
平均:select avg(field1) as avgvalue from table1 HsHB!mQV
最大:select max(field1) as maxvalue from table1 \&i P`v`K
最小:select min(field1) as minvalue from table1 D0#x
Lh
B&.FOO
u(wGl_
+{W>i; U
11、说明:几个高级查询运算词 3rcKzS7
X90J!
r.>].~}4
A: UNION 运算符 TT4./R:
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 'b#0t#|TM
B: EXCEPT 运算符 I9mvte
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 EVVP]ND
C: INTERSECT 运算符 B,@c;K
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 ]):<ZsT
注:使用运算词的几个查询结果行必须是一致的。 5i1>I=N
12、说明:使用外连接 mqAWL:VvQ7
A、left outer join: :xh?eN&
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 d_)o
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 ,>eMG=C; g
B:right outer join: 0\@dYPa&C
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 , 'ZD=4_
C:full outer join: `9uB~LY^i
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 k25WucQ
#&m0WI1
o;=l^-
二、提升 dUF&."pW e
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 7"w2$*4 '0
法一:select * into b from a where 1<>1 L.2/*H#
法二:select top 0 * into b from a CFqJ/''
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 8-_QFgY
insert into b(a, b, c) select d,e,f from b; 2cq I[t@0
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) nM-h&na{s
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 -Gw$#!
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. j|/]#@Yr
4、说明:子查询(表名1:a 表名2:b) O km{Xx
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) C_n9T{k
5、说明:显示文章、提交人和最后回复时间 ni6{pK4Wqm
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b zSSB>D
6、说明:外连接查询(表名1:a 表名2:b) @*Wh
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c .Y3pS/VI
7、说明:在线视图查询(表名1:a ) z(fAnn
T?
select * from (SELECT a,b,c FROM a) T where t.a > 1; +S R+x/?z
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 z[cyA.
select * from table1 where time between time1 and time2 f~dd3m('
select a,b,c, from table1 where a not between 数值1 and 数值2 @Q^P{
9、说明:in 的使用方法 \z$p%4`E@
select * from table1 where a [not] in ('值1','值2','值4','值6') &Ibu>di4[
10、说明:两张关联表,删除主表中已经在副表中没有的信息 (A?H1 9
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) |d*&y#kV
11、说明:四表联查问题: ewfP G,S
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 ..... rfgI$eu
12、说明:日程安排提前五分钟提醒 S6+y?,^
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 $P(v{W)
13、说明:一条sql 语句搞定数据库分页 >OG:vw)E
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 phn9:{TI
14、说明:前10条记录 &s$(g~ 4gC
select top 10 * form table1 where 范围 P4F3Dc
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) C!R1})_^
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) $`wMX{
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 BwBm[jtP
(select a from tableA ) except (select a from tableB) except (select a from tableC) a_ `[Lj
17、说明:随机取出10条数据 GF>'\@Th
select top 10 * from tablename order by newid() 0\:(ageY?
18、说明:随机选择记录 H'LD}\K l
select newid() j8fpj {hp
19、说明:删除重复记录 ;Ww7"-=sw
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ??i,Vr@)w
20、说明:列出数据库里所有的表名 Q<KvBgmT
select name from sysobjects where type='U' Mnz!nWhk
21、说明:列出表里的所有的 #ssN027
select name from syscolumns where id=object_id('TableName') g q}I[N
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 2A\,-*pc
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 #SX8=f`K5
显示结果: .h&
.K
type vender pcs ;}1xn3THCn
电脑 A 1 baP^<w^
电脑 A 1 CWNx4)ZGw
光盘 B 2 8S<@"v
光盘 A 2 (vB<%l.&
手机 B 3 @E-\ J7 yh
手机 C 3 *=wYuJ#
23、说明:初始化表table1 qqu.EE
TRUNCATE TABLE table1 C%U`"-%n@7
24、说明:选择从10到15的记录 -W<vyNSr
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc ^.hoLwp.
kf;/c}}
~",`,ZXQy
Ma\Gb+>
三、技巧 e+j)~RBnu3
1、1=1,1=2的使用,在SQL语句组合时用的较多 \N4
y<
"where 1=1" 是表示选择全部 "where 1=2"全部不选, U
R%4@
如: i-'9AYyw
if @strWhere !='' '2laTl]`
begin GN0`rEh
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere N @#c,,
end EM/@T}
else Cz W:L&t
begin 9{$<0,?
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' rS?pWTg"8
end ^kgBa2 7
我们可以直接写成 %<\6TZr
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere !Yw3 d
2、收缩数据库 TD9;kN1`
--重建索引 Xu>r~^w=S
DBCC REINDEX MzP7Py
8.
DBCC INDEXDEFRAG OZIW_'Wm/
--收缩数据和日志 24/XNSE,-
DBCC SHRINKDB Rt{B(L.?<
DBCC SHRINKFILE oh
KCdT~
3、压缩数据库 &E40*
(C
dbcc shrinkdatabase(dbname) jC3Vbm&ZZ
4、转移数据库给新用户以已存在用户权限 P{5-Mx!{&
exec sp_change_users_login 'update_one','newname','oldname' aj"M>zd*}
go \2(SB
5、检查备份集 ZWm8*}3]7_
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' !TP@-
X;
6、修复数据库 yY&3p1AxW]
ALTER DATABASE [dvbbs] SET SINGLE_USER LS5vW|]w
GO Qq@G\eRo
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK .(X
lg-H,
GO ]/!<PF
ALTER DATABASE [dvbbs] SET MULTI_USER S<L.c
GO =1u@7Bh
7、日志清除 NFr:y<0>z
SET NOCOUNT ON M#4QQ} F.
DECLARE @LogicalFileName sysname, <d<mvXbw_@
@MaxMinutes INT, 3VUWX5K?
@NewSize INT ^47PLLRP
USE tablename -- 要操作的数据库名 u- o--q
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 A#W?2k9
@MaxMinutes = 10, -- Limit on time allowed to wrap log. g1UGd
@NewSize = 1 -- 你想设定的日志文件的大小(M) UDe |Sb
-- Setup / initialize Bcjx>#3?L
DECLARE @OriginalSize int /c$\X<b);
SELECT @OriginalSize = size r&2~~_d3y
FROM sysfiles D!oc>K$B
WHERE name = @LogicalFileName U^.4Hy&D
SELECT 'Original Size of ' + db_name() + ' LOG is ' + )OLq_':^@
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + TP}h~8 /;
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' Hh4 n
FROM sysfiles Ic{F*nnM
WHERE name = @LogicalFileName `g_"GE
CREATE TABLE DummyTrans 2o9$4{}rG
(DummyColumn char (8000) not null) S8l1"/?aHE
DECLARE @Counter INT, 4:sjH.u<
@StartTime DATETIME, HeK
h>
@TruncLog VARCHAR(255) -wv6s#"u
SELECT @StartTime = GETDATE(), .p ls!
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' VN'Wq7>6
DBCC SHRINKFILE (@LogicalFileName, @NewSize) W>=o*{(YO
EXEC (@TruncLog) N6T{
-- Wrap the log if necessary. 4_D@ST%
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired rFZrYm
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) `$YP<CJeq
AND (@OriginalSize * 8 /1024) > @NewSize jr /lk
BEGIN -- Outer loop. k78Vh$AA6%
SELECT @Counter = 0 _oB_YL;,*
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) JI/_ce
BEGIN -- update X>I)~z}9#
INSERT DummyTrans VALUES ('Fill Log') a|BcnYN
DELETE DummyTrans ;oxAe<VIj
SELECT @Counter = @Counter + 1 ^Q{Bq
END H3H_u4_?SE
EXEC (@TruncLog) /R
LI,.%
END +xXH2b$wWC
SELECT 'Final Size of ' + db_name() + ' LOG is ' + e8EfQ1 Ar
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + ai'4_
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' `$604+G
FROM sysfiles 8*SP~q
WHERE name = @LogicalFileName BT_tOEL#
DROP TABLE DummyTrans : 5U"XY x@
SET NOCOUNT OFF 7Mx6
8、说明:更改某个表 +"ueq
exec sp_changeobjectowner 'tablename','dbo' cM&2SRBZ
9、存储更改全部表 M('d-Q{B7L
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch `Ci4YDaz;k
@OldOwner as NVARCHAR(128), fRvAKz|rL
@NewOwner as NVARCHAR(128) @-)tM.8~
AS T'#!~GpB
DECLARE @Name as NVARCHAR(128) T%F0B`
DECLARE @Owner as NVARCHAR(128) OI0B:()
DECLARE @OwnerName as NVARCHAR(128) @+Y8*Rj\3
DECLARE curObject CURSOR FOR 8CC/ BOe
select 'Name' = name, oW$s
xS
'Owner' = user_name(uid) u=&Bmn_
from sysobjects -z:&*=
where user_name(uid)=@OldOwner Kv{8iAB#c
order by name 9]>iSG^H
OPEN curObject D\~e&0*
FETCH NEXT FROM curObject INTO @Name, @Owner #g5^SR|qE
WHILE(@@FETCH_STATUS=0) o\`>c:.
BEGIN GOSI3RRn
if @Owner=@OldOwner -#29xRPk
begin w#
*1 /N
set @OwnerName = @OldOwner + '.' + rtrim(@Name) %@R~DBS
exec sp_changeobjectowner @OwnerName, @NewOwner XMRNuEU
end *8ExRQZ$
-- select @name,@NewOwner,@OldOwner `*\{.;,]#
FETCH NEXT FROM curObject INTO @Name, @Owner .9|uQEL
END ue8qIZH
close curObject l12$l<x&M
deallocate curObject (X6sSO
GO O!Wd5Y
10、SQL SERVER中直接循环写入数据 .1 QgK
declare @i int 3|rn] yZ
set @i=1 . -"E^f
while @i<30 (shK
begin ~"!a9GZ
insert into test (userid) values(@i) @-#T5?
set @i=@i+1 3P <'F2o
end [B0K
小记存储过程中经常用到的本周,本月,本年函数 BwJuYH7QJ$
Dateadd(wk,datediff(wk,0,getdate()),-1) h7;bclU
Dateadd(wk,datediff(wk,0,getdate()),6) ]$M<]w,IJ2
Dateadd(mm,datediff(mm,0,getdate()),0) cUK\x2
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 'FzN[% K"
Dateadd(yy,datediff(yy,0,getdate()),0) 0m+8P$)C%
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) i_F$&?)
上面的SQL代码只是一个时间段 1Xyp/X2rI
Dateadd(wk,datediff(wk,0,getdate()),-1) }t>q9bZ9z
Dateadd(wk,datediff(wk,0,getdate()),6) y1BgK>R
就是表示本周时间段. |*,jU;NI
下面的SQL的条件部分,就是查询时间段在本周范围内的: nSY-?&l6P
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ~E=\t9r
而在存储过程中 kA7(CqUW
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) ]=D5p_A(
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) rwpgBl