SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 9<S};I;
}@S''AA\
yW)X
asn
一、基础 h"5!puN+
1、说明:创建数据库 0'.7dzz
CREATE DATABASE database-name YkbZ 2J*-
2、说明:删除数据库 (xhV>hsA
drop database dbname S)[$F}
3、说明:备份sql server tcU4$%H/
--- 创建 备份数据的 device Af _yb`W?
USE master A/{0J\pA
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' dk4|*l-
--- 开始 备份 h2]gA_T`
BACKUP DATABASE pubs TO testBack
dJwE/s
4、说明:创建新表 mBZg(TY
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) |Y\BI^
根据已有的表创建新表: 3"J85V%h]n
A:create table tab_new like tab_old (使用旧表创建新表) 8]-c4zK
B:create table tab_new as select col1,col2... from tab_old definition only -?&s6XA%#
5、说明:删除新表 5 NdIbC
drop table tabname WF0[/Y
6、说明:增加一个列 A('_.J=
Alter table tabname add column col type 5W=jQ3 C
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 &fYV FRVkq
7、说明:添加主键: Alter table tabname add primary key(col) -{'WIGm
说明:删除主键: Alter table tabname drop primary key(col) wX*F'r"z
8、说明:创建索引:create [unique] index idxname on tabname(col....) F-2&P:sjQ
删除索引:drop index idxname WGrG#Kw[
注:索引是不可更改的,想更改必须删除重新建。 z^r
9、说明:创建视图:create view viewname as select statement ~}fQ.F*7R
删除视图:drop view viewname @$(@64r
10、说明:几个简单的基本的sql语句 ~)&im.Q4
选择:select * from table1 where 范围 N3}jLl/
插入:insert into table1(field1,field2) values(value1,value2) zV8^Hxl
删除:delete from table1 where 范围 ?h4Rh0rkX
更新:update table1 set field1=value1 where 范围 %1oG<s
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! $9Yk]~
排序:select * from table1 order by field1,field2 [desc] h16 i]V
总数:select count as totalcount from table1 $5n6C7
求和:select sum(field1) as sumvalue from table1 jvfQG:F }
平均:select avg(field1) as avgvalue from table1 4S+sz?W2j
最大:select max(field1) as maxvalue from table1 #b?)fqRJL
最小:select min(field1) as minvalue from table1 jsrIZbN
:pZWFJ34{
;_vo2zl1
7v^V]&&s
11、说明:几个高级查询运算词 #fR~7K R
XY1eeB-
(jY -MF3
A: UNION 运算符 ,:1_I`d>#X
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 /Sag_[i
B: EXCEPT 运算符 bAa+MB#A
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 B Ctm05
C: INTERSECT 运算符 8S_v} NUm
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 L&2 Zn{#`
注:使用运算词的几个查询结果行必须是一致的。 CnA0^JX
12、说明:使用外连接 AT%@T|
A、left outer join: 4Cdl^4(LT
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 !{,
`h<
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 pNzSy"Y$
B:right outer join: ] oh.w
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 s,|s;w*.
C:full outer join: <CnTiS#
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 BRg(h3 ED
^cy.iolt
'U"ub2j
二、提升 T@ecWRro
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) uqg#(ADy?R
法一:select * into b from a where 1<>1 BCK0fk~
法二:select top 0 * into b from a T+y3Ph--^
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) aA5rvP+
insert into b(a, b, c) select d,e,f from b; 09psqXU@I
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) }L1-2
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 &CEZ+\bA
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. hD5G\TR.
4、说明:子查询(表名1:a 表名2:b) a;2Lgv0/
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) *Bgk3(n)
5、说明:显示文章、提交人和最后回复时间 .^%!X!r
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b _Bh ^<D-
6、说明:外连接查询(表名1:a 表名2:b) C;&44cU/]
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c /v,H%8S
7、说明:在线视图查询(表名1:a ) s28rj6q
select * from (SELECT a,b,c FROM a) T where t.a > 1; '[nH]N
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 3:;2Av2(X.
select * from table1 where time between time1 and time2 ,,-g*[/3
select a,b,c, from table1 where a not between 数值1 and 数值2 X-&U-S;
9、说明:in 的使用方法 DfNX@gbo
select * from table1 where a [not] in ('值1','值2','值4','值6') LmKG6>Q1#1
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Mk -Rl
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) #~SQujgB
11、说明:四表联查问题: vQ/&iAyut
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 ..... E4nj*Lp~+
12、说明:日程安排提前五分钟提醒 %j3*j
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 "$VqOSo
13、说明:一条sql 语句搞定数据库分页 \!["U`\.K
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 G/*0*&fW
14、说明:前10条记录 c9
UJ=
select top 10 * form table1 where 范围 A$9^JF0$
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) c8'!>#$
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) )OAd[u<
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 Gg3cY{7
(select a from tableA ) except (select a from tableB) except (select a from tableC) ~HH#aXh*
17、说明:随机取出10条数据 n2JwZ?
select top 10 * from tablename order by newid() Y0||>LX
18、说明:随机选择记录 Y GZX}-
select newid() FD&"k=p+X
19、说明:删除重复记录 Wy2 pa
#Q
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ,KMt9<
20、说明:列出数据库里所有的表名 %S<0l@=5`l
select name from sysobjects where type='U' _Co*"hl>2
21、说明:列出表里的所有的 +s}"&IV%
select name from syscolumns where id=object_id('TableName') A{:PpYs
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 )9L:^i6
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 ?y\gjC6CNG
显示结果: ~9OART='
type vender pcs $ 'B0ZL
电脑 A 1 *[(}rpp M
电脑 A 1 MMpGI^x!-X
光盘 B 2 XkWO -L
光盘 A 2 U2DE"
手机 B 3 .5',w"R
手机 C 3 f,?P1D\
23、说明:初始化表table1 ]&')#YO
TRUNCATE TABLE table1 c:/H}2/C
24、说明:选择从10到15的记录 bk**% ]
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc =c-,uW11[
1?6;Oc^
<3wfY
#;><
i U^tv_1
三、技巧 <4gT8kQ$x
1、1=1,1=2的使用,在SQL语句组合时用的较多 [ET03 nZ
"where 1=1" 是表示选择全部 "where 1=2"全部不选, ;BsPms@U
如: RN0@Q~oTI
if @strWhere !='' _7AR2
begin BnLM ;5
>
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 5/:BtlFx
end VPB,8zb]
else 6d RxfbL
begin F9sVMV
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' z8SrZ#mg
end +w
;2k w
我们可以直接写成 A{5^A)$
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere *20$u% z2
2、收缩数据库 a)+;<GZ~
--重建索引 J!$q"0G'WT
DBCC REINDEX ,~@Nhd~k
DBCC INDEXDEFRAG Ix;9D'^}
--收缩数据和日志 W?5u O
DBCC SHRINKDB rKR2v(c
DBCC SHRINKFILE `wJR^O!e
3、压缩数据库 6]=R#d 7U
dbcc shrinkdatabase(dbname) ,qS-T'[v,(
4、转移数据库给新用户以已存在用户权限 Hoaf3
`n
exec sp_change_users_login 'update_one','newname','oldname' TNA?fm
go 1rr\l`
5、检查备份集 f\W1u#;u)
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' (RP"VEVR
6、修复数据库 B?qLXRv
ALTER DATABASE [dvbbs] SET SINGLE_USER $YM>HZe-
GO Pa.D+
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK OC$Y8Ofr
GO l .8@F
ALTER DATABASE [dvbbs] SET MULTI_USER 6dG:3n}
GO wzr3y}fCe
7、日志清除 u? a*bW
SET NOCOUNT ON JmJ8s hq
DECLARE @LogicalFileName sysname,
J1waiOh
@MaxMinutes INT, ,4bqjkX5q
@NewSize INT "T`Q,
USE tablename -- 要操作的数据库名 xwZcO
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 28KS*5S
@MaxMinutes = 10, -- Limit on time allowed to wrap log. a=<l}`*
@NewSize = 1 -- 你想设定的日志文件的大小(M) Le&SN7I
-- Setup / initialize c~B[<.Qj
DECLARE @OriginalSize int <1HbjRw
SELECT @OriginalSize = size nu1s
FROM sysfiles *C~O[:6D
WHERE name = @LogicalFileName R^`# xQ
SELECT 'Original Size of ' + db_name() + ' LOG is ' + S\"/=|\
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + kKU,|>3h
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' \/3Xb
FROM sysfiles O@@=ZyYwc
WHERE name = @LogicalFileName GXV<fc"1
CREATE TABLE DummyTrans WD=#. $z$
(DummyColumn char (8000) not null) N`FgjnQ`
DECLARE @Counter INT, "XWrd[Df
@StartTime DATETIME, R<}n?f\#JZ
@TruncLog VARCHAR(255) }B{bM<dF
SELECT @StartTime = GETDATE(), K&zp2V
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' "~y@rqIba
DBCC SHRINKFILE (@LogicalFileName, @NewSize) qNI2+<u)j
EXEC (@TruncLog) ('q u#.'
-- Wrap the log if necessary. y$=$Yc&Ub
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired uqaP\
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) yF&"'L
AND (@OriginalSize * 8 /1024) > @NewSize \,<5U
F0
BEGIN -- Outer loop. zJnF#G
SELECT @Counter = 0 0v%ZKvSID
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) EgAM,\
BEGIN -- update W0n/B&C
INSERT DummyTrans VALUES ('Fill Log') o ]UG*2
DELETE DummyTrans s2-`}LL
SELECT @Counter = @Counter + 1 VKW9Rn9Qg
END wb@TYvDt
EXEC (@TruncLog) ,/O,j
SRk
END
czM Thm
SELECT 'Final Size of ' + db_name() + ' LOG is ' + Gj6(ycaS
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + lkNaSz[
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' mM| 313
FROM sysfiles FOB9J.w4
WHERE name = @LogicalFileName
D$W&6'
DROP TABLE DummyTrans (Sr D
SET NOCOUNT OFF D -Goi-4
8、说明:更改某个表 x7qVLpcL3z
exec sp_changeobjectowner 'tablename','dbo' }@
Nurs)%_
9、存储更改全部表 b5kw*h+/'h
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch W\V'o Vt
@OldOwner as NVARCHAR(128), xE$(I<:
@NewOwner as NVARCHAR(128) {H
FF|Dx
AS O?<R.W<QI
DECLARE @Name as NVARCHAR(128) oxN~(H)/ #
DECLARE @Owner as NVARCHAR(128) _^+z2m+~N
DECLARE @OwnerName as NVARCHAR(128) %SW"{GnO^
DECLARE curObject CURSOR FOR pIKQx5;
select 'Name' = name, p<5ED\;N;
'Owner' = user_name(uid) W,<P])
from sysobjects Q;]g9T[)
where user_name(uid)=@OldOwner S2/6VoGE
order by name 8]!%mrS
OPEN curObject r|U'2+vn
FETCH NEXT FROM curObject INTO @Name, @Owner 8`e75%f:2
WHILE(@@FETCH_STATUS=0) mJBvhK9%
BEGIN s68&AB
if @Owner=@OldOwner ''+6qH-.|]
begin 7,.Hj&'B
set @OwnerName = @OldOwner + '.' + rtrim(@Name) |a7W@LVYD
exec sp_changeobjectowner @OwnerName, @NewOwner ?}y{tav=
end a1lF8; [
-- select @name,@NewOwner,@OldOwner os|Y=a
FETCH NEXT FROM curObject INTO @Name, @Owner NdpcfZq
END Nqih LUv
close curObject CAGaZ rx
deallocate curObject k 7 !{p
GO H-&Z+4 +Xs
10、SQL SERVER中直接循环写入数据 f9A^0A?c
declare @i int V2< 4~J2:9
set @i=1 m_{?py@tZ
while @i<30 . zM
begin dgb#PxOMH
insert into test (userid) values(@i) Ho3$T
set @i=@i+1 'Xl[ y
end 9|Z25_sS
小记存储过程中经常用到的本周,本月,本年函数 1
J3h_z6/
Dateadd(wk,datediff(wk,0,getdate()),-1) gv7(-I
Dateadd(wk,datediff(wk,0,getdate()),6) i
*W9 4
Dateadd(mm,datediff(mm,0,getdate()),0) 8*sZ/N.
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ich\`j[i
Dateadd(yy,datediff(yy,0,getdate()),0) =yn|.%b
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) <I}O_:%
上面的SQL代码只是一个时间段 +9S_H(
Dateadd(wk,datediff(wk,0,getdate()),-1) ! }u'%
Dateadd(wk,datediff(wk,0,getdate()),6) crV2T
就是表示本周时间段. iHKWz)0
下面的SQL的条件部分,就是查询时间段在本周范围内的: ?k$3( -
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) PCxv_Svf
而在存储过程中 iqCZIahf
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) <t9#~x#'b
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) %_*q'6K