Oracle SQL基礎培訓

1、1Oracle SQL 基礎培訓基礎培訓2目錄目錄u 課程介紹u DML基礎u SQL介紹u DCL基礎u DDL基礎u TL基礎3課程介紹課程介紹教程概述 本教程假設讀者已了解關系型數據庫基本原理，明白表、視圖、主鍵、索引、外鍵、約束、關聯等基本概念 本教程定位是Oracle SQL簡明、實用教程，偏向于SQL開發(fā)，若進一步學習Oracle數據庫設計，請參閱其他教程或書籍文檔 課程目標是完成課程后可進行項目中大部分Oracle SQL開發(fā)適用對象 學習過標準SQL，未使用過Oracle數據庫的讀者 適用過SQL Server或其他數據庫，未使用過Oracle數據庫的讀者4目錄目錄u 課程介紹

2、u DML基礎u SQL介紹u DCL基礎u DDL基礎u TL基礎5SQLSQL介紹介紹概述 SQL是Structured Query Language簡寫 SQL用于組織、訪問和處理數據庫中的數據標準的計算機語言 SQL具有第四代語言的特征，程序關注要告訴DBMS要得做什么操作，及其相應條件，至于如何得到數據，如何使用索引，如何關聯多表，由DBMS完成分類 Data Definition Language(DDL數據定義語言) Data Manipulation Language(DML數據操作語言) Data Control Language(DCL數據控制語言) Transaction

3、 Language(TL數據事務語言)6目錄目錄u 課程介紹u DML基礎u SQL介紹u DCL基礎u DDL基礎u TL基礎7DMLDML基礎基礎 算術運算符 關系運算符 邏輯運算符 SELECT語句 :查詢數據 集合運算符:復合查詢 SUBQUERY子查詢 LEVEL、PRIOR與CONNECT BY START WITH ROWNUM與分頁查詢 INSERT語句 :插入數據 UDATE語句 :更新數據 DELETE語句 :刪除數據 SELECT與INSERT、UPDATE、DELETE8DMLDML基礎基礎算術運算符號+ 加 1+1- 減 2-1* 乘 2*3/ 除 4/2| 字符連接

4、 Beijing | 20089DMLDML基礎基礎關系運算符號= 等于 ，1=1 不等于， 21 大于，21 小于， 1= 大于等于， 2=1= 小于等于，11，因聚合條件不能在WHERE指明;語法SELECT column1, column2, . column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2, . column_n;HAVING aggregate_condition1 . condition_n;aggregate_function 是匯聚

5、函數，通常為SUM, COUNT, MIN, MAX,AVG示例 查詢總工資大于35000的部門28DMLDML基礎基礎-SELECT-SELECT語句語句- GROUP BY- GROUP BY從句從句示例 查詢員工個數大于5的部門及其員工個數 查詢有最低工資為10000的部門及其最低員工工資 查詢有最高工資為10000的部門及其最高員工工資29DMLDML基礎基礎-SELECT-SELECT語句語句- GROUP BY- GROUP BY從句從句直接聚合 COUNT、SUM、MIN、MAX、AVG可以不和GROUP BY一起使用，直接對查詢數據直接進行聚合，但SELECT不能包含其他列示例

6、查詢員工總數Select Count(*) From employees查詢員工平均工資Select avg(salary) From employees查詢員工最高工資Select max(salary) From employees查詢員工最低工資Select min(salary) From employees查詢員工工資總數Select sum(salary) From employees30DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯用途JOIN將多表數據關聯起來，JOIN是關系型數據庫的魅力所在語法SELECT columns FROM table1,table2,

7、table3 WHERE predicates;只要把幾個表放于FROM子句后，這些表就會自動關聯起來分類CROSS-JOIN:不在WHERE從句指定任何關聯條件，返回的記錄數為各個表記錄數的笛卡爾乘積。INNER JOIN:在WHERE從句指定表之間關聯條件，返回符合關聯條件的指定的各個表記錄數及其各列值;OUTER JOIN: 在WHERE從句指定表之間關聯條件，返回符合關聯條件的指定的各個表記錄數及各列值，不符合關聯條件的“外連表”列返回空;31DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯- -兩表兩表示例 Suppliers供應商表 Orders采購訂單表;32DMLD

