SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 y-b%T|p9
L<cx:Vz
HVCe;eI
一、基础 eb\K "ec"
1、说明:创建数据库 }0*@fO
CREATE DATABASE database-name L[fiU0^o
2、说明:删除数据库 9<?M8_
drop database dbname oSKXt}sh
3、说明:备份sql server xj)F55e?
--- 创建 备份数据的 device F{e@W([
USE master (S5R!lpO
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' u@)U"FZ
--- 开始 备份 a5"D @E
BACKUP DATABASE pubs TO testBack C==hox7b
4、说明:创建新表 net@j#}j-
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) &m7]v,&
根据已有的表创建新表: @i_FTN
A:create table tab_new like tab_old (使用旧表创建新表) ?zMHP#i
B:create table tab_new as select col1,col2... from tab_old definition only <NY^M!
5、说明:删除新表 H2 {+)
drop table tabname u~:y\/Y6
6、说明:增加一个列 x_}:D *aI
Alter table tabname add column col type Mj3A5;#
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 h2A <" w
7、说明:添加主键: Alter table tabname add primary key(col) qA7>vi%
说明:删除主键: Alter table tabname drop primary key(col) k"%~"9
8、说明:创建索引:create [unique] index idxname on tabname(col....) K7B/s9/xs
删除索引:drop index idxname p4rL}Jm&
注:索引是不可更改的,想更改必须删除重新建。 ;`4&Rm9n?
9、说明:创建视图:create view viewname as select statement >2)OiQ`zg
删除视图:drop view viewname
DPxM'7
10、说明:几个简单的基本的sql语句 wmL'F:UP
选择:select * from table1 where 范围 UhWNl]Z
插入:insert into table1(field1,field2) values(value1,value2) )EuvRLo{S7
删除:delete from table1 where 范围 HWrO"b*tO
更新:update table1 set field1=value1 where 范围 {]!mrAjD
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! i#/Jr=
排序:select * from table1 order by field1,field2 [desc] {lDd.Fn
总数:select count as totalcount from table1 2]jn '4
求和:select sum(field1) as sumvalue from table1 Sv#XIMw{,
平均:select avg(field1) as avgvalue from table1 XEp{VC@=
最大:select max(field1) as maxvalue from table1 [!uG1 GJ>
最小:select min(field1) as minvalue from table1 U$.@]F4&
oulVg];
%XDc,AR[
HZB>{O
11、说明:几个高级查询运算词 P )"m0Lu<
2;`1h[,-^
b5I I/Y
A: UNION 运算符 )9G[dDeC
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 N)| yu1S
B: EXCEPT 运算符 6<SAa#@ey
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 %lhEM}Sm
C: INTERSECT 运算符 \ZFGw&yN
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 /{l$sBUL
注:使用运算词的几个查询结果行必须是一致的。 ,4e:I.b
12、说明:使用外连接 G6P?2@
A、left outer join: H5B:;g@
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 qJs<#MQ2
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 L| +~"'l
B:right outer join: 286;=rN]*
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 L#?Ek-
C:full outer join: h 8S. x)
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 4r#= *
hbDXo:
8I?Wt
W
二、提升 bdrg(d6
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) S~bOUdV
Z
法一:select * into b from a where 1<>1 .t-4o<7 3
法二:select top 0 * into b from a VBGuC c/
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 6Q@j
insert into b(a, b, c) select d,e,f from b; FaSf7D`C
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) $y &E(J
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 BwGfTua
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. (O?.)jEW(.
4、说明:子查询(表名1:a 表名2:b) d#Y^>"|$.
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) rSk>
5、说明:显示文章、提交人和最后回复时间 T^t#
c
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b drP=A~?&:
6、说明:外连接查询(表名1:a 表名2:b) %QGC8Tz
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c m+R[#GE8#
7、说明:在线视图查询(表名1:a ) .Wj;%|
select * from (SELECT a,b,c FROM a) T where t.a > 1; B$ PP&/
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 J.b9F:&}
select * from table1 where time between time1 and time2 t;Sb/ 3
select a,b,c, from table1 where a not between 数值1 and 数值2 NjScc%@y
9、说明:in 的使用方法 e7Z32P0ls
select * from table1 where a [not] in ('值1','值2','值4','值6') Q7\w+ANf0
10、说明:两张关联表,删除主表中已经在副表中没有的信息 [< ?s?Ci
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) ;>yxNGV`
11、说明:四表联查问题: &*,#5.
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 ..... }Yzco52
12、说明:日程安排提前五分钟提醒 i2Qz4 $z
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 YMcD|Kb p
13、说明:一条sql 语句搞定数据库分页 u#$]?($}d
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 Y|f[bw
14、说明:前10条记录 mt{nm[D!Xp
select top 10 * form table1 where 范围 0/MtYIYk
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) pfD c9PMj
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) -t'jNR'
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 Y'S%O/$
(select a from tableA ) except (select a from tableB) except (select a from tableC) -q1??u
17、说明:随机取出10条数据 @Z
%ivR:
select top 10 * from tablename order by newid() Y0@"fU35
18、说明:随机选择记录 GqvpA#
i
select newid() '&tG?gb&
19、说明:删除重复记录 zuad~%D<I
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) T{.pM4Hd
20、说明:列出数据库里所有的表名 XbKYiy
select name from sysobjects where type='U' r&JgLC(
21、说明:列出表里的所有的 4y?n
[/M/
select name from syscolumns where id=object_id('TableName') u(>^3PJ+
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 p!7FpxZY
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 XB^'K2
显示结果: Vpz\.]
type vender pcs <I\/n<*
电脑 A 1 Uw. `7b>B
电脑 A 1 8,4"uuI
光盘 B 2 QUc= &5 %
光盘 A 2 <4si/=
手机 B 3 rdP[<Y9
手机 C 3 4{U T!WIi
23、说明:初始化表table1 i Dp)FQ$
TRUNCATE TABLE table1 D9=KXo^
24、说明:选择从10到15的记录 + T1pJ 89P
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc H9`)BbR
%KlrSo
x.!V^HQSN
ZF9z~9
三、技巧 v\gLWq'
1、1=1,1=2的使用,在SQL语句组合时用的较多 5oW!YJg
"where 1=1" 是表示选择全部 "where 1=2"全部不选, g0=z&2Q[_)
如: P|tO<t6/9*
if @strWhere !='' *xxx:*6rk;
begin KE5kOU;
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 1~Y<//5E
end {9&;Q|D z
else
!Y0Vid
begin DrUO-
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 30#s aGV
end /tx]5`#@7]
我们可以直接写成 ;~)5s'
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere y|i,|
2、收缩数据库 %+W{iu[|
--重建索引 |^"1{7)
DBCC REINDEX |P
HT694Uz
DBCC INDEXDEFRAG f;o5=)Y
--收缩数据和日志 eCU:Q
DBCC SHRINKDB "Y
=;.:qe
DBCC SHRINKFILE h6D<go-b56
3、压缩数据库 TCwFPlF|
dbcc shrinkdatabase(dbname) o4F2%0gJ
4、转移数据库给新用户以已存在用户权限 +s,=lL
exec sp_change_users_login 'update_one','newname','oldname' !&y8@MD15
go ~*&H$6NJS
5、检查备份集 Ju!]&G8
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' <e=#F-DE
6、修复数据库 *e TqVG.
ALTER DATABASE [dvbbs] SET SINGLE_USER jjRi*^d9
GO '6iEMg&3
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK P6'1.R
GO jjB~G^n
ALTER DATABASE [dvbbs] SET MULTI_USER h,u,^ r
GO O~#!l"0 L+
7、日志清除 `!;_ho
SET NOCOUNT ON gZ3u=uME
DECLARE @LogicalFileName sysname, Xv5wJlc!d
@MaxMinutes INT, b7?uq9
@NewSize INT r"3=44St
USE tablename -- 要操作的数据库名 Pe_W;q.
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 wtQ++l%{G
@MaxMinutes = 10, -- Limit on time allowed to wrap log. \R9(x]nZ%
@NewSize = 1 -- 你想设定的日志文件的大小(M) shy-Gu&
-- Setup / initialize v!-/&}W)1
DECLARE @OriginalSize int {yTGAf-DV
SELECT @OriginalSize = size [[Ls_ZL!=
FROM sysfiles F3[T.sf
WHERE name = @LogicalFileName ^+>laOzC`8
SELECT 'Original Size of ' + db_name() + ' LOG is ' + D(@S+r_ota
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + hc(#{]].
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' KEo,m
FROM sysfiles ios&n)W&
WHERE name = @LogicalFileName WtsFz*`)y
CREATE TABLE DummyTrans *MFIV02[N
(DummyColumn char (8000) not null) 7?!d^$B
DECLARE @Counter INT, ~]IOK$1F%
@StartTime DATETIME, Tj`,Z5vy
@TruncLog VARCHAR(255) 5K1)1E/Fu
SELECT @StartTime = GETDATE(), ~]|6T~+]83
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ntX3Nt_n
DBCC SHRINKFILE (@LogicalFileName, @NewSize) :\`o8`
EXEC (@TruncLog) }#RakV4
-- Wrap the log if necessary. zOAd~E
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired %8 B}Cb&2c
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) A7Cm5>Y_S
AND (@OriginalSize * 8 /1024) > @NewSize kYP#SH/
BEGIN -- Outer loop. Ytp(aE:
SELECT @Counter = 0 $t'MSlF
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) y4
#>X
BEGIN -- update vFzRg5lH
INSERT DummyTrans VALUES ('Fill Log') ^qvZXb
DELETE DummyTrans !I{0 _b{
SELECT @Counter = @Counter + 1 @|Cz-J;D
END Tt`u:ZwhF
EXEC (@TruncLog) 6m/r+?'
END ;LKkbT
5
SELECT 'Final Size of ' + db_name() + ' LOG is ' + L^/5ux
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + e9Wa<i8
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' hE'-is@7
FROM sysfiles eH'av}
WHERE name = @LogicalFileName Jc&{`s^Nu
DROP TABLE DummyTrans x$A+lj]x
SET NOCOUNT OFF n:I,PS0H<
8、说明:更改某个表 q5J5>
exec sp_changeobjectowner 'tablename','dbo' .O5Z8 p
9、存储更改全部表 RtkEGxw*^
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch ,\W 8b-Z
@OldOwner as NVARCHAR(128), P[G)sA_"
@NewOwner as NVARCHAR(128) U|H=Y"pL
AS g>E LGG|Q
DECLARE @Name as NVARCHAR(128) :[.vM
DECLARE @Owner as NVARCHAR(128) imhwY#D
DECLARE @OwnerName as NVARCHAR(128) Di,^%
DECLARE curObject CURSOR FOR !,_u)4
select 'Name' = name, p}}R-D&K
'Owner' = user_name(uid)
i<C*j4qQ
from sysobjects K(e$esLs-
where user_name(uid)=@OldOwner XAD- 'i
order by name G{As,`{
OPEN curObject 1fp?
FETCH NEXT FROM curObject INTO @Name, @Owner nI?[rCM
WHILE(@@FETCH_STATUS=0) <`8n^m*
BEGIN H5/6TX72N
if @Owner=@OldOwner \i>?q
begin |"q5sym8Y_
set @OwnerName = @OldOwner + '.' + rtrim(@Name) r8t}TU>C
exec sp_changeobjectowner @OwnerName, @NewOwner v4a8}G
end Ye%~I`@?
-- select @name,@NewOwner,@OldOwner '&P%C" 5
FETCH NEXT FROM curObject INTO @Name, @Owner c8 )DuJ#U
END x;O[c3I
close curObject F>Ah0U0
deallocate curObject (q/e1L-S
GO 4Co6(
10、SQL SERVER中直接循环写入数据 A7{\</Z
declare @i int ]6,\r"
set @i=1 J/`<!$<c
while @i<30 /aCc17>2V{
begin ^cC,.Fdw
insert into test (userid) values(@i) c1(RuP:S
set @i=@i+1 ;$, U~ 0
end ~Y[r`]X`"m
小记存储过程中经常用到的本周,本月,本年函数 EmWn%eMN
Dateadd(wk,datediff(wk,0,getdate()),-1) JJ-( Sl
Dateadd(wk,datediff(wk,0,getdate()),6) ;J( 8
L
Dateadd(mm,datediff(mm,0,getdate()),0) 0(}t8lc
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 5+0gR
&|j
Dateadd(yy,datediff(yy,0,getdate()),0) 0@0w+&*"@
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) $?iLLA~
上面的SQL代码只是一个时间段 C\3rJy(VJ
Dateadd(wk,datediff(wk,0,getdate()),-1) /|m2WxK)
Dateadd(wk,datediff(wk,0,getdate()),6) ,$L4dF3
就是表示本周时间段. Wx%H%FeK
下面的SQL的条件部分,就是查询时间段在本周范围内的: h]&GLb&<?
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) #<"~~2?
而在存储过程中 BQHVQs
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) |*eZD-f
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 8P\G}