1、第第3章章 數據庫的創建和操作數據庫的創建和操作3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）3. 2 以界面方式操作數據庫（采用以界面方式操作數據庫（采用SQL Developer）3. 3 命令方式操作數據庫（采用命令方式操作數據庫（采用SQL*Plus）3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（1）啟動DBCA，出現“歡迎使用”界面，如圖3.1所示，單擊“下一步”按鈕進入創建數據庫的向導。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（2）在“操作”窗口中，用戶可以選擇要執行的操作，這里選中“創建數據庫”

2、選項，如圖3.2所示，單擊“下一步”按鈕。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（3）在“數據庫模板”窗口中，選擇相應選項后單擊“顯示詳細資料”按鈕可查看該數據庫模板的各種信息。這里選擇“一般用途或事務處理”選項，如圖3.3所示，單擊“下一步”按鈕。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（4）在“數據庫標識”窗口中輸入“全局數據庫名”和“SID”，如圖3.4所示，單擊“下一步”按鈕。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（5）在“管理選項”窗口中可以選擇配置Enterprise Manager

3、企業管理器或者配置Database Control管理本地數據庫，這里保持默認設置，如圖3.5所示，單擊“下一步”按鈕。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（6）在“數據庫身份證明”窗口中，將所有賬戶設置為同一管理口令（Mm123456），如圖3.6所示，單擊“下一步”按鈕。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（7）在“數據庫文件所在位置”窗口中，選擇“所有數據庫文件使用公共位置”，單擊“瀏覽”按鈕選擇數據庫文件的存放路徑，如圖3.7所示，單擊“下一步”按鈕。（8）在“恢復配置”窗口中采取默認的配置，單擊“下一步”按鈕繼續

4、。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（9）在“數據庫內容”窗口中勾選“示例方案”，如圖3.8所示，這樣就可以在學習的過程中參考標準設置，也可了解基礎的數據庫創建方法和SQL語言。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（10）在“初始化參數”和“數據庫存儲”窗口中也保持默認配置，兩次單擊“下一步”按鈕。（11）在“創建選項”窗口中選擇“創建數據庫”選項，如圖3.9所示，單擊“完成”按鈕，之后會彈出確認創建的對話框，單擊“確定”按鈕開始創建數據庫。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（12）

5、創建數據庫期間顯示的創建進度窗口如圖3.10所示，過程較為漫長，讀者要耐心地等待3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）（13）創建數據庫完畢后，系統會彈出窗口顯示相關的提示信息，如圖3.11所示，需要在這一步解鎖SCOTT、SYSTEM、SYS賬戶并設置其口令，單擊窗口中的“口令管理”按鈕，彈出“口令管理”對話框。3. 1 以界面方式創建數據庫（采用以界面方式創建數據庫（采用DBCA）至此，所有步驟都已全部完成。現在，系統服務中應該已經有SID為XSCJ的服務選項并已置為“自動”啟動，服務正在運行，如圖3.12所示，訪問XSCJ數據庫前必須保證已啟動了這兩個系

6、統服務：OracleOraDb11g_home1TNSListener和OracleServiceXSCJ。3. 2 以界面方式操作數據庫（采用以界面方式操作數據庫（采用SQL Developer）3.2.1 表操作表操作1表的概念表的概念表是日常工作和生活中經常使用的一種表示數據及其關系的形式，表3.1就是用來表示學生信息的一個學生表。學 號姓 名性 別出 生 時 間專 業總 學 分備 注151101王林男1997-02-10計算機50 151103王燕女1996-10-06計算機50 151108林一帆男1996-08-05計算機52已提前修完一門課151202王林男1996-01-29通

7、信工程40有一門課不及格，待補考151204馬琳琳女1996-02-10通信工程42 3.2.1 表操作表操作關系數據庫使用表（即關系）來表示實體及其聯系。表包含下列概念。（1）表結構表結構：每個表都包含一組固定的列，而列由數據類型（DATATYPE）和長度（LENGTH）兩部分組成，以描述該表所代表的實體的屬性。（2）記錄記錄：每個表包含了若干行數據，它們是表的“值”，其中的一行稱為一個記錄，因此，表是記錄的有限集合。（3）字段字段：每個記錄由若干個數據項構成，將構成記錄的數據項稱為字段。例如，表3.1的XSB，其表結構為（學號，姓名，性別，出生時間，專業，總學分，備注），包含7個字段，由5

