SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 G5aieD.#
(UvM@]B
q[W
0 N>
一、基础 Q&=w_Wc
1、说明:创建数据库 jun_QiU:2
CREATE DATABASE database-name _Wq
2、说明:删除数据库 cacr=iX
drop database dbname %'7lbpy,f
3、说明:备份sql server WR yaKM
--- 创建 备份数据的 device vMt/u?oB
USE master <vh/4
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' kJzoFFWo$
--- 开始 备份 6qoyiT%P&
BACKUP DATABASE pubs TO testBack [] `&vWZ
4、说明:创建新表 _'>oXQJ
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) ``Dq
根据已有的表创建新表: 2ZMb<b4H
A:create table tab_new like tab_old (使用旧表创建新表) 9c#+qH
B:create table tab_new as select col1,col2... from tab_old definition only pU%n]]qF
5、说明:删除新表 #W'HR
drop table tabname >
BY&,4r
6、说明:增加一个列 XJ` ]ga
Alter table tabname add column col type Z/0fXn})
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
(SDr!!V<
7、说明:添加主键: Alter table tabname add primary key(col) uU <=d
说明:删除主键: Alter table tabname drop primary key(col) I E&!YP(U(
8、说明:创建索引:create [unique] index idxname on tabname(col....) Vp*KfS]
删除索引:drop index idxname F6OpN"UM'
注:索引是不可更改的,想更改必须删除重新建。 m)v"3ib
9、说明:创建视图:create view viewname as select statement Nj
xoTLI
删除视图:drop view viewname Ba*,-i3ZK
10、说明:几个简单的基本的sql语句 )ufg9"\
选择:select * from table1 where 范围 luuX2Mx>o
插入:insert into table1(field1,field2) values(value1,value2) "2P&X
删除:delete from table1 where 范围 WEQ1 Seq
更新:update table1 set field1=value1 where 范围 +HeTtFo{M
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! V4P;
5[
排序:select * from table1 order by field1,field2 [desc] Gh}LlX!w
总数:select count as totalcount from table1 Y*>#T
求和:select sum(field1) as sumvalue from table1 =Ja] T~0A
平均:select avg(field1) as avgvalue from table1 (\a]"g,]v
最大:select max(field1) as maxvalue from table1 W<$Z=(_v
最小:select min(field1) as minvalue from table1 t2"O
BuWHX>H
C8e
!H
9S7kUl{
11、说明:几个高级查询运算词 5rRN-
h[1MtmNw
6c"0})p
A: UNION 运算符 +5o8KYV
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 =Z+nz^'b
B: EXCEPT 运算符 $8xl#SqH
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 zb}9%.U
C: INTERSECT 运算符 Z!@~>i
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 *-q"3D`
注:使用运算词的几个查询结果行必须是一致的。 Nq` C.&
12、说明:使用外连接 P 8>d6;o($
A、left outer join: xA1hfe.9
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 |e?64%l5P
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 3'qJ/*]9
B:right outer join: -/cZeQDPb
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 ##;Er47@^
C:full outer join: 65p?Igb
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 `'c_=<&n
J(%0z:exs
\"^w'ng
二、提升 =fve/_Q~
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) sqJSSNt
法一:select * into b from a where 1<>1
\ 3?LqJ
法二:select top 0 * into b from a U,gti,IX^
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) Ph}|dGb
insert into b(a, b, c) select d,e,f from b; %D8ZO0J7H
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 7L@K _ZJ
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 W4e5Rb4~f"
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ryCI>vJz
4、说明:子查询(表名1:a 表名2:b) Y$Y_fjd_
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) &)vC;$vD`
5、说明:显示文章、提交人和最后回复时间 jhu&&==\f
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b CkD#/
6、说明:外连接查询(表名1:a 表名2:b) ;SaX;!`39+
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Y&_&s7z
7、说明:在线视图查询(表名1:a ) NqEA4C
select * from (SELECT a,b,c FROM a) T where t.a > 1; ?jt}*q>X]
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 XC "'Q+
select * from table1 where time between time1 and time2 E3l> 3
select a,b,c, from table1 where a not between 数值1 and 数值2 _~tEw.fM5
9、说明:in 的使用方法 0=q;@OIf
select * from table1 where a [not] in ('值1','值2','值4','值6') *U$!I?
10、说明:两张关联表,删除主表中已经在副表中没有的信息 2aB^WY'tC
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) B`o]*"xkB
11、说明:四表联查问题: 0i|oYaC
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 ..... rBTeb0i?
12、说明:日程安排提前五分钟提醒 C2xL1`
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 )+"'oY$]}
13、说明:一条sql 语句搞定数据库分页 |t)}VM%
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 !x>%+&c>k
14、说明:前10条记录 iNWo"=J
select top 10 * form table1 where 范围 \uq/x^?yo
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) !$Tw^$n
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) n;p:=\uN
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 T<@ cd|`
(select a from tableA ) except (select a from tableB) except (select a from tableC) Fxqp-}:
17、说明:随机取出10条数据 n?ctLbg
select top 10 * from tablename order by newid() |'+eMl
18、说明:随机选择记录 #8bsxx!s
select newid() o fMY,~w
19、说明:删除重复记录 U
uM$~qf/K
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ;)I'WQ]Q
20、说明:列出数据库里所有的表名 NeBsv= [-
select name from sysobjects where type='U' jhX[fT1m
21、说明:列出表里的所有的 @81Vc<dJ
select name from syscolumns where id=object_id('TableName') >'xGp7}y
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 gEhN3(
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 @]c(V%x
显示结果: hj$e|arB
type vender pcs 8kOKwEX
电脑 A 1 N0w`!<y:c
电脑 A 1 HCJ>X;(`f?
光盘 B 2 f%)zg(YlO
光盘 A 2 $GQ-(/
手机 B 3 ;k86"W
手机 C 3 za9)Q=6FD
23、说明:初始化表table1 )VK }m9Ae
TRUNCATE TABLE table1 Za7q$7F7Bc
24、说明:选择从10到15的记录
kWb2F7m
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc B_l{<
m6yIR6H
8W+gl=C~
JwRF(1_sM
三、技巧 eo!zW
1、1=1,1=2的使用,在SQL语句组合时用的较多 J~iBB~x.
"where 1=1" 是表示选择全部 "where 1=2"全部不选, p!V>XY'N^
如: M9f?q.Bv
if @strWhere !='' !k(_PM
begin {(#%N5%
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere Hb(B?!M)
end ^*= 85iyo
else N+)?$[
begin 0hn-FH-XE
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' Q2];RS3.
end qcJft'>F
我们可以直接写成 Op?OruT[
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere $1zvgep
2、收缩数据库 4E[!,zvl
--重建索引 BH@)QVs-
DBCC REINDEX cx$Gic:4
DBCC INDEXDEFRAG 1b>C<\
--收缩数据和日志 #4h+j%y[H
DBCC SHRINKDB p|/j4@-h
DBCC SHRINKFILE NHgjRPz"
3、压缩数据库 n*'<uKpM
dbcc shrinkdatabase(dbname) Grz 3{U
4、转移数据库给新用户以已存在用户权限 0 Hw-59MK
exec sp_change_users_login 'update_one','newname','oldname' iH2n.M
"
go m&0"<V!H/B
5、检查备份集 "SoHt]%#
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 5ZPzPUa8~
6、修复数据库 Q2%QLM:.,
ALTER DATABASE [dvbbs] SET SINGLE_USER O:/yAc`
GO 0l#)fJo
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK qxJQPz
GO 9H]Lpi^OH
ALTER DATABASE [dvbbs] SET MULTI_USER =}fd6ea(o
GO @C-dG7U.P
7、日志清除 Dli^2hD
SET NOCOUNT ON Ld,5iBiO:
DECLARE @LogicalFileName sysname, B 2.q3T
@MaxMinutes INT, ;#)mLsl
@NewSize INT x2#qg>`l
USE tablename -- 要操作的数据库名 s&{Qdf
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 Lj%{y.Rj
@MaxMinutes = 10, -- Limit on time allowed to wrap log. q 'a
@NewSize = 1 -- 你想设定的日志文件的大小(M) "?GebA
-- Setup / initialize ZDYJhJ.
DECLARE @OriginalSize int Zz |MIGHm
SELECT @OriginalSize = size Bl1Z4` 3
FROM sysfiles 9kY[j2,+
WHERE name = @LogicalFileName 8g7,2f/ }
SELECT 'Original Size of ' + db_name() + ' LOG is ' + kK~IwA
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + ?vGffMm
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' do>"[RO
FROM sysfiles ?68uS;
WHERE name = @LogicalFileName :Ze+%d=
CREATE TABLE DummyTrans :y,v&Kk#T
(DummyColumn char (8000) not null) d(*fy}
DECLARE @Counter INT, W {.78Zi9K
@StartTime DATETIME, hvt@XZT
@TruncLog VARCHAR(255) m>e3vu
SELECT @StartTime = GETDATE(), 6vf\R*D|A
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' *NSlo^R-[
DBCC SHRINKFILE (@LogicalFileName, @NewSize) pY^9l3y^
EXEC (@TruncLog) l t]B#, '
-- Wrap the log if necessary. F X1ZG!
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired qturd7
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) }yEoEI`
AND (@OriginalSize * 8 /1024) > @NewSize #S+Z$DQD
BEGIN -- Outer loop. L8vOB I7N
SELECT @Counter = 0 m^\TUj
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 4`2$_T$F
BEGIN -- update P8gXCX!>U
INSERT DummyTrans VALUES ('Fill Log') gKb0)4 AK
DELETE DummyTrans 88a<{5
:z
SELECT @Counter = @Counter + 1 e}cnX`B
END Hwe)Tsh e
EXEC (@TruncLog) H.J5i~s
END ?&h3P8
SELECT 'Final Size of ' + db_name() + ' LOG is ' + =ziy`#fm,
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + *R`MMm
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' PG)_L.7rJ
FROM sysfiles a~^Srj!}x
WHERE name = @LogicalFileName =O{~Q3z@s
DROP TABLE DummyTrans 'CS.p!Z\
SET NOCOUNT OFF NyI;v=
8、说明:更改某个表 c! H 9yk
exec sp_changeobjectowner 'tablename','dbo' "t&{yBQ0u
9、存储更改全部表 KLt%[$CTi
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch ij&p4
@OldOwner as NVARCHAR(128), tnW;E\cR
@NewOwner as NVARCHAR(128)
H=zN[MU
AS ~j,TVY
DECLARE @Name as NVARCHAR(128) C'9 1d7E
DECLARE @Owner as NVARCHAR(128) +3bfD
DECLARE @OwnerName as NVARCHAR(128) ? Ekq6uz\)
DECLARE curObject CURSOR FOR H^CilwD158
select 'Name' = name, RyRqH:p)3
'Owner' = user_name(uid) ~' =lou
from sysobjects ":d*dl
where user_name(uid)=@OldOwner /?zW<QUI
order by name j+748QAhh
OPEN curObject bGh0<r7R
FETCH NEXT FROM curObject INTO @Name, @Owner %7`d/dgR
WHILE(@@FETCH_STATUS=0) Wm6dQQ;Bj
BEGIN )hL^+Nn bR
if @Owner=@OldOwner !J.rM5K
begin 5urE
set @OwnerName = @OldOwner + '.' + rtrim(@Name) Y%vP#>h
exec sp_changeobjectowner @OwnerName, @NewOwner ixOw=!@
end wt7.oKbW
-- select @name,@NewOwner,@OldOwner Xn7[n
FETCH NEXT FROM curObject INTO @Name, @Owner +6%7CC 6
END l6B.6
'4)w
close curObject T~Y g5J
deallocate curObject W<gD6+=8
GO TJ2/?p\x
10、SQL SERVER中直接循环写入数据 iiwpSGFl]
declare @i int uaQ&&5%%J
set @i=1 h1%y:[_
while @i<30 ?\yB)Nd y
begin \!X?zR_
insert into test (userid) values(@i) j3P RAe
set @i=@i+1 Rx.
rj~
end tm xP Oe
小记存储过程中经常用到的本周,本月,本年函数 %^^h) Wy}
Dateadd(wk,datediff(wk,0,getdate()),-1) rr>~WjZ3
Dateadd(wk,datediff(wk,0,getdate()),6) S.fXHtSx
Dateadd(mm,datediff(mm,0,getdate()),0) ti;%BS
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) _XN~@5elrC
Dateadd(yy,datediff(yy,0,getdate()),0) Kpu<rKP`
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) j-P^Zv};u
上面的SQL代码只是一个时间段 FYeEG
Dateadd(wk,datediff(wk,0,getdate()),-1) [u\CD sX
Dateadd(wk,datediff(wk,0,getdate()),6) px&=((Z7>
就是表示本周时间段. H*qD: N
下面的SQL的条件部分,就是查询时间段在本周范围内的: iy
3DX|]
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) [oHOHp/V
而在存储过程中 Pw#2<>
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) M-91
JOt~
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ~M[>m~8