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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 k0{5)Su"xr  
R 2uo ZA,  
!3{> F"  
一、基础 C>q,c3s5  
1、说明:创建数据库 V:rq}F}  
CREATE DATABASE database-name **V^8'W<  
2、说明:删除数据库 ">}l8MA  
drop database dbname  ZqQJFyV*  
3、说明:备份sql server I| qoHN,g  
--- 创建 备份数据的 device dnVl;L8L3  
USE master )+c4n]  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' K@P5]}'#  
--- 开始 备份 !HM|~G7  
BACKUP DATABASE pubs TO testBack )miY>7K  
4、说明:创建新表 9 ve q  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) H/>86GG  
根据已有的表创建新表: ;E /:_DWPD  
A:create table tab_new like tab_old (使用旧表创建新表) k=j--`$8k  
B:create table tab_new as select col1,col2... from tab_old definition only < @9p|[!  
5、说明:删除新表 =PiDZS^"  
drop table tabname HTK79 +  
6、说明:增加一个列 Kd8V,teH  
Alter table tabname add column col type R9o3T)9V  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 kAbRXID  
7、说明:添加主键: Alter table tabname add primary key(col) yjODa90!G  
说明:删除主键: Alter table tabname drop primary key(col) 7@u0;5p|  
8、说明:创建索引:create [unique] index idxname on tabname(col....) *ktM<N58  
删除索引:drop index idxname |?n=~21"1O  
注:索引是不可更改的,想更改必须删除重新建。 'v.i' 6  
9、说明:创建视图:create view viewname as select statement )A9K9pZj  
删除视图:drop view viewname D.H$4[u;j  
10、说明:几个简单的基本的sql语句 UH1AT#?!W  
选择:select * from table1 where 范围 @~0kSA7  
插入:insert into table1(field1,field2) values(value1,value2) 3A%/H`  
删除:delete from table1 where 范围 nS0K&MH6B  
更新:update table1 set field1=value1 where 范围 cg$@x\fJ  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! .L[WvAo  
排序:select * from table1 order by field1,field2 [desc] !8^:19+  
总数:select count as totalcount from table1 je1f\N45  
求和:select sum(field1) as sumvalue from table1 <JE-#i  
平均:select avg(field1) as avgvalue from table1 TIbqUR  
最大:select max(field1) as maxvalue from table1 77- Jx`C  
最小:select min(field1) as minvalue from table1 RwMK%^b  
hM")DmvB4  
Jw'%[(q Q  
Be+CV">2  
11、说明:几个高级查询运算词 zXQ o pQ1  
">]v'h(s  
V`$Jan  
A: UNION 运算符 z5PFppSQ  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 J&w%lYiu5  
B: EXCEPT 运算符 K^bzZa+a  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 :1"{0 gm  
C: INTERSECT 运算符 8Czy<}S<G  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 gNJ,Bj Pd  
注:使用运算词的几个查询结果行必须是一致的。 jA R@?X  
12、说明:使用外连接 k;PQVF&E  
A、left outer join: "h'0&ZP~_  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 $F-qqkR$  
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 W!pLk/|ls  
B:right outer join: Qhb].V{utV  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 0UeDM*  
C:full outer join: $e#p -z  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 dg/OjiD[P  
4Y5Q>2D}  
!>T.*8  
二、提升 A6Ttx{]  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) w*[i!i  
法一:select * into b from a where 1<>1 9E^IEwq'  
法二:select top 0 * into b from a `T2RaWR4=  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) %;kr%%t%  
insert into b(a, b, c) select d,e,f from b; z5-vx`  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) R,CFU l7Q  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 L6yRN>5aE  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ucQ2/B#'4l  
4、说明:子查询(表名1:a 表名2:b) xoI;s}*E  
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) [{e[3b*M|  
5、说明:显示文章、提交人和最后回复时间 2%"2~d7  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b }Z*@EWc>  
6、说明:外连接查询(表名1:a 表名2:b) az@{O4  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 0qXd?z$  
7、说明:在线视图查询(表名1:a ) J >Zd0Dn  
select * from (SELECT a,b,c FROM a) T where t.a > 1; /v"u4Ipj  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 U^SJWYi<Y  
select * from table1 where time between time1 and time2 mMm_=cfv  
select a,b,c, from table1 where a not between 数值1 and 数值2 ~Emeo&X  
9、说明:in 的使用方法 3eQ-P8LS  
select * from table1 where a [not] in ('值1','值2','值4','值6') dABmK;  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 sh(G{Yz@  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) @ROMHMd}  
11、说明:四表联查问题: @0A7d $J(  
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 ..... wvsKn YKX  
12、说明:日程安排提前五分钟提醒 Ub=g<MYHV  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 YlD ui8.N  
13、说明:一条sql 语句搞定数据库分页 /gT$d2{  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 44 ,:@  
14、说明:前10条记录 mxsmW  
select top 10 * form table1 where 范围 'F3Xb  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) {aP5Mem  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) r=6-kC!T9  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 )p'ZSXb  
(select a from tableA ) except (select a from tableB) except (select a from tableC) TB 9{e!4  
17、说明:随机取出10条数据 =zBcfFii`w  
select top 10 * from tablename order by newid() "1>I/CM  
18、说明:随机选择记录 !a?$  
select newid() ]kA0C~4   
19、说明:删除重复记录 [mph iH/  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) wjW>#DE  
20、说明:列出数据库里所有的表名 so}(*E&(a  
select name from sysobjects where type='U' r# MJ  
21、说明:列出表里的所有的 tr0P ;}=  
select name from syscolumns where id=object_id('TableName') _cdrz)T  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 +@[T0cXp  
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 ScU?T<u:i  
显示结果: V8ka*VJ(B  
type vender pcs 'EoJo9p6}  
电脑 A 1 j+AAhn  
电脑 A 1 d;O16xcM/  
光盘 B 2 =?>f[J5  
光盘 A 2 q15t7-Z6  
手机 B 3 braHWC'VYg  
手机 C 3 OcQ>01Q  
23、说明:初始化表table1 3jQy"9f  
TRUNCATE TABLE table1 Sc'z vlq  
24、说明:选择从10到15的记录 :xISS  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc }eh<F^  
OW1i{  
-b+VzVJZ  
Cm g(# $ X  
三、技巧 Q!8AFLff4  
1、1=1,1=2的使用,在SQL语句组合时用的较多 (hej 3;W  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, h<2o5c|  
如: x`K<z J   
if @strWhere !='' ?t++IEoP  
begin 8o43J;mA  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere eS(\E0%QI  
end [:<CgU9C  
else KM$L u2  
begin mUY+v>F  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ^6j: lL  
end S0( ).2#  
我们可以直接写成 m` ^o<V&  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 9 I>qD  
2、收缩数据库 9qS~-'&q#  
--重建索引 6Mu_9UAl`  
DBCC REINDEX *YmR7g|k  
DBCC INDEXDEFRAG sFv68Ag+  
--收缩数据和日志 qYFOHu  
DBCC SHRINKDB 9/3gF)I}  
DBCC SHRINKFILE %suSZw`  
3、压缩数据库 l&l&e OE  
dbcc shrinkdatabase(dbname) UFBggT\  
4、转移数据库给新用户以已存在用户权限 :VpRpj4f  
exec sp_change_users_login 'update_one','newname','oldname' $sK8l=#  
go 1!_$HA  
5、检查备份集 [.Vy  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' Z5 iP1/&D  
6、修复数据库 |O3wAxc3W  
ALTER DATABASE [dvbbs] SET SINGLE_USER Xkc y~e  
GO  tKOTQ8i4  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 9IJBK  
GO A;ip V :)  
ALTER DATABASE [dvbbs] SET MULTI_USER 6'CZfs\  
GO 2F9Gx;}t5=  
7、日志清除 ~+w'b7T,=  
SET NOCOUNT ON kt?G\H!}  
DECLARE @LogicalFileName sysname, Sy|fX_i  
        @MaxMinutes INT, aphfzo  
        @NewSize INT )D'SfNx#{  
USE    tablename            -- 要操作的数据库名 eV:I :::  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 A|>~/OW=@  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. gDbj!(tm  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) r4&g~+ck  
-- Setup / initialize pu#h:nb>88  
DECLARE @OriginalSize int | a001_Wv  
SELECT @OriginalSize = size _8x:%$   
  FROM sysfiles u#(VR]u\7  
  WHERE name = @LogicalFileName kI7c22OJ  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + kT6h}d^/^  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + jb;!"HC  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' ]@E_Hx{S  
  FROM sysfiles -PXRd)~  
  WHERE name = @LogicalFileName {*utke]}*  
CREATE TABLE DummyTrans n N.6?a  
  (DummyColumn char (8000) not null) &V/n!|q<H  
DECLARE @Counter  INT, vbEAd)*S  
        @StartTime DATETIME, )!SA]>-  
        @TruncLog  VARCHAR(255) 'fpm] *ig  
SELECT  @StartTime = GETDATE(), '5xIisP  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' u5D@,wSNz  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) oz3N 8^M  
EXEC (@TruncLog) OpFe=1Q  
-- Wrap the log if necessary. ,:6gp3  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired Jw13 Wb-  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  $ 9bIUJ  
      AND (@OriginalSize * 8 /1024) > @NewSize  %oPW`r  
  BEGIN -- Outer loop. WUOoK$I~K  
    SELECT @Counter = 0 A^lJlr:_`  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) .*FBr7rE\  
      BEGIN -- update 8<V6W F`e  
        INSERT DummyTrans VALUES ('Fill Log')  L#U-d zy\  
        DELETE DummyTrans <ZmC8&Uo  
        SELECT @Counter = @Counter + 1 XC 44]o4jx  
      END  '-9B`O,&  
    EXEC (@TruncLog)  #snwRW>=[  
  END  t Qo) *z  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + = iJfz  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + xvo""R/g8  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' pJ8;7u  
  FROM sysfiles K1y]  
  WHERE name = @LogicalFileName E"i<fr T  
DROP TABLE DummyTrans %L;z~C  
SET NOCOUNT OFF ',Y`XP"Q  
8、说明:更改某个表 'T=$Q%Qv  
exec sp_changeobjectowner 'tablename','dbo' VF#2I %R*  
9、存储更改全部表 o[=h=&@5p  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch x=-dv8N?  
@OldOwner as NVARCHAR(128), =NJ:%kvF  
@NewOwner as NVARCHAR(128) z!`aJE/  
AS rl:6N*kK  
DECLARE @Name  as NVARCHAR(128) $D;/b+a  
DECLARE @Owner  as NVARCHAR(128) n^}M*#  
DECLARE @OwnerName  as NVARCHAR(128) a'zXLlXgGd  
DECLARE curObject CURSOR FOR 2rxZN\gyL  
select 'Name'  = name, T''PzY!Qf  
  'Owner'  = user_name(uid) tE|W8=be/  
from sysobjects O*qSc^9q  
where user_name(uid)=@OldOwner Ml-GAkgG  
order by name +]?/c>M  
OPEN  curObject wWq(|"  
FETCH NEXT FROM curObject INTO @Name, @Owner Buxn!s  
WHILE(@@FETCH_STATUS=0) ?a)X)#lQ  
BEGIN    Mw{0A\6  
if @Owner=@OldOwner ,orq&#*Wd  
begin kT7x !7C  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) <HYK9{Q  
  exec sp_changeobjectowner @OwnerName, @NewOwner LYTx8  
end SNLZU%jan  
-- select @name,@NewOwner,@OldOwner r0MUv}p#|L  
FETCH NEXT FROM curObject INTO @Name, @Owner =yT3#A~<G  
END R1,.H92  
close curObject Tt^PiaS!  
deallocate curObject /NE<?t N  
GO gc5u@(P"  
10、SQL SERVER中直接循环写入数据 ;Gf,I1d}{  
declare @i int <V`1?9c7D1  
set @i=1 sY|by\-c  
while @i<30 aC!e#(q  
begin 4c})LAwd&  
  insert into test (userid) values(@i) whH_<@!  
  set @i=@i+1 wl^7.IR  
end pt/UY<@yoN  
小记存储过程中经常用到的本周,本月,本年函数 /Kw}R5l  
Dateadd(wk,datediff(wk,0,getdate()),-1) $*k(h|XfwW  
Dateadd(wk,datediff(wk,0,getdate()),6) Kivr)cIG  
Dateadd(mm,datediff(mm,0,getdate()),0) %#AM }MWIa  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) Ai*R%#  
Dateadd(yy,datediff(yy,0,getdate()),0) HACY  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) p* '%<3ml  
上面的SQL代码只是一个时间段 Wi;wu*  
Dateadd(wk,datediff(wk,0,getdate()),-1) )Bz2-|\  
Dateadd(wk,datediff(wk,0,getdate()),6) /5**2Kgv1  
就是表示本周时间段. J&hzr t  
下面的SQL的条件部分,就是查询时间段在本周范围内的: yW =I*f  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) M53{e;.kN  
而在存储过程中 w(,K  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) SRP.Mqg9  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) CIt%7 \c  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
批量上传需要先选择文件,再选择上传
认证码:
验证问题:
10+5=?,请输入中文答案:十五