SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 h aAY =:
|?!i},Ki;
&W2*'$j"_
一、基础 3z8i0
1、说明:创建数据库 IO\4dU)
CREATE DATABASE database-name o:Fq|?/e
2、说明:删除数据库 !zA@{gvEc
drop database dbname UkL1h7}a\
3、说明:备份sql server YZol4q|ic
--- 创建 备份数据的 device y}?|+/ dN
USE master <`}P
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' Pxlc RF
--- 开始 备份 %O"8|ZG9{
BACKUP DATABASE pubs TO testBack mO>L]<O
4、说明:创建新表 Pyo|Sgk
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) dHnCSOM<
根据已有的表创建新表: I!sT=w8V
A:create table tab_new like tab_old (使用旧表创建新表) &$MC!iMh
B:create table tab_new as select col1,col2... from tab_old definition only n>Ff tVZNJ
5、说明:删除新表 C96/
drop table tabname R_!.vGhkN
6、说明:增加一个列 $YSXE
:
Alter table tabname add column col type 8z9{H
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 #{cy( &cz
7、说明:添加主键: Alter table tabname add primary key(col) aB,-E>+
说明:删除主键: Alter table tabname drop primary key(col) 5'zXCHt
8、说明:创建索引:create [unique] index idxname on tabname(col....) }Le]qR9Y]
删除索引:drop index idxname U$OZkHA[
注:索引是不可更改的,想更改必须删除重新建。 +,76|oMsQ%
9、说明:创建视图:create view viewname as select statement `b?uQ\#-M
删除视图:drop view viewname 4b;Mb
10、说明:几个简单的基本的sql语句 =oBpS=<7
选择:select * from table1 where 范围 KdVKvs[
插入:insert into table1(field1,field2) values(value1,value2) X6Ha C+P
删除:delete from table1 where 范围 02-ql
F@i
更新:update table1 set field1=value1 where 范围 vF72#BNs
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! kK? SG3
排序:select * from table1 order by field1,field2 [desc] PYkhY;*
总数:select count as totalcount from table1 #Bd]M#J17a
求和:select sum(field1) as sumvalue from table1 bZnOX*y]
平均:select avg(field1) as avgvalue from table1 5hrI#fpOR
最大:select max(field1) as maxvalue from table1 H"A%mrb
最小:select min(field1) as minvalue from table1 }3(!kW
XqTguO'
@62T:Vl
z(|^fi(
11、说明:几个高级查询运算词 5ya9VZ5#
')m!48
jP+yN|
A: UNION 运算符 28MMH
Q
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 &2tfj(ms
B: EXCEPT 运算符 9zp!lw~;+
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 &,nv+>D
C: INTERSECT 运算符 1QoW/X'>.
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 \[MAa:/
注:使用运算词的几个查询结果行必须是一致的。 i^s Vy
12、说明:使用外连接 S6~y!J6Ok4
A、left outer join: nS+Rbhs
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 @Cj!MZ=T
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 $RD~,<oEm
B:right outer join: ?cV,lak
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 zm_8a!.
C:full outer join: feej'l }F
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 2dn^K3
7({)ou x
@^,q/%;
二、提升 >ahDc!Jyu
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Y
;Ym=n'
法一:select * into b from a where 1<>1 GP}; ~
法二:select top 0 * into b from a c./\sN@
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) VvhfD2*T
insert into b(a, b, c) select d,e,f from b; 1Bh"'9-!JT
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ho\1[xS
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 fM=o?w6v
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. D!j/a!MaKk
4、说明:子查询(表名1:a 表名2:b) xl}rdnf}
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) RT[p!xL
5、说明:显示文章、提交人和最后回复时间 cx\"r
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b .;? Bni
6、说明:外连接查询(表名1:a 表名2:b) {U5sRM|I
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c pBsb>wvej
7、说明:在线视图查询(表名1:a ) dY1t3@E
select * from (SELECT a,b,c FROM a) T where t.a > 1; i5e10@Q{
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 o E+'@
select * from table1 where time between time1 and time2 'Y?-."eKh
select a,b,c, from table1 where a not between 数值1 and 数值2 X=)V<2WO
9、说明:in 的使用方法 bLc5$U$!I
select * from table1 where a [not] in ('值1','值2','值4','值6') CoN[Yf3\
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Al$z.i?R
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) %>|FJ
11、说明:四表联查问题: 6= ?0&Bx&
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 ..... ;_}pIO
12、说明:日程安排提前五分钟提醒 2#wnJdr6E
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 'xW=qboOp
13、说明:一条sql 语句搞定数据库分页 ;UdM8+^/V]
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 B,>02EZ
14、说明:前10条记录 V DFgu
select top 10 * form table1 where 范围 .LzA'q1+z
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) te@m#`p9
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) T;w:^XW
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 [,=?e
(select a from tableA ) except (select a from tableB) except (select a from tableC) }M07-qIX{
17、说明:随机取出10条数据 IwyA4Ak Ru
select top 10 * from tablename order by newid() b?~p/[
18、说明:随机选择记录 rj4@
select newid() <8r"QJY/
19、说明:删除重复记录 TvU
z^
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) +=tdgw/
20、说明:列出数据库里所有的表名 Wf~^,]9N
select name from sysobjects where type='U' w-|Rb~XT
h
21、说明:列出表里的所有的
@|gG3
select name from syscolumns where id=object_id('TableName') UHl3/m7g
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 !0{SVsc)
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 ]kj^T?&n.
显示结果: {*xE+ |
type vender pcs 4^7 v@3
电脑 A 1 )nm+_U
电脑 A 1 4n,&,R r#
光盘 B 2 K?.~}82c
光盘 A 2 &PMQ]B
手机 B 3
[gW eD
手机 C 3 a&s34Pd
23、说明:初始化表table1 kWzp*<lWe
TRUNCATE TABLE table1 n
Kkpp-
24、说明:选择从10到15的记录 k!c7eP"%8^
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc ~&?([}A
\@Wv{0a(
+t!]nE#
6-U_TV
三、技巧 9q;O`&
1、1=1,1=2的使用,在SQL语句组合时用的较多 !BQt+4G7
"where 1=1" 是表示选择全部 "where 1=2"全部不选, 9*"
如: -]3 K#M)s
if @strWhere !='' (HNc9QVC'W
begin pqG>|#RG
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere x@#>l8k?
end ?2@^O=I
else |rx5O5p
begin ;*%rFt9FK
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' %\'=Y/yP
end ;c 7I "?@z
我们可以直接写成 h,LSqjf"
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 5U84*RY
2、收缩数据库 U,rI/'
--重建索引 J(1Tl
DBCC REINDEX d)
-(C1f
DBCC INDEXDEFRAG jcCAXk055
--收缩数据和日志 b4L7M1l
DBCC SHRINKDB 196aYLE
DBCC SHRINKFILE "P !
.5B
3、压缩数据库 ,%pCcM)
dbcc shrinkdatabase(dbname) [@i:qB>B
4、转移数据库给新用户以已存在用户权限 BMp'.9Qgm
exec sp_change_users_login 'update_one','newname','oldname' yfl?\X{
go #Xg;E3BM
5、检查备份集 oW~W(h!
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
Zkp~qx
6、修复数据库 F^l1WX6
ALTER DATABASE [dvbbs] SET SINGLE_USER yi$CkG}
GO &xGdKH
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK {B$CqsvJ
GO 80nE QT
y
ALTER DATABASE [dvbbs] SET MULTI_USER LnR>!0:c
GO WwmYJl0
7、日志清除 'm<Lx _i
SET NOCOUNT ON =2!p>>t,d;
DECLARE @LogicalFileName sysname, 0cm34\*
@MaxMinutes INT, IMM;LC%rD9
@NewSize INT z5@XFaQ
USE tablename -- 要操作的数据库名 D]~K-[V?l
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 rWht},-|1
@MaxMinutes = 10, -- Limit on time allowed to wrap log. a`wjZ"}'[
@NewSize = 1 -- 你想设定的日志文件的大小(M) 3kxo1eb
-- Setup / initialize Sca"LaW1
DECLARE @OriginalSize int "uH>S+%|b
SELECT @OriginalSize = size 0i~U(qoI
FROM sysfiles l7QxngWw
WHERE name = @LogicalFileName J|WE&5'
SELECT 'Original Size of ' + db_name() + ' LOG is ' + +n1!xv]
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + y
4i3m(S
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' R ]Ev=V'U
FROM sysfiles :1 +Aj
(
WHERE name = @LogicalFileName @.;+WQE
CREATE TABLE DummyTrans }geb959
(DummyColumn char (8000) not null) ^4sfVpD2!
DECLARE @Counter INT, fD!c t; UK
@StartTime DATETIME, M`Y^hDl 6
@TruncLog VARCHAR(255) Nj9A-*0g6N
SELECT @StartTime = GETDATE(), H-_gd.VD
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' !Fl'?Kz
DBCC SHRINKFILE (@LogicalFileName, @NewSize) g*$2qKm
EXEC (@TruncLog) 12`u[O}\}-
-- Wrap the log if necessary. "#C2+SKM1
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 3Gs\Q{O:
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 3?o4
AND (@OriginalSize * 8 /1024) > @NewSize 2@S}x@^
BEGIN -- Outer loop. (Yewd/T
SELECT @Counter = 0 }UyQGRZ=
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ~kW?]/$h
BEGIN -- update +tPBm{|
INSERT DummyTrans VALUES ('Fill Log') <8WFaP3,
DELETE DummyTrans (3n "a'
SELECT @Counter = @Counter + 1 snaAn?I4
END |))NjM'ZBl
EXEC (@TruncLog) dN2JOyS
END NK|UeL7ght
SELECT 'Final Size of ' + db_name() + ' LOG is ' + GxdAOiq;
CONVERT(VARCHAR(30),size) + ' 8K pages or ' + &nEL}GM)E
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' |k.'w<6mb9
FROM sysfiles ]p! {
WHERE name = @LogicalFileName xXJ*xYn"}
DROP TABLE DummyTrans xsa`R^5/c
SET NOCOUNT OFF FWbp;v{
8、说明:更改某个表 .n<vhLDQn
exec sp_changeobjectowner 'tablename','dbo' $zP5Hzx
9、存储更改全部表 )Do 0
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch Pb&tWv\ql
@OldOwner as NVARCHAR(128), @^| [J
_4
@NewOwner as NVARCHAR(128) UhH#>2r_
AS 3aW<FSgP
DECLARE @Name as NVARCHAR(128) ImN'o4vo
DECLARE @Owner as NVARCHAR(128) B4.:
9Od3
DECLARE @OwnerName as NVARCHAR(128) ;UQza ]i
DECLARE curObject CURSOR FOR `Gio
2gl9
select 'Name' = name, H<d~AurX)J
'Owner' = user_name(uid) 7d;|?R-8D
from sysobjects HzTmNm)
where user_name(uid)=@OldOwner P&0eu
order by name w/|&N>ZOx
OPEN curObject K6DN>0sY
FETCH NEXT FROM curObject INTO @Name, @Owner 5Zq
hyv=
WHILE(@@FETCH_STATUS=0) %]+R>+
BEGIN "3RFyi
if @Owner=@OldOwner fZiAl7b!
begin J?O0ixU
set @OwnerName = @OldOwner + '.' + rtrim(@Name) 5/"$_7"{a
exec sp_changeobjectowner @OwnerName, @NewOwner (p>|e\(]0
end R XCn;nM4
-- select @name,@NewOwner,@OldOwner TIGtX]`
FETCH NEXT FROM curObject INTO @Name, @Owner $d*9]M4
END "\wMs
close curObject 3E*|^*
deallocate curObject (=j;rfvP
GO b~aM=71
10、SQL SERVER中直接循环写入数据 sF{~7IB
declare @i int %,\JTN|g|A
set @i=1 J?o
while @i<30 #RlZxtx.O
begin Q^b&
insert into test (userid) values(@i) "D'e
set @i=@i+1 wv9HiHz8gD
end !v}TRGX
小记存储过程中经常用到的本周,本月,本年函数 8^>qor.]M
Dateadd(wk,datediff(wk,0,getdate()),-1) aqON6|6K
Dateadd(wk,datediff(wk,0,getdate()),6) ) H,Xkex
Dateadd(mm,datediff(mm,0,getdate()),0) NWf=mrS8@$
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) }zGx0Q
Dateadd(yy,datediff(yy,0,getdate()),0) ed6@o4D/kf
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) re*}a)iL
上面的SQL代码只是一个时间段 =Dn<DV
Dateadd(wk,datediff(wk,0,getdate()),-1) !Se0&Ob
Dateadd(wk,datediff(wk,0,getdate()),6) .OdtM
Xy
就是表示本周时间段. yCxYFi
下面的SQL的条件部分,就是查询时间段在本周范围内的: D0Q9A]bD;
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) JLu$1A@ '
而在存储过程中 SA TX_
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) ~P|;Y<?3
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ?~o`mg