SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 EN;4EC7tE
xY<*:&
\CVrLn;}
一、基础 c%5Suu(J6
1、说明:创建数据库 /[,0,B9!3
CREATE DATABASE database-name pv@w 8*
2、说明:删除数据库 k4`(7Z
drop database dbname @ *n oma
3、说明:备份sql server ,^@z;xF
--- 创建 备份数据的 device cxc-|Xori
USE master @ w?,7i-S
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' fO,m_
OR:)
--- 开始 备份 gaU1A"S}
BACKUP DATABASE pubs TO testBack }-T
:
4、说明:创建新表 CC|=$(PgT
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) IZOO>-g'f
根据已有的表创建新表: *:8,w?Nt
A:create table tab_new like tab_old (使用旧表创建新表) LXf*
B:create table tab_new as select col1,col2... from tab_old definition only ~w"e 2a
5、说明:删除新表 +r$ M 9
drop table tabname h_\OtoRa
6、说明:增加一个列 mV#U=zqb!S
Alter table tabname add column col type \VHRI<$+5
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 7[It
7、说明:添加主键: Alter table tabname add primary key(col) ((T6z$:hA
说明:删除主键: Alter table tabname drop primary key(col) bEli!N$
8、说明:创建索引:create [unique] index idxname on tabname(col....) #@}wl
删除索引:drop index idxname \vF*n Z5/
注:索引是不可更改的,想更改必须删除重新建。 aqKrf(Rv
9、说明:创建视图:create view viewname as select statement rHJtNN8$k
删除视图:drop view viewname (Z?g^kjq)
10、说明:几个简单的基本的sql语句 Dgm"1+
选择:select * from table1 where 范围 (gjCm0#_%
插入:insert into table1(field1,field2) values(value1,value2) h1Logm+m
删除:delete from table1 where 范围 O>[B"mMt
更新:update table1 set field1=value1 where 范围 Z!*k 0<Z
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! rH9[x8e
排序:select * from table1 order by field1,field2 [desc] Z=zD~ka
总数:select count as totalcount from table1 ~$]Puv1V>
求和:select sum(field1) as sumvalue from table1 e7M6|6nb
平均:select avg(field1) as avgvalue from table1 F`M`c%
最大:select max(field1) as maxvalue from table1 =PIarUJ
最小:select min(field1) as minvalue from table1 }$@ EpM
A}G>JL
npMPjknl
U[M~O*9
11、说明:几个高级查询运算词 A O3MlK9t
36\_Y?zx%
} T&~DVM
A: UNION 运算符 MTAq}8
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 DTz)qHd#X
B: EXCEPT 运算符 i^}ib
RQbN
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 "Zu>cbE
C: INTERSECT 运算符 Ug8>|wCE
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 <Y+>a#T
注:使用运算词的几个查询结果行必须是一致的。 ~qkn1N%'
12、说明:使用外连接 DvY)n<U1qA
A、left outer join: hGbSN_F
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 yQE9S+%M
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 u+qj_Ej
B:right outer join: %6E:SI4
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 gp NAM"
C:full outer join: 5v"S v
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Esdw^MGL2
%nhE588xf
<F?UdMT4y
二、提升 Jp-6]uW
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Vw+RRi(
法一:select * into b from a where 1<>1 Ti&v9re%wO
法二:select top 0 * into b from a ?6 //'bO:%
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) `c? 8i
insert into b(a, b, c) select d,e,f from b; L16">,5
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) vQmqYyOc2
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 $Go)Zs-bL?
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. {!xDJnF;
4、说明:子查询(表名1:a 表名2:b) `gz/?q
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) ~'0W(~Q8
5、说明:显示文章、提交人和最后回复时间 TnJJ& "~3b
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b sZI$t L<j
6、说明:外连接查询(表名1:a 表名2:b) $PFE>=nM
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c S3ZIC\2
7、说明:在线视图查询(表名1:a ) ASUleOI79(
select * from (SELECT a,b,c FROM a) T where t.a > 1; wVkRrFJ
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 Le*gdoW .
select * from table1 where time between time1 and time2 LTcZdQd$
select a,b,c, from table1 where a not between 数值1 and 数值2 FY"csZ
9、说明:in 的使用方法 TV~S#yg+H
select * from table1 where a [not] in ('值1','值2','值4','值6') 91M5F$
10、说明:两张关联表,删除主表中已经在副表中没有的信息 4nKlW_{,
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) }Apn.DYbbf
11、说明:四表联查问题: F.-:4m(Z
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 ..... ^1;Eq>u
12、说明:日程安排提前五分钟提醒 A$-\Er+f
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 e`zCz`R
13、说明:一条sql 语句搞定数据库分页 l!j,9wz7
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 -`,Fe3
14、说明:前10条记录 ahg]OWn#
select top 10 * form table1 where 范围 kHd`k.nW
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 2X6L'!=
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 4DsHUc6
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 LN`Y`G|op
(select a from tableA ) except (select a from tableB) except (select a from tableC) #7=- zda5
17、说明:随机取出10条数据 ?[2>x{5Z
select top 10 * from tablename order by newid() 9}z%+t8u
18、说明:随机选择记录 B:#9
select newid() IC+!XZqS
19、说明:删除重复记录 3ICM H
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ^CW{`eBwk
20、说明:列出数据库里所有的表名 S#nW )=
select name from sysobjects where type='U' HM &"2c
21、说明:列出表里的所有的 3|=L1Pw#
select name from syscolumns where id=object_id('TableName') c+501's
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 i!yE#zew
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 T+41,
显示结果: @|<qTci
type vender pcs _&aPF/
电脑 A 1 h6 Cqc}P
电脑 A 1 .zsYVtK
光盘 B 2 sPvjJ r"s
光盘 A 2 "~L$oji
手机 B 3 i9D<jkc
手机 C 3 6mV^akapv
23、说明:初始化表table1 U&0 RQ:B
TRUNCATE TABLE table1 *vOk21z77d
24、说明:选择从10到15的记录 Fhga^.5U&
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc czT]XF
m(IyW734I
f0
kz:sZ9
$ EexNz
三、技巧 GezMqt;2
1、1=1,1=2的使用,在SQL语句组合时用的较多 ^/~C\
(
"where 1=1" 是表示选择全部 "where 1=2"全部不选, ;),vUu,k
如: VM<$!Aaz
if @strWhere !='' 8/R9YiY5*
begin `o?PLE;)p
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere s&1}^'|
end v\D.j4%ij
else {\gpXVrn_
begin BH0s` K"
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 5
Q/yPQN
end %Ot*k%F
我们可以直接写成 }J $\<ZT
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere BT"n;L?[
2、收缩数据库 wY3|5kbDj
--重建索引 eu'S~c-l
DBCC REINDEX J@+b_e*
DBCC INDEXDEFRAG 'oF
XNO
--收缩数据和日志 Qcn;:6_&W
DBCC SHRINKDB \|CuTb;0
DBCC SHRINKFILE NRazI_Z
3、压缩数据库 TT={>R[B
dbcc shrinkdatabase(dbname) %:Z_~7ZR
4、转移数据库给新用户以已存在用户权限 1}~`g ED
exec sp_change_users_login 'update_one','newname','oldname' kAq#cLprG
go r?/!VO-*N
5、检查备份集 6eb~Z6n&?
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' f dJ<(i]7W
6、修复数据库 5]K2to)>`
ALTER DATABASE [dvbbs] SET SINGLE_USER !\!j?z=O8
GO hGRHuJ
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK q4Mv2SPT
GO m .R**g
ALTER DATABASE [dvbbs] SET MULTI_USER f$qkb$?]}
GO }6gum
7、日志清除 I.it4~]H
SET NOCOUNT ON D9!$H!T _
DECLARE @LogicalFileName sysname, ?hYWxWW
@MaxMinutes INT, J3$@: S'
@NewSize INT tGF3Hw^mS
USE tablename -- 要操作的数据库名 tac\Ki?
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 6G{ Q@
@MaxMinutes = 10, -- Limit on time allowed to wrap log. $e:bDZ(hjj
@NewSize = 1 -- 你想设定的日志文件的大小(M) #I\" 'n5M
-- Setup / initialize V3ExS1fNf
DECLARE @OriginalSize int <==6fc>s
SELECT @OriginalSize = size gBOF#"-
FROM sysfiles Hyi'z 1
WHERE name = @LogicalFileName odn3*{c{x
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 'V\V=yc1
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + %e:[[yq)G
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' 0~ o,^AW
FROM sysfiles e m
WHERE name = @LogicalFileName bnJ4Edy
CREATE TABLE DummyTrans 7&u$^c S(
(DummyColumn char (8000) not null) WEtPIHruyt
DECLARE @Counter INT, Nh\o39=
@StartTime DATETIME, f{2I2kJr
@TruncLog VARCHAR(255) J?Oeuk~[D
SELECT @StartTime = GETDATE(), qG +PqK;
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' J~C=o(r
DBCC SHRINKFILE (@LogicalFileName, @NewSize) ij1YV2v
EXEC (@TruncLog) V[n,fEPBr
-- Wrap the log if necessary. rU6A^p\,
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired ;=WwJ Np~
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) '4CD
}
AND (@OriginalSize * 8 /1024) > @NewSize KDb`g}1Q
BEGIN -- Outer loop. 0{
SELECT @Counter = 0 3-'3w ,
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) Jhfw$ DF
BEGIN -- update E6z&pM8<8
INSERT DummyTrans VALUES ('Fill Log') .y lvJ$
DELETE DummyTrans [s{[
.0P]+
SELECT @Counter = @Counter + 1 'V&Tlw|
END /fdrf
EXEC (@TruncLog) zO@>)@~
END Jt0U`_
SELECT 'Final Size of ' + db_name() + ' LOG is ' + o#=C[d5BV
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + XlnSh<e
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' ]B$J8.{q0
FROM sysfiles a ,"
WHERE name = @LogicalFileName G #M0
C>n
DROP TABLE DummyTrans }F"98s W
SET NOCOUNT OFF P](8Qrl
8、说明:更改某个表 _3.rPS,s
exec sp_changeobjectowner 'tablename','dbo' nLCaik_,m
9、存储更改全部表 )j\_*SoH
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch q@tym5
@OldOwner as NVARCHAR(128), _07$TC1
@NewOwner as NVARCHAR(128) LR';cR;
AS #jd.i
DECLARE @Name as NVARCHAR(128) `?b'.Z_J
DECLARE @Owner as NVARCHAR(128) wJ7^)tTRF
DECLARE @OwnerName as NVARCHAR(128) ~@(C+ 3,
DECLARE curObject CURSOR FOR @C^wV
select 'Name' = name,
J5';Hb)
'Owner' = user_name(uid) \+=`o .2
from sysobjects mxpj<^n}
where user_name(uid)=@OldOwner q;UGiB^(A
order by name yDWBrN._
OPEN curObject #sxv?r
FETCH NEXT FROM curObject INTO @Name, @Owner )@P*F)g~
WHILE(@@FETCH_STATUS=0) p_X{'=SQ1
BEGIN k1ja ([Q
if @Owner=@OldOwner FBbaLqgVF{
begin ~Z!YB,)bp
set @OwnerName = @OldOwner + '.' + rtrim(@Name) n$v4$_qS
exec sp_changeobjectowner @OwnerName, @NewOwner WA0D#yuJ/
end pWq+`|l$
-- select @name,@NewOwner,@OldOwner o\]U;#YD
FETCH NEXT FROM curObject INTO @Name, @Owner ]^T-X/v9
END `oH4"9&]k3
close curObject SN]g4}K-
deallocate curObject s;Gg
GO )(_NFpM
10、SQL SERVER中直接循环写入数据 -e_op'`
declare @i int Js vdC]+
set @i=1 `(
w"{8laB
while @i<30 _ Yc"{d3S
begin 3zu6#3^
insert into test (userid) values(@i) *ra>Kl0
set @i=@i+1 vbd)L$$20+
end /'5d0' ,M
小记存储过程中经常用到的本周,本月,本年函数 kD?@nx>
Dateadd(wk,datediff(wk,0,getdate()),-1) P|Gwt&
Dateadd(wk,datediff(wk,0,getdate()),6) &GkD5b
Dateadd(mm,datediff(mm,0,getdate()),0) 4 Yv:\c
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) l1KgPRmEP
Dateadd(yy,datediff(yy,0,getdate()),0) qDYNY`
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) ZO/u3&gU
上面的SQL代码只是一个时间段 e([>sAx!1
Dateadd(wk,datediff(wk,0,getdate()),-1) B\e*-:pq>
Dateadd(wk,datediff(wk,0,getdate()),6) l#%7BGwzY
就是表示本周时间段. }WaZ+Mdg\
下面的SQL的条件部分,就是查询时间段在本周范围内的: ^i_+ugJX
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) W`NF4 0)
而在存储过程中 <oV[[wl
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) i q oXku
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) bX,#z,