SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 pq`MO
.R
c`Cn9bX
q\"$~*
一、基础 N"{o3QmA
1、说明:创建数据库 V-|}.kOH2
CREATE DATABASE database-name '`"&RuB
2、说明:删除数据库 F'!}$oT"
drop database dbname %Z|*!A+wN5
3、说明:备份sql server V _,*
--- 创建 备份数据的 device SfR_#"Uu
USE master 5{[0Clb)
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' m 9S5;kB]
--- 开始 备份 gS 3&,^
BACKUP DATABASE pubs TO testBack 8a{g EZT,
4、说明:创建新表 6P8X)3CE<T
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) o\#e7 Hqbh
根据已有的表创建新表: 3{=4q
A:create table tab_new like tab_old (使用旧表创建新表) MJoC*8QxM
B:create table tab_new as select col1,col2... from tab_old definition only ~]Jfg$'
5、说明:删除新表 fQh!1 R
drop table tabname ,#{aAx|]
6、说明:增加一个列 <o
O_wS@:
Alter table tabname add column col type &iivSc;#
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 ljRR
7、说明:添加主键: Alter table tabname add primary key(col) sj~'.Zs%
说明:删除主键: Alter table tabname drop primary key(col) 1+Oo Qs
8、说明:创建索引:create [unique] index idxname on tabname(col....) b7/4~_s
删除索引:drop index idxname ZhU2z*qN#
注:索引是不可更改的,想更改必须删除重新建。 }^t?v*kcA
9、说明:创建视图:create view viewname as select statement 5q[@N J
删除视图:drop view viewname N 2\,6 <
10、说明:几个简单的基本的sql语句 1^mO"nX
选择:select * from table1 where 范围 l0f6L xfz
插入:insert into table1(field1,field2) values(value1,value2) $I%]jAh6
删除:delete from table1 where 范围 I nk76-
更新:update table1 set field1=value1 where 范围 H{If\B%1t
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 3ly|y{M",
排序:select * from table1 order by field1,field2 [desc] fQdQ[
总数:select count as totalcount from table1 pe8MG(V
求和:select sum(field1) as sumvalue from table1 TaH9Nu
平均:select avg(field1) as avgvalue from table1 ,==lgM2V>
最大:select max(field1) as maxvalue from table1 <ZLs+|1
最小:select min(field1) as minvalue from table1 qmGB~N|N
*(J<~:V?
;S/fe(C
.W\Fa2}%av
11、说明:几个高级查询运算词 Om*Dy}
?p]w_l
(Y86q\DQ?|
A: UNION 运算符 AiuF3`Xa
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 3-0Y<++W3>
B: EXCEPT 运算符 vnE,}(M
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 3mWN?fC
C: INTERSECT 运算符 *hba>LZ
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 sE% n=Ww
注:使用运算词的几个查询结果行必须是一致的。 _kfApO)O
12、说明:使用外连接 q%l<Hw6{z
A、left outer join: b1+Nm
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 MWB?V?qPSC
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 {v(3[7
B:right outer join: %rkUy?=vu
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 gyIPG2d
C:full outer join: b.F2m(e2
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 aE+E'iL
]M.ufbg uq
'(?@R5a
二、提升 Y) Z>Bi
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) nZ]d[
法一:select * into b from a where 1<>1 | jlR],
法二:select top 0 * into b from a
"dIoIW
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) a,X3=+_K
insert into b(a, b, c) select d,e,f from b; / wEr>[8S
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) )57OZ
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 9E+^FZ e
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. !|SawT5t
4、说明:子查询(表名1:a 表名2:b) HRk+2'wjAz
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) .d;/6HD[y
5、说明:显示文章、提交人和最后回复时间 kC)dia{$
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b Xo
P]PR`cQ
6、说明:外连接查询(表名1:a 表名2:b) lw7wvZD
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 }q/VH57
7、说明:在线视图查询(表名1:a ) Q"KH!Bu%P
select * from (SELECT a,b,c FROM a) T where t.a > 1; f_}55?i0
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 K/altyj`
select * from table1 where time between time1 and time2 H4UnF5G
select a,b,c, from table1 where a not between 数值1 and 数值2 + IMP<
9、说明:in 的使用方法 l#Iof)@#
select * from table1 where a [not] in ('值1','值2','值4','值6') %k"-rmW
10、说明:两张关联表,删除主表中已经在副表中没有的信息 6_XTeu
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
!9-dS=:Y
11、说明:四表联查问题: ~*&_zPTN
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 ..... :wMZ&xERDZ
12、说明:日程安排提前五分钟提醒 tUzuel*
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 &_ber ad
13、说明:一条sql 语句搞定数据库分页 #+XKfumLk
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 f"/NY6
14、说明:前10条记录 w$1.h'2
select top 10 * form table1 where 范围 8YCtU9D
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 7:]I@Gc'
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) u4%-e)$X
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 -)w/nq
(select a from tableA ) except (select a from tableB) except (select a from tableC) avdi9!J2
17、说明:随机取出10条数据 rLp0VKPe
select top 10 * from tablename order by newid() B4|3@X0(
18、说明:随机选择记录 - iU7'
select newid() nfd^'}$]
19、说明:删除重复记录 Hc}(+wQN%
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) #;+GNF}0mG
20、说明:列出数据库里所有的表名 F<oJ
select name from sysobjects where type='U' _TH'v:C
21、说明:列出表里的所有的 o)w'w34FCT
select name from syscolumns where id=object_id('TableName') {jbOcx$t
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 97Whn*
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 O -@7n0
显示结果: Hh,\>= ':
type vender pcs 8I
JFQDGA9
电脑 A 1 jQc$>M<"o
电脑 A 1 S-My6'ar
光盘 B 2 u)%J5TR .Y
光盘 A 2 By%aTuV$
手机 B 3 V_h, UYN
手机 C 3 N"T+.
r
23、说明:初始化表table1 .DHPKz`W0
TRUNCATE TABLE table1 *PD7H9m
24、说明:选择从10到15的记录 (T,ST3{*k
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc znD0&CS9q
lBl`R|Gt
eR?`o !@y
k: D<Q
三、技巧 po!0j+ r3
1、1=1,1=2的使用,在SQL语句组合时用的较多 L\!Pa+Iod
"where 1=1" 是表示选择全部 "where 1=2"全部不选, OF!(BJL
如: }{HlY?S
if @strWhere !='' e_7a9:2e
begin Ymx/N+Jl
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere *&!&Y*Jzg
end T2GJoJ!
else U",kAQY
begin {o AJL
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' o[aRG7C
end fE,\1LK4
我们可以直接写成 ^k/@y@%
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
dCN4aY[d
2、收缩数据库 kowBB0
--重建索引 1ih|b8)Dn
DBCC REINDEX =#^\9|?$
DBCC INDEXDEFRAG ]v$VZ'
--收缩数据和日志 9/`T]s"
DBCC SHRINKDB W
A-\2
DBCC SHRINKFILE 'jqkDPn
3、压缩数据库 6ID@ 0
dbcc shrinkdatabase(dbname) ZE#A?5lb
4、转移数据库给新用户以已存在用户权限 /aNlr>^
exec sp_change_users_login 'update_one','newname','oldname' sZA7)Z`7
go fn;`V it#
5、检查备份集 c#Y/?F2p
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' PIl:z?q({
6、修复数据库 g=Rl4F]
ALTER DATABASE [dvbbs] SET SINGLE_USER ]9F$/M#
GO xbsp[0I,
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK yO.q{|kX
GO \9jEpE^Ju(
ALTER DATABASE [dvbbs] SET MULTI_USER
~p<w>C9
GO =wtu
7、日志清除 PF~w$ eeQ
SET NOCOUNT ON Bz!SZpW(M
DECLARE @LogicalFileName sysname, 8\P!47'q
@MaxMinutes INT, 90X<Qs
@NewSize INT J4"?D9T3G
USE tablename -- 要操作的数据库名 &C6Z-bS"
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 LB$#]
Z
@MaxMinutes = 10, -- Limit on time allowed to wrap log. Z7J8%ywQ
@NewSize = 1 -- 你想设定的日志文件的大小(M) K+p7yZJ
-- Setup / initialize f@rR2xZoQ
DECLARE @OriginalSize int }Ox5,S}ra
SELECT @OriginalSize = size N"L@
FROM sysfiles NU"Ld+gw
WHERE name = @LogicalFileName #?u#=]
SELECT 'Original Size of ' + db_name() + ' LOG is ' + K!g!tA$
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + Cj'XL}
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' zsOOx%
+
FROM sysfiles b*Sw")#
WHERE name = @LogicalFileName n%X5TJE
CREATE TABLE DummyTrans .Yg7V'R1
(DummyColumn char (8000) not null) WCRGqSr4
DECLARE @Counter INT, +`=rzL"0I7
@StartTime DATETIME, ~+
[T{{
@TruncLog VARCHAR(255) 1L3+KD~
SELECT @StartTime = GETDATE(), >sGIpER7
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' @|N{EI
DBCC SHRINKFILE (@LogicalFileName, @NewSize) 2Kwr=t
EXEC (@TruncLog) @` 5P^H7
-- Wrap the log if necessary. *QH~z2:[
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired xU9T8Lw
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 5d|hP4fEc
AND (@OriginalSize * 8 /1024) > @NewSize fkk&pu
BEGIN -- Outer loop. 2:GS(%~
SELECT @Counter = 0 t[}&*2"$/
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) I' [gGK4F
BEGIN -- update p.)IdbC`B
INSERT DummyTrans VALUES ('Fill Log') '8Wu9 phT
DELETE DummyTrans mH6\8I
SELECT @Counter = @Counter + 1 x<d2/[(}mT
END C@b-)In
EXEC (@TruncLog) W<Ri(g-
END q[}W&t,
SELECT 'Final Size of ' + db_name() + ' LOG is ' + efN5(9*9R
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + T]oVNy
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' zPm|$d
FROM sysfiles `]F}O \H
WHERE name = @LogicalFileName M,w5F5
DROP TABLE DummyTrans $/J4?Wik
SET NOCOUNT OFF ;x,yGb`
8、说明:更改某个表 ^J~5k,7jX
exec sp_changeobjectowner 'tablename','dbo' L+K,Y:D!W
9、存储更改全部表 Tji* \<?
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch ,B 2p\
@OldOwner as NVARCHAR(128), L5DeLF+
@NewOwner as NVARCHAR(128) >v#6SDg
AS _D"V^4^yqu
DECLARE @Name as NVARCHAR(128) hik.c3
DECLARE @Owner as NVARCHAR(128) 2,'~'
DECLARE @OwnerName as NVARCHAR(128) 6v?tZ&,
G
DECLARE curObject CURSOR FOR 5D+rR<pD}"
select 'Name' = name, Fe L !%z
'Owner' = user_name(uid) ?uh%WN6nU]
from sysobjects =[do([A
where user_name(uid)=@OldOwner aE(DNeG-H
order by name <5O:jd
OPEN curObject P1_6:USBM
FETCH NEXT FROM curObject INTO @Name, @Owner &[b(Lx|i
WHILE(@@FETCH_STATUS=0) C[R|@9NI
BEGIN *)bh6b=7
if @Owner=@OldOwner VW\xuP
begin T3bYj|rh=
set @OwnerName = @OldOwner + '.' + rtrim(@Name) w5<&b1:
exec sp_changeobjectowner @OwnerName, @NewOwner aOhi<I`*
end lK Ry4~O
-- select @name,@NewOwner,@OldOwner VPvQ]}g6k
FETCH NEXT FROM curObject INTO @Name, @Owner 0JE*| CtK
END .k!<Oqa
close curObject q~. .Z Y`7
deallocate curObject ,8[R0wsBaz
GO *E|#g
10、SQL SERVER中直接循环写入数据 T-F8[dd^/
declare @i int :d1Kq _\K
set @i=1 lk4U/:
while @i<30 ^]k=*>{
R
begin
VXPsYR&
insert into test (userid) values(@i) Ju-#F@38
set @i=@i+1 D4jZh+_|S
end lw`$(,
小记存储过程中经常用到的本周,本月,本年函数 m^$KDrkD
Dateadd(wk,datediff(wk,0,getdate()),-1) K |^OnM
Dateadd(wk,datediff(wk,0,getdate()),6) p'4ZcCW?f
Dateadd(mm,datediff(mm,0,getdate()),0) |-9##0H
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 9}T(m(WQVu
Dateadd(yy,datediff(yy,0,getdate()),0) ~Dbu;cqR@
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) }hA)p:
上面的SQL代码只是一个时间段 Lvb'qZ6n
Dateadd(wk,datediff(wk,0,getdate()),-1) uWLf9D "
Dateadd(wk,datediff(wk,0,getdate()),6) .R^q$U~v3
就是表示本周时间段. g?rK&UTU
下面的SQL的条件部分,就是查询时间段在本周范围内的: Ri/D>[
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ,l#f6H7p
而在存储过程中 k r5'E#
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) Wgm{
]9Q
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) wvI}|c