MSSQL常用语句(转)

7nkzDk5K,t _6K!q0下列语句部分是Mssql语句，不可以在access中使用。 　　

0Z*JJq*^K{
D&cZ O0SQL分类： 　友商社区 I+p?4XkV(f

4dxD/{
GCX8h^2b0    　DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
:z;d!P)CY9JPP0　　DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
*lIMi~-c2{0　　DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)　　友商社区
C%mUZ9Y [&I~
首先,简要介绍基础语句：　　友商社区@
a:}U+s
1、说明：创建数据库CREATE DATABASE database-name 　　友商社区FM4{*O?#d
2、说明：删除数据库drop database dbname　　友商社区%E/~n&Ee!j?
rP
3、说明：备份sql server　　友商社区&aK4g A)^2hM
--- 创建 备份数据的 deviceUSE master友商社区
u RH:JR"z$wJ
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'　　友商社区
\5E9_wNV
--- 开始 备份BACKUP DATABASE pubs TO testBack 　　
/g5~k L.XEs^O04、说明：
c G9lF3k)D {u0创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)　　友商社区s'};b5t-Z(s!{ y
根据已有的表创建新表：
N]O(N&Q.\8Z5~JS0A：create table tab_new like tab_old (使用旧表创建新表)友商社区yI1D#\B1a7dPc
B：create table tab_new as select col1,col2… from tab_old definition only　　友商社区.} Djy2BHJ DCd)W
5、说明：　　
+s0XU8@%TZb0删除新表：drop table tabname 　　友商社区3qM4Y ^,a,o2f
6、说明：　　友商社区:Z4T2o
J5a3y6}H6Q
增加一个列：Alter table tabname add column col type　　注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。　　
TbV9GTpg"f[yp'_'T07、说明：友商社区"^ Ul*U0o&^I*yz-O0A
　　添加主键：Alter table tabname add primary key(col)友商社区3pv P3iZ H k
　　说明：友商社区mU4tf2l~"X
　　删除主键：Alter table tabname drop primary key(col)　　友商社区o`)q1G-XG$@
8、说明：
9bFOY+N"m0　　创建索引：create [unique] index idxname on tabname(col….)友商社区
}} Y2G+\f0|8y
　　删除索引：drop index idxname　　注：索引是不可更改的，想更改必须删除重新建。　　
)xF!C5v$T}c:x09、说明：友商社区 F"Wo r%`/x
　　创建视图：create view viewname as select statement
Z;pN?Oyb_0　　删除视图：drop view viewname　　友商社区^(y8KI1E5W
10、说明：几个简单的基本的sql语句
1A&f7y| dw$ei0　　选择：select * from table1 where 范围友商社区/L'A&J%E5U4Ac
　　插入：insert into table1(field1,field2) values(value1,value2)友商社区K:Ed#_;Q1yYHW
　　删除：delete from table1 where 范围友商社区*}5fn5zH
　　更新：update table1 set field1=value1 where 范围
-{`@&i6tX lq0　　查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
SZ$OX+QLBS0　　排序：select * from table1 order by field1,field2 [desc]
Q.F LF:@
H0　　总数：select count * as totalcount from table1
z"dz3w%}0　　求和：select sum(field1) as sumvalue from table1
*P5}sA ym6G^0　　平均：select avg(field1) as avgvalue from table1友商社区.{0l)xgHk
　　最大：select max(field1) as maxvalue from table1友商社区M5~,cD7D-s*r
　　最小：select min(field1) as minvalue from table1　　
5N,kFNk`/}x011、说明：几个高级查询运算词
1LCo7]$BZ9jc!?Go0　　A： UNION 运算符
3J8gRN`}eR0　　UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
k6H^G4}_qZC&h+G0　　B： EXCEPT 运算符
/b zJ#hCA1k0　　EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
U
v5p%^(g0　　C： INTERSECT 运算符
U b+S)[V&T8c z0　　INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。　　注：使用运算词的几个查询结果行必须是一致的。　　友商社区S q`wTV


