SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 &b& ,
Aq7osU1B
@7n"yp*"
一、基础 j"Pv0tehw
1、说明:创建数据库 h@@=M
CREATE DATABASE database-name sCHJ&>m5-
2、说明:删除数据库 [}]Q?*_
drop database dbname S>1Iky|
3、说明:备份sql server -A!%*9Z
--- 创建 备份数据的 device 7Hu3>4<
USE master P7/X|M z
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ,s;UfF
--- 开始 备份 .#pU=v#/[
BACKUP DATABASE pubs TO testBack UW
EV^ &"x
4、说明:创建新表 $* Kvc$D
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) wLr_-vJ
根据已有的表创建新表: wq `Bd
A:create table tab_new like tab_old (使用旧表创建新表) }RqK84K
B:create table tab_new as select col1,col2... from tab_old definition only >[*qf9$
5、说明:删除新表 bA->{OPkT
drop table tabname GR32S=\
6、说明:增加一个列 ,zY$8y]
Alter table tabname add column col type tIgN$BHR>
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 Pgea NK5Y
7、说明:添加主键: Alter table tabname add primary key(col) cYt!n5w~W
说明:删除主键: Alter table tabname drop primary key(col) 6!FQzFCZq
8、说明:创建索引:create [unique] index idxname on tabname(col....) 4HA<P6L
删除索引:drop index idxname A3@6N(
注:索引是不可更改的,想更改必须删除重新建。 cExS7~*
9、说明:创建视图:create view viewname as select statement *;*r8[U}q
删除视图:drop view viewname rw
#$lP
10、说明:几个简单的基本的sql语句 um0N)&iY
选择:select * from table1 where 范围 P";'jVcR
插入:insert into table1(field1,field2) values(value1,value2) 83q6Sv
删除:delete from table1 where 范围 ^y%T~dLkp'
更新:update table1 set field1=value1 where 范围 MFk5K
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! ^gnZ+`3
排序:select * from table1 order by field1,field2 [desc] L;I]OC^J
总数:select count as totalcount from table1 sLQ^F
求和:select sum(field1) as sumvalue from table1 8X|-rM{
平均:select avg(field1) as avgvalue from table1 G'A R`"F
最大:select max(field1) as maxvalue from table1 0"bcdG<}
最小:select min(field1) as minvalue from table1 ea')$gR
=C.$
UX
7Jho}5J
~Jz6O U*z
11、说明:几个高级查询运算词 [hj6N*4y
z' >_Mc6
n6a`;0f[R
A: UNION 运算符 kW&TJP+5*
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 [IhYh<i
B: EXCEPT 运算符 y
h9*z3
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 9qG6Pb
C: INTERSECT 运算符 Jg|XH
L)
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 b1?'gn~
注:使用运算词的几个查询结果行必须是一致的。 S|`o]?nc>
12、说明:使用外连接 dlTt_.
A、left outer join: ) hfpwdQ
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 u4h4.NHX
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 <W $mj04@
B:right outer join: k+pr \d ~
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 `+Q%oj#FF
C:full outer join: j8lb~0JD
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 #O dJ"1A|
*bA.zmzM
"1M[5\Ax
二、提升 V6reqEh
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) R/z=p_6p7`
法一:select * into b from a where 1<>1 NHZz _a=
法二:select top 0 * into b from a 9mTJ|sN:e
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) hZ
insert into b(a, b, c) select d,e,f from b; v^ VitLC
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) :G%61x&=Zc
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 $ gS>FJ
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. }Kbb4]t|"
4、说明:子查询(表名1:a 表名2:b) B,epzI
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) v
z '&%(
5、说明:显示文章、提交人和最后回复时间 0.k7oB;f(@
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 7%eK37@u
6、说明:外连接查询(表名1:a 表名2:b)
7E~;xn;
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c fS78>*K
7、说明:在线视图查询(表名1:a ) Z}Ft:7
select * from (SELECT a,b,c FROM a) T where t.a > 1; uk<9&{
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 )|=j`jCC
select * from table1 where time between time1 and time2
]-/VHh
select a,b,c, from table1 where a not between 数值1 and 数值2 ?2Py_gkf
9、说明:in 的使用方法 -C?ZB}`
select * from table1 where a [not] in ('值1','值2','值4','值6') L0WN\|D
10、说明:两张关联表,删除主表中已经在副表中没有的信息 b!5~7Ub.No
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) XuM'_FN`A<
11、说明:四表联查问题: 2!=f hN
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 ..... *YuF0Yt
12、说明:日程安排提前五分钟提醒 9m~p0 ILh
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 *wB1,U{
13、说明:一条sql 语句搞定数据库分页 4u})+2W
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 n8ZZ#}Nhg
14、说明:前10条记录 -z%^)VE
select top 10 * form table1 where 范围 q9r[$%G
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) ZRU{[4
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) i6Emhji
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 mSh[}%swj
(select a from tableA ) except (select a from tableB) except (select a from tableC) &Ys<@M7E:
17、说明:随机取出10条数据 .jjG(L
select top 10 * from tablename order by newid() JYbL?N
18、说明:随机选择记录 Vb]=B~ ^`
select newid() [%1CRk
19、说明:删除重复记录 %2V? ,zY@
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ok\vQs(a
20、说明:列出数据库里所有的表名 Q:d]imw!O
select name from sysobjects where type='U' 0[?Xxk}s0
21、说明:列出表里的所有的 fSvM(3Y<Qh
select name from syscolumns where id=object_id('TableName') Uf;^%*P4
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 K:#I
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 \\B(r
显示结果: @>Km_Ax
type vender pcs VY=jc~c]v
电脑 A 1 ^ Q ?
电脑 A 1 CU2*z(]&
光盘 B 2 _H7x9
y=
光盘 A 2 #( 146
手机 B 3 |~mOfuQb
手机 C 3 ra
g Xn
23、说明:初始化表table1 O) n~](sC\
TRUNCATE TABLE table1 9gK`E
24、说明:选择从10到15的记录 y(yHt=r
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc HJ[c M6$2
$1L>)S
-3Z,EaG^
1JG'%8}#8
三、技巧 YHygo#4=8
1、1=1,1=2的使用,在SQL语句组合时用的较多 Pw`8Wj
"where 1=1" 是表示选择全部 "where 1=2"全部不选, nV/G8SeI
如: y'nK>)WG4
if @strWhere !='' j[J-f@F \Y
begin E,x+JeKV
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere wc^tgE
end h( u8&MHx
else ">,|V-H
begin ag;pN*z
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' czgO ;3-C
end "
9wvPC ^
我们可以直接写成 yEoF4bt
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere Ww+IWW@
2、收缩数据库 Ad9}9!<
--重建索引 9ZsVy
DBCC REINDEX w4{<n/"
DBCC INDEXDEFRAG paE[rS\
--收缩数据和日志 %axh`xK#
DBCC SHRINKDB }?_?V&K|
DBCC SHRINKFILE 4-y:/8
3、压缩数据库 ,~@X{7U
dbcc shrinkdatabase(dbname) RmeD$>7
4、转移数据库给新用户以已存在用户权限 SBk4_J/_
exec sp_change_users_login 'update_one','newname','oldname' k:#!zK}
go [
=9T*Sp
5、检查备份集 j[G
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' )e=D(qd
6、修复数据库 Nv}=L
: E
ALTER DATABASE [dvbbs] SET SINGLE_USER WH@,kH@
GO Zbt.t]N
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK '9Xu
p
GO $$;M^WV^?.
ALTER DATABASE [dvbbs] SET MULTI_USER /cQueUME`
GO _P 3G
7、日志清除 ND#Yenye
SET NOCOUNT ON -[9JJ/7y
DECLARE @LogicalFileName sysname, }t=!(GOb}
@MaxMinutes INT, }"P|`"WW
@NewSize INT pis`$_kmwV
USE tablename -- 要操作的数据库名 1N#|
}ad
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
}Gm>`cw-
@MaxMinutes = 10, -- Limit on time allowed to wrap log. S8wLmd>
@NewSize = 1 -- 你想设定的日志文件的大小(M) N&+x+;Kx
-- Setup / initialize ^]0Pfna+N
DECLARE @OriginalSize int :tB1D@Cb6
SELECT @OriginalSize = size iDz++VNV
FROM sysfiles Sc1 8dC0
WHERE name = @LogicalFileName (!7sE9rP
SELECT 'Original Size of ' + db_name() + ' LOG is ' + mt+Oi70
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + f<fXsSv(
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' l\!fj#
FROM sysfiles PI:4m%[
WHERE name = @LogicalFileName e L^|v
CREATE TABLE DummyTrans p6]1w]*R
(DummyColumn char (8000) not null) RYQR(v
DECLARE @Counter INT, t?-n*9,#S
@StartTime DATETIME, 5z8d}
I
@TruncLog VARCHAR(255) b"uu
SELECT @StartTime = GETDATE(), TA`1U;c{n
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ~"&|W'he[
DBCC SHRINKFILE (@LogicalFileName, @NewSize) vkx7paY_
EXEC (@TruncLog) WwBOM~/`2
-- Wrap the log if necessary. ;!mzyb*
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired L:pYn_
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) qYjce]c
AND (@OriginalSize * 8 /1024) > @NewSize 2W96Zju\
BEGIN -- Outer loop. vrhT<+q
SELECT @Counter = 0 JPc+rfF
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 8:c-k|CX
BEGIN -- update ]}-7_n#cC
INSERT DummyTrans VALUES ('Fill Log') ?FeYN+qR
DELETE DummyTrans \378rQU
SELECT @Counter = @Counter + 1 0w\zLU
END 7Oa#c<2]
EXEC (@TruncLog) Pg0x/X{t
END mzaWST]
SELECT 'Final Size of ' + db_name() + ' LOG is ' + `iAF3:
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 0d"[l@UU0
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' Vod\a5c
FROM sysfiles dGYn4i2k?
WHERE name = @LogicalFileName Ustv{:7v
DROP TABLE DummyTrans <ro7vPKNa
SET NOCOUNT OFF uk<4+x,2)
8、说明:更改某个表 8 S:w7Hr
exec sp_changeobjectowner 'tablename','dbo' <EB+1GFuI
9、存储更改全部表 B:;pvW]
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @fZ,.2ar
@OldOwner as NVARCHAR(128), |mdVdD~go
@NewOwner as NVARCHAR(128) (
iBl
AS G C),N\@Q
DECLARE @Name as NVARCHAR(128) .779pT!,M
DECLARE @Owner as NVARCHAR(128) ?cBwPetp
DECLARE @OwnerName as NVARCHAR(128) \:# L)
DECLARE curObject CURSOR FOR av}k)ZT_
select 'Name' = name, H1pO!>M
'Owner' = user_name(uid) =)H.cuc
from sysobjects 5,Jp[bw{H{
where user_name(uid)=@OldOwner (lBCO?`fx
order by name (>UZ<2GPL
OPEN curObject 2\A$6N;_
FETCH NEXT FROM curObject INTO @Name, @Owner Ja7R2-0ii#
WHILE(@@FETCH_STATUS=0) dh`K`b4I
BEGIN |"X*@s\'
if @Owner=@OldOwner xaq-.IQAM$
begin 8rnwXPBN
set @OwnerName = @OldOwner + '.' + rtrim(@Name) =r?hgGWe
exec sp_changeobjectowner @OwnerName, @NewOwner |C;=-|
end Z58X5"
-- select @name,@NewOwner,@OldOwner ?>D+ge
FETCH NEXT FROM curObject INTO @Name, @Owner Yh@JXJ>
END
_JzEGpeG
close curObject b@gc{R}7
deallocate curObject V%7WUq
GO knu,"<
10、SQL SERVER中直接循环写入数据 =V,mtT
declare @i int DbBcQ%
set @i=1 a?I=
!js
while @i<30 1y4|{7bb
begin }WC[$Y_@
insert into test (userid) values(@i) &=@IzmA
set @i=@i+1 \+oQd=K@
end $B2J
T9
小记存储过程中经常用到的本周,本月,本年函数 sQUM~HD\a
Dateadd(wk,datediff(wk,0,getdate()),-1) ="1Ind@w!
Dateadd(wk,datediff(wk,0,getdate()),6) GfxZ'VIn
Dateadd(mm,datediff(mm,0,getdate()),0) fa
jGZyd0:
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) tzWSA-Li
Dateadd(yy,datediff(yy,0,getdate()),0) Go`vfm"S
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) y|C(X
上面的SQL代码只是一个时间段 qTRsZz@
Dateadd(wk,datediff(wk,0,getdate()),-1) ,8S/t+H
Dateadd(wk,datediff(wk,0,getdate()),6) -/wtI
就是表示本周时间段. tVYF{3BhA
下面的SQL的条件部分,就是查询时间段在本周范围内的: n$MO4s8)
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) YFLZ %(
而在存储过程中 XO>KZV7)
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 6y-@iJ*ld;
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 4M=]wR;