1、Oracle數據庫入門線上線下平臺預習2/46本章任務安裝Oracle數據庫創建員工表和部門表，并對表中數據進行CRUD操作對員工表進行分頁查詢按照業務的需要，使用SQL語言對員工信息數據進行查詢3/46本章目標理解數據庫基本概念掌握安裝、配置和連接數據庫了解數據類型和操作符會使用SQL語句對數據進行操作會使用常用內置函數4/46Oracle簡介Oracle（甲骨文）公司1977年，三人合伙創辦（Software Development Laboratories，SDL）1979年，更名為Relational Software Inc.，RSI1983年，為了突出核心產品 ，RSI更名為Ora

2、cle2002年04月26日，啟用“甲骨文”作為中文注冊商標Oracle數據庫管理系統Oracle公司的核心產品 目前最流行的數據庫主要版本Oracle8i/9i(internet)、Oracle10g/11g(grid) 基于C/S系統結構 5/46Oracle 主要組件 實 例 PMON SMON DBWR LGWR CKPT 其他 數據文件控制文件控制文件日志文件日志文件參數文件歸檔日志文件口令文件SGA用戶進程服務器進程PGA共享池數據緩沖區日志緩沖區內存結構后臺進程數據庫數據文件數據文件6/46數據庫表空間1表空間2表空間1包含1個數據文件表空間2包含2個數據文件data1_01.d

3、bfdata2_01.dbfdata2_02.dbfOracle基本概念表空間全局數據庫名 用于區分一個數據庫的內部標識全局數據庫名=數據庫名+域名使數據庫的取名在整個網絡環境中唯一模式和模式對象模式為模式對象的集合每一個用戶對應一個模式模式對象是用戶擁有的對象非模式對象與用戶無關，如表空間7/46安裝Oracle安裝注意事項選擇安裝目錄最好是非系統盤安裝時可以直接創建數據庫，也可以選擇不創建服務器的主機名不能有特殊字符如果在創建數據庫階段提示錯誤，繼續安裝輸入的數據庫口令最好記錄在記事本中，以免使用時忘記經驗12348/46啟動、配置和鏈接數據庫 根據安裝時創建數據庫ORCL，使用Sytem

4、用戶登錄前做哪些工作？啟動數據庫服務配置客戶端網絡服務每個數據庫都有SYS和SYSTEM兩個默認用戶，都具有創建用戶權限使用SYSTEM用戶登錄ORCL數據庫問題分析9/46啟動數據庫服務通過選擇打開“服務”窗口，可以看到Oracle服務OracleService：數據庫服務OracleOraDb11g_hom1TNSListener ：數據庫監聽服務OracleDBConsole：企業管理器服務演示示例1：查看Oracle服務10/46配置客戶端網絡服務服務器端配置監聽器客戶端配置網絡服務名tnsnames.oraOracle 客戶端listener.oraOracle 服務器監聽協議地址端

5、口號全局數據庫名稱監聽協議地址端口號服務名本地網絡服務名在安裝服務器軟件時自動配置一個監聽器Oracle中的 Net Manager工具和Net Configuration Assistant都能用來配置監聽器和網絡服務名建議使用Net Manager工具注意演示示例2：連接數據庫配置11/46SYS和SYSTEM用戶 SYS和SYSTEM用戶都是Oracle 的系統用戶，都使用SYSTEM表空間，SYS擁有更大的權限通過SQL*Plus方式連接 通過PL/SQL Developer方式連接連接數據庫SYS用戶SYSTEM用戶地位Oracle的一個超級用戶Oracle默認的系統管理員，擁有DB

6、A權限作用主要用來維護系統信息和管理實例 通常用來管理Oracle數據庫的用戶、權限和存儲等登錄身份只能以SYSDBA或SYSOPER角色登錄只能以Normal方式登錄12/46學員操作配置并登錄ORCL數據庫2-1訓練要點啟動數據庫配置數據庫連接數據庫需求說明教師機作為服務器，學生機作為客戶端在客戶端創建本地網絡服務名myOrcl以System用戶連接服務器的Orcl數據庫訪問Scott用戶下的emp表指導技術顧問講解需求13/46學員操作配置并登錄ORCL數據庫2-2實現思路服務器端啟動操作系統中的數據庫服務和監聽服務客戶端使用“Net Manager”工具創建本地網絡服務名“myOrcl

