第一篇：數(shù)據(jù)庫(kù)實(shí)驗(yàn)心得

我在sql server 索引基礎(chǔ)知識(shí)系列中,第一篇就講了記錄數(shù)據(jù)的基本格式。那里主要講解的是，數(shù)據(jù)庫(kù)的最小讀存單元：數(shù)據(jù)頁(yè)。一個(gè)數(shù)據(jù)頁(yè)是8k大小。

對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，它不會(huì)每次有一個(gè)數(shù)據(jù)頁(yè)變化后，就存到硬盤(pán)。而是變化達(dá)到一定數(shù)量級(jí)后才會(huì)作這個(gè)操作。這時(shí)候，數(shù)據(jù)庫(kù)并不是以數(shù)據(jù)頁(yè)來(lái)作為操作單元，而是以64k的數(shù)據(jù)（8個(gè)數(shù)據(jù)頁(yè)，一個(gè)區(qū)）作為操作單元。

區(qū)是管理空間的基本單位。一個(gè)區(qū)是八個(gè)物理上連續(xù)的頁(yè)（即 64 kb）。這意味著 sql server 數(shù)據(jù)庫(kù)中每 mb 有 16 個(gè)區(qū)。

為了使空間分配更有效，sql server 不會(huì)將所有區(qū)分配給包含少量數(shù)據(jù)的表。sql server 有兩種類(lèi)型的區(qū)：

統(tǒng)一區(qū)，由單個(gè)對(duì)象所有。區(qū)中的所有 8 頁(yè)只能由所屬對(duì)象使用。

混合區(qū)，最多可由八個(gè)對(duì)象共享。區(qū)中八頁(yè)的每頁(yè)可由不同的對(duì)象所有。

通常從混合區(qū)向新表或索引分配頁(yè)。當(dāng)表或索引增長(zhǎng)到 8 頁(yè)時(shí)，將變成使用統(tǒng)一區(qū)進(jìn)行后續(xù)分配。如果對(duì)現(xiàn)有表創(chuàng)建索引，并且該表包含的行足以在索引中生成 8 頁(yè)，則對(duì)該索引的所有分配都使用統(tǒng)一區(qū)進(jìn)行。

為何會(huì)這樣呢？

其實(shí)很簡(jiǎn)單：

讀或?qū)?8kb 的時(shí)間與讀或?qū)?64 kb的時(shí)間幾乎相同。

在 8 kb 到 64 kb 范圍之內(nèi)，單個(gè)磁盤(pán) i/o 傳輸操作所花的時(shí)間主要是磁盤(pán)取數(shù)臂和讀/寫(xiě)磁頭運(yùn)動(dòng)的時(shí)間。

因此，從數(shù)學(xué)上來(lái)講，當(dāng)需要傳輸 64 kb 以上的 sql 數(shù)據(jù)時(shí)，盡可能地執(zhí)行 64 kb 磁盤(pán)傳輸是有益的，即分成數(shù)個(gè)64k的操作。

因?yàn)?64 kb 傳輸基本上與 8 kb 傳輸一樣快，而每次傳輸?shù)?sql server 數(shù)據(jù)是 8 kb 傳輸?shù)?8 倍。

我們通過(guò)一個(gè)實(shí)例來(lái)看 有and 操作符時(shí)候的最常見(jiàn)的一種情況。我們有下面一個(gè)表，create table [dbo].[member]([member_no] [dbo].[numeric_id] identity(1,1)not null, [lastname] [dbo].[shortstring] not null, [firstname] [dbo].[shortstring] not null, [middleinitial] [dbo].[letter] null, [street] [dbo].[shortstring] not null, [city] [dbo].[shortstring] not null, [state_prov] [dbo].[statecode] not null, [country] [dbo].[countrycode] not null, [mail_code] [dbo].[mailcode] not null, [phone_no] [dbo].[phonenumber] null, [photograph] [image] null, [issue_dt] [datetime] not null default(getdate()), [expr_dt] [datetime] not null default(dateadd(year,1,getdate())), [region_no] [dbo].[numeric_id] not null, [corp_no] [dbo].[numeric_id] null, [prev_balance] [money] null default(0), [curr_balance] [money] null default(0), [member_code] [dbo].[status_code] not null default(' '))

這個(gè)表具備下面的四個(gè)索引：

索引名 細(xì)節(jié) 索引的列

member_corporation_link nonclustered located on primary corp_no

member_ident clustered, unique, primary key located on primary member_no

member_region_link nonclustered located on primary region_no

memberfirstname nonclustered located on primary firstname

當(dāng)我們執(zhí)行下面的sql查詢時(shí)候，select m.member_no, m.firstname, m.region_nofrom dbo.member as mwhere m.firstname like 'k%' and m.region_no > 6 and m.member_no < 5000go

