SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 |BC/ERms
>p[skN
3om_Z/k
一、基础 Si:$zGL$(
1、说明:创建数据库 FS1>
J%P
CREATE DATABASE database-name $ncJc
2、说明:删除数据库 'T7=.Hq<4
drop database dbname y-/,,,r
3、说明:备份sql server "3r7/>xy
--- 创建 备份数据的 device hYI0S7{G
USE master _?YP0GpU
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' gjGKdTr'
--- 开始 备份 L6ifT`;T
BACKUP DATABASE pubs TO testBack ALOS>Bi&
4、说明:创建新表 :CR1Oy 9
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) ,2R7AHk
根据已有的表创建新表: V(5=-8k
A:create table tab_new like tab_old (使用旧表创建新表) b;O@|HK&~
B:create table tab_new as select col1,col2... from tab_old definition only WC pCWtmy
5、说明:删除新表 6HK
dBW$/
drop table tabname %!vgAH4
6、说明:增加一个列 '20S oVp
Alter table tabname add column col type .GM}3(1fX`
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 $OAak
7、说明:添加主键: Alter table tabname add primary key(col) MB:VACCr
说明:删除主键: Alter table tabname drop primary key(col) XeJ|Z)qZ
8、说明:创建索引:create [unique] index idxname on tabname(col....) kYl')L6
删除索引:drop index idxname {=q$k=ib
注:索引是不可更改的,想更改必须删除重新建。 nB+UxU@
9、说明:创建视图:create view viewname as select statement 5J1q]^
删除视图:drop view viewname Shm$>\~=
10、说明:几个简单的基本的sql语句 n_qDg
选择:select * from table1 where 范围 _Dv<
插入:insert into table1(field1,field2) values(value1,value2) .vm.g=-q
删除:delete from table1 where 范围 2GB+st,
更新:update table1 set field1=value1 where 范围 hHoc>S6^M
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! |LwW/>I
排序:select * from table1 order by field1,field2 [desc] Jy?#@/~
总数:select count as totalcount from table1 KXtc4wra
求和:select sum(field1) as sumvalue from table1 (oiF05n
h
平均:select avg(field1) as avgvalue from table1 8!!iwmH{
最大:select max(field1) as maxvalue from table1 `Qrrnq
最小:select min(field1) as minvalue from table1 |*5QFp
f5droys9
4NN81~v 4
}z/Y
Hv%
11、说明:几个高级查询运算词 [|NgrU_.
Bp.z6x4
3H'+7[~qH
A: UNION 运算符 Bv)4YU
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 Z#i5=,Bk
B: EXCEPT 运算符 Mb2rHUr
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 vA(')"DDT
C: INTERSECT 运算符 j+E[[
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 |iJ+e -_R
注:使用运算词的几个查询结果行必须是一致的。 a33SY6.
12、说明:使用外连接 ju@5D
h
A、left outer join: 7LB#\2
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 u3Jsu=Nx-
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 &7gE=E(M
B:right outer join: qZ8V/
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 3XeCaq'N
C:full outer join: ~H0WHqcy
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 S&rfMRP
Lh M{d
"* Qwaq_
二、提升 ZV=)`E`I|
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 09RJc3XE9
法一:select * into b from a where 1<>1 TN=MZ{L
法二:select top 0 * into b from a XP$ 1CWI
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) A^a9,T
insert into b(a, b, c) select d,e,f from b; :e&P's=
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) wkp|V{k
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 tVf 1]3(_>
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. r|bvpZV
4、说明:子查询(表名1:a 表名2:b) I'_.U]An
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) |c]Y1WwDx
5、说明:显示文章、提交人和最后回复时间 ON$^_l/c
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ~IB~>5U!
6、说明:外连接查询(表名1:a 表名2:b) ZA;wv+hF=
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c vk:m>?(
7、说明:在线视图查询(表名1:a ) &UWSf
select * from (SELECT a,b,c FROM a) T where t.a > 1; E+ 65
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 ?\7" A
select * from table1 where time between time1 and time2 n{~Ws^d
select a,b,c, from table1 where a not between 数值1 and 数值2 );$L#XpB
9、说明:in 的使用方法 7@.UkBOx
select * from table1 where a [not] in ('值1','值2','值4','值6') Mb I';Mq
10、说明:两张关联表,删除主表中已经在副表中没有的信息 ijYSYX@
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) wHQyMq^
11、说明:四表联查问题: r[:)-`]b
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 ..... ~D4%7U"dv
12、说明:日程安排提前五分钟提醒 YdgaZJs
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 ;UU+:~
13、说明:一条sql 语句搞定数据库分页 F:8cd^d~u
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 T[iwP~l
14、说明:前10条记录 ZQHANr=
6
select top 10 * form table1 where 范围 O; qerE?i`
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) RhG9Xw9
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 8"a[W3b
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 A22h+8yG
(select a from tableA ) except (select a from tableB) except (select a from tableC) a&Ti44a[
17、说明:随机取出10条数据 dpO ZqhRs.
select top 10 * from tablename order by newid() zkdyfl5
18、说明:随机选择记录 NU*6MT4
select newid() *8zn\No<,
19、说明:删除重复记录 k,xY\r$
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) "8Ud&o
20、说明:列出数据库里所有的表名 p;dH[NW
select name from sysobjects where type='U' Q0f7gY1-%
21、说明:列出表里的所有的 ]@W.5!5H
select name from syscolumns where id=object_id('TableName') d/8I&{.
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 ?hh4M
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 2$^n@<uZ@
显示结果: Y~T;{&wi
type vender pcs H|4O`I;~(
电脑 A 1 &46h!gW
电脑 A 1 k0{5)Su"xr
光盘 B 2 6@-VLO))O
光盘 A 2 ocCC63J
手机 B 3 I3QK~ V*j)
手机 C 3 #Sj:U1x
23、说明:初始化表table1 cn$E?&-
TRUNCATE TABLE table1 W'2|hP
24、说明:选择从10到15的记录 uI7 d?s
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc EKsL0;FV
|->{NUZ{
'W5r(M4U
}qlU
三、技巧 12*'rU;*
1、1=1,1=2的使用,在SQL语句组合时用的较多 vgSs]g
"where 1=1" 是表示选择全部 "where 1=2"全部不选, (MiEXU~v
如: F#KO!\iA+
if @strWhere !='' Ycypd\q/
begin % (R10G
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere V-U,3=C
end r>1M&Y=<
else UH1AT#?!W
begin `y; s1nL
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' HDUtLUd
end `QV}je
我们可以直接写成 6RV]9
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere =.,]}
2、收缩数据库 3Xgf=yG:M
--重建索引 hM")DmvB4
DBCC REINDEX f?T6Ne'
DBCC INDEXDEFRAG 60P^aj$V
--收缩数据和日志 N"MuAUB:K
DBCC SHRINKDB CZ*c["x2
DBCC SHRINKFILE QLYb>8?"C
3、压缩数据库 TSXa#SKp
dbcc shrinkdatabase(dbname) )p'ZSXb
4、转移数据库给新用户以已存在用户权限 ,-^Grmr4M
exec sp_change_users_login 'update_one','newname','oldname' uTGd{w@]0|
go @G< J+pm
5、检查备份集 6KPM4#61o
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' @s RRcP~
6、修复数据库 _cdrz)T
ALTER DATABASE [dvbbs] SET SINGLE_USER Xwy0dXko
GO 0FmYM@Wc
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK {h/[!I`
GO =?>f[J5
ALTER DATABASE [dvbbs] SET MULTI_USER ($EA/|z
GO e9/:q"*)/
7、日志清除 3jQy"9f
SET NOCOUNT ON >2l1t}"\
DECLARE @LogicalFileName sysname, xayo{l=uGv
@MaxMinutes INT, 4E]w4BG)
@NewSize INT Q!8AFLff4
USE tablename -- 要操作的数据库名 p\OUx Am
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 *4#)or
@MaxMinutes = 10, -- Limit on time allowed to wrap log. O?e38(
@NewSize = 1 -- 你想设定的日志文件的大小(M) f*KNt_|:
-- Setup / initialize =TKu2
DECLARE @OriginalSize int }SYR)eE\
SELECT @OriginalSize = size m`
^o<V&
FROM sysfiles y<(q<V#0!S
WHERE name = @LogicalFileName cbe&SxJ
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 0u[Vd:()v(
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 8<V6W F`e
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' t~vOm
FROM sysfiles t&scvXh
WHERE name = @LogicalFileName r'bctFsD
CREATE TABLE DummyTrans SURbH;[
(DummyColumn char (8000) not null) RcJ.=?I!
DECLARE @Counter INT, bY`
b3
@StartTime DATETIME, {qxFRi#\k
@TruncLog VARCHAR(255) Nr~9] S
SELECT @StartTime = GETDATE(), k;EG28
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' x=-dv8N?
DBCC SHRINKFILE (@LogicalFileName, @NewSize) FPAy.cljJ
EXEC (@TruncLog) vh+ '
W
-- Wrap the log if necessary. n^}M*#
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired J)R2O{ z
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) =AaTn::e/
AND (@OriginalSize * 8 /1024) > @NewSize jnF-kia
BEGIN -- Outer loop. Eo
h4#fZ\N
SELECT @Counter = 0 ad
<z+a
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) Mw{0A\6
BEGIN -- update S
^5EG;[
INSERT DummyTrans VALUES ('Fill Log') `Uz2(zqS
DELETE DummyTrans {?X#E12vf
SELECT @Counter = @Counter + 1 :vsBobiJ
END |[6jf!F
EXEC (@TruncLog) Z"ce1cB
END B!PT|
SELECT 'Final Size of ' + db_name() + ' LOG is ' + FE'|wf
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + XSfl'Fll D
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' K;ncviGu
FROM sysfiles <H; z4
WHERE name = @LogicalFileName RC[mpR;2
DROP TABLE DummyTrans V1yY>
SET NOCOUNT OFF mZ%"""X\Ei
8、说明:更改某个表 dWR-}>
exec sp_changeobjectowner 'tablename','dbo' )># Y,/q
9、存储更改全部表 s)dL^lj;
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch )Bz2-|\
@OldOwner as NVARCHAR(128), 3y#U|&]{
@NewOwner as NVARCHAR(128) *|Bu 7nwg
AS w(,K
DECLARE @Name as NVARCHAR(128) jH5VrN*Q
DECLARE @Owner as NVARCHAR(128) n0\k(@+k
DECLARE @OwnerName as NVARCHAR(128) \OzPDN
DECLARE curObject CURSOR FOR {zckY
select 'Name' = name, K3Sa6"U
'Owner' = user_name(uid) hXAgT!ZD
from sysobjects J2_~iC&;s
where user_name(uid)=@OldOwner MBIlt
1P
order by name a+-X\qN
OPEN curObject +VSq [P
FETCH NEXT FROM curObject INTO @Name, @Owner DqH?:`G
WHILE(@@FETCH_STATUS=0) Q
*]d[
BEGIN _N @h
if @Owner=@OldOwner q
HU}EEv
begin Y^Y1re+}
set @OwnerName = @OldOwner + '.' + rtrim(@Name) hx.ln6=4
exec sp_changeobjectowner @OwnerName, @NewOwner 7A,lQh
end uY_vX\;67z
-- select @name,@NewOwner,@OldOwner ?'8(']/
FETCH NEXT FROM curObject INTO @Name, @Owner 7Rq|N$y.3
END |T;]%<O3E
close curObject :lfUVa{HN
deallocate curObject > N bb0T
GO r3}Q1b&
10、SQL SERVER中直接循环写入数据 f,
iHM
declare @i int xwJ.cy
set @i=1 *.,G;EC^
while @i<30 AY(z9&;6
begin f(*ygI
insert into test (userid) values(@i) L|`(u
set @i=@i+1 sX
c|++
end qib4DT$v-6
小记存储过程中经常用到的本周,本月,本年函数 9 *+X^q'
Dateadd(wk,datediff(wk,0,getdate()),-1) vmGGdj5aI
Dateadd(wk,datediff(wk,0,getdate()),6) 2mt
S\bAF
Dateadd(mm,datediff(mm,0,getdate()),0) q(XO_1W0V
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) +t
JEG:
Dateadd(yy,datediff(yy,0,getdate()),0) pIhy3@bY
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) U3tA"X.K
上面的SQL代码只是一个时间段 O/ih9,
Dateadd(wk,datediff(wk,0,getdate()),-1) ?.~hex#M@
Dateadd(wk,datediff(wk,0,getdate()),6) Q1
5h \!u
就是表示本周时间段. KmX?W/%R
下面的SQL的条件部分,就是查询时间段在本周范围内的: xV\mS+#
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) >"|"Gy (
而在存储过程中 "/g\?Nce
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) !?aL_{7J
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) r"hogmFD;