SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 II;Te7~
y7z( &M@
o)&"Rf
一、基础 GRT]aw
1、说明:创建数据库 3pSj kS|?>
CREATE DATABASE database-name */w7?QOv
2、说明:删除数据库 ydQ!4
drop database dbname wiJRCH
3、说明:备份sql server 56DoO'
--- 创建 备份数据的 device l$a?A[M$
USE master ! Z;T-3^.
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' U\jb"
--- 开始 备份 #op:/j
BACKUP DATABASE pubs TO testBack @QdnjXII*
4、说明:创建新表 +@ MPQv
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) s\gp5MT
根据已有的表创建新表: nO{ x^b <
A:create table tab_new like tab_old (使用旧表创建新表) nA_%2F'W}
B:create table tab_new as select col1,col2... from tab_old definition only {,?ss$L
5、说明:删除新表 7?J3ci\
drop table tabname byGn,m
6、说明:增加一个列 qsI^oBD"
Alter table tabname add column col type S`m,S4-eD
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 j13DJ.xu
7、说明:添加主键: Alter table tabname add primary key(col) R>2I RvY(
说明:删除主键: Alter table tabname drop primary key(col) 9 |.Ao
8、说明:创建索引:create [unique] index idxname on tabname(col....) BLn_u,3
删除索引:drop index idxname $.rzc]s
注:索引是不可更改的,想更改必须删除重新建。 R,t$"bOd
9、说明:创建视图:create view viewname as select statement S2K#[mDG
删除视图:drop view viewname sN~ \+_
10、说明:几个简单的基本的sql语句 c~imE%
选择:select * from table1 where 范围 ,%[4j9#!_
插入:insert into table1(field1,field2) values(value1,value2) "R[l ZJ@
删除:delete from table1 where 范围 E]I$}>k
更新:update table1 set field1=value1 where 范围 j*400
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! ?Go!j?#a
排序:select * from table1 order by field1,field2 [desc] aD9q^EoEs
总数:select count as totalcount from table1 Wd8Ru/
求和:select sum(field1) as sumvalue from table1 Gb2L }
平均:select avg(field1) as avgvalue from table1 6L9,'Bg
最大:select max(field1) as maxvalue from table1 *k [J6
最小:select min(field1) as minvalue from table1 &|9.}Z8U
h2~4G)J
9b"MQ[B4#a
UDEj[12S
11、说明:几个高级查询运算词 tfYB _N
_=EKXE)&}
C ^w)|2o}
A: UNION 运算符 5o)Y$>T0
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 8Pmdk1 ~
B: EXCEPT 运算符 0;<)\Wt=i9
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 3qaMO#{M
C: INTERSECT 运算符 .Z\Q4x#!Z
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 SeEw.;Xw
注:使用运算词的几个查询结果行必须是一致的。 $q_R?Eay
12、说明:使用外连接 %m&@o~+
A、left outer join: &~~wX,6+
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 &nj&:?w
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 "m$3)7 $
B:right outer join: "6CMA0R
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 KxzYfH
C:full outer join: `~#<&w
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 H8{ol6wc)6
Vh|\ _~9
A+getdr
二、提升 F;q#&
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Kibr ]w
法一:select * into b from a where 1<>1 Hfym30
法二:select top 0 * into b from a N&,]^>^u
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) fv!?Ga(
insert into b(a, b, c) select d,e,f from b; -/P\"c
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) .}B(&*9,v
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 X4|4QgY
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. :A1:
4、说明:子查询(表名1:a 表名2:b) ?r=jF)C<'
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) r(h`XMsU
5、说明:显示文章、提交人和最后回复时间 aEt/NwgiQ
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 5jB*fIz
6、说明:外连接查询(表名1:a 表名2:b) UUc8*yU)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ?jx1R^
7、说明:在线视图查询(表名1:a ) p-GAe,2q
select * from (SELECT a,b,c FROM a) T where t.a > 1; T;5r{{
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 #,d I$gY
select * from table1 where time between time1 and time2 c; 2#,m^
select a,b,c, from table1 where a not between 数值1 and 数值2 YW/QC'_iC
9、说明:in 的使用方法 he(A3{'
select * from table1 where a [not] in ('值1','值2','值4','值6') `=lc<T^
10、说明:两张关联表,删除主表中已经在副表中没有的信息 "N?+VkZEv
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) $za8"T*I
11、说明:四表联查问题: oU*45B`"
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 ..... G\de2Q"d:O
12、说明:日程安排提前五分钟提醒 r|u MovnV
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 FRu]kZv2
13、说明:一条sql 语句搞定数据库分页 ' o_:^'c
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 iB[~U3
14、说明:前10条记录 LJ)5W
select top 10 * form table1 where 范围 7!WA)@6
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) c yyVg!+
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 7*5Z
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 [* ?Awf`
(select a from tableA ) except (select a from tableB) except (select a from tableC) Z;/$niY
17、说明:随机取出10条数据 "pP^*9FrA
select top 10 * from tablename order by newid() ~`M\Ir
18、说明:随机选择记录 0'YG6(h
select newid() kE9esC3
19、说明:删除重复记录 !K
f#@0E..
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) xG&)1sT#-\
20、说明:列出数据库里所有的表名 G s+3e8
select name from sysobjects where type='U' Eow_WW;P
21、说明:列出表里的所有的 l
vMlL5t
select name from syscolumns where id=object_id('TableName') hCjR&ZA
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 L>yJ
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 W\&8auds
显示结果: x^4xq#Bb7
type vender pcs Qx;\USv
电脑 A 1 U4aU}1RKz
电脑 A 1 /='. 4v
光盘 B 2 (3J$>Na
光盘 A 2 (8.Z..PH
手机 B 3 .qMOGbd?
手机 C 3 3b' QLfU
23、说明:初始化表table1 7Mb#O_eh
TRUNCATE TABLE table1 8>,jpAN}r
24、说明:选择从10到15的记录 S"wR%\NIp
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 7(5xL T$
5[0
O'%$
y{dTp
.ZvM ^GJb
三、技巧 ![]``g2
1、1=1,1=2的使用,在SQL语句组合时用的较多 i;LXu%3\
"where 1=1" 是表示选择全部 "where 1=2"全部不选, &wD;SMr<
如: o(DOQ Gl
if @strWhere !='' h 3]wL.V
begin I)A`)5="5
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere n2)q}_d
end ]o cWt3|
else fFb_J`'ue
begin 3;S,3
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' [0"'T[ok
end Llr>9(|
我们可以直接写成 +qh[N@F
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere > ;/l)qk,
2、收缩数据库 28 8XF9B^
--重建索引 /"eey(X
DBCC REINDEX Jn{OWw2
DBCC INDEXDEFRAG .C 8PitS
--收缩数据和日志
f7m%|v!
DBCC SHRINKDB )+:EJH~
DBCC SHRINKFILE $5Xh,DOg
3、压缩数据库 6d_'4B
dbcc shrinkdatabase(dbname) Vx~,Uex0+
4、转移数据库给新用户以已存在用户权限 b0lq\9
exec sp_change_users_login 'update_one','newname','oldname' $2W%2rZ
go (p2K36,9m
5、检查备份集 UK<Nj<-'t
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' anXc|
6、修复数据库 0g0i4IV
ALTER DATABASE [dvbbs] SET SINGLE_USER ;W>k@L
GO l
c+g&f
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 9 FB19
GO -r-k_6QP
ALTER DATABASE [dvbbs] SET MULTI_USER u(fm@+$^
GO G1 vNt7
7、日志清除 0aG ni|
SET NOCOUNT ON rg^'S1x|
DECLARE @LogicalFileName sysname, e" St_z(
@MaxMinutes INT, j'A_'g'^
@NewSize INT dBz/7&Q
USE tablename -- 要操作的数据库名 7=;R& mqC
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 D9
g#Ff6
@MaxMinutes = 10, -- Limit on time allowed to wrap log. :]\([Q+a
@NewSize = 1 -- 你想设定的日志文件的大小(M) eEuvl`&
-- Setup / initialize Vh_P/C+
DECLARE @OriginalSize int i\,-oO
SELECT @OriginalSize = size 3j\1S1
FROM sysfiles ,6-:VIHQ
WHERE name = @LogicalFileName Wk)OkIFR
SELECT 'Original Size of ' + db_name() + ' LOG is ' + u6AA4(
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 5`~PR
:dN
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' x[a<mk
FROM sysfiles vN`klDJgW[
WHERE name = @LogicalFileName ibj87K
CREATE TABLE DummyTrans vX/T3WV
(DummyColumn char (8000) not null) A"L&a
l$i
DECLARE @Counter INT, gt@m?w(
@StartTime DATETIME, -*1J f&
@TruncLog VARCHAR(255) #qK:J;Sn3
SELECT @StartTime = GETDATE(), |y(Q
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' f&Gt|
DBCC SHRINKFILE (@LogicalFileName, @NewSize) }H^+A77v
EXEC (@TruncLog) KV(Q;~8"X
-- Wrap the log if necessary. >CHrg]9
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired lhy*h_>
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) ?l9XAWt\
AND (@OriginalSize * 8 /1024) > @NewSize D]zwl@sRX:
BEGIN -- Outer loop. nAv#?1cjz
SELECT @Counter = 0 aDU<wxnSvO
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) |?,A]|j
BEGIN -- update 1q7|OWFT
INSERT DummyTrans VALUES ('Fill Log') f4fvrL
DELETE DummyTrans N sXHO
SELECT @Counter = @Counter + 1 8WXQOo8
END MN\HDKN
EXEC (@TruncLog) >T^;MS
END jIJ~QpNE
SELECT 'Final Size of ' + db_name() + ' LOG is ' + t'n pG}`tE
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 2LF/H$]o5
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' `sn^ysp
FROM sysfiles fD[*_^;h)
WHERE name = @LogicalFileName HiZ*+T.B
DROP TABLE DummyTrans IxY|>5z
SET NOCOUNT OFF !|^|,"A)
8、说明:更改某个表 Mk"^?%PxT
exec sp_changeobjectowner 'tablename','dbo' |-:()yxs
9、存储更改全部表 GeH#I5y
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch v[1aWv:
@OldOwner as NVARCHAR(128), H#,W5EJzM
@NewOwner as NVARCHAR(128) ekWD5,G
AS | )K8N<n
DECLARE @Name as NVARCHAR(128) ~vm%6CABM
DECLARE @Owner as NVARCHAR(128) ]cHgleHQ
DECLARE @OwnerName as NVARCHAR(128) (C\]-E>
DECLARE curObject CURSOR FOR ^aItoJq
select 'Name' = name, h4fJvOk|!
'Owner' = user_name(uid) p`olCp'
from sysobjects lXW%FH6c+
where user_name(uid)=@OldOwner c"f-3kFv
order by name 6'k<+IR
OPEN curObject bRFLcM
FETCH NEXT FROM curObject INTO @Name, @Owner y%"{I7!A
WHILE(@@FETCH_STATUS=0) DX#Nf""Pw
BEGIN mE+*)gb:Rd
if @Owner=@OldOwner ~Y^+M*
begin Sc]B#/~B
set @OwnerName = @OldOwner + '.' + rtrim(@Name) +}Dw3;W}m
exec sp_changeobjectowner @OwnerName, @NewOwner xQ7l~O
b
end fDv2JdiU
-- select @name,@NewOwner,@OldOwner V5+=e^pa2
FETCH NEXT FROM curObject INTO @Name, @Owner <FV1Wz
END G#ZH.24Y
close curObject <sb~ ^B
deallocate curObject }bb;~
GO T<n
10、SQL SERVER中直接循环写入数据 Acez'@z
declare @i int b/+u4'"
set @i=1 G/)O@Ugp
while @i<30 6AAz
begin BtkOnbz8X
insert into test (userid) values(@i) 3#3n!(
set @i=@i+1 `V}q-Zdy
end X-bcQ@Oj
小记存储过程中经常用到的本周,本月,本年函数 r8`ffH
Dateadd(wk,datediff(wk,0,getdate()),-1) |mZxfI
Dateadd(wk,datediff(wk,0,getdate()),6) 0"jY.*_EW
Dateadd(mm,datediff(mm,0,getdate()),0) xG~P+n7t5$
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) ER%^!xA
Dateadd(yy,datediff(yy,0,getdate()),0) bV^rsJm
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) qU \w=
上面的SQL代码只是一个时间段 Q*D;U[
Dateadd(wk,datediff(wk,0,getdate()),-1) qqjwJ!@P
Dateadd(wk,datediff(wk,0,getdate()),6) `+]Qz =}
就是表示本周时间段. (p" %O
下面的SQL的条件部分,就是查询时间段在本周范围内的: 4>wP7`/+y
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) R$R *'l
而在存储过程中 !z\h|wU+
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) \1k79 c
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) Hus)c3Ty7