目 錄
項(xiàng)目一 數(shù)據(jù)庫(kù)基礎(chǔ) .................................................................................................................. 1
任務(wù)一 數(shù)據(jù)庫(kù)概述 .......................................................................................................... 2
一、數(shù)據(jù)與數(shù)據(jù)庫(kù) ...................................................................................................... 2
二、數(shù)據(jù)庫(kù)管理系統(tǒng) .................................................................................................. 2
三、數(shù)據(jù)庫(kù)系統(tǒng) .......................................................................................................... 4
四、數(shù)據(jù)模型 .............................................................................................................. 4
五、結(jié)構(gòu)化查詢語(yǔ)言 .................................................................................................. 6
任務(wù)二 關(guān)系數(shù)據(jù)庫(kù)基礎(chǔ) .................................................................................................... 6
一、問(wèn)題的提出 .......................................................................................................... 7
二、函數(shù)依賴 .............................................................................................................. 9
三、范式的判定條件與規(guī)范化 ................................................................................ 11
任務(wù)三 數(shù)據(jù)庫(kù)設(shè)計(jì) ........................................................................................................ 13
一、需求分析 ............................................................................................................ 14
二、概念設(shè)計(jì) ............................................................................................................ 19
三、邏輯設(shè)計(jì) ............................................................................................................ 21
四、物理設(shè)計(jì) ............................................................................................................ 22
五、數(shù)據(jù)庫(kù)實(shí)施 ........................................................................................................ 22
六、數(shù)據(jù)庫(kù)運(yùn)行和維護(hù) ............................................................................................ 23
項(xiàng)目總結(jié) ............................................................................................................................ 23

項(xiàng)目二 Oracle基礎(chǔ) ............................................................................................................... 24
任務(wù)一 Oracle簡(jiǎn)介 ........................................................................................................ 25
一、什么是 Oracle .................................................................................................... 25
二、Oracle 的發(fā)展過(guò)程 ............................................................................................ 26
三、Oracle 體系結(jié)構(gòu) ................................................................................................ 27
任務(wù)二 Oracle下載與安裝 ............................................................................................ 31
一、Oracle 的下載 .................................................................................................... 32
二、Oracle 的安裝 .................................................................................................... 34
三、測(cè)試安裝是否成功 ............................................................................................ 39
任務(wù)三 Oracle管理工具 ................................................................................................ 41
一、SQL*Plus 工具 .................................................................................................. 41
二、數(shù)據(jù)庫(kù)配置助手 ............................................................................................... 44
三、SQL Developer 工具 ......................................................................................... 47
項(xiàng)目總結(jié) ........................................................................................................................... 51
項(xiàng)目實(shí)戰(zhàn) ........................................................................................................................... 51
實(shí)戰(zhàn)一 啟動(dòng)并登錄 SQL*Plus，然后退出 .......................................................... 51
實(shí)戰(zhàn)二 創(chuàng)建名稱為 oracle 的連接，指定用戶名和角色 .................................... 52

項(xiàng)目三 數(shù)據(jù)表操作 ............................................................................................................... 54
任務(wù)一 數(shù)據(jù)表基礎(chǔ) ....................................................................................................... 55
一、數(shù)據(jù)類型 ........................................................................................................... 55
二、數(shù)據(jù)表的結(jié)構(gòu) ................................................................................................... 56
任務(wù)二 創(chuàng)建和管理表 ................................................................................................... 57
一、創(chuàng)建表 ............................................................................................................... 57
二、修改表 ............................................................................................................... 60
任務(wù)三 數(shù)據(jù)記錄 ............................................................................................................. 62
一、添加數(shù)據(jù) ........................................................................................................... 62
二、編輯數(shù)據(jù) ........................................................................................................... 64
三、表約束 ............................................................................................................... 65
四、刪除表 ............................................................................................................... 69
項(xiàng)目總結(jié) ........................................................................................................................... 70
項(xiàng)目實(shí)戰(zhàn) ........................................................................................................................... 71
實(shí)戰(zhàn)一 創(chuàng)建表 ....................................................................................................... 71
實(shí)戰(zhàn)二 給表添加數(shù)據(jù) ........................................................................................... 72

