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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 %y;Cgo[  
|(a< b  
|JH1?n  
一、基础 p)=Fi}#D\  
1、说明:创建数据库 Yv jRJ  
CREATE DATABASE database-name bi[gyl#  
2、说明:删除数据库 lTpmoDa%  
drop database dbname  $mG&4Y  
3、说明:备份sql server h+h`0(z  
--- 创建 备份数据的 device p,+$7f1S  
USE master w">p 8  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' I- X|-  
--- 开始 备份 u!&Vbo? .B  
BACKUP DATABASE pubs TO testBack pjX')i<  
4、说明:创建新表 ryp@<}A]!d  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) YWPAc>uw,  
根据已有的表创建新表: |>P`Gl]E  
A:create table tab_new like tab_old (使用旧表创建新表) NI136P  
B:create table tab_new as select col1,col2... from tab_old definition only hE>i~:~R  
5、说明:删除新表 S_B;m1  
drop table tabname htGk:  
6、说明:增加一个列 y2eeE CS]  
Alter table tabname add column col type Awad!_VdHS  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 cC6W1K!  
7、说明:添加主键: Alter table tabname add primary key(col) G.a^nQ@e%  
说明:删除主键: Alter table tabname drop primary key(col) L7tC?F]}SK  
8、说明:创建索引:create [unique] index idxname on tabname(col....) 3M{/9rR[  
删除索引:drop index idxname "b"Q0"w  
注:索引是不可更改的,想更改必须删除重新建。 0SBiMTm  
9、说明:创建视图:create view viewname as select statement g^DPb pWxu  
删除视图:drop view viewname /a$RJ6t&3  
10、说明:几个简单的基本的sql语句 "!6 Ax-'  
选择:select * from table1 where 范围 X} v]iX  
插入:insert into table1(field1,field2) values(value1,value2) RWi~34r  
删除:delete from table1 where 范围 :jq   
更新:update table1 set field1=value1 where 范围 DKfw8"L]  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! IU`&h2KZ.  
排序:select * from table1 order by field1,field2 [desc] ApYri|^r  
总数:select count as totalcount from table1 q E`  
求和:select sum(field1) as sumvalue from table1 ',yY  
平均:select avg(field1) as avgvalue from table1 tc'` 4O]c8  
最大:select max(field1) as maxvalue from table1 L 59q\_|  
最小:select min(field1) as minvalue from table1 rSVU|O3m;  
fN TPW]  
I2=?H <  
r9@Q="J_)  
11、说明:几个高级查询运算词 GJY7vS^#  
T.zU erbO  
 %Ln7{w  
A: UNION 运算符 Y|=/*?o}  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 t F<|Eja *  
B: EXCEPT 运算符 q|. X[~e|  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 FU|c[u|z  
C: INTERSECT 运算符 %K_[Bx{B  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 8ctUK|  
注:使用运算词的几个查询结果行必须是一致的。 Yl+r>+^  
12、说明:使用外连接 YoKY&i6r}  
A、left outer join: 1wl8  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 0'm4 ) \  
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 $!<J_ d*  
B:right outer join: A({8p  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 nJ`JF5tI  
C:full outer join: &z r..i4O  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 UNJ]$x0  
x62 b=k}  
MeqW/!72$L  
二、提升 Fa$ pr`  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) qsUlfv9L6  
法一:select * into b from a where 1<>1 UJ_E&7,L  
法二:select top 0 * into b from a HKk;oG  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) dD3I.?DY  
insert into b(a, b, c) select d,e,f from b; Y zXL8  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) [}|-% 4s  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 sV/#P<9  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. MmBM\Dnv  
4、说明:子查询(表名1:a 表名2:b) 2 fX-J  
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) +1 H.5|  
5、说明:显示文章、提交人和最后回复时间 ^<R*7mB*  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b !+4}x;!8  
6、说明:外连接查询(表名1:a 表名2:b) y8Bi5Ae,+1  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c }MDuQP]  
7、说明:在线视图查询(表名1:a ) ->x+ p"  
select * from (SELECT a,b,c FROM a) T where t.a > 1; is%qG?,P  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 m Kwhd} V  
select * from table1 where time between time1 and time2 dQR2!yHEq  
select a,b,c, from table1 where a not between 数值1 and 数值2 K4i#:7r'b  
9、说明:in 的使用方法 zlmb_akJ  
select * from table1 where a [not] in ('值1','值2','值4','值6') 2yhtJ9/  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 [EDw0e  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) >8~+[e  
11、说明:四表联查问题: m-lUgx7  
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 ..... Cyxt EzPp  
12、说明:日程安排提前五分钟提醒 `5;O|qRq  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 #e0tT+  
13、说明:一条sql 语句搞定数据库分页 !6ZkLE[XJ<  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 3VbQDPG  
14、说明:前10条记录 ip4:px-  
select top 10 * form table1 where 范围 C26PQGo#$  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) ^.F@yo2}  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) BW}^n  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 iKa}@U  
(select a from tableA ) except (select a from tableB) except (select a from tableC) tnz BNW8  
17、说明:随机取出10条数据 SeBbI&Ju  
select top 10 * from tablename order by newid() :<w3.(Z  
18、说明:随机选择记录 <L@0w8i`  
select newid() v6 DN:!&  
19、说明:删除重复记录 Rx*T7*xg{  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) L=Q- r[  
20、说明:列出数据库里所有的表名 9}Tf9>qP>M  
select name from sysobjects where type='U' '2a}1?  
21、说明:列出表里的所有的 o_p//S#q  
select name from syscolumns where id=object_id('TableName') qn#\ro1H  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 _JA.~edqM  
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 \Nu(+G?e  
显示结果:  gM20n^  
type vender pcs 2As 4}  
电脑 A 1 W|3XD-v@  
电脑 A 1 qtTys gv  
光盘 B 2 '8~7Ru\KyX  
光盘 A 2 NjVuwIm+  
手机 B 3 3uCC_Am  
手机 C 3 ZGa>^k[:  
23、说明:初始化表table1 \pB"R$YZ6  
TRUNCATE TABLE table1 ="RDcf/  
24、说明:选择从10到15的记录 OC9_EP\"  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc !SIGzj  
|]~tX zY  
Gd`qZqx#  
)JTh=w4n|z  
三、技巧 "g\  
1、1=1,1=2的使用,在SQL语句组合时用的较多 J[;c}  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, FGBPhH% (8  
如: gk~.u  
if @strWhere !='' V^=z\wBZ  
begin ts3%cRN r  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 5UR$Pn2a2  
end JQ'NFl9<  
else dfGdY"&  
begin ZPn`.Qc  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ]v@#3,BV  
end x&tad+T  
我们可以直接写成 C<2vuZD  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere X^#48*"a  
2、收缩数据库 Q <EFd   
--重建索引 a_m P$4T  
DBCC REINDEX 4s~Y qP{K  
DBCC INDEXDEFRAG IP$^)t[  
--收缩数据和日志 ~" B0P>7  
DBCC SHRINKDB xA#B1qbw  
DBCC SHRINKFILE 4hg]/X"H#  
3、压缩数据库 (1%u`#5n-N  
dbcc shrinkdatabase(dbname) /sH3Rk.>  
4、转移数据库给新用户以已存在用户权限 &@c=$+#C  
exec sp_change_users_login 'update_one','newname','oldname' p-UACMN& c  
go W+&ZYN 'E  
5、检查备份集 Vp\BNq_!s  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' =U!'v X d  
6、修复数据库 CN\SxK`,  
ALTER DATABASE [dvbbs] SET SINGLE_USER xZjD(e'  
GO |Rw0$he  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK fzRzkn:=  
GO tQbDP!,A*=  
ALTER DATABASE [dvbbs] SET MULTI_USER ?C//UN;  
GO ||cG/I&,  
7、日志清除 P*T 'R  
SET NOCOUNT ON Q1IN@Db}y  
DECLARE @LogicalFileName sysname, 6DD^h:*>  
        @MaxMinutes INT, 2BBGJE  
        @NewSize INT <g5Bt wo%  
USE    tablename            -- 要操作的数据库名 G6_Kid}"q  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 K7Kd{9-2  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. <)n1Z[4  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) Axhe9!Fm  
-- Setup / initialize }XWic88!~  
DECLARE @OriginalSize int /}-]n81m  
SELECT @OriginalSize = size kWc%u-_  
  FROM sysfiles .B{3=z^  
  WHERE name = @LogicalFileName ,(}7 ST  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + abuHu'73  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + p@/!+$^{  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' wy <m&M<Gr  
  FROM sysfiles pMYEL  
  WHERE name = @LogicalFileName Fd2Eq&:en$  
CREATE TABLE DummyTrans HlBw:D(z:^  
  (DummyColumn char (8000) not null) SJ^.#^)  
DECLARE @Counter  INT, +|).dm  
        @StartTime DATETIME, E:T<mI?d  
        @TruncLog  VARCHAR(255) {N[IjY  
SELECT  @StartTime = GETDATE(), 9kuL1tcY  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' XL>Vwd  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) r5Jy( ~  
EXEC (@TruncLog) bv5,Yk  
-- Wrap the log if necessary. ;hJTJMA6/6  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired )}hp[*C  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  ^IOf%  
      AND (@OriginalSize * 8 /1024) > @NewSize  "'p:M,:  
  BEGIN -- Outer loop. nV,qC .z  
    SELECT @Counter = 0 =Bi>$Ly  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ]8*g%  
      BEGIN -- update +'2Mj|d@p  
        INSERT DummyTrans VALUES ('Fill Log')  gpVZZ:~  
        DELETE DummyTrans Yvs)H'n=  
        SELECT @Counter = @Counter + 1 *oL?R2#7  
      END  vXLiYWo  
    EXEC (@TruncLog)  63QMv[`,  
  END  f{FW7T}O2  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + y/h~oGxy  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + {*ATY+  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' wAkpk&R  
  FROM sysfiles g+t-<D"L5  
  WHERE name = @LogicalFileName ]C3{ _?=  
DROP TABLE DummyTrans /+.Bc(`  
SET NOCOUNT OFF ]Vo;ZY_\  
8、说明:更改某个表 4 FW~Y  
exec sp_changeobjectowner 'tablename','dbo' %N7b XKDP  
9、存储更改全部表 v*<hE>J0  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch jxL} tS{j  
@OldOwner as NVARCHAR(128), "yXKu)_  
@NewOwner as NVARCHAR(128) lPSyFb"  
AS d+rrb>-OU  
DECLARE @Name  as NVARCHAR(128) =21$U[  
DECLARE @Owner  as NVARCHAR(128) |Nd!+zE$Z  
DECLARE @OwnerName  as NVARCHAR(128) G)]'>m<y  
DECLARE curObject CURSOR FOR K>l$Y#x}k  
select 'Name'  = name, F?\XhoJ3G  
  'Owner'  = user_name(uid) H)}>&Z4  
from sysobjects Ij` %'/J  
where user_name(uid)=@OldOwner 0#<q]M?hW  
order by name 'Xoif"  
OPEN  curObject " JFx  
FETCH NEXT FROM curObject INTO @Name, @Owner %/"I.\%d  
WHILE(@@FETCH_STATUS=0) 2Hw&}8  
BEGIN    !'wh hi  
if @Owner=@OldOwner %%)"W n#`  
begin U BzX%:A  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) Z,)4(#b =  
  exec sp_changeobjectowner @OwnerName, @NewOwner !?Gt5$f  
end ?OW 4J0B'  
-- select @name,@NewOwner,@OldOwner \,ARYwd  
FETCH NEXT FROM curObject INTO @Name, @Owner i#Io;  
END m~'!  
close curObject Yrs7F.Y"  
deallocate curObject aY}:9qBice  
GO )=;GQ*<8Zs  
10、SQL SERVER中直接循环写入数据 Wf/r@/ q  
declare @i int f_Ma~'3   
set @i=1 dKTyh:_{  
while @i<30 3p6QJuSB  
begin Oq@+/UWX  
  insert into test (userid) values(@i) f(:+JH<P~  
  set @i=@i+1 u,AP$+Qk  
end B(7oHj.i2  
小记存储过程中经常用到的本周,本月,本年函数 6=U81  
Dateadd(wk,datediff(wk,0,getdate()),-1) DDQ}&`s  
Dateadd(wk,datediff(wk,0,getdate()),6) JFH3)Q  
Dateadd(mm,datediff(mm,0,getdate()),0) |tIr?nXSW3  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ug{@rt/"Z  
Dateadd(yy,datediff(yy,0,getdate()),0) %V71W3>6WS  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 4~xKW2*`K  
上面的SQL代码只是一个时间段 Q)c3=.[>  
Dateadd(wk,datediff(wk,0,getdate()),-1) g= ~Y\$&  
Dateadd(wk,datediff(wk,0,getdate()),6) k#uSH eq7f  
就是表示本周时间段. AD K)p?  
下面的SQL的条件部分,就是查询时间段在本周范围内的: 7/+I"~  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) G d~ v _  
而在存储过程中 %c"PMTq(  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 7rQwn2XD{  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) Swz{5 J2C  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
认证码:
验证问题:
3+5=?,请输入中文答案:八 正确答案:八