SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 %f{kT<XHu
BT#'<!7!
xTAC&OCk^[
一、基础 y'4=
1、说明:创建数据库 JN3Oe5yB2@
CREATE DATABASE database-name o"UqI
2、说明:删除数据库 PkG+`N
drop database dbname S4?ssI
3、说明:备份sql server
ND21;
--- 创建 备份数据的 device '{OZ[$E
USE master 25YJH1x
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' vV=$N"bT~
--- 开始 备份 SrHRpxy
BACKUP DATABASE pubs TO testBack 7Bmt^J5i&t
4、说明:创建新表 C'5i>;
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) :Z=A,G
根据已有的表创建新表: MWhFNfS8=
A:create table tab_new like tab_old (使用旧表创建新表) IL>Gi`Y&
B:create table tab_new as select col1,col2... from tab_old definition only {SROg;vA
5、说明:删除新表 ~@sx}u
drop table tabname +Do7rl
6、说明:增加一个列
ze#LX4b I
Alter table tabname add column col type z
^a,7}4
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 Y%wF;I1x
7、说明:添加主键: Alter table tabname add primary key(col) >nl*aN
说明:删除主键: Alter table tabname drop primary key(col) =cRJtn
8、说明:创建索引:create [unique] index idxname on tabname(col....)
tb@/E
删除索引:drop index idxname \>I&UFfH)4
注:索引是不可更改的,想更改必须删除重新建。 )cOm\^,
9、说明:创建视图:create view viewname as select statement "&C'K
删除视图:drop view viewname 4H1s"mP<
10、说明:几个简单的基本的sql语句 b(~NqV!i
选择:select * from table1 where 范围 DUW;G9LP$-
插入:insert into table1(field1,field2) values(value1,value2) u4.-AY {
删除:delete from table1 where 范围 %C)U
F
更新:update table1 set field1=value1 where 范围 bLNQ%=FjO
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! o'D6lkf0
排序:select * from table1 order by field1,field2 [desc] 0V`/oaW;
总数:select count as totalcount from table1 TH6g:YP`7
求和:select sum(field1) as sumvalue from table1 6dg[
平均:select avg(field1) as avgvalue from table1 NrL%]dl3/
最大:select max(field1) as maxvalue from table1 a(BC(^1!
最小:select min(field1) as minvalue from table1 U'lrdc"Q
wetkmd
0Y"==g+>f
pK$^@~DE
11、说明:几个高级查询运算词 teM&[U
cQ+V4cW
Z
WJJ!NoP
A: UNION 运算符 !_V*VD
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 ICV67(Ui
B: EXCEPT 运算符 ZC0F:=/K
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 x$M[/ID0
C: INTERSECT 运算符 [0IeEjL
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 =ohdL_6
注:使用运算词的几个查询结果行必须是一致的。 Ye(0'*-jyc
12、说明:使用外连接 %A64 Y<K
A、left outer join: e#W@ep|n
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 `CHgTkv
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 }b,a*4pN
B:right outer join: >xH3*0Lp
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 !^\|r<2M
C:full outer join: 0>.'w\,87B
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 L@[bgN`=v
+%>L;'L
^X
][_:{ N/
二、提升 9$d (`-&9p
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) w1s#8:
法一:select * into b from a where 1<>1 ?|8H$1
法二:select top 0 * into b from a :Eob"WH
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) ew"[]eZ:ut
insert into b(a, b, c) select d,e,f from b; N*o+m~:y
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) &O!d!Pf
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 c"0CHrd
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. sY1*WolA
4、说明:子查询(表名1:a 表名2:b) TYLf..i<
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) orL7y&w(v:
5、说明:显示文章、提交人和最后回复时间 wBmbn=>#S
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ExnszFX*
6、说明:外连接查询(表名1:a 表名2:b) 1lx\Pz@ol
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Wje7fv
7、说明:在线视图查询(表名1:a ) l sUQ7%f
select * from (SELECT a,b,c FROM a) T where t.a > 1; 1 bv L
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 9`vse>,-hg
select * from table1 where time between time1 and time2 Cf%)W:Q9
select a,b,c, from table1 where a not between 数值1 and 数值2 L(X:=)
!K0
9、说明:in 的使用方法 s!UC{)g,
select * from table1 where a [not] in ('值1','值2','值4','值6') X|.X4fs
10、说明:两张关联表,删除主表中已经在副表中没有的信息 /+66y=`UJ
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) /=-E`%R}!
11、说明:四表联查问题: 2U#OBvNU
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 ..... @c.QrKSaD
12、说明:日程安排提前五分钟提醒 ,sJ{2,]~
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 5F0sfX
13、说明:一条sql 语句搞定数据库分页
(+Er
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 @o>2:D1G
14、说明:前10条记录 $Y ]*v)}X
select top 10 * form table1 where 范围 _39b8s{
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 1M<'^(t3d
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) @Yt[%tOF+
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 Lp{l&-uQ
(select a from tableA ) except (select a from tableB) except (select a from tableC) ,',fO?Qv'
17、说明:随机取出10条数据 q 2=^l
select top 10 * from tablename order by newid() oR3$A :!P=
18、说明:随机选择记录 `#9ZP
select newid() Lqz}h-Ei
19、说明:删除重复记录 >Axe7<l
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) i>0bI^H
20、说明:列出数据库里所有的表名 XSZW9/I-(|
select name from sysobjects where type='U' 242lR0#aY
21、说明:列出表里的所有的 Y.&z $+
select name from syscolumns where id=object_id('TableName') J)o~FC]b*
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 uRUysLIw
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 Q OdvzVy<
显示结果: $R"~BZbt;
type vender pcs rKR<R(=!=
电脑 A 1 2M|jWy _
电脑 A 1 r)*KgGsk
光盘 B 2 >\VZ9bP<
光盘 A 2 ,"*[T\u
手机 B 3 qt3\*U7x
手机 C 3 3
vE;s"/
23、说明:初始化表table1 m~X:KwK4
TRUNCATE TABLE table1 WXGLo;+>I
24、说明:选择从10到15的记录 TrHBbyqk
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc PRf2@0ZV
hp[8.Z$7
Aja'`Mu
k.0$~juu
三、技巧 +fKLCzj
1、1=1,1=2的使用,在SQL语句组合时用的较多 o>j3<#?
"where 1=1" 是表示选择全部 "where 1=2"全部不选, I,q3J1K
如: Z/a]oR@
if @strWhere !='' *jDzh;H!w
begin >5XE*9
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere <;_X=s`f,
end 9/Q5(P
else `bivAL
begin K4oLb"gB1
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' iiO4.@nT
end ;l~gA |A
我们可以直接写成 w'cZ\<N[
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere QDSB
<0j
2、收缩数据库 2uqdx'^"
--重建索引 H%sbf&
gi
DBCC REINDEX i)
:Q{[D
DBCC INDEXDEFRAG +}*]9nG
--收缩数据和日志 6``!DMDt/P
DBCC SHRINKDB Rrk3EL
DBCC SHRINKFILE xNl_Q8Z?R^
3、压缩数据库 D(L%fK` +
dbcc shrinkdatabase(dbname) %hOe `2#$
4、转移数据库给新用户以已存在用户权限 6kYn5:BhIi
exec sp_change_users_login 'update_one','newname','oldname' (}c}=V
go `ZNzDr
5、检查备份集 M-0BQs`N
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' )<jj O
6、修复数据库 Ue~M.LZb
ALTER DATABASE [dvbbs] SET SINGLE_USER |?{Zx&yUw
GO ?2DYz"/')
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK }0qgvw
GO #O `nQ
ALTER DATABASE [dvbbs] SET MULTI_USER b+3{ bE
GO T2^@x9
7、日志清除 "TG}aS
SET NOCOUNT ON ar>S_VW*
DECLARE @LogicalFileName sysname, g6r3V.X'
@MaxMinutes INT, 8'/vW ~f
@NewSize INT K]Ed-Tz8QZ
USE tablename -- 要操作的数据库名 YHg4WW$
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 $40tAes9
@MaxMinutes = 10, -- Limit on time allowed to wrap log. kg9ZSkJr
@NewSize = 1 -- 你想设定的日志文件的大小(M) |P~TZ
-- Setup / initialize Z>M0[DJ_
DECLARE @OriginalSize int |<9R%
SELECT @OriginalSize = size F8/4PB8-
FROM sysfiles Q>= :$I
WHERE name = @LogicalFileName 8"RX~Igf
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 265df
Y9Pu
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + (w)Qt/P^4
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' L?<V KT
FROM sysfiles ;R|5sCb/m
WHERE name = @LogicalFileName o3j4XrK
CREATE TABLE DummyTrans * UBU?
(DummyColumn char (8000) not null) *7DQ#bD
DECLARE @Counter INT, 0FHN
@StartTime DATETIME, .gx*gX1<
@TruncLog VARCHAR(255) 1;g>?18@
SELECT @StartTime = GETDATE(), BWz*!(
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' -bcm"(<T'
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Bo)w#X
EXEC (@TruncLog) O`Nzn~),x
-- Wrap the log if necessary. } n_9d.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired \JN?3}_J
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) zTm&m#){3A
AND (@OriginalSize * 8 /1024) > @NewSize 'tp+g3V
BEGIN -- Outer loop. s#-`,jqD
SELECT @Counter = 0 57D /"
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) %A:<rO85o
BEGIN -- update M~p=OM<
INSERT DummyTrans VALUES ('Fill Log') +-K-CXt
DELETE DummyTrans YG!~v~sV
SELECT @Counter = @Counter + 1 4GeWo@8h
END ;1K.SDj
EXEC (@TruncLog) )0~zL} )?
END U
$e-e/
SELECT 'Final Size of ' + db_name() + ' LOG is ' + !&?(ty^F
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + @My-O@C>
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 3zv_q&+8b
FROM sysfiles -h8A<
WHERE name = @LogicalFileName @6(4}&sEdm
DROP TABLE DummyTrans >o%.`)Ar
SET NOCOUNT OFF *8t_$<'dQ
8、说明:更改某个表 .$L'Jt2X
exec sp_changeobjectowner 'tablename','dbo' 4*G#fW-
9、存储更改全部表 sHmzwvpLA
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch ##EMJi
@OldOwner as NVARCHAR(128), cZPv6c_w
@NewOwner as NVARCHAR(128) ^4h/6^b0c
AS #1&wfI$
DECLARE @Name as NVARCHAR(128) Ae;>
@k/|=
DECLARE @Owner as NVARCHAR(128) `o)rAD^e
DECLARE @OwnerName as NVARCHAR(128) }&bO;o&>
DECLARE curObject CURSOR FOR _cQTQ
select 'Name' = name, \l"1Io=
'Owner' = user_name(uid) /*B-y$WQk
from sysobjects hL+)XJu^J
where user_name(uid)=@OldOwner _>S."cm}!k
order by name -1DQO|q#
OPEN curObject +pkX$yz
FETCH NEXT FROM curObject INTO @Name, @Owner +.!
F]0ju
WHILE(@@FETCH_STATUS=0) 6f v{?0|
BEGIN Q~MV0<{
if @Owner=@OldOwner 1 }%vZE2
begin Lf4c[[@%gd
set @OwnerName = @OldOwner + '.' + rtrim(@Name) dmgoVF_qR
exec sp_changeobjectowner @OwnerName, @NewOwner {r yv7G
end F\^9=}b_i
-- select @name,@NewOwner,@OldOwner A9`& Wnw?
FETCH NEXT FROM curObject INTO @Name, @Owner 5
<X.1T1
END wrSw> sE"
close curObject Y`eU WCD
deallocate curObject *9Ej fs7L
GO *UJ.cQ}
10、SQL SERVER中直接循环写入数据 |08b=aR6ro
declare @i int +*Y/+.4WE$
set @i=1 e`27 ?
while @i<30 x|g>Zd/n
begin OuWG.Za
insert into test (userid) values(@i) X0wvOs:
set @i=@i+1 }TI"j{(QJ
end :08b&myx
小记存储过程中经常用到的本周,本月,本年函数 J$Uj@M
Dateadd(wk,datediff(wk,0,getdate()),-1) #e|G!'wdj
Dateadd(wk,datediff(wk,0,getdate()),6) qS1byqq78l
Dateadd(mm,datediff(mm,0,getdate()),0) 0eP7efy
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) jrJ!A(<)
Dateadd(yy,datediff(yy,0,getdate()),0) 6b`3AAGU"
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) sl?> X)}
上面的SQL代码只是一个时间段 $2I^ ;5r[
Dateadd(wk,datediff(wk,0,getdate()),-1) xvn@zi
Dateadd(wk,datediff(wk,0,getdate()),6) !6.}{6b
就是表示本周时间段. S<fSoU+RJ
下面的SQL的条件部分,就是查询时间段在本周范围内的: 1X.E:
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) h]WPWa)M
而在存储过程中 %{"v^4
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) '&v.h#<
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) XLQt>y)