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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 wkA+j9.  
trPAYa}W  
FbaEB RM  
一、基础 }=gx#  
1、说明:创建数据库 _=Z,E.EN  
CREATE DATABASE database-name Xjo5v*Pu  
2、说明:删除数据库 /'].lp  
drop database dbname s>;v!^N?u  
3、说明:备份sql server "?ucO4d  
--- 创建 备份数据的 device !;i`PPRwk  
USE master Ox&P}P0f  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' -8:&>~4`  
--- 开始 备份 Ghx3EVqnx"  
BACKUP DATABASE pubs TO testBack NdtB1b  
4、说明:创建新表 Bg5Wba%NK  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) Q&wB$*u  
根据已有的表创建新表: v(B<Nb  
A:create table tab_new like tab_old (使用旧表创建新表) ^W'fA{sr  
B:create table tab_new as select col1,col2... from tab_old definition only e+$p9k~  
5、说明:删除新表 +$C 4\$t  
drop table tabname v0-cd  
6、说明:增加一个列 %W%9j#!aN  
Alter table tabname add column col type S M0~fAtE  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 tZ=E')!\  
7、说明:添加主键: Alter table tabname add primary key(col) C${Vg{g7a  
说明:删除主键: Alter table tabname drop primary key(col) {QcLu"?c  
8、说明:创建索引:create [unique] index idxname on tabname(col....) gVq;m>\|F  
删除索引:drop index idxname 4L ;% h  
注:索引是不可更改的,想更改必须删除重新建。 WHsgjvh"  
9、说明:创建视图:create view viewname as select statement E*.{=W }C  
删除视图:drop view viewname e,F1Xi #d  
10、说明:几个简单的基本的sql语句 k9:{9wW  
选择:select * from table1 where 范围 (]0%}$Fo  
插入:insert into table1(field1,field2) values(value1,value2) ~$iIVJ`  
删除:delete from table1 where 范围 P3cRl']  
更新:update table1 set field1=value1 where 范围 _LMM,!f  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! LR.Hh   
排序:select * from table1 order by field1,field2 [desc] t]y D-3'l&  
总数:select count as totalcount from table1 {5%5}[/x  
求和:select sum(field1) as sumvalue from table1 Ss~;m']68  
平均:select avg(field1) as avgvalue from table1 "x=f=;  
最大:select max(field1) as maxvalue from table1 < ,Ue 0  
最小:select min(field1) as minvalue from table1 @hJ%@(  
|]J>R  
b8V~S'6VqO  
7ePqmB<.  
11、说明:几个高级查询运算词 0vEoGgY0*:  
vy0X_DPCr  
p<TpK )  
A: UNION 运算符 ?]Pmxp H}  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 CN#+U,NZV  
B: EXCEPT 运算符 qUjmB sB  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 {;N,t]>8M  
C: INTERSECT 运算符 6|aKL[%6  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 jGXO\:s O  
注:使用运算词的几个查询结果行必须是一致的。 ^* J2'X38I  
12、说明:使用外连接 S0~2{ G"v  
A、left outer join: =U#dJ^4P  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 m@"QDMHk.  
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 #JgH}|&a$  
B:right outer join: "} q@Y=  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 f|h|q_<;  
C:full outer join: :n0vQ5a  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 bu:S:`  
ln?v j)j  
kSR\RuY*  
二、提升 8Eakif0CO  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) IhA5Wt0j  
法一:select * into b from a where 1<>1 12;8o<~  
法二:select top 0 * into b from a 2_n7=&  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 4SlADvGl  
insert into b(a, b, c) select d,e,f from b; :YXX8|>  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ,_Z5m;  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 }\HN&@  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. Pl=ZRKn  
4、说明:子查询(表名1:a 表名2:b) 4a'N>eDR  
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) PV9pa/`@  
5、说明:显示文章、提交人和最后回复时间 `S6x<J&T\/  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b .2%zC & ;  
6、说明:外连接查询(表名1:a 表名2:b) jUSmq m'  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Po ZuMF  
7、说明:在线视图查询(表名1:a ) -u2P ?~  
select * from (SELECT a,b,c FROM a) T where t.a > 1; =x<ge_Y  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 {DU`[:SQZg  
select * from table1 where time between time1 and time2 .WPV dwV4U  
select a,b,c, from table1 where a not between 数值1 and 数值2 =R#Qx,  
9、说明:in 的使用方法 pPcTrN'  
select * from table1 where a [not] in ('值1','值2','值4','值6') |/09<F:L[  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 x$1]M DAGb  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 0BIy>wy:  
11、说明:四表联查问题: ;.TRWn#  
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 ..... /9HVY %n  
12、说明:日程安排提前五分钟提醒 k Mu8"Az  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 Q^ pmQ  
13、说明:一条sql 语句搞定数据库分页 ^r*r w=  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 +)y^ 'Qs  
14、说明:前10条记录 { jhr<  
select top 10 * form table1 where 范围 /lo2y?CS*  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) k 9L? +PD  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) xZ6~Ma 2z  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 vH#huZA?7  
(select a from tableA ) except (select a from tableB) except (select a from tableC) g=;%  
17、说明:随机取出10条数据 #=6E\&NC  
select top 10 * from tablename order by newid() W}5xmz  
18、说明:随机选择记录 T(t+ iv  
select newid() 4<% *E{`  
19、说明:删除重复记录 nq6@6GRG  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) QlJ)F{R8il  
20、说明:列出数据库里所有的表名 yp$_/p O=2  
select name from sysobjects where type='U' xn5l0'2  
21、说明:列出表里的所有的 pgOQIzu  
select name from syscolumns where id=object_id('TableName') KO]T<R h<  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 eu(:`uu  
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 nHm}zOLc  
显示结果: MFb9H{LA  
type vender pcs  OU8Lldt  
电脑 A 1 Wzw7tLY._  
电脑 A 1 s (zL   
光盘 B 2 gREzZ+([  
光盘 A 2 +xrr? g  
手机 B 3 f ` R/ i  
手机 C 3 S,Xnzrz  
23、说明:初始化表table1 ?)u@Rf9>  
TRUNCATE TABLE table1 dYL"h.x  
24、说明:选择从10到15的记录 (+B5|_xQu  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 4"(<X  
S" xKL{5  
BZR:OtR^  
nPye,"A Ol  
三、技巧 :.$3vaZ@  
1、1=1,1=2的使用,在SQL语句组合时用的较多 !4^C #{$  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, m^b Nuo  
如: MO n  
if @strWhere !='' 8P1=[i]  
begin @ Wd9I;hWv  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere ~} ,=OF-b  
end w]]8dz  
else UPG9)aF  
begin . koYHq  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' \'|> p/5I  
end i[?Vin  
我们可以直接写成 >AcrG]  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere Ib+Y~ XYR  
2、收缩数据库 V+VkY3  
--重建索引 D^=J|7e  
DBCC REINDEX Pmh8sw  
DBCC INDEXDEFRAG Mdl{}P0)  
--收缩数据和日志 RLLTw ?]$  
DBCC SHRINKDB 4iKgg[)7`=  
DBCC SHRINKFILE X{\F;Cb*  
3、压缩数据库 `NgAT 3zq  
dbcc shrinkdatabase(dbname) )XYv}U   
4、转移数据库给新用户以已存在用户权限 cVv;Jn  
exec sp_change_users_login 'update_one','newname','oldname' p$PKa.Y3  
go X)7x<?DAy  
5、检查备份集 YbTxn="_  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' H;YP8MoQ  
6、修复数据库 U$_xUG  
ALTER DATABASE [dvbbs] SET SINGLE_USER ~ xft  
GO >D(RYI  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK rvnT6Ve  
GO ,u`B<heoLU  
ALTER DATABASE [dvbbs] SET MULTI_USER "4uS3h2r  
GO C/TF-g-_Y  
7、日志清除 TWQG591  
SET NOCOUNT ON f!!V${)X  
DECLARE @LogicalFileName sysname, G>1eFBh }  
        @MaxMinutes INT, a2 Y;xe  
        @NewSize INT f;1K5Y  
USE    tablename            -- 要操作的数据库名 f"qga/  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 <N_+=_  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. Il= W,/y  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) f] J M /  
-- Setup / initialize 4KH45|; 3  
DECLARE @OriginalSize int S$Q8>u6Wk  
SELECT @OriginalSize = size l zYnw)Pv  
  FROM sysfiles 1oN^HG6O  
  WHERE name = @LogicalFileName Hhari!R XC  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + &!/}Qp  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + x)rM/Kq  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' \H=&`?  
  FROM sysfiles #m{UrTC  
  WHERE name = @LogicalFileName Y6Lf@}2(i  
CREATE TABLE DummyTrans (&+kl q  
  (DummyColumn char (8000) not null) .)<(Oj|4  
DECLARE @Counter  INT, vfq%H(  
        @StartTime DATETIME, 'J6 M*vO  
        @TruncLog  VARCHAR(255) B:!W$ <  
SELECT  @StartTime = GETDATE(), <-,gAk)u  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DRc)iE>@  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) *T~Ve;3h;  
EXEC (@TruncLog)  Fl1;;F  
-- Wrap the log if necessary. 't6V:X  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired .8y3O]  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  t=-SH^$SR  
      AND (@OriginalSize * 8 /1024) > @NewSize  h\PHK C2  
  BEGIN -- Outer loop. J,AR5@)1  
    SELECT @Counter = 0 _c, '>aH=  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 1. rj'  
      BEGIN -- update L (khAmm  
        INSERT DummyTrans VALUES ('Fill Log')  l PK +$f$  
        DELETE DummyTrans /ew Ukc8,  
        SELECT @Counter = @Counter + 1 }w1~K'ck}>  
      END  QoG cWJ  
    EXEC (@TruncLog)  DM}YJ  
  END  8[J}CdS  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + /ig:9R  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + []A%<EI7  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' /k<WNZM  
  FROM sysfiles 4Rvf  
  WHERE name = @LogicalFileName #@"<:!?z  
