SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Q=d.y&4%
a$ C2}
:d;[DYFLxb
一、基础 jMN)?6$=
1、说明:创建数据库 siZr@g !L
CREATE DATABASE database-name CTl(_g
2、说明:删除数据库 1pd 9s8CA
drop database dbname 1i4WWK7k
3、说明:备份sql server \:]DFZ= !
--- 创建 备份数据的 device 1S+;ZMk
USE master nAIH`L"X
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' cwk+#ur
--- 开始 备份 nY OY"'z
BACKUP DATABASE pubs TO testBack DW1@<X
4、说明:创建新表 F(d:t!
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) M0RRmW@f.a
根据已有的表创建新表: ,%A)"doaG
A:create table tab_new like tab_old (使用旧表创建新表) znt)]>f#
B:create table tab_new as select col1,col2... from tab_old definition only r)gCTV(kb
5、说明:删除新表 y&1%1 #8F
drop table tabname 0z<H(|
6、说明:增加一个列 qv
*3A?uzr
Alter table tabname add column col type ]53O}sH>
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 y|^EGnaE
7、说明:添加主键: Alter table tabname add primary key(col) F;#zN
说明:删除主键: Alter table tabname drop primary key(col) 9*4 .
8、说明:创建索引:create [unique] index idxname on tabname(col....) !6M Bxg >
删除索引:drop index idxname G@9u:\[l
注:索引是不可更改的,想更改必须删除重新建。 Yg/}ghF\
9、说明:创建视图:create view viewname as select statement nt=x]wEC
删除视图:drop view viewname %Ys$@dB
10、说明:几个简单的基本的sql语句 jk )Vb
选择:select * from table1 where 范围 xPt*CB
插入:insert into table1(field1,field2) values(value1,value2) _l<mu? "
删除:delete from table1 where 范围 &M[MEO`t8
更新:update table1 set field1=value1 where 范围 cQX:%Ix=
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! Zb."*zL
排序:select * from table1 order by field1,field2 [desc] fe|g3>/|
总数:select count as totalcount from table1 ^00{Hd6
求和:select sum(field1) as sumvalue from table1 s
D_G)c
平均:select avg(field1) as avgvalue from table1 #:#Dz.$L
最大:select max(field1) as maxvalue from table1 L x.jrF|&
最小:select min(field1) as minvalue from table1 J@QdieW6
f3G1r5x
oCVku:.
c_#*mA"+
11、说明:几个高级查询运算词 W .U+.hR
/Pk:4,
LKx` v90p
A: UNION 运算符 DVh)w}v
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 oSY7IIf%L
B: EXCEPT 运算符 }#6xFTH
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 C',6%6P
C: INTERSECT 运算符 )Q]w6he3
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 +Rqbf
注:使用运算词的几个查询结果行必须是一致的。 BxdX WO
12、说明:使用外连接 UW6VHA>
A、left outer join: #dxvz^2V.3
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 6aY>lkp
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 W pN.]x
B:right outer join: 90fs:.
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 ;1`!wG-DD
C:full outer join: <
bFy(+
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Uyk,.*8"
1`JB)9P
OPuj|%Wgw
二、提升 ]g,lRG
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Z5B/|{
法一:select * into b from a where 1<>1 <bUXC@3W
法二:select top 0 * into b from a l^W uS|G[
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) ]
8Tzr
insert into b(a, b, c) select d,e,f from b; XA{F:%
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) AbfZ++aJ
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 BvK QlT
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. &94W-zh
4、说明:子查询(表名1:a 表名2:b) I]R9HGJNlJ
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) VEs5;]#<2D
5、说明:显示文章、提交人和最后回复时间 c)~h<=)
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b fF;h V
6、说明:外连接查询(表名1:a 表名2:b) `/4:I
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c P!e= b-T
7、说明:在线视图查询(表名1:a ) W5SN I>|E
select * from (SELECT a,b,c FROM a) T where t.a > 1; %e?fH.)
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 {@.Vh]
select * from table1 where time between time1 and time2 z9DcnAs
select a,b,c, from table1 where a not between 数值1 and 数值2 C:i|-te
9、说明:in 的使用方法 zzIr2so
select * from table1 where a [not] in ('值1','值2','值4','值6') "9XfQ"P
10、说明:两张关联表,删除主表中已经在副表中没有的信息 (=c1
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) =&vFVIhWcf
11、说明:四表联查问题: U| 5-0 u5
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 ..... b>11h
12、说明:日程安排提前五分钟提醒 6F|j(LB
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 `A)"%~
13、说明:一条sql 语句搞定数据库分页 ;Vu5p#,O<M
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 KbW9s,:p
14、说明:前10条记录 V diJ>d[
select top 10 * form table1 where 范围 RU#F8O
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) zpjqEEY;
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) Z`Z5sj 4{
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 3tAU?sV!
(select a from tableA ) except (select a from tableB) except (select a from tableC) j/!H$0PN
17、说明:随机取出10条数据 R 94^4I
select top 10 * from tablename order by newid() (u1m]WYL
18、说明:随机选择记录 &
E}mX]t
select newid() 6'-As=iw
19、说明:删除重复记录 3V<&|
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) "TOa=Tt{,
20、说明:列出数据库里所有的表名 1;F`c`0<
select name from sysobjects where type='U' # Su~`]
21、说明:列出表里的所有的 #0Ds'pE-
select name from syscolumns where id=object_id('TableName') ^Q}eatEn
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 PBjmGwg7
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 (eWPis[
显示结果: \cJa;WM>
type vender pcs 7^6uG6
电脑 A 1 >SbK.Q@ei
电脑 A 1 yd_
(?V&;_
光盘 B 2 9pKGr@ &
光盘 A 2 mN_Z7n;^eh
手机 B 3 E%N2k|%8d_
手机 C 3 x.mrCJn)
23、说明:初始化表table1 '@nbqM
TRUNCATE TABLE table1 {FO$yw=>
24、说明:选择从10到15的记录 zg)sd1@
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc jNDx,7F-
V@-)\RZm
f .
}c7
<If35Z)~
三、技巧 i5}4(sV
1、1=1,1=2的使用,在SQL语句组合时用的较多 0MF}^"R
"where 1=1" 是表示选择全部 "where 1=2"全部不选, LPd\-S_rsP
如: ,F6=b/eZ
if @strWhere !='' BqK(DH^9N
begin $FTO
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere ;E^K.6
end OX]V)QHVZ
else *XOJnyC_H
begin ),:c+~@@kT
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 7r#ymQ
end y[};J
vk
我们可以直接写成 `_ %S
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere nuQ]8- ,
2、收缩数据库 d~J-|yyT
--重建索引 igL<g
DBCC REINDEX `mfq
2bVc
DBCC INDEXDEFRAG 0fXLcal
--收缩数据和日志 g^Yl TB
DBCC SHRINKDB ZFm`UXS
DBCC SHRINKFILE PXoz*)tk
3、压缩数据库 G6P)C##ibn
dbcc shrinkdatabase(dbname) m0w;8uF2UV
4、转移数据库给新用户以已存在用户权限 eDI=nSo
exec sp_change_users_login 'update_one','newname','oldname' 7R.Q
Ql
go
uE/T2BX*
5、检查备份集 6DIZ@ oi
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' J@pb[O L,
6、修复数据库 9I7\D8r
ALTER DATABASE [dvbbs] SET SINGLE_USER ) /vhclkb
GO S{]7C?4`
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK *.-.iY.a]
GO \`<cH#
ALTER DATABASE [dvbbs] SET MULTI_USER s'aip5P
GO OI1ud/>h
7、日志清除 cLa]D[H
SET NOCOUNT ON Cb t{H}I3
DECLARE @LogicalFileName sysname, 8M*PML4r
@MaxMinutes INT, [S]S^ej*8
@NewSize INT /I5X"x
USE tablename -- 要操作的数据库名 v44}%$
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 ,C.:;Ime({
@MaxMinutes = 10, -- Limit on time allowed to wrap log. c\6+=\
@NewSize = 1 -- 你想设定的日志文件的大小(M) yq-=],h
-- Setup / initialize b !@Sn/
DECLARE @OriginalSize int >7`<!YJkK
SELECT @OriginalSize = size {gA\ph%s
FROM sysfiles enNiI$H]`_
WHERE name = @LogicalFileName g[
0<m#"
SELECT 'Original Size of ' + db_name() + ' LOG is ' + <$w?/y/'
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + >SN|?|2U/
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' f %lD08Sl
FROM sysfiles -!;l~#K=
WHERE name = @LogicalFileName (6CN/A{qe
CREATE TABLE DummyTrans |{*}|
(DummyColumn char (8000) not null) 4H5pr
DECLARE @Counter INT, (bOpV>\Q7
@StartTime DATETIME, etT +
@TruncLog VARCHAR(255) pMg3fUIM
SELECT @StartTime = GETDATE(), Om'+]BBN
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' "Td`AuP@,
DBCC SHRINKFILE (@LogicalFileName, @NewSize) k:2QuG^
EXEC (@TruncLog) 9odJr]
-- Wrap the log if necessary. kg][qn|>J]
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 6iEhsL&K
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) <Llp\XcZ
AND (@OriginalSize * 8 /1024) > @NewSize GLn{s
BEGIN -- Outer loop. IKMeJ(:S
SELECT @Counter = 0 Rj F'x
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) o-xDh7v
BEGIN -- update _LAS~x7,
INSERT DummyTrans VALUES ('Fill Log') x:vu'A
DELETE DummyTrans 72sD0)?A
SELECT @Counter = @Counter + 1 yKXff1^M
END }>^Q'BW;65
EXEC (@TruncLog) }R3=fbe,\
END $Y=T&O
SELECT 'Final Size of ' + db_name() + ' LOG is ' + lnovykR
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + i |{Dd%4vK
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' "G-1>:
FROM sysfiles VL[R(a6c
<
WHERE name = @LogicalFileName YY?a>j."a
DROP TABLE DummyTrans 2fU$J>Y
SET NOCOUNT OFF ^mAYBOE
8、说明:更改某个表 <APB11
exec sp_changeobjectowner 'tablename','dbo' #-3=o6DCK
9、存储更改全部表 hlu:=<B
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch |K?#$~
@OldOwner as NVARCHAR(128), {k4CEt;
@NewOwner as NVARCHAR(128) D+~_TA
AS S$f6a'
DECLARE @Name as NVARCHAR(128) V`adWXu
DECLARE @Owner as NVARCHAR(128) MYBx&]!\
DECLARE @OwnerName as NVARCHAR(128) Hk>79};
DECLARE curObject CURSOR FOR =0@&GOq
select 'Name' = name, 0Io'bF
'Owner' = user_name(uid) 7&KT0a*
from sysobjects v+jsC`m
where user_name(uid)=@OldOwner Rwr 2gMt7
order by name c}3W:}lW
OPEN curObject ZEYgK)^
FETCH NEXT FROM curObject INTO @Name, @Owner T&86A\D\z
WHILE(@@FETCH_STATUS=0) >5R<;#8
BEGIN [2ax>Yk$
if @Owner=@OldOwner 5Z4-Z
begin <foCb%$(?
set @OwnerName = @OldOwner + '.' + rtrim(@Name) JFgoN,xn
exec sp_changeobjectowner @OwnerName, @NewOwner &z"krM]G
end `mye}L2I
-- select @name,@NewOwner,@OldOwner 0l;<5
FETCH NEXT FROM curObject INTO @Name, @Owner 7PR#(ftz
END 7iJ&6=/
close curObject yr#5k`&\_
deallocate curObject "O8gJ0e
GO A:<;M@q!
10、SQL SERVER中直接循环写入数据 H:p Z-v*
declare @i int zI~owK)%Z
set @i=1 c!8=lrT.
while @i<30 z} '! eCl
begin w&4~Q4
insert into test (userid) values(@i) {cpEaOyOM
set @i=@i+1 X-Wz:NA
end nA?Hxos
小记存储过程中经常用到的本周,本月,本年函数 gX{loG
Dateadd(wk,datediff(wk,0,getdate()),-1) N_L,]QT?
Dateadd(wk,datediff(wk,0,getdate()),6) cI]WrI2CQa
Dateadd(mm,datediff(mm,0,getdate()),0) [![%9'+P
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) PpLU
Dateadd(yy,datediff(yy,0,getdate()),0) &R~n>>c
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) yfU<UQ!1
上面的SQL代码只是一个时间段 Z ,^9Z
Dateadd(wk,datediff(wk,0,getdate()),-1) 8G>>i)Sbg
Dateadd(wk,datediff(wk,0,getdate()),6) K
~ 44i
就是表示本周时间段. ]d[ge6
下面的SQL的条件部分,就是查询时间段在本周范围内的: )} DUMq7
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) D@EO=08<b
而在存储过程中 7VK}Dy/Vvn
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) "^I
mb,
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) @H !$[m3