SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 \6j^kY=
J_#R 87
0_<Nc/(P
一、基础 QBE@(2G}C
1、说明:创建数据库 =
Rc"^oS
CREATE DATABASE database-name `kBnSi o~
2、说明:删除数据库 Ln#a<Rx.E7
drop database dbname ,i`h
x,
Rg
3、说明:备份sql server W,hWOO
--- 创建 备份数据的 device IvBGpT"(I
USE master *8g<R
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ]Nk!4"
--- 开始 备份 s'a= _cN
BACKUP DATABASE pubs TO testBack ;\)=f6N
4、说明:创建新表 3-wD^4)O,
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) {0jIY
根据已有的表创建新表: ?H;{~n?
A:create table tab_new like tab_old (使用旧表创建新表) cHvF* A
B:create table tab_new as select col1,col2... from tab_old definition only T.?k>Ak
5、说明:删除新表 (
76{2
drop table tabname -
HOnB=
6、说明:增加一个列 j^u[F"
Alter table tabname add column col type f$xhb3Qn
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 +/'<z
7、说明:添加主键: Alter table tabname add primary key(col) )q?$p9
说明:删除主键: Alter table tabname drop primary key(col) z)L}ECZh9
8、说明:创建索引:create [unique] index idxname on tabname(col....) -]"T^wib
删除索引:drop index idxname 2g`[u|
注:索引是不可更改的,想更改必须删除重新建。 ~5#)N{GbY
9、说明:创建视图:create view viewname as select statement ?s{C//
删除视图:drop view viewname X}JWf<=q
10、说明:几个简单的基本的sql语句 9k2,3It
选择:select * from table1 where 范围 KXBL
eR&^
插入:insert into table1(field1,field2) values(value1,value2) R ZcH+?7
删除:delete from table1 where 范围 bcJ@-i0V
更新:update table1 set field1=value1 where 范围 8cr NOZS6
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! saK;[&I*
排序:select * from table1 order by field1,field2 [desc] eO4)|tW
总数:select count as totalcount from table1 z&'f/w8
求和:select sum(field1) as sumvalue from table1 f~gSJ<t4
平均:select avg(field1) as avgvalue from table1 Z$2L~j"=!
最大:select max(field1) as maxvalue from table1 ]if;A ) '
最小:select min(field1) as minvalue from table1 {/UhUG
(.^8^uc7X
[ #]jC[
z%2w(&1
11、说明:几个高级查询运算词 Kmry=`=A
LcUlc)YH5
)bW<8f2
A: UNION 运算符 X=_Z(;<&
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 (wL3 +
B: EXCEPT 运算符 X5E
'*W
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 i-13~Dk
C: INTERSECT 运算符 !UNNjBBP7
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 ^8742.
注:使用运算词的几个查询结果行必须是一致的。 ?V+wjw
12、说明:使用外连接 P>htQ
A、left outer join: V/H@vKN2
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 wc[c N+p
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 T Oy7?;|=
B:right outer join: 8W{~wg`
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 @\!!t{y
C:full outer join: F.KrZ3%4iB
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 {!K;`I[]v
q) _r3
#S?c ;3-
二、提升 'Oy5e@G+?
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) |3@=CE7G
法一:select * into b from a where 1<>1 i[=C_+2
法二:select top 0 * into b from a .~<]HAwq
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) )fCMITq.|
insert into b(a, b, c) select d,e,f from b; f'_S1\
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) F$ {4X /9n
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 SI_?~Pf3k
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. nVTM3Cz
4、说明:子查询(表名1:a 表名2:b) I@PJl
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) ,8`O7V{W
5、说明:显示文章、提交人和最后回复时间 #:W%,$9\P
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b A}4t9|/K6
6、说明:外连接查询(表名1:a 表名2:b) C"No5r'K3
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c h6FgS9H
7、说明:在线视图查询(表名1:a ) :@e\'~7sH
select * from (SELECT a,b,c FROM a) T where t.a > 1; %c0z)R~
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 2?1}ZXr
select * from table1 where time between time1 and time2 22IYrk
select a,b,c, from table1 where a not between 数值1 and 数值2 |uQ[W17^N
9、说明:in 的使用方法 ^Jtl;Q
select * from table1 where a [not] in ('值1','值2','值4','值6') LhKY}R
10、说明:两张关联表,删除主表中已经在副表中没有的信息 I=b'j5c
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) <UK5eVQn
11、说明:四表联查问题: J{H?xc
o
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 ..... 0Q3 YN(
12、说明:日程安排提前五分钟提醒 ?H0m<jO8~
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 >Fh#DmQ
13、说明:一条sql 语句搞定数据库分页 8_awMVAy
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 ?d,M.o{0]
14、说明:前10条记录 5ZUy:
select top 10 * form table1 where 范围 65"uD7;
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) J" wKR y
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) {e6KJ@H6
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 &G=0
(select a from tableA ) except (select a from tableB) except (select a from tableC) =BW9/fG
17、说明:随机取出10条数据 GWh|FEqUbf
select top 10 * from tablename order by newid() iE+6UK
18、说明:随机选择记录 yjv&4pIc1
select newid() E@]sq A
19、说明:删除重复记录 ]W|RtdF3.N
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) TPqvp|~2
20、说明:列出数据库里所有的表名 aZxO/b^j
select name from sysobjects where type='U' r$?Vx_f`Q
21、说明:列出表里的所有的
w[{*9
select name from syscolumns where id=object_id('TableName') p.aE
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 KE#$+,?
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 QB9A-U<J
显示结果: w%I8CU_}.
type vender pcs N.n1<
电脑 A 1 H\f/n`@,G
电脑 A 1 m|`VJ0
光盘 B 2
I9Om#m
光盘 A 2 P09,P
手机 B 3 hqWbp*
手机 C 3 /[L)tj7B
23、说明:初始化表table1 lG
<yJ~{
TRUNCATE TABLE table1 `
Rsl]
GB
24、说明:选择从10到15的记录 hJ4S3b
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc r?]%d!
K
/8qB~J*
l"?]BC~
E6JV}`hSk
三、技巧 ;6zPiaDQ
1、1=1,1=2的使用,在SQL语句组合时用的较多 ?AT(S
"where 1=1" 是表示选择全部 "where 1=2"全部不选, A_]D~HH
如: y*
rY~U#3
if @strWhere !='' TL]bY'%
begin `_0)kdu
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere YjL
t&D:IZ
end W`5a:"Vg
else [Q=4P*G}X
begin m"q/,}DR
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' }eI`Qg
end pbFYiu+
我们可以直接写成 e-jw^
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere CY5w$E
2、收缩数据库 wU.'_SBfB
--重建索引 xLZMpP5c
DBCC REINDEX ` )]lUvR
DBCC INDEXDEFRAG tz3]le|ml
--收缩数据和日志 m.Twgin
DBCC SHRINKDB %L28$c3p
DBCC SHRINKFILE 4"z;CGE7
3、压缩数据库 r
/^'Xj'(
dbcc shrinkdatabase(dbname) `{%-*f^
4、转移数据库给新用户以已存在用户权限 h2AGEg'g2[
exec sp_change_users_login 'update_one','newname','oldname' 2>ys2:z
go RpU Lm1b
5、检查备份集 5W|u5AIw
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' DYkC'+TEX
6、修复数据库 hO%Y{Gg
ALTER DATABASE [dvbbs] SET SINGLE_USER we
}#Ru*
GO <TL])@da
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK $>|?k$(x
GO (%Ng'~J\|
ALTER DATABASE [dvbbs] SET MULTI_USER 1"M"h_4
GO y>%W;r)
7、日志清除 ]|t9B/()i
SET NOCOUNT ON /^~p~HKtx
DECLARE @LogicalFileName sysname, x}_rnf_
@MaxMinutes INT, .:T9pplq
@NewSize INT ( e0_RQ
USE tablename -- 要操作的数据库名 jm4)gmC
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 \3L$I-]m
@MaxMinutes = 10, -- Limit on time allowed to wrap log. iY}QgB< M
@NewSize = 1 -- 你想设定的日志文件的大小(M) |^>u<E5
-- Setup / initialize Z0{f
DECLARE @OriginalSize int oy`3r5g
SELECT @OriginalSize = size {a[Uv
FROM sysfiles l<s :%%CX
WHERE name = @LogicalFileName " S ?Km
SELECT 'Original Size of ' + db_name() + ' LOG is ' + _dJp
3D
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + ys/`{:w8p
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' gZ1N&/9;
FROM sysfiles F{kG
WHERE name = @LogicalFileName rA[nUJ,
CREATE TABLE DummyTrans JThk Wx
(DummyColumn char (8000) not null) !B0v<+;P8
DECLARE @Counter INT, Y=hPErw
@StartTime DATETIME, /j$$0F>s7
@TruncLog VARCHAR(255) b_q!>&c
SELECT @StartTime = GETDATE(), tsB.oDMP
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' Q3(hK<Qh;
DBCC SHRINKFILE (@LogicalFileName, @NewSize) d$4WK)U
EXEC (@TruncLog) ]~$c~*0g
-- Wrap the log if necessary. gv`%Z8u(
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired ]aREQ?ma&z
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) *X%?3"WH8
AND (@OriginalSize * 8 /1024) > @NewSize L,f^mX0<
BEGIN -- Outer loop. D`1I;Tb#
SELECT @Counter = 0 Ml'bZLwq
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) FpwlV}:
BEGIN -- update [SKP|`I>I
INSERT DummyTrans VALUES ('Fill Log') *oKgP8CF
DELETE DummyTrans IvPA|8(
SELECT @Counter = @Counter + 1 (MZ A
END MacL3f
EXEC (@TruncLog) [O.LUR;
END PY[Sz=[
SELECT 'Final Size of ' + db_name() + ' LOG is ' + /,=Wy"0TJ
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + \x3^
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' IiG4ib>)W
FROM sysfiles @>d&5}F_>{
WHERE name = @LogicalFileName `'dX/d
DROP TABLE DummyTrans @\#'oIc|
SET NOCOUNT OFF 1!K!oY
8、说明:更改某个表 HJnv'^yn
exec sp_changeobjectowner 'tablename','dbo' ]!n*V/g
9、存储更改全部表 hz&^_G6`
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch Y+|L3'H
@OldOwner as NVARCHAR(128), &z7N\n
@NewOwner as NVARCHAR(128) jI@bTS o
AS b>GqNf!
DECLARE @Name as NVARCHAR(128) I|Vk.,
DECLARE @Owner as NVARCHAR(128) N )b|
DECLARE @OwnerName as NVARCHAR(128) at_dmU2[7
DECLARE curObject CURSOR FOR JrY"J]/
select 'Name' = name, XHU<4l:kl
'Owner' = user_name(uid) R^n*
o
from sysobjects 8#[%?}tK
where user_name(uid)=@OldOwner AT2NC6{M
order by name T2c_vY
OPEN curObject J"m%q\'
FETCH NEXT FROM curObject INTO @Name, @Owner K8e4ax
WHILE(@@FETCH_STATUS=0) ]L5Z=.z&
BEGIN AJJ%gxqGq
if @Owner=@OldOwner ,Y78Q
begin >\^N\&
set @OwnerName = @OldOwner + '.' + rtrim(@Name) >[A65q'
exec sp_changeobjectowner @OwnerName, @NewOwner Om &{4a\
end dVY(V&p
-- select @name,@NewOwner,@OldOwner A>rW Go.{E
FETCH NEXT FROM curObject INTO @Name, @Owner EZgxSQaPH
END Pf^Ly97
close curObject [wXwKr
deallocate curObject /6Jy'"+'0
GO 4]|9!=\
10、SQL SERVER中直接循环写入数据 ~ wJ3AqNC?
declare @i int wj5qQ]WC
set @i=1 =R"Eb1
while @i<30 S)Ub/`f{s
begin )'/nS$\E:
insert into test (userid) values(@i) j\jL[hG_
set @i=@i+1 x
mrugNRg
end vTe$77n
小记存储过程中经常用到的本周,本月,本年函数 >*<6 zQf
Dateadd(wk,datediff(wk,0,getdate()),-1) +73=2.C0
Dateadd(wk,datediff(wk,0,getdate()),6) =:ya;k&
Dateadd(mm,datediff(mm,0,getdate()),0) `\WcF7
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ai<MsQQ:=
Dateadd(yy,datediff(yy,0,getdate()),0) Ppi- skT
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) q9g[+*9]$
上面的SQL代码只是一个时间段 V'f&JQA
Dateadd(wk,datediff(wk,0,getdate()),-1) rU2YMghE
Dateadd(wk,datediff(wk,0,getdate()),6) R
&1mo
就是表示本周时间段. 3.K{T
下面的SQL的条件部分,就是查询时间段在本周范围内的: Lk8W&|;0|
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) v"G%5pq*\
而在存储过程中 E)rOlh7
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) O,V6hU/ *
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) }]Gi@Nh|o