SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 H59}d
oKH
pQ%~u3
-q1vB8gjj
一、基础 Yqz(@( %
1、说明:创建数据库 kKr|PFz
CREATE DATABASE database-name am.}2QZU
2、说明:删除数据库 1A\Jh3;Q
drop database dbname N,U<.{T=A
3、说明:备份sql server MmH[ 7R
--- 创建 备份数据的 device 7U68|\fI!
USE master %^){Z,}M}
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' $g&,$7}O_
--- 开始 备份 `Wq4k>J}*
BACKUP DATABASE pubs TO testBack D?;8bI%"
4、说明:创建新表 l>K z5re^
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) P 1`X<A
根据已有的表创建新表: sYnf
# '
A:create table tab_new like tab_old (使用旧表创建新表) Zwj\Hz.
B:create table tab_new as select col1,col2... from tab_old definition only YEfa8'7R
5、说明:删除新表 q#9JJWSs
drop table tabname ELoE-b)Cb
6、说明:增加一个列 ZuZe8&
Alter table tabname add column col type *Y!'3|T
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 N8a+X|3]0
7、说明:添加主键: Alter table tabname add primary key(col) VZ"W_U,
说明:删除主键: Alter table tabname drop primary key(col) W,&z:z>
8、说明:创建索引:create [unique] index idxname on tabname(col....) b37F;"G
删除索引:drop index idxname y4* }E
注:索引是不可更改的,想更改必须删除重新建。 <aps)vF
9、说明:创建视图:create view viewname as select statement 4+&4
删除视图:drop view viewname }^r=(
10、说明:几个简单的基本的sql语句 3[8F:I0UL
选择:select * from table1 where 范围 UeNa
插入:insert into table1(field1,field2) values(value1,value2)
MZp`
删除:delete from table1 where 范围 9_yO6)`
更新:update table1 set field1=value1 where 范围 u&MlWKCi
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! )Los\6PRn
排序:select * from table1 order by field1,field2 [desc] Y rq-(
总数:select count as totalcount from table1 \-(.cj)?
求和:select sum(field1) as sumvalue from table1 7/*Q?ic
平均:select avg(field1) as avgvalue from table1 T=lir%q
最大:select max(field1) as maxvalue from table1 Q1tZ]Q.6
最小:select min(field1) as minvalue from table1 W 2/`O?
L~F"
E-Z6qZ^
6zuWG0t
11、说明:几个高级查询运算词 b[+G+V
V%k #M
jSMvZJX3n
A: UNION 运算符 =.vc={_?
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 piq1cV
B: EXCEPT 运算符 aLr\Uq,83
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 ,S"a ,}8
C: INTERSECT 运算符 r$3{1HXc
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 U)IW6)q
注:使用运算词的几个查询结果行必须是一致的。 ?-"xP'#
12、说明:使用外连接 lwS6"2q
A、left outer join: 6=ukR=]v
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 cYBv}ylw}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 FPM@%U
B:right outer join: =#WoeWFW*
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 MUo}Qi0K
C:full outer join: [x}]sT`#a
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 P'Q|0lB
jKFypIZ4
lMpjE
二、提升 _Wcr'*7
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Af8&PhyrU
法一:select * into b from a where 1<>1 6{2LV&T=u
法二:select top 0 * into b from a M%dJqwH5{
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) F$TNYZ
insert into b(a, b, c) select d,e,f from b; #qL?;Zh0S
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) wps`2`z
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 Jw%0t'0Zi
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. XXuIWIhm
4、说明:子查询(表名1:a 表名2:b) (1.E9+MquU
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) VCOz?Y*
5、说明:显示文章、提交人和最后回复时间 4]c.mDo[T
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ~jK'n4
6、说明:外连接查询(表名1:a 表名2:b) d*7nz=0&$
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c WfbG }%&J
7、说明:在线视图查询(表名1:a ) r>fx55dw
select * from (SELECT a,b,c FROM a) T where t.a > 1; Z i-)PK^
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 sGx"ja+
select * from table1 where time between time1 and time2 <~9z.v7
select a,b,c, from table1 where a not between 数值1 and 数值2 HKA7|z9{
9、说明:in 的使用方法 w+ZeVZv!r
select * from table1 where a [not] in ('值1','值2','值4','值6') YE<_a;yh1
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Jb'l.xN
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) p|mt2oDjw
11、说明:四表联查问题: HWe?vz$4"
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 ..... 9
yH/5'
12、说明:日程安排提前五分钟提醒 @4h{#
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 dZnAdlJ
13、说明:一条sql 语句搞定数据库分页 J!?hajw7N
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 (_^pX
14、说明:前10条记录 sjBP#_lW
select top 10 * form table1 where 范围 @eQIwz
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) /[f9Z:>V
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 6?-vj2,
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 fVCpG~&t
(select a from tableA ) except (select a from tableB) except (select a from tableC) ]Lg$p
17、说明:随机取出10条数据 ]MjQr0&M
select top 10 * from tablename order by newid()
<^j,jX
18、说明:随机选择记录 8 VMe#41
select newid() xA|72!zk0P
19、说明:删除重复记录 ><odBM-
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) V+7x_>!&)
20、说明:列出数据库里所有的表名 P;R`22\3
select name from sysobjects where type='U' =]r<xON%S
21、说明:列出表里的所有的 qaK9E@l
select name from syscolumns where id=object_id('TableName') P]*,955*)
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 M7R.?nk
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 `Q d_Gu,M
显示结果: >;Er[Rywr
type vender pcs #K1VPezN
电脑 A 1 R
+H0+omj
电脑 A 1 p|Po##E}g^
光盘 B 2 9Xw(|22
光盘 A 2 }:UNL^e?
手机 B 3 U8_<?Hd
手机 C 3 E0XfM B]+
23、说明:初始化表table1 Q[t|+RNKv2
TRUNCATE TABLE table1 OZ2gIK
24、说明:选择从10到15的记录 uveby:dh
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc s)HLFdis@
0zD[mt
?[#nh@mI
qx3@]9
三、技巧 xij`Mr
1、1=1,1=2的使用,在SQL语句组合时用的较多 =aM(r6 C
"where 1=1" 是表示选择全部 "where 1=2"全部不选, P@Oq'y[
如: 3z$9jN/<u
if @strWhere !='' 0fhz7\a^_<
begin ?kfLOJQ:I
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere d>j`|(\
end ttlFb]zZh
else z;? 32K
begin kWVaHZr
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 9ohaU
end SGi(Zkc
我们可以直接写成 r(g:b
^S
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere _]~`t+W'DJ
2、收缩数据库 CmyCne
--重建索引 !g=2U`j^
DBCC REINDEX |KF_h^
DBCC INDEXDEFRAG JHN{vB
--收缩数据和日志 B}n
tD
DBCC SHRINKDB 6_`9
4+
DBCC SHRINKFILE ] B
ZSW
3、压缩数据库 9?8Yf(MC%u
dbcc shrinkdatabase(dbname) M^f+R'Q3
4、转移数据库给新用户以已存在用户权限 0q6$KP}q
exec sp_change_users_login 'update_one','newname','oldname' Bf
{h\>q
go mUFg(;ya
5、检查备份集 -:mT8'.F-
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' [wHGt?R
6、修复数据库 /G}TPXA
ALTER DATABASE [dvbbs] SET SINGLE_USER Tf[o'=2
GO ~.-o*
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK b#ih=qE
GO "Dr8}g:X
ALTER DATABASE [dvbbs] SET MULTI_USER NJ6*
7Cd
GO wGRMv1|lIu
7、日志清除 tWD5Yh>.?$
SET NOCOUNT ON jIpc^iu`,
DECLARE @LogicalFileName sysname, (yh zjN~
@MaxMinutes INT, >"}z
% #
@NewSize INT 1|U8DK
USE tablename -- 要操作的数据库名 U]!~C 1cmw
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 <d&9`e1Hc
@MaxMinutes = 10, -- Limit on time allowed to wrap log. :=CRsQAn
@NewSize = 1 -- 你想设定的日志文件的大小(M) UBj"m<
-- Setup / initialize h^Bp^V5#
DECLARE @OriginalSize int ~yrEB:w`_
SELECT @OriginalSize = size HN7C+e4U~
FROM sysfiles )?L
WHERE name = @LogicalFileName nIWZo ~
SELECT 'Original Size of ' + db_name() + ' LOG is ' + jv<C#0E^
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + ;:2:f1_
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' >St]MS
FROM sysfiles &\w:jI44Bs
WHERE name = @LogicalFileName -pb>=@Yq
CREATE TABLE DummyTrans *Fb|iR
(DummyColumn char (8000) not null) y5oC|v7
DECLARE @Counter INT, bUcq
LV
@StartTime DATETIME, |3ob1/)p0
@TruncLog VARCHAR(255) h_xHQf
SELECT @StartTime = GETDATE(), {Z~5#<t
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' OE{PP9eh
DBCC SHRINKFILE (@LogicalFileName, @NewSize) },tN{()
EXEC (@TruncLog) GxFmw:
-- Wrap the log if necessary. zG"*B_l}+
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired R:[IH2F s
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) LBCat=d<
AND (@OriginalSize * 8 /1024) > @NewSize &@dMIJK"(
BEGIN -- Outer loop. 295U<
SELECT @Counter = 0 8Q wn
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ?WD JWp%
BEGIN -- update E:!qncL:
INSERT DummyTrans VALUES ('Fill Log') D(z}c,
DELETE DummyTrans ;XUiV$
SELECT @Counter = @Counter + 1 0\/cTNN
END 1I^uq>r
EXEC (@TruncLog) DrY5Q&S
END jE.yT(+lW
SELECT 'Final Size of ' + db_name() + ' LOG is ' + =x QLf4>
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + FF} A_ZFY
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 1kDr;.m%
FROM sysfiles <aScA`\B#
WHERE name = @LogicalFileName I7[F,xci
DROP TABLE DummyTrans H0 %;t
SET NOCOUNT OFF Iz^lED
8、说明:更改某个表 8i?Hh?Mf}
exec sp_changeobjectowner 'tablename','dbo' S8S<>W
9、存储更改全部表 6Z:swgi6&
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch li`4&<WGC
@OldOwner as NVARCHAR(128), Bkq3-rX\
@NewOwner as NVARCHAR(128) 7y/Pch
AS *
11|P
DECLARE @Name as NVARCHAR(128) @/DHfs 4O
DECLARE @Owner as NVARCHAR(128) 0r |mg::'
DECLARE @OwnerName as NVARCHAR(128) r/ G6O
DECLARE curObject CURSOR FOR <}vult^
select 'Name' = name, Uiz#QGt
'Owner' = user_name(uid) Ie<`WU K
from sysobjects @y+Hb@ >.
where user_name(uid)=@OldOwner \/
9s<
order by name FlqE!6[[
OPEN curObject 0</]Jo%
FETCH NEXT FROM curObject INTO @Name, @Owner ~A X@o-WU
WHILE(@@FETCH_STATUS=0) G\I DgPj`
BEGIN L'KgB=5K&i
if @Owner=@OldOwner ?F!='6D}b
begin +hW^wqk/.
set @OwnerName = @OldOwner + '.' + rtrim(@Name) LY? `+/
exec sp_changeobjectowner @OwnerName, @NewOwner uPvE;E_
end /HiRbwQK#
-- select @name,@NewOwner,@OldOwner <O]TM-h
FETCH NEXT FROM curObject INTO @Name, @Owner ;`(l)X+7
END 2rG;j52))a
close curObject *It`<F|
deallocate curObject +)cjW"9
GO XXe?@w2{
10、SQL SERVER中直接循环写入数据 Hyj<Fqr!.
declare @i int $
M8ZF(W
set @i=1 "F.0(<4)
while @i<30 4XCy>;4u
begin VEtdp*ot
insert into test (userid) values(@i) )j^~=Sio.
set @i=@i+1 jJ B+UF=
end iFi6,V*PRt
小记存储过程中经常用到的本周,本月,本年函数 o/6'g)r*
Dateadd(wk,datediff(wk,0,getdate()),-1) -*QxZiKD
Dateadd(wk,datediff(wk,0,getdate()),6) W-ctx"9DS
Dateadd(mm,datediff(mm,0,getdate()),0) lls-Nir%
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) foi@z9
Dateadd(yy,datediff(yy,0,getdate()),0) a7_ &;
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) UmC_C[/n?
上面的SQL代码只是一个时间段 XLeQxp=
Dateadd(wk,datediff(wk,0,getdate()),-1) zt23on2
Dateadd(wk,datediff(wk,0,getdate()),6) kg@Okz N%
就是表示本周时间段. R54wNm@
下面的SQL的条件部分,就是查询时间段在本周范围内的: nJ]oApb/-
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) y!,Ly_x$@
而在存储过程中 oOj7y>Nm
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 2L!wbeTb;
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) &/}reE*