8、ML基礎基礎-JOINS-JOINS多表關聯多表關聯- -兩表兩表示例 INNER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id;33DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯- -兩表兩表示例 OUTER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders od

9、WHERE sp.supplier_id = od.supplier_id (+);34DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯- -三表三表示例 Suppliers供應商表 Orders采購訂單表 員工表35DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯- -三表三表示例 INNER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_date, od.order_date ,em.employee_nameFROM suppliers sp, orders od, employees emWHERE

10、sp.supplier_id = od.supplier_idAnd od.employee_number = em.employee_number;36DMLDML基礎基礎-JOINS-JOINS多表關聯多表關聯- -三表三表示例 OUTER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_date, od.order_date ,em.employee_nameFROM suppliers sp, orders od, employees emWHERE sp.supplier_id = od.supplier_id(+)And

11、od.employee_number = em.employee_number(+);37DMLDML基礎基礎集合運算符:復合查詢UNIONUNION ALLINTERSECTMINUS38DMLDML基礎基礎- -復合查詢復合查詢-UNION-UNION用途將多個SELECT語句的結果集加起來，去掉重復的數據(集合并運算)，限制：參與UNION的每個SELECT語句的相應列必須是相同數據類型語法select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;示例select

12、 supplier_idfrom suppliersUNIONselect supplier_idfrom orders;39DMLDML基礎基礎- -復合查詢復合查詢-UNION ALL-UNION ALL用途將多個SELECT語句的結果集加起來，不去掉重復的數據限制：參與UNION ALL的每個SELECT語句的相應列必須是相同數據類型語法select field1, field2, . field_nfrom tablesUNION ALLselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppli

13、ersUNION ALLselect supplier_idfrom orders;40DMLDML基礎基礎- -復合查詢復合查詢-INTERSECT-INTERSECT用途求多個SELECT的交集(集合交運算)限制：參與INTERSECT的每個SELECT語句的相應列必須是相同數據類型語法select field1, field2, . field_nfrom tablesINTERSECTselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersINTERSECTselect supplier

14、_idfrom orders;41DMLDML基礎基礎- -復合查詢復合查詢-MINUS-MINUS用途求在第一個SELECT，不在第二個SELECT的數據(集合減運算)限制：參與MINUS的每個SELECT語句的相應列必須是相同數據類型語法select field1, field2, . field_nfrom tablesMINUSselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersMINUSselect supplier_idfrom orders;42DMLDML基礎基礎-SUBQU

15、ERY-SUBQUERY子查詢子查詢概念 在一個主SELECT、INSERT、UPDATE、DELETE語句中，嵌套的、用括號括起來的SELECT語句，稱為子查詢分類 相關子查詢:子查詢中引用外部表 非相關子查詢:子查詢中不引用外部表示例 工資大于工號為1002員工工資的員工信息(不相關子查詢)Select * From employees em Where em.salary (Select salary From employees pl Where pl.employee_number = 1002);43DMLDML基礎基礎-SUBQUERY-SUBQUERY子查詢子查詢示例 查詢有下

16、采購訂單的員工(不相關子查詢)Select * From employees em, (Select Distinct employee_number From orders) v Where em.employee_number = v.employee_number 查詢所有部門及其部門員工工資總和(相關子查詢)Select dp.department_id, dp.department_name, (Select Sum(salary) From employees pl Where pl.department_id = dp.department_id) total_salary Fr

17、om departments dp44DMLDML基礎基礎-SUBQUERY-SUBQUERY子查詢子查詢示例 查詢有下采購訂單的員工(相關子查詢)Select * From employees em Where em.employee_number In (Select Distinct employee_number From orders) 查詢有下采購訂單的員工(相關子查詢)Select * From employees em Where Exists (Select 1 From orders od Where od.employee_number = em.employee_num

18、ber)45DMLDML基礎基礎-level-level與與CONNECT BYSTART WITHCONNECT BYSTART WITH背景應用系統(tǒng)長通常有類似菜單、產品類別等有上下級關系的樹形結構數據 通常會設計為ID、PARENT_ID結構46DMLDML基礎基礎-level-level與與CONNECT BYSTART WITHCONNECT BYSTART WITH原理 Oracle提供CONNECT BYSTART WITH用于類似結構查詢 提供關鍵字level在CONNECT BYSTART WITH查詢返回數據所在樹形結構層次，level值越大表示層級越深 示例 查詢“訂單管