8、個記錄組成。（4）關鍵字關鍵字：若表中記錄的某個字段或字段組合能唯一標識記錄，則稱該字段（字段組合）為候選關鍵字（Candidate Key）。若一個表有多個候選關鍵字，則選定其中一個為主關鍵字（Primary Key），也稱為主鍵。3.2.1 表操作表操作2數據類型數據類型在設計表的列時，必須為其指定數據類型，它決定了該列數據的取值、范圍和存儲格式。列的數據類型可以是Oracle提供的系統數據類型，其中主要的類型列于表3.2中。3.2.1 表操作表操作3表結構設計表結構設計創建表的實質就是定義表結構以及設置表和列的屬性。創建表之前，先要確定表的名字、表的屬性，同時確定表所包含的列名、列的數據

9、類型、長度、是否可為空值、約束條件、默認值設置、規則以及所需索引、哪些列是主鍵、哪些列是外鍵等屬性，這些屬性構成表結構。這里以本書要使用到的學生成績管理系統的三個表：學生表（表名為XSB）、課程表（表名為KCB）和成績表（表名為CJB）為例介紹如何設計表的結構。最終設計出XSB的表結構如表3.3所示。列 名數 據 類 型是 否 可 空默 認 值說 明學號char(6)無主鍵姓名char(8)無 性別char(2)“男” 出生時間date無 專業char(12)無 總學分number(2)00 總學分100備注varchar2(200)無 3.2.1 表操作表操作參照XSB表結構的設計方法，同樣

10、可以設計出其他兩個表的結構。如表3.4所示是KCB的表結構，表3.5是CJB的表結構。列 名數 據 類 型是 否 可 空默 認 值說 明課程號char(3)無主鍵課程名char(16)無 開課學期number(1)1只能為18學時number(2)0 學分number(1)0 列 名數 據 類 型是 否 可 空默 認 值說 明學號char(6)無主鍵課程號char(3)無主鍵成績number(2)無 表3.4 KCB的表結構表3.5 CJB的表結構3.2.1 表操作表操作4創建表創建表（1）啟動SQL Developer，在“連接”節點下打開數據庫連接myorcl（已創建）。右擊“表”節點，選

11、擇“新建表”菜單項。（2）進入“創建表”窗口，在“名稱”欄中填寫表名XSB，在“表”選項卡的“列名”、“類型”、“大小”、“非空”、“主鍵”欄中分別填入（選擇）XSB表的“學號”列的列名、數據類型、長度、非空性和是否為主鍵等信息，完成后單擊“添加列”按鈕輸入下一列，直到所有的列輸入完為止，如圖3.13所示。3.2.1 表操作表操作（3）輸完最后一列的信息后，選中右上角的“高級”復選框，這時會顯示出更多的表選項，如表類型、列的默認值、約束條件、外鍵和存儲選項等，如圖3.14所示，例如要設置默認值可以在“列屬性”選項頁中該列的“默認”欄中輸入默認值。3.2.1 表操作表操作5修改表修改表使用SQL

12、 Developer工具修改表的方法很簡單。XSB表創建完成后在主界面的“表”目錄下可以找到該表。右擊XSB表選擇“編輯”菜單項，進入“編輯表”窗口（類似圖3.14的界面），在該窗口中的“列”選項頁右側單擊 按鈕可以添加新列，單擊 按鈕可以刪除列，在“列屬性”選項頁的各欄中可以修改列的屬性。表的主鍵列不能直接刪除，要刪除必須先取消主鍵。單擊窗口左側的“主鍵”選項，在窗口右邊的“所選列”欄會顯示已被設為主鍵的列，如圖3.15所示。3.2.1 表操作表操作6刪除表刪除表以刪除XSB表為例，在“表”目錄下右擊XSB表選擇“表”菜單下的“刪除”子菜單項，如圖3.16所示，之后彈出“刪除”確認對話框，選

