SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 .LZ?S"z$w
//MUeTxR
**0~K" ;\
一、基础 sdrfsrNvB-
1、说明:创建数据库 ]cvwIc">
CREATE DATABASE database-name 0auYG><=
2、说明:删除数据库 FUzzB94a
drop database dbname By,eETU]
3、说明:备份sql server b_krk\e@S
--- 创建 备份数据的 device aKDKmHd
USE master ;1=1:S8
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' Po0A#Z l
--- 开始 备份 uMv,zO5
BACKUP DATABASE pubs TO testBack bWS&Yk(
4、说明:创建新表 J{<X7uB
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) CxmKz78
根据已有的表创建新表: :Ov6_x]*
A:create table tab_new like tab_old (使用旧表创建新表) E=Bf1/c\
B:create table tab_new as select col1,col2... from tab_old definition only RC"MdcD:]y
5、说明:删除新表 B mb0cFQ
drop table tabname ttQGoUkj
6、说明:增加一个列 {fM'6;ak
Alter table tabname add column col type ~=LE0. 3[
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 hE/cd1iJ$
7、说明:添加主键: Alter table tabname add primary key(col) ) q4[zv9
说明:删除主键: Alter table tabname drop primary key(col) B-Hrex]
8、说明:创建索引:create [unique] index idxname on tabname(col....) #%2rP'He
删除索引:drop index idxname UDFDJm$
注:索引是不可更改的,想更改必须删除重新建。 ;;t yoh~t
9、说明:创建视图:create view viewname as select statement (,2SXV
删除视图:drop view viewname h"W,WxL8
10、说明:几个简单的基本的sql语句 A{zN| S[
选择:select * from table1 where 范围 (mB&m@-N
插入:insert into table1(field1,field2) values(value1,value2) |-ALklXr
删除:delete from table1 where 范围 Rv>-4@fMJ
更新:update table1 set field1=value1 where 范围 Q{>k1$fkV
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! Yh7t"=o
排序:select * from table1 order by field1,field2 [desc] KF}hV9IU
总数:select count as totalcount from table1 Dy&i&5E.-l
求和:select sum(field1) as sumvalue from table1 = svN#q5s
平均:select avg(field1) as avgvalue from table1 q<<v,ihh
最大:select max(field1) as maxvalue from table1 wJqMa9|
最小:select min(field1) as minvalue from table1 o/)h"i0P
JR|ck=tq
>y>5#[M!
r;2^#6/Z
11、说明:几个高级查询运算词 .Hm>i
>:!5*E5?
/N.b%M]!
A: UNION 运算符 pki%vRY
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 r5/0u(\LB
B: EXCEPT 运算符 ^\% (,KNo
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 8,%^
M9zBP
C: INTERSECT 运算符 gJ{)-\
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 @HC Vmg:
注:使用运算词的几个查询结果行必须是一致的。 ~~P5k:
12、说明:使用外连接 kTB0b*V
A、left outer join: Zx@a/jLO[n
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 5DZ#9m/
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 gD?l-RT>
B:right outer join: $PPi5f}HD
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 Zi
i
C:full outer join: sP~<*U.7
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 j$:~Rek
00y!K
m_D
uzPVTo|=
二、提升 q`-N7 ,$T
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 33q}CzK
法一:select * into b from a where 1<>1 ^
@5QP$.
法二:select top 0 * into b from a *&W"bOMH*
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) N+xP26D8
insert into b(a, b, c) select d,e,f from b; J@'wf8Ub
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) "S]TP$O D
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 SfyQ$$Z
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. CRE3icXbQ
4、说明:子查询(表名1:a 表名2:b) 'H!Uh]!
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) BU_nh+dF
5、说明:显示文章、提交人和最后回复时间 am'7uy!ka~
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b kzLsoZ!I
6、说明:外连接查询(表名1:a 表名2:b) X_h}J=33Q
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c cT,sh~-x,
7、说明:在线视图查询(表名1:a ) bE. .P&"
select * from (SELECT a,b,c FROM a) T where t.a > 1; 4$<JHo
@.
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 cq]6XK-W
select * from table1 where time between time1 and time2 ~
7s!VR
select a,b,c, from table1 where a not between 数值1 and 数值2 q9_OGd|P
9、说明:in 的使用方法 * u>\57W
select * from table1 where a [not] in ('值1','值2','值4','值6') o.!Dq7R
10、说明:两张关联表,删除主表中已经在副表中没有的信息 M }D}K\)
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 2ilQXy
11、说明:四表联查问题: vE?G7%,
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 ..... aFYIM`?(
12、说明:日程安排提前五分钟提醒 oc`H}Wvn
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 F41=b4/
13、说明:一条sql 语句搞定数据库分页 3 0H?KAV
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 ,"ZMRq
14、说明:前10条记录 ?a5! H*,
select top 10 * form table1 where 范围 cwg"c4V
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) =H8;iS2R
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 7:1Lol-V
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 c@7rqHU-0
(select a from tableA ) except (select a from tableB) except (select a from tableC) p5iuYHKk?
17、说明:随机取出10条数据 &QgR*,5eo
select top 10 * from tablename order by newid() Rm( "=(
18、说明:随机选择记录 }7Q% 6&IR
select newid() /8S>;5hvK@
19、说明:删除重复记录 T~e.PP
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) |{ip T SH
20、说明:列出数据库里所有的表名 C6PdDRf
select name from sysobjects where type='U' W6Fo6a"<
21、说明:列出表里的所有的 V,njO{Q
select name from syscolumns where id=object_id('TableName') 7.oM J
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 fHFE){
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 y6a3tG
显示结果: 0 H:X3y+
type vender pcs WsB ?C&>x
电脑 A 1 U xGApK=X
电脑 A 1 * EH~_F
光盘 B 2 1qA;/-Zr<o
光盘 A 2 M= (u]%\
手机 B 3 ]/v[8dS(l
手机 C 3 ygcm|PrS
23、说明:初始化表table1 MQ2}EY*A
TRUNCATE TABLE table1 upmx $H>
24、说明:选择从10到15的记录 QXK{bxwC
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc W=?<<dVYD
?J0y|
Bzf^ivT3L
I?CZQ+}Hq
三、技巧 i
ct])
1、1=1,1=2的使用,在SQL语句组合时用的较多 _[BP0\dPW
"where 1=1" 是表示选择全部 "where 1=2"全部不选, h*\%vr
如: Le^ n +5x
if @strWhere !='' ;xTpE2 -~
begin SXh-A1t
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere "tK=+f`NM
end PKz':_|
else p_4<6{KEt
begin m&3xJuKih
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ~}
~4
end Vurqt_nb
我们可以直接写成 %cn<ych
G
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere dZuOrTplA
2、收缩数据库 UEL_uij
--重建索引 #'`{Qv0,
DBCC REINDEX KI.hy2?e
DBCC INDEXDEFRAG vY3h3o
--收缩数据和日志 n@3>6_^rwT
DBCC SHRINKDB [-w%/D%@
DBCC SHRINKFILE y~V(aih}D
3、压缩数据库 *-X[u:
dbcc shrinkdatabase(dbname) %BODkc Zh
4、转移数据库给新用户以已存在用户权限 PA*5Bk="q
exec sp_change_users_login 'update_one','newname','oldname' "[N!m1i:{
go ;tf=gdX;
5、检查备份集 DY*N|OnqJ
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' EU#^7
6、修复数据库 |7~<Is~*
ALTER DATABASE [dvbbs] SET SINGLE_USER >$7B
wO
GO zH
r_!~
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK Z\sDUJ
GO ]4e;RV-B
ALTER DATABASE [dvbbs] SET MULTI_USER zt%Mx>V@
GO z$sGv19pB
7、日志清除 cMIEtK`
SET NOCOUNT ON ALHIGJW:6$
DECLARE @LogicalFileName sysname, 8P`"M#fI
@MaxMinutes INT, eMzk3eOJ
@NewSize INT 5)40/cBe
USE tablename -- 要操作的数据库名 46;uW{EY
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 5h*p\cl!Y
@MaxMinutes = 10, -- Limit on time allowed to wrap log. {;oPLr+Z
@NewSize = 1 -- 你想设定的日志文件的大小(M) J}t%p(mb
-- Setup / initialize -?a 26o%e
DECLARE @OriginalSize int ]M3yLYK/P
SELECT @OriginalSize = size k?}Zg*
FROM sysfiles U0+-W07>
WHERE name = @LogicalFileName MQ2_`pi
SELECT 'Original Size of ' + db_name() + ' LOG is ' + mE[y SrV
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + V]^$S"Tv
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' X8\GzNE~R
FROM sysfiles An@t?#4gxi
WHERE name = @LogicalFileName ssL\g`xe
CREATE TABLE DummyTrans xSu >
(DummyColumn char (8000) not null) ,r}6iFu
DECLARE @Counter INT, ,,r>,Xq6
@StartTime DATETIME, Bw.i}3UT6
@TruncLog VARCHAR(255) Ys7]B9/1O
SELECT @StartTime = GETDATE(), FI.\%x
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' X>^fEQq"
DBCC SHRINKFILE (@LogicalFileName, @NewSize) "N#Y gSr
EXEC (@TruncLog) O.M1@w]
-- Wrap the log if necessary. 6u%&<")4HP
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 4M T 7 `sr
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) |j|rS5
AND (@OriginalSize * 8 /1024) > @NewSize Gw` L"
BEGIN -- Outer loop. '%;m?t%q
SELECT @Counter = 0 &,/S`ke=
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) - YBY[%jF>
BEGIN -- update E-FUlOG&
INSERT DummyTrans VALUES ('Fill Log') A@'OJRc
DELETE DummyTrans $~kA
B8z
SELECT @Counter = @Counter + 1 W*G<X.Hf
END ?]5qr?W%
EXEC (@TruncLog) OrW
END u?EN
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
:11
A
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + r_d!ikOT(
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' SX#&5Ka/
FROM sysfiles ^rz_f{c]-
WHERE name = @LogicalFileName L},_.$I?
DROP TABLE DummyTrans "
1tH
SET NOCOUNT OFF >mkFV@`
8、说明:更改某个表 jWgX_//!
exec sp_changeobjectowner 'tablename','dbo' H/Jbk*Q
9、存储更改全部表 A}w/OA97RO
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch ?A0)L27UE&
@OldOwner as NVARCHAR(128), sos5Y}
@NewOwner as NVARCHAR(128) z9"U!A4
AS dWW.Y*339
DECLARE @Name as NVARCHAR(128) $Kd>:f=A
DECLARE @Owner as NVARCHAR(128) 7$#u
DECLARE @OwnerName as NVARCHAR(128) AFn7uW!9Gw
DECLARE curObject CURSOR FOR HKe K<V
select 'Name' = name, BLFdHB.$T
'Owner' = user_name(uid) =|9!vzG4
from sysobjects 3$/IC@+
where user_name(uid)=@OldOwner d_CT$
order by name MOC/KNb
OPEN curObject eH,or ,r
FETCH NEXT FROM curObject INTO @Name, @Owner A(X KyEx
WHILE(@@FETCH_STATUS=0) j1Ezf=N6`
BEGIN 4z)]@:`}z
if @Owner=@OldOwner {[F A#
begin )gi9f1n`
set @OwnerName = @OldOwner + '.' + rtrim(@Name) d5 -qZ{W
exec sp_changeobjectowner @OwnerName, @NewOwner r<\u6jF
end }2oc#0
-- select @name,@NewOwner,@OldOwner X{VOAcugr
FETCH NEXT FROM curObject INTO @Name, @Owner M\=2uKG#
END ,u m|1dh
close curObject kcEeFG;DQ
deallocate curObject
lRQYpc\
GO @nf`Gw ;
10、SQL SERVER中直接循环写入数据 |uDdHX8T
declare @i int `u\n0=go
set @i=1 W3RT{\
while @i<30 Kpp_|2|@<
begin h];I{crh
insert into test (userid) values(@i) 2SLU:=<3
set @i=@i+1 (sj,[
end [-&Zl(9&
小记存储过程中经常用到的本周,本月,本年函数 >dT*rH 3w
Dateadd(wk,datediff(wk,0,getdate()),-1) kVL.PY\K
Dateadd(wk,datediff(wk,0,getdate()),6) }WV:erg`
Dateadd(mm,datediff(mm,0,getdate()),0) pk~WrqK}
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) V.Mry`9-
Dateadd(yy,datediff(yy,0,getdate()),0) %)n=x
ne
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) Ho%CDz
z
上面的SQL代码只是一个时间段 +[P{&\d4}
Dateadd(wk,datediff(wk,0,getdate()),-1) "#48% -'x
Dateadd(wk,datediff(wk,0,getdate()),6) 11lsf/IP
就是表示本周时间段. D{!IW!w
下面的SQL的条件部分,就是查询时间段在本周范围内的: xC?h2hIt
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) <GsuZ
而在存储过程中 j.YA2mr
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) n`KY9[0U=
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) @pxcpXCy