[数据库类(MYSQL/SQLIT)] 后端必备:15000 字的 SQL 语句大全

[复制链接]
查看2278 | 回复0 | 2020-9-19 21:45:17 | 显示全部楼层 |阅读模式

登录网站,浏览更多精彩内容

您需要 登录 才可以下载或查看,没有账号?加入我们

×
本帖最后由 nantong 于 2020-9-20 10:43 编辑 - ]6 O& [1 l3 _5 B3 ^6 G+ W

. \+ w' E/ @  U' W, n一、基础
8 j8 U; t' ~- Q. N1、说明:创建数据库CREATE DATABASE database-name2、说明:删除数据库drop database dbname3、说明:备份sql server--- 创建 备份数据的 device* g2 s  _  p, e6 k" W) S
USE master
& h) G/ s( g2 S3 p' T8 d) J+ qEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
4 T( t( \" G* c6 L--- 开始 备份, }' @: x! M3 Z/ x8 |/ p
BACKUP DATABASE pubs TO testBack
4、说明:创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表: A:create table tab_new like tab_old (使用旧表创建新表)3 H) n& D1 p. O2 m
Bcreate table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表drop table tabname 6、说明:增加一个列Alter table tabname add column col type:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。7、说明:添加主键:Alter table tabname add primary key(col)
( C- Q8 d' q6 W  ^! ]说明:删除主键:Alter table tabname drop primary key(col)
8、说明:创建索引:create [unique] index idxname on tabname(col….) 删除索引:drop index idxname注:索引是不可更改的,想更改必须删除重新建。9、说明:创建视图:create view viewname as select statement删除视图:drop view viewname10、说明:几个简单的基本的sql语句选择:select * from table1 where 范围; D9 C; l! R4 \: p& h
插入:insert into table1(field1,field2) values(value1,value2)6 L* e( `& U* g) o/ F6 R6 L' K& J
删除:delete from table1 where 范围更新:update table1 set field1=value1 where 范围) Z6 M  L9 H; ]* l7 _
查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
3 k5 R0 Q: n7 F% L% O2 h9 H2 x排序:select * from table1 order by field1,field2 [desc]  W, O2 ]7 Q- F# ?2 _3 ~0 B
总数:select count as totalcount from table1
9 s7 s. |; y+ O& W/ C  A求和:select sum(field1) as sumvalue from table1
2 Z* ^& |0 q% o* l4 C平均:select avg(field1) as avgvalue from table1
" u( z8 }+ o5 F% T- ~最大:select max(field1) as maxvalue from table1, S! D3 g: u  Z' q' @5 B
最小:select min(field1) as minvalue from table1
11、说明:几个高级查询运算词A:UNION 运算符 UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B:EXCEPT 运算符 EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。C:INTERSECT 运算符INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
, U. W$ V1 Q8 Z3 A# Z* h, m1 `注:使用运算词的几个查询结果行必须是一致的。
12、说明:使用外连接 A、left (outer) join左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cB:right (outer) join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full/cross (outer) join全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。12、分组:Group by:+ m4 i. S. x$ V1 B( L) d
一张表,一旦分组 完成后,查询后只能得到组相关的信息。组相关的信息:(统计信息) count,sum,max,min,avg 分组的标准)在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作:
4 X0 K* N5 x4 _分离数据库 sp_detach_db;附加数据库sp_attach_db 后接表明,附加需要完整的路径名; t4 J8 R0 j( o- R$ n3 `( H2 |
) n+ R. g8 v' h) z+ q% K% p
14.如何修改数据库的名称:sp_renamedb 'old_name', 'new_name' 二、提升 1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)4 ]0 V+ x% C# y: i1 l1 O

  H  s+ _4 z/ \( V2 s( N' f/ R法一:select * into b from a where 1<>1(仅用于SQlServer)法二:select top 0 * into b from a
& P' v+ n6 J# B# @) }
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
% [& [$ r; r' F0 a& O

3 J' A) x2 x# G" S1 T* A
insert into b(a, b, c) select d,e,f from b; 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)' a0 \8 d4 m0 I% r2 {2 p* @
# p1 z1 a2 B- m( R
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
8 b- Z/ q' j$ W3 A例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明:子查询(表名1:a 表名2:b)
7 s. Z& a4 n/ {

, k7 f9 U: L- C' O
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) 5、说明:显示文章、提交人和最后回复时间  e- Y5 U& W4 v2 r1 z
  H# m& v: C* e
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6、说明:外连接查询(表名1:a 表名2:b)' H$ U5 I8 y' S% A6 T

: D( `9 Q  A1 P7 @7 F# T
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明:在线视图查询(表名1:a )) q/ r% B8 E: ~  L: y9 q
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
& c) D. Y$ y  c. g: [/ D' i+ ?
! @6 a, x  o! |5 C) \
select * from table1 where time between time1 and time2
# m* b# `* m4 }4 l3 Bselect a,b,c, from table1 where a not between 数值1 and 数值2
9、说明:in 的使用方法3 p# N- x2 F. W8 Q$ o8 L

( A' W# P" P4 [# ]' M% V
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明:两张关联表,删除主表中已经在副表中没有的信息 9 q/ M, w' T# D# E* f) e* I
6 t' ]7 W! i' o$ F
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题:
7 G$ D1 f( |2 y7 ?# D. M
& ^( J8 p6 T: H6 j8 d0 ]# s+ E2 z
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 ..... 12、说明:日程安排提前五分钟提醒 - W# B  {/ I7 l5 }0 I4 |. u

! K7 E8 J6 ~, S5 |
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 13、说明:一条sql 语句搞定数据库分页
- e) ?! i0 z" N8 }$ H' H4 Tselect top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段( X: q, i/ r2 |2 k
具体实现:关于数据库分页:
+ b5 O2 [3 A' Q* F& T) @5 ddeclare @start int,@end int
6 b# u" z/ g( A  @sql  nvarchar(600)
3 k1 H2 F7 x+ ~( G  set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’4 P3 k7 W7 g. J1 L4 V& ~! z
  exec sp_executesql @sql
注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引) 14、说明:前10条记录+ c7 @; {6 `9 ?  ?/ e" B: m
8 w0 }3 R8 J8 j0 f. ?5 O/ p
select top 10 * form table1 where 范围 15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)6 }/ V3 f( J. ~$ g) l: H
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明:包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表
4 Y( j, h" z( u5 p4 @
(select a from tableA ) except (select a from tableB) except (select a from tableC) 17、说明:随机取出10条数据% i3 Z4 K" t9 i8 }1 o# W1 U& A
" p) s6 s  e8 I8 d
select top 10 * from tablename order by newid() 18、说明:随机选择记录
$ r$ c4 _' H' B6 R4 j
5 X2 P$ j' R5 G% X: S
select newid() 19、说明:删除重复记录; C9 y5 ~, @- A7 H$ p2 j! k+ q
: k  t+ z* o2 W6 q6 P. X
1) delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
* a! L+ ]* v2 l5 ?- ~9 Z. W2) select distinct * into temp from tablename" R4 F$ o$ B7 P
  delete from tablename
; v. i5 L% c" B3 ^  insert into tablename select * from temp
- e$ B) h5 R& C- E3 G. i5 L. B/ F
评价:这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段 2 R" L% C! r+ ?. u6 }
alter table tablename
2 h* T0 Y, R. o# r! O--添加一个自增列2 k* D: e. K. M4 T6 K
add  column_b int identity(1,1)
8 M) n6 B' Z- x, B  G  T delete from tablename where column_b not in(" I* J% x3 E4 f% G8 W
select max(column_b) from tablename group by column1,column2,...)
( G/ Q7 h# @) q+ t3 Z) [/ d. C4 [7 Ralter table tablename drop column column_b

3 h) k  _3 o8 @, p- d- K# P20、说明:列出数据库里所有的表名
+ V% `- q4 B& f1 `+ g+ J& s" a9 F
select name from sysobjects where type='U' // U代表用户 21、说明:列出表里的所有的列名+ `! L3 y$ [! y! y; V! D9 x2 f9 b7 k
select name from syscolumns where id=object_id('TableName') 22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。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+ B% I% i2 y  {3 _7 y" x$ `1 g
显示结果:
; H9 @9 R, r( |) |type vender pcs2 t* B1 i% S; l7 p8 o1 F
电脑 A 19 ?. s) d" M2 f3 @* W% [
电脑 A 1
) \' B5 q4 h/ d# H  ^8 |光盘 B 21 a4 G, K* l7 {6 n
光盘 A 2* Y- K4 \  e. Y; m
手机 B 32 e" S6 C/ q, F9 A& W$ j
手机 C 3
23、说明:初始化表table1 TRUNCATE TABLE table1 24、说明:选择从10到15的记录
/ Z% a# M. A- j# t1 Z
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 三、技巧 1、1=1,1=2的使用,在SQL语句组合时用的较多 “where 1=1” 是表示选择全部    “where 1=2”全部不选,
  r; v4 A7 s6 R如:
if @strWhere !=''
7 x7 Y, B9 O: W! q; k8 Q# ubegin5 x4 H; B- x7 ]: c2 v5 o/ o8 s
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
5 P) D- Y5 u) i6 `1 w1 V/ B- D7 Qend+ I1 ~- R2 b) o2 l; b
else 1 q. `/ [& N8 s6 b: A- r
begin$ Z4 p  y! `) m3 v/ ], l7 R4 C% N* A
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
, f* k$ ^* a" [6 p+ M8 @, H# g  Jend
* i+ f$ @  M0 ^" ]- k" {
我们可以直接写成 set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere % S  I/ A" }3 n5 S# Y2 [# R
2、收缩数据库--重建索引
. r+ G/ l+ O8 I* _: i' M6 ADBCC REINDEX9 w- [' b2 B1 P
DBCC INDEXDEFRAG
- S' ]. t9 ^& s2 l- ^5 C--收缩数据和日志
( C  d3 s" d, W8 h  `3 pDBCC SHRINKDB! |" @% m  [( A7 ^
DBCC SHRINKFILE 3、压缩数据库dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限exec sp_change_users_login 'update_one','newname','oldname'( ^) o( E, A: V
go 5、检查备份集2 U  [/ j* r4 x, N9 s
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 6、修复数据库ALTER DATABASE [dvbbs] SET SINGLE_USER4 R0 d- S- m' j: F' u% }
GO
% v+ w+ n# P1 g2 Y) ZDBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
6 q9 Z6 Y  f* t% L, ?0 `5 \9 \GO
: T0 @  f7 V/ |/ j/ S! [# i# O1 {ALTER DATABASE [dvbbs] SET MULTI_USER
4 a. d- m; E7 R) w6 O; y2 LGO 7、日志清除SET NOCOUNT ON- o- J7 ~" a- x
DECLARE @LogicalFileName sysname,+ r3 P# K' ^& F# x% `5 [
@MaxMinutes INT,. ?: V6 u3 L# a) @
@NewSize INT USE tablename -- 要操作的数据库名7 x, V+ `0 }9 U/ C* C0 k
SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
% w3 g7 l5 F1 U@MaxMinutes = 10, -- Limit on time allowed to wrap log.) i+ J9 C3 Y/ U& y) m
@NewSize = 1  -- 你想设定的日志文件的大小(M) Setup / initialize
, n6 V  C; X! @9 s* i7 ?DECLARE @OriginalSize int8 ^& Z9 b2 R3 w% X
SELECT @OriginalSize = size
- C) j3 [0 A: ^- M9 O FROM sysfiles' c; y7 r8 e: ]* h  h
WHERE name = @LogicalFileName( a& J+ H' J- J0 M% [2 Y: @) E; o
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
' W5 [$ ]" n7 p, [! j' b& j% S CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 5 A" }+ n" p$ @. I; L4 {# H! p
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
0 Q/ {/ j) Y4 @, z FROM sysfiles2 z3 K+ T# h) e* l( P9 A5 w( i6 ~$ ^
WHERE name = @LogicalFileName
5 A- [2 G9 z2 U/ y$ bCREATE TABLE DummyTrans- e, D) I% P  ^# q. i8 q2 X
(DummyColumn char (8000) not null) DECLARE @Counter    INT,
- l+ V$ @0 {: S4 X" k @StartTime DATETIME,( h; Q( D' _, k$ D
@TruncLog   VARCHAR(255)- b3 j) ~! D3 ?5 f. N- H% Y: @3 A7 p
SELECT @StartTime = GETDATE(),
9 `* m4 j) N) X) K @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (@LogicalFileName, @NewSize)
3 m3 I* F& l1 \( |/ Y5 ^2 W/ ^# k  XEXEC (@TruncLog)
, J" s2 ]- c* b6 T8 q, Q-- Wrap the log if necessary.
$ f% i2 G9 v2 C5 W0 [" |WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
" {# A3 Y7 k7 j2 `1 b AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  " ~" n  f5 j" j: f% i" f
AND (@OriginalSize * 8 /1024) > @NewSize  5 L2 R% t# {! E" \& u
BEGIN -- Outer loop.* Q) o$ M$ l# t# o3 I( y% r1 _
SELECT @Counter = 0
5 p5 ?3 A& C0 d4 c! m- l2 w WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
: C( M2 h6 J" m0 ?, ?" C2 h BEGIN -- update
' z$ l( {/ c3 A4 R: d INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans9 o' i' K, A" ?+ n0 ~
SELECT @Counter = @Counter + 1
' j9 }3 t! {! Z& L. X END, t1 l& C" b) Q* V$ P
EXEC (@TruncLog)  
1 @1 d2 V. T" w" e END4 h; i3 @! c2 ~
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
2 J) b) S8 [3 X) z2 t' m CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
' }' n, s2 _! T1 _ CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
  n- O7 u3 y- X3 g FROM sysfiles & b/ l. n. R: ?' {$ Z7 Y& `% Y
WHERE name = @LogicalFileName6 |4 a  v6 c1 a7 ]8 `. P9 ]
DROP TABLE DummyTrans/ e4 t+ h# }( t9 J/ |
SET NOCOUNT OFF 8、说明:更改某个表exec sp_changeobjectowner 'tablename','dbo' 9、存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch! [- P' u; {4 P9 K/ |+ }) k& r2 {
@OldOwner as NVARCHAR(128),
* F: Z, D% r8 E; _@NewOwner as NVARCHAR(128)
" s$ ?1 U- H& ZAS DECLARE @Name    as NVARCHAR(128)
$ G  p1 i8 y) xDECLARE @Owner   as NVARCHAR(128)/ u! C2 B4 @) s) j" y
DECLARE @OwnerName   as NVARCHAR(128) DECLARE curObject CURSOR FOR 5 G) c5 V% y* s, Q8 s) X
select 'Name'    = name,
# H( L" o2 e0 b( g+ k: ^  q   'Owner'    = user_name(uid)' |0 |" C6 E$ ?/ k  o! G1 k  e
from sysobjects2 _# Q* z+ \6 ^) q3 j
where user_name(uid)=@OldOwner
" J0 ]' d1 D4 F) z# f! {0 B) i; Rorder by name OPEN   curObject
* R  i3 {" A" X; t) o2 O7 m$ Q! MFETCH NEXT FROM curObject INTO @Name, @Owner
; c7 }# Z  N, ?6 Z* Q. _8 zWHILE(@@FETCH_STATUS=0)* P  P  }% ?( Y0 A- p, y
BEGIN     1 E3 _4 t- @& \9 r
if @Owner=@OldOwner 0 q2 N- r: }% g7 ^" r
begin
: N6 Z3 t+ j, I5 s* U( q2 h" G   set @OwnerName = @OldOwner + '.' + rtrim(@Name)' d$ x8 D3 m, Q1 e$ w; ^& i8 P
   exec sp_changeobjectowner @OwnerName, @NewOwner
& _& j5 w. ?' Z1 Hend8 C# @3 I0 p% J" j5 ~
-- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner
: C1 N" ]) y, a* v6 F3 k: V+ JEND close curObject
; z6 f  p: ?: R" Cdeallocate curObject4 A" a! q( G5 R* b$ H
GO 10、SQL SERVER中直接循环写入数据declare @i int
/ y: u* k2 J$ A% D0 gset @i=13 ~' s: V; S7 A/ F6 E  Q
while @i<30* y, A: E) F, {
begin  e( S: O! X3 j+ Y/ O6 v; [
    insert into test (userid) values(@i)
* F+ n  |( F0 P    set @i=@i+1; e9 x, e( r8 a4 W9 F, F$ |
end+ B% e8 o% `7 K1 B
案例有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:Name   scor: G' I; n$ \2 l  T
Zhangshan 80; M2 V  S( M' _" A
Lishi 59: B: `/ ~0 L2 a8 v! \( i) M- L
Wangwu 50
+ q# {& e7 S0 g# X+ d7 ^- jSongquan 69while((select min(score) from tb_table)<60) beginupdate tb_table set score =score*1.01where score<60if  (select min(score) from tb_table)>60  break else    continueend* s. Z5 n$ K3 Z
数据开发-经典 1.按姓氏笔画排序:8 W* ~) E0 Y1 X! B
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多 2.数据库加密:
: p7 a8 g$ w( Hselect encrypt('原始密码')3 d# i2 @8 f3 M- C7 N. C, F
select pwdencrypt('原始密码'), P9 O$ T6 }, O+ W9 L; s5 a
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')' n9 D. g* w5 ]; ~
select pwdencrypt('原始密码')
9 W2 S5 V, x0 ^3 ]1 fselect pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 3.取回表中字段:declare @list varchar(1000),. l& k# \8 `! M$ `- }# i
@sql nvarchar(1000) 4 ]! g" X0 Q. q, }3 h. l# e
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'# g1 y; v7 m6 k* F+ g
set @sql='select '+right(@list,len(@list)-1)+' from 表A' % }7 x3 X0 A- t9 N) o9 v
exec (@sql) 4.查看硬盘分区:EXEC master..xp_fixeddrives 5.比较A,B表是否相等:if (select checksum_agg(binary_checksum(*)) from A)( L: F% e9 ?" o! \: L4 g2 y! U3 ~) C
     =% V5 N8 U) i4 E. ^9 c
    (select checksum_agg(binary_checksum(*)) from B)+ j) ?; D# }& p3 W4 H" _6 [
print '相等'6 @( O# J6 _) o
else( Y% P5 ~) j/ E5 S
print '不相等' 6.杀掉所有的事件探察器进程:DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses  |; J. F; D/ |! z
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')! z1 _2 J& T$ s
EXEC sp_msforeach_worker '?' 7.记录搜索:# p1 B" O1 g8 T! h# `3 c2 k1 I- E
开头到N条记录Select Top N * From 表-------------------------------& @7 Y  i4 ~4 H; u$ `3 z
NM条记录(要有主索引ID)2 h" g+ n' g  \$ y( X
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc' R) s& r; K6 ~$ F2 _2 z/ T
----------------------------------% T. W! g  i# H7 R
N到结尾记录Select Top N * From 表 Order by ID Desc
+ T: m5 ?0 x$ f; ]案例例如1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句, 找出表的第31到第40个记录。  select top 10 recid from A where recid not  in(select top 30 recid from A) 分析:如果这样写会产生某些问题,如果recid在表中存在逻辑索引。 select top 10 recid from A where……是从索引中查找,而后面的select top 30 recid from A则在数据表中查找,这样由于索引中的顺序有可能和数据表中的不一致,这样就导致查询到的不是本来的欲得到的数据。 解决方案 1,用order by select top 30 recid from A order by ricid 如果该字段不是自增长,就会出现问题 2,在那个子查询中也加条件:select top 30 recid from A where recid>-1 例2:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。% I- h: f9 q! ^  }% e
set @s = 'select top 1 * from T   where pid not in (select top ' + str(@count-1) + ' pid  from  T)'
print @s      exec  sp_executesql  @s 9:获取当前数据库中的所有用户表( w+ T% v0 K9 }/ S
select Name from sysobjects where xtype='u' and status>=0 10:获取某一个表的所有字段select name from syscolumns where id=object_id('表名') select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名') 两种方式的效果相同 11:查看与某一个表相关的视图、存储过程、函数' v  E# e$ k2 z9 H( |( _# C, w
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 12:查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype='P' 13:查询用户创建的所有数据库
: I' M6 R, b& ?. W  w5 qselect * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
; T1 ]4 G+ r! Z' |1 n( T或者, [/ q# G& B  k1 A
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 14:查询某一个表的字段和数据类型select column_name,data_type from information_schema.columns
6 c% A% H) i! e! u' lwhere table_name = '表名' 15:不同服务器数据库之间的数据操作  w% V, U7 ~( `7 Z9 g
--创建链接服务器
  o2 x+ J9 j) A5 o
6 L1 n6 S- _# v* z8 R4 dexec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
0 m- @! D1 o& Z# ~3 x5 I' U- x+ |/ N! E/ i* q
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
' n* X9 }% y. [6 B( c
; P6 x5 |' V7 @--查询示例
+ C# ^; p0 M% B& p8 T7 I" N5 u5 O
select * from ITSV.数据库名.dbo.表名1 N. N4 T. x/ O' l& A, X

6 r9 f3 u% H* Q5 Y* p--导入示例% _5 D( Q+ I0 N9 D9 K/ o/ x! `
; L* X" a; O% F6 n! ]
select * into 表 from ITSV.数据库名.dbo.表名
2 \/ {8 Z" V; }  I0 e; l
2 q+ q2 @- y  Y2 n2 N, y--以后不再使用时删除链接服务器3 @0 F' ?3 n6 C

0 ]$ d6 H. J- O- d( @2 Fexec sp_dropserver 'ITSV ', 'droplogins '
8 K* r8 C4 n/ l. H/ N/ a
0 ^( ]0 M1 C2 T% R4 I8 ]. `, L--连接远程/局域网数据(openrowset/openquery/opendatasource)! G" ]5 l) p( W; U% P

3 o- }- g& k2 x--1、openrowset
- X- z/ U. B$ `% r: H" K( [
7 T* R& I9 K# n* q7 S7 Z, p--查询示例) n' J' u- Q% L" k

0 O& x$ q" R* K% K8 Gselect * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)5 ]- U; r' F. P% h+ A

. ]$ ?2 c" x$ W0 }8 S) g" n9 P--生成本地表
) ~; P/ d- ^( h& P* T  E1 e
( O& i0 a9 ?  J/ ^0 C% iselect * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)8 H" }2 Z" ?( g% b

; L$ a& M3 B/ H- [  W( G--把本地表导入远程表
3 C, W: E+ i2 C2 s6 c) G) o; _9 M- q- b7 o, y1 q. p( o
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
: T6 `0 C' M- I! A' }% f
; T" S' w/ O2 I( m' \- Qselect *from 本地表
  J+ O5 l7 A* v: d8 S7 B" e# t5 p" j
--更新本地表
( I1 }& m# R. V# T( \update b4 Y; A; S# o  A! z% q% K
set b.列A=a.列A& N) s: Q& _, n9 i( {- b
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
& G' I! X& T( d& g& S2 aon a.column1=b.column1! q, C* }: x+ T8 d: a3 ^! g
--openquery用法需要创建一个连接5 v' }1 B9 S5 \  a' k. T/ d  }. U$ g
--首先创建一个连接创建链接服务器" B' ?( [2 O/ C9 g( j) Z! N
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '+ P; e5 E' k2 q' f- s$ ^9 l% a
--查询" D& }+ z: a4 |: }- W
select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
( z5 _; V$ R0 z; y, M--把本地表导入远程表
% m/ T) f: J& j$ v% D/ H! iinsert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
; f- p& K2 B; k1 O9 W: gselect * from 本地表
( {! r$ [' D* b( l' M6 l5 v--更新本地表$ {8 w+ q& F) ]
  1. update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A2 J8 F, s1 p" R2 t
  2. ' I6 ?5 T) A1 S( V
  3. --3、opendatasource/openrowset' U  P- Q/ V0 c. t1 F
  4. 4 n% q8 g8 D2 ^
  5. SELECT   * FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta; M4 x. q9 [  Y& h* `

  6. / \  n! N# D# D
  7. --把本地表导入远程表; Q" N2 O" G8 H* H4 X7 s
  8. - J! T6 I$ [2 H! E( G
  9. insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名* c0 I+ f+ Z; h& B. t
  10. 4 _8 R/ u- b: M& |! s: O
  11. select * from 本地表
复制代码
update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A, C4 V% d8 \6 Q1 B. N  ^
--3、opendatasource/openrowset2 F8 n* I; I0 g* [2 [5 X) |
SELECT   * FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta4 Q5 j+ I$ n8 T+ T" a/ D' G
--把本地表导入远程表
  p! \  @2 ]" j* L" D: ?insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
4 I7 q8 P- T) \/ y" y% wselect * from 本地表
中国领先的数字技术资源交流中心!
您需要登录后才可以回帖 登录 | 加入我们

本版积分规则

238

主题

46

回帖

9300

积分

版主

积分
9300
学费
8227