sql server 會(huì)根據(jù)索引方式，優(yōu)化成下面方式來(lái)執(zhí)行。

select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname from dbo.member as m where m.firstname like 'k%' and m.member_no < 5000)a ,--這個(gè)查詢可以直接使用 memberfirstname 非聚集索引，而且這個(gè)非聚集索引覆蓋了所有查詢列--實(shí)際執(zhí)行時(shí)，只需要 邏輯讀取 3 次

(select m.member_no, m.region_no from dbo.member as mwhere m.region_no > 6)b

--這個(gè)查詢可以直接使用 member_region_link 非聚集索引，而且這個(gè)非聚集索引覆蓋了所有查詢列--實(shí)際執(zhí)行時(shí)，只需要 邏輯讀取 10 次

where a.member_no = b.member_no

不信，你可以看這兩個(gè)sql 的執(zhí)行計(jì)劃，以及邏輯讀信息，都是一樣的。

其實(shí)上面的sql，如果優(yōu)化成下面的方式，實(shí)際的邏輯讀消耗也是一樣的。為何sql server 不會(huì)優(yōu)化成下面的方式。是因?yàn)?and 操作符優(yōu)化的另外一個(gè)原則。

1/26 的數(shù)據(jù)和 1/6 的數(shù)據(jù)找交集的速度要比 1/52 的數(shù)據(jù)和 1/3 的數(shù)據(jù)找交集速度要慢。

select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname from dbo.member as mwhere m.firstname like 'k%'--1/26 數(shù)據(jù))a,(select m.member_no, m.region_no from dbo.member as mwhere m.region_no > 6 and m.member_no < 5000--1/3 * 1/ 2 數(shù)據(jù))bwhere a.member_no = b.member_no

當(dāng)然，我們要學(xué)習(xí)sql 如何優(yōu)化的話，就會(huì)用到查詢語(yǔ)句中的一個(gè)功能，指定查詢使用哪個(gè)索引來(lái)進(jìn)行。

比如下面的查詢語(yǔ)句

select m.member_no, m.firstname, m.region_nofrom dbo.member as m with(index(0))where m.firstname like 'k%' and m.region_no > 6 and m.member_no < 5000go

select m.member_no, m.firstname, m.region_nofrom dbo.member as m with(index(1))where m.firstname like 'k%' and m.region_no > 6 and m.member_no < 5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with(index(membercovering3))where m.firstname like 'k%' and m.region_no > 6 and m.member_no < 5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with(index(memberfirstname, member_region_link))where m.firstname like 'k%' and m.region_no > 6 and m.member_no < 5000go

這里 index 計(jì)算符可以是 0，1，指定的一個(gè)或者多個(gè)索引名字。對(duì)于 0，1 的意義如下：

如果存在聚集索引，則 index(0)強(qiáng)制執(zhí)行聚集索引掃描，index(1)強(qiáng)制執(zhí)行聚集索引掃描或查找（使用性能最高的一種）。

如果不存在聚集索引，則 index(0)強(qiáng)制執(zhí)行表掃描，index(1)被解釋為錯(cuò)誤。

總結(jié)知識(shí)點(diǎn)：

簡(jiǎn)單來(lái)說(shuō)，我們可以這么理解：sql server 對(duì)于每一條查詢語(yǔ)句。會(huì)根據(jù)實(shí)際索引情況（sysindexes 系統(tǒng)表中存儲(chǔ)這些信息），分析每種組合可能的成本。然后選擇它認(rèn)為成本最小的一種。作為它實(shí)際執(zhí)行的計(jì)劃。

成本代價(jià)計(jì)算的一個(gè)主要組成部分是邏輯i/o的數(shù)量，特別是對(duì)于單表的查詢。

and 操作要滿足所有條件，這樣，經(jīng)常會(huì)要求對(duì)幾個(gè)數(shù)據(jù)集作交集。數(shù)據(jù)集越小，數(shù)據(jù)集的交集計(jì)算越節(jié)省成本。的項(xiàng)目中，竟然出現(xiàn)了濫用聚集索引的問(wèn)題。看來(lái)沒(méi)有培訓(xùn)最最基礎(chǔ)的索引的意義，代價(jià)，使用場(chǎng)景，是一個(gè)非常大的失誤。這篇博客就是從這個(gè)角度來(lái)羅列索引的基礎(chǔ)知識(shí)。

使用索引的意義

索引在數(shù)據(jù)庫(kù)中的作用類(lèi)似于目錄在書(shū)籍中的作用，用來(lái)提高查找信息的速度。

使用索引查找數(shù)據(jù)，無(wú)需對(duì)整表進(jìn)行掃描，可以快速找到所需數(shù)據(jù)。

使用索引的代價(jià)