19、理”及其下屬菜單，按樹形結構展現，使用lpad函數輔助效果更明顯(縮進)，lpad函數參考后續(xù)教程介紹47DMLDML基礎基礎-ROWNUM-ROWNUM與分頁查詢與分頁查詢背景應用系統(tǒng)在數據量大的時候，通常需要將數據分頁顯示48DMLDML基礎基礎-ROWNUM-ROWNUM與分頁查詢與分頁查詢原理 Oracle提供關鍵字rownum，在SELECT語句查詢返回數據中表示是第幾行 示例 查詢“訂單管理”及其下屬菜單，按樹形結構展現，使用lpad函數輔助效果更明顯，并讀取第1至第7行數據，選定部分要排好序49DMLDML基礎基礎INSERT語句基本INSERT語句INSERT與SELECT50

20、DMLDML基礎基礎-INSERT-INSERT-基本基本INSERTINSERT語句語句用途 將數據插入到指定表中語法INSERT INTO table(column-1, column-2, . column-n)VALUES(value-1, value-2, . value-n);示例Insert Into suppliers (supplier_id, supplier_name, city, type)Values (53553, TestInsertSupplier, Detroit, Cooking);51DMLDML基礎基礎-INSERT-INSERT-INSERT-INSE

21、RT與與SELECTSELECT用途將數據插入到指定表中語法INSERT INTO table(column-1, column-2, . column-n)SELECTColumn-1,column-2 . column-nFROM TablesWhERE predicates;示例 將所在城市為Newark的客戶生成供應商， 其中供應商supplier_id對應為客戶account_no， supplier_name對應為customer_nameINSERT INTO suppliers(supplier_id, supplier_name)SELECT account_no, cust

22、omer_nameFROM customersWHERE city = Newark;52DMLDML基礎基礎-INSERT-INSERT-INSERT-INSERT與與SELECTSELECT示例INSERT INTO clients(client_id, client_name, client_type)SELECT supplier_id, supplier_name, advertisingFROM suppliers spWHERE not exists (select * from clients clwhere cl.client_id = sp.supplier_id);53D

23、MLDML基礎基礎UPDATE語句基本UPDATE語句54DMLDML基礎基礎-UPDATE-UPDATE語句語句用途 UPDATE用于更新指定表符合條件的數據;語法 UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;示例 將名稱為Smith的供應商名稱更新為HP，同時在備注記錄舊名稱UPDATE suppliers SET supplier_name = HP,commets= Smith WHERE supplier_name = Smith;55DMLDML基礎基礎-UPDATE-UPDATE語句語句用

24、途更新根據客戶生產的供應商(客戶account_no生成為supplier_id)的備注為相應客戶的備注Update suppliers sp Set mets = (Select mets From customers cc Where cc.account_no = sp.supplier_id) Where Exists (Select mets From customers cc Where cc.account_no = sp.supplier_id);56DMLDML基礎基礎DELETE語句DELETE語句57DMLDML基礎基礎-DELETE-DELETE語句語句用途 DELET

25、E用于將數據從指定表刪除;語法 DELETE FROM tables WHERE predicates ;示例刪除供應商為DELETE FROM suppliers WHERE supplier_name = Smath;刪除沒有采購訂單的供應商Delete From suppliers sp Where Not Exists (Select od.order_id From orders od Where od.supplier_id = sp.supplier_id);58DMLDML基礎基礎-SELECT-SELECT與與INSERTINSERT、UPDATEUPDATE、DELETED

26、ELETE用途SELECT語句可以使用字符串拼湊的方法生成INSERT、UPDATE、DELETE語句，通常用于數據處理，比如清除按一定條件重復數據，修正數據，調整數據等;語法SELECT column1, column2, . column_n, INSERT STATEMENT, insert_sql,UPDATE STATEMENT update_sql,DELETE STATEMENT delete_sql FROM tables WHERE predicates示例 找到按名稱、所屬城市有重復的供應商數據，刪除ID最小的數據59DMLDML基礎基礎-SELECT-SELECT與與IN