DROP TABLE DummyTrans %7gkNa  
SET NOCOUNT OFF ,{LG4qvP  
8、说明:更改某个表 av$/Om :  
exec sp_changeobjectowner 'tablename','dbo' h3Q21D'f  
9、存储更改全部表 [&nh5 |f  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch "Q?k'^@  
@OldOwner as NVARCHAR(128), l"2OP6d  
@NewOwner as NVARCHAR(128) 'ul~7h;n  
AS uvV;Mlo]  
DECLARE @Name  as NVARCHAR(128) v0YG,)_  
DECLARE @Owner  as NVARCHAR(128) opJMS6%r  
DECLARE @OwnerName  as NVARCHAR(128) bIEhgiH  
DECLARE curObject CURSOR FOR !X<~-G2)l  
select 'Name'  = name, mGGsB5#w>  
  'Owner'  = user_name(uid) T9u<p=p  
from sysobjects QNxl/y\l0  
where user_name(uid)=@OldOwner 7?kIVP1r  
order by name bf!M#QOk?  
OPEN  curObject ijdXU8  
FETCH NEXT FROM curObject INTO @Name, @Owner <F.Tx$s  
WHILE(@@FETCH_STATUS=0) JGH60|  
BEGIN    DNj "SF(J  
if @Owner=@OldOwner 2w-51tqm  
begin Hx\H $Y  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) h<SQL97N  
  exec sp_changeobjectowner @OwnerName, @NewOwner kJ-*fe'S  
