SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 $H9xM
Av[jFk
b13>>'BMB
一、基础 /[-hJ=<Yb
1、说明:创建数据库 D;E&;vP6%
CREATE DATABASE database-name uJ$"2<O
2、说明:删除数据库 Yk5kC0B
drop database dbname G@(7d1){
3、说明:备份sql server 8;5/_BwMu
--- 创建 备份数据的 device BZBsE
:(F
USE master f0uiNy(r$
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' 4H:WpW*r
--- 开始 备份 *Zvw&y*
BACKUP DATABASE pubs TO testBack x{- caOH
4、说明:创建新表 t&oNJq{
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
1Li@O[%X<
根据已有的表创建新表: :z:Blp>nK/
A:create table tab_new like tab_old (使用旧表创建新表) `l}-S |a
B:create table tab_new as select col1,col2... from tab_old definition only tEUmED0FY
5、说明:删除新表 7MfT~v
drop table tabname QJ/SP
6、说明:增加一个列 3,[2-obmi
Alter table tabname add column col type }B5I#Af7
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 p%s
D>1k
7、说明:添加主键: Alter table tabname add primary key(col) @K/Ia!Lw
说明:删除主键: Alter table tabname drop primary key(col) 40<&0nn
8、说明:创建索引:create [unique] index idxname on tabname(col....) zxh"@j$?
删除索引:drop index idxname GxkG$B
注:索引是不可更改的,想更改必须删除重新建。 (pmo[2kg
9、说明:创建视图:create view viewname as select statement A0XFu}
删除视图:drop view viewname ^WNJQg'
10、说明:几个简单的基本的sql语句 ~JXHBX
选择:select * from table1 where 范围 J-}NFWR;t
插入:insert into table1(field1,field2) values(value1,value2) I%p#E#[G
删除:delete from table1 where 范围 @2mP
更新:update table1 set field1=value1 where 范围 ZeeuH"A
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! I ?>#neHc6
排序:select * from table1 order by field1,field2 [desc] Y8PT`7gd`
总数:select count as totalcount from table1 j#&
求和:select sum(field1) as sumvalue from table1 lK4+8VZ
平均:select avg(field1) as avgvalue from table1 xYT.J 6
最大:select max(field1) as maxvalue from table1 kslN_\
最小:select min(field1) as minvalue from table1 FMVmH!E
H[D/Sz5`
T?Y\~.+99
`9ox?|iJ
11、说明:几个高级查询运算词 'm|m+K83
0U>Q<I}
RVfe}4Stm#
A: UNION 运算符 CC\z_C*P-p
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 K(gj6SrjV
B: EXCEPT 运算符 V5B-S.i@
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 GcnY=%L?
C: INTERSECT 运算符 akQH+j
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 o!~bR
注:使用运算词的几个查询结果行必须是一致的。 qedGBl&
12、说明:使用外连接 {n]sRz
A、left outer join: ?_aR-[XRg
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 blcKtrYg
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 C t)MvZ
B:right outer join: 9$&e~^&B
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 ~8*oGG~s
C:full outer join: ^{:jY, ?]
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Jdn*?hc+
!9,
pX
(fa?ftK
二、提升 >x4[7YAU{
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) ]ufW61W6Ci
法一:select * into b from a where 1<>1 !dY:S';~
法二:select top 0 * into b from a |8 bO5l:
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) :OCuxSc%5
insert into b(a, b, c) select d,e,f from b; d|]O<]CG_
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) Net)l@IB]
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 M"^Vf{X^
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. Eap/7U1Q
4、说明:子查询(表名1:a 表名2:b) m>ycN
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) N@6OQ:,[F
5、说明:显示文章、提交人和最后回复时间 `{F~'t['
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b </gp3WQ.
6、说明:外连接查询(表名1:a 表名2:b) \oX8/-0 f
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 87KrSZ
7、说明:在线视图查询(表名1:a ) _}OJPahw
select * from (SELECT a,b,c FROM a) T where t.a > 1; p &>A5
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 KC:4
select * from table1 where time between time1 and time2 P0e ""9JOo
select a,b,c, from table1 where a not between 数值1 and 数值2 tvT8UW'
9、说明:in 的使用方法 kQVDC,d
select * from table1 where a [not] in ('值1','值2','值4','值6') -'[(Uzj
10、说明:两张关联表,删除主表中已经在副表中没有的信息 F,M"/hnPT
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) _1<'"u#6w
11、说明:四表联查问题: ceZ8}Sh
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 ..... 6@q[tN7_^
12、说明:日程安排提前五分钟提醒 QZ3(u<f
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 Jh4&Qh|t
13、说明:一条sql 语句搞定数据库分页 pP* ~ =?
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 8i;)|z7
14、说明:前10条记录 rLh9`0|D
select top 10 * form table1 where 范围 eQFb$C]R}y
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) @f{_=~+
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) BwJ^_:(p~
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 }l(m5
(select a from tableA ) except (select a from tableB) except (select a from tableC) rN1U.FRe/
17、说明:随机取出10条数据 a4(?]ND~6
select top 10 * from tablename order by newid() =6L*!JP<
18、说明:随机选择记录 0[:9 Hb6
select newid() -,Q $
19、说明:删除重复记录 Y^6[[vaj2
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) dHG Io
20、说明:列出数据库里所有的表名 .L7Yf+yFg
select name from sysobjects where type='U' /Y[o=Uyl
21、说明:列出表里的所有的 }"^'%C8EX
select name from syscolumns where id=object_id('TableName') VQ#3#Hj
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 ACq7dLys,B
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 x1&W^~
显示结果: Qnd5X`jF#
type vender pcs OTDg5:>
电脑 A 1 G 2!xPHz
电脑 A 1 5L,}e<S$
光盘 B 2 `n5"0QRd
光盘 A 2 j _L@U2i
手机 B 3 UMl#D>:C<
手机 C 3 A][fLlpr
23、说明:初始化表table1 '6y}ZE[
TRUNCATE TABLE table1 E.|-?xQ6
24、说明:选择从10到15的记录 z7GTaX$d
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc *jIqAhs0{
N-
H^lqD
1K,1X(0rL8
jO*l3:!~ \
三、技巧 .
\
1、1=1,1=2的使用,在SQL语句组合时用的较多 ,<BbpIQ2o
"where 1=1" 是表示选择全部 "where 1=2"全部不选, Suk
如: 4lhw3,5
if @strWhere !='' JM1R ;i6
begin X3'H
`/
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere r}[7x]sP
end <S?ddp2
else
9$`lIy@B
begin 2MZCw^s>
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' A+hT3;lp
end nWQ;9_qBB
我们可以直接写成 M9zfT!-
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere otggN:^Qw
2、收缩数据库 mU}F!J#6
--重建索引 2^8%>,
DBCC REINDEX ^&y$Wd]6
DBCC INDEXDEFRAG M%jPH
--收缩数据和日志 Vz6Qxd{m3
DBCC SHRINKDB y
WV#Up
DBCC SHRINKFILE a7N!B' y
3、压缩数据库 e[hcJz!D
dbcc shrinkdatabase(dbname) Aq3}Ng
4、转移数据库给新用户以已存在用户权限 UTXSeNP
exec sp_change_users_login 'update_one','newname','oldname' p<M\U"5Ye
go 0RoU}r@z4
5、检查备份集 M|:UwqV>
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' "
Hd|7F'u=
6、修复数据库 [l,Ei?
ALTER DATABASE [dvbbs] SET SINGLE_USER 7jdb)l\p=
GO )7h$G-fe
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK w*R$o
GO 'C"9QfK
ALTER DATABASE [dvbbs] SET MULTI_USER h:9Zt0,
GO =UV`.d2[
7、日志清除 K4kMM*D
SET NOCOUNT ON hdrm!aBd
DECLARE @LogicalFileName sysname, o}ZdTf=
@MaxMinutes INT, i[ mEi|
@NewSize INT ti#7(^j
USE tablename -- 要操作的数据库名 yj\Nkh
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 1YH+d0UGn
@MaxMinutes = 10, -- Limit on time allowed to wrap log. uCcYPvm
@NewSize = 1 -- 你想设定的日志文件的大小(M) 6Oy$gW)
-- Setup / initialize rW=Z>1
DECLARE @OriginalSize int kBffF@{
SELECT @OriginalSize = size =zz~kon9
FROM sysfiles S_Wrw z
WHERE name = @LogicalFileName jbAx;Xt'=M
SELECT 'Original Size of ' + db_name() + ' LOG is ' + pej|!oX
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + MjU6/pO}L
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' ;5)P6S.D
FROM sysfiles &wV]"&-
WHERE name = @LogicalFileName w 47tgPPk
CREATE TABLE DummyTrans [C'JH//q*t
(DummyColumn char (8000) not null) h5^Z2:#
DECLARE @Counter INT, x U4 +|d
@StartTime DATETIME, sr{a(4*\
@TruncLog VARCHAR(255) Y/*mUS[oa
SELECT @StartTime = GETDATE(), j-lfMEa$o
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' o6uJyCO
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Kkm>e{0)AY
EXEC (@TruncLog) o{y9r{~A
-- Wrap the log if necessary. ]jo1{IcI
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired t&AFUt\c
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) !]=d-RGNe
AND (@OriginalSize * 8 /1024) > @NewSize 6V7B;tB
BEGIN -- Outer loop. N(Fp0
SELECT @Counter = 0 xwq+j "
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) >LOjV0K/
BEGIN -- update &tg&5_
INSERT DummyTrans VALUES ('Fill Log') ']2E {V
DELETE DummyTrans ,Y/ g2
4R
SELECT @Counter = @Counter + 1 (KDD e}f
END 6">+
~
G
EXEC (@TruncLog) -;^j:L{
END
zem8G2#c
SELECT 'Final Size of ' + db_name() + ' LOG is ' + ewctkI$,5
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + @`q:IIgW
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' BRgXr
FROM sysfiles 7/I, HxXp!
WHERE name = @LogicalFileName HTX?,C_
DROP TABLE DummyTrans W04@!_) <
SET NOCOUNT OFF w9< R#y[A
8、说明:更改某个表 `erV$( M
exec sp_changeobjectowner 'tablename','dbo' [YlKR'_
9、存储更改全部表 W3MJr&p
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch #h`
V>;
@OldOwner as NVARCHAR(128), 1_PoqD!q
@NewOwner as NVARCHAR(128) H4:`6 PSL
AS t*Sa@$p
DECLARE @Name as NVARCHAR(128) 9]f!'d!5
DECLARE @Owner as NVARCHAR(128) +BETF;0D
DECLARE @OwnerName as NVARCHAR(128) TO]@
Zu1
DECLARE curObject CURSOR FOR _m5uDF?[
select 'Name' = name, haK3?A,"_A
'Owner' = user_name(uid) zv0l,-o
from sysobjects a_f~N1kq
where user_name(uid)=@OldOwner mr*JJF0Z
order by name hT`J1nNt
OPEN curObject Xg|B \\
FETCH NEXT FROM curObject INTO @Name, @Owner le/,R@]B9
WHILE(@@FETCH_STATUS=0) +B B@OW
BEGIN ?!A7rb/tj
if @Owner=@OldOwner RJ 8+h
begin <D<4BnZ(
set @OwnerName = @OldOwner + '.' + rtrim(@Name) ,(d)Qg
exec sp_changeobjectowner @OwnerName, @NewOwner Q=;U@k@>
end 8 OY 3A
-- select @name,@NewOwner,@OldOwner Mm.<r-b
FETCH NEXT FROM curObject INTO @Name, @Owner nAW:utTB
END :61Tun
close curObject ^1iSn)&
deallocate curObject HHDl8lo
GO ]m
g)Q:d,
10、SQL SERVER中直接循环写入数据 *H"B _3<n
declare @i int @XLy7_}
set @i=1 *scVJ
while @i<30 4|buk]9
begin adCU61t
insert into test (userid) values(@i) }zkHJxZgE
set @i=@i+1 @L<*9sLWh
end "-e
\p lKj
小记存储过程中经常用到的本周,本月,本年函数 z>58dA@f
Dateadd(wk,datediff(wk,0,getdate()),-1) SQBa;hvgM
Dateadd(wk,datediff(wk,0,getdate()),6) `eE&