1、Oracle SQL 根底培訓(xùn)BI運用部 高棉泉2021.03目錄 課程引見 DDL根底 SQL引見 DML根底 DCL根底 TL根底課程引見教程概述本教程假設(shè)讀者已了解關(guān)系型數(shù)據(jù)庫根本原理，明白表、視圖、主鍵、索引、外鍵、約束、關(guān)聯(lián)等根本概念本教程定位是Oracle SQL簡明、適用教程，偏向于SQL開發(fā)，假設(shè)進一步學(xué)習(xí)Oracle數(shù)據(jù)庫設(shè)計，請參閱其他教程或書籍文檔課程目的是完成課程后可進展工程中大部分Oracle SQL開發(fā)適用對象學(xué)習(xí)過規(guī)范SQL，未運用過Oracle數(shù)據(jù)庫的讀者適用過SQL Server或其他數(shù)據(jù)庫，未運用過Oracle數(shù)據(jù)庫的讀者目錄 課程引見 DDL根底 SQL引

2、見 DML根底 DCL根底 TL根底SQL引見概述SQL是Structured Query Language簡寫SQL用于組織、訪問和處置數(shù)據(jù)庫中的數(shù)據(jù)規(guī)范的計算機言語SQL具有第四代言語的特征，程序關(guān)注要通知DBMS要得做什么操作，及其相應(yīng)條件，至于如何得到數(shù)據(jù)，如何運用索引，如何關(guān)聯(lián)多表，由DBMS完成分類Data Definition Language(DDL數(shù)據(jù)定義言語)Data Manipulation Language(DML數(shù)據(jù)操作言語)Data Control Language(DCL數(shù)據(jù)控制言語)Transaction Language(TL數(shù)據(jù)事務(wù)言語)目錄 課程引見 DD

3、L根底 SQL引見 DML根底 DCL根底 TL根底DDL根底語句CREATE 創(chuàng)建數(shù)據(jù)庫對象DROP 刪除數(shù)據(jù)庫對象TRUNCATE 刪除指定表全部數(shù)據(jù)，不可回滾ALTER 修正數(shù)據(jù)庫對象COMMENT 給表、字段添加備注RENAME重命名數(shù)據(jù)庫對象分類表相關(guān)視圖相關(guān)同義詞相關(guān)DDL根底表規(guī)范創(chuàng)建表語句從查詢創(chuàng)建表修正表構(gòu)造刪除表數(shù)據(jù)刪除表構(gòu)造DDL根底-表-規(guī)范建語句用途在數(shù)據(jù)庫中建立表語法例如DDL根底-表-規(guī)范建語句例如創(chuàng)建客戶表并設(shè)置CUSTOMER_ID為主鍵DDL根底-表-規(guī)范建語句例如創(chuàng)建部門表departments并設(shè)置DEPARTMENT_ID為主鍵創(chuàng)建員工表eployee

4、s并設(shè)置EMPLOYEE_NUMBER為主鍵，DEPARTMENT_ID為外鍵，援用部門表主鍵DEPARTMENT_IDDDL根底-表-從查詢創(chuàng)建表用途在數(shù)據(jù)庫中建立表，并將相應(yīng)查詢前往的數(shù)據(jù)插入至新建表中。但查詢相關(guān)表的索引、主鍵等不會復(fù)制到新建表中語法CREATE table table_name asselect query例如備份整個suppliers表數(shù)據(jù)到新建表supplier_20210303CREATE table suppliers_20210303asselect * from suppliersDDL根底-表-從查詢創(chuàng)建表例如單表查詢，按指定查詢列創(chuàng)建表suppliers

5、，并填充數(shù)據(jù)多表關(guān)聯(lián)，按指定查詢列創(chuàng)建表suppliers，并填充數(shù)據(jù)創(chuàng)建表單但不填充數(shù)據(jù)，再用腳本插入數(shù)據(jù)數(shù)據(jù)處置時常用DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表添加備注語法例如DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表字段添加備注語法例如DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表重命名語法例如DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表添加字段語法例如DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表修正字段語法例如DDL根底-表-修正表構(gòu)造用途在數(shù)據(jù)庫中對已有表刪除字段語法例如DDL根底-表-刪除表數(shù)據(jù)用途在數(shù)據(jù)庫中刪除指定表數(shù)據(jù)(全部數(shù)據(jù)，不可回滾)語法TRUNCA