索引需要占用數(shù)據(jù)表以外的物理存儲(chǔ)空間。

創(chuàng)建索引和維護(hù)索引要花費(fèi)一定的時(shí)間。

當(dāng)對(duì)表進(jìn)行更新操作時(shí)，索引需要被重建，這樣降低了數(shù)據(jù)的維護(hù)速度。

創(chuàng)建索引的列

主鍵

外鍵或在表聯(lián)接操作中經(jīng)常用到的列

在經(jīng)常查詢的字段上最好建立索引

不創(chuàng)建索引的列

很少在查詢中被引用

包含較少的惟一值

定義為 text、ntext 或者 image 數(shù)據(jù)類(lèi)型的列

heaps是staging data的很好選擇，當(dāng)它沒(méi)有任何index時(shí)

excellent for high performance data loading(parallel bulk load and parallel index creation after load)

excellent as a partition to a partitioned view or a partitioned table

聚集索引提高性能的方法，在前面幾篇博客中分別提到過(guò)，下面只是一個(gè)簡(jiǎn)單的大綱，細(xì)節(jié)請(qǐng)參看前面幾篇博客。

何時(shí)創(chuàng)建聚集索引？

clustered index會(huì)提高大多數(shù)table的性能，尤其是當(dāng)它滿足以下條件時(shí)：

獨(dú)特, 狹窄, 靜止： 最重要的條件

持續(xù)增長(zhǎng)的，最好是只向上增加。例如：

identity

date, identity

guid(only when using newsequentialid()function)

聚集索引唯一性（獨(dú)特型的問(wèn)題）

由于聚集索引的b+樹(shù)結(jié)構(gòu)的葉子節(jié)點(diǎn)必須指向具體數(shù)據(jù)。如果你要建立聚集索引的列不唯一，并且你指定的創(chuàng)建的聚集索引是非唯一的聚集索引，則會(huì)有以下情況：

如果未使用 unique 屬性創(chuàng)建聚集索引，數(shù)據(jù)庫(kù)引擎 將向表自動(dòng)添加一個(gè)四字節(jié) uniqueifier 列。必要時(shí)，數(shù)據(jù)庫(kù)引擎 將向行自動(dòng)添加一個(gè) uniqueifier 值，使每個(gè)鍵唯一。此列和列值供內(nèi)部使用，用戶不能查看或訪問(wèn)。

第二篇：數(shù)據(jù)庫(kù)實(shí)驗(yàn)心得

沒(méi)接觸數(shù)據(jù)庫(kù)的時(shí)候總是覺(jué)得它比較深?yuàn)W 或是不可接近的電腦知識(shí)，盡管自己對(duì)電腦 非常感興趣，其實(shí)還是有些心理上的陌生 感。學(xué)習(xí)電腦就和我們平時(shí)的其它科目學(xué)習(xí)一樣感覺(jué)它有永無(wú)止境的知識(shí)，在這從初接 觸電腦時(shí)連個(gè)電腦的鍵盤(pán)都不敢動(dòng)到現(xiàn)在 連硬盤(pán)都也修理，其中的過(guò)程是多么長(zhǎng)啊，數(shù)據(jù)庫(kù)是我在高中時(shí)候聽(tīng)過(guò)，到了大學(xué)漸漸 了解了些，但就其原理性的內(nèi)容還不知道，也就是根本就不清楚什么是數(shù)據(jù)庫(kù)，只是知 道一個(gè)所謂的中國(guó)字典里的名詞。經(jīng)過(guò)此次 的課程設(shè)計(jì)，我初步明白了數(shù)據(jù)庫(kù)的基本原 理。也已經(jīng)掌握了數(shù)據(jù)庫(kù)的基本知識(shí)。我想 對(duì)我以后的更深度學(xué)習(xí)打下了基礎(chǔ)。這次課 程設(shè)計(jì)讓我知道了讓 vb 連接 sql 的方法。其實(shí)就是前臺(tái)和后臺(tái)的連接。有了這個(gè)思 想，我相信對(duì)以后是大有裨益的。

我按照系統(tǒng)工程軟件設(shè)計(jì)的要求，從需求分析，概念設(shè)計(jì)，總體設(shè)計(jì)，詳細(xì) 設(shè)計(jì)，系統(tǒng)測(cè)試等各個(gè)步驟，分步完成系統(tǒng)的各項(xiàng)任務(wù)，實(shí)現(xiàn)了系統(tǒng)中的學(xué)生信 息查詢，學(xué)生信息更新，學(xué)生信息添加等模塊的功能。在這短短的五天里我收獲如下：

1、鞏固和加深了對(duì) C#的理解，提高綜合運(yùn)用本課程所學(xué)知識(shí)的能力。

