SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 C *]XQ1F4
C]M{
pGP$2
一、基础 e$N1m:1*
1、说明:创建数据库 TGzs|-
CREATE DATABASE database-name d(F4-kBd
2、说明:删除数据库 UQO?hZ!y/.
drop database dbname PJ2m4ulY
3、说明:备份sql server TlpQ9T
--- 创建 备份数据的 device 9
=D13s(C
USE master e_dsBmTh
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' /vAA]n8
--- 开始 备份 XL`i9kV?
BACKUP DATABASE pubs TO testBack zv~b-Tp
4、说明:创建新表
\z? -
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) dF'oZQz
根据已有的表创建新表: 8_{XrTw(
A:create table tab_new like tab_old (使用旧表创建新表) E[M.q;rM
B:create table tab_new as select col1,col2... from tab_old definition only [-h=L
Jf#
5、说明:删除新表 `g'z6~c7n
drop table tabname l-M
.C8N
6、说明:增加一个列 m`#UV-$J
Alter table tabname add column col type oD_#oX5\
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 %kL]-Z
7、说明:添加主键: Alter table tabname add primary key(col) w
C-x'
说明:删除主键: Alter table tabname drop primary key(col) NLZUAtx(
8、说明:创建索引:create [unique] index idxname on tabname(col....) YiC_,8A~
删除索引:drop index idxname X -_0wR
注:索引是不可更改的,想更改必须删除重新建。 rQ&F Gb
9、说明:创建视图:create view viewname as select statement yD(v_J*
删除视图:drop view viewname .2/W.z2
10、说明:几个简单的基本的sql语句 o_?A^u
选择:select * from table1 where 范围 GtkZ%<KF9
插入:insert into table1(field1,field2) values(value1,value2) *7K)J8kq
删除:delete from table1 where 范围 !KLY*bt6
更新:update table1 set field1=value1 where 范围 /^b=| +Do
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! AUPTtc`#Y
排序:select * from table1 order by field1,field2 [desc] :{x
总数:select count as totalcount from table1 !\|L(Paf
求和:select sum(field1) as sumvalue from table1 hA387?
平均:select avg(field1) as avgvalue from table1 nj7\vIR7
最大:select max(field1) as maxvalue from table1 leO..M
最小:select min(field1) as minvalue from table1 h;t5v6["
ux1SQ8C *
D]hwG0Chd
tkf^sGgNO
11、说明:几个高级查询运算词 pF'M
#i|AE`
5
DvD
A: UNION 运算符 9pq-"?vHY0
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 crRYgr
B: EXCEPT 运算符 pR7 D3Q:^7
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 Hb@PQcj
C: INTERSECT 运算符 t"FB}%G
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 Uf<IXx&;
注:使用运算词的几个查询结果行必须是一致的。 w[s}#Q
12、说明:使用外连接 %<;PEQQ|C
A、left outer join: 7A4_b8
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 >l(|c9OWM
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 zn/b\X/
B:right outer join: wQ~F%rQ$
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 dnstm@0k
C:full outer join: #~:@H&f790
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 .dl4f"k
-%>.Z1uj
V"Y-|R
二、提升 (xlAS
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) }?=4pGsI
法一:select * into b from a where 1<>1 W5zlU2
法二:select top 0 * into b from a 0j(/ N
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) wsyAq'%L
insert into b(a, b, c) select d,e,f from b; ,-_\Y hY>
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) +G[N
lb
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 Yp8GW1@
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 6@cT;=W;xj
4、说明:子查询(表名1:a 表名2:b) O`Ge|4
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) tU, >EbwO
5、说明:显示文章、提交人和最后回复时间 dEW I8Q]
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 7~7_T#dTh
6、说明:外连接查询(表名1:a 表名2:b) D+69U[P_A
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c GsvB5i
7、说明:在线视图查询(表名1:a ) 6xAR:
select * from (SELECT a,b,c FROM a) T where t.a > 1; H?W8_XiN
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 LGF5yRk
select * from table1 where time between time1 and time2 (| X?
select a,b,c, from table1 where a not between 数值1 and 数值2 <ZgbmRY8
9、说明:in 的使用方法 o2r)K AA
select * from table1 where a [not] in ('值1','值2','值4','值6') 0ntf%#2{
10、说明:两张关联表,删除主表中已经在副表中没有的信息 y%=t((.Z
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) O'~;|-Z<
11、说明:四表联查问题: KL6FmL)HH
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 ..... |XoW
Z,K
12、说明:日程安排提前五分钟提醒 z 2Rg`1B
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 C6cEt5
13、说明:一条sql 语句搞定数据库分页 $l<(*,,l
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 x cA5
14、说明:前10条记录 #JIh-h@
select top 10 * form table1 where 范围 "0#d F:qt
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) S1oP_A[|
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) Hj^_Cp]@*
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 ";$rcg"%X
(select a from tableA ) except (select a from tableB) except (select a from tableC) `UDB9Ca
17、说明:随机取出10条数据 pQf5s7
select top 10 * from tablename order by newid() "~^#{q
18、说明:随机选择记录 o2#_CdU
select newid() ^P
!}"
19、说明:删除重复记录 L!
DK2,
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) K,Z_lP_~Vw
20、说明:列出数据库里所有的表名 }*s%|!{H
select name from sysobjects where type='U' "g>, X[g
21、说明:列出表里的所有的 ;u
"BCW
select name from syscolumns where id=object_id('TableName') sAec*Q(R
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 zxffjz,Fe:
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 eod-N}o
显示结果: 6o!Y^^/U
type vender pcs }4A $j{\
电脑 A 1 ]E!b&
电脑 A 1 ,U^V]jC
光盘 B 2 uF89B-t
光盘 A 2 B %Vz -t
手机 B 3 1 /dy@'
手机 C 3 Y ~%9TC
23、说明:初始化表table1 (^4V]N&
TRUNCATE TABLE table1 [~#]p9|L
24、说明:选择从10到15的记录 4itadQS
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc G34fxhh
M8HHyV[AmC
J;AwC>N
suP/I?4'@
三、技巧 F T/STI
1、1=1,1=2的使用,在SQL语句组合时用的较多 |}Wm,J
"where 1=1" 是表示选择全部 "where 1=2"全部不选, 0M_~@E*&
如: oYn|>`+6:y
if @strWhere !='' oP CtLz}z
begin :497]c3#5C
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere pX~X{JTaL)
end M~jV"OF=
else S%t*!
begin Q"+)xj
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' [x\?._>
end ,KyG^;Riy
我们可以直接写成 gcKXda(
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere >.X& v
2、收缩数据库 ?\7$63gBH
--重建索引 !:<(p
DBCC REINDEX #Z)8,N
DBCC INDEXDEFRAG lk?@ =U~
--收缩数据和日志 7)U08"
DBCC SHRINKDB (o5^@aDr
DBCC SHRINKFILE V0ig#?]
3、压缩数据库 S7Tc9"oqV
dbcc shrinkdatabase(dbname) @P@j9yR
4、转移数据库给新用户以已存在用户权限 ,/uVq G
exec sp_change_users_login 'update_one','newname','oldname' #D2.RN
go Q eZg l!
5、检查备份集 S_ELV#X
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' \J0fr'(S
6、修复数据库 E[8R
)xC@
ALTER DATABASE [dvbbs] SET SINGLE_USER 2#hfBJg@
GO k=D}i\F8
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK ~As/cd>9
GO &oXN*$/dlJ
ALTER DATABASE [dvbbs] SET MULTI_USER a\@k5?
GO J+o6*t2|
7、日志清除 _a`J>~$
SET NOCOUNT ON _d`)N
DECLARE @LogicalFileName sysname, &u}]3E'-k
@MaxMinutes INT, :*6#(MX
@NewSize INT ,u&K(Z%
USE tablename -- 要操作的数据库名 |Y")$pjz
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 (- QvlpZ
@MaxMinutes = 10, -- Limit on time allowed to wrap log. bAVlL&^@|
@NewSize = 1 -- 你想设定的日志文件的大小(M) b Y^K)0+^s
-- Setup / initialize (G<fvl!~
DECLARE @OriginalSize int 1@"os[9
SELECT @OriginalSize = size alV{| Vf[6
FROM sysfiles WnkI i,<
WHERE name = @LogicalFileName OoqA`%
SELECT 'Original Size of ' + db_name() + ' LOG is ' + $[1J[eY*
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + _Q=h3(ZI
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' w$1B|7tX;2
FROM sysfiles Ht_7:5v&
WHERE name = @LogicalFileName |JVp(Kx
CREATE TABLE DummyTrans #P)(/>nF
(DummyColumn char (8000) not null) u P&<
DECLARE @Counter INT, Mr6 q7
@StartTime DATETIME, l?Qbwv}
@TruncLog VARCHAR(255) HV}*}Ty
SELECT @StartTime = GETDATE(), OB5t+_s
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 4;D>s8dgG
DBCC SHRINKFILE (@LogicalFileName, @NewSize) !bGMVw6_
EXEC (@TruncLog) __OH
gp 1
-- Wrap the log if necessary. o_@6R"|
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired sb"h:i>O4
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) vZJu=t
AND (@OriginalSize * 8 /1024) > @NewSize 1 LUvs~Qu
BEGIN -- Outer loop. @5:#J!
SELECT @Counter = 0 }*>xSb1
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) oH>G3n|U^
BEGIN -- update l/6$BPU`
INSERT DummyTrans VALUES ('Fill Log') t[=teB v<
DELETE DummyTrans ul!e!^qwx
SELECT @Counter = @Counter + 1 FNy-&{P2
END S #6:!
EXEC (@TruncLog) iQ#dWxw4
END $s,Az_bs
SELECT 'Final Size of ' + db_name() + ' LOG is ' + W'3~vQF
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 9>7w1G#
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' t}x^*I$*
FROM sysfiles dR@XwEpP
WHERE name = @LogicalFileName bb}$7v`G
DROP TABLE DummyTrans 7:$zSj#y
SET NOCOUNT OFF &++tp5
8、说明:更改某个表 <R.Ipyt.
exec sp_changeobjectowner 'tablename','dbo' h4geoC_W2
9、存储更改全部表 Wa!}$q+
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch \yKYBfp-p
@OldOwner as NVARCHAR(128), ?j|i|WUD
@NewOwner as NVARCHAR(128) + )lkHv$R
AS DNmP> ~
DECLARE @Name as NVARCHAR(128) (*Fb/
DECLARE @Owner as NVARCHAR(128) 2'T uS?
DECLARE @OwnerName as NVARCHAR(128) Zv^n
DECLARE curObject CURSOR FOR =Yt)b/0b9
select 'Name' = name, xI(t!aYp
'Owner' = user_name(uid) >yr1wVS
from sysobjects <
s1
where user_name(uid)=@OldOwner k+;XQEH
order by name P&.-c _
OPEN curObject T'${*NVn
FETCH NEXT FROM curObject INTO @Name, @Owner wG}Rh,
WHILE(@@FETCH_STATUS=0) d*tn&d~k,
BEGIN .\}nDT
if @Owner=@OldOwner W~Ae&gcn#
begin v FWg0 $,
set @OwnerName = @OldOwner + '.' + rtrim(@Name) )7;E,m<:tO
exec sp_changeobjectowner @OwnerName, @NewOwner u81@vEK:_
end Gq0Q}[53
-- select @name,@NewOwner,@OldOwner F|!
ib5
FETCH NEXT FROM curObject INTO @Name, @Owner u.9syr
END k@Mt8Ln
close curObject rPXy(d1<`S
deallocate curObject
q:vc;y
GO >NMq^J'/
10、SQL SERVER中直接循环写入数据 r";;Fk#5
declare @i int :V
ZXI#([
set @i=1 U])$#/ v
while @i<30 h;JO"J@H
begin ^udl&>
insert into test (userid) values(@i) .ovG_O
set @i=@i+1 Z>(K|3_
end (Y:?qy
小记存储过程中经常用到的本周,本月,本年函数 @*^%^ P
Dateadd(wk,datediff(wk,0,getdate()),-1) kmu r={IR
Dateadd(wk,datediff(wk,0,getdate()),6) C|-QU
Dateadd(mm,datediff(mm,0,getdate()),0) BVe c
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 0.z\YTZ9
Dateadd(yy,datediff(yy,0,getdate()),0) tr@)zM
GB
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) TaNcnAY>9
上面的SQL代码只是一个时间段 GFfZ TA
Dateadd(wk,datediff(wk,0,getdate()),-1) }2.0e5[
Dateadd(wk,datediff(wk,0,getdate()),6) Ee097A?1vj
就是表示本周时间段. |?<^4U8
下面的SQL的条件部分,就是查询时间段在本周范围内的: ]%ey rbU
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) Bhg,P.7
而在存储过程中 6)[gF1
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) +V9<ug6T
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) z2IKd'Wy