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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 CaZEU(i  
bpKMQrwd  
4lvo9R  
一、基础 }_5z(7}3  
1、说明:创建数据库 ^>[DG]g  
CREATE DATABASE database-name q& 4Z.(  
2、说明:删除数据库 *R1x^t+)  
drop database dbname }6m5MH$7q  
3、说明:备份sql server [- vd]ob  
--- 创建 备份数据的 device .R biF  
USE master M8S4D&vpD4  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' fs>0{  
--- 开始 备份 lKH"PH7*_w  
BACKUP DATABASE pubs TO testBack Gash3}+  
4、说明:创建新表 N|7<*\o  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) "0zMx`Dh  
根据已有的表创建新表: OXA_E/F  
A:create table tab_new like tab_old (使用旧表创建新表) %#ms`"H  
B:create table tab_new as select col1,col2... from tab_old definition only /KlA7MH6  
5、说明:删除新表 <m UDx n  
drop table tabname ,iiWVA"  
6、说明:增加一个列 2/?pI/W  
Alter table tabname add column col type -aKL 78  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 G}D?+MWY  
7、说明:添加主键: Alter table tabname add primary key(col) vAwFPqu  
说明:删除主键: Alter table tabname drop primary key(col) hiU_r="*ox  
8、说明:创建索引:create [unique] index idxname on tabname(col....) k]; <PF  
删除索引:drop index idxname sks_>BM  
注:索引是不可更改的,想更改必须删除重新建。  /=[M  
9、说明:创建视图:create view viewname as select statement BQ_\8Qt|  
删除视图:drop view viewname 7{az %I$h  
10、说明:几个简单的基本的sql语句 uyjZmT/-  
选择:select * from table1 where 范围 YJeZ{Wws  
插入:insert into table1(field1,field2) values(value1,value2) nGX~G^mZ  
删除:delete from table1 where 范围 |]r# IpVf  
更新:update table1 set field1=value1 where 范围  $@8\9Y {  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! l]3g6c  
排序:select * from table1 order by field1,field2 [desc] :M|bw{P*  
总数:select count as totalcount from table1 ^b>E_u  
求和:select sum(field1) as sumvalue from table1 ,FS iE\  
平均:select avg(field1) as avgvalue from table1 SuGlNp>#qm  
最大:select max(field1) as maxvalue from table1 A(;J  
最小:select min(field1) as minvalue from table1 bs%]xf ~D;  
69yTGUG3  
N]+x@M @^3  
#Yj0'bgK  
11、说明:几个高级查询运算词 Q7c_;z_  
bp$8hUNYz-  
?_n.B=H`8  
A: UNION 运算符 },[S9I`p  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 uvD 6uIW<  
B: EXCEPT 运算符 t'm]E2/  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 G.B^C)guu  
C: INTERSECT 运算符 kFD-  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 YF&SH)Y7  
注:使用运算词的几个查询结果行必须是一致的。 [ .dNX  
12、说明:使用外连接 hTVN`9h7  
A、left outer join: >SfC '*1  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 j] M)i:n  
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 z13"S(5D~  
B:right outer join: s/P\w"/fN  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 }})4S;j  
C:full outer join: 8 _`Lx_R  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 ,EwJg69  
-cq ~\m^6  
;J?^M!l2=  
二、提升 Zd~s5  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) l\$_t2U  
法一:select * into b from a where 1<>1 d5^ipu  
法二:select top 0 * into b from a 5:n&G[Md  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) U@Aq@d+n  
insert into b(a, b, c) select d,e,f from b; +zL=UEBN  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) =Ee f  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 u!L8Sv  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. _;LHC;,:  
4、说明:子查询(表名1:a 表名2:b) b2p<!?  
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) DB?_E{y]  
5、说明:显示文章、提交人和最后回复时间 :p8JO:g9  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b ax7 M  
6、说明:外连接查询(表名1:a 表名2:b) Z.<1,EKi=  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c z^B!-FcIz>  
7、说明:在线视图查询(表名1:a ) +H ="5uO<  
select * from (SELECT a,b,c FROM a) T where t.a > 1; V!FzVl=G  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 ]p0m6}B  
select * from table1 where time between time1 and time2 2px5>4<  
select a,b,c, from table1 where a not between 数值1 and 数值2 \ 0<e#0-V  
9、说明:in 的使用方法 %$sWNn  
select * from table1 where a [not] in ('值1','值2','值4','值6') pR\etXeLd  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 \I'A:~b)L  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) WYaDN:kZf  
11、说明:四表联查问题: Y>%A*|U%  
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 ..... X4%*&L  
12、说明:日程安排提前五分钟提醒 ;y5cs;s  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 I X\&lV  
13、说明:一条sql 语句搞定数据库分页 ]]d@jj  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 {' r(P&  
14、说明:前10条记录 hb? |fi  
select top 10 * form table1 where 范围 `5GJ,*{z  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) uLL#(bhDr  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) Tb{,WUJg2  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 kN>d5q9b%X  
(select a from tableA ) except (select a from tableB) except (select a from tableC) WWE?U-o  
17、说明:随机取出10条数据 zWjGGTP~3&  
select top 10 * from tablename order by newid() 3_Oq4/  
18、说明:随机选择记录 n]8_]0{qi  
select newid() +;; fw |/  
19、说明:删除重复记录 EidIi"sr  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) DlIfr6F  
20、说明:列出数据库里所有的表名 Pu axS  
select name from sysobjects where type='U' T<!`~#kM  
21、说明:列出表里的所有的 h)A+5^:^  
select name from syscolumns where id=object_id('TableName') Th,2gX9  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 UI;!_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 <w2Nh eM 3  
显示结果: |<BTK_R  
type vender pcs U*a!Gn7l  
电脑 A 1 c#{<| .  
电脑 A 1 F1%' zsv  
光盘 B 2 7g&_`(  
光盘 A 2 vZ|-VvG  
手机 B 3 I;mtyS  
手机 C 3 4] DmgOru%  
23、说明:初始化表table1 p1Lx\   
TRUNCATE TABLE table1 AA05wpu8  
24、说明:选择从10到15的记录 \uanQ|Nu  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc |: nuT$(  
:;??!V  
a`|/*{  
1 !\pwd@{  
三、技巧 UdLC]  
1、1=1,1=2的使用,在SQL语句组合时用的较多 d,D)>Y'h  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, Wg}#{[4  
如: 7r}gS2d  
if @strWhere !='' #c!(97l6o  
begin s0nihX1Z-  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere ?TzN?\   
end wy Le3  
else 0U$6TDtmE  
begin X.UIFcK^  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' d3n TJX  
end gNZ^TeT  
我们可以直接写成 IFv2S|  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere .s2d  
2、收缩数据库  ^5 ;Y  
--重建索引 u\t ;  
DBCC REINDEX eY&UFe  
DBCC INDEXDEFRAG ~:+g+Mf~[  
--收缩数据和日志 +Z{ 4OJK  
DBCC SHRINKDB T>?sPq  
DBCC SHRINKFILE 9qqEr~  
3、压缩数据库 jpBE| Nm  
dbcc shrinkdatabase(dbname) 4|:{apH  
4、转移数据库给新用户以已存在用户权限 $6'xRUx X  
exec sp_change_users_login 'update_one','newname','oldname' W tzV|e,  
go b]Z@zS<8  
5、检查备份集 5<#H=A~(  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' <&+l;z  
6、修复数据库 Y[x ^59  
ALTER DATABASE [dvbbs] SET SINGLE_USER crhck'?0  
GO xaeY^"L  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK nh E!Pk  
GO \XB71DUF  
ALTER DATABASE [dvbbs] SET MULTI_USER ::M/s#-@  
GO zBjqYqZ<+  
7、日志清除 o[cKh7&+  
SET NOCOUNT ON LRbevpZ,  
DECLARE @LogicalFileName sysname, WO}JIExy  
        @MaxMinutes INT, uF^+}Y ZT  
        @NewSize INT Cch1"j<k$  
USE    tablename            -- 要操作的数据库名 mIr{Wocx  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 XhIgzaGVu  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. ^ePSI|EW  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) WVo%'DtF`  
-- Setup / initialize ZE=~ re  
DECLARE @OriginalSize int L)w& f  
SELECT @OriginalSize = size 2"i<--Y  
  FROM sysfiles \!YPht  
  WHERE name = @LogicalFileName nFB;!r  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 2nEj X\BY  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + FlkAo]  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' J'7){C"G$  
  FROM sysfiles dmF<J>[  
  WHERE name = @LogicalFileName c/x(v=LW  
CREATE TABLE DummyTrans $[|8bE  
  (DummyColumn char (8000) not null) B2,! 0Re  
DECLARE @Counter  INT, m&$H ?yXW>  
        @StartTime DATETIME, \<PX'mnO  
        @TruncLog  VARCHAR(255) @D60  
SELECT  @StartTime = GETDATE(), 'wQ=b  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' sJ0y3)PQ  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) _5X}&>>lhF  
EXEC (@TruncLog) ^qk$W? pX  
-- Wrap the log if necessary. \T[*|"RFZ  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired {)%B?75~  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  c9'#G>&h~^  
      AND (@OriginalSize * 8 /1024) > @NewSize  IXg${I}_Q  
  BEGIN -- Outer loop. glv(`cQ  
    SELECT @Counter = 0 S`*al<m  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 'Lm.`U  
      BEGIN -- update $9l3 DJ  
        INSERT DummyTrans VALUES ('Fill Log')  hyTi':  
        DELETE DummyTrans p jrA:;  
        SELECT @Counter = @Counter + 1 E|5gKp-wJ  
      END  VvltVYOZA  
    EXEC (@TruncLog)  r":<1+07  
  END  dj]sr!q+  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + Nf;vUYP  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + m|-O/6~  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' %ZQl.''ISa  
  FROM sysfiles 6dinC <[}  
  WHERE name = @LogicalFileName E?FPxs  
DROP TABLE DummyTrans F-=er e  
SET NOCOUNT OFF x[>A'.m@)  
8、说明:更改某个表 e EU :  
exec sp_changeobjectowner 'tablename','dbo' Q% dpGI  
9、存储更改全部表 RL&*.r&  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch )v|a:'%K_  
@OldOwner as NVARCHAR(128), Ne#nSx5,  
@NewOwner as NVARCHAR(128) S>*T&K  
AS nxH$$}9  
DECLARE @Name  as NVARCHAR(128) r^ "mPgY  
DECLARE @Owner  as NVARCHAR(128) I&cb5j]C  
DECLARE @OwnerName  as NVARCHAR(128) t^7R6y  
DECLARE curObject CURSOR FOR %WO;WxG8^  
select 'Name'  = name, YqDw*S{  
  'Owner'  = user_name(uid) F*NIs:3;  
from sysobjects Dgkt-:S/T|  
where user_name(uid)=@OldOwner d?S<h`{x   
order by name 7C 4Njei"  
OPEN  curObject r[?rwc^  
FETCH NEXT FROM curObject INTO @Name, @Owner %`}Qkb/Lyh  
WHILE(@@FETCH_STATUS=0) *PMql$  
BEGIN    `b] NB^/  
if @Owner=@OldOwner ,)QmQ ^/  
begin PDir?'  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) / _cOg? o  
  exec sp_changeobjectowner @OwnerName, @NewOwner 9:kb0oBa?l  
end 8F@6^9C  
-- select @name,@NewOwner,@OldOwner Tok"-$`N  
FETCH NEXT FROM curObject INTO @Name, @Owner !?+3 jzG  
END Lc.7:r  
close curObject ~ h:^Q  
deallocate curObject /g8yc'{p  
GO :]//{HF  
10、SQL SERVER中直接循环写入数据 fx}R7GN2  
declare @i int =_wgKXBFa  
set @i=1 lLg23k{'  
while @i<30 yV]-![`D  
begin zcNV<tx  
  insert into test (userid) values(@i) (ncfR  
  set @i=@i+1 [XQNgSy?z  
end )kd)v4#  
小记存储过程中经常用到的本周,本月,本年函数 qQom=x  
Dateadd(wk,datediff(wk,0,getdate()),-1) w?5b:W,  
Dateadd(wk,datediff(wk,0,getdate()),6) PD$'xY|1=  
Dateadd(mm,datediff(mm,0,getdate()),0) |Jq/kmn  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) >kB?C!\  
Dateadd(yy,datediff(yy,0,getdate()),0) oOD|FrlY  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) zO+nEsf^O  
上面的SQL代码只是一个时间段 Z os~1N]3  
Dateadd(wk,datediff(wk,0,getdate()),-1) =_UPZ]  
Dateadd(wk,datediff(wk,0,getdate()),6) )0%<ZVB  
就是表示本周时间段. V3m!dp]  
下面的SQL的条件部分,就是查询时间段在本周范围内的: <e=0J8V8,i  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) wWm#[f],?  
而在存储过程中 vx ,yz+yP  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) $]T7Iwk  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) |fJ,+)_(  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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