SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 'kk
B>g7B
SAGECK[Ix
sr`)l& t?
一、基础 $iOkn|~<@W
1、说明:创建数据库 0xpE+GY
CREATE DATABASE database-name VMV~K7%0
2、说明:删除数据库 >@L^^-r
drop database dbname %y R~dt'
3、说明:备份sql server ^li(q]g1!
--- 创建 备份数据的 device ~:):.5o
USE master &-4SA j
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' =\)qUs\z
--- 开始 备份 #(d/A<
BACKUP DATABASE pubs TO testBack j8{,u6w)-
4、说明:创建新表
CO.e.:h
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) A.(xa+z?
根据已有的表创建新表: r_e]sOCb
A:create table tab_new like tab_old (使用旧表创建新表) F=8gtk|U
B:create table tab_new as select col1,col2... from tab_old definition only +@#k<.yqn
5、说明:删除新表
Mgc|># =
drop table tabname :y(HOUB
6、说明:增加一个列 i T&Y9
Alter table tabname add column col type C_
(s
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 N1jJ(}{3
7、说明:添加主键: Alter table tabname add primary key(col) ,)P6fa/
说明:删除主键: Alter table tabname drop primary key(col) Xsv^GmP+
8、说明:创建索引:create [unique] index idxname on tabname(col....) =Ye I,KbA)
删除索引:drop index idxname `#>JRQ=
注:索引是不可更改的,想更改必须删除重新建。 \>(S?)6
9、说明:创建视图:create view viewname as select statement $_b^p=
删除视图:drop view viewname \C;F5AO
10、说明:几个简单的基本的sql语句 -'Y@yIb
选择:select * from table1 where 范围 e*jfxQ=qG
插入:insert into table1(field1,field2) values(value1,value2) ^%2S,3*0
删除:delete from table1 where 范围 L+d4&x
更新:update table1 set field1=value1 where 范围 Y<9Lqc.i
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 4z^5|$?_ta
排序:select * from table1 order by field1,field2 [desc] :H`Z.>K
总数:select count as totalcount from table1 h6C:`0o
求和:select sum(field1) as sumvalue from table1
Kgu#Mi~
平均:select avg(field1) as avgvalue from table1 -
]Mp<Y
最大:select max(field1) as maxvalue from table1 IL N0/eH
最小:select min(field1) as minvalue from table1 7P7d[KP<
AcxC$uh
ro*$OLc/
O7GJg;>?
11、说明:几个高级查询运算词 Hp?uYih0
8i'EO6
DJ<F8-sb2r
A: UNION 运算符 0FEn& \2<
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 hNGD`"U
B: EXCEPT 运算符 ;mLbgiqQ J
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 +5IC-=ZB
C: INTERSECT 运算符 _!C'oG6s?
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 I85bzzZB
注:使用运算词的几个查询结果行必须是一致的。 R.B3
12、说明:使用外连接 u!K5jqP
A、left outer join: R7-+@
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 vqnFyd
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 tA6x
B:right outer join: @$%[D`Wa<
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 ?q+^U>wy&
C:full outer join: i>n)T
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 n8vteGQ
BA cnFO
$Hbd:1%i
{
二、提升 Uv"O'Z
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) @8xa"Dc
法一:select * into b from a where 1<>1 MuCnBx
法二:select top 0 * into b from a 9q|36CAO_
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) @E@5/N6M
insert into b(a, b, c) select d,e,f from b; H@!kgaNF
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) v^QUYsar
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 b^I(>l-
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 8ECBi(
4、说明:子查询(表名1:a 表名2:b) 8WvQ[cd
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) v05B7^1@_
5、说明:显示文章、提交人和最后回复时间 #Mmr{4m
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b v$i[dZSN[
6、说明:外连接查询(表名1:a 表名2:b) "I`g(q#Uo
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c wUBug
7、说明:在线视图查询(表名1:a ) v a
j
select * from (SELECT a,b,c FROM a) T where t.a > 1; q&N1| f7
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 I&Y9
select * from table1 where time between time1 and time2 li
Hz5<|
select a,b,c, from table1 where a not between 数值1 and 数值2 p^ojhrr
9、说明:in 的使用方法 CEr*VsvjsU
select * from table1 where a [not] in ('值1','值2','值4','值6') gm}[`GMU
10、说明:两张关联表,删除主表中已经在副表中没有的信息 yQM<(;\O
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) &*3O+$L
11、说明:四表联查问题: FeAMt
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 ..... !>Ru= $9
12、说明:日程安排提前五分钟提醒 $2+(|VG4F
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 skRI\
13、说明:一条sql 语句搞定数据库分页 y%^TZ[S
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 +`H{
14、说明:前10条记录 :UdH}u!Ek
select top 10 * form table1 where 范围 YoEL|r|
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) L-\o zp
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) tfb_K4h6,
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
/i
(select a from tableA ) except (select a from tableB) except (select a from tableC) n:d7 Tv1Z8
17、说明:随机取出10条数据 z3X:.%
select top 10 * from tablename order by newid() a'm\6AW2)
18、说明:随机选择记录 v<wR`7xG
select newid() EM&;SQ;C9
19、说明:删除重复记录 iYHCa }
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) jWL;ElM'
20、说明:列出数据库里所有的表名 :Z'q1kW@"
select name from sysobjects where type='U' 4RYvI!
21、说明:列出表里的所有的 ,V}Vxq3
select name from syscolumns where id=object_id('TableName') .*>pD/
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 v)AadtZ0d
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 $IU|zda8
显示结果: gcNpA?mC|u
type vender pcs :0)nL
电脑 A 1 ;x=r.3OQy
电脑 A 1 }qhNz0*
光盘 B 2 1FQ_`wF4
光盘 A 2 "Yu';&
手机 B 3 +zup+=0e
手机 C 3 '7Aj0U(
23、说明:初始化表table1 31@m36? X
TRUNCATE TABLE table1 uY~xHV_-
24、说明:选择从10到15的记录 IR<`OA
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 3S_H hvB
F;,LY:s|Z
)5(Ko<"
9q=\_[\[
三、技巧 UPI'O %
1、1=1,1=2的使用,在SQL语句组合时用的较多 D^%DYp
"where 1=1" 是表示选择全部 "where 1=2"全部不选,
P)$q
如: !e"TWO*X
if @strWhere !='' QTNE.n<?
begin 9?xc3F2EBD
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere \X?GzQkr
end ^.f`6 6/
else ^%:syg_RM[
begin O]nZr
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' Z$'483<
end Ao/KB_4f*Q
我们可以直接写成 aAX(M=3
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 9WH
2、收缩数据库 [8J/#!B
--重建索引 !ufSO9eDx"
DBCC REINDEX 7Vd"AVn}g
DBCC INDEXDEFRAG :)9^T<
--收缩数据和日志 4Nx]*\\
DBCC SHRINKDB [x.DwU%S
DBCC SHRINKFILE iA[WDB\|0
3、压缩数据库 Ef2#}%>
dbcc shrinkdatabase(dbname) o/U"'FP
4、转移数据库给新用户以已存在用户权限 ~!Q\\_
exec sp_change_users_login 'update_one','newname','oldname' lN-[2vT<
go !] -ET7
5、检查备份集 X+*"FKm S.
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' z&@Vg`w"
6、修复数据库 w u
ALTER DATABASE [dvbbs] SET SINGLE_USER u0vq`5L
GO MiX*PqNTM
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK ct3^V M&/
GO =h{jF7
ALTER DATABASE [dvbbs] SET MULTI_USER oNfNe^/T
GO R3dCw:\O+Z
7、日志清除 FojsI<
SET NOCOUNT ON #
[0>wEq
DECLARE @LogicalFileName sysname, FLI0C
@MaxMinutes INT, q ["T6
@NewSize INT dgIEc]#pH
USE tablename -- 要操作的数据库名 0y"Ra%Y
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 BP7&wd
@MaxMinutes = 10, -- Limit on time allowed to wrap log. y,`SLgBID
@NewSize = 1 -- 你想设定的日志文件的大小(M) re `B fN
-- Setup / initialize !PFc)J
DECLARE @OriginalSize int Ao:<aX,=
SELECT @OriginalSize = size JlF$|y,gV,
FROM sysfiles }gr6naz
WHERE name = @LogicalFileName q-;z!iq|!
SELECT 'Original Size of ' + db_name() + ' LOG is ' + iw$n*1M
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + ;6?VkF
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' 'z=QV {ni
FROM sysfiles Y_}DF.>I P
WHERE name = @LogicalFileName -Xw i}/OX
CREATE TABLE DummyTrans QE.a2
}
(DummyColumn char (8000) not null) *k]izWsV*
DECLARE @Counter INT, e uF@SS
@StartTime DATETIME, ,/qS1W(
@TruncLog VARCHAR(255) D\Nhq Vw
SELECT @StartTime = GETDATE(), Vf'r6Rf
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' !P6\-.
DBCC SHRINKFILE (@LogicalFileName, @NewSize) ?1f(@
EXEC (@TruncLog) NG2@.hP:uU
-- Wrap the log if necessary. @\=%M^bx
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired HZ#<+~J
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) OC [ +t6
AND (@OriginalSize * 8 /1024) > @NewSize ~S],)E1w
BEGIN -- Outer loop. k365.nc
SELECT @Counter = 0 SRixT+E
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) #hOAG_a,
BEGIN -- update sKkk+-J4
INSERT DummyTrans VALUES ('Fill Log') dz6i~&
DELETE DummyTrans \.R+|`{tf
SELECT @Counter = @Counter + 1 E_aDkNT
END F`3J=AJOJ
EXEC (@TruncLog) L0Fhjbc
END j^g^=uau
SELECT 'Final Size of ' + db_name() + ' LOG is ' + Z5vpo$l
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + W* XG9
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' d +]Gw
FROM sysfiles 5jpb`Axj#
WHERE name = @LogicalFileName f/r@9\x
DROP TABLE DummyTrans p&:(D=pIu
SET NOCOUNT OFF RSNukg
8、说明:更改某个表 -qPYm?$
exec sp_changeobjectowner 'tablename','dbo' d@:4se-q+
9、存储更改全部表 s5s'$|h"
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch jH1!'1s|
@OldOwner as NVARCHAR(128), vq df-i
@NewOwner as NVARCHAR(128) X\I"%6$
AS drJ<&1O
DECLARE @Name as NVARCHAR(128) ~olta\|
DECLARE @Owner as NVARCHAR(128) <V}^c/c!
DECLARE @OwnerName as NVARCHAR(128) em87`Hj^lo
DECLARE curObject CURSOR FOR *uLlf'qU]
select 'Name' = name, FE)L?
'Owner' = user_name(uid) (5SN=6O
from sysobjects G|Du/XYh
where user_name(uid)=@OldOwner M``I5r*cg
order by name CywQ
OPEN curObject Btmv{'T_y@
FETCH NEXT FROM curObject INTO @Name, @Owner
W6&s_ (
WHILE(@@FETCH_STATUS=0) )1KlcF
BEGIN BCE}Er&
if @Owner=@OldOwner /A U&
X
begin 8TK&i,
set @OwnerName = @OldOwner + '.' + rtrim(@Name) u |hT1l
exec sp_changeobjectowner @OwnerName, @NewOwner ^_5Nh^
end .,C8ASfh
-- select @name,@NewOwner,@OldOwner ^cE|o&Rm;
FETCH NEXT FROM curObject INTO @Name, @Owner y]
Io`w(>
END -NG`mfu
close curObject BwN65_5p
deallocate curObject =%4vrY
`
GO ; 7`y##
10、SQL SERVER中直接循环写入数据 m)A~1+M$)L
declare @i int 'NM$<<0
set @i=1 +v 9@du
while @i<30 n]/7UH}(<&
begin (z}q6Lfa
insert into test (userid) values(@i) DQ{Yr>J
set @i=@i+1 >f [Lb|t
end )"im|9
小记存储过程中经常用到的本周,本月,本年函数 L}bS"=B[&W
Dateadd(wk,datediff(wk,0,getdate()),-1) ? jywW$
Dateadd(wk,datediff(wk,0,getdate()),6) !+?,y/*5(
Dateadd(mm,datediff(mm,0,getdate()),0) ,FvBZ.4c3=
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) IH;+pN
Dateadd(yy,datediff(yy,0,getdate()),0) =R08B)yR
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) $J>J@4
上面的SQL代码只是一个时间段 Z7<N<
Dateadd(wk,datediff(wk,0,getdate()),-1) cxhS*"Ph
Dateadd(wk,datediff(wk,0,getdate()),6) oC]|ARgQk|
就是表示本周时间段. GW_@hYIqD
下面的SQL的条件部分,就是查询时间段在本周范围内的: :V>M{vd
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) P"`OuN
而在存储过程中 T@[(FVA N
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) OY'490
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) sLE@Cm]k