7、”客戶端使用SQL*Plus工具，用System用戶連接Orcl數據庫訪問Scott用戶下emp表使用PL/SQL Developer工具，以System用戶連接Orcl數據庫訪問Scott用戶下emp表SELECT * FROM Scott.emp;指導完成時間：20分鐘14/46共性問題集中講解常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解 15/46小結使用 Oracle 數據庫的開發流程安裝 Oracle 服務器軟件創建數據庫(安裝時自動創建)配置監聽器(安裝時自動配置)啟動Oracle實例(自動啟動服務)創建用戶表空間創建新用戶并授權安裝 Oracle 客戶端軟件配置網絡服務

8、名以新用戶登錄 Oracle提交 SQL 查詢服務器端客戶端后面章節講解16/46數據類型CHAR：VARCHAR2：NCHAR和NVARCHAR2：NUMBER：字符數據類型數值數據類型存儲固定長度的字符串存儲可變長度的字符串存儲Unicode字符集類型存儲整數和浮點數，格式為NUMBER(p, s)數據類型描述DATE ：TIMESTAMP：BLOB：CLOB：BFILE：LOB數據類型DATE：存儲日期和時間數據 TIMESTAMP：秒值精確到小數點后6位存儲二進制對象存儲字符格式的大型對象 將二進制數據存儲操作系統文件中日期時間數據類型不建議使用VARCHAR、 INTEGER、FLO

9、AT、DOUBLE等類型經驗17/46偽列Oracle 中偽列就像一個表列，但是它并沒有存儲在表中偽列可以從表中查詢，但不能插入、更新和刪除它們的值常用的偽列有ROWID和ROWNUMROWID格式ROWID 是表中行的存儲地址，該地址可以唯一地標識數據庫中的一行，可以使用 ROWID 偽列快速地定位表中的一行AAAR3s AAE AAAACX AAA數據對象編號文件編號塊編號 行編號ROWNUM 是查詢返回的結果集中行的序號，可以使用它來限制查詢返回的行數如何查詢stuInfo表中自然排序的第3條記錄？提問演示示例1：查詢ROWID偽列演示示例2：查詢ROWNUM偽列18/46分頁如何從EM

10、P表中查詢出薪水從高到低排序的第59條記錄？使用偽列ROWNUM實現分頁查詢SELECT *FROM ( SELECT e.*,rownum rnFROM ( SELECT * FROM emp ORDER BY sal DESC) e)WHERE rn=5 AND rn100萬的表的信息選擇無重復的行選擇帶條件和排序的記錄實用列別名利用現有的表創建新表問題演示示例3：創建stuInfo表22/46數據操縱語言4-1不重復顯示所有學員姓名和年齡按照姓名升序，如果姓名相同按照年齡降序排序SQLSELECT stuNo,stuName, stuAge FROM stuInfo WHERE stuA

11、ge17 ORDER BY stuName ASC, stuAge DESC;SQLSELECT DISTINCT stuName,stuAge FROM stuInfo;降序示例演示示例4：操縱stuInfo表23/46升序數據操縱語言4-2使用別名顯示姓 名、年 齡和身份證號列利用現有的表創建新表SQL CREATE TABLE newStuInfo1 AS SELECT * FROM StuInfo; 選擇所有數據 SQL SELECT stuName as 姓 名, stuAge as 年 齡, stuID as 身份證號 FROM StuInfo;含有特殊字符（如空格）加雙引號SQL

