SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 ;cKH1
5M:D?9E+
ES}. xZ#~
一、基础 \}JrFc%O
1、说明:创建数据库 #Qh>z%Mn^3
CREATE DATABASE database-name b9Y_!Qe
2、说明:删除数据库 - $JO8'TP
drop database dbname >w.'KR0L
3、说明:备份sql server C>X|VP|C
--- 创建 备份数据的 device ]^K;goQv
USE master VFj(M
j`}G
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' /0lC KU!=
--- 开始 备份 =eBmBn
BACKUP DATABASE pubs TO testBack z/ 7$NxJH
4、说明:创建新表 3;_
n{&
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) >A}0Ho
根据已有的表创建新表: LA4<#KP
A:create table tab_new like tab_old (使用旧表创建新表) ;`(R7X
*3
B:create table tab_new as select col1,col2... from tab_old definition only [2
zt ^
5、说明:删除新表 8IGt4UF&?
drop table tabname _1|$P|$P.
6、说明:增加一个列 JA^v
Alter table tabname add column col type 7I}P*%(f
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 -M4p\6)Ge
7、说明:添加主键: Alter table tabname add primary key(col) ``|AgIg
说明:删除主键: Alter table tabname drop primary key(col) 6/tI8H3E
8、说明:创建索引:create [unique] index idxname on tabname(col....) dE5D3ze
删除索引:drop index idxname >xg5z
注:索引是不可更改的,想更改必须删除重新建。 pQWHG#?7
9、说明:创建视图:create view viewname as select statement #NN ewzC<*
删除视图:drop view viewname NfzF.{nh
10、说明:几个简单的基本的sql语句 ^jD1vUL 2:
选择:select * from table1 where 范围 v`DI<Lt
插入:insert into table1(field1,field2) values(value1,value2) sx
9uV
删除:delete from table1 where 范围 3`F) AWzdr
更新:update table1 set field1=value1 where 范围 =Z,5$6%)
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! =X(%Svnp
排序:select * from table1 order by field1,field2 [desc] H&4~Uo.5
总数:select count as totalcount from table1 8=;k"
求和:select sum(field1) as sumvalue from table1 'bu )M1OLi
平均:select avg(field1) as avgvalue from table1 >t <pFh
最大:select max(field1) as maxvalue from table1 &@v<nO-
最小:select min(field1) as minvalue from table1 t'1Y@e
O#eZ<hNV
9V
0}d2d
N|:'XwL
11、说明:几个高级查询运算词 0CAa^Q^w
qp p/8M
M \D]ml~
A: UNION 运算符 OqUr9?+
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 F{m{d?:OA
B: EXCEPT 运算符 1||+6bRP
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 z[nS$]u
C: INTERSECT 运算符 \9{F5Sz
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 6GL=)0Ah
注:使用运算词的几个查询结果行必须是一致的。 T!2=*~A
12、说明:使用外连接 T~xwo
A、left outer join: 3
hKBc0
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 oxz{ ejd{
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 kc$)^E7
B:right outer join: +wO#'D
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 pyZ9OA!PD
C:full outer join: ~DF:lqwWP
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 p9qKLJ*.C
$m| V :/
d8o53a]
二、提升 -db75=
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) {+.r5py
法一:select * into b from a where 1<>1 Q<(YP.k
法二:select top 0 * into b from a 1zxq^BI
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) &^".2)zU
insert into b(a, b, c) select d,e,f from b; O;9?(:_
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ExBUpDQc
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 8wZf]_
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ]wVk+%e
4、说明:子查询(表名1:a 表名2:b) TbvtqM 0
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) b=;nm#cAI
5、说明:显示文章、提交人和最后回复时间 9~\kF5Q"
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b s
+s" MI
6、说明:外连接查询(表名1:a 表名2:b) C.Uju`3
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c NH A 5e<
7、说明:在线视图查询(表名1:a ) b1#dz]
select * from (SELECT a,b,c FROM a) T where t.a > 1; e [h8}F
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 UUe#{6Jx_
select * from table1 where time between time1 and time2 $md%xmQ[
select a,b,c, from table1 where a not between 数值1 and 数值2 c=O,;lWFqm
9、说明:in 的使用方法 *Zk>2<^R
select * from table1 where a [not] in ('值1','值2','值4','值6') &a0r%L()X
10、说明:两张关联表,删除主表中已经在副表中没有的信息 g"VMeW^
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 23F/\2MSG
11、说明:四表联查问题: u.XQ&
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 ..... `:NaEF?Sj
12、说明:日程安排提前五分钟提醒 TUK"nKSZ`.
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 ,:2'YB
13、说明:一条sql 语句搞定数据库分页 LNYKm~cN
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 c}Z6V1]QP
14、说明:前10条记录 r,1e 'd:
select top 10 * form table1 where 范围 }T2xXbU
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) k?B[>aQn.0
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) )!bUR\
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 |SZo'
6
(select a from tableA ) except (select a from tableB) except (select a from tableC) %r\n%$@_
17、说明:随机取出10条数据 21X`h3+=
select top 10 * from tablename order by newid() Dim>
7Wbh
18、说明:随机选择记录 "r4AY
select newid() N2r/ho}8
19、说明:删除重复记录 [lzN !!B!
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) op2Of<{h
20、说明:列出数据库里所有的表名 F9"w6;hh
select name from sysobjects where type='U' xM >W2
21、说明:列出表里的所有的 _gj&$zP
select name from syscolumns where id=object_id('TableName') ;*TIM%6#
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 1/+C5Bp*
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 {$D,?V@%_
显示结果: >et-{(G
type vender pcs =ac_,]z
电脑 A 1 tC?=E#3V
电脑 A 1 n:
ui
光盘 B 2 5|0,X<&
光盘 A 2 MM_k
]-7
手机 B 3 C*=Xk/0
手机 C 3 _9 .(a
23、说明:初始化表table1 fEf_F
r
TRUNCATE TABLE table1 $``1PJoi
24、说明:选择从10到15的记录 JCx
WWre
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc +j_;(Gw7
|y;}zQB-dH
3981ie
VZr>U*J[:
三、技巧 `_I@i]i^
1、1=1,1=2的使用,在SQL语句组合时用的较多 ]B"'}%>ez
"where 1=1" 是表示选择全部 "where 1=2"全部不选, C&LBr|
如: 2"P99$"
if @strWhere !='' }"vW4
begin vy2Q g
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere Y`7~Am/r;&
end -Xu.1S
else z<sg0K8z63
begin QZp6YSz.4
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' /n~\\9#3
end -C-?`R
我们可以直接写成 :bV mgLgG
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere EF7+ *Q9
2、收缩数据库 S1Z2_V
--重建索引 z?/1Kj}xG
DBCC REINDEX omO
S=d!o
DBCC INDEXDEFRAG =!O*/6rz
--收缩数据和日志
/tV/85r
DBCC SHRINKDB 'FlJpA}
DBCC SHRINKFILE b5$JfjI
3、压缩数据库 [ylsz?
dbcc shrinkdatabase(dbname) S:4crI
4、转移数据库给新用户以已存在用户权限 WG*t::NN
exec sp_change_users_login 'update_one','newname','oldname' Q?ahr~qo
go B[=(#W
5、检查备份集 4a0:2 kIKa
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' [${
QzO
6、修复数据库 MObt,[^W
ALTER DATABASE [dvbbs] SET SINGLE_USER 'j^xbikr
GO ]V %.I_
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK WARb"8Kg
GO \P} p5k[
ALTER DATABASE [dvbbs] SET MULTI_USER 3&u_A?;
GO _{t9 x\=
7、日志清除 M` q?Fk
SET NOCOUNT ON E J$36
DECLARE @LogicalFileName sysname, {FRAv(,\
@MaxMinutes INT, I}e3zf>
@NewSize INT i|w8.}0
USE tablename -- 要操作的数据库名 !CXt*/~
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 ]2#
@MaxMinutes = 10, -- Limit on time allowed to wrap log. _Jwq`]Z
@NewSize = 1 -- 你想设定的日志文件的大小(M) NaVQ9ku7VW
-- Setup / initialize F(4?tX T
DECLARE @OriginalSize int ,fK3ZC
SELECT @OriginalSize = size "|;:>{JC
FROM sysfiles lzw3= H
WHERE name = @LogicalFileName ,NnhHb2\
SELECT 'Original Size of ' + db_name() + ' LOG is ' + sK{l 9
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + +iRq8aS_
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' .Ha'p.
FROM sysfiles 56^+;^f^`
WHERE name = @LogicalFileName JdIlWJY
CREATE TABLE DummyTrans 4S~o-`&W
(DummyColumn char (8000) not null) h\plQ[T
DECLARE @Counter INT, ]\/tVn.'
@StartTime DATETIME, jV.g}F+1m
@TruncLog VARCHAR(255) ^~{$wVGa
SELECT @StartTime = GETDATE(), a+hd(JX0~
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' o]nw0q?
DBCC SHRINKFILE (@LogicalFileName, @NewSize) (P&4d~)m
EXEC (@TruncLog) rl9.]~
-- Wrap the log if necessary. g{W;I_P^9
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired x~.:64
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) R@Gq)P9?
AND (@OriginalSize * 8 /1024) > @NewSize &]
\X]p
BEGIN -- Outer loop. u0P)7~%
SELECT @Counter = 0
T+N|R
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) [M.f-x:
BEGIN -- update k>t)g-,2
INSERT DummyTrans VALUES ('Fill Log') (`SRJ$~f
DELETE DummyTrans USFDy
SELECT @Counter = @Counter + 1 *x)Ozfe
END UzXE_S
EXEC (@TruncLog) &/Ro lIHF
END 2X:4CC%5
SELECT 'Final Size of ' + db_name() + ' LOG is ' + gp$Ucfu'
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 2o>)7^9|#<
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 83;NIE;
FROM sysfiles !LkWzn3
WHERE name = @LogicalFileName PW3GL3+
DROP TABLE DummyTrans |_omr&[_
SET NOCOUNT OFF D;UV&.$'v
8、说明:更改某个表 S1D@vnZ3O\
exec sp_changeobjectowner 'tablename','dbo' ^Rx9w!pAN
9、存储更改全部表 Vi4~`;|&b+
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch kId
n6 Wx,
@OldOwner as NVARCHAR(128), A
AHt218
@NewOwner as NVARCHAR(128) J8Yd1.Qj
AS `%09xMPu
DECLARE @Name as NVARCHAR(128) mhW-J6u*
DECLARE @Owner as NVARCHAR(128) +~xnXb1
DECLARE @OwnerName as NVARCHAR(128) &$`yo`
DECLARE curObject CURSOR FOR DGevE~
select 'Name' = name, ^(*n]
'Owner' = user_name(uid) #Rg|BfV-
from sysobjects 0* /{4)r
where user_name(uid)=@OldOwner BTM),
w2
order by name vD 5vbl
OPEN curObject )sho*;_o
FETCH NEXT FROM curObject INTO @Name, @Owner :ss,Hl
WHILE(@@FETCH_STATUS=0) -hkQ2[Ew#
BEGIN [:^-m8QC
if @Owner=@OldOwner $9S(_xdI&
begin Y?ez9o:/#
set @OwnerName = @OldOwner + '.' + rtrim(@Name) ^<LY4^
exec sp_changeobjectowner @OwnerName, @NewOwner R\XKMF3mN3
end Cgz D$`~
-- select @name,@NewOwner,@OldOwner 6sa"O89
FETCH NEXT FROM curObject INTO @Name, @Owner ~G27;Npy
END Z}|(FRVk
close curObject %*#n d
deallocate curObject ;<0LXYL;
GO 0%)i<a!_Z
10、SQL SERVER中直接循环写入数据 ~4?9a(>3
declare @i int 4A9{=~nwT
set @i=1 ?|:BuHkT
while @i<30 $.Q$`/dF
begin zni)<fmju
insert into test (userid) values(@i) rp5(pV7*
set @i=@i+1
BUwONF
end RxMH!^
小记存储过程中经常用到的本周,本月,本年函数 o[H{(f1%
Dateadd(wk,datediff(wk,0,getdate()),-1) :SxW.?[%u
Dateadd(wk,datediff(wk,0,getdate()),6) v\`9;QV5
Dateadd(mm,datediff(mm,0,getdate()),0) p-+K4
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) J[^}u_z
Dateadd(yy,datediff(yy,0,getdate()),0) o!4!"O'E
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) lY*[tmz)
上面的SQL代码只是一个时间段 9<Kj6t_
Dateadd(wk,datediff(wk,0,getdate()),-1) +:3*
Dateadd(wk,datediff(wk,0,getdate()),6) MLFKH
就是表示本周时间段. d~h:~
下面的SQL的条件部分,就是查询时间段在本周范围内的: >a3p >2
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) V5 U?F6
而在存储过程中 >J u]2++lx
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) :_Eqf8T
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) &i!vd/*WlD