{4j'u(o:t1m7b6`012、说明：使用外连接
r i'G#`5y+JD&`0　　A、left outer join：
(G b*F`y&m0　　左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 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友商社区V(\V*TK;J%Zu*K*r ]
　　B：right outer join:友商社区0F3u'm~ We.V b,V
　　右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。友商社区}9CZ-uf)q1D,c)U
　　C：full outer join：友商社区4JoBX"B"kSO
　　全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。友商社区"?2UFz"[!H0`7g
　　其次，大家来看一些不错的sql语句友商社区1hB ~AhN@0Bb
　　1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
G0E"~3@-g2{9Kf"]o0　　法一：select * into b from a where 1<>1
h$IP8oms A0　　法二：select top 0 * into b from a
M j_)ze5D.l0　　2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b;
NPFi2V&IP1G&J0　　3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件　　例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..友商社区!N~)j'p7s
　　4、说明：子查询(表名1：a 表名2：b)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)友商社区 s!AV]9VO
　　5、说明：显示文章、提交人和最后回复时间select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b　　6、说明：外连接查询(表名1：a 表名2：b)select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
:?i-f"r K0　　7、说明：在线视图查询(表名1：a )select * from (SELECT a,b,c FROM a) T where t.a > 1;友商社区Nb] U'`i(H
　　8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 数值1 and 数值2
E"i_&bMl9l0　　9、说明：in 的使用方法select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
%W-l!Q![ GP{z0　　10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )友商社区
{UMScZ
　　11、说明：四表联查问题：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 .....友商社区f5|:^ ~/Lp
　　12、说明：日程安排提前五分钟提醒SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
M)R$T&\ c0　　13、说明：一条sql 语句搞定数据库分页select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
|3R [*v\+E4o0　　14、说明：前10条记录select top 10 * form. table1 where 范围友商社区d!fjIt
　　15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)友商社区6rvm(H!tE~
　　16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)
2uh2kc X w-`i$WK0　　17、说明：随机取出10条数据select top 10 * from tablename order by newid()友商社区4w6q0zhrM
　　18、说明：随机选择记录select newid()
Dst2Xts+b0　　19、说明：删除重复记录Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
Cw C3v2@y+C o:T0　　20、说明：列出数据库里所有的表名select name from sysobjects where type='U'
9EC s5?7s#Y0Y_"N8g(t0　　21、说明：列出表里的所有的select name from syscolumns where id=object_id('TableName')友商社区){3BGN$YI@
　　22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。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
n3dggfk0　　显示结果：type vender pcs友商社区NR3k-s;I1B0z9|}8K"N
电脑 A 1友商社区x Ex HD8u'Hz/Y
电脑 A 1友商社区
I&j"lh:H
光盘 B 2
t?$[?'exJ0t0光盘 A 2友商社区r$h;f5} HQe5QPe s
手机 B 3友商社区9F/t.QV4X8P4X
手机 C 3
IPNZ&F0　　23、说明：初始化表table1TRUNCATE TABLE table1友商社区.{%k ?A7ii1K
　　24、说明：选择从10到15的记录select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc友商社区s2@7odE@:~\g
　　随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
T5Au? S
b9CWA0　　对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：
gL q7HY.Ki"G2g0Randomize友商社区;C5vwY h
mN*J0p
友商社区$`"W'aOq
RNumber = Int(Rnd*499) +1友商社区!`9nGC&WkS1e
　友商社区 t(zX5Hk t|1MY
While Not objRec.EOF
)U't$xn1i~/e I0
du-wp_B0If objRec("ID") = RNumber THEN
,Sir&v)h __9[9n0友商社区*YfA-z j)`
... 这里是执行脚本 ...友商社区 e:M Ej"rU

wJVWf)g([2\ d%RT0end if友商社区"|Sd9v
P
友商社区1f%o:\5Xr9U@A
objRec.MoveNext友商社区3yd$W9Y1Idl

6Ng:V$\lFb1H\v0Wend 　　友商社区zU_(Q;RT~-l1X
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一个数据库内就包含了成千上万条记录。这时候不就死定了？ 　　
f|-E5j7A0采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：友商社区 XK8nv2~ CIx*vNXC
Randomize

Jq5Y"u8RY5d8[b0
)pE8H"h;lB0RNumber = Int(Rnd*499) + 1友商社区Ho0uc;tx.e-zP%{ E9r

cv"\9a[8U1\[0　
N6?y3PdTQAg0SQL = "SELECT * FROM Customers WHERE ID = " & RNumber友商社区5ufN F"u#Bt
友商社区 k;q$r)f F
　
6u?$cy#o6tS4|Y0set bjRec = ObjConn.Execute(SQL)
{)FZ}?*V3\F+Rx0
2y9lM:p*lVq*x+k0Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")友商社区8? Mi.x p7lf4Y6|
　　不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。友商社区-l.e&D fr$]$P*{o
　　再谈随机数　　友商社区FL ZF
JW
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。友商社区me cv wB^"Zo
　　为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：
U$Uw*?~tN4L0　　SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
M} [ O4gSk0　　假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：友商社区#H|| Es~0Z"h

g"G+R*}T"v'\1M }%G0SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
R"QT2yV1R9X Y2f8Uu0　　注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
5}aX)I*cR0　　随机读取若干条记录，测试过Access语法：
yHR!]]
B+IO:i,E0SELECT top 10 * From 表名 ORDER BY Rnd(id)
C
\DJa"q/_)B5_p0友商社区$A!jQ T^3N
Sql server:select top n * from 表名 order by newid()友商社区([*TK*q%[5vA P

B~I0o5Q7N]c:\0mysql select * From 表名 Order By rand() Limit n　　友商社区-Y^$[,_7dk3@:LR8N_
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)友商社区lgg%c}+`c'uW
　　语法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
M"v0Fx.{0　　使用SQL语句 用...代替过长的字符串显示
,LAhC+z#r,`(WS0　　语法：
_$T`pQ&DcA!N0　　SQL数据库：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
0T,_O&L3`H_0
p}"GI-F+w%L0　　Access数据库：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;友商社区t8g4zx_S#]
　　Conn.Execute说明　友商社区OfU"XNMS3O$[
　Execute方法　　
,h"F3Z(P9YO-L(n;{0该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：友商社区 `*~7]o[9U(~%n
　　1．执行SQL查询语句时，将返回查询得到的记录集。友商社区 qU#{ZO
用法为：　　Set 对象变量名=连接对象.Execute("SQL 查询语言")友商社区)RUm`$d0Bc?
　　Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。
`V3C~3w{4g*d3?0　　2．执行SQL的操作性语言时，没有记录集的返回。
5vX$b+M3_Xi1?8M Hy0此时用法为：　　连接对象.Execute "SQL 操作性语句" [, RecordAffected][, Option]　友商社区o*e?9Hz-w EC
　·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。
#Xz{Nk Z+[0　　·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。友商社区|lJ-c uS"W!o
　　·BeginTrans、RollbackTrans、CommitTrans方法　　这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；友商社区L yUu*AQ2mt{
CommitTrans用于提交所有的事务处理结果，即确认事务的处理。
2e&hL5]u&h0　　事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。
7B"d*} s/k@0
w4R`[${m0　　BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。

精妙SQL语句整理2007-01-08 13:08一、基础
'L;`*O*Ff4U0
~})sY'J Q.}7X&r01、说明：创建数据库
$P6QnK$MkY1L0CREATE DATABASE database-name
G._$Xy%p7?jxSC02、说明：删除数据库
:xGiS+Ky%E zp(zOu0drop database dbname
$v1U%A q"H+r.z(@03、说明：备份sql server
9r*J0w ovC0--- 创建 备份数据的 device
a*@.lt6X[#MN1g0USE master友商社区.`}&k/R q#k"Sy
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'友商社区F%cJz\
--- 开始 备份友商社区&r)s2F S0RB
BACKUP DATABASE pubs TO testBack
mc"m&D
?_0rmc%S04、说明：创建新表
0}4NY%WcqQXb
f"]0create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
+M*Vw~z7U0根据已有的表创建新表：友商社区D#CV-I)o,B
A：create table tab_new like tab_old (使用旧表创建新表)友商社区?kU(?X@A*t8_
B：create table tab_new as select col1,col2… from tab_old definition only友商社区h{
h^5m2l
5、说明：删除新表友商社区Z{9f X^,T2_
drop table tabname友商社区#B$s:FF"E?*ZX5y
D
6、说明：增加一个列友商社区R!Qs8m![&y7^
Alter table tabname add column col type
%K:j~2q^`(hl)w*P'~0注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。友商社区 c%yH'l+?{1bpL3N
7、说明：添加主键： Alter table tabname add primary key(col)友商社区$qJ5Z\jnyQ%B,D
说明：删除主键： Alter table tabname drop primary key(col)友商社区4@m-_:N~9V7Z+{5o
8、说明：创建索引：create [unique] index idxname on tabname(col….)友商社区)t-{%v+V3]9\PR[e!c\c
删除索引：drop index idxname友商社区Ew lLm$wzQ)Y
注：索引是不可更改的，想更改必须删除重新建。
9y[*{ye09、说明：创建视图：create view viewname as select statement友商社区(V4f!v*I_)W8~4`
删除视图：drop view viewname友商社区:\/s$Rnz+s4X+LS
10、说明：几个简单的基本的sql语句
kkR%i4vq3Aii0选择：select * from table1 where 范围友商社区5E$G&@A&q"FU
插入：insert into table1(field1,field2) values(value1,value2)
9^R%a gtk;MC0删除：delete from table1 where 范围
oY5D lu0更新：update table1 set field1=value1 where 范围友商社区,~#ITH6AH7`
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!友商社区}0a%s-kEDPf
排序：select * from table1 order by field1,field2 [desc]友商社区(tv8XQ#PvNL
总数：select count as totalcount from table1
!\5L)t&X*oi_0求和：select sum(field1) as sumvalue from table1
4Em2bCt2K0平均：select avg(field1) as avgvalue from table1
fsE)A3M-ik{j0最大：select max(field1) as maxvalue from table1友商社区;g$qR+ODh
最小：select min(field1) as minvalue from table1友商社区1CD9W)~"jv
11、说明：几个高级查询运算词友商社区8Y@5| X D`"c {w
A： UNION 运算符友商社区M!yEg%Dn @ I
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。友商社区(O,J?[+cj
B： EXCEPT 运算符
x6q*]Hl0EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
`+LOlI0C： INTERSECT 运算符友商社区+@W"v!_:l
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
"UK7v)u%I(e+C0注：使用运算词的几个查询结果行必须是一致的。
0_RjSM012、说明：使用外连接
H(C!bHU&E3s7o0A、left outer join：友商社区}8J1^"qnz
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。友商社区`SZe C-i)B
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友商社区I`cBJn"J
B：right outer join:
O$nJPN+r @:_G0G0右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。友商社区/R(LP/us%Bn
C：full outer join：
"n5Q,N#F
o$q.hce4A0全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
Oj)\qru(hz%~0
!RFeRC9V2a {${;z0二、提升友商社区Lil7|"OQ
友商社区
NG4pSm$['K
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)友商社区5?Mu+lVc)ir
法一：select * into b from a where 1<>1友商社区:p_ E \E
法二：select top 0 * into b from a友商社区p4B+tJ#Px+TR
友商社区1mH'{%v3B4Y%u
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
$AK U
?'|#u0insert into b(a, b, c) select d,e,f from b;友商社区i7{d Z;\-a/Po

rR WHo03、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)友商社区B2JH(S2@F5pIj
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
0J-~~E BQ]Y0例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..友商社区mjz5SDgXg
友商社区;Ukb5S!|Y
4、说明：子查询(表名1：a 表名2：b)

Yd#S#h` Yf0select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
B;p+dlhY\1Y0友商社区5K0DO JQGy
5、说明：显示文章、提交人和最后回复时间友商社区;p'?'p obTU
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
8@M1u{VD N0友商社区kpDO~*v|MK
6、说明：外连接查询(表名1：a 表名2：b)
u0h5Qr,LI,_0select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c友商社区0A9Mw4~nev-A

lW+q0ZOv@p3n07、说明：在线视图查询(表名1：a )
._zJ%j#b'I8@0select * from (SELECT a,b,c FROM a) T where t.a > 1;友商社区a,Y {_Y4U/xD%G
友商社区gr'jk/d
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括友商社区+XD'J$fS0?k
select * from table1 where time between time1 and time2友商社区1f^UU8Lq|
select a,b,c, from table1 where a not between 数值1 and 数值2友商社区 I*}Y b2{cA

#Ct]!`,x3W[ ?F09、说明：in 的使用方法友商社区n:N9I$e`J
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
,d[4V8Am&IGb&YB2@0
].Zd8SXV:g*e010、说明：两张关联表，删除主表中已经在副表中没有的信息友商社区wsk(g4K
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )友商社区$[o:r({,V f-Y-o
友商社区g8c:J.M#^
11、说明：四表联查问题：
{"r*W,Hw2hn+E0select * 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 .....
Fq"eOo0友商社区;dT/YG1wF%V,B
12、说明：日程安排提前五分钟提醒友商社区 W t+B*i+c(v[d0WH9v
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
$_`yS#E{k0
0t|2f~y#R5n'b013、说明：一条sql 语句搞定数据库分页
Bx~0["ucA.@0select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段友商社区,a%Auc8c9\h

1m0p?F?q$j014、说明：前10条记录
U'p.pqZ*D0select top 10 * form. table1 where 范围友商社区?6B0MM(K-@7f$Br

n7SWJD`|015、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
7RNh"Pk b#p/xb q0select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)友商社区:b M] W7h#Sn(Se$S
友商社区ieR3D(I(Y:u2p
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表友商社区A/GK(?6I
(select a from tableA ) except (select a from tableB) except (select a from tableC)
(X*qz2tu)X W(uyw0
uki?v)b4X@9m017、说明：随机取出10条数据
lcbADZ,`U'|0select top 10 * from tablename order by newid()友商社区oGFdDv/Tr

)E"H'l wN
K018、说明：随机选择记录友商社区)Y$xfI0|7Ma.R!YV
select newid()友商社区g3dvC]kU

1?_\{q `$m1p4w~1R019、说明：删除重复记录友商社区WX/r,D!ID,FU7Y7e
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
uN [3N_1d.C {0友商社区^ D#J1`#sH(H:}
20、说明：列出数据库里所有的表名友商社区un i+p_
select name from sysobjects where type='U'友商社区9?:cUXVu

(b)I3L2` c|N:_;s.l1s021、说明：列出表里的所有的友商社区3`;@ G;km_&]
select name from syscolumns where id=object_id('TableName')
^%`1d{:y0
#M)q6m6d"O Ef022、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
2Dsm.D(c"Iy0select 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友商社区Z%^H&Q)M
显示结果：
l$?'p8EM gog'L\0type vender pcs
qKl \3r-|*mv9lR0电脑 A 1
;y_Ril0g6Ag1d0电脑 A 1友商社区C0R|;l*X {
光盘 B 2友商社区*]z S_|5I"an3af9|
光盘 A 2
3L$v8sE)ps/x0手机 B 3友商社区w"R5dkC.d@%} S6V%l
手机 C 3
1`)`"h$F3c3JU0友商社区:b#aR*n\1Lpe
23、说明：初始化表table1
W9]o0Gp2LU|I[K0
\R;Rwl,V"[,R0B0TRUNCATE TABLE table1友商社区(rW5tS%N%Pw*v GD
友商社区Wu D7_ZA1t
24、说明：选择从10到15的记录友商社区u&{htZ8A
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc友商社区,V0?M?gyj$H

'n-B+cUX-j'Hy+UI:w?0三、技巧友商社区9Har hBV B
友商社区SRAb,{~/Oc0G
1、1=1，1=2的使用，在SQL语句组合时用的较多
N2{7mobU0友商社区d!o"e)w-|0[
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
uYg buV
mz0如：友商社区,ImP9z2G^3_Ek
if @strWhere !=''
KgP,Z6u3kr0begin
mW7X-h^w;L!}`0set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
Y8H~1lYkS0end
qCt)_ s$B!y1}0else友商社区"]3RISU\
begin友商社区
k)g `"?"r
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'友商社区 o0[e'z(]
end
C/I"UrN(K.weE0友商社区H;Z7s/r/Bl9W(J
我们可以直接写成
,yejLBk0set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
i0tX:wNR.U0友商社区 EplWi)n7A'H9_9p,N
2、收缩数据库友商社区;QdP_[ s7p3R
--重建索引友商社区(k3EXJ2h3e|
DBCC REINDEX友商社区4ze.vK j2Wq:O
DBCC INDEXDEFRAG
;LM2`#s6k\f `0--收缩数据和日志友商社区$]\\9G!_
DBCC SHRINKDB
$f*l,IJZV/C0DBCC SHRINKFILE
e&?;b2C V _y6`O0友商社区Fm Lq P;Au%j
3、压缩数据库
^4t6`\$Om"p9u9?Q*f;D0dbcc shrinkdatabase(dbname)友商社区t\d!v@

wj z'{6r+j04、转移数据库给新用户以已存在用户权限友商社区#d Tv IN"Z4_Lk
exec sp_change_users_login 'update_one','newname','oldname'友商社区SLD#eo2iePm
go友商社区8YP}L6MW{

Ydxw/R C7nR05、检查备份集友商社区,?DZ f:Q9|3M*?3i
RESTORE VERIFYONLY from disk='E:dvbbs.bak'友商社区{J%B7H8vs

`V{@ \F4n|06、修复数据库友商社区)]Z"nn j9Z
ALTER DATABASE [dvbbs] SET SINGLE_USER
!O%d N9\W0GO
(ttdW$Ob(S\0DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK友商社区h$zc7n mq.] ~E(@
GO
|.q:U\e(a8K0ALTER DATABASE [dvbbs] SET MULTI_USER友商社区l.z b3qDG%azF
GO友商社区ElU6cxUa8}
友商社区"Qv"m;J/]B,~
7、日志清除友商社区H*M q`
lt;ij
SET NOCOUNT ON
2Xk8bocG,_G0DECLARE @LogicalFileName sysname,友商社区1OI)f1M{LLW,V
@MaxMinutes INT,友商社区+Z"sD$}W;Q0G3s
@NewSize INT友商社区3dV9R(PR
友商社区)G wjz X!og2]H
友商社区/t/^3KR*G%i&_
USE tablename -- 要操作的数据库名
gtv K0r6[0]o H'p0SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
f{$_]/C0@MaxMinutes = 10, -- Limit on time allowed to wrap log.友商社区\ w*g'cE
@NewSize = 1 -- 你想设定的日志文件的大小(M)友商社区$y'V&F'S6A#gl;_
友商社区3Yj@u-H"o/GB}
-- Setup / initialize
mBmVfX+QD{%[0DECLARE @OriginalSize int友商社区%? ["GU%H-m
SELECT @OriginalSize = size
Q.T dO&W4o0FROM sysfiles友商社区q
NH~v!u!m
a
WHERE name = @LogicalFileName
B5} A"L5H0SELECT 'Original Size of ' + db_name() + ' LOG is ' +友商社区FbW;eXc_+TS
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +友商社区^(lv*v0GR!jN
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
7P)L*yM!W(E6Iup}0FROM sysfiles友商社区Xth6SqS)NY+z s6n
WHERE name = @LogicalFileName
R$z2T'E5V0CREATE TABLE DummyTrans友商社区 Hk(f%ys/}2B6Z
(DummyColumn char (8000) not null)
f!RiQ|0友商社区'WaJg B+u
友商社区K'X3N!s QD8d3]-o
DECLARE @Counter INT,友商社区 t QeAD-F3C:Fc?
@StartTime DATETIME,
$c2q4@Y2g[cI!P0@TruncLog VARCHAR(255)
)Sq*C+j(vLJ?d5a0SELECT @StartTime = GETDATE(),友商社区n;f$k+u&d x6LO
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'友商社区 b{ YK.VS

vI{@;nTzS0DBCC SHRINKFILE (@LogicalFileName, @NewSize)
9} J"?WqL9V0EXEC (@TruncLog)友商社区^a~0be
-- Wrap the log if necessary.
,D$CKH%ve:a2IJsf0WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired友商社区FPRe3J&x
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)友商社区e)gOz
X w!T
AND (@OriginalSize * 8 /1024) > @NewSize
$N@V)VT5R,X9lH0BEGIN -- Outer loop.
(nX)}:nJ
H0SELECT @Counter = 0
#eZl2AX0WHILE ((@Counter BEGIN -- update
h(Cvh8q0INSERT DummyTrans VALUES ('Fill Log')友商社区;m%v-Kkv)z
DELETE DummyTrans友商社区e?o%i9n_
SELECT @Counter = @Counter + 1
'Z p1m*v5w'Ugf0END友商社区G!`E'q6p$i
EXEC (@TruncLog)友商社区w(a0Fr-Y |_4M aP
END友商社区:f l2C8sn4iJ
SELECT 'Final Size of ' + db_name() + ' LOG is ' +友商社区,L8N;{/z]mW
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
h0]"wEGX0CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

K$x-op5z[3k"kC0FROM sysfiles
w6DFc8A9jF@6vb0WHERE name = @LogicalFileName
,NX`Q _0DROP TABLE DummyTrans
?t[8wiU mik;r0SET NOCOUNT OFF友商社区 cLE2V M:QP

7EKE;K]-D#L08、说明：更改某个表
'yW?$tm+[q0exec sp_changeobjectowner 'tablename','dbo'
kb.q~*_0友商社区8QD?A!N*] J
9、存储更改全部表友商社区K}tXlB O
友商社区)GFUG$V9Y
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
uD(`aK,r+J0@OldOwner as NVARCHAR(128),
QO:ih1}U0@NewOwner as NVARCHAR(128)友商社区
_ `;RxLP8sX
AS友商社区.| wZqLo

`ZF*B$Pc#y/z} k
K0DECLARE @Name as NVARCHAR(128)
0I[n*uC&@!`#mA$s*OM0DECLARE @Owner as NVARCHAR(128)友商社区#sOW1Q"~
}s!Q*D
DECLARE @OwnerName as NVARCHAR(128)友商社区&Z*WD@4y)R)laF;D

;N-O&SK!\Y#O1Q0DECLARE curObject CURSOR FOR
.e-JzS"T*PQT0select 'Name' = name,
~2t0l5qe[8D|0'Owner' = user_name(uid)
\ ^%fNA(V ^`c&M;O0from sysobjects友商社区xjQ'y1xC
gqi
where user_name(uid)=@OldOwner友商社区J.HB'[vYhW
order by name友商社区8l3w$nF8BC:K$x0a M

{qSRQ2P0V~0OPEN curObject
7B;s9O#R!?0FETCH NEXT FROM curObject INTO @Name, @Owner友商社区7fF@T@,C@)g
WHILE(@@FETCH_STATUS=0)友商社区/GNfA#m V4\
BEGIN友商社区i5fqd/]
if @Owner=@OldOwner友商社区K_/[5qw_tb
begin
0vbz QbiI+r0set @OwnerName = @OldOwner + '.' + rtrim(@Name)友商社区({:gg`l.\z
exec sp_changeobjectowner @OwnerName, @NewOwner
-kChh
l,T7@ ?
H}"?%l0end
NI-I)y+o0WuD0-- select @name,@NewOwner,@OldOwner
:`^2S6v~] d,|0
.L|}C!Ta0FETCH NEXT FROM curObject INTO @Name, @Owner
y2{9g8]lz4b&R,o0END
(SZ^p)t0友商社区[,\I!UL
close curObject
Q%w2a,z`q9I.^j~0deallocate curObject
2Bx@tK0GO
Nsg+|o!\b%X0友商社区"q*yhJV&v?z
友商社区2G$i&F3V1{/FUW
10、SQL SERVER中直接循环写入数据友商社区?CyC!n3MlJ7p1k
declare @i int
4a6bT$J6}6dV`0set @i=1友商社区7zg"{X|:J;?\
while @ibegin友商社区1Cza
W{0E8_
insert into test (userid) values(@i)
5s+m)x|W]/x
]M0set @i=@i+1
6GND$~[H0end
_7C|2xO B0
G;e Np^0小记存储过程中经常用到的本周，本月，本年函数友商社区9^i$K)j
W|pyf6O
Dateadd(wk,datediff(wk,0,getdate()),-1)
/e,\| ZE6yr+PF0Dateadd(wk,datediff(wk,0,getdate()),6)友商社区I
K$`*aU6D]?

k!wXcd%R6lu0Dateadd(mm,datediff(mm,0,getdate()),0)
;Nss+b^A:N0Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))
:?"k|2PM_Os zg0友商社区j{H j,S;`M'q
Dateadd(yy,datediff(yy,0,getdate()),0)友商社区#Ys%Hq[8O
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))友商社区
\WH:Q9o
友商社区'rwm+_Q6U yJG
上面的SQL代码只是一个时间段友商社区PI$w[X-m$S
Dateadd(wk,datediff(wk,0,getdate()),-1)
,U+K(cU"b0Dateadd(wk,datediff(wk,0,getdate()),6)
{UL!E0g k]f_-m0就是表示本周时间段.
Xez9Cf8xH0下面的SQL的条件部分,就是查询时间段在本周范围内的:
f:k)tTMy0Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6)友商社区#[d#t N
_6z
而在存储过程中友商社区 |k*mz4F4o1e
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1)友商社区*u:Z)NqqVjH D
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6)
$XK0R [q0友商社区
ef?H6J]8g9oJ(C
最后，再补充一些：友商社区$U*DmM,^+H;M

4w1htn0rC|3}_J:w5C0分组group
s&snE)b_.f0
0E#A#NSA
p B.|W0　　常用于统计时，如分组查总数：友商社区 L%\{"R#K Oh
select gender,count(sno)友商社区3Ff(r2~4D/d}
from students
+K^p)MB8j[0group by gender
}"GR4KU!p9A}0(查看男女学生各有多少)友商社区4n#A7rAQG