27、SERTINSERT、UPDATEUPDATE、DELETEDELETE示例 將每個部門工資低于平均部門平均工資的員工工資，增加100060目錄目錄u 課程介紹u DML基礎u SQL介紹u DCL基礎u DDL基礎u TL基礎61DDLDDL基礎基礎語句CREATE 創(chuàng)建數據庫對象DROP 刪除數據庫對象TRUNCATE 刪除指定表全部數據，不可回滾ALTER 修改數據庫對象COMMENT 給表、字段添加備注RENAME重命名數據庫對象分類表相關視圖相關同義詞相關62DDLDDL基礎基礎表標準創(chuàng)建表語句從查詢創(chuàng)建表修改表結構刪除表數據刪除表結構63DDLDDL基礎基礎- -表表- -標準建語

28、句標準建語句用途在數據庫中建立表語法示例64DDLDDL基礎基礎- -表表- -標準建語句標準建語句示例創(chuàng)建客戶表并設置CUSTOMER_ID為主鍵65DDLDDL基礎基礎- -表表- -標準建語句標準建語句示例創(chuàng)建部門表departments并設置DEPARTMENT_ID為主鍵創(chuàng)建員工表eployees并設置EMPLOYEE_NUMBER為主鍵，DEPARTMENT_ID為外鍵，引用部門表主鍵DEPARTMENT_ID66DDLDDL基礎基礎- -表表- -從查詢創(chuàng)建表從查詢創(chuàng)建表用途在數據庫中建立表，并將相應查詢返回的數據插入至新建表中。但查詢相關表的索引、主鍵等不會復制到新建表中語法C

29、REATE table table_name asselect query示例 備份整個suppliers表數據到新建表supplier_20100303CREATE table suppliers_20100303asselect * from suppliers67DDLDDL基礎基礎- -表表- -從查詢創(chuàng)建表從查詢創(chuàng)建表示例單表查詢，按指定查詢列創(chuàng)建表suppliers，并填充數據多表關聯，按指定查詢列創(chuàng)建表suppliers，并填充數據創(chuàng)建表單但不填充數據，再用腳本插入數據（數據處理時常用）68DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表增加備注語法

30、示例69DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表字段增加備注語法示例70DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表重命名語法示例71DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表添加字段語法示例72DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表修改字段語法示例73DDLDDL基礎基礎- -表表- -修改表結構修改表結構用途在數據庫中對已有表刪除字段語法示例74DDLDDL基礎基礎- -表表- -刪除表數據刪除表數據用途在數據庫中刪除指定表數據(全部數據，不可

31、回滾)語法TRUNCATE TABLE table_name;示例 TRUNCATE TABLE supplier;75DDLDDL基礎基礎- -表表- -刪除表結構刪除表結構用途在數據庫中刪除指定表結構語法DROP TABLE table_name;示例 DROP TABLE supplier;76DDLDDL基礎基礎視圖創(chuàng)建視圖修改視圖刪除視圖77DDLDDL基礎基礎- -視圖視圖- -創(chuàng)建視圖創(chuàng)建視圖用途在數據庫中創(chuàng)建視圖語法示例 查詢數據78DDLDDL基礎基礎- -視圖視圖- -修改視圖修改視圖用途在數據庫中修改或者創(chuàng)建視圖語法示例 79DDLDDL基礎基礎- -視圖視圖- -刪除視

32、圖刪除視圖用途在數據庫中刪除指定視圖語法DROP VIEW view_name;示例 DROP VIEW sup_orders_v;80DDLDDL基礎基礎同義詞創(chuàng)建同義詞修改同義詞刪除同義詞同義詞概念同義詞本身也是數據庫對象，一般用于映射別的用戶的對象，使用同義詞可以屏蔽具體被映射對象的所屬用戶和真實對象名稱同義詞和視圖類似，是一種映射，視圖只能映射查詢SQL，同義詞可以映射表、視圖、存儲過程、函數 、包、序列等81DDLDDL基礎基礎- -同義詞同義詞- -創(chuàng)建同義詞創(chuàng)建同義詞用途在數據庫中使用別的用戶(schema)的數據庫對象語法示例 查詢數據：82DDLDDL基礎基礎- -視圖視圖- -修改同義詞修改同義詞用途在數據庫中修改或者創(chuàng)建視圖語法示例 83DDLDDL基礎基礎- -表表- -刪除同義詞刪除同義詞用途在數據庫中刪除指定同義詞語法DROP SY