2、培養(yǎng)了我選用參考書(shū)，查閱手冊(cè)及文獻(xiàn)資料的能力。培養(yǎng)獨(dú)立思考，深 入研究，分析問(wèn)題、解決問(wèn)題的能力。

3、通過(guò)實(shí)際編譯系統(tǒng)的分析設(shè)計(jì)、編程調(diào)試，掌握應(yīng)用軟件的分析方法和 工程設(shè)計(jì)方法。根據(jù)我在課程設(shè)計(jì)中遇到的問(wèn)題，我將在以后的學(xué)習(xí)當(dāng)中注意以下幾點(diǎn)：

1、認(rèn)真上好專(zhuān)業(yè)實(shí)驗(yàn)課，多在實(shí)踐中鍛煉自己。

2、寫(xiě)程序的過(guò)程中要考慮周到，嚴(yán)密。

3、在做設(shè)計(jì)的時(shí)候要有信心，有耐心，切勿浮躁。

4、認(rèn)真的學(xué)習(xí)課本知識(shí)，掌握課本中的知識(shí)點(diǎn)，并在此基礎(chǔ)上學(xué)會(huì)靈活運(yùn) 用。

5、在課余時(shí)間里多寫(xiě)程序，熟練掌握在調(diào)試程序的過(guò)程中所遇到的常見(jiàn)錯(cuò) 誤，以便能節(jié)省調(diào)試程序的時(shí)間

第三篇：數(shù)據(jù)庫(kù)實(shí)驗(yàn)

實(shí)驗(yàn)1.1 使用SQL Server工具(Microsoft SQL Server Management Studio Express)管理數(shù)據(jù)庫(kù) 實(shí)驗(yàn)內(nèi)容：

(1)使用SSMS(SQL Server Management Studio)加入實(shí)驗(yàn)數(shù)據(jù)庫(kù)。(2)使用SSMS可視化建立、修改和刪除數(shù)據(jù)庫(kù)、表。(3)使用SSMS對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份和恢復(fù)。

(4)使用SSMS對(duì)表進(jìn)行查詢、插入、修改、刪除。實(shí)驗(yàn)步驟：

(1)加入School數(shù)據(jù)庫(kù)。(2)建立Test數(shù)據(jù)庫(kù)。

(3)在數(shù)據(jù)庫(kù)中建立人員表PERSON(P#,Pname,Page)。更改表設(shè)置P#為主鍵，增加屬性Ptype(類(lèi)型是CHAR,長(zhǎng)度是10)。

(4)用SSMS的查詢功能(新建查詢)對(duì)PERSON表進(jìn)行查詢、插入、修改、刪除等操作：首先插入兩條記錄；修改第二條記錄；刪除第二條記錄。(5)備份Test數(shù)據(jù)庫(kù)。(6)刪除表PERSON。(7)恢復(fù)Test數(shù)據(jù)庫(kù)。(8)刪除Test數(shù)據(jù)庫(kù)。

第四篇：數(shù)據(jù)庫(kù)實(shí)驗(yàn)

3.實(shí)驗(yàn)步驟

在學(xué)生選課數(shù)據(jù)庫(kù)中實(shí)現(xiàn)其查詢操作，寫(xiě)出T-SQL語(yǔ)句（注：學(xué)生選課數(shù)據(jù)庫(kù)為前三次實(shí)驗(yàn)課所建立的數(shù)

據(jù)庫(kù)）

一、簡(jiǎn)單查詢實(shí)驗(yàn)

（1）查詢選修了課程的學(xué)生學(xué)號(hào)。

（2）查詢選修課程號(hào)為0101的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)降序排列，如果成績(jī)相同則按

學(xué)號(hào)升序排列。

（3）查詢選修課程號(hào)為0101的成績(jī)?cè)?0~90分之間的學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)0.8輸出。

二、連接查詢實(shí)驗(yàn)

（1）查詢每個(gè)學(xué)生的情況以及他（她）所選修的課程。

（2）查詢選修離散散學(xué)課程且成績(jī)?yōu)?0分以上的學(xué)生學(xué)號(hào)、姓名及成績(jī)。

（3）查詢每一門(mén)課的間接先行課（即先行課的先行課）

三、嵌套查詢操作

（1）查詢0101課程的成績(jī)高于張林的學(xué)生學(xué)號(hào)和成績(jī)。

（2）查詢其他系中年齡小于計(jì)算機(jī)系年齡最大者的學(xué)生。

（3）查詢同王洪敏“數(shù)據(jù)庫(kù)原理”課程分?jǐn)?shù)相同的學(xué)生的學(xué)號(hào)。

（4）查詢選修了全部課程的學(xué)生的姓名。

（5）查詢與學(xué)號(hào)為09001103的學(xué)生所選修的全部課程相同的學(xué)生學(xué)號(hào)和姓名。

