SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Bw_Ih|y,w
gZ+I(o{
%ly;2HIk
一、基础 lwY{rWo
1、说明:创建数据库 > T-O3/KN
CREATE DATABASE database-name j}VOr >xz
2、说明:删除数据库 <khx%<)P
drop database dbname vlPE8U=
3、说明:备份sql server *$cp"
--- 创建 备份数据的 device :jUuw:\
USE master YAPD7hA
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' l?R_wu,Q
--- 开始 备份 0l:5hD,)F
BACKUP DATABASE pubs TO testBack eXOFA d]>u
4、说明:创建新表 (C3d<a\:
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) (Dl"s`UH~
根据已有的表创建新表: bv+e'$U3
A:create table tab_new like tab_old (使用旧表创建新表) @ [FFYVru
B:create table tab_new as select col1,col2... from tab_old definition only UpIf t=@P
5、说明:删除新表 WuNu}Ibl}m
drop table tabname U3V<ITZI8t
6、说明:增加一个列 e{}o:r
Alter table tabname add column col type 8 6+>|
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 DA
wzXsx
7、说明:添加主键: Alter table tabname add primary key(col) ]bR'J\Fwl
说明:删除主键: Alter table tabname drop primary key(col) :5*<QJuI#A
8、说明:创建索引:create [unique] index idxname on tabname(col....) 6=g7|}
删除索引:drop index idxname vJCL
m/}*
注:索引是不可更改的,想更改必须删除重新建。 [.Y=~)7FB
9、说明:创建视图:create view viewname as select statement ho20>vw#
删除视图:drop view viewname l3afuD:
10、说明:几个简单的基本的sql语句 m[bu(q z
选择:select * from table1 where 范围 V")Q4h{
插入:insert into table1(field1,field2) values(value1,value2) c:6w >:
删除:delete from table1 where 范围 qnS7z%H8
更新:update table1 set field1=value1 where 范围 IY19G U9
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 9@1W= sl
排序:select * from table1 order by field1,field2 [desc] ~>C >LH>8
总数:select count as totalcount from table1 kp6x6%{K\
求和:select sum(field1) as sumvalue from table1 M[{Cy[ta
平均:select avg(field1) as avgvalue from table1 7_3O]e[8
最大:select max(field1) as maxvalue from table1 lET)<V(Y
最小:select min(field1) as minvalue from table1 P
X0#X=$
}dHiW:J>
amSyGQ2
)aC+qhh
11、说明:几个高级查询运算词 JdRs=#X
"_K 6=
/iN\)y#u1
A: UNION 运算符 sXa8(xc
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 64vSJx>u
B: EXCEPT 运算符 yTn@p(J
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 b910Z?B^L
C: INTERSECT 运算符 C5M-MZaS
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 H<xC%/8
注:使用运算词的几个查询结果行必须是一致的。 -,;Ep'
12、说明:使用外连接 =z/F=1^<
A、left outer join: D1n2Z:9
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 KwHOV$lD;
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 $G_<YVXcG
B:right outer join: :acQK=fe
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 1A)~Y
C:full outer join: uUe\[-~
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 G8s`<:9*
3k<#;(
[GP(r
二、提升 UBVb#FNF
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) kYs|")isj
法一:select * into b from a where 1<>1 x-pMT3m\D#
法二:select top 0 * into b from a |gVO Iq
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) ?>y-5B[K/(
insert into b(a, b, c) select d,e,f from b; K7.<,E"M.
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 3DHm9n+/:
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 RI(uG-Y
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ~ YK<T+
4、说明:子查询(表名1:a 表名2:b) `Z/ IW
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) 9CNHjs+-}s
5、说明:显示文章、提交人和最后回复时间 K_5&_P1
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b @5y(>>C}8%
6、说明:外连接查询(表名1:a 表名2:b) `Ek !;u>
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c )ziQ=k6d6
7、说明:在线视图查询(表名1:a ) nB5[]x'
select * from (SELECT a,b,c FROM a) T where t.a > 1; !{Y#<tG]
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 4BT`|(7
select * from table1 where time between time1 and time2 F^YIZ,=p!
select a,b,c, from table1 where a not between 数值1 and 数值2 _}&]`,s>
9、说明:in 的使用方法 C6VoOT)\
select * from table1 where a [not] in ('值1','值2','值4','值6') \uk #pL
10、说明:两张关联表,删除主表中已经在副表中没有的信息 9^^#I~-
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) W~%~^2g ;k
11、说明:四表联查问题: Fsf22
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 ..... ;*2e;m~)?
12、说明:日程安排提前五分钟提醒 gQuw|u
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 kYB
<FwwB
13、说明:一条sql 语句搞定数据库分页 vb- .^l
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 ?I'-C?(t@1
14、说明:前10条记录 v-3zav
select top 10 * form table1 where 范围 Hl;p>>n
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) J,O@T)S@
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) j/<y
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 J31M:<
(select a from tableA ) except (select a from tableB) except (select a from tableC) tA-B3 ]
17、说明:随机取出10条数据 mx9/K+:
select top 10 * from tablename order by newid() 7LwS =yP
18、说明:随机选择记录 pQ
6#L
select newid() D5pF:~tQ(j
19、说明:删除重复记录 `t1$Ew<
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) NVeRn
20、说明:列出数据库里所有的表名 bUN,P"
select name from sysobjects where type='U' @q/1m~t
21、说明:列出表里的所有的 pK9^WT@
select name from syscolumns where id=object_id('TableName') Z0eBx
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 z#VpS=
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 +Rgw+o
显示结果: )$B+3f
type vender pcs !Blk=L+p
电脑 A 1 o#xg:m_py
电脑 A 1 ;a&:r7]=
光盘 B 2 oKi1=d+T
光盘 A 2 (AG
手机 B 3 r^t{Ii~
手机 C 3 1N!g`=}
23、说明:初始化表table1 X-1Vp_(,TP
TRUNCATE TABLE table1 Z9&D'n)
24、说明:选择从10到15的记录 c@-K
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc Zd U{`>v
1Wk
EPj,
K$cIVsfr
g/,Bx!'8p
三、技巧 \Byk`}
9
1、1=1,1=2的使用,在SQL语句组合时用的较多 B bw1k
"where 1=1" 是表示选择全部 "where 1=2"全部不选, SECQVA_y`
如: RQCQGa^cP
if @strWhere !='' V;-.38py
begin 5\0.[W{^
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere _IV@^v
end )v=G}j^
else `Kw"XGT
begin 4E-A@FR
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' *ZR@z80i
end &}0wzcMg
我们可以直接写成 TucAs0-bF
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 4)HWPX
2、收缩数据库 P"h\7V,d%
--重建索引 .'b3iG&
DBCC REINDEX KVM@//:{
DBCC INDEXDEFRAG O^Vy"8Ji}y
--收缩数据和日志 M`P]cX)x
DBCC SHRINKDB n&m?BuG
DBCC SHRINKFILE (}X?v`Y^W
3、压缩数据库 > &vO4L
dbcc shrinkdatabase(dbname) / =m9s
4、转移数据库给新用户以已存在用户权限 'e>sHL
exec sp_change_users_login 'update_one','newname','oldname' bo;pj$eR3R
go -;)SER3Wq4
5、检查备份集 46Q;F
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' s#4ew}
6、修复数据库 Zng` oFD
ALTER DATABASE [dvbbs] SET SINGLE_USER @B'8SLoP
GO G0QXf
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK DIqT>HHZ
GO NhoS7 y(
ALTER DATABASE [dvbbs] SET MULTI_USER fuD1U}c
GO .Spi$>v
7、日志清除 QHzX
5$IM
SET NOCOUNT ON
.x!7
DECLARE @LogicalFileName sysname, StZRc\k
@MaxMinutes INT, >3`ctbe
@NewSize INT nqxq@.L2
USE tablename -- 要操作的数据库名 BgWz<k}5M
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 FPMSaN P
@MaxMinutes = 10, -- Limit on time allowed to wrap log. PyT}}UKj:
@NewSize = 1 -- 你想设定的日志文件的大小(M) "56?/ jF
-- Setup / initialize +B q}>
DECLARE @OriginalSize int gLaO#cQ%
SELECT @OriginalSize = size =3sldKL&F
FROM sysfiles ,AbKxT
f2
WHERE name = @LogicalFileName :@>br+S
SELECT 'Original Size of ' + db_name() + ' LOG is ' + Dd#
SUQ
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + SZ2q}[o`R
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' }C{}oLz
FROM sysfiles Q)6wkY+!
WHERE name = @LogicalFileName d0A\#H_&
CREATE TABLE DummyTrans \ ~LU 'j
(DummyColumn char (8000) not null) sK 1m9
DECLARE @Counter INT, [B~zoB(
@StartTime DATETIME, { 1@4}R4
@TruncLog VARCHAR(255) 32 1={\X
SELECT @StartTime = GETDATE(), ^Em@6fz[
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' P\X=*
DBCC SHRINKFILE (@LogicalFileName, @NewSize) 8q~FUJhU
EXEC (@TruncLog) {{]=zt|69
-- Wrap the log if necessary. 0"kE^=
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired QK?2E
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) ?St=7a(D
AND (@OriginalSize * 8 /1024) > @NewSize `F2*o47|t
BEGIN -- Outer loop. 3_oD[ ])A
SELECT @Counter = 0 *)NR$9lGv
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) B)DC,+@$
BEGIN -- update Jl>at
INSERT DummyTrans VALUES ('Fill Log') F/h :&B:;
DELETE DummyTrans )pS_+ZF
SELECT @Counter = @Counter + 1 V"7<[u]K|
END < R|)5/9
EXEC (@TruncLog) 7zg)h
END 2-6.r_
SELECT 'Final Size of ' + db_name() + ' LOG is ' + /G)KkBC
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + pKxX{i1l
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' y/@;c)1b9
FROM sysfiles /+4^.Q*
WHERE name = @LogicalFileName FU5LYXCs
DROP TABLE DummyTrans Z9"{f)T
SET NOCOUNT OFF \2R`q*a+
8、说明:更改某个表 KO-Zz&2f
exec sp_changeobjectowner 'tablename','dbo' z[5Y
Z~}*
9、存储更改全部表 -; us12SZ
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch P^b:?%
@OldOwner as NVARCHAR(128), yul<n>X|
@NewOwner as NVARCHAR(128) ~"JE![XR
AS Uin k
DECLARE @Name as NVARCHAR(128) ?v"K1C1.
DECLARE @Owner as NVARCHAR(128) 7#Uz*G\iZ
DECLARE @OwnerName as NVARCHAR(128) hB
P$9GR
DECLARE curObject CURSOR FOR C`2*2Y%xkG
select 'Name' = name, 'z +$3\5L
'Owner' = user_name(uid) ez^*M:K
from sysobjects + 9\:$wMN
where user_name(uid)=@OldOwner +Q SxYV
order by name uv|eVT3jNs
OPEN curObject %UUp=I
FETCH NEXT FROM curObject INTO @Name, @Owner Ok}{jwJ%W;
WHILE(@@FETCH_STATUS=0) o\@ A2r3
BEGIN N?a1sdR
if @Owner=@OldOwner P&[F t)`
begin NIGB[2V(
set @OwnerName = @OldOwner + '.' + rtrim(@Name) mh
A~eJ
exec sp_changeobjectowner @OwnerName, @NewOwner 'ZGT`'ri
end LsJs Q
h
-- select @name,@NewOwner,@OldOwner d`?U!?Si
FETCH NEXT FROM curObject INTO @Name, @Owner <OR.q
END `W"a!,s2
close curObject ;#Jq$v)D
deallocate curObject J.bFv/R
GO 0<]$v"`I
10、SQL SERVER中直接循环写入数据 7m|`tjQ1
declare @i int @4/~~
set @i=1 zj~nnfoys
while @i<30 fqcU5l[v,
begin !paN`Fz\a
insert into test (userid) values(@i) .N5hV3
set @i=@i+1 i"%JFj_G
end uQ[vgNe*m
小记存储过程中经常用到的本周,本月,本年函数 ,zAK3d&hj
Dateadd(wk,datediff(wk,0,getdate()),-1) i7S>RB
Dateadd(wk,datediff(wk,0,getdate()),6) .)iO Du
Dateadd(mm,datediff(mm,0,getdate()),0) f$1Gu
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) CN\|_y
Dateadd(yy,datediff(yy,0,getdate()),0) C2+{U
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) ?(5o@Xq
上面的SQL代码只是一个时间段 U6c)"^\
Dateadd(wk,datediff(wk,0,getdate()),-1) j>$=SMc
Dateadd(wk,datediff(wk,0,getdate()),6) pau*kMu^}
就是表示本周时间段. tJUVw=
下面的SQL的条件部分,就是查询时间段在本周范围内的: Smq r
q
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) IvEMg2f}
而在存储过程中 2YL`3cgfb
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 94~"U5oQ:
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 4*0:bhhhf_