SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 )^a#Xn3z
?&bVe__
iPi'5g(a
一、基础 ~$`YzK^*X
1、说明:创建数据库 p!5JO4F$
CREATE DATABASE database-name OKH~Y-%<
2、说明:删除数据库 InGbV+ I
drop database dbname lbXkZ ,
3、说明:备份sql server Z.#glmw^=R
--- 创建 备份数据的 device G"R>a w
USE master `x^,k%
:4
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' 6xQe!d3>s3
--- 开始 备份 fP4IOlHkE
BACKUP DATABASE pubs TO testBack a5g{.:NfO
4、说明:创建新表 RwLdV+2\R`
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) ^oZs&+z
根据已有的表创建新表: L,ey3i7a\
A:create table tab_new like tab_old (使用旧表创建新表)
61;5Yo
B:create table tab_new as select col1,col2... from tab_old definition only Wn</",Gf
5、说明:删除新表 1OGv+b)
drop table tabname g KY
,G
6、说明:增加一个列 wEn&zZjx
Alter table tabname add column col type ktJLpZ<0O
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 79fyn!Iz<
7、说明:添加主键: Alter table tabname add primary key(col) BY2txLLB
说明:删除主键: Alter table tabname drop primary key(col) a[9OtZX<
8、说明:创建索引:create [unique] index idxname on tabname(col....) *j; r|P;g
删除索引:drop index idxname YuW\GSV00
注:索引是不可更改的,想更改必须删除重新建。 g?Ty5~:lq
9、说明:创建视图:create view viewname as select statement `v+O5
删除视图:drop view viewname wAwH8x LU
10、说明:几个简单的基本的sql语句 ?At-
选择:select * from table1 where 范围 &(1NOyX&
插入:insert into table1(field1,field2) values(value1,value2) G
U/k^Qy
删除:delete from table1 where 范围 NjMLq|X
更新:update table1 set field1=value1 where 范围 H[yLlv
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! Sgk{NM7|k
排序:select * from table1 order by field1,field2 [desc] %R5MAs&-5
总数:select count as totalcount from table1 -]MP,P%
求和:select sum(field1) as sumvalue from table1 tm#y`1-
平均:select avg(field1) as avgvalue from table1 .VV!$;
FB
最大:select max(field1) as maxvalue from table1 g5HqU2
最小:select min(field1) as minvalue from table1 2xxwQwg8
9 W
r(w
n;Wf|>
{oC69n:
11、说明:几个高级查询运算词 K#yH\fn8
r_CN/ a
+*~3"ww<
A: UNION 运算符 eEFT(e5.>3
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 eWs^[^c.<
B: EXCEPT 运算符 jWCC`0
T
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 <qiap2
C: INTERSECT 运算符 enepAu-="p
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 O!yn
`<l
注:使用运算词的几个查询结果行必须是一致的。 ^^(ZK 6 d
12、说明:使用外连接 _!Q\Xn
A、left outer join: -$p-o
Z)
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 a{6|[aR
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 AFA*_9Ut
B:right outer join: aM1JG$+7 G
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 cHd39H9
C:full outer join: d$
7b
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 )y Y;%
a"N_zGf2$
Vp94mi#L}
二、提升 1T`"/*!
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) q/zdd3a
法一:select * into b from a where 1<>1 1Tkdr2
法二:select top 0 * into b from a {.)D)8`<d
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) =ZL20<TeH
insert into b(a, b, c) select d,e,f from b; XV!EjD~q
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) j<5R$^?U
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 $dUN+9
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. $5[RR
4、说明:子查询(表名1:a 表名2:b) 6lFs N2
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 6Ua~N^
5、说明:显示文章、提交人和最后回复时间 >,1LBM|0u
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b Y5pNKL
6、说明:外连接查询(表名1:a 表名2:b) ~+ur*3X
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
/PS]AM
7、说明:在线视图查询(表名1:a ) sP8B?Tn1W
select * from (SELECT a,b,c FROM a) T where t.a > 1; ^ 9E(8DD
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 !(o2K!v0
select * from table1 where time between time1 and time2 D/>5\da+y
select a,b,c, from table1 where a not between 数值1 and 数值2 a-=apD1RvG
9、说明:in 的使用方法 (q7mzZY
select * from table1 where a [not] in ('值1','值2','值4','值6') |U0@(H
10、说明:两张关联表,删除主表中已经在副表中没有的信息 9_$Odc%]
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) `Nr7N#g+u
11、说明:四表联查问题: Qgi:q
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 ..... "+_0idpF
12、说明:日程安排提前五分钟提醒 tx-bzLo\
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 osI(g'Xb
13、说明:一条sql 语句搞定数据库分页 lvffQ_t
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 s| oU$?eA
14、说明:前10条记录 b@Cvs4
select top 10 * form table1 where 范围 #nz$RJsX
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) J ASn\z
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) !ZdUW]
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 d_hcv|%
(select a from tableA ) except (select a from tableB) except (select a from tableC) g#*N@83C
17、说明:随机取出10条数据 !9NAm?Fw
select top 10 * from tablename order by newid() vA `.8U 0S
18、说明:随机选择记录 N<WFe5
select newid() >i<-rO>kN
19、说明:删除重复记录 $x*GvI1D
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) eU0-_3gN_
20、说明:列出数据库里所有的表名 zq&lxySa
select name from sysobjects where type='U' pj6Cvq4bD
21、说明:列出表里的所有的 ^(3k
uF
select name from syscolumns where id=object_id('TableName') Y@eUvz
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 :SGQ4@BV
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 .S;/v--F
显示结果: hFv}JQJw<
type vender pcs m~A/.t%=
电脑 A 1 ,5,4 Qf7
电脑 A 1 PHkvt!uH
光盘 B 2 :W"ITY(
光盘 A 2 $G[##j2
手机 B 3 u a-p^X`w
手机 C 3 E%N]t} }[
23、说明:初始化表table1 TPuzL(ws
TRUNCATE TABLE table1 4j5 "{
24、说明:选择从10到15的记录 QNY{pk
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 7_,X9^z
oZCO$a
_JS'~JO3{
5ZLH=8L
三、技巧 B=7L+6
1、1=1,1=2的使用,在SQL语句组合时用的较多 iuEdm:pW
"where 1=1" 是表示选择全部 "where 1=2"全部不选, 6gXc-}dp
如: @B6[RZ R
if @strWhere !='' #XZ?,neY
begin HO>uS>+
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere R0WJdW#
end 5h&8!!$[
else jiat5
begin 42M3c&@P
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' l;4F,iI
end fi1UUJ0
U;
我们可以直接写成 `Cc<K8s8
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere '@FKgy;B)-
2、收缩数据库 z3,z&Ra
--重建索引 4{V=X3,x
DBCC REINDEX #X+)
DBCC INDEXDEFRAG P;GUGG*W
--收缩数据和日志 c!c!;(
DBCC SHRINKDB [M.Vu
DBCC SHRINKFILE =B\?(
3、压缩数据库 xAI<<[-
dbcc shrinkdatabase(dbname) Lqt]
4、转移数据库给新用户以已存在用户权限 hikun2
exec sp_change_users_login 'update_one','newname','oldname' L(L;z'3y
go L_(|5#IDw
5、检查备份集 kci H
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' E?V:dr
6、修复数据库 q(C+D%xB
ALTER DATABASE [dvbbs] SET SINGLE_USER li'#< "R?'
GO P)3e^~+A
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
d%<Uh(+:
GO QS[%`-dR2
ALTER DATABASE [dvbbs] SET MULTI_USER 5%9&
7
GO JSM{|HJxh
7、日志清除 u+*CpKR}
SET NOCOUNT ON &{>~|^
DECLARE @LogicalFileName sysname, B!:(*lF
@MaxMinutes INT, [mhY_Hmz]
@NewSize INT {YkW5zC(L
USE tablename -- 要操作的数据库名 {2,V3*NF
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 E0YXgQa
@MaxMinutes = 10, -- Limit on time allowed to wrap log. 1:-$mt_*
@NewSize = 1 -- 你想设定的日志文件的大小(M) "ey~w=B$M
-- Setup / initialize ?O.&=im_
DECLARE @OriginalSize int :
"UBeo<Z
SELECT @OriginalSize = size j0Q;OKu
FROM sysfiles E@?jsN7
WHERE name = @LogicalFileName ^ H'|iju
SELECT 'Original Size of ' + db_name() + ' LOG is ' + ruoiG?:T
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + Lm8cY
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' }hGbF"clqg
FROM sysfiles
@521zi
WHERE name = @LogicalFileName vJYy` k^Y
CREATE TABLE DummyTrans J=()
A+
(DummyColumn char (8000) not null) c|E
DECLARE @Counter INT, e%8K
A#DX
@StartTime DATETIME, 2lQ'rnqS)
@TruncLog VARCHAR(255) UlyX$f%2
SELECT @StartTime = GETDATE(), vHWw*gg(/E
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' gbF.Q7?$u
DBCC SHRINKFILE (@LogicalFileName, @NewSize) |[gnWNdR$M
EXEC (@TruncLog) *F=wMWa
-- Wrap the log if necessary. \i{=%[c
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired J|s4c`=
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 2+0'vIw}
AND (@OriginalSize * 8 /1024) > @NewSize k*M1m'1
BEGIN -- Outer loop. %6 Bt%H
SELECT @Counter = 0 U:ZklDW
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) N~SG=\rP;o
BEGIN -- update V]IS(U(
INSERT DummyTrans VALUES ('Fill Log') VaH#~!
DELETE DummyTrans 9v^MZ^Y{
SELECT @Counter = @Counter + 1 zL1H[}[z+
END k0%4&pU
EXEC (@TruncLog) xZBmQ:s',S
END 7)~/`w)P
SELECT 'Final Size of ' + db_name() + ' LOG is ' + V"gnG](2l
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + u"tv6Qp
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' a Z
^SK|E
FROM sysfiles "{|9Yis=
WHERE name = @LogicalFileName BH\qm
(X
DROP TABLE DummyTrans Ip|7JL0Z
SET NOCOUNT OFF !DD|dVA{
8、说明:更改某个表 EY':m_7W
exec sp_changeobjectowner 'tablename','dbo' $KKrl
9、存储更改全部表 Ot5
$~o
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch A\gj\&B0"
@OldOwner as NVARCHAR(128), DqbN=[!X~n
@NewOwner as NVARCHAR(128) u[y>DPPx
AS Wk`G+VR+
DECLARE @Name as NVARCHAR(128) PoQ@9
A
DECLARE @Owner as NVARCHAR(128) anHP5gD
DECLARE @OwnerName as NVARCHAR(128) s+9q:
DECLARE curObject CURSOR FOR V\^?V|
select 'Name' = name, U_Id6J]8
'Owner' = user_name(uid) `
Y"Rh[C
from sysobjects Q lql(*
where user_name(uid)=@OldOwner DM"`If%3j
order by name Jy{A1i@4~s
OPEN curObject D:M0_4S
FETCH NEXT FROM curObject INTO @Name, @Owner @zW'!Ol
WHILE(@@FETCH_STATUS=0) q#F;GD
BEGIN c(i-~_
if @Owner=@OldOwner ZI-)'
begin %#Fd0L
set @OwnerName = @OldOwner + '.' + rtrim(@Name) r)q6^|~47
exec sp_changeobjectowner @OwnerName, @NewOwner VWaI!bK
end ?E=&LAI#
-- select @name,@NewOwner,@OldOwner ~"iCx+pr
FETCH NEXT FROM curObject INTO @Name, @Owner }r9f}yX9Q
END
&CG3_s<2
close curObject iE0A-;:5
deallocate curObject x/R|i%u-s
GO A{Jv`K
10、SQL SERVER中直接循环写入数据 0'% R@|
declare @i int !zVuO*+
set @i=1 :;#Kg_bz
while @i<30 UXnd~DA
begin /k l0(='
insert into test (userid) values(@i) `b+f^6SJn
set @i=@i+1 n(0O'nS^
end 1)X%n)2pr
小记存储过程中经常用到的本周,本月,本年函数 y/'2WO[
Dateadd(wk,datediff(wk,0,getdate()),-1) "n=`{~F
Dateadd(wk,datediff(wk,0,getdate()),6) TY],H=
Dateadd(mm,datediff(mm,0,getdate()),0) #swzZyM$
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 1BmevEa)
Dateadd(yy,datediff(yy,0,getdate()),0) "9>~O`l,
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) YO|Kc
{j2e
上面的SQL代码只是一个时间段 FDkRfh K
Dateadd(wk,datediff(wk,0,getdate()),-1) j_H{_Ug
Dateadd(wk,datediff(wk,0,getdate()),6) %e+hM $Q
就是表示本周时间段. =;9Wh!{
下面的SQL的条件部分,就是查询时间段在本周范围内的: 50F6jj
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) D: NBb!
而在存储过程中 1>[3(o3t
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) =hH>]$J[
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ;@Alr?y