（6）查詢至少選修了學(xué)號(hào)為09001103的學(xué)生所選修的全部課程的學(xué)生學(xué)號(hào)和姓名。

四、組合查詢和分組查詢

（1）查找選修“計(jì)算機(jī)基礎(chǔ)”課程的學(xué)生成績(jī)比此課程的平均成績(jī)大的學(xué)生學(xué)號(hào)、成績(jī)。

（2）查詢年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡。

（3）列出各系學(xué)生的總?cè)藬?shù)，并按人數(shù)進(jìn)行降序排列。

（5）查詢選修計(jì)算機(jī)基礎(chǔ)和離散數(shù)學(xué)的學(xué)生學(xué)號(hào)和平均成績(jī)。

4、要求

請(qǐng)按題號(hào)依次作答，完成在word文檔中，寫(xiě)明班級(jí)學(xué)號(hào)姓名，于周日之于周日之前發(fā)至xcf7@163.com，獨(dú)立完成，嚴(yán)禁抄

第五篇：數(shù)據(jù)庫(kù)實(shí)驗(yàn)答案

實(shí)驗(yàn)四

五、實(shí)驗(yàn)方法和步驟：

1.向數(shù)據(jù)庫(kù)的USERS表空間添加一個(gè)大小為10M的數(shù)據(jù)文件userdata02.dbf。

Alter tablespace users add datafile ‘c:oracleoradataorcluserdata02.dbf’ size 10M;2.向數(shù)據(jù)庫(kù)的TEMP表空間添加一個(gè)大小為5N的臨時(shí)數(shù)據(jù)文件temp02.dbf。

Alter tablespace temp add tempfile’c:oracleoradataorcltemp02.dbf’ size 5M;3.修改userdata02.dbf為自動(dòng)增長(zhǎng)方式，每次增長(zhǎng)512k，沒(méi)有限制大小。

Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ autoextend on next 512k maxsize unlimited;

4.取消userdata02.dbf的自動(dòng)增長(zhǎng)方式。

Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ autoextend off;5.將userdata02.dbf大小設(shè)置為8MB。

Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ resize 8M;6.將userdata02.dbf設(shè)置為脫機(jī)。

Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ offline;7.將userdata02設(shè)置為聯(lián)機(jī)。

Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ online;8.改變userdata02的名稱(chēng)。

(1)alter tablespace users offline;

(2)將userdata02.dbf重命名為userdata002.dbf;(3)alter tablesace users rename datafile ‘c:oracleoradataorcluserdata02.dbf’,‘c:oracleoradataorcluserdata002.dbf’;(4)alter tablespace users online;

9.查詢數(shù)據(jù)文件信息（包括數(shù)據(jù)文件動(dòng)態(tài)信息；數(shù)據(jù)文件詳細(xì)信息；臨時(shí)數(shù)據(jù)文件信息）。(1)select name,file#,status,checkpoint_change# from V$DATAFILE;

(2)select tablespace_name,bytes,autoextensible,file_name from DBA_DATA_FILES;(3)select tablespace_name,file_name,autoextensible from DBA_TEMP_FILES;

實(shí)驗(yàn)五 表空間、段、區(qū)、塊的管理

五、實(shí)驗(yàn)方法和步驟：

1.編輯一個(gè)腳本文件TEST，腳本文件內(nèi)容為創(chuàng)建一個(gè)永久性的Student表空間，區(qū)自動(dòng)擴(kuò)展，段采用手動(dòng)管理方式，并執(zhí)行腳本文件。

Create tablespace student datafile ‘c:oracleoradataora404orcltbs1_1.dbf’ 2.為數(shù)據(jù)庫(kù)的student表空間添加一個(gè)大小為10MB的新的數(shù)據(jù)文件。

Alter tablespace student add datafile ‘c:oracleoradataora404orcltbs1_2.dbf’ size 10M 3.創(chuàng)建一個(gè)臨時(shí)表空間mytemp，并為臨時(shí)表空間添加一個(gè)大小為10MB的臨時(shí)數(shù)據(jù)文件。

Create temporary tablespace mytemp tempfile ‘c:oracleoradataora404orcltemp1_1.dbf’ size 20M extent management local uniform size 16M;

Alter tablespace mytemp add tempfile c:oracleoradataora404orltemp1_2.dbf’ size 10M 4.將student表空間新添加的數(shù)據(jù)文件的大小改為20MB。

Alter database datafile c:oracleoradataora404orcltbs1_2.dbf’ resize 20M 5.將studnet表空間設(shè)置為offline；再設(shè)置為online。Alter tablespace student offline;Alter tablespace student online;6.將student表空間設(shè)置為只讀狀態(tài)。Alter tablespace student read only;7.為student表空間創(chuàng)建一個(gè)回退段。