6、TE TABLE table_name;例如 TRUNCATE TABLE supplier;DDL根底-表-刪除表構(gòu)造用途在數(shù)據(jù)庫中刪除指定表構(gòu)造語法DROP TABLE table_name;例如 DROP TABLE supplier;DDL根底視圖創(chuàng)建視圖修正視圖刪除視圖DDL根底-視圖-創(chuàng)建視圖用途在數(shù)據(jù)庫中創(chuàng)建視圖語法例如 查詢數(shù)據(jù)DDL根底-視圖-修正視圖用途在數(shù)據(jù)庫中修正或者創(chuàng)建視圖語法例如 DDL根底-視圖-刪除視圖用途在數(shù)據(jù)庫中刪除指定視圖語法DROP VIEW view_name;例如 DROP VIEW sup_orders_v;DDL根底同義詞創(chuàng)建同義詞修正同義詞刪除

7、同義詞同義詞概念同義詞是一個數(shù)據(jù)庫對象的別名，和視圖類似，是一種映射關(guān)系視圖只能映射查詢，同義詞可以映射表、視圖、存儲過程、函數(shù) 、包、序列等同義詞本身也是數(shù)據(jù)庫對象，普通用于運用別的用戶的對象，運用同義詞可以屏蔽詳細(xì)被運用對象的所屬用戶和真實稱號DDL根底-同義詞-創(chuàng)建同義詞用途在數(shù)據(jù)庫中創(chuàng)建別的用戶(schema)的數(shù)據(jù)庫對象語法例如 查詢數(shù)據(jù)DDL根底-視圖-修正同義詞用途在數(shù)據(jù)庫中修正或者創(chuàng)建視圖語法例如 DDL根底-表-刪除同義詞用途在數(shù)據(jù)庫中刪除指定同義詞語法DROP SYNONYM synonym_name;例如 DROP SYNONYM fnd_user;目錄 課程引見 DDL

8、根底 SQL引見 DML根底 DCL根底 TL根底DCL根底權(quán)限清單授權(quán)語句GrantRevokeDCL根底-授權(quán)語句-Grant語法grant privileges on object to users;例如授指定表一個權(quán)限，給指定用戶grant select on suppliers to smithj;授指定表多個權(quán)限，給指定用戶grant select,insert,update,delte on suppliers to smithj;授指定表全部權(quán)限，給指定用戶grant all on suppliers to smithj;授權(quán)指定表指定權(quán)限，給一切用戶grant select

9、on suppliers to public;DCL根底-授權(quán)語句-Revoke語法revoke privileges on object from users;例如從指定用戶收回指定表一個權(quán)限r(nóng)evoke select on suppliers from smithj;從指定用戶收回指定表多個權(quán)限r(nóng)evoke select,insert,update,delte on suppliers from smithj;從指定用戶收回指定表全部權(quán)限r(nóng)evoke all on suppliers from smithj;從一切用戶收回指定表指定權(quán)限r(nóng)evoke select on suppliers

10、from public;目錄 課程引見 DDL根底 SQL引見 DML根底 DCL根底 TL根底DML根底算術(shù)運算符關(guān)系運算符SELECT語句 :查詢數(shù)據(jù)集合運算符:復(fù)合查詢SUBQUERY子查詢LEVEL、PRIOR與CONNECT BY START WITHROWNUM與分頁查詢INSERT語句 :插入數(shù)據(jù)UDATE語句 :更新數(shù)據(jù)DELETE語句 :刪除數(shù)據(jù)SELECT與INSERT、UPDATE、DELETEDML根底算術(shù)運算符號+ 加 1+1- 減 2-1* 乘 2*3/ 除 4/2| 字符銜接 | 2021DML根底關(guān)系運算符號= 等于 ，1=1 不等于， 21 大于，21 小于，

