社区应用 最新帖子 精华区 社区服务 会员列表 统计排行 社区论坛任务 迷你宠物
  • 8018阅读
  • 0回复

[转贴]精妙SQL语句收集

级别: 店掌柜
发帖
5692
铜板
103378
人品值
1520
贡献值
26
交易币
0
好评度
5373
信誉值
0
金币
0
所在楼道

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 miSC'!  
U$ bM:d  
)wd~639U  
一、基础 +ETw:i9!?  
1、说明:创建数据库 C\D4C]/8  
CREATE DATABASE database-name 0fU>L^P_?  
2、说明:删除数据库 blv6  
drop database dbname a@J :*W  
3、说明:备份sql server u*`GIRfWT  
--- 创建 备份数据的 device 9t1_"{'N1  
USE master 74#@F{w  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' Lp=B? H  
--- 开始 备份 Qpq0j^\  
BACKUP DATABASE pubs TO testBack {*9i}w|2  
4、说明:创建新表 ?]N&H90^5  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) Q-5wI$=  
根据已有的表创建新表: t+ ]+Gn  
A:create table tab_new like tab_old (使用旧表创建新表) ,#l oVLy  
B:create table tab_new as select col1,col2... from tab_old definition only .*"IJD9  
5、说明:删除新表 &ii =$4"R  
drop table tabname ^pa).B.`T  
6、说明:增加一个列 l/LUwDI{  
Alter table tabname add column col type ;@hP*7Lm  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 r1]^#&V;MC  
7、说明:添加主键: Alter table tabname add primary key(col) lc7]=,qyF  
说明:删除主键: Alter table tabname drop primary key(col) qa0Zgn5q  
8、说明:创建索引:create [unique] index idxname on tabname(col....) H l@rS  
删除索引:drop index idxname b}*hodzF  
注:索引是不可更改的,想更改必须删除重新建。 f *vziC<m  
9、说明:创建视图:create view viewname as select statement LBB[aF,Lr  
删除视图:drop view viewname bT}WJ2}  
10、说明:几个简单的基本的sql语句 `( Gk_VAa  
选择:select * from table1 where 范围 yK^k*)2N  
插入:insert into table1(field1,field2) values(value1,value2) z16++LKmM  
删除:delete from table1 where 范围 *TkABUL  
更新:update table1 set field1=value1 where 范围 NQ!F`  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! bX1ip2X lk  
排序:select * from table1 order by field1,field2 [desc] FC#Q tu~J  
总数:select count as totalcount from table1 9h8G2J o  
求和:select sum(field1) as sumvalue from table1 /([aD~.  
平均:select avg(field1) as avgvalue from table1 DJ^JUVi  
最大:select max(field1) as maxvalue from table1 oP6G2@3P/  
最小:select min(field1) as minvalue from table1 hlZjk0ez  
J4i0+u  
/'&L M\  
H@:@zD!G[  
11、说明:几个高级查询运算词 ;21JM2JI8  
\Wk$>?+#@  
JV>OmUAk  
A: UNION 运算符 Wwz{98,K  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 (x@"Dp=MZW  
B: EXCEPT 运算符 =[&Jxy>Y  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 </QSMs  
C: INTERSECT 运算符 Fz% n!d  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 XEI]T~  
注:使用运算词的几个查询结果行必须是一致的。 ( 9l|^w["  
12、说明:使用外连接 Lsdu:+-  
A、left outer join: j>iM(8`t1  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 T5h[{J^  
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 =Sq7U^(>  
B:right outer join: \q>,c49a{  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 `U R.Rn/x  
C:full outer join: cg5DyQ(  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 ` g~-5Z~J  
5{> cfN\q  
m[f\I^ \%8  
二、提升 %y q}4[S+o  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) I f(_$>  
法一:select * into b from a where 1<>1 uu>g(q?4II  
法二:select top 0 * into b from a EbQ}w"{  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) *bx cq  
insert into b(a, b, c) select d,e,f from b; .z"[z^/uF  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 8 _J:Yg  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 4/4IZfznX  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. I}X8-WFB  
4、说明:子查询(表名1:a 表名2:b) u(R`}C?P'  
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) *))|ZE6jI  
5、说明:显示文章、提交人和最后回复时间 7o<RvM  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ;/.ZYTD  
6、说明:外连接查询(表名1:a 表名2:b) ~U|te_l  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c @WmB0cc_  
7、说明:在线视图查询(表名1:a ) RjT[y: !  
select * from (SELECT a,b,c FROM a) T where t.a > 1; jv ";?*I6.  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 '};Xb|msU  
select * from table1 where time between time1 and time2 g;pFT  
select a,b,c, from table1 where a not between 数值1 and 数值2 -vyC,A  
9、说明:in 的使用方法 I zT%Kq  
select * from table1 where a [not] in ('值1','值2','值4','值6') jcj)9;n=!  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Q%a4g  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) yWuq/J:  
11、说明:四表联查问题: s5.2gu|"%  
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 ..... QS_u<B  
12、说明:日程安排提前五分钟提醒 o,-@vp  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 GCoqKE  
13、说明:一条sql 语句搞定数据库分页 JF7T1T  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 -[=`bHo  
14、说明:前10条记录 X:A\{^ ~  
select top 10 * form table1 where 范围 D+V^nCcx%  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 8Y9mB #X  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 7"NUof?i  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 7j Q`i;L}Y  
(select a from tableA ) except (select a from tableB) except (select a from tableC) E=y#~W  
17、说明:随机取出10条数据 M@8(h=  
select top 10 * from tablename order by newid() }Y[.h=X  
18、说明:随机选择记录 "elh~K  
select newid() vv u((b  
19、说明:删除重复记录 Q7C'O @  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) &Wba2fD  
20、说明:列出数据库里所有的表名 D|xSO~M5  
select name from sysobjects where type='U' U;(&!Ei  
21、说明:列出表里的所有的 G`pI{_-e  
select name from syscolumns where id=object_id('TableName') 46vz=# ,6L  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 0ode&dB  
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 0V^?~ex  
显示结果: #E#70vWp\O  
type vender pcs B 5?(gb"  
电脑 A 1 ]OVjq ?  
电脑 A 1 by {~gu  
光盘 B 2 ,FTF@h-Cs  
光盘 A 2 */1z=  
手机 B 3 &~j"3G;e  
手机 C 3 U+K_eEI0_I  
23、说明:初始化表table1 6jn<YR E-  
TRUNCATE TABLE table1 +RbCa c  
24、说明:选择从10到15的记录 aU3&=aN+  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc dCHU* 7DS  
olqHa5qn  
u^ T2  
T:si?7CR  
三、技巧 0<Y)yNsV  
1、1=1,1=2的使用,在SQL语句组合时用的较多 +,smjg:O  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, ' o 5,P/6  
如: /ZczfM\  
if @strWhere !='' *"#>Ov>  
begin M! s&<Bi  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere =$m|M m[a  
end I=1tf;Bsi  
else AOTI&v  
begin O:#to  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' m,pDjf  
end $oNkE  
我们可以直接写成 h\1_$ac  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere dLAElTg  
2、收缩数据库 { "/@,!9rJ  
--重建索引 ;{>z\6N  
DBCC REINDEX gAE}3//  
DBCC INDEXDEFRAG P"- ,^?6  
--收缩数据和日志 X \h]N  
DBCC SHRINKDB p5*i d5  
DBCC SHRINKFILE 39OZZaWL  
3、压缩数据库 Bp}<H<@  
dbcc shrinkdatabase(dbname) "8-]6p3u  
4、转移数据库给新用户以已存在用户权限 43/|[  
exec sp_change_users_login 'update_one','newname','oldname' x>t:&Y M  
go Y A;S'dxY  
5、检查备份集 _uRgKoiy  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' W4Eo1 E  
6、修复数据库 'Ct+0X:D  
ALTER DATABASE [dvbbs] SET SINGLE_USER 6rRPqO j  
GO jtZ@`io  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK ?vZ&CB  
GO oV*3Mec  
ALTER DATABASE [dvbbs] SET MULTI_USER X }^,g  
GO uy B ?-Y+  
7、日志清除 Tj.;\a|d  
SET NOCOUNT ON BqR8%F  
DECLARE @LogicalFileName sysname, r+) A)a,  
        @MaxMinutes INT, 13B[m p4  
        @NewSize INT $ @^n3ZQ4  
USE    tablename            -- 要操作的数据库名 %DiZ&}^Ck  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 %N!Y}$y  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. iJq}tIk#2'  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) #fa~^]EM]  
-- Setup / initialize vHao y  
DECLARE @OriginalSize int 50CU|  
SELECT @OriginalSize = size N?~K9jGx(  
  FROM sysfiles ;X\!*Loe  
  WHERE name = @LogicalFileName NxNz(R $~  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + -tDmzuD6  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + N 4Dyec\  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' u%&zY97/  
  FROM sysfiles w;X-i.%`  
  WHERE name = @LogicalFileName nkv zv  
CREATE TABLE DummyTrans byd[pnI$H  
  (DummyColumn char (8000) not null) GXsHc,  
DECLARE @Counter  INT, Ij#?r2Z%  
        @StartTime DATETIME, lT*Hj.  
        @TruncLog  VARCHAR(255) '*22j ]  
SELECT  @StartTime = GETDATE(), rQ/S|gG  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' S9mj/GpL3  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) e\/Lcng  
EXEC (@TruncLog) 1MOQ/N2BR  
-- Wrap the log if necessary. rNZN}g  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired Zr`:A$  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  N2C^'dFj  
      AND (@OriginalSize * 8 /1024) > @NewSize  XO\P4x :c  
  BEGIN -- Outer loop. oZ!rK/qoA  
    SELECT @Counter = 0 4j/8Otn  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) \p.ku%{  
      BEGIN -- update $NqT ={!  
        INSERT DummyTrans VALUES ('Fill Log')  MvObx'+  
        DELETE DummyTrans V" I+E  
        SELECT @Counter = @Counter + 1 QarA.Ne~  
      END  RM,r0Kv17Y  
    EXEC (@TruncLog)  aWW|.#L  
  END  E_-CsL%  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + )V+ ;7j<"D  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + ]_j{b)t  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' j5tA!o  
  FROM sysfiles 5&6S["lt  
  WHERE name = @LogicalFileName kIM* K%L}  
DROP TABLE DummyTrans #Ey!?Z  
SET NOCOUNT OFF 7j{SCE;  
8、说明:更改某个表 J}lBK P:-*  
exec sp_changeobjectowner 'tablename','dbo' Z5\u9E"]  
9、存储更改全部表 Zs)HzOP)9  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch kyz_r6  
@OldOwner as NVARCHAR(128), 5^[V%4y>  
@NewOwner as NVARCHAR(128) WG< D+P  
AS y1f&+y9e  
DECLARE @Name  as NVARCHAR(128) zZseK  
DECLARE @Owner  as NVARCHAR(128) sJ!AI n<  
DECLARE @OwnerName  as NVARCHAR(128) /O+,vRw\A  
DECLARE curObject CURSOR FOR ><5tnBP|+L  
select 'Name'  = name, WM:we*k8h  
  'Owner'  = user_name(uid) r=<,`_@Y  
from sysobjects p)d'yj  
where user_name(uid)=@OldOwner S_aml  
order by name 03[(dRK>=  
OPEN  curObject P)ZGNtO9fG  
FETCH NEXT FROM curObject INTO @Name, @Owner K5'@$Km  
WHILE(@@FETCH_STATUS=0) W~FcU+a  
BEGIN    .\qZkk}2l  
if @Owner=@OldOwner =((#kDrN  
begin 'ym/@h7h  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) G^5}T>TV  
  exec sp_changeobjectowner @OwnerName, @NewOwner z1_\P) M  
end StA5h+[m  
-- select @name,@NewOwner,@OldOwner $ ^m_M.1  
FETCH NEXT FROM curObject INTO @Name, @Owner JT,8/o  
END -@e9!/GP,  
close curObject A F>!:  
deallocate curObject mRFcZ.7  
GO  g&#.zJ[-  
10、SQL SERVER中直接循环写入数据 I[G<aI!  
declare @i int {088j?[hzk  
set @i=1 m^%[  
while @i<30 0k0 y'1SL  
begin G)M9to  
  insert into test (userid) values(@i) Jah~h44&  
  set @i=@i+1 *h$Z:p-g  
end aB+Ux< -  
小记存储过程中经常用到的本周,本月,本年函数 PJsiT4<  
Dateadd(wk,datediff(wk,0,getdate()),-1) },e f(  
Dateadd(wk,datediff(wk,0,getdate()),6) s=#3f3  
Dateadd(mm,datediff(mm,0,getdate()),0) CUaI66  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 7xz|u\?_2  
Dateadd(yy,datediff(yy,0,getdate()),0) A#\NVN8sk  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) V)oUSHillH  
上面的SQL代码只是一个时间段 ![P1Qv p  
Dateadd(wk,datediff(wk,0,getdate()),-1) ?`3` azfM  
Dateadd(wk,datediff(wk,0,getdate()),6) #B_ ``XV  
就是表示本周时间段. f)~urGazS  
下面的SQL的条件部分,就是查询时间段在本周范围内的: DI"mi1ObE  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) Rku9? zf^  
而在存储过程中 S zsq|T  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) "(>P=  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ,GA2K .:#  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

简单生活
执著追求
别笑我浅溥,天真的以为用一腔真诚就能感动这个冷漠的世界。
也别说我幼稚,竟想用不长的人生去诠释繁杂的红尘。
然而除了真诚,我还能给你什么,的确我真的一无所有!

描述
快速回复

您目前还是游客,请 登录注册
如果您在写长篇帖子又不马上发表,建议存为草稿
认证码:
验证问题:
10+5=?,请输入中文答案:十五