Create rollback segment student_roll tablespace student;8.刪除student表空間及其所有內(nèi)容。

Drop tablespace student including contents;9.查詢表空間基本信息。

Select tablespace_name, extent_management,a llocation_type, contents from DBA_TABLESPACE;10.查詢表空間數(shù)據(jù)文件信息。

Select file_name, blocks, tablespace_name from DBA_DATA_FILES;11.統(tǒng)計(jì)表空間空閑空間信息。

SELECT TABLESAPCE_NAME “TABLESPACE”, FILE_ID, COUNT(*)“PIECES”, MAX(BLOCKS)“MAXIMUM”, MIN(BLOCKS)“MINIMUM”, AVG(BLOCKS)“AVERAGE”, SUM(BLOCKS)“TOTAL” FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;

12.查詢表空間空閑空間大小。

SELECT TABLESPACE_NAME, SUM(BYTES)FREE_SPACES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

13.查詢scott用戶emp表的某個(gè)員工元組的物理地址（ROWID）。SELECT ROWID, EMPNO FROM SCOTT.EMP WHERE EMPNO=7369;

實(shí)驗(yàn)六 模式對(duì)象管理—基礎(chǔ)表

五、實(shí)驗(yàn)方法和步驟：

1、創(chuàng)建一個(gè)表

目標(biāo)：創(chuàng)建一個(gè)名為STUDENT的表，該表有ID（學(xué)號(hào)）、NAME（姓名）、SEX（性別）、COURSE（課程）、SCORE（成績(jī)）字段，并保存在USER01表空間。

（1）在SQL*PLUS中執(zhí)行使用CREATE TABLE語(yǔ)句在某一表空間中創(chuàng)建STUDENTS表，并通過(guò)INSERT、SELECT、UPDATE、DELETE語(yǔ)句對(duì)表進(jìn)行操作，各舉一例。（2）通過(guò)OEM控制臺(tái)新的表，并查看新表情況。Create table student(Id number primary key, Name varchar2(20), Sex char(1)，Course varchar2(20), Score number(5,2))tablespace users

Insert into student(id)values(1);Select id from student;

Update student set id=2 where id=1;Delete from student where id=2;

2、創(chuàng)建一個(gè)臨時(shí)表

目標(biāo)：創(chuàng)建一個(gè)名為T(mén)EMP01的事務(wù)級(jí)別臨時(shí)表，該表有ID（學(xué)號(hào)）、NAME（姓名）字段，并保存在temp表空間中。

Create global temporary table temp01(id number（2）primary key, name varchar(20))on commit delete rows;

3、修改表

目標(biāo)：修改STUDENTS表的存儲(chǔ)參數(shù)；為表分配新的存儲(chǔ)空間；添加一個(gè)新的列；設(shè)置列為無(wú)用狀態(tài)（UNUSED）。

（1）在SQL*PLUS中執(zhí)行ALTER TABLE語(yǔ)句修改STUDENTS表的存儲(chǔ)參數(shù)(修改pctfree和pctused分別為20和40）。

Alter table student pctfree 20;Alter table student ptused 40;

（2）在SQL*PLUS中執(zhí)行ALTER TABLE語(yǔ)句在STUDENTS表分配大小為400KB的新區(qū)。Alter table student allocate extent(size 400k);

（3）在SQL*PLUS中執(zhí)行ALTER TABLE語(yǔ)句在STUDENTS表新增加一個(gè)AGE列，列的類(lèi)型為NUMBER(2)。Alter table student add(age number(2));

（4）在SQL*PLUS中執(zhí)行ALTER TABLE語(yǔ)句在將STUDENTS表中的AGE設(shè)置為無(wú)用狀態(tài)。Alter table student set unused(age);

4、截?cái)啾?/p>

目標(biāo)：刪除表STUDENTS中的所有記錄。

（1）在SQL*PLUS中執(zhí)行TRUNCATE TABLE語(yǔ)句刪除STUDENTS表中的所有記錄。Truncate table student;

（2）在SQL*PLUS中執(zhí)行SELECT語(yǔ)句查看STUDENTS表中的內(nèi)容。Select * from student;

5、查詢ORACLE數(shù)據(jù)庫(kù)的模式對(duì)象信息

目標(biāo)：使用管理工具（SQL*PLUS或OEM）獲得有關(guān)數(shù)據(jù)庫(kù)模式對(duì)象的有關(guān)信息。

（1）用SCOTT帳戶登錄SQL*PLUS，顯示用戶，并通過(guò)用戶視圖USER_TABLES、USER_VIEWS、USER_INDEXS、USER_OBJECTS、USER_TRIGGERS、USER_SEQUENCES等模式對(duì)象信息。Show user

