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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 q,ur[ &<  
;~nz%L J  
\~ACWF7l  
一、基础 ItLP&S=  
1、说明:创建数据库 f3bZ*G%f  
CREATE DATABASE database-name ;_]Z3  
2、说明:删除数据库 RlW7l1h&  
drop database dbname Rq,ST:  
3、说明:备份sql server OsSGVk #Qh  
--- 创建 备份数据的 device HW,2x}[  
USE master Z#Q)a;RA  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ZA;VA=)\8  
--- 开始 备份 }iu(-{Z  
BACKUP DATABASE pubs TO testBack Vy+UOV&v-  
4、说明:创建新表 ]E8S`[Vn  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) qjc8$#zXS  
根据已有的表创建新表: 6X1_NbC  
A:create table tab_new like tab_old (使用旧表创建新表) 4R8W ot  
B:create table tab_new as select col1,col2... from tab_old definition only {\87]xJ  
5、说明:删除新表 3 J{hG(5  
drop table tabname VlLc[eVV  
6、说明:增加一个列 ptcG:  
Alter table tabname add column col type F| ib=_)3  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 UN,y /V  
7、说明:添加主键: Alter table tabname add primary key(col) 3aK/5)4|B  
说明:删除主键: Alter table tabname drop primary key(col) BAQ;.N4  
8、说明:创建索引:create [unique] index idxname on tabname(col....) ;:0gN|+  
删除索引:drop index idxname vWM'}(  
注:索引是不可更改的,想更改必须删除重新建。 .*0`}H+_  
9、说明:创建视图:create view viewname as select statement s3., N|  
删除视图:drop view viewname `/1Zy}cD  
10、说明:几个简单的基本的sql语句 E#cW3\)  
选择:select * from table1 where 范围 HDTdOG)  
插入:insert into table1(field1,field2) values(value1,value2) ":o1g5?  
删除:delete from table1 where 范围 .W9/*cZV0  
更新:update table1 set field1=value1 where 范围 p]7Gj &a  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 1|PmZPKq9n  
排序:select * from table1 order by field1,field2 [desc] *C0gpEf9S  
总数:select count as totalcount from table1 (ex^=fv  
求和:select sum(field1) as sumvalue from table1 2k^dxk~$V;  
平均:select avg(field1) as avgvalue from table1 Xc^7  
最大:select max(field1) as maxvalue from table1 +]!`>  
最小:select min(field1) as minvalue from table1 h/CF^0m"!  
kOe~0xoT@u  
a%wK[yVp  
B-OuBS,fwC  
11、说明:几个高级查询运算词 jJ' LM>e  
j&S8x|5  
qM$~5uu  
A: UNION 运算符 >QusXD"L>  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 MKuy?mri~  
B: EXCEPT 运算符 , yC-QFQE  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 1qs~[7{C1  
C: INTERSECT 运算符 Qbc62qFu!  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 J{Z-4y  
注:使用运算词的几个查询结果行必须是一致的。 b=#3p  
12、说明:使用外连接 6$'6x2,  
A、left outer join: }/Wd9x  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 l; e&p${P  
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 LRhq%7p7  
B:right outer join: .2xp.i{  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 )-3!-1  
C:full outer join: %;.|?gR  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 5T[9|zJs  
))n7.pB9/  
{C%/>e2-%  
二、提升 ZSuMQ32  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) %Km_Sy[7']  
法一:select * into b from a where 1<>1 X;5U@l  
法二:select top 0 * into b from a cLQvzd:h=  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) I +,D,Vg  
insert into b(a, b, c) select d,e,f from b; ;3n0 bKDY  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ,i2%FW  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 's{-1aW  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. Ii?"`d+JA  
4、说明:子查询(表名1:a 表名2:b) uP/WRQ{rW>  
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) 'aB0abr|  
5、说明:显示文章、提交人和最后回复时间 s0Ii;7fA{  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b @j$tpz  
6、说明:外连接查询(表名1:a 表名2:b) IbpE@C  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c KJa?TwnC  
7、说明:在线视图查询(表名1:a ) {rWu`QT  
select * from (SELECT a,b,c FROM a) T where t.a > 1; y+RT[*bX5o  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 b`n+[UCPtn  
select * from table1 where time between time1 and time2 s)HbBt-  
select a,b,c, from table1 where a not between 数值1 and 数值2 x8T5aS  
9、说明:in 的使用方法 W%09.bF  
select * from table1 where a [not] in ('值1','值2','值4','值6') ?lb1K'(  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Jkt L|u:k  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) I~S`'()J  
11、说明:四表联查问题: ( $3j  
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 ..... l;L&ijTQD  
12、说明:日程安排提前五分钟提醒 ~?vm97l  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 R*TCoEKO  
13、说明:一条sql 语句搞定数据库分页 7& k lX  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 (k M\R|  
14、说明:前10条记录 ok'0Byo  
select top 10 * form table1 where 范围 !{s $V2_  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) pP?J(0Q~  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) d"XS;;l%<  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 _89 _*t(  
(select a from tableA ) except (select a from tableB) except (select a from tableC) c ~YD|l  
17、说明:随机取出10条数据 {<~XwJ.  
select top 10 * from tablename order by newid() q) /;|h  
18、说明:随机选择记录 ~<v.WP<:  
select newid() 2k"a%#H8  
19、说明:删除重复记录 =Mn! [  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) A i9*w?C  
20、说明:列出数据库里所有的表名 R_vZh|  
select name from sysobjects where type='U' 2t[c^J  
21、说明:列出表里的所有的 u{H,i(mx?  
select name from syscolumns where id=object_id('TableName') :`3b|u=KZ  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 RO wbzA)]r  
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 qR]4m]o  
显示结果: cw"x0 RS  
type vender pcs /gy;~eB01  
电脑 A 1 gSR&CnqZ<  
电脑 A 1 5B2x# m|8  
光盘 B 2 @aCg1Rm  
光盘 A 2 #4na>G|  
手机 B 3 l`I]eTo)^  
手机 C 3 ^ ?tAt3dMI  
23、说明:初始化表table1 ?]9uHrdsN}  
TRUNCATE TABLE table1 t s ?b[v  
24、说明:选择从10到15的记录  N%r}0  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc `t!iknOQ$  
n#J$=@  
4#2iL+   
 b 1[U 9  
三、技巧 AF3t#)q  
1、1=1,1=2的使用,在SQL语句组合时用的较多 {]D!@87  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, oSa FmP  
如: <*(~x esPS  
if @strWhere !='' "E''ZBLO~  
begin ejr"(m(Xe  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere p Hg8(ru|  
end wkJB5i^<w  
else ]LSa(7>EU  
begin [tD*\\IA  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' $}KYpSV  
end |%RFXkHS  
我们可以直接写成 3&y u  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere C>K/C!5?  
2、收缩数据库 4tuEC-oh  
--重建索引 yPQ{tS*t  
DBCC REINDEX  gA[M  
DBCC INDEXDEFRAG ZcWl{e4  
--收缩数据和日志 CIaabn  
DBCC SHRINKDB R3@iN &  
DBCC SHRINKFILE <=7)t.  
3、压缩数据库 s lI)"+6  
dbcc shrinkdatabase(dbname) CqX%V":2  
4、转移数据库给新用户以已存在用户权限 d.} rn"(z  
exec sp_change_users_login 'update_one','newname','oldname' ?x[>g!r  
go -{7N]q)}  
5、检查备份集 /N^+a-.Qd  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' <bjy<98LT  
6、修复数据库 F"F(s!  
ALTER DATABASE [dvbbs] SET SINGLE_USER 5:PZ=jPR  
GO #-f^;=7  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK (,TO|  
GO 3AL.UBj&}  
ALTER DATABASE [dvbbs] SET MULTI_USER  WcJ{}V9  
GO GbE3 :;JI  
7、日志清除 LPNv4lT[u  
SET NOCOUNT ON 2?ednMoE  
DECLARE @LogicalFileName sysname, ""a8eB 6  
        @MaxMinutes INT, .o8Gi*PEY  
        @NewSize INT vJ'ho  
USE    tablename            -- 要操作的数据库名 w:N2 xI  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 &p%ctg  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. "D/ fB%h`  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) Cn<kl^!Q-  
-- Setup / initialize -s1VlS/  
DECLARE @OriginalSize int p4Y 9$(X  
SELECT @OriginalSize = size pqr" x2=.  
  FROM sysfiles xV6j6k  
  WHERE name = @LogicalFileName i,HAXPi  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + =e+go ]87x  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + {h=Ai[|l4Q  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' ?l(hS\N,  
  FROM sysfiles ;uDFd04w [  
  WHERE name = @LogicalFileName '8JaD6W9S  
CREATE TABLE DummyTrans y*D 8XI$  
  (DummyColumn char (8000) not null) W{h7+X]Y  
DECLARE @Counter  INT, D5p22WY  
        @StartTime DATETIME, !v`=EF.  
        @TruncLog  VARCHAR(255) )?WoL Ejq  
SELECT  @StartTime = GETDATE(), 6\K)\  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' #n"/9%35f`  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) %xRS9A 4  
EXEC (@TruncLog) <b\urtoJ  
-- Wrap the log if necessary. /$=^0v +  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired }VGiT~2$  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  4HJrR^  
      AND (@OriginalSize * 8 /1024) > @NewSize  'FhnSNT(4=  
  BEGIN -- Outer loop. &M!:,B  
    SELECT @Counter = 0 [ n2udV  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 7,zARWB!?  
      BEGIN -- update 2$91+N*w9  
        INSERT DummyTrans VALUES ('Fill Log')   V("1\  
        DELETE DummyTrans TG9)x|!  
        SELECT @Counter = @Counter + 1 ]@>|y2  
      END  Z:_y,( 1Q  
    EXEC (@TruncLog)  3^Zi/r  
  END  "->:6Oe2   
SELECT 'Final Size of ' + db_name() + ' LOG is ' + =-qsz^^a-  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + -eMRxa>  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' ^XM;D/Gp~  
  FROM sysfiles m C Ge*V}  
  WHERE name = @LogicalFileName y6/X!+3+  
DROP TABLE DummyTrans .IXwa,  
SET NOCOUNT OFF v(iUo&Ge  
8、说明:更改某个表 0pFHE>  
exec sp_changeobjectowner 'tablename','dbo' 2#3R]zIO  
9、存储更改全部表 dK: "  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch +QX>:z  
@OldOwner as NVARCHAR(128),  o 2  
@NewOwner as NVARCHAR(128) TXcKuo=  
AS 16|S 0 )  
DECLARE @Name  as NVARCHAR(128)  iC]lO  
DECLARE @Owner  as NVARCHAR(128) `Tj}4f  
DECLARE @OwnerName  as NVARCHAR(128) L])w-  
DECLARE curObject CURSOR FOR n^N]iw{G  
select 'Name'  = name, \]xYV}(FO  
  'Owner'  = user_name(uid) fP58$pwu  
from sysobjects 5>)jNtZ  
where user_name(uid)=@OldOwner keskD  
order by name Wi^rnr'S s  
OPEN  curObject pm\X*t}L  
FETCH NEXT FROM curObject INTO @Name, @Owner {R;M`EU>  
WHILE(@@FETCH_STATUS=0) s.'\&B[  
BEGIN    x[UO1% _o-  
if @Owner=@OldOwner I})t  
begin K/`RZ!  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) S:/RYT"  
  exec sp_changeobjectowner @OwnerName, @NewOwner Cg pT(E\E  
end OPW"AB J  
-- select @name,@NewOwner,@OldOwner ?(yFwR,(  
FETCH NEXT FROM curObject INTO @Name, @Owner C+XZDY(=Z  
END &pjV4m|j<  
close curObject Y&+_p$13  
deallocate curObject v`^J3A  
GO 3^> a TU<Z  
10、SQL SERVER中直接循环写入数据 aLt{X)?  
declare @i int <0)ud)~u  
set @i=1 *)1Vs'!-  
while @i<30 bs EpET  
begin 2Fgt)`{!  
  insert into test (userid) values(@i) 8zDH<Gb  
  set @i=@i+1 @jSbMI  
end Lo5@zNt%W  
小记存储过程中经常用到的本周,本月,本年函数 Tw;qY  
Dateadd(wk,datediff(wk,0,getdate()),-1) ,,%:vK+V  
Dateadd(wk,datediff(wk,0,getdate()),6) 2 BX GVo  
Dateadd(mm,datediff(mm,0,getdate()),0) +'KE T,  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 'QojSq   
Dateadd(yy,datediff(yy,0,getdate()),0) DnsP7k.8T  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) ds;cfj[  
上面的SQL代码只是一个时间段 e ]o'i;I  
Dateadd(wk,datediff(wk,0,getdate()),-1) Wk#h,p3  
Dateadd(wk,datediff(wk,0,getdate()),6) ?7MwTi8{F  
就是表示本周时间段. #z!Hb&Qi\  
下面的SQL的条件部分,就是查询时间段在本周范围内的: }BdVD t  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) $KWYe{#  
而在存储过程中 %=9yzIjbAt  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) <o!&Kk9  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) lI_Yb:  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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