數據開發-經典

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 表
-------------------------------
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個記錄。

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..sysdatabases 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,isnull( check_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