Select table_name from user_tables;Select view_name from user_views;Select index_name from user_indexes;Select object_name from user_objects;Select trigger_name from user_triggers;Select sequence_name from user_sequence;

（2）使用SCOTT帳戶登錄數(shù)據(jù)庫(kù)，在OME控制臺(tái)中查看SCOTT用戶的各種模式對(duì)象信息。

6、給表student表的nane列添加一個(gè)唯一值約束，并刪除該約束 Alter table student add constraint p_uk unique(name);Alter table student drop drop constraint p_uk;

實(shí)驗(yàn)七 模式對(duì)象管理—視圖

五、實(shí)驗(yàn)方法和步驟：

1、創(chuàng)建基礎(chǔ)表BOOKS，包含BOOK_ID,數(shù)字型，BOOK_NAME,可變長(zhǎng)50位，AUTHOR,可變長(zhǎng)20位，PRESS可變長(zhǎng)50位,Price數(shù)字型，PUBLISH_DATE，日期型；并插入10條數(shù)據(jù)。Create table books（Book_id number primary key, Book_name varchar2(50)not null, Author varchar2(20), Press varchar2(50), Price number, Publishdate date);

2、關(guān)系視圖

（1）建立關(guān)系視圖V_BOOKS，包含書(shū)名、作者、出版社。Create view v_books as

Select book_id 編號(hào)，author 作者，press 出版社

from books;（2）查看視圖定義。

Select text from user_views where view_name=’VW_BOOKS’；（3）查看視圖內(nèi)容。

Select * from vw_books;

（4）修改視圖，增加一個(gè)列出版日期。

Create or replace view VW_BOOKS As

select book_id 編號(hào)，author 作者，press 出版社，publishdate 出版日期

from books;（5）向基礎(chǔ)表增加一個(gè)屬性列“價(jià)格”，數(shù)字型。查看視圖的可用性狀態(tài)。

alter table boks add price int;

Select object_name,status from user_objects where object_name=’VW_BOOKS’ and object_type=’VIEW’;

（6）重新編譯V_BOOKS視圖。再查看視圖的可用性狀態(tài)

alter view vw_books compile

Select object_name,status from user_objects where object_name=’VW_BOOKS’ and object_type=’VIEW’;（7）刪除該視圖。

drop view vw_books;

3、內(nèi)嵌視圖

（1）查詢價(jià)格由高到低處于前三位的書(shū)本信息，注意采用內(nèi)嵌視圖的方式實(shí)現(xiàn)。

select * from

(select book_id,book_name ,price from books

order by price desc)where rownum<=3;

（2）利用內(nèi)嵌視圖將所有書(shū)籍的價(jià)格增加10元。

update(select price from books)set price=price+10

實(shí)驗(yàn)十 PL/SQL程序結(jié)構(gòu)

五、實(shí)驗(yàn)方法和步驟：

1.在SQL*Plus中使用PL/SQL塊處理EMP表中職工號(hào)7788的職工，如果工資小于5000那么把工資更改為5000。

（1）首先采用SCOTT用戶登錄；（2）查詢7788員工的工資是多少；

（3）定義聲明塊和執(zhí)行塊，修改該員工工資；（4）再查詢?cè)搯T工工資是多少； SQL>DECLARE

X NUMBER(7,2);BEGIN

SELECT sal INTO x FROM emp WHERE empno = 7788;

IF x < 5000

THEN UPDATE emp SET sal = 5000 WHERE empno = 7788;END IF;END;

2.利用三種循環(huán)控制結(jié)構(gòu)向表中插入150條記錄。（1）創(chuàng)建一張表，用來(lái)記錄循環(huán)指針的值；

Create table temp_table(num_col number, info_col char(10));（2）利用LOOP循環(huán)向表中插入前50條記錄； DECLARE

v_counter BINARY_INTEGER :=1;BEGIN

LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

v_counter := v_counter+1;

EXIT WHEN v_counter>50;

END LOOP;END;

（3）利用WHILE循環(huán)向表中插入51-100條記錄； DECLARE

v_counter BINARY_INTEGER :=1;BEGIN

WHILE v_counter<100 LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

v_counter := v_counter+1;

END LOOP;END;

（4）利用FOR循環(huán)向表中插入101-150條記錄； DECLARE

v_counter BINARY_INTEGER :=1;BEGIN FOR v_counter IN 101..150 LOOP

INSERT INTO temp_table values(v_counter,’loop index’);

END LOOP;END;

（5）查看表中的信息。

SLELECT * FROM TEMP_TABL

實(shí)驗(yàn)十一 游標(biāo)

五、實(shí)驗(yàn)方法和步驟：

1．聲明顯式游標(biāo)