13、中“級聯約束條件”復選框，單擊“應用”按鈕，彈出表已刪除的提示消息，單擊“確定”按鈕即可。3.2.2 表數據操作表數據操作1插入記錄插入記錄首先，啟動SQL Developer，打開myorcl連接（需要輸入SCOTT用戶口令），展開“表”目錄，單擊“XSB”表，在右邊窗口中單擊“數據”選項卡，切換到表數據窗口，如圖3.17所示。3.2.2 表數據操作表數據操作說明：在輸入“出生時間”列數據時，Oracle 11g默認的日期格式為“DD-MM 月-YY”，例如，日期“1997-02-10”應該輸入“10-2月-97”。為能使用我們所習慣的輸入方式，這里先要修改一下數據庫默認的日期格式，在SQL

14、 Developer命令窗口中執行如下語句（具體操作見稍后的3.2.3節）：ALTER SESSIONSET NLS_DATE_FORMAT = YYYY-MM-DD;3.2.2 表數據操作表數據操作輸入完一行數據后，單擊 （提交更改）按鈕將數據保存到數據庫中，同時下方的“消息 - 日志”子窗口列出用于插入數據的INSERT語句，如圖3.18所示。3.2.2 表數據操作表數據操作2修改記錄修改記錄修改記錄的方法與插入類似，在“數據”選項頁找到要修改的記錄所在行，修改后該行的行號前會出現一個“*”號，如圖3.19所示，更改完成單擊“提交”按鈕保存修改的數據。3.2.2 表數據操作表數據操作3刪除

15、記錄刪除記錄如果要刪除一行記錄，選中該行，單擊 （刪除所選行）按鈕，之后該行的行號前會出現一個“-”號，如圖3.20所示，單擊“提交”按鈕確認刪除。3.2.2 表數據操作表數據操作4撤銷操作撤銷操作如果需要撤銷之前對表中記錄所做的操作，只需在單擊“提交”按鈕之前單擊 （回退）按鈕即可，如圖3.21所示，為撤銷上一步的刪除操作。3.2.3 執行執行SQL命令命令使用SQL Developer不僅可以以圖形界面方式操作數據庫表中的記錄，還可以直接編輯和運行SQL語句。啟動SQL Developer，單擊工具欄 按鈕的右下箭頭選擇“myorcl”，界面上將出現命令編輯區，如圖3.22所示，在其中輸入

16、要運行的SQL語句，如下：CREATE TABLE XSB(學號 char(6) NOT NULL PRIMARY KEY,姓名 char(8) NOT NULL,性別 char(2) DEFAULT 男 NOT NULL,出生時間 date NOT NULL,專業 char(12) NULL,總學分 number(2) NULL,備注 varchar2(200) NULL);3.2.3 執行執行SQL命令命令圖3.22 在SQL Developer中運行SQL語句3.2.3 執行執行SQL命令命令這里是以執行創建表的CREATE TABLE命令（稍后在3.3節中詳細介紹）為例，操作前須先刪除

17、之前已創建的XSB表，輸完命令后單擊窗口上方的 或 按鈕即可執行該SQL語句重新創建XSB表。表建好后，往其中錄入樣本數據（見附錄A）以備后用，如圖3.23所示。3. 3 命令方式操作數據庫（采用命令方式操作數據庫（采用SQL*Plus）3.3.1 創建表創建表在以自己的模式創建表時，必須擁有CREATE TABLE系統權限；在其他用戶模式下創建表時，必須擁有CREATE ANY TABLE系統權限。Oracle創建表使用CREATE TABLE語句，基本的語法格式為：CREATE TABLE . ( DEFAULT DEFAULT ,n,n)AS 3.3.1 創建表創建表說明：說明：（1）：

18、用戶方案是指該表所屬的用戶，如果省略則默認為當前登錄的用戶。（2）DEFAULT：關鍵字DEFAULT指定某一列的默認值。默認值的數據類型必須與該列的數據類型相匹配，列的長度必須足以容納這一表達式值。（3）：定義一個完整性約束作為列定義的一部分，該子句的語法為：NOT NULLUNIQUE PRIMARY KEY REFERENCES . ()CHECK()（4）：定義一個完整性約束作為表定義的一部分。（5）AS ：表示將由子查詢返回的行插入到所創建的表中，子查詢的使用將在4.2.2節中具體介紹。3.3.1 創建表創建表【例3.1】 利用CREATE TABLE命令為XSCJ數據庫建立表KCB

