SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 9''x'E=|
lLN5***47J
[y(<1]i-a
一、基础 T)MZ`dM
1、说明:创建数据库 ab>>W!r@!
CREATE DATABASE database-name LNF|mS\+D
2、说明:删除数据库 AhQsv.t
drop database dbname o=
&/;X
3、说明:备份sql server iy [W:<c7j
--- 创建 备份数据的 device .ViOf){U\
USE master =Iy khrS
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' XT{ukEvDR
--- 开始 备份 Mi7LyIu
BACKUP DATABASE pubs TO testBack 2]+f<Z[/
4、说明:创建新表 !~te&ccPE
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) \8/$ZEom
根据已有的表创建新表: #f }ORA
A:create table tab_new like tab_old (使用旧表创建新表) _o?[0E
B:create table tab_new as select col1,col2... from tab_old definition only _o{w<b&
5、说明:删除新表 rM)#}eZK!
drop table tabname 1j+RXb\<
6、说明:增加一个列 JF(&+\i<p
Alter table tabname add column col type #=czqZw
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 -"d&Ow7o
7、说明:添加主键: Alter table tabname add primary key(col) -x+K#T0Z
说明:删除主键: Alter table tabname drop primary key(col) =|IY[2^
8、说明:创建索引:create [unique] index idxname on tabname(col....) 4Vv$bbu+
删除索引:drop index idxname W4]jx]
注:索引是不可更改的,想更改必须删除重新建。 g.COKA
9、说明:创建视图:create view viewname as select statement b21@iW
删除视图:drop view viewname .]ZMxDZ
10、说明:几个简单的基本的sql语句 /v7o!D1G
选择:select * from table1 where 范围 J@o$V- KK
插入:insert into table1(field1,field2) values(value1,value2) A<[BR*n
删除:delete from table1 where 范围 5XinZ~
更新:update table1 set field1=value1 where 范围 7?qRz
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! sYd)r%%AU
排序:select * from table1 order by field1,field2 [desc] d1u6*&@lf
总数:select count as totalcount from table1 0E/16@6=
求和:select sum(field1) as sumvalue from table1 kNv/L$oG
平均:select avg(field1) as avgvalue from table1 h8:5[;e
最大:select max(field1) as maxvalue from table1 EOG&Xa
最小:select min(field1) as minvalue from table1 T49^
II{"6YI>
xkfW^r
HA3d9`
11、说明:几个高级查询运算词 ~jMfm~
U]
av{}U
M6z$*?<
A: UNION 运算符 /`b`ai8`8
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 yu$xQ~ o
B: EXCEPT 运算符 tWITr
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 DB.)/(zWQ
C: INTERSECT 运算符 ~iU@ns|g\
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 M+Eg{^ q`
注:使用运算词的几个查询结果行必须是一致的。 ?d@zTAI
12、说明:使用外连接 ""x>-j4
A、left outer join: Frum@n
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 G(MLq"R6U
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 ApqNV
B:right outer join: diD[/&k#kh
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 @hOT<
Uo
C:full outer join: {@
Z%6%'9
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 *&$2us0%%
k;!}nQ&
6U%F
mE @
二、提升 +lw*/\7
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) [Ee <SB{
法一:select * into b from a where 1<>1 J6g:.jsK!
法二:select top 0 * into b from a \OK"r-IO
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) |/~ISB
insert into b(a, b, c) select d,e,f from b; 3(=QY)
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) jDCf]NvOPM
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 e6_`
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. k1Sr7|
4、说明:子查询(表名1:a 表名2:b) {{ +8oRzY
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) SfUbjs@a
5、说明:显示文章、提交人和最后回复时间 Q=F^Y f
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b iB3C.wd-
6、说明:外连接查询(表名1:a 表名2:b) 6(V"xjK
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c /gq\.+'{
7、说明:在线视图查询(表名1:a ) "hLmwz|a
select * from (SELECT a,b,c FROM a) T where t.a > 1; ~otV'= /my
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 /9SNXjfbt
select * from table1 where time between time1 and time2 M b(hdS90
select a,b,c, from table1 where a not between 数值1 and 数值2 YAYwrKt
9、说明:in 的使用方法 A\QrawBp0l
select * from table1 where a [not] in ('值1','值2','值4','值6') =$WDB=i
10、说明:两张关联表,删除主表中已经在副表中没有的信息 ?xb2jZ/0X
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) tW"s^r=95
11、说明:四表联查问题: lfyij[6q+
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 ..... ![/ QW
12、说明:日程安排提前五分钟提醒 QA#
7T3|
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 XrN]}S$N
13、说明:一条sql 语句搞定数据库分页 vfOG(EkG.?
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 T,5(JP(h3
14、说明:前10条记录 *DPKV$
select top 10 * form table1 where 范围 R/kfbV-b
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) m";?B1%x
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 'Jl3%axR
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 15"[MX A
(select a from tableA ) except (select a from tableB) except (select a from tableC) oZ!+._9
17、说明:随机取出10条数据 eNFZD1mS
select top 10 * from tablename order by newid() qHC/)M#L
18、说明:随机选择记录 xHWD1>
select newid() Tu-I".d+
19、说明:删除重复记录 %p
tw=Ju
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ts;C:.X
20、说明:列出数据库里所有的表名 b0yNc:
select name from sysobjects where type='U' "In$|A\?E
21、说明:列出表里的所有的 <gx"p#JbZ
select name from syscolumns where id=object_id('TableName') g/`z.?
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 Lr<?eWdCwJ
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 rwY{QBSf
显示结果: Z]=9=S|
.4
type vender pcs ,<<HkEMS
电脑 A 1 &|c] U/_w
电脑 A 1 >on' y+
光盘 B 2 q]OgT4ly
光盘 A 2 xQ\S!py-
手机 B 3 E9:p A5H-j
手机 C 3 mM-7
jz
23、说明:初始化表table1 T*zy^we
TRUNCATE TABLE table1 yrV]I(Xe
24、说明:选择从10到15的记录 <<+Hs/ ]
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc bXK$H=S Bz
2hE+Om^n
UszR. Z
XMm(D!6
三、技巧 vL~j6'
1、1=1,1=2的使用,在SQL语句组合时用的较多 +*KDtqZjk
"where 1=1" 是表示选择全部 "where 1=2"全部不选, S<"`9r)av
如: ~ ]^<*R
if @strWhere !='' +V/m V7FK
begin }BLT2]y0
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 'kk
B>g7B
end J3AS"+]
else cT3 s{k
begin tk'3Q 1L
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' G?v]|wdI
end q{RT~,%
我们可以直接写成 o3>D~9
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere CUa`#
2、收缩数据库 6cbIs_g
--重建索引 -mqTlXM
DBCC REINDEX CB>O%m[1
DBCC INDEXDEFRAG 7A46?kfu
--收缩数据和日志 J)_IfbY
DBCC SHRINKDB yMBFw:/o
DBCC SHRINKFILE WkK.ON^
3、压缩数据库 %!p/r`
dbcc shrinkdatabase(dbname) 6D1tRo
4、转移数据库给新用户以已存在用户权限 {b90c'8?a
exec sp_change_users_login 'update_one','newname','oldname' i-31Cxb
go p$bR M`R&s
5、检查备份集 ;Ak 6*Sr
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' dJUI.!hv;
6、修复数据库 `&qeSEs\
ALTER DATABASE [dvbbs] SET SINGLE_USER ?\Lf=[
GO c9axzg
UA
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK n]J;BW&Av
GO ,)P6fa/
ALTER DATABASE [dvbbs] SET MULTI_USER K 6HH_T
GO =B tmi
7、日志清除 `#>JRQ=
SET NOCOUNT ON \>(S?)6
DECLARE @LogicalFileName sysname, $Qq5Fx9kU
@MaxMinutes INT, \C;F5AO
@NewSize INT ]6TX)1
USE tablename -- 要操作的数据库名 J)a^3>
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 -
u'5xn7
@MaxMinutes = 10, -- Limit on time allowed to wrap log. L$s;tJ
@NewSize = 1 -- 你想设定的日志文件的大小(M) h|Udw3N1L
-- Setup / initialize i`HXBq!|w
DECLARE @OriginalSize int .GNl31f0
SELECT @OriginalSize = size pPtw(5bH
FROM sysfiles +*P;Vb6 D
WHERE name = @LogicalFileName yB,{:kq7D
SELECT 'Original Size of ' + db_name() + ' LOG is ' + yZ57uz
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + lO5*n|Ic,
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' \A/??8cgXs
FROM sysfiles e8$OV4X
WHERE name = @LogicalFileName {B+{2;Zk
CREATE TABLE DummyTrans ICB'?yZ,
(DummyColumn char (8000) not null) qW'5Zk
DECLARE @Counter INT, %[7<GcWl
@StartTime DATETIME, WbDD9ZS
@TruncLog VARCHAR(255) c;1Xu1
SELECT @StartTime = GETDATE(), )Qx&m}
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ^ G@o} Z
DBCC SHRINKFILE (@LogicalFileName, @NewSize) ZsepTtY
EXEC (@TruncLog) f1}b;JJTsv
-- Wrap the log if necessary. 8n Oent0a
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired {\zB'SNq
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 9Z;"9$+M
AND (@OriginalSize * 8 /1024) > @NewSize M8iI e:{ c
BEGIN -- Outer loop. coFQu ;i
SELECT @Counter = 0 osW"b"_f
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) a gM I$
BEGIN -- update /J` ZO$
INSERT DummyTrans VALUES ('Fill Log') 8lcB.M
DELETE DummyTrans e x`mu E
SELECT @Counter = @Counter + 1 >ISN2Kn
END u[2B0a
EXEC (@TruncLog) `#w`-
END @F%_{6h
SELECT 'Final Size of ' + db_name() + ' LOG is ' + !BikqTM
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + b<?A
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' @I,:(<6
FROM sysfiles Ve\=By-a|
WHERE name = @LogicalFileName $7JWA9#N!
DROP TABLE DummyTrans ums*EKjs97
SET NOCOUNT OFF d
,!sZ&v
8、说明:更改某个表 'Xw>?[BB
exec sp_changeobjectowner 'tablename','dbo' +_E96`P
9、存储更改全部表 tOf18V{a
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch R2!_)Rpf
@OldOwner as NVARCHAR(128), vaOCH*}h
@NewOwner as NVARCHAR(128) Ci?A4q$.
AS Y<oDv`aZ0
DECLARE @Name as NVARCHAR(128) T~(AXwaJ
DECLARE @Owner as NVARCHAR(128) S6pvbaMZ
DECLARE @OwnerName as NVARCHAR(128) yM-3nwk
DECLARE curObject CURSOR FOR Oe:_B/l
select 'Name' = name, FfR%@
V'
'Owner' = user_name(uid) H`028^CH$
from sysobjects )>~d`_$dt
where user_name(uid)=@OldOwner U>jLh57
order by name \:D'u<8E
OPEN curObject ~*,e &I
FETCH NEXT FROM curObject INTO @Name, @Owner 1#2B1&
WHILE(@@FETCH_STATUS=0) M~k2Y$}R
BEGIN Fi*j}4F1
if @Owner=@OldOwner H(k-jAO,
begin Kx@;LRY#
set @OwnerName = @OldOwner + '.' + rtrim(@Name) 1l*O;J9By
exec sp_changeobjectowner @OwnerName, @NewOwner jVhfpS[
end cKbsf^R[e
-- select @name,@NewOwner,@OldOwner eLc@w<yB
FETCH NEXT FROM curObject INTO @Name, @Owner
/i
END `lA[-x~
close curObject / %:%la%
deallocate curObject z3X:.%
GO a'm\6AW2)
10、SQL SERVER中直接循环写入数据 v<wR`7xG
declare @i int
Y;[#~3CA
set @i=1 Udbz;^(
while @i<30 +rA:/!b)Y
begin 3A5:D#
insert into test (userid) values(@i) Cvf^3~q
set @i=@i+1 >UUT9:,plA
end ;$= GrR
小记存储过程中经常用到的本周,本月,本年函数 'HdOW[3o
Dateadd(wk,datediff(wk,0,getdate()),-1) t9yjfyk9W
Dateadd(wk,datediff(wk,0,getdate()),6) iAAlld1
Dateadd(mm,datediff(mm,0,getdate()),0) s.oh6wz
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) '5BM*4,:O
Dateadd(yy,datediff(yy,0,getdate()),0) 540,A,>:tb
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) |N/Wu9w$
上面的SQL代码只是一个时间段 v%6mH6V
Dateadd(wk,datediff(wk,0,getdate()),-1) :n t\uwh
Dateadd(wk,datediff(wk,0,getdate()),6) g9$P J:
就是表示本周时间段. |zaYIVE[
下面的SQL的条件部分,就是查询时间段在本周范围内的: e//q`?ys
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ,\cO>y@
而在存储过程中 `aw5"ns^V
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) YPY'[j(p`n
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) b=-LQkcZhK