1奇徒、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)
CREATE?DATABASE?database-name
2查辩、說(shuō)明：刪除數(shù)據(jù)庫(kù)
drop?database?dbname
3、說(shuō)明：備份sql server
--- 創(chuàng)建 備份數(shù)據(jù)的 device
USE master
EXEC?sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 開(kāi)始 備份
BACKUP DATABASE?pubs TO?testBack
4、說(shuō)明：創(chuàng)建新表
create?table?tabname(col1 type1 [not?null] [primary?key],col2 type2 [not?null],..)
根據(jù)已有的表創(chuàng)建新表：
A：create?table?tab_new like?tab_old (使用舊表創(chuàng)建新表)
B：create?table?tab_new as?select?col1,col2… from?tab_old definition only
5、說(shuō)明：刪除新表
drop?table?tabname
6、說(shuō)明：增加一個(gè)列
Alter?table?tabname add?column?col type
注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類型的長(zhǎng)度。
7、說(shuō)明：添加主鍵： Alter?table?tabname add?primary?key(col)
說(shuō)明：刪除主鍵： Alter?table?tabname drop?primary?key(col)
8诈唬、說(shuō)明：創(chuàng)建索引：create?[unique] index?idxname on?tabname(col….)
刪除索引：drop?index?idxname
注：索引是不可更改的阅仔，想更改必須刪除重新建空民。
9耸棒、說(shuō)明：創(chuàng)建視圖：create?view?viewname as?select?statement
刪除視圖：drop?view?viewname
10碍现、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句
選擇：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的語(yǔ)法很精妙慢睡，查資料!
排序：select?* from?table1 order?by?field1,field2 [desc]
總數(shù)：select?count?as?totalcount from?table1
求和：select?sum(field1) as?sumvalue from?table1
平均：select?avg(field1) as?avgvalue from?table1
最大：select?max(field1) as?maxval from?table1
最小：select?min(field1) as?minval from?table1
11、說(shuō)明：幾個(gè)高級(jí)查詢運(yùn)算詞
A： UNION?運(yùn)算符
UNION?運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL?隨 UNION?一起使用時(shí)（即 UNION?ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2。
B： EXCEPT?運(yùn)算符
EXCEPT?運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL?隨 EXCEPT?一起使用時(shí) (EXCEPT?ALL)，不消除重復(fù)行。
C： INTERSECT?運(yùn)算符
INTERSECT?運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL?隨 INTERSECT?一起使用時(shí) (INTERSECT?ALL)，不消除重復(fù)行。
注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。
12逛万、說(shuō)明：使用外連接
A指郁、left?（outer） join：
左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。
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:
右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行锚沸。
C：full/cross?（outer） join：
全外連接：不僅包括符號(hào)連接表的匹配行恬叹，還包括兩個(gè)連接表中的所有記錄。
12菱农、分組:Group?by:
??一張表的妖，一旦分組完成后零抬，查詢后只能得到組相關(guān)的信息。
?組相關(guān)的信息：（統(tǒng)計(jì)信息） count,sum,max,min,avg?分組的標(biāo)準(zhǔn))
????在SQLServer中分組時(shí)：不能以text,ntext,image類型的字段作為分組依據(jù)
?在selecte統(tǒng)計(jì)函數(shù)中的字段锰扶，不能和普通的字段放在一起京闰；
13痊土、對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作：
?分離數(shù)據(jù)庫(kù)： sp_detach_db; 附加數(shù)據(jù)庫(kù)：sp_attach_db 后接表明酌呆，附加需要完整的路徑名
14.如何修改數(shù)據(jù)庫(kù)的名稱:
sp_renamedb 'old_name', 'new_name'
二、提升
1、說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) (Access可用)
法一：select?* into?b from?a where?1<>1（僅用于SQlServer）
法二：select?top?0 * into?b from?a
2拴念、說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) (Access可用)
insert?into?b(a, b, c) select?d,e,f from?b;
3公般、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑) (Access可用)
insert?into?b(a, b, c) select?d,e,f from?b in?‘具體數(shù)據(jù)庫(kù)’ where?條件
例子：..from?b in?'"&Server.MapPath(".")&"\data.mdb" &"'?where..
4、說(shuō)明：子查詢(表名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、說(shuō)明：顯示文章钓猬、提交人和最后回復(fù)時(shí)間
select?a.title,a.username,b.adddate from?table?a,(select?max(adddate) adddate from?table?where?table.title=a.title) b
6局齿、說(shuō)明：外連接查詢(表名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谣妻、說(shuō)明：在線視圖查詢(表名1：a )
select?* from?(SELECT?a,b,c FROM?a) T where?t.a > 1;
8、說(shuō)明：between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not?between不包括
select?* from?table1 where?time?between?time1 and?time2
select?a,b,c, from?table1 where?a not?between?數(shù)值1 and?數(shù)值2
9巡莹、說(shuō)明：in?的使用方法
select?* from?table1 where?a [not] in?(‘值1’,’值2’,’值4’,’值6’)
10、說(shuō)明：兩張關(guān)聯(lián)表吨述，刪除主表中已經(jīng)在副表中沒(méi)有的信息
delete?from?table1 where?not?exists ( select?* from?table2 where?table1.field1=table2.field1 )
11、說(shuō)明：四表聯(lián)查問(wèn)題：
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、說(shuō)明：日程安排提前五分鐘提醒
SQL: select?* from?日程安排 where?datediff('minute',f開(kāi)始時(shí)間,getdate())>5
13捡絮、說(shuō)明：一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)
select?top?10 b.* from?(select?top?20 主鍵字段,排序字段 from?表名 order?by?排序字段 desc) a,表名 b where?b.主鍵字段 = a.主鍵字段 order?by?a.排序字段
具體實(shí)現(xiàn)：
關(guān)于數(shù)據(jù)庫(kù)分頁(yè)：
??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后不能直接跟一個(gè)變量略板，所以在實(shí)際應(yīng)用中只有這樣的進(jìn)行特殊的處理。Rid為一個(gè)標(biāo)識(shí)列汇陆，如果top后還有具體的字段因妇，這樣做是非常有好處的。因?yàn)檫@樣可以避免 top的字段如果是邏輯索引的拓颓，查詢的結(jié)果后實(shí)際表中的不一致（邏輯索引中的數(shù)據(jù)有可能和數(shù)據(jù)表中的不一致，而查詢時(shí)如果處在索引則首先查詢索引）
14、說(shuō)明：前10條記錄
select?top?10 * form table1 where?范圍
15、說(shuō)明：選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績(jī)排名,等等.)
select?a,b,c from?tablename ta where?a=(select?max(a) from?tablename tb where?tb.b=ta.b)
16、說(shuō)明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表
(select?a from?tableA ) except?(select?a from?tableB) except?(select?a from?tableC)
17鲁猩、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)
select?top?10 * from?tablename order?by?newid()
18铐刘、說(shuō)明：隨機(jī)選擇記錄
select?newid()
19、說(shuō)明：刪除重復(fù)記錄
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
評(píng)價(jià)： 這種操作牽連大量的數(shù)據(jù)的移動(dòng)蜒滩，這種做法不適合大容量但數(shù)據(jù)操作
3),例如：在一個(gè)外部表中導(dǎo)入數(shù)據(jù)滨达，由于某些原因第一次只導(dǎo)入了一部分，但很難判斷具體位置俯艰，這樣只有在下一次全部導(dǎo)入捡遍，這樣也就產(chǎn)生好多重復(fù)的字段，怎樣刪除重復(fù)字段
alter?table?tablename
--添加一個(gè)自增列
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竹握、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名
select?name?from?sysobjects where?type='U'?// U代表用戶
21画株、說(shuō)明：列出表里的所有的列名
select?name?from?syscolumns where?id=object_id('TableName')
22、說(shuō)明：列示type啦辐、vender谓传、pcs字段，以type字段排列芹关，case可以方便地實(shí)現(xiàn)多重選擇续挟，類似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
顯示結(jié)果：
type vender pcs
電腦 A 1
電腦 A 1
光盤 B 2
光盤 A 2
手機(jī) B 3
手機(jī) C 3
23侥衬、說(shuō)明：初始化表table1
TRUNCATE?TABLE?table1
24诗祸、說(shuō)明：選擇從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語(yǔ)句組合時(shí)用的較多
“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
我們可以直接寫成
錯(cuò)誤功偿！未找到目錄項(xiàng)。
set?@strSQL = 'select count(*) as Total from ['?+ @tblName + '] where 1=1 安定 '+ @strWhere 2往堡、收縮數(shù)據(jù)庫(kù)
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮數(shù)據(jù)和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3械荷、壓縮數(shù)據(jù)庫(kù)
dbcc shrinkdatabase(dbname)
4、轉(zhuǎn)移數(shù)據(jù)庫(kù)給新用戶以已存在用戶權(quán)限
exec?sp_change_users_login 'update_one','newname','oldname'
go
5虑灰、檢查備份集
RESTORE VERIFYONLY from?disk='E:\dvbbs.bak'
6养葵、修復(fù)數(shù)據(jù)庫(kù)
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 -- 要操作的數(shù)據(jù)庫(kù)名
SELECT?@LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
?@NewSize = 1? -- 你想設(shè)定的日志文件的大小(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瘩缆、說(shuō)明：更改某個(gè)表
exec?sp_changeobjectowner 'tablename','dbo'
9关拒、存儲(chǔ)更改全部表
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中直接循環(huán)寫入數(shù)據(jù)
declare?@i int
set?@i=1
while @i<30
begin
????insert?into?test (userid) values(@i)
????set?@i=@i+1
end
案例：
有如下表，要求就裱中所有沒(méi)有及格的成績(jī)着绊，在每次增長(zhǎng)0.1的基礎(chǔ)上谐算，使他們剛好及格:
?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
數(shù)據(jù)開(kāi)發(fā)-經(jīng)典
1.按姓氏筆畫(huà)排序:
Select?* From?TableName Order?By?CustomerName Collate?Chinese_PRC_Stroke_ci_as //從少到多
2.數(shù)據(jù)庫(kù)加密:
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.查看硬盤分區(qū):
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.殺掉所有的事件探察器進(jìn)程:
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.記錄搜索:
開(kāi)頭到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到結(jié)尾記錄
Select?Top?N * From?表 Order?by?ID Desc
案例
例如1：一張表有一萬(wàn)多條記錄洲脂，表的第一個(gè)字段 RecID 是自增長(zhǎng)字段， 寫一個(gè)SQL語(yǔ)句剧包，找出表的第31到第40個(gè)記錄恐锦。
?select?top?10 recid from?A where?recid not?in(select?top?30 recid from?A)
分析：如果這樣寫會(huì)產(chǎn)生某些問(wèn)題，如果recid在表中存在邏輯索引疆液。
?select?top?10 recid from?A where……是從索引中查找一铅，而后面的select?top?30 recid from?A則在數(shù)據(jù)表中查找，這樣由于索引中的順序有可能和數(shù)據(jù)表中的不一致堕油，這樣就導(dǎo)致查詢到的不是本來(lái)的欲得到的數(shù)據(jù)潘飘。
解決方案
1， 用order?by?select?top?30 recid from?A order?by?ricid 如果該字段不是自增長(zhǎng)掉缺，就會(huì)出現(xiàn)問(wèn)題
2卜录， 在那個(gè)子查詢中也加條件：select?top?30 recid from?A where?recid>-1
例2：查詢表中的最后以條記錄，并不知道這個(gè)表共有多少數(shù)據(jù),以及表結(jié)構(gòu)眶明。
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：獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有用戶表
select?Name?from?sysobjects where?xtype='u'?and?status>=0
10：獲取某一個(gè)表的所有字段
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：查看與某一個(gè)表相關(guān)的視圖艰毒、存儲(chǔ)過(guò)程、函數(shù)
select?a.* from?sysobjects a, syscomments b where?a.id = b.id and?b.text like?'%表名%'
12：查看當(dāng)前數(shù)據(jù)庫(kù)中所有存儲(chǔ)過(guò)程
select?name?as?存儲(chǔ)過(guò)程名稱 from?sysobjects where?xtype='P'
13：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫(kù)
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：查詢某一個(gè)表的字段和數(shù)據(jù)類型
select?column_name,data_type from?information_schema.columns
where?table_name = '表名'
15：不同服務(wù)器數(shù)據(jù)庫(kù)之間的數(shù)據(jù)操作
--創(chuàng)建鏈接服務(wù)器
exec?sp_addlinkedserver?? 'ITSV ', ' ', 'SQLOLEDB ', '遠(yuǎn)程服務(wù)器名或ip地址 '
exec?sp_addlinkedsrvlogin? 'ITSV ', 'false ',null, '用戶名 ', '密碼 '
--查詢示例
select?* from?ITSV.數(shù)據(jù)庫(kù)名.dbo.表名
--導(dǎo)入示例
select?* into?表 from?ITSV.數(shù)據(jù)庫(kù)名.dbo.表名
--以后不再使用時(shí)刪除鏈接服務(wù)器
exec?sp_dropserver? 'ITSV ', 'droplogins '
--連接遠(yuǎn)程/局域網(wǎng)數(shù)據(jù)(openrowset/openquery/opendatasource)
--1搜囱、openrowset
--查詢示例
select?* from?openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
--生成本地表
select?* into?表 from?openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
--把本地表導(dǎo)入遠(yuǎn)程表
insert?openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
select?*from?本地表
--更新本地表
update?b
set?b.列A=a.列A
?from?openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)as?a inner?join?本地表 b
on?a.column1=b.column1
--openquery用法需要?jiǎng)?chuàng)建一個(gè)連接
--首先創(chuàng)建一個(gè)連接創(chuàng)建鏈接服務(wù)器
exec?sp_addlinkedserver?? 'ITSV ', ' ', 'SQLOLEDB ', '遠(yuǎn)程服務(wù)器名或ip地址 '
--查詢
select?*
FROM?openquery(ITSV,? 'SELECT *? FROM 數(shù)據(jù)庫(kù).dbo.表名 ')
--把本地表導(dǎo)入遠(yuǎn)程表
insert?openquery(ITSV,? 'SELECT *? FROM 數(shù)據(jù)庫(kù).dbo.表名 ')
select?* from?本地表
--更新本地表
update?b
set?b.列B=a.列B
FROM?openquery(ITSV,? 'SELECT * FROM 數(shù)據(jù)庫(kù).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
--把本地表導(dǎo)入遠(yuǎn)程表
insert?opendatasource( 'SQLOLEDB ',? 'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ').數(shù)據(jù)庫(kù).dbo.表名
select?* from?本地表
SQL Server基本函數(shù)
SQL Server基本函數(shù)
1.字符串函數(shù) 長(zhǎng)度與分析用
1,datalength(Char_expr) 返回字符串包含字符數(shù),但不包含后面的空格
2,substring(expression,start,length) 取子串，字符串的下標(biāo)是從“1”犬辰，start為起始位置，length為字符串長(zhǎng)度冰单，實(shí)際應(yīng)用中以len(expression)取得其長(zhǎng)度
3,right(char_expr,int_expr) 返回字符串右邊第int_expr個(gè)字符幌缝，還用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值诫欠，不為空涵卵，就返回check_expression字符操作類
5,Sp_addtype 自定義數(shù)據(jù)類型
例如：EXEC?sp_addtype birthday, datetime, 'NULL'
6,set?nocount {on|off}
使返回的結(jié)果中不包含有關(guān)受 Transact-SQL 語(yǔ)句影響的行數(shù)的信息。如果存儲(chǔ)過(guò)程中包含的一些語(yǔ)句并不返回許多實(shí)際的數(shù)據(jù)荒叼，則該設(shè)置由于大量減少了網(wǎng)絡(luò)流量轿偎，因此可顯著提高性能。SET?NOCOUNT 設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置被廓，而不是在分析時(shí)設(shè)置坏晦。
SET?NOCOUNT 為 ON?時(shí)，不返回計(jì)數(shù)（表示受 Transact-SQL 語(yǔ)句影響的行數(shù)）。
SET?NOCOUNT 為 OFF?時(shí)昆婿，返回計(jì)數(shù)
常識(shí)
在SQL查詢中：from后最多可以跟多少?gòu)埍砘蛞晥D：256
在SQL語(yǔ)句中出現(xiàn) Order?by,查詢時(shí)球碉，先排序，后取
在SQL中仓蛆，一個(gè)字段的最大容量是8000睁冬，而對(duì)于nvarchar(4000),由于nvarchar是Unicode碼。
SQLServer2000同步復(fù)制技術(shù)實(shí)現(xiàn)步驟
一看疙、 預(yù)備工作
1.發(fā)布服務(wù)器,訂閱服務(wù)器都創(chuàng)建一個(gè)同名的windows用戶,并設(shè)置相同的密碼,做為發(fā)布快照文件夾的有效訪問(wèn)用戶
--管理工具
--計(jì)算機(jī)管理
--用戶和組
--右鍵用戶
--新建用戶
--建立一個(gè)隸屬于administrator組的登陸windows的用戶（SynUser）
2.在發(fā)布服務(wù)器上,新建一個(gè)共享目錄,做為發(fā)布的快照文件的存放目錄,操作:
我的電腦--D:\ 新建一個(gè)目錄,名為: PUB
--右鍵這個(gè)新建的目錄
--屬性--共享
--選擇"共享該文件夾"
--通過(guò)"權(quán)限"按紐來(lái)設(shè)置具體的用戶權(quán)限,保證第一步中創(chuàng)建的用戶(SynUser) 具有對(duì)該文件夾的所有權(quán)限
--確定
3.設(shè)置SQL代理(SQLSERVERAGENT)服務(wù)的啟動(dòng)用戶(發(fā)布/訂閱服務(wù)器均做此設(shè)置)
開(kāi)始--程序--管理工具--服務(wù)
--右鍵SQLSERVERAGENT
--屬性--登陸--選擇"此賬戶"
--輸入或者選擇第一步中創(chuàng)建的windows登錄用戶名（SynUser）
--"密碼"中輸入該用戶的密碼
4.設(shè)置SQL Server身份驗(yàn)證模式,解決連接時(shí)的權(quán)限問(wèn)題(發(fā)布/訂閱服務(wù)器均做此設(shè)置)
企業(yè)管理器
--右鍵SQL實(shí)例--屬性
--安全性--身份驗(yàn)證
--選擇"SQL Server 和 Windows"
--確定
5.在發(fā)布服務(wù)器和訂閱服務(wù)器上互相注冊(cè)
企業(yè)管理器
--右鍵SQL Server組
--新建SQL Server注冊(cè)...
--下一步--可用的服務(wù)器中,輸入你要注冊(cè)的遠(yuǎn)程服務(wù)器名 --添加
--下一步--連接使用,選擇第二個(gè)"SQL Server身份驗(yàn)證"
--下一步--輸入用戶名和密碼（SynUser）
--下一步--選擇SQL Server組,也可以創(chuàng)建一個(gè)新組
--下一步--完成
6.對(duì)于只能用IP,不能用計(jì)算機(jī)名的,為其注冊(cè)服務(wù)器別名（此步在實(shí)施中沒(méi)用到）
?(在連接端配置,比如,在訂閱服務(wù)器上配置的話,服務(wù)器名稱中輸入的是發(fā)布服務(wù)器的IP)
開(kāi)始--程序--Microsoft SQL Server--客戶端網(wǎng)絡(luò)實(shí)用工具
--別名--添加
--網(wǎng)絡(luò)庫(kù)選擇"tcp/ip"--服務(wù)器別名輸入SQL服務(wù)器名
--連接參數(shù)--服務(wù)器名稱中輸入SQL服務(wù)器ip地址
--如果你修改了SQL的端口,取消選擇"動(dòng)態(tài)決定端口",并輸入對(duì)應(yīng)的端口號(hào)
二豆拨、 正式配置
1、配置發(fā)布服務(wù)器
打開(kāi)企業(yè)管理器能庆，在發(fā)布服務(wù)器（B施禾、C、D）上執(zhí)行以下步驟:
(1) 從[工具]下拉菜單的[復(fù)制]子菜單中選擇[配置發(fā)布相味、訂閱服務(wù)器和分發(fā)]出現(xiàn)配置發(fā)布和分發(fā)向?qū)?/p>
(2) [下一步] 選擇分發(fā)服務(wù)器 可以選擇把發(fā)布服務(wù)器自己作為分發(fā)服務(wù)器或者其他sql的服務(wù)器（選擇自己）
(3) [下一步] 設(shè)置快照文件夾
采用默認(rèn)\\servername\Pub
(4) [下一步] 自定義配置
可以選擇:是,讓我設(shè)置分發(fā)數(shù)據(jù)庫(kù)屬性啟用發(fā)布服務(wù)器或設(shè)置發(fā)布設(shè)置
否,使用下列默認(rèn)設(shè)置（推薦）
(5) [下一步] 設(shè)置分發(fā)數(shù)據(jù)庫(kù)名稱和位置 采用默認(rèn)值
(6) [下一步] 啟用發(fā)布服務(wù)器 選擇作為發(fā)布的服務(wù)器
(7) [下一步] 選擇需要發(fā)布的數(shù)據(jù)庫(kù)和發(fā)布類型
(8) [下一步] 選擇注冊(cè)訂閱服務(wù)器
(9) [下一步] 完成配置
2拾积、創(chuàng)建出版物
發(fā)布服務(wù)器B、C丰涉、D上
(1)從[工具]菜單的[復(fù)制]子菜單中選擇[創(chuàng)建和管理發(fā)布]命令
(2)選擇要?jiǎng)?chuàng)建出版物的數(shù)據(jù)庫(kù)拓巧，然后單擊[創(chuàng)建發(fā)布]
(3)在[創(chuàng)建發(fā)布向?qū)的提示對(duì)話框中單擊[下一步]系統(tǒng)就會(huì)彈出一個(gè)對(duì)話框。對(duì)話框上的內(nèi)容是復(fù)制的三個(gè)類型一死。我們現(xiàn)在選第一個(gè)也就是默認(rèn)的快照發(fā)布(其他兩個(gè)大家可以去看看幫助)
(4)單擊[下一步]系統(tǒng)要求指定可以訂閱該發(fā)布的數(shù)據(jù)庫(kù)服務(wù)器類型,
SQLSERVER允許在不同的數(shù)據(jù)庫(kù)如 orACLE或ACCESS之間進(jìn)行數(shù)據(jù)復(fù)制肛度。
但是在這里我們選擇運(yùn)行"SQL SERVER 2000"的數(shù)據(jù)庫(kù)服務(wù)器
(5)單擊[下一步]系統(tǒng)就彈出一個(gè)定義文章的對(duì)話框也就是選擇要出版的表
注意: 如果前面選擇了事務(wù)發(fā)布 則再這一步中只能選擇帶有主鍵的表
(6)選擇發(fā)布名稱和描述
(7)自定義發(fā)布屬性 向?qū)峁┑倪x擇:
是 我將自定義數(shù)據(jù)篩選,啟用匿名訂閱和或其他自定義屬性
否 根據(jù)指定方式創(chuàng)建發(fā)布 （建議采用自定義的方式）
(8)[下一步] 選擇篩選發(fā)布的方式
(9)[下一步] 可以選擇是否允許匿名訂閱
1)如果選擇署名訂閱,則需要在發(fā)布服務(wù)器上添加訂閱服務(wù)器
方法: [工具]->[復(fù)制]->[配置發(fā)布、訂閱服務(wù)器和分發(fā)的屬性]->[訂閱服務(wù)器] 中添加
否則在訂閱服務(wù)器上請(qǐng)求訂閱時(shí)會(huì)出現(xiàn)的提示:改發(fā)布不允許匿名訂閱
如果仍然需要匿名訂閱則用以下解決辦法
[企業(yè)管理器]->[復(fù)制]->[發(fā)布內(nèi)容]->[屬性]->[訂閱選項(xiàng)] 選擇允許匿名請(qǐng)求訂閱
2)如果選擇匿名訂閱,則配置訂閱服務(wù)器時(shí)不會(huì)出現(xiàn)以上提示
(10)[下一步] 設(shè)置快照 代理程序調(diào)度
(11)[下一步] 完成配置
當(dāng)完成出版物的創(chuàng)建后創(chuàng)建出版物的數(shù)據(jù)庫(kù)也就變成了一個(gè)共享數(shù)據(jù)庫(kù)
有數(shù)據(jù)
srv1.庫(kù)名..author有字段:id,name,phone,
srv2.庫(kù)名..author有字段:id,name,telphone,adress
要求：
srv1.庫(kù)名..author增加記錄則srv1.庫(kù)名..author記錄增加
srv1.庫(kù)名..author的phone字段更新投慈，則srv1.庫(kù)名..author對(duì)應(yīng)字段telphone更新
--*/
--大致的處理步驟
--1.在 srv1 上創(chuàng)建連接服務(wù)器,以便在 srv1 中操作 srv2,實(shí)現(xiàn)同步
exec?sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql實(shí)例名或ip'
exec?sp_addlinkedsrvlogin 'srv2','false',null,'用戶名','密碼'
go
--2.在 srv1 和 srv2 這兩臺(tái)電腦中,啟動(dòng) msdtc(分布式事務(wù)處理服務(wù)),并且設(shè)置為自動(dòng)啟動(dòng)
承耿。我的電腦--控制面板--管理工具--服務(wù)--右鍵 Distributed Transaction Coordinator--屬性--啟動(dòng)--并將啟動(dòng)類型設(shè)置為自動(dòng)啟動(dòng)
go
--然后創(chuàng)建一個(gè)作業(yè)定時(shí)調(diào)用上面的同步處理存儲(chǔ)過(guò)程就行了
企業(yè)管理器
--管理
--SQL Server代理
--右鍵作業(yè)
--新建作業(yè)
--"常規(guī)"項(xiàng)中輸入作業(yè)名稱
--"步驟"項(xiàng)
--新建
--"步驟名"中輸入步驟名
--"類型"中選擇"Transact-SQL 腳本(TSQL)"
--"數(shù)據(jù)庫(kù)"選擇執(zhí)行命令的數(shù)據(jù)庫(kù)
--"命令"中輸入要執(zhí)行的語(yǔ)句: exec p_process
--確定
--"調(diào)度"項(xiàng)
--新建調(diào)度
--"名稱"中輸入調(diào)度名稱
--"調(diào)度類型"中選擇你的作業(yè)執(zhí)行安排
--如果選擇"反復(fù)出現(xiàn)"
--點(diǎn)"更改"來(lái)設(shè)置你的時(shí)間安排
然后將SQL Agent服務(wù)啟動(dòng),并設(shè)置為自動(dòng)啟動(dòng),否則你的作業(yè)不會(huì)被執(zhí)行
設(shè)置方法:
我的電腦--控制面板--管理工具--服務(wù)--右鍵 SQLSERVERAGENT--屬性--啟動(dòng)類型--選擇"自動(dòng)啟動(dòng)"--確定.
--3.實(shí)現(xiàn)同步處理的方法2,定時(shí)同步
--在srv1中創(chuàng)建如下的同步處理存儲(chǔ)過(guò)程
create?proc p_process
as
--更新修改過(guò)的數(shù)據(jù)
update?b set?name=i.name,telphone=i.telphone
from?srv2.庫(kù)名.dbo.author b,author i
where?b.id=i.id and
(b.name?<> i.name?or?b.telphone <> i.telphone)
--插入新增的數(shù)據(jù)
insert?srv2.庫(kù)名.dbo.author(id,name,telphone)
select?id,name,telphone from?author i
where?not?exists(
select?* from?srv2.庫(kù)名.dbo.author where?id=i.id)
--刪除已經(jīng)刪除的數(shù)據(jù)(如果需要的話)
delete?b
from?srv2.庫(kù)名.dbo.author b
where?not?exists(
select?* from?author where?id=b.id)
go
