请选择 进入手机版 | 继续访问电脑版
    查看: 87|回复: 0

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

    [复制链接]

    179

    主题

    206

    帖子

    7475

    积分

    版主

    Rank: 7Rank: 7Rank: 7

    积分
    7475
    学费
    6603
    发表于 2020-9-19 21:45:17 | 显示全部楼层 |阅读模式

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

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

    x
    本帖最后由 nantong 于 2020-9-20 10:43 编辑
    ) |, V7 J7 h% n, E9 a. ~
    : l( l2 s7 ?3 D7 [) t, `1 Z6 g一、基础+ |8 ]$ ^8 q- p+ c; V8 O  _# |
    1、说明:创建数据库CREATE DATABASE database-name2、说明:删除数据库drop database dbname3、说明:备份sql server--- 创建 备份数据的 device$ j. Q" y5 C9 I, i6 e$ ^. s7 f
    USE master3 ^. @* x; e9 J
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
    4 k( ]' h% q- r--- 开始 备份7 `7 d& N# [5 @6 @3 F
    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 (使用旧表创建新表)& R, N' U; s, a5 m# x5 S: G
    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)
    " \5 B/ i( M2 ]4 @& V9 M8 p* z说明:删除主键: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 范围2 J3 P5 e/ n4 ^) N1 \, l% U! U# p# W
    插入:insert into table1(field1,field2) values(value1,value2)% e* F! R# K3 w3 M# B8 o, r. X1 t- r
    删除:delete from table1 where 范围更新:update table1 set field1=value1 where 范围
    6 e, Z8 v3 U0 ?& c查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
    - s( d0 T( X+ T. y排序:select * from table1 order by field1,field2 [desc]; l$ q3 k: Z2 |$ Q
    总数:select count as totalcount from table1
    $ Z% V' F$ e6 i4 l' `求和:select sum(field1) as sumvalue from table1
    5 f6 {# @% ]1 T/ e$ `平均:select avg(field1) as avgvalue from table1
    5 K& i7 n, X' z: L( d: j最大:select max(field1) as maxvalue from table1  q- u1 R& t$ O* G" O# z
    最小: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),不消除重复行。
    $ p& q# C% r" H0 D7 d4 _注:使用运算词的几个查询结果行必须是一致的。
    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:
    , ^2 L3 F" |# W; ~9 b
    一张表,一旦分组 完成后,查询后只能得到组相关的信息。组相关的信息:(统计信息) count,sum,max,min,avg 分组的标准)在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作:5 T, D: i4 l3 W4 G& `4 y
    分离数据库 sp_detach_db;附加数据库sp_attach_db 后接表明,附加需要完整的路径名
    + `+ c5 ^; L. b. z% ]
    5 P# Q6 ^! ]" O6 U* ~14.如何修改数据库的名称:sp_renamedb 'old_name', 'new_name' 二、提升 1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)4 k& k$ L' x0 ]4 }0 A0 S5 ]" s
    5 d* ~' V5 R- `1 K" [- N
    法一:select * into b from a where 1<>1(仅用于SQlServer)法二:select top 0 * into b from a6 m0 d" d8 h! n) V- O  e
    2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
    . o- ~% _- X8 D2 }; ^. P
    ' |* {  B9 _* j( D+ y
    insert into b(a, b, c) select d,e,f from b; 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)7 V0 |4 T/ }! a8 n

    5 b: M# Q5 r2 ^: g2 a4 \( A( G
    insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件- W/ y: h9 A* ~0 S
    例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
    4、说明:子查询(表名1:a 表名2:b); Q3 f5 i8 C  L! J, o$ X
    " I. w8 J; i+ N8 T. b$ |! x
    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、说明:显示文章、提交人和最后回复时间
    8 H1 C' u; Y+ X0 a0 a/ w& O0 T

    " Q0 T+ O+ J* `) z) o
    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)
    9 K( s) u& i2 k1 ?8 X

    " B4 @. M7 ~9 ]# l
    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 )
    ! B1 |& {# ?5 T/ B, d/ J& c9 f
    select * from (SELECT a,b,c FROM a) T where t.a > 1;
    8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
    1 G1 k2 i( w9 G! {5 q! ?
    + j5 ?( N: K* |5 [, o) Z. ?, N
    select * from table1 where time between time1 and time2
    # {, J7 n: _  c3 }% T% D3 h+ U6 hselect a,b,c, from table1 where a not between 数值1 and 数值2
    9、说明:in 的使用方法% V7 e/ b- [* ?& N( W" ~: P4 V
    - n$ D, K  [" E* b
    select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明:两张关联表,删除主表中已经在副表中没有的信息
    3 m% i# _9 o3 u* I% C- }3 {
    ; r' x2 [8 q% b, N% q+ X
    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明:四表联查问题:: h" ]* h  D7 _% _) o, Q

    2 L* @  Z% i( ]1 ~' V
    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、说明:日程安排提前五分钟提醒
    " d* i  i: B3 w! s% @% M/ e# G
    5 \  r$ C/ Z7 j4 e
    SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 13、说明:一条sql 语句搞定数据库分页0 k9 q, p  w2 ^1 p# {  n; X
    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
    ( J1 h( U% U3 J6 q具体实现:关于数据库分页:5 c% b0 N# b: A5 A; Z2 q6 T
    declare @start int,@end int: w- S/ H/ |+ ]) Y# v6 V$ c
      @sql  nvarchar(600)
    , i, L0 Z! }; c$ R9 F  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)’( B  f& G: Y% l, S2 I
      exec sp_executesql @sql
    注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引) 14、说明:前10条记录
    ! y0 u/ h4 X7 x1 u7 z1 x

    % K1 J9 S7 V& Kselect top 10 * form table1 where 范围 15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)' p" ?! z3 l) _
    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明:包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表
    ( ?& s! C  v0 V9 p3 H
    (select a from tableA ) except (select a from tableB) except (select a from tableC) 17、说明:随机取出10条数据
    $ [; L1 p* l0 f, i
    & Q6 d- S. i) f
    select top 10 * from tablename order by newid() 18、说明:随机选择记录! A  ~# B/ U2 x! K+ O- O9 c4 d

    % }3 f- U7 L. E# h# j- ?. Z
    select newid() 19、说明:删除重复记录
    6 P4 c, V! a+ _. V2 B5 p

    * S: l9 q" o8 W1) delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)4 [- A0 l! K$ N% x
    2) select distinct * into temp from tablename
    5 y& ]6 m  c4 e. g- s5 i  delete from tablename) M+ O0 M, U: Z, C/ U- r, L
      insert into tablename select * from temp
    . i+ R6 s- K9 \( ^2 s" t' I
    评价:这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段
    / v" i+ C! i2 z
    alter table tablename$ \/ L+ A. n5 Z
    --添加一个自增列
    / c: ?# k$ s" qadd  column_b int identity(1,1)
    4 x' x4 A) M8 o. B1 U. X delete from tablename where column_b not in(
    6 c4 e( q  O3 I, rselect max(column_b) from tablename group by column1,column2,...)0 p8 o) [8 C  ~. Z+ R8 ~! ~' F$ G1 I; d
    alter table tablename drop column column_b
    ) M+ ]5 V- R1 d+ J; _" m
    20、说明:列出数据库里所有的表名6 _) [5 z! }$ t- a" q7 V
    select name from sysobjects where type='U' // U代表用户 21、说明:列出表里的所有的列名- r7 _" U# g) R: M# V2 Y+ `' n
    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+ t4 U4 P% x( a3 E8 _1 x
    显示结果:
    4 @9 N! l9 v, q! h0 ~7 [& ~type vender pcs
    . s" J1 ?! z# \0 p7 `8 Z/ U
    电脑 A 18 j; W0 F1 ?1 e- e. a: h- p
    电脑 A 16 ?( w6 U5 V& J
    光盘 B 2
    & Q$ _2 V- m3 f5 m光盘 A 2
    , i( m7 X0 n. G! G$ H% l手机 B 3) w7 a) x& k  O. ^! X
    手机 C 3
    23、说明:初始化表table1 TRUNCATE TABLE table1 24、说明:选择从10到15的记录
    ' X0 Q! U; J6 r) f
    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”全部不选,
    8 u- o/ D: I' \; m如:
    if @strWhere !=''
    % O8 j" _- x" M! N5 ybegin' B/ r: t& y: [
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere+ c7 F3 M/ P3 h3 \, D: l3 y# K
    end
    0 X; ?9 ^/ n# D% ]$ n/ C) k. pelse / \! w& h$ p8 S3 G7 D7 I
    begin' ~9 ?* k: {6 R- T
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 9 a  |* v. X6 Q
    end

    ) L( h7 @# O+ U; e1 D% d我们可以直接写成 set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 6 l) }0 ?3 v* o+ T" u
    2、收缩数据库--重建索引
    ( D4 v% ^) N) N3 Q7 ?( LDBCC REINDEX0 ]- v" W* v  M5 w* x
    DBCC INDEXDEFRAG
    2 I# Y# N) p% m0 B$ A3 c--收缩数据和日志
    * C4 j0 A) v$ SDBCC SHRINKDB8 v  t& [' r! `
    DBCC SHRINKFILE 3、压缩数据库dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限exec sp_change_users_login 'update_one','newname','oldname'
    5 f) q# \" ~) l% n) Z1 |go 5、检查备份集
    * N9 j; L* U7 x, N) I
    RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 6、修复数据库ALTER DATABASE [dvbbs] SET SINGLE_USER
    7 a. }! E# c) f6 Z  p& xGO
    3 N& y1 y6 P! Q; I$ t: G$ kDBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK/ @+ g: p; L; @0 R
    GO* d% P/ H. s& S% ^$ L' Y
    ALTER DATABASE [dvbbs] SET MULTI_USER; d. Z* Y. P" d, g) i# r
    GO 7、日志清除SET NOCOUNT ON7 h5 r# X" I- f
    DECLARE @LogicalFileName sysname,
    % Z2 e/ Z. k: g! M6 [ @MaxMinutes INT,! ~6 h# {9 T' c* ~( A
    @NewSize INT USE tablename -- 要操作的数据库名( N* c+ {  Q  y/ X- j4 A
    SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
    # K( s5 w, a. ?- F5 Y3 W- Q@MaxMinutes = 10, -- Limit on time allowed to wrap log.
    ( u, N) \6 z& r* o- O! C/ U5 i @NewSize = 1  -- 你想设定的日志文件的大小(M) Setup / initialize
    , U  g& o( C- n) XDECLARE @OriginalSize int
    4 U4 |2 c- a# ~+ Q! I$ h0 N7 d' _SELECT @OriginalSize = size 0 u+ p) P1 @7 Y( c6 p# Z
    FROM sysfiles1 R3 i, `6 D9 \7 }/ i2 `* Y
    WHERE name = @LogicalFileName
    , Z& K8 \0 Y9 f' E, l7 ]1 Y% hSELECT 'Original Size of ' + db_name() + ' LOG is ' +
    " z( L3 K& U2 s0 @ CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + * m) u. H1 z! f+ b( a+ P
    CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
    3 z. }4 l% n& e3 e7 [  J' A FROM sysfiles
    & v% z7 @6 c2 j* V: n# f2 X WHERE name = @LogicalFileName
    9 d' O) n3 i9 G# p* W' L: P  i% RCREATE TABLE DummyTrans. y6 x/ V- T  r  |
    (DummyColumn char (8000) not null) DECLARE @Counter    INT,
    ( d6 j# ^( @8 h% S& R @StartTime DATETIME,
    * q( z, B3 |/ K' k6 b' q/ l @TruncLog   VARCHAR(255)
    % o+ g% V) Q' g* r" ?' }SELECT @StartTime = GETDATE(),
    . e+ W% ?; T' ` @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (@LogicalFileName, @NewSize)
    - w3 B/ E" a% s' XEXEC (@TruncLog)6 A, r# ?3 T8 [9 F4 y- G1 B- O3 V+ M
    -- Wrap the log if necessary.& B' c/ B& z: A2 ~+ B6 b2 O+ K. l
    WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired* ^( i' `0 _+ V0 q# \  a
    AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
    * a6 u9 z7 C7 S AND (@OriginalSize * 8 /1024) > @NewSize  4 j. C9 _6 E2 r3 }  U2 O1 j
    BEGIN -- Outer loop.5 V) F- h, i* P& g' m3 K
    SELECT @Counter = 0# W. D8 u0 L$ ^$ q; V# n
    WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))! u; X( v" C- Z0 M0 p7 C) \
    BEGIN -- update
    ; M5 f- e9 f1 v* a INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
    0 X! g) `! V# {# S SELECT @Counter = @Counter + 1  |; t2 J7 `# s# h
    END+ u3 E1 }' ~% k' |- ^" P
    EXEC (@TruncLog)  8 V1 \4 ], d: E* {0 m8 X. U$ N% b
    END
    0 K6 t1 e. P2 B1 T# uSELECT 'Final Size of ' + db_name() + ' LOG is ' +. _! I7 g* r. Z  e# G
    CONVERT(VARCHAR(30),size) + ' 8K pages or ' + - O5 G* n4 J9 J1 o6 Q3 h6 u. x
    CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'( ~) u5 M" V# d3 W/ A$ R7 ^. J4 e
    FROM sysfiles
    ! [$ D* Y2 N0 H WHERE name = @LogicalFileName/ W5 z3 p; X/ ^# R+ ?
    DROP TABLE DummyTrans9 [5 A& }+ w% ^
    SET NOCOUNT OFF 8、说明:更改某个表exec sp_changeobjectowner 'tablename','dbo' 9、存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch1 i9 {7 Z8 S: C/ r+ G' g' P+ M
    @OldOwner as NVARCHAR(128),, `  H  `" F# C, D/ b; F6 I0 V
    @NewOwner as NVARCHAR(128)* J; z+ z2 y* P; Z7 E& Z( F
    AS DECLARE @Name    as NVARCHAR(128)
    , v% M: J- I& z& h+ h6 ?DECLARE @Owner   as NVARCHAR(128)9 K$ j6 H6 x! |
    DECLARE @OwnerName   as NVARCHAR(128) DECLARE curObject CURSOR FOR % O8 c) p5 ~; i7 e
    select 'Name'    = name,
    ( N; T! F% h  f1 b   'Owner'    = user_name(uid)7 I; {, J) ~& l; x
    from sysobjects0 I. A' k: N' L" `3 A
    where user_name(uid)=@OldOwner
    - j3 G1 ~* ~3 w8 G# N: o, |order by name OPEN   curObject# ?0 C4 q+ j- Y5 h# Z. y
    FETCH NEXT FROM curObject INTO @Name, @Owner
      X% M% n& |2 A. EWHILE(@@FETCH_STATUS=0)+ r" S. K9 E1 E9 J8 v
    BEGIN     9 N! }) r5 k8 ?3 J
    if @Owner=@OldOwner
    & D" {& {8 o6 O$ Obegin
    ) ^7 S- g1 {. B4 H   set @OwnerName = @OldOwner + '.' + rtrim(@Name)
    1 C0 l7 C2 R' M5 g   exec sp_changeobjectowner @OwnerName, @NewOwner/ t1 Z4 v" @" q# a& [7 `& P
    end" i  A) D3 `' a. U+ P8 V
    -- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner
      q9 D1 A7 ?! p( |4 E+ v  P6 tEND close curObject
    + T& q6 k5 b5 O8 n- adeallocate curObject
    5 x- x/ E$ @+ s3 i- G: B0 UGO 10、SQL SERVER中直接循环写入数据declare @i int
    9 G" p5 r  k1 {set @i=1
    5 h! R0 U8 S  twhile @i<30
    7 N! V& @9 b. T% _begin; D6 f8 Z+ G3 J' M* @" I5 \
        insert into test (userid) values(@i)
    ! [6 e2 E) D+ I3 p    set @i=@i+16 q* S; E' p- W1 n
    end
    ! X8 U( ?& ^1 W5 R  g+ |0 \; X案例有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:Name   scor
    1 V$ a  R. b+ A# S' Z3 EZhangshan 807 a2 J& y( l8 _* z" P4 k: @
    Lishi 596 [3 ?& ~4 P# _9 d" t3 h% S
    Wangwu 50
    ' D0 n- d: c. ?; TSongquan 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
    8 A+ r8 _( S- _' i数据开发-经典 1.按姓氏笔画排序:+ H4 b, A1 g; ]* R4 t- Y1 F$ n! U
    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多 2.数据库加密:
    0 U! {, b( p' _4 }! k8 k# `+ Eselect encrypt('原始密码')6 P  F* M' {4 L' j+ t
    select pwdencrypt('原始密码')$ _3 @; ^4 `. E) i* U
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')9 w' e4 [; a) F3 U, \9 J
    select pwdencrypt('原始密码')1 f9 a0 W2 C; F, z( `3 X
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 3.取回表中字段:declare @list varchar(1000),! O0 H, N+ Z6 u! y
    @sql nvarchar(1000)
    ( i9 U! Q: w) U$ T+ Iselect @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'8 Q. r- C* I0 B2 `1 I' x3 H! z2 g3 t
    set @sql='select '+right(@list,len(@list)-1)+' from 表A' " p/ I3 j, @3 ^
    exec (@sql) 4.查看硬盘分区:EXEC master..xp_fixeddrives 5.比较A,B表是否相等:if (select checksum_agg(binary_checksum(*)) from A)4 S# P9 x5 F- Y; t. T% K
         =! m0 X( j* ?' j
        (select checksum_agg(binary_checksum(*)) from B)
    ( ~  g, m5 F- X- Q) y6 Aprint '相等'' t7 y3 W6 ]8 `, s* _4 I
    else
    & b( K& j  I/ S+ Q/ w/ Pprint '不相等' 6.杀掉所有的事件探察器进程:DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
    1 Q% U6 {; D8 ~( c  XWHERE program_name IN('SQL profiler',N'SQL 事件探查器')
    : ~4 w; k, \3 x- qEXEC sp_msforeach_worker '?' 7.记录搜索:
    4 H. \/ @) P5 R+ ^. d& j& t开头到N条记录Select Top N * From 表-------------------------------
    : H$ Y+ Y/ I: N' \NM条记录(要有主索引ID)2 b8 q" w1 a7 v+ Z1 U% m, a8 @
    Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc
    2 h& `4 ^. \3 A" |5 l----------------------------------
    ! @+ J  H. _( o3 U6 o2 E2 j. s( rN到结尾记录Select Top N * From 表 Order by ID Desc3 y- p2 y' @- I+ U# @  k' C
    案例例如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:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。. G" U3 U0 ^6 e9 W/ x( R
    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:获取当前数据库中的所有用户表
    ; q5 @' A& O+ V4 Dselect 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:查看与某一个表相关的视图、存储过程、函数& J6 {: S; m1 v/ R' W6 t
    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:查询用户创建的所有数据库
    % v# Z6 Y9 R# {. Iselect * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')3 O9 A& Y, K" K
    或者5 K' j3 J+ D+ v; K
    select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 14:查询某一个表的字段和数据类型select column_name,data_type from information_schema.columns- M  f1 v5 E$ B/ z
    where table_name = '表名' 15:不同服务器数据库之间的数据操作$ V3 L/ m" N' n8 N) n
    --创建链接服务器$ Q$ J) p2 H& H* c# S+ H0 r

    & r* q1 m/ b9 I% F$ U( lexec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
    4 z+ R. U) ^6 O+ a6 N5 w) J4 g& R2 H6 E' Y+ \+ d$ `1 [
    exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
    7 j( C5 T5 V1 H) Z+ m( e
    1 k' R/ l: f& z/ X# Y1 v+ y--查询示例
    7 I  X. d4 h! C% {2 }' ?  @4 n/ W# {/ Z
    select * from ITSV.数据库名.dbo.表名9 p0 C( ]+ C! M1 {
    ! W4 }! y0 c' z6 W+ z( b3 u
    --导入示例
    * x2 R* b8 U3 B% `" x5 |2 Q3 `; S- Q0 Z2 z
    select * into 表 from ITSV.数据库名.dbo.表名$ o6 h- ^) o1 A

    - T9 U4 T3 o0 x" ?  r7 [) m5 P--以后不再使用时删除链接服务器0 Z' \: O& K& v+ x( |
    4 y3 A. W& l" {
    exec sp_dropserver 'ITSV ', 'droplogins '' M" r1 w2 _5 U4 B  v- z, w

    / [0 r* |2 V2 j& K--连接远程/局域网数据(openrowset/openquery/opendatasource)7 T0 f  Z, R9 n: Y2 w) k

    4 R! y" G9 w3 b8 M( u0 p$ l6 c--1、openrowset3 k- M& c0 ^% ]. \# |" _* J/ U

    " C5 Y+ ?, s9 h0 I7 L9 E7 K--查询示例1 p1 K, p+ u# ]% E9 |

      {) o6 ~& c# x% x$ l) N8 N! dselect * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)) I5 t& Z/ j4 a( G
    7 p9 Z, ^  m4 R" i+ G! X. K7 ~
    --生成本地表
    ! k+ i" {* w& t+ d7 h! q
    0 J" E& Z- K0 \- Kselect * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)) U7 k% [: b: E/ W+ W4 z

    $ i/ m6 z6 p% O& x& L--把本地表导入远程表9 `: U" p7 a: j" ?; |6 t) v1 H, F

    " Z; y3 M0 V% [* s2 N! C! ainsert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
    . Z; \8 G# b. ?4 B$ N
    * M. F6 ?6 ~0 C: G6 g" d) k7 S. c# b: j0 `select *from 本地表/ h( h5 g6 ~2 o+ e

      R( X  G8 r0 V9 _! a$ Z( v--更新本地表
    $ x: W7 ?- H& |; U( P7 D/ Kupdate b! @7 @5 q4 Z/ v7 y+ I" Y! s
    set b.列A=a.列A
    8 c3 \) a! E! {3 E from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
    + n& X- W- [; T7 ?2 son a.column1=b.column16 o; y# f- L% W/ i9 A" W
    --openquery用法需要创建一个连接7 w' L+ x! w, b; C5 V. \& e* ^
    --首先创建一个连接创建链接服务器& S5 A+ y: w1 W; h" P, Q
    exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '8 g: Z" f$ O" _2 M
    --查询
    3 E( h& k% M: l, O2 h! _select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
    ) m; }3 R+ }2 K$ ^4 l! z--把本地表导入远程表
    4 o: q+ i9 v7 e2 E5 i+ f9 m0 binsert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
    8 c% p9 I( U2 a6 a# ^$ f9 i3 D$ hselect * from 本地表/ K) f0 S( `; p6 T2 |  d
    --更新本地表
    3 b( \- m; U1 p+ ~  |; e/ v/ o; N
    1. update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A
      + U" x: @" ^! P; C8 B" s
    2. 0 s( t* P* f5 x
    3. --3、opendatasource/openrowset
      1 x6 Y  ?7 n9 b+ S3 d
    4. & D  Q" e2 V) P$ \  w+ ^* r
    5. SELECT   * FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta5 S0 B. V  J3 ^" o
    6. # R$ V( C% v# m0 S% W0 C) d
    7. --把本地表导入远程表
      3 x6 r- v$ {( k# R8 {# Q. ~7 ^/ [

    8. 3 X5 @5 O& W$ c7 w* E, m
    9. insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
      5 b, m8 P+ y, `) z8 K0 z
    10. & A% ]# S3 S" p- k, ?- r" Z" i$ P8 B7 b
    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.列A9 S# s6 b/ P& ?" B* S' A
    --3、opendatasource/openrowset" R0 j' z, _2 ?; ~3 x; ^% z
    SELECT   * FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta; S+ ?, A$ J1 `
    --把本地表导入远程表
    3 l' V5 z$ m. n7 Dinsert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
    / V; ~/ m; f+ {) p" ~select * from 本地表

    最近浏览会员:

    • nantong
      2020-09-20
    • 北极孤星
      2020-09-20
    您需要登录后才可以回帖 登录 | 加入我们

    本版积分规则

    QQ|小黑屋|手机版|Archiver| 商乾电商 ( 粤ICP备15079921号-7 )

    GMT+8, 2020-10-25 12:30 , Processed in 0.338169 second(s), 39 queries .

    版权所有 惠州市商乾电子商务有限公司

    © 2015-2017 商乾

    快速回复 返回顶部 返回列表