19、。表結構參照表3.4。CREATE TABLE KCB(課程號 char(3) NOT NULL PRIMARY KEY,課程名 char(16) NOT NULL,開課學期 number(1) NULL,學時 number(2) NULL, 學分 number(1)NOT NULL)3.3.1 創建表創建表創建完畢，用命令“DESCRIBE KCB;”可查看KCB表結構，如圖3.24所示。3.3.1 創建表創建表如果表的主鍵由兩個或多個列構成，則必須使用PRIMARY KEY關鍵字定義為表的完整性約束，語法格式如下：CREATE TABLE ( DEFAULT ,nPRIMARY KEY(,

20、 ,.n)3.3.1 創建表創建表【例3.2】 利用CREATE TABLE命令為XSCJ數據庫建立表CJB。表結構參照表3.5。CREATE TABLE CJB( 學號 char(6) NOT NULL,課程號 char(3) NOT NULL,成績 number(2) NULL,PRIMARY KEY(學號, 課程號) )同樣可用命令“DESCRIBE CJB;”查看CJB表結構。3.3.1 創建表創建表【例3.3】 創建XSB表中計算機專業學生的記錄備份表，表名為XS_JSJ。CREATE TABLE XS_JSJAS SELECT * FROM XSBWHERE 專業= 計算機 ;創建

21、完畢，可用SQL Developer查看結果，如圖3.25所示，可以看到XS_JSJ表中存儲（備份）了XSB表中計算機專業全部11名學生的記錄。3.3.2 修改表修改表修改表結構使用ALTER TABLE語句，語法格式為：ALTER TABLE . ADD( DEFAULT 列約束,n) /*增加新列*/ MODIFY( DEFAULT 列約束,n) /*修改已有列屬性*/ /*刪除列或約束條件*/3.3.2 修改表修改表說明：說明：（1）ADD子句：用于向表中增加一個新列，新的列定義和創建表時定義列的格式一樣，一次可添加多個列，中間用逗號隔開。（2）MODIFY子句：用于修改表中某列的屬性（

22、數據類型、默認值等）。在修改數據類型時需要注意，如果表中該列所存數據的類型與將要修改的列類型沖突，則會發生錯誤。例如，原來char類型的列要修改為number類型，而原來列值中有字符型數據“a”，則無法修改。（3）DROP子句：該子句用于從表中刪除指定的字段或約束，語法格式為：DROP COLUMN PRIMARY KEY UNIQUE (,n) CONSTRAINT CASCADE 3.3.2 修改表修改表【例3.4】 使用ALTER TABLE語句修改XSCJ數據庫中的XS_JSJ表。（1）在表XS_JSJ中增加兩列：獎學金等級、等級說明。ALTER TABLE XS_JSJADD (獎學

23、金等級 number(1),等級說明 varchar2(40) DEFAULT 獎金1000元);運行結果如圖3.26所示。3.3.2 修改表修改表（2）在XS_JSJ表中修改“等級說明”列的默認值。ALTER TABLE XS_JSJMODIFY (等級說明 DEFAULT 獎金800元 );運行語句后，打開SQL Developer的“編輯表”窗口查看XS_JSJ表的列屬性，可見“等級說明”列的默認值已改為“獎金800元”，如圖3.27所示。3.3.2 修改表修改表（3）在表XS_JSJ中刪除“獎學金等級”和“等級說明”列。ALTER TABLE XS_JSJDROP COLUMN 獎學金

24、等級;ALTER TABLE XS_JSJDROP COLUMN 等級說明;運行結果如圖3.28所示。3.3.2 修改表修改表（4）為XS_JSJ表添加主鍵。ALTER TABLE XS_JSJADD (CONSTRAINT PK_JSJ PRIMARY KEY(學號) );運行語句后，打開SQL Developer的“編輯表”窗口，可以看到“學號”列已被設為主鍵，如圖3.29所示。3.3.3 刪除表刪除表語法格式：DROP TABLE . 例如要刪除表XS_JSJ，使用如下語句：DROP TABLE XS_JSJ;執行結果如圖3.30所示。3.3.4 插入記錄插入記錄1INSERT語句語句插

25、入記錄一般使用INSERT語句，語法格式為：INSERT INTO (,n) VALUES(,n)該語句的功能是向指定的表中加入一行，由VALUES指定各列的值。【例3.5】 向XSCJ數據庫的表XSB中插入如下的一行：151114 周何駿 計算機 男 1998-09-25 90可以使用如下的SQL語句：INSERT INTO XSB(學號, 姓名, 性別, 出生時間, 專業, 總學分) VALUES(151114, 周何駿, 男,TO_DATE(19980925,YYYYMMDD), 計算機, 90);或者執行下列命令的效果相同：INSERT INTO XSB VALUES(151114,

26、周何駿, 男, 1998-09-25, 計算機, 90, NULL);然后再運行COMMIT命令：COMMIT;3.3.4 插入記錄插入記錄最后，使用SELECT語句查詢是否添加了該行記錄：SELECT 學號, 姓名, 性別, 出生時間, 專業, 總學分 FROM XSBWHERE 學號= 151114;運行結果如圖3.31所示。3.3.4 插入記錄插入記錄【例3.6】 向具有默認值字段的表中插入記錄。創建一個具有默認值字段的表test：CREATE TABLE test(姓名 char(20) NOT NULL, 專業 varchar2(30) DEFAULT(計算機), 年級 number

27、 NOT NULL);用INSERT向test表中插入一條記錄：INSERT INTO test(姓名, 年級) VALUES(周何駿, 3);3.3.4 插入記錄插入記錄運行結果如圖3.32所示。利用INSERT語句還可以把一個表中的部分數據插入到另一個表中，但結果集中每行數據的字段數、字段的數據類型要與被操作的表完全一致，語法格式為：INSERT INTO 3.3.4 插入記錄插入記錄【例3.7】 用如下的CREATE語句建立表XSB1：CREATE TABLE XSB1( num char(6) NOT NULL, name char(8) NOT NULL, speiality cha

28、r(12) NULL);然后用INSERT語句向XSB1表中插入數據，如下：INSERT INTO XSB1 SELECT 學號, 姓名, 專業 FROM XSB WHERE 姓名= 王林;3.3.4 插入記錄插入記錄這條INSERT語句將XSB表中姓名為“王林”的所有學生的學號、姓名和專業名列的值插入到XSB1表的各行中。運行結果如圖3.33所示。3.3.4 插入記錄插入記錄2MERGE語句語句在Oracle 12c中有MERGE語句，用于根據與源表聯接的結果，對目標表執行插入、更新或刪除操作。例如，根據在一個表中找到的差異在另一個表中插入、更新或刪除行，這種方法可以對兩個表進行信息同步。語

29、法格式如下：MERGE INTO USING ON ()WHEN MATCHED THEN UPDATE SET| DELETEWHEN NOT MATCHED THEN INSERT() VALUES()3.3.4 插入記錄插入記錄【例3.8】 創建表a，將XSB表中的數據添加至該表。創建表a的語句如下：CREATE TABLE a( XH char(6)NOT NULL PRIMARY KEY, XM char(8) NOT NULL, XB char(2) NOT NULL, CSSJ date NOT NULL, ZY char(12) NULL, ZXF number(2) NULL, BZvarchar(200) NULL);進行信息同步使用如下語句：MERGE INTO aUSING XSB ON (a.XH=XSB.學號)WHEN MATCHED THEN UPDATE SET a.XM=XSB.姓名, a.XB=XSB.性別, a.CSSJ=XSB.出生時間,a.ZY=XSB.專業, a.ZXF=XSB.總學分, a.BZ=XSB.備注WHEN NOT MATCHEDTHEN INSERT VALUES(XSB.學號,XSB.姓名,XSB.性別,XSB.出生時間,XSB.專業,XSB.總學分, XSB.備注);