11、 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 是會聚函數(shù)，通常為SUM, COUNT, MIN, MAX,AVG例如查詢總工資大于35000的部門DML根底-SELECT

12、語句- GROUP BY從句例如查詢員工個數(shù)大于5的部門及其員工個數(shù)查詢有最低工資為10000的部門及其最低員工工資查詢有最低工資為10000的部門及其最低員工工資DML根底-SELECT語句- GROUP BY從句直接聚合COUNT、SUM、MIN、MAX、AVG可以不和GROUP BY一同運用，直接對查詢數(shù)據(jù)直接進展聚合，但SELECT不能包含其他列例如查詢員工總數(shù)Select Count(*) From employees查詢員工平均工資Select avg(salary) From employees查詢員工最高工資Select max(salary) From employees查詢

13、員工最低工資Select min(salary) From employees查詢員工工資總數(shù)Select sum(salary) From employeesDML根底-JOINS多表關(guān)聯(lián)用途JOIN將多表數(shù)據(jù)關(guān)聯(lián)起來，JOIN是關(guān)系型數(shù)據(jù)庫的魅力所在語法SELECT columns FROM table1,table2,table3 WHERE predicates;只需把幾個表放于FROM子句后，這些表就會自動關(guān)聯(lián)起來分類CROSS-JOIN:不在WHERE從句指定任何關(guān)聯(lián)條件，前往的記錄數(shù)為各個表記錄數(shù)的笛卡爾乘積。INNER JOIN:在WHERE從句指定表之間關(guān)聯(lián)條件，前往符合關(guān)聯(lián)

14、條件的指定的各個表記錄數(shù)及其各列值;OUTER JOIN: 在WHERE從句指定表之間關(guān)聯(lián)條件，前往符合關(guān)聯(lián)條件的指定的各個表記錄數(shù)及各列值，不符合關(guān)聯(lián)條件的“外連表列前往空;DML根底-JOINS多表關(guān)聯(lián)-兩表例如Suppliers供應(yīng)商表Orders采購訂單表;DML根底-JOINS多表關(guān)聯(lián)-兩表例如INNER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id;DML根底-JOINS多表關(guān)聯(lián)-兩

15、表例如OUTER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id (+);DML根底-JOINS多表關(guān)聯(lián)-三表例如Suppliers供應(yīng)商表Orders采購訂單表員工表DML根底-JOINS多表關(guān)聯(lián)-三表例如INNER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_date, od.order_date ,em.employee_nam

16、eFROM suppliers sp, orders od, employees emWHERE sp.supplier_id = od.supplier_idAnd od.employee_number = em.employee_number;DML根底-JOINS多表關(guān)聯(lián)-三表例如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

17、_id = od.supplier_id(+)And od.employee_number = em.employee_number(+);DML根底集合運算符:復(fù)合查詢UNIONUNION ALLINTERSECTMINUSDML根底-復(fù)合查詢-UNION用途將多個SELECT語句的結(jié)果集加起來，去掉反復(fù)的數(shù)據(jù)(集合并運算)，限制：參與UNION的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;例如selec

18、t supplier_idfrom suppliersUNIONselect supplier_idfrom orders;DML根底-復(fù)合查詢-UNION ALL用途將多個SELECT語句的結(jié)果集加起來，不去掉反復(fù)的數(shù)據(jù)限制：參與UNION ALL的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesUNION ALLselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersUNION ALLselect sup

19、plier_idfrom orders;DML根底-復(fù)合查詢-INTERSECT用途求多個SELECT的交集(集合交運算)限制：參與INTERSECT的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesINTERSECTselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersINTERSECTselect supplier_idfrom orders;DML根底-復(fù)合查詢-MINUS用途求在第一個SELECT，

20、不在第二個SELECT的數(shù)據(jù)(集合減運算)限制：參與MINUS的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesMINUSselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersMINUSselect supplier_idfrom orders;DML根底-SUBQUERY子查詢概念在一個主SELECT、INSERT、UPDATE、DELETE語句中，嵌套的、用括號括起來的SELECT語句，稱為子查詢語法相