項(xiàng)目四 數(shù)據(jù)查詢 ................................................................................................................... 75
任務(wù)一 基本數(shù)據(jù)查詢 ................................................................................................... 76
一、SELECT 的基本語(yǔ)法 ........................................................................................ 76
二、簡(jiǎn)單查詢 ........................................................................................................... 76
三、設(shè)置別名 ........................................................................................................... 78
四、使用 DISTINCT 過(guò)濾重復(fù)數(shù)據(jù) ....................................................................... 79
五、WHERE 查詢 .................................................................................................... 79
六、ORDER BY 排序查詢 ...................................................................................... 83
七、多表關(guān)聯(lián)查詢 ................................................................................................... 84
任務(wù)二 聚合函數(shù) ........................................................................................................... 87
任務(wù)三 連接查詢 ........................................................................................................... 90
一、交叉連接 ........................................................................................................... 90
二、內(nèi)連接 ............................................................................................................... 91
三、外連接 ............................................................................................................... 91
任務(wù)四 子查詢 ................................................................................................................ 93
項(xiàng)目總結(jié) ............................................................................................................................ 97
項(xiàng)目實(shí)戰(zhàn) ............................................................................................................................ 98
實(shí)戰(zhàn)一 范圍查詢 .................................................................................................... 98
實(shí)戰(zhàn)二 模糊查詢 .................................................................................................... 98
實(shí)戰(zhàn)三 排序查詢 .................................................................................................... 98
實(shí)戰(zhàn)四 使用聚合函數(shù)查詢 .................................................................................... 99
實(shí)戰(zhàn)五 連接查詢 .................................................................................................... 99
實(shí)戰(zhàn)六 子查詢 ...................................................................................................... 100

項(xiàng)目五 索引和視圖 .............................................................................................................. 101
任務(wù)一 索引 .................................................................................................................. 102
一、索引分類 .......................................................................................................... 102
二、創(chuàng)建索引 .......................................................................................................... 103
三、修改和刪除索引 .............................................................................................. 105
任務(wù)二 視圖 .................................................................................................................. 107
一、創(chuàng)建視圖 .......................................................................................................... 107
二、管理視圖 .......................................................................................................... 110
項(xiàng)目總結(jié) .......................................................................................................................... 114
項(xiàng)目實(shí)戰(zhàn) .......................................................................................................................... 114
實(shí)戰(zhàn)一 創(chuàng)建位圖索引 .......................................................................................... 114
實(shí)戰(zhàn)二 創(chuàng)建視圖并查詢數(shù)據(jù) .............................................................................. 115

項(xiàng)目六 序列、同義詞和事務(wù) .............................................................................................. 117
任務(wù)一 序列 .................................................................................................................. 118
一、創(chuàng)建序列 .......................................................................................................... 118
二、使用序列 .......................................................................................................... 120
三、管理序列 .......................................................................................................... 121
任務(wù)二 同義詞 .............................................................................................................. 122
一、同義詞概述 ...................................................................................................... 123
二、創(chuàng)建同義詞 ...................................................................................................... 123
三、刪除同義詞 ...................................................................................................... 125
任務(wù)三 事務(wù) .................................................................................................................... 126
一、事務(wù)處理概述 .................................................................................................. 126
二、執(zhí)行事務(wù) .......................................................................................................... 126
項(xiàng)目總結(jié) .......................................................................................................................... 132
項(xiàng)目實(shí)戰(zhàn) .......................................................................................................................... 133
實(shí)戰(zhàn)一 創(chuàng)建序列并使用 ...................................................................................... 133
實(shí)戰(zhàn)二 設(shè)置保存點(diǎn)，然后回滾該保存點(diǎn) .......................................................... 133