聲明一個(gè)游標(biāo)用來(lái)讀取基表EMP中部門(mén)號(hào)是20且工作為分析員的職工： DECLARE Cursor c1 IS

SELECT ename, sal, hiredate FROM emp WHERE deptno = 20 AND job = 'ANALYST';v_ename VARCHAR2(10);v_sal NUMBER(7,2);v_hiredate date;begin OPEN c1;

FETCH c1 INTO v_ename, v_sal, v_hiredate;CLOSE c1;end;

2．游標(biāo)的應(yīng)用

（1）利用游標(biāo)修改數(shù)據(jù)，如果EMP中部門(mén)號(hào)是20，工作為分析員的職工工資小于5000，更改為5000： DECLARE CURSOR c1 IS

SELECT empno, sal, hiredate, rowid

FROM emp WHERE deptno = 20 AND job = 'ANALYST' FOR UPDATE OF sal;emp_record c1%ROWTYPE;BEGIN OPEN c1;LOOP

FETCH c1 INTO emp_record;EXIT WHEN c1%NOTFOUND;IF emp_record.sal <5000 THEN

UPDATE emp set sal = 5000 where empno = emp_record.empno;END IF;END LOOP;CLOSE c1;END;

（2）利用游標(biāo)，如果部門(mén)是SALES，地址不是DALLAS的，地址更改為DALLAS；如果部門(mén)不是SALES，地址不是NEW YORK的，地址更改為NEW YORK： DECLARE CURSOR c1 IS SELECT dname, loc FROM dept FOR UPDATE OF loc;Dept_rec c1%ROWTYPE;BEGIN OPEN c1;LOOP

FETCH c1 INTO dept_rec;EXIT WHEN c1%NOTFOUND;

IF dept_rec.dname = 'SALES' AND dept_rec.loc!= 'DALLAS'

THEN UPDATE dept SET LOC = 'DALLAS' WHERE CURRENT OF C1;ELSE IF DEPT_REC.DNAME!= 'SALES' AND DEPT_REC.LOC!= 'NEW YORK' THEN UPDATE dept SET LOC = 'NEW YORK' WHERE CURRENT OF C1;END IF;END LOOP;CLOSE c1;END;

實(shí)驗(yàn)十二 存儲(chǔ)過(guò)程和函數(shù)

1.編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，用它顯示scott.dept、scott.emp表中各部門(mén)的名稱(chēng)、員工總

數(shù)以及員工的工資總和和平均工資。

set serverout on;

create or replace procedure outputEMP as begin declare

cursor cEMP is

select dname,count(*),avg(sal),sum(sal)

from scott.emp,scott.dept

where scott.emp.deptno=scott.dept.deptno

group by dname;

d_name varchar2(14);

count_EMP number;

sum_sal number(7,2);

avg_sal number(7,2);

begin

open cEMP;

fetch cEMP into d_name,count_EMP,avg_sal,sum_sal;

while cEMP %found loop

dbms_output.put_line(d_name||','||count_EMP||','||avg_sal||','||sum_sal);

fetch cEMP into d_name,count_EMP,avg_sal,sum_sal;

end loop;

close cEMP;

end;

end outputEMP;2.編寫(xiě)一個(gè)函數(shù)，用它計(jì)算0到指定數(shù)字（作為函數(shù)輸入?yún)?shù)）之間的整數(shù)和。set sertverout on;

create or replace function get_sum(maxNUM number)return number as begin

declare sumNUM number;

i number;

begin

sumNUM:=0;

i:=maxNum;

while i>0 loop

sumNUM:=sumNUM+i;

i:=i-1;

end loop;

return sumNUM;

end;end get_sum;

實(shí)驗(yàn)十三 觸發(fā)器

1.編寫(xiě)一個(gè)觸發(fā)器，用它把數(shù)據(jù)庫(kù)系統(tǒng)的關(guān)閉事件記錄到一個(gè)表(表結(jié)構(gòu)自己設(shè)計(jì))內(nèi)。Create table shutdown_log(shut_user varchar2(20)，shut_time date，shut_action varchar2(20));

create or replace trigger shutdown_record before shutdown on database begin

insert into shutdown_log values(user,sysdate,'關(guān)閉數(shù)據(jù)庫(kù)');end;

2.編寫(xiě)一個(gè)行觸發(fā)器，把emp表增、刪、改操作前后員工的姓名和工資前后變化情況顯示處理。create or replace trigger trg_emp_dml_row

Before insert or update or delete on scott.emp

For each row

Begin

If inserting then

Dbms_output.put_line(:new.empno||’ ’||:new.ename);

Elsif updating then

Dbms_output.put_line(:old.sal||’ ’||:new.sal);

Else

Dbms_output.put_line(:old.empno||’ ’||:old.ename);

End if;

End trg_emp_dml_row;