21、關(guān)子查詢:子查詢中援用外部表非相關(guān)子查詢:子查詢中不援用外部表例如工資大于工號為1002員工工資的員工信息(不相關(guān)子查詢)Select * From employees em Where em.salary (Select salary From employees pl Where pl.employee_number = 1002);DML根底-SUBQUERY子查詢例如查詢有下采購訂單的員工(不相關(guān)子查詢)Select * From employees em, (Select Distinct employee_number From orders) v Where em.employe

22、e_number = v.employee_number查詢一切部門及其部門員工工資總和(相關(guān)子查詢)Select dp.department_id, dp.department_name, (Select Sum(salary) From employees pl Where pl.department_id = dp.department_id) total_salary From departments dpDML根底-SUBQUERY子查詢例如查詢有下采購訂單的員工(相關(guān)子查詢)Select * From employees em Where em.employee_number In

23、 (Select Distinct employee_number From orders)查詢有下采購訂單的員工(相關(guān)子查詢)Select * From employees em Where Exists (Select 1 From orders od Where od.employee_number = em.employee_number)DML根底-level與CONNECT BYSTART WITH背景運用系統(tǒng)長通常有類似菜單、產(chǎn)品類別等有上下級關(guān)系的樹形構(gòu)造數(shù)據(jù)通常會設(shè)計為ID、PARENT_ID構(gòu)造DML根底-level與CONNECT BYSTART WITH原理Oracle

24、提供CONNECT BYSTART WITH用于類似構(gòu)造查詢提供關(guān)鍵字level在CONNECT BYSTART WITH查詢前往數(shù)據(jù)所在樹形構(gòu)造層次，level值越大表示層級越深例如查詢“訂單管理及其下屬菜單，按樹形構(gòu)造展現(xiàn)，運用lpad函數(shù)輔助效果更明顯(縮進)，lpad函數(shù)參考后續(xù)教程引見DML根底-ROWNUM與分頁查詢背景運用系統(tǒng)在數(shù)據(jù)量大的時候，通常需求將數(shù)據(jù)分頁顯示DML根底-ROWNUM與分頁查詢原理Oracle提供關(guān)鍵字rownum，在SELECT語句查詢前往數(shù)據(jù)中表示是第幾行例如查詢“訂單管理及其下屬菜單，按樹形構(gòu)造展現(xiàn)，運用lpad函數(shù)輔助效果更明顯，并讀取第1至第7行數(shù)

25、據(jù)，選定部分要排好序DML根底INSERT語句根本INSERT語句INSERT與SELECTDML根底-INSERT-根本INSERT語句用途將數(shù)據(jù)插入到指定表中語法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);DML根底-INSERT-IN

26、SERT與SELECT用途將數(shù)據(jù)插入到指定表中語法INSERT INTO table(column-1, column-2, . column-n)SELECTColumn-1,column-2 . column-nFROM TablesWhERE predicates;例如將所在城市為Newark的客戶生成供應(yīng)商， 其中供應(yīng)商supplier_id對應(yīng)為客戶account_no， supplier_name對應(yīng)為customer_nameINSERT INTO suppliers(supplier_id, supplier_name)SELECT account_no, customer_n

27、ameFROM customersWHERE city = Newark;DML根底-INSERT-INSERT與SELECT例如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);DML根底UPDATE語句根本UPDATE語句DML根底-UPDATE語句

28、用途UPDATE用于更新指定表符合條件的數(shù)據(jù);語法UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;例如將稱號為Smith的供應(yīng)商稱號更新為HP，同時在備注記錄舊稱號UPDATE suppliers SET supplier_name = HP,commets= Smith WHERE supplier_name = Smith;DML根底-UPDATE語句用途更新根據(jù)客戶消費的供應(yīng)商(客戶account_no生成為supplier_id)的備注為相應(yīng)客戶的備注Update suppliers sp Set spmets = (Select ccmets From customers cc Where cc.account_no = sp.supplier_id) Where Exists (Select ccmets From customers cc Where cc.acco