項(xiàng)目七 PL/SQL編程 .......................................................................................................... 135
任務(wù)一 PL/SQL基礎(chǔ) ................................................................................................... 136
一、PL/SQL 簡(jiǎn)介 ................................................................................................... 136
二、數(shù)據(jù)類型 ......................................................................................................... 137
三、變量 ................................................................................................................. 140
四、函數(shù) ................................................................................................................. 142
五、流程控制語(yǔ)句 ................................................................................................. 151
任務(wù)二 游標(biāo) ................................................................................................................. 155
一、游標(biāo)概念 ......................................................................................................... 155
二、顯式游標(biāo)處理 ................................................................................................. 155
三、隱式游標(biāo)處理 ................................................................................................. 157
四、使用游標(biāo) ......................................................................................................... 157
項(xiàng)目總結(jié) ......................................................................................................................... 158
項(xiàng)目實(shí)戰(zhàn) ......................................................................................................................... 159
實(shí)戰(zhàn)一 查詢員工信息 ......................................................................................... 159
實(shí)戰(zhàn)二 打印 101 號(hào)學(xué)生的信息 ......................................................................... 159

項(xiàng)目八 存儲(chǔ)過(guò)程、函數(shù)和觸發(fā)器 ..................................................................................... 161
任務(wù)一 存儲(chǔ)過(guò)程 ......................................................................................................... 162
一、存儲(chǔ)過(guò)程概述 ................................................................................................. 162
二、創(chuàng)建存儲(chǔ)過(guò)程 ................................................................................................. 163
三、調(diào)用存儲(chǔ)過(guò)程 ................................................................................................. 165
四、存儲(chǔ)過(guò)程的參數(shù) ............................................................................................. 166
五、刪除存儲(chǔ)過(guò)程 ................................................................................................. 169
任務(wù)二 函數(shù) ................................................................................................................. 170
一、創(chuàng)建函數(shù) ......................................................................................................... 171
二、調(diào)用函數(shù) ......................................................................................................... 173
三、刪除函數(shù) ......................................................................................................... 174 任務(wù)三 觸發(fā)器 ............................................................................................................. 175
一、觸發(fā)器概述 ..................................................................................................... 175
二、創(chuàng)建觸發(fā)器 ..................................................................................................... 176
三、刪除觸發(fā)器 ..................................................................................................... 183
項(xiàng)目總結(jié) ......................................................................................................................... 184
項(xiàng)目實(shí)戰(zhàn) ......................................................................................................................... 184
實(shí)戰(zhàn)一 創(chuàng)建存儲(chǔ)過(guò)程并調(diào)用 ............................................................................. 184
實(shí)戰(zhàn)二 創(chuàng)建函數(shù)并調(diào)用 ..................................................................................... 185

項(xiàng)目九 數(shù)據(jù)的安全管理 ...................................................................................................... 187
任務(wù)一 表空間 .............................................................................................................. 188
一、表空間概述 ...................................................................................................... 188
二、查看表空間 ...................................................................................................... 189
三、創(chuàng)建表空間 ...................................................................................................... 190
四、修改表空間 ...................................................................................................... 191
五、刪除表空間 ...................................................................................................... 192
任務(wù)二 用戶和權(quán)限 ...................................................................................................... 192
一、用戶 .................................................................................................................. 192
二、權(quán)限 .................................................................................................................. 193
三、角色 .................................................................................................................. 194
任務(wù)三 數(shù)據(jù)導(dǎo)入和導(dǎo)出 .............................................................................................. 195
一、導(dǎo)出數(shù)據(jù) .......................................................................................................... 196
二、導(dǎo)入數(shù)據(jù) .......................................................................................................... 199
項(xiàng)目總結(jié) .......................................................................................................................... 204
項(xiàng)目實(shí)戰(zhàn) .......................................................................................................................... 204
實(shí)戰(zhàn)一 導(dǎo)出 EMP 表中的數(shù)據(jù) ............................................................................ 204
實(shí)戰(zhàn)二 向 STUDENT 表中導(dǎo)入數(shù)據(jù) .................................................................. 206