end &ZX{R#[L  
-- select @name,@NewOwner,@OldOwner +C(/ Lyo}  
FETCH NEXT FROM curObject INTO @Name, @Owner l+g9 5m jP  
END }-o{ASC#  
close curObject !ZI7&r`u;  
deallocate curObject ;x8k[p~2  
GO T7d9ChU\#.  
10、SQL SERVER中直接循环写入数据 &2=dNREJ}1  
declare @i int `p7&> BOA  
set @i=1 K%Rj8J7|u?  
while @i<30 {nvLPUL  
begin GKFq+]W  
  insert into test (userid) values(@i) V]vc(rH  
  set @i=@i+1 F`9ZH.  
end =pk)3<GwF  
小记存储过程中经常用到的本周,本月,本年函数 <@Fy5k-%.  
Dateadd(wk,datediff(wk,0,getdate()),-1) 't9hXzAfW  
Dateadd(wk,datediff(wk,0,getdate()),6) D.1J_Y=9  
Dateadd(mm,datediff(mm,0,getdate()),0) o,!T2&}  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) C$@yG)Pj   
Dateadd(yy,datediff(yy,0,getdate()),0) {M?vBg R\B  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) yG sz2T;w  
上面的SQL代码只是一个时间段 ryc& n5  
Dateadd(wk,datediff(wk,0,getdate()),-1) "n=vN<8(o  
Dateadd(wk,datediff(wk,0,getdate()),6) V2<?ol  
就是表示本周时间段. lZrVY+ D  
下面的SQL的条件部分,就是查询时间段在本周范围内的: YTjkPj:  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ]wWPXx[>/  
而在存储过程中 9Y<#=C  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) ZZ.m(A TR  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) D^-7JbE]  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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