SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 {x3"/sF
@`qhQ
xt! DS0|*Y
一、基础 }Q 7~tu
1、说明:创建数据库 Et\z^y
CREATE DATABASE database-name e 1W9Z $m
2、说明:删除数据库 F_m[EB
drop database dbname g~5$X{
3、说明:备份sql server 93zoJiLRf
--- 创建 备份数据的 device =WaZy>n}7
USE master hpftVEB
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' N:#"4e
--- 开始 备份 u$7od$&S
BACKUP DATABASE pubs TO testBack =.@{uu;
4、说明:创建新表 Ppw0vaJ^
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) _m;#+`E
根据已有的表创建新表: Vb0((c%&
A:create table tab_new like tab_old (使用旧表创建新表) gbP]!d:I
B:create table tab_new as select col1,col2... from tab_old definition only AxD&_G T
5、说明:删除新表 l{:7*U{d
drop table tabname uG1)cm
B}
6、说明:增加一个列 Y lI/~J
Alter table tabname add column col type YT)jBS~&
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 O|t@p=]
7、说明:添加主键: Alter table tabname add primary key(col) j@jaFsX|
说明:删除主键: Alter table tabname drop primary key(col) S>W_p~@
8、说明:创建索引:create [unique] index idxname on tabname(col....) Z.a`S~U
删除索引:drop index idxname A}(&At%n4
注:索引是不可更改的,想更改必须删除重新建。 !/+'O}@-E
9、说明:创建视图:create view viewname as select statement +tbG^w%
删除视图:drop view viewname !J 3dlUFRO
10、说明:几个简单的基本的sql语句 C;#-2^h
选择:select * from table1 where 范围 #nQZ/[|
插入:insert into table1(field1,field2) values(value1,value2) ac8+?FpK #
删除:delete from table1 where 范围 +|#lUXC
更新:update table1 set field1=value1 where 范围 !d@q T.
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! Ga5O&`h
排序:select * from table1 order by field1,field2 [desc] 3$fzqFo
总数:select count as totalcount from table1 X.u&4SH
求和:select sum(field1) as sumvalue from table1 V%F^6ds$]0
平均:select avg(field1) as avgvalue from table1 J{W<6AK\S
最大:select max(field1) as maxvalue from table1 !f
7CN<
最小:select min(field1) as minvalue from table1 9M3XHj
+!dWQ=W
?:D#\4=US
Ks|qJ3;
11、说明:几个高级查询运算词 Z=VAjJ;i[
-;5WMX6
UY@^KT]
A: UNION 运算符 I9xQ1WJc`
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 JI TQ3UL:W
B: EXCEPT 运算符 ~b.C[s
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 xign!=
C: INTERSECT 运算符 C&Nga
`J
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 vM_UF{a$=
注:使用运算词的几个查询结果行必须是一致的。 dso6ZRx
12、说明:使用外连接 xcBV,[E{
A、left outer join: ]njObU)[zr
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 IYeX\)Gv&
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 jNKu5"HB
B:right outer join: [PVem
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 Ce:2Tw
C:full outer join: FW](GWp`:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 @'go?E)f
L9,;zkgo
6[r-8_
二、提升 P?- #d\qi
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) $
{Y?jJ
法一:select * into b from a where 1<>1 uB;\nj5'D
法二:select top 0 * into b from a a?_!
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) Nqf6CPXE
insert into b(a, b, c) select d,e,f from b; 0K+a/G@
n\
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) o>(I_3J[p
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 xvx5@lx
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. "eqN d"~
4、说明:子查询(表名1:a 表名2:b) dj>ZHdTn
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) ,ALEfepo
5、说明:显示文章、提交人和最后回复时间 ;5i~McH#
t
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b +4 8a..4sN
6、说明:外连接查询(表名1:a 表名2:b) r&$r=f<
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c J.nJ@?O+
7、说明:在线视图查询(表名1:a ) *{_WM}G
select * from (SELECT a,b,c FROM a) T where t.a > 1; QqpXUyHp[
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 F]_w~1
n5
select * from table1 where time between time1 and time2 }6U`/"RfcO
select a,b,c, from table1 where a not between 数值1 and 数值2 zk\YW'x|r
9、说明:in 的使用方法 dRl*rP/
select * from table1 where a [not] in ('值1','值2','值4','值6') Wt$" f
10、说明:两张关联表,删除主表中已经在副表中没有的信息 4z{jWNM)N
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) a]JQZo1$
11、说明:四表联查问题: nSMw 5
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 ..... hUL5V1-j
12、说明:日程安排提前五分钟提醒 ]3u$%vc
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 d A[MjOd3
13、说明:一条sql 语句搞定数据库分页 <a=,{O
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 S6Er#)k
14、说明:前10条记录 tc.`P]R
select top 10 * form table1 where 范围 W3AtO
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) UbWeE,T~S
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) bSK> p3
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 %Z:07|57I[
(select a from tableA ) except (select a from tableB) except (select a from tableC) S,Y\ox-
17、说明:随机取出10条数据 `5J`<BPs
select top 10 * from tablename order by newid() <B+xE?v4
18、说明:随机选择记录 itH`
s<E
select newid() ';HNQe?vT
19、说明:删除重复记录 hv]}b'M$
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) orT%lHwjL
20、说明:列出数据库里所有的表名 WF'Di4
select name from sysobjects where type='U' 8-f2$
21、说明:列出表里的所有的 m+jW+
select name from syscolumns where id=object_id('TableName') 0uw3[,I
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 pwu8LQ3b{O
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 !YM;5vte+
显示结果: #$W bYL|
type vender pcs \Z?.Po`!j
电脑 A 1 at N%csA0
电脑 A 1 {pzu1*
光盘 B 2 J83{&N2u
光盘 A 2 $|0?$U7!
手机 B 3 L%hVts'
手机 C 3 [/P}1
c[)U
23、说明:初始化表table1 3U.?Jbm-8
TRUNCATE TABLE table1 VG)Y$S8.>
24、说明:选择从10到15的记录 8w 2$H
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 3#d?
<KBzZ
!n5
aDDs"DXx
In3},x+$
三、技巧 }3^b1D>2O
1、1=1,1=2的使用,在SQL语句组合时用的较多 G1:*F8q
"where 1=1" 是表示选择全部 "where 1=2"全部不选, W*S!}ZT`
如: ;!k{{Xndd
if @strWhere !='' gwm}19JC
begin f:w#r.]
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere !F^j\
end |z]O@@j$
else Xp_3EQl
begin l.Psh7B2
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ".@}]z8
end Xa=M{x
我们可以直接写成 2D?V0>/
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere ?zS
t
2、收缩数据库 dg(fD>+
--重建索引 JGLjx"Y
DBCC REINDEX JA")L0a_
DBCC INDEXDEFRAG #z(JYw,
--收缩数据和日志 Y{Yp N
DBCC SHRINKDB vX9B^W||x
DBCC SHRINKFILE z?b[ 6DLV;
3、压缩数据库 )bl''
yO
dbcc shrinkdatabase(dbname) z~Ec *
4、转移数据库给新用户以已存在用户权限 |aaoi4OJ
exec sp_change_users_login 'update_one','newname','oldname' q#=}T~4j
go T+$Af,~
5、检查备份集 J&vmW}&
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' A_:YpQ07@
6、修复数据库 }@+{;"
ALTER DATABASE [dvbbs] SET SINGLE_USER <"&I'9
GO o<pb!]1
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK c@uNA0
p
GO lZ\8$,B)
ALTER DATABASE [dvbbs] SET MULTI_USER );m7;}gE
GO ~9tPT0^+
7、日志清除 sz7|2OV"
SET NOCOUNT ON p!XB\%sv'"
DECLARE @LogicalFileName sysname, dxz.%a@PW
@MaxMinutes INT, D09/(%4j
@NewSize INT t V]BcDp
USE tablename -- 要操作的数据库名 hYj!*P)uV
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 ;$0)k(c9
@MaxMinutes = 10, -- Limit on time allowed to wrap log. KX|7mr90K
@NewSize = 1 -- 你想设定的日志文件的大小(M) %wc=Mf
-- Setup / initialize qaj~q(j~C
DECLARE @OriginalSize int ]jkaOj
SELECT @OriginalSize = size t7(#Cuv-
FROM sysfiles dHAI4Yf4U
WHERE name = @LogicalFileName <<ze84E
SELECT 'Original Size of ' + db_name() + ' LOG is ' + K~U5jpc
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + I_h8)W
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' cTq}H_hC
FROM sysfiles C}7c:4c
WHERE name = @LogicalFileName !8z,}HUdK
CREATE TABLE DummyTrans z. 6-D
(DummyColumn char (8000) not null) A.D@21py
DECLARE @Counter INT, gGtl*9a=
@StartTime DATETIME, ]V `L\
@TruncLog VARCHAR(255) 52zD!(
SELECT @StartTime = GETDATE(), nw)yK%`;M
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' U}=o3u
DBCC SHRINKFILE (@LogicalFileName, @NewSize) ;q3"XLV(T[
EXEC (@TruncLog) P:p@Iep
-- Wrap the log if necessary. [q<Vm-
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired Z2%ySO
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) |z5`h
AND (@OriginalSize * 8 /1024) > @NewSize 5Az4 <
BEGIN -- Outer loop. O< \i{4}}
SELECT @Counter = 0 :*/'W5iM
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) a$~pAy5C
BEGIN -- update Z0(}doh
INSERT DummyTrans VALUES ('Fill Log') Hxw 7Q?F
DELETE DummyTrans j$he5^GC
SELECT @Counter = @Counter + 1 ;QiSz=DyA
END k9'`<82Y
EXEC (@TruncLog) ^xpiNP!?a
END _xyq25/
SELECT 'Final Size of ' + db_name() + ' LOG is ' + Zeeixg-1<
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + npJyVh47
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 3Dm`8Xt
FROM sysfiles 7M#irCX
WHERE name = @LogicalFileName )PU_'n=>
DROP TABLE DummyTrans ` !JcQ'u
SET NOCOUNT OFF #cZ<[K q6
8、说明:更改某个表 [5iBXOmpS=
exec sp_changeobjectowner 'tablename','dbo' ;mi+[`E
9、存储更改全部表 Oh|KbM*vS
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch =:5o"g
@OldOwner as NVARCHAR(128), 1U/ dc.x5
@NewOwner as NVARCHAR(128) &2,0?ra2&
AS xv+47.?N
DECLARE @Name as NVARCHAR(128) Q96"^Hd
DECLARE @Owner as NVARCHAR(128) ?FRuuAS
DECLARE @OwnerName as NVARCHAR(128) gaIN]9wLm
DECLARE curObject CURSOR FOR ]{/1F:bcQ
select 'Name' = name, Y[8GoqE|
'Owner' = user_name(uid) L
PDx3MS
from sysobjects 'on8r*
where user_name(uid)=@OldOwner T+0Z2H
order by name "E6*.EtTN#
OPEN curObject c^?+"7oO0
FETCH NEXT FROM curObject INTO @Name, @Owner B9&$sTAB
WHILE(@@FETCH_STATUS=0) q0>@!1Wb
BEGIN P>i!f!o*I
if @Owner=@OldOwner %#zqZ|q
begin UP})j.z
set @OwnerName = @OldOwner + '.' + rtrim(@Name) m"r=p
exec sp_changeobjectowner @OwnerName, @NewOwner "6<L)
8
end 3O'6 Ae
-- select @name,@NewOwner,@OldOwner )Gu:eYp+`
FETCH NEXT FROM curObject INTO @Name, @Owner $&C~Qti|G
END $YNW T\FE
close curObject Fr,qVYf
deallocate curObject RTJ\|#w
GO t.ci!#/d
10、SQL SERVER中直接循环写入数据 !=Hu?F p
declare @i int e[:i`J2
set @i=1 z+k[HE^S
while @i<30 WcG}9)9
begin }C<<l5/ z
insert into test (userid) values(@i) !I8m(axW
set @i=@i+1 v"LH^!/
end SFiK_;
小记存储过程中经常用到的本周,本月,本年函数 8(b
C.
Dateadd(wk,datediff(wk,0,getdate()),-1) 0?{Y6:d+
Dateadd(wk,datediff(wk,0,getdate()),6) qSg=[7XOO
Dateadd(mm,datediff(mm,0,getdate()),0) k,kr7'Q
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) EJz?GM
Dateadd(yy,datediff(yy,0,getdate()),0) K4{1}bU{>
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) zIeJ[J@
上面的SQL代码只是一个时间段 (9phRo)>
Dateadd(wk,datediff(wk,0,getdate()),-1) u@{z
xYn
Dateadd(wk,datediff(wk,0,getdate()),6) FS1>
J%P
就是表示本周时间段. 3rUuRsXn
下面的SQL的条件部分,就是查询时间段在本周范围内的: )qL UHE=
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) [2 yxTK
而在存储过程中 g9XAUZe
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) bh~"LQS1
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) @uJ^k
>B