SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 @4#vm@Yf_
"@ n%Z
%iB,IEw
一、基础 mE[y SrV
1、说明:创建数据库 :T~ [
CREATE DATABASE database-name An@t?#4gxi
2、说明:删除数据库 gqR(.Pu
drop database dbname F0#
'WfM#
3、说明:备份sql server \2z>?i)
--- 创建 备份数据的 device AXB7oV,xt
USE master CC`JZ.SO
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' I1J-)R+
--- 开始 备份 +ge?w#R
BACKUP DATABASE pubs TO testBack ^zr`;cJ+c
4、说明:创建新表 dN6?c'iN?2
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) i#O SC5ZI
根据已有的表创建新表: '"Nr, vQo
A:create table tab_new like tab_old (使用旧表创建新表) VU#7%ufu&
B:create table tab_new as select col1,col2... from tab_old definition only
!@sUj
5、说明:删除新表 wuBPfb
drop table tabname 1;iUWU1@
6、说明:增加一个列 .)3 <Q}>
Alter table tabname add column col type xD 7]C|8o
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 +7a6*;\ y
7、说明:添加主键: Alter table tabname add primary key(col) u?EN
说明:删除主键: Alter table tabname drop primary key(col) @VI@fN
8、说明:创建索引:create [unique] index idxname on tabname(col....) SX#&5Ka/
删除索引:drop index idxname QV8g#&z
注:索引是不可更改的,想更改必须删除重新建。 /_.|E]
9、说明:创建视图:create view viewname as select statement <ZW-QN4
删除视图:drop view viewname s#MPX3itK
10、说明:几个简单的基本的sql语句 kGJC\{N5N
选择:select * from table1 where 范围 x~sBzTa
插入:insert into table1(field1,field2) values(value1,value2) 6~+emlD
删除:delete from table1 where 范围 kf9X$d6
更新:update table1 set field1=value1 where 范围 (At$3b6
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! tX[WH\(xI
排序:select * from table1 order by field1,field2 [desc] d_CT$
总数:select count as totalcount from table1 T4F/w|Q
求和:select sum(field1) as sumvalue from table1 =lSNs
平均:select avg(field1) as avgvalue from table1 Xc.`-J~Il
最大:select max(field1) as maxvalue from table1 ABkl%m6xf
最小:select min(field1) as minvalue from table1 sRfcF`7
<naz+QK'
8EY:tzw
ZC8wA;!z^
11、说明:几个高级查询运算词 5R7DDJk
/Kbl%u
,,TnIouy
A: UNION 运算符 Z :gyz$9w
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 6B-16
B: EXCEPT 运算符 R-Sym8c
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 5-M-X#(
C: INTERSECT 运算符 rlD8D|ZG
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 LQ% `c
注:使用运算词的几个查询结果行必须是一致的。 9u:Q,0\
12、说明:使用外连接 N g,j#
A、left outer join: _cwpA#x`}
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 >d6| ^h'0
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 Pz^544\~ou
B:right outer join: I:.s_8mH}
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 ?Ob3tUz2
C:full outer join: ]R *A
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Xr{v~bf
ntY]SK%Z
F[0]/
二、提升 aDCwI :Li(
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) pJ{Y
lS{
法一:select * into b from a where 1<>1 Debv4Gr;^
法二:select top 0 * into b from a f!"w5qC^
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) = /8cp
insert into b(a, b, c) select d,e,f from b; E.f%H(b
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ?4B`9<j8%
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 ElXFeJ%[G
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ~5g ~;f[4
4、说明:子查询(表名1:a 表名2:b) y}H!c;
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) c9Yrw^
5、说明:显示文章、提交人和最后回复时间 `x|?&Ytmf9
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
@8
6f
6、说明:外连接查询(表名1:a 表名2:b) <}LC~B!
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 0`hdMLONR
7、说明:在线视图查询(表名1:a ) rs.)CMk53
select * from (SELECT a,b,c FROM a) T where t.a > 1; v2?ZQeHr_(
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 UI#h&j5pW
select * from table1 where time between time1 and time2 `2snz1>!j
select a,b,c, from table1 where a not between 数值1 and 数值2 +q oRP2
9、说明:in 的使用方法 ix$bRdl
select * from table1 where a [not] in ('值1','值2','值4','值6') f5r0\7y0
10、说明:两张关联表,删除主表中已经在副表中没有的信息 1"g<0
W
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) "]dI1 g_
11、说明:四表联查问题: r3Ykz%6
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 ..... Q;u pau
12、说明:日程安排提前五分钟提醒 }'.m*#Y
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 #F#%`Rv1
13、说明:一条sql 语句搞定数据库分页 `9 L>*
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 }*-@!wc-N
14、说明:前10条记录 <qSC#[xu
select top 10 * form table1 where 范围 nlYNN/@"
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 1qch]1
^G
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) HhpDR
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 PdCEUh\>y
(select a from tableA ) except (select a from tableB) except (select a from tableC) TN.rrop`#g
17、说明:随机取出10条数据 OH88n69
select top 10 * from tablename order by newid() @VBcJ{e,
18、说明:随机选择记录 w &(ag$p'
select newid() +H.`MZ=
19、说明:删除重复记录 ;I*o@x_
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) -%~4W?
20、说明:列出数据库里所有的表名 ~`/V(r;o
select name from sysobjects where type='U' R@0R`Zs
21、说明:列出表里的所有的 /mMV{[
select name from syscolumns where id=object_id('TableName') 1,~D4lD|
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 ;r8X.>P*
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 Rv=YFo[B
显示结果: yr6V3],Tp
type vender pcs <[phnU^
8
电脑 A 1 q<J~ ~'
电脑 A 1 pI[uUu7O
光盘 B 2 j [a(#V{
光盘 A 2 XkqCZHYkS
手机 B 3 GeqPRah
手机 C 3 qLCR] _*
23、说明:初始化表table1 dI2
V>vk
TRUNCATE TABLE table1 #ABCDi={zA
24、说明:选择从10到15的记录 lk!@?
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc *#2h/Q.
yX5\gO6G
@ 7u 0v
>usL*b0%
三、技巧 43w}qY1
1、1=1,1=2的使用,在SQL语句组合时用的较多 GB^B r6
"where 1=1" 是表示选择全部 "where 1=2"全部不选, edD)TpmE,
如: ~>G^=0LT
if @strWhere !='' HsWk*L `y
begin KXrjqqXs
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere D=$)n_F
end K@2),(z
else +mPx8P&%
begin $j~RWfw-
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' }b}m3i1
end gr{ DWCK
我们可以直接写成 {I((p_
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere {vj)76%y
2、收缩数据库 7^285)UQA
--重建索引 Om<a<q
DBCC REINDEX 0_/[k*Re
DBCC INDEXDEFRAG yu|>t4#GT
--收缩数据和日志 JC"z&ka
DBCC SHRINKDB q)GdD==
DBCC SHRINKFILE =D(j)<9$A
3、压缩数据库 yauvXosX
dbcc shrinkdatabase(dbname) l] vm=7:
4、转移数据库给新用户以已存在用户权限 pCDmXB
exec sp_change_users_login 'update_one','newname','oldname' jdN`mosJ
go }vuARZ>
5、检查备份集 ;a/E42eN;
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' #Z #-Ht
6、修复数据库 o-\[,}T)M
ALTER DATABASE [dvbbs] SET SINGLE_USER s"|Pdc4
GO Wqnc{oq|$
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK #tHK"20
GO gUlo]!$
ALTER DATABASE [dvbbs] SET MULTI_USER SM'|+ d
GO t1".0
7、日志清除 m 5.Zu.
SET NOCOUNT ON #E[0ys1O
DECLARE @LogicalFileName sysname, x2EUr,7
@MaxMinutes INT, _!6jR5&r,
@NewSize INT Gt1U!dP
USE tablename -- 要操作的数据库名 M}a6Vu9
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 3]>| i
@MaxMinutes = 10, -- Limit on time allowed to wrap log. 0sqFF[i
@NewSize = 1 -- 你想设定的日志文件的大小(M) w]H->B29C
-- Setup / initialize sK{e*[I>W
DECLARE @OriginalSize int 9x8fhAy}4
SELECT @OriginalSize = size 5R-6ji
FROM sysfiles b
6p|q_e
WHERE name = @LogicalFileName XSDpRo
SELECT 'Original Size of ' + db_name() + ' LOG is ' + Y73C5.dNcE
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + :h$$J
lP
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' oRFq@g
FROM sysfiles !VJoM,b8
WHERE name = @LogicalFileName {=WgzP
CREATE TABLE DummyTrans +0&/g&a\R
(DummyColumn char (8000) not null) NUZl`fu1Z4
DECLARE @Counter INT, M{@(G5
@StartTime DATETIME, Z r8*et
@TruncLog VARCHAR(255) 3mgD(,(^
SELECT @StartTime = GETDATE(), -@s#uA
h
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 7r!x1
DBCC SHRINKFILE (@LogicalFileName, @NewSize) M7T5
~/4
EXEC (@TruncLog) %4H%?4
-- Wrap the log if necessary. Sf'CN8
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired QY/w
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) zdYjF|
AND (@OriginalSize * 8 /1024) > @NewSize \<' ?8ri#
BEGIN -- Outer loop. 2:kH[#
SELECT @Counter = 0 Ie_wHcM<
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) +R &gqja
BEGIN -- update NJ<F>3
INSERT DummyTrans VALUES ('Fill Log') Q?vlfZR`8
DELETE DummyTrans (e~N q
SELECT @Counter = @Counter + 1 +2{Lh7Ks
END 6t$8M[0-U
EXEC (@TruncLog) qna8|3eP
END Nc`L;CP
SELECT 'Final Size of ' + db_name() + ' LOG is ' + Y|n"dMrL
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + "[J^YKoF
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' DI>s-7
FROM sysfiles e=
AKD#
WHERE name = @LogicalFileName yAt^;
DROP TABLE DummyTrans oxs#866x
SET NOCOUNT OFF ?
k /`
8、说明:更改某个表 @5FQX
exec sp_changeobjectowner 'tablename','dbo' bw7@5=?;
9、存储更改全部表 Ytkv!]"
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch b;n[mk
@OldOwner as NVARCHAR(128), az$FnVNn=
@NewOwner as NVARCHAR(128) ,F|f. 7;
AS p2eGm-Erq
DECLARE @Name as NVARCHAR(128) }tz7b#
DECLARE @Owner as NVARCHAR(128) [WmM6UEVS
DECLARE @OwnerName as NVARCHAR(128) iMlWM-wz>O
DECLARE curObject CURSOR FOR U/U);frH
select 'Name' = name, icgfB-1|i
'Owner' = user_name(uid) S'" Df5
from sysobjects 6Oq7#3]
where user_name(uid)=@OldOwner Vksuu@cch
order by name F%|h;+5
OPEN curObject aUp
g u"
FETCH NEXT FROM curObject INTO @Name, @Owner KoT\pY^7\
WHILE(@@FETCH_STATUS=0) g#bRT*,L
BEGIN ^W^OfY
if @Owner=@OldOwner /wp6KXm
begin `3pW]&
set @OwnerName = @OldOwner + '.' + rtrim(@Name) 'DR!9De
exec sp_changeobjectowner @OwnerName, @NewOwner -f .,tM=
end c)J%`i$
-- select @name,@NewOwner,@OldOwner ;uJMG
FETCH NEXT FROM curObject INTO @Name, @Owner 4dlGxat
END Hs8>anVo[
close curObject zPO9!?7|
deallocate curObject *wearCPeJ
GO dN q$}
10、SQL SERVER中直接循环写入数据 h{Y",7]!
declare @i int
D7Z /H'|
set @i=1 LV Ge]lD
while @i<30 Xvu(vA
begin vP&(-a
insert into test (userid) values(@i) aN?zmkPpov
set @i=@i+1 /:
"1Z]@
end <)9y{J}s:
小记存储过程中经常用到的本周,本月,本年函数 )`:UP~)H
Dateadd(wk,datediff(wk,0,getdate()),-1) ]Ze1s02(
Dateadd(wk,datediff(wk,0,getdate()),6) \e*]Ls#jS
Dateadd(mm,datediff(mm,0,getdate()),0) 4x34u}l
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) %J(:ADu]
Dateadd(yy,datediff(yy,0,getdate()),0) e
,(mR+a8
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) vsPu*[%
上面的SQL代码只是一个时间段 G{}VPcrbC
Dateadd(wk,datediff(wk,0,getdate()),-1) @JMiO^
Dateadd(wk,datediff(wk,0,getdate()),6) C+$#y2"z#n
就是表示本周时间段. P:c w|Q
下面的SQL的条件部分,就是查询时间段在本周范围内的: M3\AY30L
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) 54T`OE
=
而在存储过程中 iS^QTuk3%
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) uRvP hkqm
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ,+k\p5P