SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 V *=To
:X[(ymWNE
-
Z "w
一、基础 oC>QJ(o,8
1、说明:创建数据库 (Q !4\Gy
CREATE DATABASE database-name <@n/[ +3
2、说明:删除数据库 Q3#-q>;7
drop database dbname @oC8:
3、说明:备份sql server h0NM5
--- 创建 备份数据的 device o#{D;'
USE master ;$@7iL
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' XM3N>OR.
--- 开始 备份 @.fuR#
BACKUP DATABASE pubs TO testBack e*uaxh+7
4、说明:创建新表 irCS}Dbw
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) euM7>
$`
根据已有的表创建新表: $}<+~JpGfP
A:create table tab_new like tab_old (使用旧表创建新表) lhTjG,U=
B:create table tab_new as select col1,col2... from tab_old definition only )W'l^R4W
5、说明:删除新表 F\+wM*:U
drop table tabname H,qIHQW#
6、说明:增加一个列 hGcq>Cvf
Alter table tabname add column col type #d%'BUde
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 n6;jIf|
7、说明:添加主键: Alter table tabname add primary key(col) i TY4X:x
说明:删除主键: Alter table tabname drop primary key(col) d$s1l
8、说明:创建索引:create [unique] index idxname on tabname(col....) X'Q$v~/
删除索引:drop index idxname Vb06z3"r
注:索引是不可更改的,想更改必须删除重新建。 T#^
9、说明:创建视图:create view viewname as select statement >#B%gxff
删除视图:drop view viewname 4EzmH)4G
10、说明:几个简单的基本的sql语句 #M6@{R2_
选择:select * from table1 where 范围 Y((s<]7
插入:insert into table1(field1,field2) values(value1,value2) %y33evX/B
删除:delete from table1 where 范围 goi.'8M|/b
更新:update table1 set field1=value1 where 范围 (,PO(
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! JxI}#iA
排序:select * from table1 order by field1,field2 [desc] vpx8GiV
总数:select count as totalcount from table1 AwB ]0H
求和:select sum(field1) as sumvalue from table1 1?"vKm
平均:select avg(field1) as avgvalue from table1 r00waw>C\
最大:select max(field1) as maxvalue from table1 p~I+ZYWF'
最小:select min(field1) as minvalue from table1 nnIBN4
o:_}=1nh
s
S8Z5k;
^8aj\xe(
11、说明:几个高级查询运算词 u&`7 C
_n_lO8mK
7f#[+i
A: UNION 运算符 QJp
_>K
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 6}
!n0
B: EXCEPT 运算符 aT[Z#Zd, N
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 =?T\zLN=
C: INTERSECT 运算符 ?"PUw3V3lB
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 8 s!0Z1Roc
注:使用运算词的几个查询结果行必须是一致的。 "aK3
ylz;
12、说明:使用外连接 DDn@M|*$
A、left outer join: j$^3
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 K+ xiov-r?
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 *7.!"rb8A
B:right outer join: Gvv~P3Dm
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 i 4
KW
C:full outer join: 3N(s)N_P M
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 p>=YPi/d
?8. $A2(Xw
j[gX"PdQ
二、提升 lDO9GNz$
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 4&)*PKq
法一:select * into b from a where 1<>1 ]uX'[Z}t
法二:select top 0 * into b from a *}Zd QJL
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) cBM
A.'uIL
insert into b(a, b, c) select d,e,f from b; ),0_ C\
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) z`((l#(
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 eIK8J,-
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. +ZtqR
4、说明:子查询(表名1:a 表名2:b) xn1
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) R_\{a*lV0
5、说明:显示文章、提交人和最后回复时间 C_=! ( @`8
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b |{+D65R
6、说明:外连接查询(表名1:a 表名2:b) ln)_Jf1r
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 8s pGDg\g
7、说明:在线视图查询(表名1:a ) CL|t!+wU/
select * from (SELECT a,b,c FROM a) T where t.a > 1; :}TT1@
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 ej>8$^y
select * from table1 where time between time1 and time2
]p:x,%nm
select a,b,c, from table1 where a not between 数值1 and 数值2 6+BR5Nr
9、说明:in 的使用方法 KOGbC`TN<
select * from table1 where a [not] in ('值1','值2','值4','值6') ibex:W^
10、说明:两张关联表,删除主表中已经在副表中没有的信息 d*Dq=.F(
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) *:bNK5I.t
11、说明:四表联查问题: y$7Fq'
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 ..... bKj#HHy\I
12、说明:日程安排提前五分钟提醒 X0J@c "%0
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 a \B<(R.
13、说明:一条sql 语句搞定数据库分页 e~=fo#*2?@
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 q.FgX
14、说明:前10条记录 0e9W>J9
select top 10 * form table1 where 范围 1w'iD
X
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) ~F^=7oq
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) |_8::kir:
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 g<{/mxv/
(select a from tableA ) except (select a from tableB) except (select a from tableC) RK#e7
17、说明:随机取出10条数据 [>::@[
select top 10 * from tablename order by newid() _aL:XKM
18、说明:随机选择记录 ^RrufwUA
select newid() :XcU @m
19、说明:删除重复记录 9d^o2Yo
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) #ebT$hf30
20、说明:列出数据库里所有的表名 #>GUfhou)
select name from sysobjects where type='U' Bu">)AnN
21、说明:列出表里的所有的 T!eeMsI
select name from syscolumns where id=object_id('TableName') xz[a3In+
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 PmyS6a@
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 He^+>XIam
显示结果: YUJlQ2e(
type vender pcs {co(w
7
电脑 A 1 kX."|]
电脑 A 1 E8J`7sa
光盘 B 2 +Tc<|-qQn
光盘 A 2 @4Z>;
手机 B 3 Q7UFF
手机 C 3 7)S`AQ2:)
23、说明:初始化表table1 I
\zM\^S>]
TRUNCATE TABLE table1 _C~e(/=z
24、说明:选择从10到15的记录 P\KP )bkC
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc P}N%**>`
a{^[<
>
nY<J
9"1 0:\U
三、技巧 eG9tn{
1、1=1,1=2的使用,在SQL语句组合时用的较多 KL,=Z&.<=
"where 1=1" 是表示选择全部 "where 1=2"全部不选, dN\Byl(6
如: P;bl+a'gu
if @strWhere !='' 4_3Jpz*
begin v>YdPQky
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere ^%-$8sV
end DhV($&*M
else su/l'p'
begin )Y}t~ Zfx
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' :,%~rR
end 87c7p=/0`
我们可以直接写成 /1ooOq]
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere >'wl)j$
2、收缩数据库 trnjOm
--重建索引 8<t6_* f
DBCC REINDEX Pe8WBr;`
DBCC INDEXDEFRAG xCFk1%qf
--收缩数据和日志 R}c,ahd
DBCC SHRINKDB %&5 !vK
DBCC SHRINKFILE $UavM|
3、压缩数据库 z:-a7_
dbcc shrinkdatabase(dbname) _O2},9L n
4、转移数据库给新用户以已存在用户权限 vt<r_&+ pJ
exec sp_change_users_login 'update_one','newname','oldname' W,5A|Q~
go U(3+*'8r,1
5、检查备份集 5:/
zbt\C
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' I!&|L0Qq
6、修复数据库 v+f:VA
ALTER DATABASE [dvbbs] SET SINGLE_USER a'U7 t
GO &$t BD@7
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK `}#(Ze*V:
GO uQazUFw
ALTER DATABASE [dvbbs] SET MULTI_USER '; ,DgR;'
GO n3t1'_/TU}
7、日志清除 h
1G`z
SET NOCOUNT ON v]\io#
DECLARE @LogicalFileName sysname, eyf\j,xP&
@MaxMinutes INT, iM+K&\{_h
@NewSize INT )$Xd#bzD|
USE tablename -- 要操作的数据库名 A9\m.3jo
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 Y,?s-AB
@MaxMinutes = 10, -- Limit on time allowed to wrap log. ,S
E5W2a]
@NewSize = 1 -- 你想设定的日志文件的大小(M) ]\w0u7}
-- Setup / initialize zOD5a=[1
DECLARE @OriginalSize int X>:@`}bq
SELECT @OriginalSize = size p0~=
FROM sysfiles 9YRoWb{y
WHERE name = @LogicalFileName CwZ+Pn0
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 2%U)y;$m2
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
(M5w:qbR
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' $7eO33Bm
FROM sysfiles i71,
WHERE name = @LogicalFileName u[9i>7}9
CREATE TABLE DummyTrans MEMD8:['
(DummyColumn char (8000) not null) Y~EKMowI&e
DECLARE @Counter INT, RB.&,1
@StartTime DATETIME, 3XdN\xc
@TruncLog VARCHAR(255) @-nCK Yj
SELECT @StartTime = GETDATE(), Yh)yp?
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' S/G6NBnbS
DBCC SHRINKFILE (@LogicalFileName, @NewSize) &cztUM(
EXEC (@TruncLog) ,}2yxo;i
-- Wrap the log if necessary. QEK,mc3
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired OY7\*wc:
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) q+f]E&':
AND (@OriginalSize * 8 /1024) > @NewSize [T#5$J
BEGIN -- Outer loop. rTYDa3
SELECT @Counter = 0 R}njFQvS)
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) QLrFAV
BEGIN -- update O^ZOc0<
INSERT DummyTrans VALUES ('Fill Log') 4of3#M
DELETE DummyTrans xO)vn\uJ
SELECT @Counter = @Counter + 1 c;c'E&9P]
END ;> **+ezF
EXEC (@TruncLog)
/B)ZB})z
END H6(kxpOI\
SELECT 'Final Size of ' + db_name() + ' LOG is ' + s8Kf$E^?e.
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + lG12Su/
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 7|LJwXQ-
FROM sysfiles qawb9Iud0
WHERE name = @LogicalFileName XlIRedZ{
DROP TABLE DummyTrans p>4$&-
SET NOCOUNT OFF P.Pw.[:3
8、说明:更改某个表 ~6m-2-14q
exec sp_changeobjectowner 'tablename','dbo' uqwB`<>KJ
9、存储更改全部表 zJJ
KLr;
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch P5/K?I~/So
@OldOwner as NVARCHAR(128), Bf!i(gM
@NewOwner as NVARCHAR(128) s$`g%H>
AS Fq/?0B8
DECLARE @Name as NVARCHAR(128) wEL$QOu$
DECLARE @Owner as NVARCHAR(128) So; ;
DECLARE @OwnerName as NVARCHAR(128) YY-{&+,
DECLARE curObject CURSOR FOR nD6mLNi%a
select 'Name' = name, 6}^0/76^,
'Owner' = user_name(uid) d2lOx|jt
from sysobjects 4<._)_m
where user_name(uid)=@OldOwner b);Pw"_2
order by name RaT(^b(
OPEN curObject +;~JHx.~X
FETCH NEXT FROM curObject INTO @Name, @Owner y;Xb."e~
WHILE(@@FETCH_STATUS=0) R r! PU
BEGIN ofbNg_K>
if @Owner=@OldOwner 6OR5zXpk
begin 6Ug(J$Ouh
set @OwnerName = @OldOwner + '.' + rtrim(@Name) s\QhCS
exec sp_changeobjectowner @OwnerName, @NewOwner RK?b/9y
end lxoc.KDtR
-- select @name,@NewOwner,@OldOwner cAq>|^f0a
FETCH NEXT FROM curObject INTO @Name, @Owner 2GOQ| Z
END &09z`*,
close curObject }@MOkj
deallocate curObject >!O3 jb k
GO Nf8."EDUW
10、SQL SERVER中直接循环写入数据 YSwAu,$jf
declare @i int !Cxo4Twg
set @i=1 1~:7W
while @i<30 (\m4o
begin xc dy/J&
insert into test (userid) values(@i) {[WEA^C~Q
set @i=@i+1 nN" Y~W^k
end q !\Ht2$b
小记存储过程中经常用到的本周,本月,本年函数 2KVMQH`B9
Dateadd(wk,datediff(wk,0,getdate()),-1) L4`bGZl55
Dateadd(wk,datediff(wk,0,getdate()),6) ?95^&4Oh0
Dateadd(mm,datediff(mm,0,getdate()),0) f`'? 2
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) K=Z~$)Og)
Dateadd(yy,datediff(yy,0,getdate()),0) p\I,P2on
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) (dD+?ZOO
上面的SQL代码只是一个时间段 ,73kh
Dateadd(wk,datediff(wk,0,getdate()),-1) )\!_`ob
Dateadd(wk,datediff(wk,0,getdate()),6) wY|&qX,
就是表示本周时间段. W^; wr#
下面的SQL的条件部分,就是查询时间段在本周范围内的: m[ *)sm
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6)
jL8[;*^G
而在存储过程中 )W9W8>Cc5_
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) @Ee{ GH^-
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) H59}d
oKH