12、 CREATE TABLE newStuInfo2 AS SELECT stuName,stuNo,stuAge FROM StuInfo; SQL CREATE TABLE newStuInfo2 AS SELECT stuName,stuNo,stuAge FROM StuInfo WHERE 1=2; 選擇指定的列所有數據只留表結構，不留數據示例24/46數據操縱語言4-3查看表中行數取出stuName,stuAge列不存在重復數據的記錄SQL SELECT stuName,stuAge FROM stuInfo GROUP BY stuName,stuAge HAVING(COUNT(

13、stuName|stuAge) SELECT COUNT (*) FROM stuInfo;執行效率高SQLSELECT COUNT (1) FROM stuInfo;執行效率低示例25/46數據操縱語言4-4刪除stuName、stuAge列重復的行(保留一行)SQLDELETE FROM stuInfo WHERE ROWID NOT IN( SELECT max(ROWID) FROM stuInfo GROUP BY stuName,stuAge );示例26/46事務控制語言2-1用于事務控制的語句SQLINSERT INTO dept VALUES(50,a,null);SQLIN

14、SERT INTO dept VALUES(60,b,null);SQLSAVEPOINT a;SQLINSERT INTO dept VALUES(70,c,null);SQLROLLBACK TO SAVEPOINT a;SQLCOMMIT;SQLSELECT * FROM dept;下列代碼執行后，結果集是否包含50、60和70的記錄？為什么？COMMIT ROLLBACK SAVEPOINT ROLLBACK TO 提問演示示例5：事務控制語言27/46事務控制語言2-2何時結束事務？數據被提交COMMIT命令執行DDL或DCL后與Oracle分離數據被撤消ROLLBACK命令服務器進

15、程異常結束DBA停止會話28/46學員操作創建員工表并進行操作2-1訓練要點創建員工表插入、查詢數據添加約束添加、刪除列需求說明使用System用戶登錄，創建員工表employee為員工編號創建主鍵約束，部門編號列創建外鍵約束根據提供的資料插入數據，顯示員工表中薪水從高到低排序記錄指導技術顧問講解需求29/46學員操作創建員工表并進行操作2-2實現思路使用System用戶連接ORCL數據庫創建employee表插入數據可以直接插入數據，也可以利用SCOTT用戶下emp中的數據進行插入添加約束向employee 表添加empTel_no 和empAddress 兩列刪除empTel_no 和em

16、pAddress 兩列按照薪水從高到低顯示數據指導完成時間：15分鐘30/46學員操作實現分頁查詢需求需求說明使用System用戶登錄，根據employee表現有記錄，實現查詢員工表中薪水從高到低排序的第59條記錄練習完成時間：15分鐘31/46共性問題集中講解常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解 32/46小結Oracle SQL語言分為DDL、DCL、TCL和DMLDDL對表結構進行管理DML對表數據進行管理TCL進行事務控制DML進行用戶權限管理33/46SQL 操作符SQL 操作符集合操作符邏輯操作符比較操作符算術操作符連接操作符Oracle 支持的 SQL 操作符分

17、類集合操作符UNION（聯合）UNION ALL（聯合所有）INTERSECT（交集）MINUS（減集）連接2個字符串；連接字符串和數字等演示示例6：操作符示例34/46SQL 函數SQL函數 01.單行函數每一行只返回一個值可以出現在 SELECT 子句中和 WHERE 子句中 03.分析函數分析函數根據一組行來計算聚合值分析函數為每組記錄返回多個行02.分組函數根據分組的情況，返回每組里的一個結果值可以在使用ORDER BY 和 HAVING子句中使用日期函數數字函數字符函數轉換函數其他函數35/46轉換函數轉換函數將值從一種數據類型轉換為另一種數據類型常用的轉換函數TO_CHAR()TO

18、_DATE()TO_NUMBER()SELECT TO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SS) FROM dual; SELECT TO_CHAR(1210.7, $9,999.00) FROM dual;SELECT TO_DATE(2005-12-06,yyyy-mm-dd) FROM dual; SELECT TO_NUMBER(100) FROM dual;示例演示示例7：轉換函數36/46其它函數轉換空值的函數NVLNVLDECODESELECT ename, sal+NVL(comm,0) sal1, NVL2(comm,sal+comm,

19、sal) sal2, DECODE(to_char(hiredate, MM ), 01, 一月, 02, 二月, 03, 三月,04,四月, 05,五月,06,六月, 下半年) mon FROM employee;演示示例8：其他函數示例37/46分析函數分析函數根據一組行來計算聚合值分析函數為每組記錄返回多個行以下三個分析函數用于計算一個行在一組有序行中的排位，序號從1開始返回連續的排位，不論值是否相等具有相等值的行排位相同，序數隨后跳躍 具有相等值的行排位相同，序號是連續的RANKDENSE_RANKROW_NUMBER函數名：分析函數名字 參數：函數需要傳入的參數分區子句（PARTITION BY）：將查詢結果分為不同的組，功能類似于GROUP BY語句 排序子句（ORDERBY）：將每個分區進行排序 例：函數名( 參數) OVER( 分區子句 排序子句)RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC)語法演示示例9： 分析函數38/46學員操作使用分析函數進行查詢需求說明公司需要查詢每個部門薪水第二高的員工基本信息（包含并列第二）實現步驟使用分析