第一篇:數(shù)據(jù)庫實(shí)驗(yàn)心得
我在sql server 索引基礎(chǔ)知識(shí)系列中,第一篇就講了記錄數(shù)據(jù)的基本格式。那里主要講解的是,數(shù)據(jù)庫的最小讀存單元:數(shù)據(jù)頁。一個(gè)數(shù)據(jù)頁是8k大小。
對(duì)于數(shù)據(jù)庫來說,它不會(huì)每次有一個(gè)數(shù)據(jù)頁變化后,就存到硬盤。而是變化達(dá)到一定數(shù)量級(jí)后才會(huì)作這個(gè)操作。這時(shí)候,數(shù)據(jù)庫并不是以數(shù)據(jù)頁來作為操作單元,而是以64k的數(shù)據(jù)(8個(gè)數(shù)據(jù)頁,一個(gè)區(qū))作為操作單元。
區(qū)是管理空間的基本單位。一個(gè)區(qū)是八個(gè)物理上連續(xù)的頁(即 64 kb)。這意味著 sql server 數(shù)據(jù)庫中每 mb 有 16 個(gè)區(qū)。
為了使空間分配更有效,sql server 不會(huì)將所有區(qū)分配給包含少量數(shù)據(jù)的表。sql server 有兩種類型的區(qū):
統(tǒng)一區(qū),由單個(gè)對(duì)象所有。區(qū)中的所有 8 頁只能由所屬對(duì)象使用。
混合區(qū),最多可由八個(gè)對(duì)象共享。區(qū)中八頁的每頁可由不同的對(duì)象所有。
通常從混合區(qū)向新表或索引分配頁。當(dāng)表或索引增長到 8 頁時(shí),將變成使用統(tǒng)一區(qū)進(jìn)行后續(xù)分配。如果對(duì)現(xiàn)有表創(chuàng)建索引,并且該表包含的行足以在索引中生成 8 頁,則對(duì)該索引的所有分配都使用統(tǒng)一區(qū)進(jìn)行。
為何會(huì)這樣呢?
其實(shí)很簡單:
讀或?qū)?8kb 的時(shí)間與讀或?qū)?64 kb的時(shí)間幾乎相同。
在 8 kb 到 64 kb 范圍之內(nèi),單個(gè)磁盤 i/o 傳輸操作所花的時(shí)間主要是磁盤取數(shù)臂和讀/寫磁頭運(yùn)動(dòng)的時(shí)間。
因此,從數(shù)學(xué)上來講,當(dāng)需要傳輸 64 kb 以上的 sql 數(shù)據(jù)時(shí),盡可能地執(zhí)行 64 kb 磁盤傳輸是有益的,即分成數(shù)個(gè)64k的操作。
因?yàn)?64 kb 傳輸基本上與 8 kb 傳輸一樣快,而每次傳輸?shù)?sql server 數(shù)據(jù)是 8 kb 傳輸?shù)?8 倍。
我們通過一個(gè)實(shí)例來看 有and 操作符時(shí)候的最常見的一種情況。我們有下面一個(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)化成下面方式來執(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ì)用到查詢語句中的一個(gè)功能,指定查詢使用哪個(gè)索引來進(jìn)行。
比如下面的查詢語句
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):
簡單來說,我們可以這么理解:sql server 對(duì)于每一條查詢語句。會(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)了濫用聚集索引的問題。看來沒有培訓(xùn)最最基礎(chǔ)的索引的意義,代價(jià),使用場(chǎng)景,是一個(gè)非常大的失誤。這篇博客就是從這個(gè)角度來羅列索引的基礎(chǔ)知識(shí)。
使用索引的意義
索引在數(shù)據(jù)庫中的作用類似于目錄在書籍中的作用,用來提高查找信息的速度。
使用索引查找數(shù)據(jù),無需對(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ù)類型的列
heaps是staging data的很好選擇,當(dāng)它沒有任何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
聚集索引提高性能的方法,在前面幾篇博客中分別提到過,下面只是一個(gè)簡單的大綱,細(xì)節(jié)請(qǐng)參看前面幾篇博客。
何時(shí)創(chuàng)建聚集索引?
clustered index會(huì)提高大多數(shù)table的性能,尤其是當(dāng)它滿足以下條件時(shí):
獨(dú)特, 狹窄, 靜止: 最重要的條件
持續(xù)增長的,最好是只向上增加。例如:
identity
date, identity
guid(only when using newsequentialid()function)
聚集索引唯一性(獨(dú)特型的問題)
由于聚集索引的b+樹結(jié)構(gòu)的葉子節(jié)點(diǎn)必須指向具體數(shù)據(jù)。如果你要建立聚集索引的列不唯一,并且你指定的創(chuàng)建的聚集索引是非唯一的聚集索引,則會(huì)有以下情況:
如果未使用 unique 屬性創(chuàng)建聚集索引,數(shù)據(jù)庫引擎 將向表自動(dòng)添加一個(gè)四字節(jié) uniqueifier 列。必要時(shí),數(shù)據(jù)庫引擎 將向行自動(dòng)添加一個(gè) uniqueifier 值,使每個(gè)鍵唯一。此列和列值供內(nèi)部使用,用戶不能查看或訪問。
第二篇:數(shù)據(jù)庫實(shí)驗(yàn)心得
沒接觸數(shù)據(jù)庫的時(shí)候總是覺得它比較深?yuàn)W 或是不可接近的電腦知識(shí),盡管自己對(duì)電腦 非常感興趣,其實(shí)還是有些心理上的陌生 感。學(xué)習(xí)電腦就和我們平時(shí)的其它科目學(xué)習(xí)一樣感覺它有永無止境的知識(shí),在這從初接 觸電腦時(shí)連個(gè)電腦的鍵盤都不敢動(dòng)到現(xiàn)在 連硬盤都也修理,其中的過程是多么長啊,數(shù)據(jù)庫是我在高中時(shí)候聽過,到了大學(xué)漸漸 了解了些,但就其原理性的內(nèi)容還不知道,也就是根本就不清楚什么是數(shù)據(jù)庫,只是知 道一個(gè)所謂的中國字典里的名詞。經(jīng)過此次 的課程設(shè)計(jì),我初步明白了數(shù)據(jù)庫的基本原 理。也已經(jīng)掌握了數(shù)據(jù)庫的基本知識(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)了我選用參考書,查閱手冊(cè)及文獻(xiàn)資料的能力。培養(yǎng)獨(dú)立思考,深 入研究,分析問題、解決問題的能力。
3、通過實(shí)際編譯系統(tǒng)的分析設(shè)計(jì)、編程調(diào)試,掌握應(yīng)用軟件的分析方法和 工程設(shè)計(jì)方法。根據(jù)我在課程設(shè)計(jì)中遇到的問題,我將在以后的學(xué)習(xí)當(dāng)中注意以下幾點(diǎn):
1、認(rèn)真上好專業(yè)實(shí)驗(yàn)課,多在實(shí)踐中鍛煉自己。
2、寫程序的過程中要考慮周到,嚴(yán)密。
3、在做設(shè)計(jì)的時(shí)候要有信心,有耐心,切勿浮躁。
4、認(rèn)真的學(xué)習(xí)課本知識(shí),掌握課本中的知識(shí)點(diǎn),并在此基礎(chǔ)上學(xué)會(huì)靈活運(yùn) 用。
5、在課余時(shí)間里多寫程序,熟練掌握在調(diào)試程序的過程中所遇到的常見錯(cuò) 誤,以便能節(jié)省調(diào)試程序的時(shí)間
第三篇:數(shù)據(jù)庫實(shí)驗(yàn)
實(shí)驗(yàn)1.1 使用SQL Server工具(Microsoft SQL Server Management Studio Express)管理數(shù)據(jù)庫 實(shí)驗(yàn)內(nèi)容:
(1)使用SSMS(SQL Server Management Studio)加入實(shí)驗(yàn)數(shù)據(jù)庫。(2)使用SSMS可視化建立、修改和刪除數(shù)據(jù)庫、表。(3)使用SSMS對(duì)數(shù)據(jù)庫進(jìn)行備份和恢復(fù)。
(4)使用SSMS對(duì)表進(jìn)行查詢、插入、修改、刪除。實(shí)驗(yàn)步驟:
(1)加入School數(shù)據(jù)庫。(2)建立Test數(shù)據(jù)庫。
(3)在數(shù)據(jù)庫中建立人員表PERSON(P#,Pname,Page)。更改表設(shè)置P#為主鍵,增加屬性Ptype(類型是CHAR,長度是10)。
(4)用SSMS的查詢功能(新建查詢)對(duì)PERSON表進(jìn)行查詢、插入、修改、刪除等操作:首先插入兩條記錄;修改第二條記錄;刪除第二條記錄。(5)備份Test數(shù)據(jù)庫。(6)刪除表PERSON。(7)恢復(fù)Test數(shù)據(jù)庫。(8)刪除Test數(shù)據(jù)庫。
第四篇:數(shù)據(jù)庫實(shí)驗(yàn)
3.實(shí)驗(yàn)步驟
在學(xué)生選課數(shù)據(jù)庫中實(shí)現(xiàn)其查詢操作,寫出T-SQL語句(注:學(xué)生選課數(shù)據(jù)庫為前三次實(shí)驗(yàn)課所建立的數(shù)
據(jù)庫)
一、簡單查詢實(shí)驗(yàn)
(1)查詢選修了課程的學(xué)生學(xué)號(hào)。
(2)查詢選修課程號(hào)為0101的學(xué)生學(xué)號(hào)和成績,并要求對(duì)查詢結(jié)果按成績降序排列,如果成績相同則按
學(xué)號(hào)升序排列。
(3)查詢選修課程號(hào)為0101的成績?cè)?0~90分之間的學(xué)生學(xué)號(hào)和成績,并將成績乘以系數(shù)0.8輸出。
二、連接查詢實(shí)驗(yàn)
(1)查詢每個(gè)學(xué)生的情況以及他(她)所選修的課程。
(2)查詢選修離散散學(xué)課程且成績?yōu)?0分以上的學(xué)生學(xué)號(hào)、姓名及成績。
(3)查詢每一門課的間接先行課(即先行課的先行課)
三、嵌套查詢操作
(1)查詢0101課程的成績高于張林的學(xué)生學(xué)號(hào)和成績。
(2)查詢其他系中年齡小于計(jì)算機(jī)系年齡最大者的學(xué)生。
(3)查詢同王洪敏“數(shù)據(jù)庫原理”課程分?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é)生成績比此課程的平均成績大的學(xué)生學(xué)號(hào)、成績。
(2)查詢年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡。
(3)列出各系學(xué)生的總?cè)藬?shù),并按人數(shù)進(jìn)行降序排列。
(5)查詢選修計(jì)算機(jī)基礎(chǔ)和離散數(shù)學(xué)的學(xué)生學(xué)號(hào)和平均成績。
4、要求
請(qǐng)按題號(hào)依次作答,完成在word文檔中,寫明班級(jí)學(xué)號(hào)姓名,于周日之于周日之前發(fā)至xcf7@163.com,獨(dú)立完成,嚴(yán)禁抄
第五篇:數(shù)據(jù)庫實(shí)驗(yàn)答案
實(shí)驗(yàn)四
五、實(shí)驗(yàn)方法和步驟:
1.向數(shù)據(jù)庫的USERS表空間添加一個(gè)大小為10M的數(shù)據(jù)文件userdata02.dbf。
Alter tablespace users add datafile ‘c:oracleoradataorcluserdata02.dbf’ size 10M;2.向數(shù)據(jù)庫的TEMP表空間添加一個(gè)大小為5N的臨時(shí)數(shù)據(jù)文件temp02.dbf。
Alter tablespace temp add tempfile’c:oracleoradataorcltemp02.dbf’ size 5M;3.修改userdata02.dbf為自動(dòng)增長方式,每次增長512k,沒有限制大小。
Alter database datafile ‘c:oracleoradataorcluserdata02.dbf’ autoextend on next 512k maxsize unlimited;
4.取消userdata02.dbf的自動(dò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的名稱。
(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ù)庫的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(成績)字段,并保存在USER01表空間。
(1)在SQL*PLUS中執(zhí)行使用CREATE TABLE語句在某一表空間中創(chuàng)建STUDENTS表,并通過INSERT、SELECT、UPDATE、DELETE語句對(duì)表進(jìn)行操作,各舉一例。(2)通過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è)名為TEMP01的事務(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è)置列為無用狀態(tài)(UNUSED)。
(1)在SQL*PLUS中執(zhí)行ALTER TABLE語句修改STUDENTS表的存儲(chǔ)參數(shù)(修改pctfree和pctused分別為20和40)。
Alter table student pctfree 20;Alter table student ptused 40;
(2)在SQL*PLUS中執(zhí)行ALTER TABLE語句在STUDENTS表分配大小為400KB的新區(qū)。Alter table student allocate extent(size 400k);
(3)在SQL*PLUS中執(zhí)行ALTER TABLE語句在STUDENTS表新增加一個(gè)AGE列,列的類型為NUMBER(2)。Alter table student add(age number(2));
(4)在SQL*PLUS中執(zhí)行ALTER TABLE語句在將STUDENTS表中的AGE設(shè)置為無用狀態(tài)。Alter table student set unused(age);
4、截?cái)啾?/p>
目標(biāo):刪除表STUDENTS中的所有記錄。
(1)在SQL*PLUS中執(zhí)行TRUNCATE TABLE語句刪除STUDENTS表中的所有記錄。Truncate table student;
(2)在SQL*PLUS中執(zhí)行SELECT語句查看STUDENTS表中的內(nèi)容。Select * from student;
5、查詢ORACLE數(shù)據(jù)庫的模式對(duì)象信息
目標(biāo):使用管理工具(SQL*PLUS或OEM)獲得有關(guān)數(shù)據(jù)庫模式對(duì)象的有關(guān)信息。
(1)用SCOTT帳戶登錄SQL*PLUS,顯示用戶,并通過用戶視圖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ù)庫,在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,可變長50位,AUTHOR,可變長20位,PRESS可變長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,包含書名、作者、出版社。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à)格由高到低處于前三位的書本信息,注意采用內(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)嵌視圖將所有書籍的價(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)建一張表,用來記錄循環(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)用來讀取基表EMP中部門號(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中部門號(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),如果部門是SALES,地址不是DALLAS的,地址更改為DALLAS;如果部門不是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ǔ)過程和函數(shù)
1.編寫一個(gè)存儲(chǔ)過程,用它顯示scott.dept、scott.emp表中各部門的名稱、員工總
數(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.編寫一個(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.編寫一個(gè)觸發(fā)器,用它把數(shù)據(jù)庫系統(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ù)庫');end;
2.編寫一個(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;