qj])Z$q0　　注意：从哪种角度分组就从哪列"group by"
{.o#PY4} p w0友商社区ESy(M+g]#f O
　　对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数 ，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"group by grade, mno, gender"友商社区B AZQ%?:E4W/q Y!t
友商社区8jUz8B!A4y#UH
select grade, mno, gender, count(*)
g*{
^,fN+Z4A0from students
\4{V t;D0group by grade, mno, gender
/Sv!TE2R [?0友商社区*AT0SQ\%D2A*^
　　通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有：
R-tW:cr0友商社区 QCV!Mz"}m
select sno,count(*) from grades
3?f]SrQK8i&~0where markgroup by sno
:a'lQ2UUn P[%u e0having count(*)>1
/RxT |#fU-^dpX0
E,T(i n"Nz,d0　　6.UNION联合
B@g9J] ^w6S0友商社区DiX-e0eK0EC
　　合并查询结果，如：
+_7D1qMu+Q-T%TC8O0
oPg"ut0SELECT * FROM students
Jc!u l}IY_ JQ0WHERE name like ‘张%’
? Iz!v3Ppc:yJ G0UNION [ALL]
Yd L2N4M:] uC0SELECT * FROM students友商社区b KqP.Yk M
WHERE name like ‘李%’友商社区M|q1olb;D

!Y*g\5J:MEg9ED-k'C0　　7.多表查询友商社区noB!GY \!to,|
友商社区8E~m n
w9tD'n
　　a.内连接
,J Z+W0p!y _6F/mU0友商社区Xb)p*kC*lO2R.J{
select g.sno,s.name,c.coursename友商社区n!`NY?P
from grades g JOIN students s ON g.sno=s.sno友商社区s%g6|:Q1jN
JOIN courses c ON g.cno=c.cno
g_MR`.f*c3h#u0(注意可以引用别名)
#v7m;}&N/M e2Zz0b.外连接友商社区x0e:AY4IXh'di6@Z
b1.左连接友商社区 N{ iZ'['K1\1ec:P
select courses.cno,max(coursename),count(sno)友商社区}!wuB4O y2P'|FQL BK8Q
from courses LEFT JOIN grades ON courses.cno=grades.cno友商社区d1G8x s{v
group by courses.cno友商社区A'H8z7C~|/e7uI Mc6g
友商社区2E!WF_9Uh(}+E
　　左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。友商社区$v5h*T+H&K3C

0n"x;WP+xg0　　左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。友商社区5H1BS[(Mz
友商社区W#y,i+l@
　　b2.右连接
.HM/T,E0P^p+z0
P/G d"O8nWY$\c7W~0　　与左连接类似
}qc$^0fFm0^0
u)t(N @z%i.}
q{0　　b3.全连接友商社区,?0?Nk9Jzb fc
友商社区?JnOfv2s@"\
select sno,name,major友商社区@Ly5i-]
from students FULL JOIN majors ON students.mno=majors.mno
%Mj,H)JW0友商社区S,MNXT*n qU
　　两边表中的内容全部显示友商社区2Kq9a?$Bg ]u:p
友商社区4a
t"b1O0`)B-U"S
　　c.自身连接
Q
yb[7U4D0友商社区|B6mx+?(W*ap
select c1.cno,c1.coursename,c1.pno,c2.coursename友商社区 fpY:] hzvgm
from courses c1,courses c2 where c1.pno=c2.cno友商社区S!C es'\.QR y:qwoD

y8M}
a1S]0　　采用别名解决问题。友商社区$yoEw+?Kx S-m

p
ncf3E;e0S0　　d.交叉连接友商社区Y(dE#a$RXp:a1z2E

T/gZ1ftEB`TY0select lastname+firstname from lastname CROSS JOIN firstanme
Wzrc$tT0