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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 #cnq(S=.  
U'8bdsF_  
(5R?#vj  
一、基础 +s,Qmmb7)  
1、说明:创建数据库 g6Q!8  
CREATE DATABASE database-name 7N-w eX  
2、说明:删除数据库 :,Pn3xl  
drop database dbname f#?fxUH~  
3、说明:备份sql server h!&prYx  
--- 创建 备份数据的 device 94+KdHAo^M  
USE master wT `a3Ymm  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' Q7R~{5r>W  
--- 开始 备份 j<u@j+V  
BACKUP DATABASE pubs TO testBack T? tG~  
4、说明:创建新表 j:k[90  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) '`eO\huf  
根据已有的表创建新表: KMU4n-s"o  
A:create table tab_new like tab_old (使用旧表创建新表) \=uKHNP?#  
B:create table tab_new as select col1,col2... from tab_old definition only "ul {d(K3  
5、说明:删除新表 ]3VI|f$$  
drop table tabname <1FC%f/  
6、说明:增加一个列 G]fRk^~  
Alter table tabname add column col type 29!q!g|  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 #>%X_o-o23  
7、说明:添加主键: Alter table tabname add primary key(col) X=hYB}}nu  
说明:删除主键: Alter table tabname drop primary key(col) BDq%'~/^  
8、说明:创建索引:create [unique] index idxname on tabname(col....) 9:,V5n=  
删除索引:drop index idxname RnhL< Ywu  
注:索引是不可更改的,想更改必须删除重新建。 ,_yh z0.  
9、说明:创建视图:create view viewname as select statement kD*2~Z?;  
删除视图:drop view viewname Ys@}3\Mc  
10、说明:几个简单的基本的sql语句 an|x$e7|?  
选择:select * from table1 where 范围 nX(+s*Y+w  
插入:insert into table1(field1,field2) values(value1,value2) %;e/7`>Ma  
删除:delete from table1 where 范围 )^4\,u\@  
更新:update table1 set field1=value1 where 范围 1jy9lP=  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! I 4,K43|  
排序:select * from table1 order by field1,field2 [desc] 2C/$Ei^t  
总数:select count as totalcount from table1 #Yr9AVr}K  
求和:select sum(field1) as sumvalue from table1 c:-!'l$ !  
平均:select avg(field1) as avgvalue from table1 Z2TL#@  
最大:select max(field1) as maxvalue from table1 h<Ft_#|o[  
最小:select min(field1) as minvalue from table1 HvM)e.!  
U}MXT <6  
cJ1#ge%4  
JS*m65e  
11、说明:几个高级查询运算词 um4yF*3b9  
4d8B`Fa9  
&K/ya7  
A: UNION 运算符 qjf[zF  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 mH Ic f{RG  
B: EXCEPT 运算符 dZi(&s  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 '[ C.|)"  
C: INTERSECT 运算符 H2um|6>  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 F{eU";D  
注:使用运算词的几个查询结果行必须是一致的。 G`\f  
12、说明:使用外连接 LUC4=kk4   
A、left outer join: ^j" .  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 L5#P[cHzz  
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 %,D<O,N  
B:right outer join: @1DX  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 87=^J xy  
C:full outer join: y($%;l   
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 t%'Z<DmG+  
q)Fq i  
?pn}s]*/  
二、提升 Md0 s K  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) EmODBTu+  
法一:select * into b from a where 1<>1 -PS#Z0>  
法二:select top 0 * into b from a ve% xxn:  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) =|I>G?g-  
insert into b(a, b, c) select d,e,f from b; |lJX 3  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) q o\?o    
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 NX|v=  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. [k6nW:C  
4、说明:子查询(表名1:a 表名2:b) d/ bEt&  
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) mnmP<<8C,  
5、说明:显示文章、提交人和最后回复时间 9G+V;0Q  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b H&]gOs3So  
6、说明:外连接查询(表名1:a 表名2:b) f. FYR|%tq  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c SE),":aY  
7、说明:在线视图查询(表名1:a ) w9, iq@  
select * from (SELECT a,b,c FROM a) T where t.a > 1; 2 !At2P2  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 z)9wXo#~  
select * from table1 where time between time1 and time2 0&/b42W  
select a,b,c, from table1 where a not between 数值1 and 数值2 ;PjQt=4K  
9、说明:in 的使用方法 l2l(_$@3  
select * from table1 where a [not] in ('值1','值2','值4','值6') q|8{@EMT  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 tQ H+)*  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) b|+wc6   
11、说明:四表联查问题: 2Z3('?\z~  
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 ..... Y]L9Y9  
12、说明:日程安排提前五分钟提醒 iVG-_RsKK  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 (;q\}u  
13、说明:一条sql 语句搞定数据库分页 cG?cUw).E  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 n84GZ5O>7  
14、说明:前10条记录 r8,romE$  
select top 10 * form table1 where 范围 nWMmna.5  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) |7X:TfJ  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) #Sa27$&.>  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 OtGb<v<_H  
(select a from tableA ) except (select a from tableB) except (select a from tableC) ^NX"sM0g  
17、说明:随机取出10条数据 .!G94b  
select top 10 * from tablename order by newid() f-5:wM&  
18、说明:随机选择记录 VY)9|JJCO  
select newid() ]R=,5kK3  
19、说明:删除重复记录 mExVYp h  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 5g9; +}X;  
20、说明:列出数据库里所有的表名 RLSc+kDH_  
select name from sysobjects where type='U' BRk0CLr5  
21、说明:列出表里的所有的 !OT-b>*w  
select name from syscolumns where id=object_id('TableName') lKlU-4  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 PSPmO'C+  
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 wlEdt1G  
显示结果: \[jq4`\$  
type vender pcs D5:{fWVsV/  
电脑 A 1 7}vg.hmZ  
电脑 A 1 s%2v3eb  
光盘 B 2 L3n_ 5|  
光盘 A 2 L5qwWvbT  
手机 B 3 -.T&(&>^  
手机 C 3 u-tQ9ioKC  
23、说明:初始化表table1 L~I hsiB  
TRUNCATE TABLE table1 XK: 9r{r{  
24、说明:选择从10到15的记录 M?[h0{^K  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc ^b7GH9<&  
5vw{b?  
^|TG$`M(w  
jq+A-T}@  
三、技巧 $d,0=Ci  
1、1=1,1=2的使用,在SQL语句组合时用的较多 lhtZaU~V  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, A0fFv+RN3  
如: (sQr X{~  
if @strWhere !='' bk)g;+@  
begin 'sxNDnGg  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere D`xHD#j h  
end 59#lU~Kv  
else i| ZceX/  
begin Bf$_XG3  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' !YP@m~  
end n_B"- n  
我们可以直接写成 *FmTy|  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 8X I?  
2、收缩数据库 P(;?kg}0  
--重建索引 v$=QA:!U  
DBCC REINDEX P0$e~=Q^4  
DBCC INDEXDEFRAG Dc+'<"  
--收缩数据和日志 <a[Yk 2  
DBCC SHRINKDB P|HKn,ar  
DBCC SHRINKFILE Z*])6=2Q  
3、压缩数据库 $DZHQH  
dbcc shrinkdatabase(dbname) <ERB.d!  
4、转移数据库给新用户以已存在用户权限 aDehqP6vf  
exec sp_change_users_login 'update_one','newname','oldname' on8WQf'A#  
go  y2+p1  
5、检查备份集 ^mb[j`CCt  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' A.D{.a  
6、修复数据库 =+x yI  
ALTER DATABASE [dvbbs] SET SINGLE_USER [Tnsr(Z  
GO .cR -V`  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK EaWS. eK  
GO jZ%TJ0(H  
ALTER DATABASE [dvbbs] SET MULTI_USER !o>H1#2l  
GO /[9t`  
7、日志清除 W$'R} L  
SET NOCOUNT ON nwN@DqO  
DECLARE @LogicalFileName sysname, (E,Yo  
        @MaxMinutes INT, Raw)9tUt  
        @NewSize INT /'hCi]b@v  
USE    tablename            -- 要操作的数据库名 \T;\XAGr  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名  ru`U'  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. & u!\<\  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) nN~~cV  
-- Setup / initialize gN>2xnh'm  
DECLARE @OriginalSize int de]zT^&C  
SELECT @OriginalSize = size ,&d@O>$E:  
  FROM sysfiles t!2(7=P30(  
  WHERE name = @LogicalFileName Vf`7V$sr  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + Iu{kPyx  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + XTd3|Pm  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' I"1;|`L~:  
  FROM sysfiles c5Q<$86  
  WHERE name = @LogicalFileName &|aqP \Q5  
CREATE TABLE DummyTrans (708H_  
  (DummyColumn char (8000) not null) c)Ic#<e(  
DECLARE @Counter  INT, DaH?@Q  
        @StartTime DATETIME, XK"-'  
        @TruncLog  VARCHAR(255) Uh'#izm[l  
SELECT  @StartTime = GETDATE(), IQ!\w-  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' :=9] c17=  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) @m?QR(LJ  
EXEC (@TruncLog) !I\!;b  
-- Wrap the log if necessary. Y $u9%0q|?  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired k6kM'e3V  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  ^T.E+2=>z  
      AND (@OriginalSize * 8 /1024) > @NewSize  o0ZM[0@j  
  BEGIN -- Outer loop. Sggq3l$Qc  
    SELECT @Counter = 0 =E&OuX-R  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) E0/mSm"(T  
      BEGIN -- update Z--@.IYoJ  
        INSERT DummyTrans VALUES ('Fill Log')  9z I.pv+]  
        DELETE DummyTrans `y+-H|%?  
        SELECT @Counter = @Counter + 1 1.D-FPK  
      END  $HG}[XD?  
    EXEC (@TruncLog)  N-g8}03  
  END  ?DH"V7bs  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + '&99?s`u  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + KIeT!kmDl  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 5*\\J&H  
  FROM sysfiles b7/AnSR~Jt  
  WHERE name = @LogicalFileName A!vCb 8(TX  
DROP TABLE DummyTrans {}o>{&X  
SET NOCOUNT OFF W[[bV  
8、说明:更改某个表 >3gi yeJ  
exec sp_changeobjectowner 'tablename','dbo' GdVhK:<>  
9、存储更改全部表 `]v[5E  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch )>7%pz  
@OldOwner as NVARCHAR(128), o&hIHfZri  
@NewOwner as NVARCHAR(128)  h C=:q  
AS 9]'($:LF08  
DECLARE @Name  as NVARCHAR(128) WU4UZpz  
DECLARE @Owner  as NVARCHAR(128) \ j.x0/;  
DECLARE @OwnerName  as NVARCHAR(128) S?{ /hy  
DECLARE curObject CURSOR FOR eh*6cQ.0  
select 'Name'  = name, Eh| .  
  'Owner'  = user_name(uid) Y:ldR  
from sysobjects `imWc "'Ej  
where user_name(uid)=@OldOwner a{[+<8=@1  
order by name ~dHM4lGY  
OPEN  curObject \#'TNmS  
FETCH NEXT FROM curObject INTO @Name, @Owner FA90`VOWYU  
WHILE(@@FETCH_STATUS=0) #,(sAj  
BEGIN    q@hp.(V  
if @Owner=@OldOwner Sb".]>^  
begin `d2,*KR  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) as+GbstN  
  exec sp_changeobjectowner @OwnerName, @NewOwner $3X-r jQtW  
end O|cu.u|  
-- select @name,@NewOwner,@OldOwner ,&HR(jTo  
FETCH NEXT FROM curObject INTO @Name, @Owner OOBhbpg!D  
END zu2HH<E  
close curObject >%Ee#m  
deallocate curObject >\<*4J$PZ  
GO ]v G{kAnH  
10、SQL SERVER中直接循环写入数据 CnN9!~]"  
declare @i int qP!P +'B  
set @i=1 8_H=^a>2  
while @i<30 _)$PKOzbb  
begin ?hYqcT[%  
  insert into test (userid) values(@i) !}M,  
  set @i=@i+1 2}vg U$a  
end #(LfYw.P1V  
小记存储过程中经常用到的本周,本月,本年函数 O;[9_[  
Dateadd(wk,datediff(wk,0,getdate()),-1) dz#5q-r  
Dateadd(wk,datediff(wk,0,getdate()),6) ZiFooA  
Dateadd(mm,datediff(mm,0,getdate()),0) JM.XH7k  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) #kkY@k$4  
Dateadd(yy,datediff(yy,0,getdate()),0) O)R(==P26P  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) r C[6lIP  
上面的SQL代码只是一个时间段 B6}FIg)  
Dateadd(wk,datediff(wk,0,getdate()),-1) d h^^G^  
Dateadd(wk,datediff(wk,0,getdate()),6) $!A:5jech  
就是表示本周时间段. aH_6s4+:  
下面的SQL的条件部分,就是查询时间段在本周范围内的: hbOnlj4  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) rAdacnZV  
而在存储过程中 I-NN29Sk  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) _ia!mT <  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) E{Pgf8  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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