SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 p{A}pnjf
tC?=E#3V
%8~Q!=*Iq
一、基础 x&sI=5l
1、说明:创建数据库 ))kF<A_MK
CREATE DATABASE database-name `>Tu|3%\
2、说明:删除数据库 f"G-
drop database dbname CvSIV7zYo
3、说明:备份sql server ?Ea;J0V
--- 创建 备份数据的 device 5zJj]A
USE master ^FmU_Q0
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' >eQr<-8
--- 开始 备份 ^|~mlY@w
BACKUP DATABASE pubs TO testBack #AkV/1Y
4、说明:创建新表 h0--B]f@
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) @}p2aV59
根据已有的表创建新表: $4kH3+WJ
A:create table tab_new like tab_old (使用旧表创建新表) 8I20*#
B:create table tab_new as select col1,col2... from tab_old definition only GG064zPq7
5、说明:删除新表 'VyM{:8
drop table tabname Bs+(L [Z
6、说明:增加一个列 ok^d@zI
Alter table tabname add column col type =uk0@hy9b
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 NL=|z=q
7、说明:添加主键: Alter table tabname add primary key(col) C
(n+SY^
说明:删除主键: Alter table tabname drop primary key(col) Mv544>:
8、说明:创建索引:create [unique] index idxname on tabname(col....) EC2+`HJ"
删除索引:drop index idxname EKEjv|_)
注:索引是不可更改的,想更改必须删除重新建。 $EZN1\
9、说明:创建视图:create view viewname as select statement ZX!r1*c
6
删除视图:drop view viewname $n^MD_1!
10、说明:几个简单的基本的sql语句 @bM2{Rh:
选择:select * from table1 where 范围 o+`6LKg;
插入:insert into table1(field1,field2) values(value1,value2) l&4,v
删除:delete from table1 where 范围 <U5wB]]
更新:update table1 set field1=value1 where 范围 uzmk6G
v
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! ]w T 7*( Y
排序:select * from table1 order by field1,field2 [desc] S:4crI
总数:select count as totalcount from table1 `e9$,h|4
求和:select sum(field1) as sumvalue from table1 Q?ahr~qo
平均:select avg(field1) as avgvalue from table1 B[=(#W
最大:select max(field1) as maxvalue from table1 4a0:2 kIKa
最小:select min(field1) as minvalue from table1 [${
QzO
MObt,[^W
'j^xbikr
]V %.I_
11、说明:几个高级查询运算词 WARb"8Kg
\P} p5k[
3&u_A?;
A: UNION 运算符 _{t9 x\=
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 M` q?Fk
B: EXCEPT 运算符 E J$36
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 {,*"3O:\:
C: INTERSECT 运算符 >_rha~
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 N8qDdr9p?c
注:使用运算词的几个查询结果行必须是一致的。 )vmA^nU>
12、说明:使用外连接 P71 (
A、left outer join: IdYzgDH
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 *D o/+[Ae
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 ur
:i)~wXn
B:right outer join: ?88[|;b3
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 .)}@J5P)
C:full outer join: Q~R
~xz
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Q9I
j\HbA"
&PkLp4mQ
p
raaY}}
二、提升 }I3gU
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) G+B~Ix-
法一:select * into b from a where 1<>1 M02uO`Y9
法二:select top 0 * into b from a a#mNE*Dg
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) F'g Vzf
insert into b(a, b, c) select d,e,f from b; ]\/tVn.'
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ]| N3eu
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 ^~{$wVGa
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. a+hd(JX0~
4、说明:子查询(表名1:a 表名2:b) +k
dT(7
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) (P&4d~)m
5、说明:显示文章、提交人和最后回复时间 rl9.]~
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ?$f)&O
6、说明:外连接查询(表名1:a 表名2:b) x~.:64
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c wi9DhVvc 0
7、说明:在线视图查询(表名1:a ) &]
\X]p
select * from (SELECT a,b,c FROM a) T where t.a > 1; u0P)7~%
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 .sQ=;w/ZA
select * from table1 where time between time1 and time2 [M.f-x:
select a,b,c, from table1 where a not between 数值1 and 数值2 k>t)g-,2
9、说明:in 的使用方法 "ZTTg>r
select * from table1 where a [not] in ('值1','值2','值4','值6') USFDy
10、说明:两张关联表,删除主表中已经在副表中没有的信息 )o\jJrVDf
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) pO8ePc@=D
11、说明:四表联查问题: U4 13?Pe
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 ..... 2o>)7^9|#<
12、说明:日程安排提前五分钟提醒 83;NIE;
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 Pnd`=%w%]
13、说明:一条sql 语句搞定数据库分页 ;<UW A.
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 `ptj?6N-
14、说明:前10条记录 n@ w^V
select top 10 * form table1 where 范围 sAg Kg=)
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) P&Pj>!T5
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) mv5n4mav
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 yLsz8j-QJ
(select a from tableA ) except (select a from tableB) except (select a from tableC) V5p=
mmnA,
17、说明:随机取出10条数据 :>p8zG
select top 10 * from tablename order by newid() Lm@vXgMD
18、说明:随机选择记录 9f\/\L
select newid() W8lx~:v
19、说明:删除重复记录 5,)Qw
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) LH:i| I
20、说明:列出数据库里所有的表名 (`? y2n)~W
select name from sysobjects where type='U' /y^7p9Z`
21、说明:列出表里的所有的 F:6SPY
y
select name from syscolumns where id=object_id('TableName') =]-j;#'&
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 6a;v&5
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 nFe%vu8a
显示结果: %,hV[[ @.
type vender pcs
aR,}W\6M
电脑 A 1 TYI7<-Mp:[
电脑 A 1 [`]4P&
光盘 B 2 $9S(_xdI&
光盘 A 2
9CCkqB/
手机 B 3 )5|I_PXB
手机 C 3 q~o,WZG
23、说明:初始化表table1 +za8=`2o
TRUNCATE TABLE table1 XQ4G)
24、说明:选择从10到15的记录 S1/`th
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc w[6J
`
: Sq?a0!S
%Th>C2\
@iEA:?9uX
三、技巧 4A9{=~nwT
1、1=1,1=2的使用,在SQL语句组合时用的较多 Xn~I=Ml d
"where 1=1" 是表示选择全部 "where 1=2"全部不选, $.Q$`/dF
如: e@{i
if @strWhere !='' 0oEOre3^%
begin 191&_*Xb
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere #GJ
dZ
end E*?<KZe"
else \6;=$f/?t
begin L28*1]\Jh
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ;Jd3u
-
end 6\61~u ~
我们可以直接写成 o!4!"O'E
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere (%O@r!{
2、收缩数据库 +:3*
--重建索引 gIA@l`"
DBCC REINDEX V'w@rc\XN
DBCC INDEXDEFRAG w&xDOyW]
--收缩数据和日志 O$IjNx
DBCC SHRINKDB m^x6>9,
DBCC SHRINKFILE 5wUUx#
3、压缩数据库 ^<X@s1^#
dbcc shrinkdatabase(dbname) g#]wLm#
4、转移数据库给新用户以已存在用户权限 .(Qx{r$
exec sp_change_users_login 'update_one','newname','oldname' ,RN:^5 p
go "QvmqI>
5、检查备份集 QMEcQV>
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' (|wz7AY2
6、修复数据库 R0oKbs{
ALTER DATABASE [dvbbs] SET SINGLE_USER :{(w3<i
GO $<ld3[l i
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK ~^+0
GO W
d0NT@
ALTER DATABASE [dvbbs] SET MULTI_USER \P1=5rP
GO WoxwEi1~0
7、日志清除 0j C3fT!n
SET NOCOUNT ON M`6y@<
DECLARE @LogicalFileName sysname, h5yzwj:C?
@MaxMinutes INT, :UJ a&$)
@NewSize INT wCk~CkC?
USE tablename -- 要操作的数据库名 P]z[v)}
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 f@co<iA
@MaxMinutes = 10, -- Limit on time allowed to wrap log. b~-%c_
@NewSize = 1 -- 你想设定的日志文件的大小(M) <9>vO,n
-- Setup / initialize ]:34kE}e5
DECLARE @OriginalSize int kp\\"+,VC
SELECT @OriginalSize = size ["}rk
FROM sysfiles T)\"Xj
WHERE name = @LogicalFileName k? Xc
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 3OM2Y_
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + vd6l7"0/
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' hR5_+cuIp
FROM sysfiles "*O4GPj
WHERE name = @LogicalFileName 2S' {!A
CREATE TABLE DummyTrans _j_x1.l
(DummyColumn char (8000) not null) 'H7x L
DECLARE @Counter INT, d,$d~alY
@StartTime DATETIME, ,.gQ^^+=
@TruncLog VARCHAR(255) 'EFyIVezg9
SELECT @StartTime = GETDATE(), } G<rt
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ?aW^+3i
DBCC SHRINKFILE (@LogicalFileName, @NewSize) <LRey%{q
EXEC (@TruncLog) WMMO5_Mz
-- Wrap the log if necessary. Y?534l)j
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired Mc!Xf[
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) )#F]G$51r
AND (@OriginalSize * 8 /1024) > @NewSize q64k7<C,
BEGIN -- Outer loop. 16SOIT
SELECT @Counter = 0 /s];{m|>
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) >&!RWH9*q
BEGIN -- update vy,&N^P
INSERT DummyTrans VALUES ('Fill Log') $)H@|<K
DELETE DummyTrans dJ?XPo"Cm=
SELECT @Counter = @Counter + 1
y<C<_2
END cQ:"-!ff
EXEC (@TruncLog) gT/@dVV
END RmrL^asg
SELECT 'Final Size of ' + db_name() + ' LOG is ' + -)vEWn$3<
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 2YuN~-
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' %&
_V0R\k
FROM sysfiles exdx\@72
WHERE name = @LogicalFileName nADX0KI
DROP TABLE DummyTrans !`bio cA
SET NOCOUNT OFF ,7XtH>2s
8、说明:更改某个表 SR*wvQnOx
exec sp_changeobjectowner 'tablename','dbo' ?|e'Gbb_
9、存储更改全部表 (Z5##dS3
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @E.k/G!~Nb
@OldOwner as NVARCHAR(128), 1
y}2+Kk
@NewOwner as NVARCHAR(128) ! Q<>3xZ
AS "7>>I D
DECLARE @Name as NVARCHAR(128) f&D]anf33
DECLARE @Owner as NVARCHAR(128) 8}w6z7e|{
DECLARE @OwnerName as NVARCHAR(128) w:'dhr':
DECLARE curObject CURSOR FOR Ap{}^
select 'Name' = name, G|8%qd
'Owner' = user_name(uid) .WQ<jZt>
from sysobjects ,<DB&&EV8
where user_name(uid)=@OldOwner (z$r :p
order by name ~ d^<_R
OPEN curObject oUQ07z\C
FETCH NEXT FROM curObject INTO @Name, @Owner @Mvd'.r<;
WHILE(@@FETCH_STATUS=0) i
ZL2p>
BEGIN c"!lwm3b
if @Owner=@OldOwner 09o~9z0
begin }IEbyb
set @OwnerName = @OldOwner + '.' + rtrim(@Name) aCV4AyG
exec sp_changeobjectowner @OwnerName, @NewOwner L!_ZY
end ;v
-- select @name,@NewOwner,@OldOwner )5fQ$<(Z
FETCH NEXT FROM curObject INTO @Name, @Owner HyiFy7j
END .}')f;jH5<
close curObject !se0F.K
deallocate curObject W0jZOP5_.$
GO 7kKy\W
10、SQL SERVER中直接循环写入数据 L}#0I+Ml7
declare @i int 0N=X74
set @i=1 Nx#4W1B[`H
while @i<30 YC]L)eafo`
begin H;aYiy
insert into test (userid) values(@i) N)% ;jh:T
set @i=@i+1 drwgjLC+
end 3\;27&~gV
小记存储过程中经常用到的本周,本月,本年函数 z'p:gv]
Dateadd(wk,datediff(wk,0,getdate()),-1) Da$r `
Dateadd(wk,datediff(wk,0,getdate()),6) g/UaYCjM
Dateadd(mm,datediff(mm,0,getdate()),0) X}P$emr7
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))
>ds%].$-\
Dateadd(yy,datediff(yy,0,getdate()),0) 9t@:4O
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) ~](fFa{
上面的SQL代码只是一个时间段 OPBt$Ki
Dateadd(wk,datediff(wk,0,getdate()),-1) UueD(T;p
Dateadd(wk,datediff(wk,0,getdate()),6) z=&z_}M8
就是表示本周时间段. \RQ='/H*
下面的SQL的条件部分,就是查询时间段在本周范围内的: e$c?}3E!z
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) (SVWdgb
而在存储过程中 -oz`"&%
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) ^BZkHAp
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) bU 63X={