精华内容
下载资源
问答
  • SQL Server Replication

    千次阅读 2016-04-18 23:26:48
    sql server replication

    sql server replication 为实现数据库读写分离,高可用等都提供了不错的解决方案。实现 replication 总共分为三种方法:

    1. 是 SSMS 来搭建,这个方法比较简单,只要你在脑袋里有了成型的架构思维和拓扑结构,就能很容易实现;
    2. 是依赖 T-sql script 的一套存储过程,以 sp_create*, sp_drop* 等存储过程, 配合 distribution 数据库的动态管理视图 (DMV) 来实现搭建,维护和监控;
    3. 可以使用 .NET Client 编程实现,这套方法比较灵活,而且可以系统化的管理多个 replciation 应用,比较适合大规模的 replication 搭建与管理。当然,既然有 .NET Client 方法也会有 Native Code 的方法,这个应用就有点广了,我也不至于用 c 从头来玩一遍分布式。

    网络上针对第一种方法有了很多的记载和实验,动手一搜可以搜到很多的案例,自己看着搭建也可以很快走完整套流程,中间可能会有一些小细节或者小问题需要注意。但是涉及到第二种和第三种方法,就很少了,我在 MaryKay 的时候,老外针对这两种情况,也是选择放弃,甚至第一种就不考虑使用,大概就是因为不好用的原因。所以今天我就要以第一种方法切入,然后谈一下后面两种方法。

    先从架构拓扑谈起,再细化到组件(Agents) 以及实现方法,最后讲一讲管理。从概念讲起,先在头脑里搭建这个 replication 的框架,每一个组件的配置及使用,然后灵活配置各个组件的应用,这一步一个脚印的往前进,循序渐进,才能走的更远。这个过程会花费很长的时间,所以耐心,是我完成搭建 replication 的一个必要条件。

    这里写图片描述
    上面这张图,简明扼要的阐述了 replication 的架构与组件,绿线表示读,红线表示写。看图说话,我们一一将里面的关键点说明白:

    1. Publisher, Distributor, Subscriber, 这三者可以分布在不同的 database instance 上面,也可以都在同一个 Instance 上面。 实践中,我们一般都会将三种角色,分布在三个不同的服务器的 database instance 上面,保证了应用的可用性,不至于一台服务器宕机,其他服务都不可用的状况。
    2. Replication 是一种数据复制服务,在 sql server 中的实现,是脱离了 database engine 概念的,像 storage engine, query engine 等。 Replication (数据读)的实现,其实是一组 windows executables 的应用,学名上是 Agents,这些 Agents 普遍来说,都是建立在 Distributor 这个角色所在的 database instance 里面,具体就是配置在 Distribution 这个数据库里面,当然这个数据库名字可以更改,我们需要知道的是,这个数据库就像是一组元数据,存储了我们定义好的一系列 replication 组件,包括像 publication, subscription 等等。 这里唯一要注意的,就是 pull subscription 与 push subscription 的 Agents 存放地点,pull subscription 是将 Agents 存放到了 subscriber 上面而 push subscription 的 Agents 都存放到了 Distributor 上面。
    3. 既然谈到了 distributor, 我们就要提到,整个 replication 的创建的时候,第一个要创建的要素就是 Distributor: 选一台服务器,setup 一个 database instance, 让它作为 Distributor 角色,创建存放元数据的 distribution 数据库。为了易于管理,可以创建多个 Distribution 数据库,让每一个 distribute 数据库存放一组 publisher 的publication. 接下来就是创建 Publication 与 Subscription. 次序是先 publication 再 subscription. 这里有两个注意点: 一是 distributor 所在的 instance, 想要 publish 一些 publication的话,只能以自己的 distributor 作为分布者;二是任何想要用到这个 distributor 的 publisher 都必须在这个 distributor 上面先注册。理论上就是,我们不能随便分发你的出版刊物,但是你们出版社,也不能随便使用我们的分销渠道为你们服务,都必须先订了合同。
    4. Replication 可以有三种表现形式: Snapshot Replication, Transactional Replication 以及 Merge Replication. transactional replication: 可以用来集成数据仓库,提供高可用与高并发。 Merge Replication, 最常见的应用场景是移动应用,也可以做数据集成;Snapshot replication 则是用来初始化数据,在应用 transactional , merge replication 的时候,做一次全量抽取。可见, Snapshot Replicatoin 提供的是一个基础功能。
    5. 关于 Snapshot Replication, 有趣的是它的实现机制: 首先会在 publication 数据库记上俩比 marker, 第一笔是记录 snapshot 开始的时间,第二笔是记录 snapshot 结束的时间。当把 snapshot 生成 bcp file 之后, log reader agent 会把俩比 marker 中发生的 command 也生成日志,再应用到已经生成的 bcp 文件中。 这样的作用就是保证一份完整的 snapshot。之后的 transaction replication 就只要生成最后一个 marker 之后的操作就行。从而避免了重复生成 snapshot 生成时间之间的发生在 publication database 上的操作。这样的 snapshot replication 就可以接受最后一个 log marker 之后的日志重做了。
      这里先埋下个小问题:如果没有日志的操作,该怎么同步呢?

    把图里的内容都讲了一遍了,就要问自己几个问题了:

    1. Publication 的内容都分发出去了,那么怎么保证 Publication 的内容与各个 Subscription 之间的内容保持一致性了呢? 检验机制有哪些? 一般我们想到的是做 Audit, 比如 checksum, except 等等。 但是实现起来比较复杂,sql server replication有哪些好用的特性能帮助我们快速的达到验证数据一致性的目的呢?
    2. 既然是 replication, 数据复制嘛,那么什么时候会同步一次,多久呢? 我们是不是可控这些同步数据的时间间隔呢?怎么去同步,手工,还是定义一些同步脚本?
    3. 错误控制?如果同步不成功,会有哪些警告,我们改如何处理这些警告?比如一台 remote replication 服务器宕机,那么这台服务器就不能接受 publication 了,那这一部分没有同步的数据,是不是会保留?什么时候会继续同步这部分数据?
    4. Snapshot 在创建的时候,会给数据库带来多少影响, 锁在这里面是如何实现的 ? Transactional , Merge Replication 又是如何使用锁的?
    5. replication 与 Log shipping , database mirroring 的区别: 前者可以发布整个数据库也可以仅仅是一个库中的部分对象,而后俩者则发布的是整个数据库。那么这部分对象同步到已存在的数据库时,是不是会重写当前数据库的其他对象?
    6. 上面留下的小问题: 如果没有日志的操作,该怎么同步呢? 比如我是用 bcp 插入的 publication 中的表,这部分数据都是没有日志的,那么怎么被 log reader agent 读取到,并同步到其他 replica 里面去?

    人生嘛,就是解决一个一个的问题,要回答上面这些问题,还是靠一步一步做实验来弄清楚,先从 SSMS 搭建一个 replication 开始:

    1. 新建 3 台虚拟机,装上 sql server 2008R2 , 分别将 instance 命名为 Distributor, Publisher, Subscriber. 在 Publisher 中新建一个数据库, 名叫 stock, 我们假定这是一个用来存放股票相关的数据库。 新建一张表叫做 region. 我们的目标是把这张表同步到 Subscriber 中的 stock 数据库中去。这个 Subscriber 中的 stock 是必须先创建好的。
    2. 先建立一个 Distributor 角色,将它安装在 Distributor 这个 instance 上面,建立完之后,会有一个 distribution 的数据库,我们这里要额外注意下这个数据库里面的对象,数据库表,stored procedure 等。这些数据库对象在我们手工用 .net client 或者 T-sql 存储过程搭建 replication 的时候,会经常用到。
    3. 接着在 Publisher 这个 instance 下面,新建一个 publication. 这个 Publication 包含了一张表, region. 没错,这份 publication 是定义在 publisher 上面,所以不能在 distributor instance 上面找到。但是在 Publisher 上面指定 Distributor 的时候,会选择某一个 instance 来当 distributor ,我们这里正好是这个装有 distributor instance 的机器。所以在 distribution 数据库中,会有相关记录,表示已经有 publisher 在使用我们的 distributor 功能了。这里不能搞混的是 publication 与 Distributor Agent 的概念。Distributor 的 snapshot Agent 以及 Log Reader Agent 是在 Distributor 这个服务器上的,这两个 Agent 是去读 publication 的。所以 Publication 一定是在 publisher 服务器上创建的.
    4. 同3 所说, Subscription 也是配置在 Subscriber 这个 instance 上面的。 至于 Distributor Agent 是创建在 Distributor 还是 Subscriber 上面, 就看是哪种类型的 Subscription 了。前文有讲这里不再累述。配置完,记得要看下 distribute 数据库。这里发生的变化都在这个库里能体现出来。

    这里有张图,可以很好的看到,publication, distributor agents, subscription 的所属问题,注意黑圆点的标注:

    这里写图片描述

    好了,上面写的是思路,具体的实现,我们一步一步来,在这个实现的步骤里面,肯定会遇到很多不可思议的事情,一件一件记录下来,肯定能获得不少 serendipity (意外的收获):

    1. Distributor 配置好之后,我们要为 publisher配置 distributor 了,这个时候,奇怪的问题来了,用密码登陆 distributor 的时候,始终登陆不了,总是出现这个错误 “ sql server could not cononect to the distributor with the specified password”。参考了网络上的一篇帖子,有这么个用法:
      首先,先检查下 remote access 在数据库里面是不是开启,而在可以检查这个选项的时候,还需要将 show advanced options 这个开关给开起来,看看是怎么开的 :
      sp_configure ‘show advanced options’, 1
      reconfigure
      这还只是第一步,先将 show advanced options 启动,reconfigure 就是让这个选项获得新值之后,重新写到数据库配置文里面。接下来就是配置 remote access, 让其 Run Value 等于 1:
      sp_configure ‘remote access’, 1
      reconfigure
      到这一步就可以了,重启 sql server instance, 再 sp_configure 查看下 remote access 的值, 确认 Run_Value 是1 了。这一步在 publisher, distributor 上面都要执行好。

    2. 别以为 sql server 默认会帮你把一切都配置好,尤其是要用到 replication,failover cluster 等这类特性的时候,我们需要检查一些常规的配置,比如刚才 1 中提到的 remote access, 接下来 remote access 是允许了,但是 remote access 用到的相关渠道(协议)是不是也已经开启了呢? 这里讲的就是 sql server configuration manager 里面会用到的 TCP/IP, Named pipes 远程协议是不是被启动了? 没启动,这里要启动起来。否则,经典的连接错误就会找上来:
      A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn’t support requested protocol)

    3. Distributor 的功能包括了,从 publisher 拉回 snapshot和相关 transaction,并推给 subscriber 或者等着 subscriber 来取 snapshot 或者 transaction。 这里就会有两个 sql agent job(根据我们的 replication type , job 的数量会有不同), 名字很好认,一眼就能看出来,那么修改这两个 job 的时间调度就能修改我们需要的 replication 更新频率了。
    4. 前面有个问题,publication 的这一部分数据,会不会冲掉原来的表的数据和对象? 同一个表的数据会被冲掉,但是已经存在的对象是冲不掉的,比如有一张表是在订阅之前就存在在 subscriber 数据库中的,那么只要 publication 里面没有同名的表,这张表还是会被留下来的,不受影响。
    5. 继续回答前面的问题,我们知道 Distributor 的 log Reader Agent 是去读取 publisher 的日志,从而生成一份日志重做的行为数据,存在 distribution 数据库里,等待下次同步的时候,在 subscriber 的 subscription 数据库里重新跑下这份日志重做的行为数据,完成一次 Transactional Replication. 但是如果我们在 publisher 的 publication 上面(这份 publication 就包含一份 article, 就是一张表 region, 包括了两个字段 regionId, regionName), 用了一次 bcp 导入了 1000 条数据,都知道 bcp 是不写日志的,那么 Transaction Replication 做增量更新的时候,会不会同步这 1000 条数据到 subscription 的数据库里面呢?
      首先,我们要搭建一个 Transactional Replication 的环境。注意要给需要做 publication 的表带上主键,否则会有这么个错误:
      the database contains no objects that can be replicated with the selected publication type
      接着用 bcp 导入 1000 条数据,过不到 1 分钟,去看下 subscription 里的同步表 stock, 发现数据居然同步过来了。这里面其实我犯了个错误,概念性的错误,bcp 其实是会写日志的,不写日志的情况,仅仅是在bulk-logged 和 simple recovery model 下面,不过这样是肯定会丢数据的,事实证明,没有日志的表,是不允许 replicate 的。碰到这种情况, snapshot replication 是个好选择。
    6. Snapshot Replication 在 init snapshot 的时候,会对表进行一个 S 锁,对 page, rid 分别进行 IX, X 锁。影响还是蛮大的。Transaction Replication 增量读的是日志,由于有 primary key 的存在,所以对 key 也进行了锁,而且是 X 锁。
    7. 在配置 publisher publication的时候,会有对 retention 做限制。比如,(即使在 publication全部同步完之后)增量抽取的日志保留多长时间,在每一份 subscription 没有同步之前,会保留多长时间等。
    8. 在每一份 publication 的右键菜单里,包含了一个 validate subscription 的选项,好东西,用来验证数据一致性。比起我们手写 checksum 或者 except 来验证,是不是方便了很多呢?这个 validation 的结果,SSMS 是不会马上给你看到的,我们要打开 Launch Replication Monitor 来看, 找到 Distributor to Subscription history , 查看 action message, 就可以看到 validation 到底有没有 pass 了。
    9. 不过细想,一个 publisher 只能订一个 distributor ,好像不合理? 为什么不能签订多个 distributor ,这样可以分散一个 distributor 的压力,也可以实现多播的可能性。

    到这里,把前面的自个儿问自个儿的问题都回答了,那么我们就要进入下一个话题,如何用 T-SQL 编程的方法或者 .NET Client 的方法来构建 replication 呢?

    1. 大概的一个拓扑, 我们将三种角色分别安装在三台不同的 sql server 服务器上
      1.1 distributor
      1.2 publisher, publication, article
      1.3 subscriber , subscription
      1.4 Agents , Schedulers
    2. 简单的一个实现, 以 snapshot replication 为例子
      2.1 distributor script :
      我们会在 distributor 角色的 sql server 服务器上,将这台服务器设置为 distributor ,建立 distribution 数据库, 并配置一个允许使用它作为 distributor 的 publisher。
      2.2.1 sp_adddistributor 第一次执行的时候,必须指定password, 这个 password 是 distributor_admin 密码。 在 publisher 连接 distributor 的时候,也必须指定这个 distributor_admin 的密码,用来通信。下面这个例子其实还需要为 sp_adddistributor 参数 @password 赋值.
      2.2.2 三大要素: distributor 所用到的 Instance , 以 serverName\instanceName 命名; distributor 用到的数据库 distribution (名字可以更改); Snapshot 用到的存储路径.
    DECLARE @distributor AS sysname;
    DECLARE @distributionDB AS sysname;
    DECLARE @publisher AS sysname;
    DECLARE @directory AS nvarchar(500);
    DECLARE @publicationDB AS sysname;
    -- Specify the Distributor name.
    SET @distributor = 'storage\distributor';
    -- Specify the distribution database.
    SET @distributionDB = N'distributionstock';
    -- Specify the Publisher name.
    SET @publisher ='publisher\crm';
    -- Specify the replication working directory.
    SET @directory = N'C:\snapshot';
    -- Specify the publication database.
    SET @publicationDB = N'stocktrans';
    -- Install the server Storage\Distributor as a Distributor using the defaults,
    -- including autogenerating the distributor password.
    USE master
    EXEC sp_adddistributor @distributor = @distributor;
    -- Create a new distribution database using the defaults, including
    -- using Windows Authentication.
    USE master
    EXEC sp_adddistributiondb @database = @distributionDB,
    @security_mode = 1;
    GO
    -- Create a Publisher and enable stocktrans for replication.
    -- Add publisher\crm as a publisher with storage\distributor as a local distributor
    -- and use Windows Authentication.
    DECLARE @distributionDB AS sysname;
    DECLARE @publisher AS sysname;
    -- Specify the distribution database.
    SET @distributionDB = N'distribution'stock;
    -- Specify the Publisher name.
    SET @publisher = 'publisher\crm'
    USE [distributionstock]
    EXEC sp_adddistpublisher @publisher=@publisher,
    @distribution_db=@distributionDB,
    @security_mode = 1;
    GO
    2.2 publication script , article script
        2.2.1 首先要做的两点,就是:一启动 publisher 的角色;二配置要使用的 distributor . 这里使用到的存储过程 sp_replicationdboption .
    
            use stocktrans
            go
            sp_adddistributor 
            @distributor = 'storage\distributor', 
            @password = 'XXXXXXXXXXX'
            sp_replicationdboption 'stocktrans','publish',true
        2.2.2 在第一步里指定的 replication database, 执行 sp_addpublication 来添加 publication.
    
    -- Create a new transactional publication with the required properties.
    EXEC sp_addpublication
    @publication = 'stocktranspub',
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';
    -- Create a new snapshot job for the publication, using a default schedule.
    EXEC sp_addpublication_snapshot
    @publication = 'stocktranspub',
    @job_login = 'smartoffice\joe',
    @job_password = 'xxxx',
    -- Explicitly specify the use of Windows Integrated Authentication (default)
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;
    GO
        2.2.3 添加 article
    
    DECLARE @publication AS sysname;
    DECLARE @table AS sysname;
    DECLARE @filterclause AS nvarchar(500);
    DECLARE @filtername AS nvarchar(386);
    DECLARE @schemaowner AS sysname;
    SET @publication = N'stocktranspub';
    SET @table = N'stock';
    SET @schemaowner = N'dbo'';
    
    EXEC sp_addarticle
    @publication = @publication,
    @article = @table,
    @source_object = @table,
    @source_owner = @schemaowner,
    @vertical_partition = N'true',
    @type = N'logbased'
    2.3 subscription script 以 push subscription 为例子. 所有的操作都在publisher, publication 里面执行。
        2.3.1 判断 publication 是不是可以被 push 或者 pull
            Sp_helppublication
        2.3.2 添加 push subscription
            Sp_addsubscription
        2.3.3 添加 push distributor agent
            Sp_addpushsubscription_agent
        2.3.4 默认是一天执行一次 snapshot push over ,那么怎么去修改这个同步间隔呢?
            Sp_add_schedule
    
    use stocktrans
    go
    sp_addsubscription 
      @publication = 'stocktranspub',
      @subscriber = 'sqlcluster\mysqlcluster',
      @destination_db = 'stock'

    3 . 监控健康指标
    3.1 Replication Monitor

    4 . 移除 replication , 察看 distribution 数据库元数据的更改

    4.1 先移除 subscriber 和 subscription Sp_dropsubscription,sp_subscription_cleanup
    4.2 再移除 publisher 和 publication
    4.3 再移除 distributor
    4.4 细节解说

    为了禁止 Replication,我们必须依次执行以下步骤:
    4.4.1 停掉所有的 Replication 相关的 Sql Agent Job, 关于 Replication 相关的 Sql Agent job ,命名也是有规律可以寻的,可以参考这篇文章 Replication Agent Security Model
    4.4.2 在每一个 subscriper 的 subscription 数据库上,执行 sp_removedbreplication,删掉 replication 相关的数据库对象。
    4.4.3 在 publisher 的 publication 数据库上,执行 sp_removedbreplication 来删掉 replication 相关的数据库对象。如果配置了 distributor ,那么执行 sp_dropdistributor 来删掉相关应用。
    4.4.4 在 Distributor 的实例上,执行 sp_dropdisttpublisher. 有多少 publisher 配置了,就要执行相应的次数的命令,直到全部删掉。之后,执行 sp_dropdistributiondb 来卸载相关的 distribution 数据库。配置了多少 distribution 数据库,针对每个库都要做一次 sp_dropdistributiondb。最后执行 sp_dropdistributor 来删掉这个 distributor 角色。

    展开全文
  • sqlserver sql语句|经典sql语句|实用sql语句

    千次阅读 多人点赞 2012-08-14 09:35:05
    sqlserver sql语句|经典sql语句|实用sql语句 原文地址 http://www.blogjava.net/wangdetian168/archive/2010/07/27/337091.html sqlserver sql语句|经典sql语句|实用sql语句 、基础 1、说明:创建...
     
     
    

    sqlserver sql语句|经典sql语句|实用sql语句

    一、基础

    1、说明:创建数据库
    CREATE DATABASE database-name 
    2
    、说明:删除数据库
    drop database dbname

    3、说明:备份sql server
    --- 创建 备份数据的 device
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
    --- 开始 备份
    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 (使用旧表创建新表)
    B
    :create 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) 
    说明:删除主键 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 viewname
    10
    、说明:几个简单的基本的sql语句
    选择:
    select * from table1 where 范围
    插入:insert into table1(field1,field2) values(value1,value2)
    删除:delete from table1 where 范围
    更新
    update table1 set field1=value1 where 范围
    查找select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
    排序select * from table1 order by field1,field2 [desc]
    总数select count as totalcount from table1
    求和select sum(field1) as sumvalue from table1
    平均select avg(field1) as avgvalue from table1
    最大select max(field1) as maxvalue from table1
    最小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),不消除重复行。 
    注:使用运算词的几个查询结果行必须是一致的。 
    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.c
    B:right (outer) join: 
    右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 
    C:full/cross (outer) join: 
    全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
    12
    、分组:Group by:
      
    一张表,一旦分组 完成后,查询后只能得到组相关的信息。
        
    组相关的信息:(统计信息) count,sum,max,min,avg  分组的标准)
     
      在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
       
    selecte统计函数中的字段,不能和普通的字段放在一起;

    13、对数据库进行操作:
       
    分离数据库
     sp_detach_db; 附加数据库sp_attach_db 后接表明,附加需要完整的路径名
    14.如何修改数据库的名称:

    sp_renamedb 'old_name', 'new_name'

     

    二、提升

    1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
    法一:
    select * into b from a where 1<>1仅用于SQlServer
    法二:select top 0 * into b from a
    2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
    insert into b(a, b, c) select d,e,f from b;

    3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
    insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
    例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

    4、说明:子查询(表名1:a 表名2:b)
    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、说明:显示文章、提交人和最后回复时间
    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)
    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 )
    select * from (SELECT a,b,c FROM a) T where t.a > 1;

    8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
    select * from table1 where time between time1 and time2
    select a,b,c, from table1 where a not between 
    数值1 and 数值2

    9、说明:in 的使用方法
    select * from table1 where a [not] in (‘1’,’值2’,’值4’,’值6’)

    10、说明:两张关联表,删除主表中已经在副表中没有的信息 
    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

    11、说明:四表联查问题:
    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、说明:日程安排提前五分钟提醒 
    SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

    13、说明:一条sql 语句搞定数据库分页
    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
    具体实现:
    关于数据库分页:

     declare @start int,@end int

     @sql nvarchar(600)

     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)’

     exec sp_executesql @sql


    注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引

    14、说明:前10条记录
    select top 10 * form table1 where 范围

    15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

    16、说明:包括所有在 TableA 中但不在 TableBTableC 中的行并消除所有重复行而派生出一个结果表
    (select a from tableA ) except (select a from tableB) except (select a from tableC)

    17、说明:随机取出10条数据
    select top 10 * from tablename order by newid()

    18、说明:随机选择记录
    select newid()

    19、说明:删除重复记录
    1),
    delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
    2),select distinct * into temp from tablename
     delete from 
    tablename
     insert into 
    tablename select * from temp
    评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作
    3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段

    alter table tablename
    --添加一个自增列
    add column_b int identity(1,1)
     delete from 
    tablename where column_b not in(
    select 
    max(column_b) from tablename group by column1,column2,...)
    alter table 
    tablename drop column column_b

    20、说明:列出数据库里所有的表名
    select name from sysobjects where type='U' // U代表用户

    21、说明:列出表里的所有的列名
    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
    显示结果:
    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3

    23、说明:初始化表table1

    TRUNCATE TABLE table1

    24、说明:选择从10到15的记录
    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”全部不选,
    如:
    if @strWhere !='' 
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 
    end
    else 
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 
    end

    我们可以直接写成

    错误!未找到目录项。
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 
    安定 '+ @strWhere 2、收缩数据库
    --重建索引
    DBCC REINDEX
    DBCC INDEXDEFRAG
    --收缩数据和日志
    DBCC SHRINKDB
    DBCC SHRINKFILE

    3、压缩数据库
    dbcc shrinkdatabase(dbname)

    4、转移数据库给新用户以已存在用户权限
    exec sp_change_users_login 'update_one','newname','oldname'
    go

    5、检查备份集
    RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

    6、修复数据库
    ALTER DATABASE [dvbbs] SET SINGLE_USER
    GO
    DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
    GO
    ALTER DATABASE [dvbbs] SET MULTI_USER
    GO

    7、日志清除
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
     @MaxMinutes INT,
     @NewSize INT


    USE tablename -- 
    要操作的数据库名
    SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
    @MaxMinutes = 10, -- Limit on time allowed to wrap log.
     @NewSize = 1  -- 你想设定的日志文件的大小(M)

    Setup / initialize
    DECLARE @OriginalSize int
    SELECT @OriginalSize = size 
     FROM sysfiles
     WHERE name = @LogicalFileName
    SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
     CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
     CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName
    CREATE TABLE DummyTrans
     (DummyColumn char (8000) not null)


    DECLARE @Counter    INT,
     @StartTime DATETIME,
     @TruncLog   VARCHAR(255)
    SELECT @StartTime = GETDATE(),
     @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

    DBCC SHRINKFILE (@LogicalFileName, @NewSize)
    EXEC (@TruncLog)
    -- Wrap the log if necessary.
    WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
     AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
     AND (@OriginalSize * 8 /1024) > @NewSize  
     BEGIN -- Outer loop.
    SELECT @Counter = 0
     WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
     BEGIN -- update
     INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
     SELECT @Counter = @Counter + 1
     END
     EXEC (@TruncLog)  
     END
    SELECT 'Final Size of ' + db_name() + ' LOG is ' +
     CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
     CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
     FROM sysfiles 
     WHERE name = @LogicalFileName
    DROP TABLE DummyTrans
    SET NOCOUNT OFF

    8、说明:更改某个表
    exec sp_changeobjectowner 'tablename','dbo'

    9、存储更改全部表

    CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
    @OldOwner as NVARCHAR(128),
    @NewOwner as NVARCHAR(128)
    AS

    DECLARE @Name    as NVARCHAR(128)
    DECLARE @Owner   as NVARCHAR(128)
    DECLARE @OwnerName   as NVARCHAR(128)

    DECLARE curObject CURSOR FOR 
    select 'Name'    = name,
       'Owner'    = user_name(uid)
    from sysobjects
    where user_name(uid)=@OldOwner
    order by name

    OPEN   curObject
    FETCH NEXT FROM curObject INTO @Name, @Owner
    WHILE(@@FETCH_STATUS=0)
    BEGIN     
    if @Owner=@OldOwner 
    begin
       set @OwnerName = @OldOwner + '.' + rtrim(@Name)
       exec sp_changeobjectowner @OwnerName, @NewOwner
    end
    -- select @name,@NewOwner,@OldOwner

    FETCH NEXT FROM curObject INTO @Name, @Owner
    END

    close curObject
    deallocate curObject
    GO


    10、SQL SERVER中直接循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
        insert into test (userid) values(@i)
        set @i=@i+1
    end
    案例
    有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:

        Name     score

        Zhangshan   80

        Lishi       59

        Wangwu      50

        Songquan    69

    while((select min(score) from tb_table)<60)

    begin

    update tb_table set score =score*1.01

    where score<60

    if (select min(score) from tb_table)>60

     break

     else

        continue

    end

     

    数据开发-经典


    1.按姓氏笔画排序:
    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //
    从少到多

    2.数据库加密:
    select encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同

    3.取回表中字段:
    declare @list varchar(1000),
    @sql nvarchar(1000) 
    select @list=@list+
    ','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
    set @sql='select '+right(@list,len(@list)-1)+' from 表A
    exec (@sql)

    4.查看硬盘分区:
    EXEC master..xp_fixeddrives

    5.比较A,B表是否相等:
    if (select checksum_agg(binary_checksum(*)) from A)
         =
        (select checksum_agg(binary_checksum(*)) from B)
    print '
    相等'
    else
    print '不相等'

    6.杀掉所有的事件探察器进程:
    DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
    WHERE program_name IN('SQL profiler',N'SQL 
    事件探查器')
    EXEC sp_msforeach_worker '?
    '

    7.记录搜索:
    开头到N条记录
    Select Top N * From 
    -------------------------------
    NM条记录(要有主索引ID)
    Select Top M-N * From  Where ID in (Select Top M ID From ) Order by ID   Desc
    ----------------------------------
    N
    到结尾记录
    Select Top N * From  Order by ID Desc
    案例
    例如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:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。
    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:获取当前数据库中的所有用户表
    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:查看与某一个表相关的视图、存储过程、函数
    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:查询用户创建的所有数据库
    select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
    或者
    select dbid, name AS DB_NAME from master..s
    ysdatabases where sid <> 0x01

    14:查询某一个表的字段和数据类型
    select column_name,data_type from information_schema.columns
    where table_name = '
    表名'

    15:不同服务器数据库之间的数据操作

    --创建链接服务器

    exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

    exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

    --查询示例

    select * from ITSV.数据库名.dbo.表名

    --导入示例

    select * into 表 from ITSV.数据库名.dbo.表名

    --以后不再使用时删除链接服务器

    exec sp_dropserver 'ITSV ', 'droplogins '

     

    --连接远程/局域网数据(openrowset/openquery/opendatasource)

    --1、openrowset

    --查询示例

    select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

    --生成本地表

    select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

     

    --把本地表导入远程表

    insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

    select *from 本地表

    --更新本地表

    update b

    set b.列A=a.列A

     from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

    on a.column1=b.column1

    --openquery用法需要创建一个连接

    --首先创建一个连接创建链接服务器

    exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

    --查询

    select *

    FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

    --把本地表导入远程表

    insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

    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

     

    --3、opendatasource/openrowset

    SELECT   *

    FROM   opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

    --把本地表导入远程表

    insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

    select * from 本地表 

    SQL Server基本函数

    SQL Server基本函数

    1.字符串函数 长度与分析用

    1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
    2,substring(expression,start,length) 取子串,字符串的下标是从“1,start为起始位置,length为字符串长度,实际应用中以len(expression)取得其长度
    3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符,还用left于之相反
    4,isnullcheck_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

    5,Sp_addtype 自定義數據類型
    例如:EXEC sp_addtype birthday, datetime, 'NULL'

    6,set nocount {on|off}
    使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
    SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
    
    SET NOCOUNT 为 OFF 时,返回计数
    常识
     
    在SQL查询中:from后最多可以跟多少张表或视图:256
    SQL语句中出现 Order by,查询时,先排序,后取
    SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。 
            
    SQLServer2000同步复制技术实现步骤
    一、 预备工作
    1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
    --管理工具
    --计算机管理
    --用户和组
    --右键用户
    --新建用户
    --建立一个隶属于administrator组的登陆windows的用户(SynUser)
    2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
    我的电脑--D:\ 新建一个目录,名为: PUB
    --右键这个新建的目录
    --属性--共享
    --选择"共享该文件夹"
    --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
     
    --确定
    3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
    开始--程序--管理工具--服务
    --右键SQLSERVERAGENT
    --属性--登陆--选择"此账户"
    --输入或者选择第一步中创建的windows登录用户名(SynUser)
    --"密码"中输入该用户的密码
    4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
    企业管理器
    --右键SQL实例--属性
    --安全性--身份验证
    --选择"SQL Server 和 Windows"
    --确定
    5.在发布服务器和订阅服务器上互相注册
    企业管理器
    --右键SQL Server组
    --新建SQL Server注册...
    --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加
    --下一步--连接使用,选择第二个"SQL Server身份验证"
    --下一步--输入用户名和密码(SynUser)
    --下一步--选择SQL Server组,也可以创建一个新组
    --下一步--完成
    6.对于只能用IP,不能用计算机名的,为其注册服务器别名(此步在实施中没用到)
     (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
    开始--程序--Microsoft SQL Server--客户端网络实用工具
    --别名--添加
    --网络库选择"tcp/ip"--服务器别名输入SQL服务器名
    --连接参数--服务器名称中输入SQL服务器ip地址
    --如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
    二、 正式配置
    1、配置发布服务器
    打开企业管理器,在发布服务器(B、C、D)上执行以下步骤:
    (1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导 
    (2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器(选择自己)
    (3) [下一步] 设置快照文件夹
    采用默认\\servername\Pub
    (4) [下一步] 自定义配置 
    可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置
    否,使用下列默认设置(推荐)
    (5) [下一步] 设置分发数据库名称和位置 采用默认值
    (6) [下一步] 启用发布服务器 选择作为发布的服务器
    (7) [下一步] 选择需要发布的数据库和发布类型
    (8) [下一步] 选择注册订阅服务器
    (9) [下一步] 完成配置
    2、创建出版物
    发布服务器B、C、D上
    (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令
    (2)选择要创建出版物的数据库,然后单击[创建发布]
    (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
    (4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,
    SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。
    但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
    (5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
    注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表
    (6)选择发布名称和描述
    (7)自定义发布属性 向导提供的选择:
    是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性
    否 根据指定方式创建发布 (建议采用自定义的方式)
    (8)[下一步] 选择筛选发布的方式 
    (9)[下一步] 可以选择是否允许匿名订阅
    1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器
    方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
    否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
    如果仍然需要匿名订阅则用以下解决办法 
    [企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅
    2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示
    (10)[下一步] 设置快照 代理程序调度
    (11)[下一步] 完成配置
    当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库
    有数据 
    srv1.库名..author有字段:id,name,phone, 
    srv2.库名..author有字段:id,name,telphone,adress 
     
    要求: 
    srv1.库名..author增加记录则srv1.库名..author记录增加 
    srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新 
    --*/ 
     
    --大致的处理步骤 
    --1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步 
    exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip' 
    exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码' 
    go
    --2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动
    。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动 
    go 
     
     
    --然后创建一个作业定时调用上面的同步处理存储过程就行了 
     
    企业管理器 
    --管理 
    --SQL Server代理 
    --右键作业 
    --新建作业 
    --"常规"项中输入作业名称 
    --"步骤"项 
    --新建 
    --"步骤名"中输入步骤名 
    --"类型"中选择"Transact-SQL 脚本(TSQL)" 
    --"数据库"选择执行命令的数据库 
    --"命令"中输入要执行的语句: exec p_process 
    --确定 
    --"调度"项 
    --新建调度 
    --"名称"中输入调度名称 
    --"调度类型"中选择你的作业执行安排 
    --如果选择"反复出现" 
    --点"更改"来设置你的时间安排 
     
     
    然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 
     
    设置方法: 
    我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. 
     
     
    --3.实现同步处理的方法2,定时同步 
     
    --在srv1中创建如下的同步处理存储过程 
    create proc p_process 
    as 
    --更新修改过的数据 
    update b set name=i.name,telphone=i.telphone 
    from srv2.库名.dbo.author b,author i 
    where b.id=i.id and
    (b.name <> i.name or b.telphone <> i.telphone) 
     
    --插入新增的数据 
    insert srv2.库名.dbo.author(id,name,telphone) 
    select id,name,telphone from author i 
    where not exists( 
    select * from srv2.库名.dbo.author where id=i.id) 
     
    --删除已经删除的数据(如果需要的话) 
    delete b 
    from srv2.库名.dbo.author b 
    where not exists( 
    select * from author where id=b.id)
    go

     


    展开全文
  • sqlserver sql实用语句

    2012-07-03 14:52:53
    、基础 1、说明:创建数据库 CREATE DATABASE database-name ...3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwin

    一、基础

    1、说明:创建数据库
    CREATE DATABASE database-name
    2、说明:删除数据库
    drop database dbname
    3、说明:备份sql server
    --- 创建 备份数据的 device
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack

    4、说明:创建新表
    create table tabname(col1 type1 [not null] [primary key],col2 type2 [notnull],..)

    根据已有的表创建新表:
    A:create table tab_new like tab_old (使用旧表创建新表)
    B:create 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 addprimary key(col)
    说明:删除主键: Alter table tabname drop primary key(col)
    8、说明:创建索引
    create [unique] index idxname ontabname(col….)
    删除索引:drop index idxname
    注:索引是不可更改的,想更改必须删除重新建。
    9、说明:创建视图:create view viewname as select statement
    删除视图:drop view viewname
    10、说明:几个简单的基本的sql语句
    选择:
    select * from table1 where 范围
    插入:insert into table1(field1,field2)values(value1,value2)
    删除:delete from table1 where 范围
    更新
    :update table1 set field1=value1where 范围
    查找:select * from table1 where field1like ’%value1%’ ---like的语法很精妙,查资料!
    排序:select * from table1 order byfield1,field2 [desc]
    总数:select count as totalcount fromtable1
    求和:select sum(field1) as sumvaluefrom table1
    平均:select avg(field1) as avgvaluefrom table1
    最大:select max(field1) as maxvaluefrom table1
    最小:select min(field1) as minvaluefrom 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),不消除重复行。
    注:使用运算词的几个查询结果行必须是一致的。
    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.c
    B:right (outer) join:
    右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
    C:full/cross (outer) join
    全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
    12、分组:Group by:
      
    一张表,一旦分组 完成后,查询后只能得到组相关的信息。
        组相关的信息:(统计信息) count,sum,max,min,avg  分组的标准)
       在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
       在selecte统计函数中的字段,不能和普通的字段放在一起;

    13、对数据库进行操作:
       分离数据库
    sp_detach_db; 附加数据库sp_attach_db 后接表明,附加需要完整的路径名
    14.如何修改数据库的名称:
    sp_renamedb 'old_name', 'new_name'

     

    二、提升

    1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
    法一:
    select * into b from a where1<>1(仅用于SQlServer)
    法二:select top 0 * into b from a
    2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
    insert into b(a,b, c) select d,e,f from b;

    3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
    insert into b(a,b, c) select d,e,f from b in ‘具体数据库’ where 条件
    例子:..from b in'"&Server.MapPath(".")&"\data.mdb"&"' where..

    4、说明:子查询(表名1:a 表名2:b)
    select a,b,c froma where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

    5、说明:显示文章、提交人和最后回复时间
    selecta.title,a.username,b.adddate from table a,(select max(adddate) adddate fromtable where table.title=a.title) b

    6、说明:外连接查询(表名1:a 表名2:b)
    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 )
    select * from(SELECT a,b,c FROM a) T where t.a > 1;

    8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
    select * fromtable1 where time between time1 and time2
    select a,b,c, from table1 where a not between 数值1 and 数值2

    9、说明:in 的使用方法
    select * fromtable1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

    10、说明:两张关联表,删除主表中已经在副表中没有的信息
    deletefrom table1 where not exists ( select * from table2 wheretable1.field1=table2.field1 )

    11、说明:四表联查问题:
    select * from aleft inner join b on a.a=b.b right inner join c on a.a=c.c inner join d ona.a=d.d where .....

    12、说明:日程安排提前五分钟提醒
    SQL:select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

    13、说明:一条sql 语句搞定数据库分页
    select top 10 b.*from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
    具体实现:
    关于数据库分页:

     declare@start int,@end int

     @sql nvarchar(600)

     set@sql=’select top’+str(@end-@start+1)+’+from T where rid not in(selecttop’+str(@str-1)+’Rid from T where Rid>-1)’

     execsp_executesql @sql


    注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)

    14、说明:前10条记录
    select top 10 *form table1 where 范围

    15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
    select a,b,c fromtablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

    16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
    (select a fromtableA ) except (select a from tableB) except (select a from tableC)

    17、说明:随机取出10条数据
    select top 10 *from tablename order by newid()

    18、说明:随机选择记录
    select newid()

    19、说明:删除重复记录
    1),
    delete fromtablename where id not in (select max(id) from tablename group bycol1,col2,...)
    2),select distinct * into temp from tablename
     delete from tablename
     insert into tablename select * from temp
    评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作
    3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段

    altertable tablename
    --添加一个自增列
    add column_b int identity(1,1)
     delete from tablename where column_b not in(
    select max(column_b) from tablename group by column1,column2,...)
    alter table tablename drop column column_b

    20、说明:列出数据库里所有的表名
    select name fromsysobjects where type='U' // U代表用户

    21、说明:列出表里的所有的列名
    select name fromsyscolumns where id=object_id('TableName')

    22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
    selecttype,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) FROMtablename group by type
    显示结果:
    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3

    23、说明:初始化表table1

    TRUNCATETABLE table1

    24、说明:选择从10到15的记录
    select top 5 *from (select top 15 * from table order by id asc) table_别名 order by id desc

    三、技巧

    1、1=1,1=2的使用,在SQL语句组合时用的较多

    “where1=1” 是表示选择全部    “where 1=2”全部不选,
    如:
    if @strWhere !=''
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' +@strWhere
    end
    else
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
    end

    我们可以直接写成

    错误!未找到目录项。
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库
    --重建索引
    DBCC REINDEX
    DBCC INDEXDEFRAG
    --收缩数据和日志
    DBCC SHRINKDB
    DBCC SHRINKFILE

    3、压缩数据库
    dbccshrinkdatabase(dbname)

    4、转移数据库给新用户以已存在用户权限
    execsp_change_users_login 'update_one','newname','oldname'
    go

    5、检查备份集
    RESTOREVERIFYONLY from disk='E:\dvbbs.bak'

    6、修复数据库
    ALTER DATABASE[dvbbs] SET SINGLE_USER
    GO
    DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
    GO
    ALTER DATABASE [dvbbs] SET MULTI_USER
    GO

    7、日志清除
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
     @MaxMinutes INT,
     @NewSize INT


    USE tablename -- 要操作的数据库名
    SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
    @MaxMinutes = 10, -- Limit on time allowed to wrap log.
     @NewSize = 1  -- 你想设定的日志文件的大小(M)

    Setup /initialize
    DECLARE @OriginalSize int
    SELECT @OriginalSize = size
     FROM sysfiles
     WHERE name = @LogicalFileName
    SELECT 'Original Size of ' + db_name() + ' LOG is ' +
     CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
     CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName
    CREATE TABLE DummyTrans
     (DummyColumn char (8000) not null)


    DECLARE @Counter    INT,
     @StartTime DATETIME,
     @TruncLog   VARCHAR(255)
    SELECT @StartTime = GETDATE(),
     @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

    DBCCSHRINKFILE (@LogicalFileName, @NewSize)
    EXEC (@TruncLog)
    -- Wrap the log if necessary.
    WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has notexpired
     AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =@LogicalFileName)  
     AND (@OriginalSize * 8 /1024) > @NewSize  
     BEGIN -- Outer loop.
    SELECT @Counter = 0
     WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter <50000))
     BEGIN -- update
     INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
     SELECT @Counter = @Counter + 1
     END
     EXEC (@TruncLog)  
     END
    SELECT 'Final Size of ' + db_name() + ' LOG is ' +
     CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
     CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName
    DROP TABLE DummyTrans
    SET NOCOUNT OFF

    8、说明:更改某个表
    execsp_changeobjectowner 'tablename','dbo'

    9、存储更改全部表

    CREATEPROCEDURE dbo.User_ChangeObjectOwnerBatch
    @OldOwner as NVARCHAR(128),
    @NewOwner as NVARCHAR(128)
    AS

    DECLARE@Name    as NVARCHAR(128)
    DECLARE @Owner   as NVARCHAR(128)
    DECLARE @OwnerName   as NVARCHAR(128)

    DECLAREcurObject CURSOR FOR
    select 'Name'    = name,
       'Owner'    = user_name(uid)
    from sysobjects
    where user_name(uid)=@OldOwner
    order by name

    OPEN  curObject
    FETCH NEXT FROM curObject INTO @Name, @Owner
    WHILE(@@FETCH_STATUS=0)
    BEGIN     
    if @Owner=@OldOwner
    begin
       set @OwnerName = @OldOwner + '.' + rtrim(@Name)
       exec sp_changeobjectowner @OwnerName, @NewOwner
    end
    -- select @name,@NewOwner,@OldOwner

    FETCHNEXT FROM curObject INTO @Name, @Owner
    END

    closecurObject
    deallocate curObject
    GO


    10、SQL SERVER中直接循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
        insert into test (userid) values(@i)
        set @i=@i+1
    end
    案例:
    有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:

       Name     score

       Zhangshan   80

       Lishi       59

       Wangwu      50

       Songquan    69

    while((selectmin(score) from tb_table)<60)

    begin

    updatetb_table set score =score*1.01

    wherescore<60

    if (selectmin(score) from tb_table)>60

     break

     else

       continue

    end

     

    数据开发-经典


    1.按姓氏笔画排序:
    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as//从少到多

    2.数据库加密:
    select encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同

    3.取回表中字段:
    declare @listvarchar(1000),
    @sql nvarchar(1000)
    select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.idand a.name='表A'
    set @sql='select '+right(@list,len(@list)-1)+' from 表A'
    exec (@sql)

    4.查看硬盘分区:
    EXEC master..xp_fixeddrives

    5.比较A,B表是否相等:
    if (selectchecksum_agg(binary_checksum(*)) from A)
         =
        (select checksum_agg(binary_checksum(*)) from B)
    print '相等'
    else
    print '不相等'

    6.杀掉所有的事件探察器进程:
    DECLARE hcforeachCURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
    WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
    EXEC sp_msforeach_worker '?'

    7.记录搜索:
    开头到N条记录
    Select Top N *From 表
    -------------------------------
    N到M条记录(要有主索引ID)
    Select Top M-N *From 表 Where ID in (Select Top M ID From表) Order by ID   Desc
    ----------------------------------
    N到结尾记录
    Select Top N *From 表 Order by ID Desc
    案例
    例如1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句, 找出表的第31到第40个记录。

     selecttop 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 whererecid>-1

    例2:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。
    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:获取当前数据库中的所有用户表
    select Name from sysobjects where xtype='u' and status>=0

    10:获取某一个表的所有字段
    select name from syscolumnswhere id=object_id('表名')

    selectname from syscolumns where id in (select id from sysobjects where type = 'u'and name = '表名')

    两种方式的效果相同

    11:查看与某一个表相关的视图、存储过程、函数
    select a.* from sysobjectsa, syscomments b where a.id = b.id and b.text like '%表名%'

    12:查看当前数据库中所有存储过程
    select name as 存储过程名称 from sysobjects where xtype='P'

    13:查询用户创建的所有数据库
    select * from master..sysdatabases D where sid not in(select sid from master..sysloginswhere name='sa')
    或者
    select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

    14:查询某一个表的字段和数据类型
    selectcolumn_name,data_type from information_schema.columns
    where table_name = '表名'

    15:不同服务器数据库之间的数据操作

    --创建链接服务器

    execsp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

    execsp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

    --查询示例

    select *from ITSV.数据库名.dbo.表名

    --导入示例

    select *into 表 from ITSV.数据库名.dbo.表名

    --以后不再使用时删除链接服务器

    execsp_dropserver 'ITSV ', 'droplogins '

     

    --连接远程/局域网数据(openrowset/openquery/opendatasource)

    --1、openrowset

    --查询示例

    select *from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

    --生成本地表

    select *into 表 from openrowset( 'SQLOLEDB ','sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

     

    --把本地表导入远程表

    insertopenrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

    select*from 本地表

    --更新本地表

    update b

    set b.列A=a.列A

     fromopenrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

    ona.column1=b.column1

    --openquery用法需要创建一个连接

    --首先创建一个连接创建链接服务器

    execsp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

    --查询

    select *

    FROMopenquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

    --把本地表导入远程表

    insertopenquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

    select *from 本地表

    --更新本地表

    update b

    set b.列B=a.列B

    FROMopenquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a 

    innerjoin 本地表 b on a.列A=b.列A

     

    --3、opendatasource/openrowset

    SELECT  *

    FROM  opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

    --把本地表导入远程表

    insertopendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

    select *from 本地表 

    SQLServer基本函数

    SQLServer基本函数

    1.字符串函数 长度与分析用

    1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
    2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为起始位置,length为字符串长度,实际应用中以len(expression)取得其长度
    3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符,还用left于之相反
    4,isnull( check_expression , replacement_value)如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

    5,Sp_addtype 自定義數據類型
    例如:EXEC sp_addtype birthday,datetime, 'NULL'

    6,setnocount {on|off}

    使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。

    SETNOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。

    SETNOCOUNT 为 OFF 时,返回计数

    常识

     

    在SQL查询中:from后最多可以跟多少张表或视图:256

    在SQL语句中出现 Order by,查询时,先排序,后取

    在SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。 

           

    SQLServer2000同步复制技术实现步骤

    一、 预备工作

    1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户

    --管理工具

    --计算机管理

    --用户和组

    --右键用户

    --新建用户

    --建立一个隶属于administrator组的登陆windows的用户(SynUser)

    2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:

    我的电脑--D:\ 新建一个目录,名为: PUB

    --右键这个新建的目录

    --属性--共享

    --选择"共享该文件夹"

    --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限

     

    --确定

    3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)

    开始--程序--管理工具--服务

    --右键SQLSERVERAGENT

    --属性--登陆--选择"此账户"

    --输入或者选择第一步中创建的windows登录用户名(SynUser)

    --"密码"中输入该用户的密码

    4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)

    企业管理器

    --右键SQL实例--属性

    --安全性--身份验证

    --选择"SQL Server 和 Windows"

    --确定

    5.在发布服务器和订阅服务器上互相注册

    企业管理器

    --右键SQL Server组

    --新建SQL Server注册...

    --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加

    --下一步--连接使用,选择第二个"SQL Server身份验证"

    --下一步--输入用户名和密码(SynUser)

    --下一步--选择SQL Server组,也可以创建一个新组

    --下一步--完成

    6.对于只能用IP,不能用计算机名的,为其注册服务器别名(此步在实施中没用到)

     (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)

    开始--程序--Microsoft SQL Server--客户端网络实用工具

    --别名--添加

    --网络库选择"tcp/ip"--服务器别名输入SQL服务器名

    --连接参数--服务器名称中输入SQL服务器ip地址

    --如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号

    二、 正式配置

    1、配置发布服务器

    打开企业管理器,在发布服务器(B、C、D)上执行以下步骤:

    (1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导

    (2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器(选择自己)

    (3) [下一步] 设置快照文件夹

    采用默认\\servername\Pub

    (4) [下一步] 自定义配置

    可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置

    否,使用下列默认设置(推荐)

    (5) [下一步] 设置分发数据库名称和位置 采用默认值

    (6) [下一步] 启用发布服务器 选择作为发布的服务器

    (7) [下一步] 选择需要发布的数据库和发布类型

    (8) [下一步] 选择注册订阅服务器

    (9) [下一步] 完成配置

    2、创建出版物

    发布服务器B、C、D上

    (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令

    (2)选择要创建出版物的数据库,然后单击[创建发布]

    (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)

    (4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,

    SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。

    但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器

    (5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表

    注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表

    (6)选择发布名称和描述

    (7)自定义发布属性 向导提供的选择:

    是我将自定义数据筛选,启用匿名订阅和或其他自定义属性

    否根据指定方式创建发布 (建议采用自定义的方式)

    (8)[下一步] 选择筛选发布的方式

    (9)[下一步] 可以选择是否允许匿名订阅

    1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器

    方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加

    否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅

    如果仍然需要匿名订阅则用以下解决办法

    [企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅

    2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示

    (10)[下一步] 设置快照 代理程序调度

    (11)[下一步] 完成配置

    当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库

    有数据

    srv1.库名..author有字段:id,name,phone,

    srv2.库名..author有字段:id,name,telphone,adress

     

    要求:

    srv1.库名..author增加记录则srv1.库名..author记录增加

    srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新

    --*/

     

    --大致的处理步骤

    --1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步

    execsp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip'

    execsp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

    go

    --2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动

    。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动

    go

     

     

    --然后创建一个作业定时调用上面的同步处理存储过程就行了

     

    企业管理器

    --管理

    --SQLServer代理

    --右键作业

    --新建作业

    --"常规"项中输入作业名称

    --"步骤"项

    --新建

    --"步骤名"中输入步骤名

    --"类型"中选择"Transact-SQL 脚本(TSQL)"

    --"数据库"选择执行命令的数据库

    --"命令"中输入要执行的语句: exec p_process

    --确定

    --"调度"项

    --新建调度

    --"名称"中输入调度名称

    --"调度类型"中选择你的作业执行安排

    --如果选择"反复出现"

    --点"更改"来设置你的时间安排

     

     

    然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行

     

    设置方法:

    我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.

     

     

    --3.实现同步处理的方法2,定时同步

     

    --在srv1中创建如下的同步处理存储过程

    createproc p_process

    as

    --更新修改过的数据

    update bset name=i.name,telphone=i.telphone

    fromsrv2.库名.dbo.author b,author i

    whereb.id=i.id and

    (b.name<> i.name or b.telphone <> i.telphone)

     

    --插入新增的数据

    insertsrv2.库名.dbo.author(id,name,telphone)

    selectid,name,telphone from author i

    where notexists(

    select *from srv2.库名.dbo.author where id=i.id)

     

    --删除已经删除的数据(如果需要的话)

    delete b

    fromsrv2.库名.dbo.author b

    where notexists(

    select *from author where id=b.id)

     


    展开全文
  • SQLSERVER重复插入数据

    2013-05-28 11:58:03
    数据库是SQLSERVER2008版的,在存储过程中先对数据进行逻辑检查,然后使用事务存放数据,所以重复插入数据应该是不可能发生的。使用了一年时间,发现有两数据重复插入了。发现的两数据是在很短的时间里同时插入...
  • Sql养成一个好习惯是一笔财富

    千次阅读 2011-10-08 11:03:58
    我们做软件开发的,大部分人都离不开跟数据库打交道,特别是erp开发的,跟数据库打交道更是频繁,存储过程动不动就是上千行,如果数据量大,...要知道sql语句,我想我们有必要知道sqlserver查询分析器怎么执行我么sql

    我们做软件开发的,大部分人都离不开跟数据库打交道,特别是erp开发的,跟数据库打交道更是频繁,存储过程动不动就是上千行,如果数据量大,人员流动大,那么我么还能保证下一段时间系统还能流畅的运行吗?那么还能保证下一个人能看懂我么的存储过程吗?那么我结合公司平时的培训和平时个人工作经验和大家分享一下,希望对大家有帮助。

    要知道sql语句,我想我们有必要知道sqlserver查询分析器怎么执行我么sql语句的,我么很多人会看执行计划,或者用profile来监视和调优查询语句或者存储过程慢的原因,但是如果我们知道查询分析器的执行逻辑顺序,下手的时候就胸有成竹,那么下手是不是有把握点呢?

    一:查询的逻辑执行顺序

    (1) FROM < left_table>

    (2) ON < join_condition>

    (3) <

    展开全文
  • SQL SERVER

    千次阅读 2006-01-17 15:56:00
    SQL Server数据库文件恢复技术SQL Server数据库备份有两种方式,种是使用BACKUP DATABASE将数据库文件备份出去,另外种就是直接拷贝数据库文件mdf和日志文件ldf的方式。下面将主要讨论一下后者的备份与恢复。...
  • sql server

    千次阅读 2012-03-23 01:14:56
    sql server 作者:Sanle 来源:博客园 发布时间:2006-04-27 13:06 阅读:3402 次 原文链接 [收藏]  1.清空日志:DUMP TRANSACTION 库名 WITH NO_LOG  2.截断事务日志:BACKUP LOG 数据库名 WITH NO...
  • 数据库的使用过程中由于程序方面的问题有时候会碰到重复数据,重复数据导致了数据库部分设置不能正确设置……  方法 declare @max integer,@id integer declare cur_rows cursor local for se
  • SQLServer经典SQL大全

    2014-06-05 15:45:20
    经典SQL语句大全 、基础 1、说明:创建数据库 CREATE DATABASE database-name ...3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyN
  • sql serversql语句汇总

    2015-04-25 11:23:23
    sql serversql语句总结 、入门 1、说明:创建数据库 CREATE DATABASE database-name  2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master ...
  • 本文介绍了Sql Server数据库中删除数据表中重复记录的方法。 [项目]数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除![分析]1、生成一张临时表new_users,表结构...
  • sql server 学习教程

    2020-05-19 17:23:32
    SQL Server 系统数据库 在我们安装 SQL Server 的时候,会自动创建下面的四个数据库。 master master 数据库记录了所有的 SQL Server 数据库系统的系统级信息,如用户帐户,配置设置,并在所有其他数据库信息。 ...
  • 我们做软件开发的,大部分人都离不开跟数据库打交道,特别是erp开发的,跟数据库打交道更是频繁,存储过程动不动就是上千行,如果数据量大,人员流动大,那么我么还能保证下段时间系统还能流畅的运行吗?...
  • SQL Server CDC Admin & Monitor

    千次阅读 2016-04-26 19:54:25
    SQL Server CDC Admin & Monitor
  • SQL Server调试sql语句方法()

    千次阅读 2009-11-04 16:34:00
    或者如果数据表有10000个数据行,且同个字段(WHERE条件句有用到这个字段)上有1000笔重复的数据,表扫描也会比使用非聚集索引更快。 查看图形执行计划上的数据表上的弹出式窗口时,请注意”预估的资料行数...
  • Sqlserver常用SQL 1

    千次阅读 2014-11-13 13:45:08
    -----在使用一些对数据库表的临时的SQL语句操作时,可以采用SQL SERVER事务处理,防止对数据操作后发现误操作问题 -----开始事务 Begin tran Insert Into TableName Values(…) -----SQL语句操作不正常,则回滚...
  • sql server 索引

    2012-03-15 17:08:53
    SQL SERVER 建立索引! SQL Server 聚集索引/非聚集索引2011-03-26 19:55前些天看看 SQL Server 的聚集索引与非聚集索引的资料,拿来做做实现,分享一下心得。 聚集索引: 该索引中键值的逻辑顺序决定了表中...
  • 更新视图(Updateble views) 上面我们讲解了“怎样通过远程视图从服务器中把数据读取过来”,接着我们要讲解怎样操作远程视图光标,当然我们不会在这里讨论一些Visual FoxPro数据集的普通操作,这里我们...
  • SQL Server-索引介绍

    千次阅读 2013-05-01 10:54:31
    拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放的,每页容量一般为4K 。为了加快查找的速度,汉语字(词)典一般都有按拼音、笔画、偏旁部首等排序的...
  • 我们做软件开发的,大部分人都离不开跟数据库打交道,特别是erp开发的,跟数据库打交道更是频繁,存储过程动不动就是上千行,如果数据量大,人员流动大,...要知道sql语句,我想我们有必要知道sqlserver查询分析器怎
  • SQLServer创建索引

    2017-06-28 11:25:58
    什么是索引拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放的,每页容量一般为4K 。为了加快查找的速度,汉语字(词)典一般都有按拼音、笔画、偏旁部首...
  • SQL SERVER 数据库实用SQL语句】 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.分页SQL语句 select * from(select (row_number() OVER (ORDER BY tab.ID...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,101
精华内容 4,040
关键字:

serversql多笔重复更新一笔