SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 6z@OGExmd#
Yi1*o?
L+2!Sc,>
一、基础
::Y
1、说明:创建数据库 ~Fv&z'R
CREATE DATABASE database-name 9.ZhkvR4A
2、说明:删除数据库 zq-"jpZG
drop database dbname R[ p. )F7
3、说明:备份sql server itb0dF1G
--- 创建 备份数据的 device I9P<!#q>
USE master 6r"uDV #0
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' r1&b#r>
--- 开始 备份 -]c5**O}
BACKUP DATABASE pubs TO testBack l^4[;%*f#l
4、说明:创建新表 k .? aq
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) wOQ-sp0q0
根据已有的表创建新表: z)"7qqA
A:create table tab_new like tab_old (使用旧表创建新表) dO.?S89L
B:create table tab_new as select col1,col2... from tab_old definition only cY?<
W/
5、说明:删除新表 QxCZ<|
drop table tabname T0n=nC}<
6、说明:增加一个列 %\#s@8=2u
Alter table tabname add column col type J&UFP{)
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 :UMg5eZ
7、说明:添加主键: Alter table tabname add primary key(col) *%_:[>
说明:删除主键: Alter table tabname drop primary key(col) ^p(t*%LM
8、说明:创建索引:create [unique] index idxname on tabname(col....) e\i K
删除索引:drop index idxname 5g
,u\`
注:索引是不可更改的,想更改必须删除重新建。 {n}6
9、说明:创建视图:create view viewname as select statement +%(iGI{
删除视图:drop view viewname c7T9kV8hS
10、说明:几个简单的基本的sql语句 Gb+cT
选择:select * from table1 where 范围 %J4]T35^2
插入:insert into table1(field1,field2) values(value1,value2) 3`_jNPV1
删除:delete from table1 where 范围 bf2R15|t5`
更新:update table1 set field1=value1 where 范围 xExy?5H7
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! q+2yp&zF
排序:select * from table1 order by field1,field2 [desc] NfcY30}:
总数:select count as totalcount from table1 7><n e|%
求和:select sum(field1) as sumvalue from table1 CK[2duf^~
平均:select avg(field1) as avgvalue from table1 B;tU+36nM
最大:select max(field1) as maxvalue from table1 Cd)e_&
最小:select min(field1) as minvalue from table1 Et~b^8$>
mN3}wJ}J
h+F@apUS
']^e,9=Q
11、说明:几个高级查询运算词 G|FF
jq(3y|6,
CBdSgHA3>
A: UNION 运算符 7 y}b (q=
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 k+S+: 5
B: EXCEPT 运算符 -a(f-
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 Jhu<^pjs
C: INTERSECT 运算符 _l]`Og@Y
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 <K!5N&vh
注:使用运算词的几个查询结果行必须是一致的。 F4X/ )$Dk
12、说明:使用外连接 'TpW-r:
A、left outer join: l!e8=QlJ
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 l=*^FK]L`
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 |sz`w^#
B:right outer join: )3v0ex@Jl
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 *0M#{HQ
C:full outer join: 8[5%l7's
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 *9e T#dH
_ b}\h,Ky
hH:7
二、提升 Nw $io8:d
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) vco/h
法一:select * into b from a where 1<>1 I!lzOg4~
法二:select top 0 * into b from a SzkF-yRd
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) s`Fv!
insert into b(a, b, c) select d,e,f from b; :7%JD .;W
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ^zkTV_,cRp
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 Rt~Aud[
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. NWPL18*C
4、说明:子查询(表名1:a 表名2:b) L^rtypkJ
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) u.iFlU
5、说明:显示文章、提交人和最后回复时间 +kTAOfM
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ,pir,Eozg
6、说明:外连接查询(表名1:a 表名2:b) .E!7}O6
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c )a,-Hc:Vz
7、说明:在线视图查询(表名1:a ) jzV*V<
select * from (SELECT a,b,c FROM a) T where t.a > 1; >U~.I2sz
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 "{;]T
select * from table1 where time between time1 and time2 AWCzu5ve
select a,b,c, from table1 where a not between 数值1 and 数值2 Mh'QD)28c
9、说明:in 的使用方法 LA$uD?YA
select * from table1 where a [not] in ('值1','值2','值4','值6') 1Lwi?~!LI
10、说明:两张关联表,删除主表中已经在副表中没有的信息 C3-l(N1O{
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) pVn6>\xa
11、说明:四表联查问题:
U,)Ngnd
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 ..... _v4TyJ
12、说明:日程安排提前五分钟提醒 k\_>/)g
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 W]5kM~Q@
13、说明:一条sql 语句搞定数据库分页 5)V]qV$
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 XG<J'3
14、说明:前10条记录 `
_()R`=
select top 10 * form table1 where 范围 q:#,b0|bv
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) -_'M
*-
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) $1oU^VY
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 ]+)z}lr8 C
(select a from tableA ) except (select a from tableB) except (select a from tableC) N%6jZmKip
17、说明:随机取出10条数据 PYr#vOH
select top 10 * from tablename order by newid() {r.#R|
4v
18、说明:随机选择记录 kac@yQD
select newid() 6}R^L(^M
19、说明:删除重复记录 vrn IEur
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) \*6%o0c
20、说明:列出数据库里所有的表名 :Oo
select name from sysobjects where type='U' kM]:~b2
21、说明:列出表里的所有的 aAO[Y"-:,Y
select name from syscolumns where id=object_id('TableName') qhVDC
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 is{I5IR\/
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 Gh0H)
q
显示结果: mB;W9[
type vender pcs <oV
_EZ
电脑 A 1 i:OD)l
电脑 A 1 lT$Vv=M
光盘 B 2 tr7FV1p
光盘 A 2 }aXc,;Ps
手机 B 3 hd9fD[5
手机 C 3 xuO5|{h
23、说明:初始化表table1 N-jFA8n
TRUNCATE TABLE table1 TJ7on.;
24、说明:选择从10到15的记录 UY
j
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc JI )+
1Y@6oT
.r SeJZzuj
~CldqXeI
三、技巧 :Y
y+%
1、1=1,1=2的使用,在SQL语句组合时用的较多 B:ddlxT$
"where 1=1" 是表示选择全部 "where 1=2"全部不选, h0Acpd2
如: eJE?H]
if @strWhere !='' 2f`u?T
begin ru3nnF_I
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere s['F?GWg
end JO5~Vj_"
else ^C>i(j&
begin Lcplc"C
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ?v#t{e0eQ
end MR%M[SK1
我们可以直接写成 x ' 3<F
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere fS-#dJC";`
2、收缩数据库 !40{1U&@a`
--重建索引 C2AP
DBCC REINDEX ;z#D%#Ztq
DBCC INDEXDEFRAG Ia)wlA02S
--收缩数据和日志 sq*R)cZ
DBCC SHRINKDB U/yYQZ\)
DBCC SHRINKFILE 56u'XMB?
3、压缩数据库 ckP&N:tC
dbcc shrinkdatabase(dbname) RmO-".$yt
4、转移数据库给新用户以已存在用户权限 c;w
cgU
exec sp_change_users_login 'update_one','newname','oldname' Y%p"RB[
go 4a>z]&s
5、检查备份集 !OPK?7
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' _.J{U0N
6、修复数据库 ^w^cYM,
ALTER DATABASE [dvbbs] SET SINGLE_USER P\iw[m7O
GO /+2^xEIjE
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK @`k!7?
Sq
GO =Z2U
ALTER DATABASE [dvbbs] SET MULTI_USER en!cu_]t
GO 6 )0$UW
7、日志清除 WXNJc
SET NOCOUNT ON nfy"M),et
DECLARE @LogicalFileName sysname, ?Z(
6..&
@MaxMinutes INT, -}2q-
@NewSize INT [sFD-2y
USE tablename -- 要操作的数据库名 ZNFn^iuQ
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 eN>=x40
@MaxMinutes = 10, -- Limit on time allowed to wrap log. ~yt+xWV
@NewSize = 1 -- 你想设定的日志文件的大小(M) _zJY1cr
-- Setup / initialize "6
dC
DECLARE @OriginalSize int -#3B>VY
SELECT @OriginalSize = size / !jd%,G
FROM sysfiles U;OJ.a9
WHERE name = @LogicalFileName 2 'xT%
SELECT 'Original Size of ' + db_name() + ' LOG is ' + )oG_x{
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + yXc/Nl%
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' :2 ?dl:l
FROM sysfiles M^mS#<!y
WHERE name = @LogicalFileName oQ8W0`bZa
CREATE TABLE DummyTrans @luv;X^%
(DummyColumn char (8000) not null) Eo)Q> AM
DECLARE @Counter INT, ~8`r.1aUO
@StartTime DATETIME, e_g7E+6
@TruncLog VARCHAR(255) 0u
QqPF t
SELECT @StartTime = GETDATE(), b,D+1'
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' hX$k8 o0
DBCC SHRINKFILE (@LogicalFileName, @NewSize) GpN tvo~
EXEC (@TruncLog) } UHuFff,
-- Wrap the log if necessary. 76}
N/C
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 0mH>fs 4
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) (J\"\#/d
AND (@OriginalSize * 8 /1024) > @NewSize q<r{ps
BEGIN -- Outer loop. m$*dPje
SELECT @Counter = 0 cQu1WgQ
G
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ?*tpW75hR[
BEGIN -- update YX18!OhQ
INSERT DummyTrans VALUES ('Fill Log') v)d\
5#7
DELETE DummyTrans /0!6;PC<
SELECT @Counter = @Counter + 1 50l=B]M
END ~k+-))pf
EXEC (@TruncLog) 6~&4>2b0f
END `WC~cb\
SELECT 'Final Size of ' + db_name() + ' LOG is ' + b0tr)>d
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + ;-n+=@]7
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' ~${.sD\
FROM sysfiles KxGK`'E'r
WHERE name = @LogicalFileName P`Anf_
DROP TABLE DummyTrans f`RcfYt
SET NOCOUNT OFF o9<jj> R;
8、说明:更改某个表 r?\hZ* |M
exec sp_changeobjectowner 'tablename','dbo' @/`b:sv&*
9、存储更改全部表 <{9E.6G`n
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch t{Q9Kv
@OldOwner as NVARCHAR(128), #";(&|7
@NewOwner as NVARCHAR(128) {#zJx(2yG
AS C \H%4p1r
DECLARE @Name as NVARCHAR(128) :I+%v
DECLARE @Owner as NVARCHAR(128) fHb0pp\[.
DECLARE @OwnerName as NVARCHAR(128) 3vHEPm]
DECLARE curObject CURSOR FOR O>Xyl4U
select 'Name' = name, $a(wM1S4
'Owner' = user_name(uid) `+\+
from sysobjects +<"sC+2
where user_name(uid)=@OldOwner oslrv7EK
order by name IpB0~`7YI
OPEN curObject CcLP/
FETCH NEXT FROM curObject INTO @Name, @Owner x>!#8?-h
WHILE(@@FETCH_STATUS=0) n$axqvG
BEGIN PLw;9^<
if @Owner=@OldOwner ;5q=/
begin 6S2D\Bt,_
set @OwnerName = @OldOwner + '.' + rtrim(@Name) *
"~^k^_b}
exec sp_changeobjectowner @OwnerName, @NewOwner 31
QT
end `Q,moz
-- select @name,@NewOwner,@OldOwner Qi w "x,
FETCH NEXT FROM curObject INTO @Name, @Owner *9`@
END iU~oPp[e
close curObject D5]T.8kX(7
deallocate curObject O6YYOmt3
GO BQ)zm
10、SQL SERVER中直接循环写入数据 pI( OI>~3
declare @i int )4D |sN
set @i=1 H--(zxK
while @i<30 ,-vbR&
begin ZxwI< T:&
insert into test (userid) values(@i) +'N?`l6<
set @i=@i+1 8qrE<RHU@
end i?A4uyYwS
小记存储过程中经常用到的本周,本月,本年函数 :jT1=PfL
Dateadd(wk,datediff(wk,0,getdate()),-1) {Tm31f(oD
Dateadd(wk,datediff(wk,0,getdate()),6) ](aXZ<,
Dateadd(mm,datediff(mm,0,getdate()),0) Z'/:
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ]Yp;8#:1
Dateadd(yy,datediff(yy,0,getdate()),0) V'mQ{[{R
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) C^2Tql
上面的SQL代码只是一个时间段 vO&%sjvH
Dateadd(wk,datediff(wk,0,getdate()),-1) aHXd1\6m
Dateadd(wk,datediff(wk,0,getdate()),6) E-MEMran4
就是表示本周时间段. 2Rc#{A
下面的SQL的条件部分,就是查询时间段在本周范围内的: Oq|RMl
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) * A@~!@XE4
而在存储过程中 /Pxt f~$
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) *=$Jv1"Q
+
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) bsmZR(EnU