【正文】 jects where type=39。u39。 讀表語(yǔ)句select name from syscolumns where id=object_id(39。表名39。)讀字段名(sysobject:系統(tǒng)表，存放表，視圖，規(guī)則，存儲(chǔ)過(guò)程，默認(rèn)值和觸發(fā)器信息)select db_id (N39。數(shù)據(jù)庫(kù)名字39。)：檢測(cè)相應(yīng)數(shù)據(jù)庫(kù)的ID(dbid)select object_id (N39。對(duì)象名39。)：檢測(cè)相應(yīng)對(duì)象名的ID(objectid)SELECT * FROM table WITH (HOLDLOCK):鎖定數(shù)據(jù)庫(kù)的一個(gè)表，其他事務(wù)可以讀取表，不能刪除表；SELECT * FROM table WITH (TABLOCKX):鎖定數(shù)據(jù)庫(kù)的一個(gè)表, 其他事務(wù)不能讀取表、更新和刪除；五。UPDATE 。FROM。 UPDATE a SETa.... FROM table a INNER JOIN table b ON ...... WHERR......f exists (select * from where id = object_id(N39。[dbo].[p_copydb]39。) and OBJECTPROPERTY(id, N39。IsProcedure39。) = 1)drop procedure [dbo].[p_copydb]GO/*數(shù)據(jù)庫(kù)數(shù)據(jù)復(fù)制將一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)復(fù)制到另一個(gè)數(shù)據(jù)庫(kù)如果某列在目標(biāo)數(shù)據(jù)庫(kù)中為標(biāo)識(shí)列,將不會(huì)被復(fù)制適用范圍:數(shù)據(jù)庫(kù)結(jié)構(gòu)發(fā)生了變化,想將舊數(shù)據(jù)庫(kù)進(jìn)行升級(jí)這樣就可以根據(jù)新的數(shù)據(jù)庫(kù)結(jié)構(gòu)創(chuàng)建一個(gè)空庫(kù),然后將舊數(shù)據(jù)庫(kù)的所有數(shù)據(jù)復(fù)制到新庫(kù)中鄒建 (引用請(qǐng)保留此信息)*//*調(diào)用示例exec p_copydb 39。bns_aa39。,39。bns_new39。exec p_copydb 39。acc_五醫(yī)39。,39。acc_演示數(shù)據(jù)839。*/create proc p_copydb@o_dbname sysname,要復(fù)制數(shù)據(jù)的數(shù)據(jù)庫(kù)源數(shù)據(jù)庫(kù)@n_dbname sysname接收數(shù)據(jù)的數(shù)據(jù)庫(kù)目標(biāo)數(shù)據(jù)庫(kù)asdeclare @sql nvarchar(4000)禁用約束/觸發(fā)器,防止復(fù)制時(shí)的數(shù)據(jù)沖突set @sql=39。declare tbc cursor for select namefrom [=39。+@n_dbname+39。..sysobjects]%27+@n_dbname+%27..sysobjects[/] where xtype=39。39。U39。39。 and status=039。exec(@sql)declare @tbname sysnameopen tbcfetch next from tbc into @tbnamewhile @@fetch_status=0beginset @sql=39。alter table [=39。+@n_dbname+39。..[39。+@tbname+39。]%27+@n_dbname+%27..%5B%27+@tbname+%27[/]] NOCHECK CONSTRAINT ALL39。exec(@sql)set @sql=39。alter table [=39。+@n_dbname+39。..[39。+@tbname+39。]%27+@n_dbname+%27..%5B%27+@tbname+%27[/]] disable trigger ALL39。exec(@sql)fetch next from tbc into @tbnameendclose tbc復(fù)制數(shù)據(jù)declare @sql1 varchar(8000)set @sql=39。declare tb cursor for select from 39。[=+@o_dbname+39。..sysobjects]+@o_dbname+%27..sysobjects[/] a inner join 39。[=+@n_dbname+39。..sysobjects]+@n_dbname+%27..sysobjects[/] b on =where =39。39。U39。39。 and =39。39。U39。39。39。exec(@sql)open tbfetch next from tb into @tbnamewhile @@fetch_status=0beginselect @sql1=39。39。,@sql=39。select @sql1=@sql1+39。39。,[39。39。++39。39。]39。39。 from(select name from [=39。+@o_dbname+39。..syscolumns]%27+@o_dbname+%27..syscolumns[/] where id in (select id from [=39。+@o_dbname+39。..sysobjects]%27+@o_dbname+%27..sysobjects[/] where [=name=39。39。39。+@tbname+39。39。39。]name=%27%27%27+@tbname+%27%27%27[/])) a inner join (select name from [=39。+@n_dbname+39。..syscolumns]%27+@n_dbname+%27..syscolumns[/] where status0x80 and id in (select id from [=39。+@n_dbname+39。..sysobjects]%27+@n_dbname+%27..sysobjects[/] where [=name=39。39。39。+@tbname+39。39。39。]name=%27%27%27+@tbname+%27%27%27[/])) b on =39。exec sp_executesql @sql,N39。@sql1 nvarchar(4000) out39。,@sql1 outselect @sql1=substring(@sql1,2,8000)exec(39。insert into [=39。+@n_dbname+39。..[39。+@tbname+39。](39。+@sql1]%27+@n_dbname+%27..%5B%27+@tbname+%27%5D%28%27+@sql1[/]+39。) select [=39。+@sql1+39。]%27+@sql1+%27[/] from [=39。+@o_dbname+39。..[39。+@tbname+39。]39。]%27+@o_dbname+%27..%5B%27+@tbname+%27%5D%27[/])if @@error0print(39。insert into [=39。+@n_dbname+39。..[39。+@tbname+39。](39。+@sql1]%27+@n_dbname+%27..%5B%27+@tbname+%27%5D%28%27+@sql1[/] +39。) select [=39。+@sql1+39。]%27+@sql1+%27[/] from [=39。+@o_dbname+39。..[39。+@tbname+39。]39。]%27+@o_dbname+%27..%5B%27+@tbname+%27%5D%27[/])fetch next from tb into @tbnameendclose tbdeallocate tb數(shù)據(jù)復(fù)制完成后啟用約束open tbcfetch next from tbc into @tbnamewhile @@fetch_status=0beginset @sql=39。alter table [=39。+@n_dbname+39。..[39。+@tbname+39。]%27+@n_dbname+%27..%5B%27+@tbname+%27[/]] CHECK CONSTRAINT ALL39。exec(@sql)set @sql=39。alter table [=39。+@n_dbname+39。..[39。+@tbname+39。]%27+@n_dbname+%27..%5B%27+@tbname+%27[/]] enable trigger ALL39。exec(@sql)fetch next from tbc into @tbnameendclose tbcdeallocate tbcgo(從1到n):DECLARE @i intSet @i = 0Update Table1 Set @i = @i + 1,Field1 = @iUpdate 成績(jī)表Set = (Select Count(*) + 1From 成績(jī)表 bWhere )From 成績(jī)表 aSelect a.*From OpenRowSet(39。39。,39。c:\39。39。admin39。39。39。,Table1) aSelect * From OpenDataSource(39。39。,39。Data Source=c:\。User ID=Admin。Password=。Extended properties=Excel 39。)...Sheet1$Select * From Table1 Order By Field1 COLLATE Chinese_PRC_BIN為什么要指定排序規(guī)則呢?參見(jiàn):例,檢查數(shù)據(jù)庫(kù)中的Pub_Users表中是否存在指定的用戶(hù):Select Count(*) From Pub_Users Where [UserName]=39。admin39。 And [PassWord]=39。aaa39。 COLLATE Chinese_PRC_BIN默認(rèn)比較是不區(qū)分大小寫(xiě)的,如果不加COLLATE Chinese_PRC_BIN,那么密碼aaa與AAA是等效的,每個(gè)條件都要指定排序規(guī)則,上例中用戶(hù)名就不區(qū)分大小寫(xiě). By的一個(gè)小技巧Order By可以指定列序而不用指定列名,在下面的例子里說(shuō)明它的用處(注意,第三列未指定別名)Select ,(Select Count(*) From TableB b Where =) From TableA a Order By 3下面介紹下oracle安裝文件目錄結(jié)構(gòu)(以D盤(pán)為例)：l 默認(rèn)ORACLE_BASEn 在Windows中，默認(rèn)的ORACLE_BASE目錄是：D:\oracle\product\n 在UNIX中，默認(rèn)的ORACLE_BASE目錄是：/pm/app/oracle/所有的ORACLE軟件文件和數(shù)據(jù)庫(kù)文件都位于ORACLE_BASE下面的子目錄中。l 默認(rèn)ORACLE_HOMEn 在Windows中，默認(rèn)的ORACLE_HOME目錄是：D:\oracle\product\\dbctn 在UNIX中，默認(rèn)的ORACLE_BASE目錄是：/pm/app/oracle/ORACLE_HOME是訪問(wèn)所有ORACLE軟件的路徑。l Admin文件夾，存儲(chǔ)初始化文件和日志文件l Oradata\oract文件夾，、熟悉了利用enterprice manager database control 進(jìn)行數(shù)據(jù)的基本管理，如倉(cāng)健用戶(hù)，分配權(quán)限，設(shè)置中斷點(diǎn)，啟動(dòng)監(jiān)聽(tīng)程序(Listener),保存身份驗(yàn)證，net manager 配置。還有如： 1。遠(yuǎn)程終端配置； 2。數(shù)據(jù)導(dǎo)出/導(dǎo)出； 3。數(shù)據(jù)庫(kù)備份； 4。isql*plus語(yǔ)法；