SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 `dZ|}4[1
`vSsgG
u|c+w)a
一、基础 -Me\nu8(RF
1、说明:创建数据库 A.b#r[
CREATE DATABASE database-name ^xwFjQXx
2、说明:删除数据库 (Wqhuw!u
drop database dbname (YOgQ)},
3、说明:备份sql server i]z
i[Zo$
--- 创建 备份数据的 device h(-&.Sm")H
USE master Q/9b'^UJ
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' [}p.*U_nw
--- 开始 备份 @gc"-V*-/
BACKUP DATABASE pubs TO testBack EoeEg,'~F
4、说明:创建新表 EiUV?Gvz
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) P$Q&xN<#)
根据已有的表创建新表: ~aG-^BAS
A:create table tab_new like tab_old (使用旧表创建新表) (Nahtx!/9
B:create table tab_new as select col1,col2... from tab_old definition only hd;I x%tq>
5、说明:删除新表 rzHa&:Y
drop table tabname F e.*O`
6、说明:增加一个列
P+0xi
Alter table tabname add column col type [4j;FN Fa
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 v3Yj2LSqx
7、说明:添加主键: Alter table tabname add primary key(col) ise}> A!t
说明:删除主键: Alter table tabname drop primary key(col) ,0bM*qob
8、说明:创建索引:create [unique] index idxname on tabname(col....) MVdx5,t
删除索引:drop index idxname :N}KScS|Wa
注:索引是不可更改的,想更改必须删除重新建。 eZi<C}z
9、说明:创建视图:create view viewname as select statement (&,R1dLo
删除视图:drop view viewname .)w0C%]
10、说明:几个简单的基本的sql语句 `uHpj`EU
选择:select * from table1 where 范围 G
m! ]
插入:insert into table1(field1,field2) values(value1,value2) Tt|6N*b'
删除:delete from table1 where 范围 *
U4:K@y
更新:update table1 set field1=value1 where 范围 sBnPS[Oo
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! beE%%C]X
排序:select * from table1 order by field1,field2 [desc] K~-XDLh5Nu
总数:select count as totalcount from table1 @`D6F;R
求和:select sum(field1) as sumvalue from table1 s_!Z+D$K
平均:select avg(field1) as avgvalue from table1 ~x:]ch|
最大:select max(field1) as maxvalue from table1 -;$/<
最小:select min(field1) as minvalue from table1 =1\wZuK#
.<%M8rcj
ud D[hPJd
59J9V3na
11、说明:几个高级查询运算词 UAZ&*{MM^
hJsC
\ C,^
4
G[hU4L
A: UNION 运算符 Yur)_m
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 @/L. BfTz
B: EXCEPT 运算符 |$2N$6\SP
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 J
*?_SnZ
C: INTERSECT 运算符 S45>f(!
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 5i#w:O\cz
注:使用运算词的几个查询结果行必须是一致的。 ^^l"brPa
12、说明:使用外连接 z_@zMLs
A、left outer join: FaE orQ
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 g"S+V#R
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 d
A{Jk
B:right outer join: T(^8ki
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 gq3OCA!cX
C:full outer join: GuvF
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 |LE++t*X~
GQq'~Lr5
LB7I`W
二、提升 v^fOT5\
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) lG>e6[Wc
法一:select * into b from a where 1<>1 ^\jX5)2{
法二:select top 0 * into b from a W%K8HAP "
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) `|Z@UPHzG
insert into b(a, b, c) select d,e,f from b; '/g+;^_cB
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) zqr%7U
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 D
;$+] 2
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. Zb;$ZUWQX
4、说明:子查询(表名1:a 表名2:b) C&N4<2b
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) g8A{aHb1}
5、说明:显示文章、提交人和最后回复时间 !13
/+ u
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b u#k,G`
6、说明:外连接查询(表名1:a 表名2:b) AiK4t-
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c BrMp_M
7、说明:在线视图查询(表名1:a ) | V,jd
select * from (SELECT a,b,c FROM a) T where t.a > 1; ~j#6 goKn
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 [(EH
select * from table1 where time between time1 and time2 %MZDm&f>Kk
select a,b,c, from table1 where a not between 数值1 and 数值2 O \8G~V
5"
9、说明:in 的使用方法 Ia:puks=
select * from table1 where a [not] in ('值1','值2','值4','值6') mIEaWE;E"
10、说明:两张关联表,删除主表中已经在副表中没有的信息 9R"N#w.U]
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) <L/vNP
11、说明:四表联查问题: sNmC#,
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 ..... \'tz|
12、说明:日程安排提前五分钟提醒 $'{`i5XB
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 <D;Q8
13、说明:一条sql 语句搞定数据库分页 6-14Htsk6
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 E/$@ud|l"
14、说明:前10条记录 LE80`t>M#
select top 10 * form table1 where 范围 *1S.9L
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) *Ne2l`!1m
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) }SN44 di(
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 =M{CZm
(select a from tableA ) except (select a from tableB) except (select a from tableC) } %CbZ/7&
17、说明:随机取出10条数据 T-2p`b}hW
select top 10 * from tablename order by newid() o\;"|O}
18、说明:随机选择记录 / !
select newid() 0*/ r'
19、说明:删除重复记录 !_H8Q}a
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) |SukiXJZF
20、说明:列出数据库里所有的表名
He-Ja
select name from sysobjects where type='U' UJ)M:~O
21、说明:列出表里的所有的 O8~U<'=*
select name from syscolumns where id=object_id('TableName') JX$NEq(
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 (g2r\hI
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 NF(IF.8G
显示结果: XAxI?y[c
type vender pcs `m; "I
电脑 A 1 Q[Sd
电脑 A 1 @TPgA(5NR
光盘 B 2 $0S#d@v}
光盘 A 2 "q>I?UcZ
手机 B 3 gXLZ) >+A+
手机 C 3 \{=`F`oB=
23、说明:初始化表table1 xgqv2s>L
TRUNCATE TABLE table1 uQtk|)T E
24、说明:选择从10到15的记录 <bXWkj
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc S]%U]
Dw/Gha/
\R>5F\ 0
DEp%\sj?
三、技巧 lJ] \
1、1=1,1=2的使用,在SQL语句组合时用的较多 `NWgETf^#
"where 1=1" 是表示选择全部 "where 1=2"全部不选, IL2Gsj)M
如: O-!fOdX8_k
if @strWhere !='' Nw>T$RzS
begin Nk7e iQ
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere MD
?F1l"}%
end | ]!Ky[P
else $x_52 j\j
begin LVFsd6:h
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' uyRA`<&w
end 7}tZ?vD
我们可以直接写成 s!;VUr\
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere pg}+lYGP
2、收缩数据库 .UhBvHH
--重建索引 ZDkD%SCy
DBCC REINDEX ,dj*p,J
DBCC INDEXDEFRAG CVSsB:H6e
--收缩数据和日志 s@)"IdSA(
DBCC SHRINKDB EfBVu
DBCC SHRINKFILE Ril21o! j
3、压缩数据库 &Wz`>qYL*
dbcc shrinkdatabase(dbname) BUA6(
4、转移数据库给新用户以已存在用户权限 n:^"[Le
exec sp_change_users_login 'update_one','newname','oldname' 5ih"Nds[H
go !ga(L3vf
5、检查备份集 :OQ:@Yk
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' $,QpSK`9i
6、修复数据库 E4v_2Q
-w
ALTER DATABASE [dvbbs] SET SINGLE_USER #u<oEDQ
GO 51ajE2+X&
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK U_}A{bFG
GO |`Oa/\U
ALTER DATABASE [dvbbs] SET MULTI_USER Y9@dZw%2
GO Ij6Wz.*
7、日志清除 _[{:!?-?
SET NOCOUNT ON ,7fc41O3V
DECLARE @LogicalFileName sysname, '=Kof1
@MaxMinutes INT, C/CfjRzd
@NewSize INT #?$'nya*u
USE tablename -- 要操作的数据库名 X#kjt)W
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 I~]Q55
@MaxMinutes = 10, -- Limit on time allowed to wrap log. (XG[_
@NewSize = 1 -- 你想设定的日志文件的大小(M) Q+!0)pG5#
-- Setup / initialize Oa\ `;
DECLARE @OriginalSize int ]zvVY:v
SELECT @OriginalSize = size +>!B(j\gx
FROM sysfiles 5e/qgI)M5
WHERE name = @LogicalFileName l@tyg7CwY
SELECT 'Original Size of ' + db_name() + ' LOG is ' + MCi` TXr
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + ZH;y>Z
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' kToVBU$
FROM sysfiles @`kiEg'Q
WHERE name = @LogicalFileName +i`Q 7+d
CREATE TABLE DummyTrans -#S)}NEn
(DummyColumn char (8000) not null) CEX}`I*-
DECLARE @Counter INT, Nr]8P/[~
@StartTime DATETIME, )pZekh]v
@TruncLog VARCHAR(255) te\h?H
SELECT @StartTime = GETDATE(), 7dlKdKH
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' N7~)qqb
DBCC SHRINKFILE (@LogicalFileName, @NewSize) rZ!Yi*? f
EXEC (@TruncLog) jI{~s]Q
-- Wrap the log if necessary. /[20e1 w!
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired &weY8\HD
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) (
*9Ip
AND (@OriginalSize * 8 /1024) > @NewSize X@yr$3vC
BEGIN -- Outer loop. e:$7^Y,U/
SELECT @Counter = 0 /Oggt^S
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) %7NsBR!y
BEGIN -- update W<rTq0~$?
INSERT DummyTrans VALUES ('Fill Log') 2GiUPtO&Gj
DELETE DummyTrans FM9X}%5nu9
SELECT @Counter = @Counter + 1 ;Y@!:p-H
END >St.c
EXEC (@TruncLog) 4#@W;'
END UKKSc>D1
SELECT 'Final Size of ' + db_name() + ' LOG is ' + sw41wj
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + tIyuzc~U
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' CrNwALx
FROM sysfiles ];pf
WHERE name = @LogicalFileName p- "Z'$A`
DROP TABLE DummyTrans Vedyy\TU
SET NOCOUNT OFF $*AC>i\
8、说明:更改某个表 FI1THzW4J
exec sp_changeobjectowner 'tablename','dbo' GJIWG&C03
9、存储更改全部表 %_b^!FR
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch {*?sVAvj
@OldOwner as NVARCHAR(128), R,x> $n
@NewOwner as NVARCHAR(128) GP[6nw_'^
AS <DeKs?v
DECLARE @Name as NVARCHAR(128) Ue{vg$5||
DECLARE @Owner as NVARCHAR(128) 2/yXY_L
DECLARE @OwnerName as NVARCHAR(128) e$Xq
DECLARE curObject CURSOR FOR C5PmLiOHY>
select 'Name' = name, 4-7kS85
'Owner' = user_name(uid) d)04;[=
from sysobjects fjIcB+Z
where user_name(uid)=@OldOwner _e?q4>B)c
order by name ]DC;+;8Jc
OPEN curObject I!$jYY2
FETCH NEXT FROM curObject INTO @Name, @Owner Ic[}V0dk
WHILE(@@FETCH_STATUS=0) 49+ >f
BEGIN p{ @CoOn
if @Owner=@OldOwner mVv\bl?<
begin XMN?;Hj>
set @OwnerName = @OldOwner + '.' + rtrim(@Name) 6o=qJ`m[?
exec sp_changeobjectowner @OwnerName, @NewOwner xH_A@hf;
end Lh8bQH
-- select @name,@NewOwner,@OldOwner =zeFK_S!
FETCH NEXT FROM curObject INTO @Name, @Owner )%iRZ\`f
END F>~ xzc
close curObject <`R|a *
deallocate curObject yaH
Trh%
GO -ajM5S=d*
10、SQL SERVER中直接循环写入数据 IPl@ DH
declare @i int ]Q6+e(:~ZH
set @i=1 .e`,{G(5q7
while @i<30 ?Yq J.F;
begin w`c0a&7
insert into test (userid) values(@i) r-RCe3%g%
set @i=@i+1 w=f0*$ue+w
end |Z`M*.d+
小记存储过程中经常用到的本周,本月,本年函数 @gt)P4yE
Dateadd(wk,datediff(wk,0,getdate()),-1) \8;Qv
Dateadd(wk,datediff(wk,0,getdate()),6) V19e>
Dateadd(mm,datediff(mm,0,getdate()),0) Qw24/DJK
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) s<A*[
Dateadd(yy,datediff(yy,0,getdate()),0) X}oj_zsy;^
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) rQ9*J
上面的SQL代码只是一个时间段 )!'n&UxPo$
Dateadd(wk,datediff(wk,0,getdate()),-1) D4< -8
Dateadd(wk,datediff(wk,0,getdate()),6) ss?]
就是表示本周时间段. m"lE&AM64p
下面的SQL的条件部分,就是查询时间段在本周范围内的: UF@IBb}0
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) #*!+b
而在存储过程中 (Ij0AeJ#
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) ![^EsgEB*
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) z 0~j