Oracle進(jìn)階培訓(xùn)ppt課件

1、360。企業(yè)管理信息化處理方案提供商賽意信息科技 白金級協(xié)作同伴 課程主題講師：周夢榮 日期：2021.3.2022/7/11222 Oracle數(shù)據(jù)庫根本訪問語句 PL/SQL編程根底 Oracle數(shù)據(jù)庫高級查詢 目錄.2022/7/11333SQL語句分類數(shù)據(jù)操作語句 Data Manipulation Language)： DML語句包括：select 、insert、delete、update 數(shù)據(jù)定義語句 Data Definition Language) ： DDL語句包括：create、alter、drop 、truncate 事務(wù)控制語句Transaction Control

2、Language)： TCL語句包括：commit、rollback、savepoint 數(shù)據(jù)控制語句Data Control Language)： DCL語句包括：grant、revokeORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11444DML語句：select 、insert、delete、update DML語句用途及根底語法：SELECT語句用途：用于查詢表或視圖的數(shù)據(jù)；SELECT語句語法：SELECT columns FROM tables WHERE predicates;INSERT語句用途：將數(shù)據(jù)插入到指定表中；INSERT語句語法：INSERT INTO table

3、(column-1, column-2, . column-n) VALUES (value-1, value-2, . value-n);ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11555DML語句：select 、insert、delete、update DML語句用途及根底語法：UPDATE語句用途：用于更新指定表符合條件的數(shù)據(jù)；UPDATE語句語法： UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;DELETE語句用途：用于刪除指定表符合條件的數(shù)據(jù)；DELETE語句語法： DELETE FRO

4、M tables WHERE predicates ;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11666DML語句：select 、insert、delete、update 例如：在SCOTT用戶進(jìn)展如下操作：1、查詢emp表的一切員工的一切信息；2、查詢emp表的一切員工的工號、姓名、根本薪水；3、查詢工號為7839的員工的工號、姓名、根本薪水；4、插入一個員工信息：工號:8000,姓名：SIE，職位：SALESMAN，部門：30；5、將工號為8000的員工的上級指點修正為7920；6、刪除工號為8000的員工。ORACLE數(shù)據(jù)庫根本訪問語句SELECT * FROM emp e;SE

5、LECT empno,ename,sal FROM emp;SELECT e.empno,e.ename,e.sal FROM emp e WHERE e.empno =7839;INSERT INTO EMP(empno,ename,job,deptno)VALUES(8000,SIE,SALESMAN,30);COMMIT;UPDATE emp SET MGR = 7920WHERE empno = 8000;COMMIT;DELETE FROM emp WHERE empno = 8000;COMMIT;.2022/7/11777DML語句：select 、insert、delete、u

6、pdate 例如：在SCOTT用戶進(jìn)展如下操作：7、查詢20部門的一切員工信息：部門稱號，員工姓名，根本薪水；8、統(tǒng)計20部門的員工人數(shù)及平均根本薪水；9、查詢不含員工的部門信息：部門稱號；ORACLE數(shù)據(jù)庫根本訪問語句SELECT D.DNAME, E.ENAME, E.SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 20;SELECT D.DNAME, COUNT(*) emp_qty, AVG(SAL) avg_sal FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO

7、AND D.DEPTNO = 20 GROUP BY d.dname; SELECT D.DNAME FROM DEPT D WHERE NOT EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO);.2022/7/11888DML語句：select 、insert、delete、update 總結(jié)select語句是難點，需求多練習(xí)，常用select語句的類型有：單表查詢、多表查詢、子查詢、集合操作、樹構(gòu)造查詢、聚合函數(shù)、分析函數(shù)、自定義函數(shù)；Insert、delete、update三類改動要想存入庫中必需求運(yùn)用commit語句，放棄改動

8、時要運(yùn)用rollback語句； Insert時留意數(shù)據(jù)類型、獨一約束、非空約束等；delete、update運(yùn)用時，一定要確保WHERE條件的正確性；留意NULL值，判別空用IS NULL ，判別不空用 IS NOT NULL。ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11999DDL語句：create、alter、drop 、truncate DDL語句用途及語法create語句用途：創(chuàng)建數(shù)據(jù)庫對象，如表、視圖create語句語法:創(chuàng)建表根本語法：create table table_name (column_a datatype null/not null, column_b data

9、type null/not null, );根據(jù)查詢語句創(chuàng)建表根本語法： CREATE table table_name as select query；創(chuàng)建視圖根本語法： create or replace view view_name as select columns from table where predicates;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11101010DDL語句：create、alter、drop 、truncate DDL語句用途及語法alter語句用途：修正數(shù)據(jù)庫對象，普通用于修正表構(gòu)造alter語句語法:修正表構(gòu)造根本語法： 添加字段：alter

10、table table_name add 刪除字段：alter table table_name drop cloumn . 重命名字段： alter table table_name rename column to 修正表字段(數(shù)據(jù)類型、NOT NULL約束) alter table table_name modify 添加約束： alter table table_name add constraint ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11111111DDL語句：create、alter、drop 、truncate DDL語句用途及語法drop語句用途：刪除數(shù)據(jù)庫對象。d

11、rop語句語法: drop 對象類型 對象稱號;ORACLE數(shù)據(jù)庫根本訪問語句DDL語句用途及語法truncate語句用途：刪除表中一切數(shù)據(jù)，并釋放所占用的空間。運(yùn)用truncate表刪除數(shù)據(jù)時不需求提交，也不能回滾。刪除表中一切數(shù)據(jù)時運(yùn)用truncate比delete高效。truncate語句語法:truncate table table_name; .2022/7/11121212DDL語句：create、alter、drop 、truncate DDL語句用途及語法COMMENT語句用途：給表或表的字段加備注信息COMMENT語句語法:給表加備注語法：comment on table t

12、able_name is 備注內(nèi)容; 給表中某個字段加備注語法： comment on column table.column_name is 備注內(nèi)容;RENAME語句用途：重命名數(shù)據(jù)庫對象稱號，常用于重命名表名RENAME語句語法: 重命名表名語法： RENAME old_name TO new_name ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11131313DDL語句：create、alter、drop 、truncate 例如：在SCOTT用戶下進(jìn)展如下操作：1、運(yùn)用根本語法創(chuàng)建 sie_emp表ORACLE數(shù)據(jù)庫根本訪問語句字段名數(shù)據(jù)類型Emp_noVARCHAR2(30)

13、Emp_nameVARCHAR2(30) sexCHAR(1)Dept_noVARCHAR2(30) Base_salaryNUMBER-創(chuàng)建表sie_empCREATE TABLE SIE_EMP(emp_no VARCHAR2(30),emp_name VARCHAR2(30),sex CHAR(1),dept_no VARCHAR2(30),base_salary NUMBER);.2022/7/11141414DDL語句：create、alter、drop 、truncate 例如：在SCOTT用戶下進(jìn)展如下操作：2、創(chuàng)建 sie_dept表，采用查詢scott.dept表的方式，創(chuàng)建

14、時不拷貝scott.dept表中的數(shù)據(jù)。ORACLE數(shù)據(jù)庫根本訪問語句字段名數(shù)據(jù)類型dept_nonumber(2) dept_nameVARCHAR2(14) -創(chuàng)建表sie_deptCREATE TABLE sie_dept(dept_no, dept_name )AS SELECT deptno,dname FROM dept WHERE 1=2.2022/7/11151515DDL語句：create、alter、drop 、truncate 例如：在SCOTT用戶下進(jìn)展如下操作：3、修正sie_dept表： a、給字段dept_no添加主鍵約束； b、字段dept_no數(shù)據(jù)類型修正為V

15、ARCHAR2(30)； c、字段dept_name修正為NOT NULL約束； d、刪除字段dept_name上的NOT NULL約束； e、字段dept_name上添加獨一約束；ORACLE數(shù)據(jù)庫根本訪問語句ALTER TABLE sie_dept ADD CONSTRAINT sie_dept_pk PRIMARY KEY(dept_no);ALTER TABLE sie_deptMODIFY (dept_no VARCHAR2(30);ALTER TABLE sie_dept MODIFY (dept_name NOT NULL);ALTER TABLE sie_dept MODIFY

16、 (dept_name NULL);ALTER TABLE sie_deptADD CONSTRAINT sie_dept_uq1 UNIQUE (dept_name);.2022/7/11161616DDL語句：create、alter、drop 、truncate 例如：在SCOTT用戶下進(jìn)展如下操作：4、在sie_emp表的emp_no字段上創(chuàng)建獨一索引；5、修正sie_emp表： a、字段emp_no添加主鍵約束； b、添加字段職位job、上級指點(mgr); c、字段sex更名為gender; e、字段salary添加check約束，salary=1500 f、字段dept_no添加

17、外鍵，參考表SIE_DEPT ORACLE數(shù)據(jù)庫根本訪問語句CREATE UNIQUE INDEX sie_emp_u1 ON sie_emp(emp_no);ALTER TABLE sie_empADD CONSTRAINT sie_emp_pk PRIMARY KEY(emp_no);ALTER TABLE sie_empADD (job VARCHAR2(30),mgr VARCHAR(30);ALTER TABLE sie_empRENAME COLUMN sex TO gender;ALTER TABLE sie_emp ADD CONSTRAINT sie_emp_ck1 CHEC

18、K (salary = 1500);ALTER TABLE sie_emp ADD CONSTRAINT sie_emp_fk1 FOREIGN KEY (dept_no)REFERENCES sie_dept(dept_no);.2022/7/11171717DDL語句：create、alter、drop 、truncate 例如：在SCOTT用戶下進(jìn)展如下操作：6、根本查詢員工編號、姓名、部門稱號的查詢語句， 創(chuàng)建視圖sie_emp_v；7、刪除sie_emp表dept_no字段上的外鍵約束；8、刪除sie_emp表job字段；9、刪除視圖sie_emp_v;10、刪除表sie_dept;

19、ORACLE數(shù)據(jù)庫根本訪問語句CREATE VIEW sie_emp_vASSELECT se.emp_no,se.emp_name,sd.dept_name FROM sie_emp se,sie_dept sd WHERE se.dept_no = sd.dept_noALTER TABLE sie_empDROP CONSTRAINT sie_emp_fk1;ALTER TABLE sie_emp DROP COLUMN job;DROP VIEW sie_emp_v;DROP TABLE sie_emp;.2022/7/11181818DDL語句：create、alter、drop 、

20、truncate 總結(jié)DDL語句會隱式提交，本質(zhì)是先COMMIT，再DDL語句；創(chuàng)建主鍵約束、獨一約束時會默許創(chuàng)建獨一索引；主鍵約束的字段或字段組合不允許NULL值；獨一約束的字段允許NULL值；表中新增字段會出如今表列的最后面；實踐開發(fā)中普通不創(chuàng)建CHECK、FOREIGN KEY約束；創(chuàng)建表時業(yè)務(wù)中常為NULL值的列普通放在最后；索引并不是越多越好，索引即有優(yōu)點又有缺陷；養(yǎng)成好的命名習(xí)慣、養(yǎng)成運(yùn)用別名的習(xí)慣。ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11191919TCL語句：commit、rollback、savepointTCL語句用途及語法COMMIT語句用途：提交事物。在一系列

21、DML后，向數(shù)據(jù)庫提交事務(wù)，在執(zhí)行COMMIT前一切事務(wù)按執(zhí)行先后順序可見 ，執(zhí)行COMMIT后，一切DML操作最終效果才會生效并堅持至數(shù)據(jù)庫中。COMMIT語句語法：commit;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11202020TCL語句：commit、rollback、savepointTCL語句用途及語法ROLLBACK語句用途：回滾事物。顯式回滾數(shù)據(jù)庫事務(wù)，一切DML操作都回滾至最近一次commit后。ROLLBACK語句語法： ROLLBACK;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11212121TCL語句：commit、rollback、savepointT

22、CL語句用途及語法savepoint語句用途：實現(xiàn)部分回滾，回滾至某個設(shè)置點。savepoint語句語法： savepoint A; -設(shè)置回滾點 . rollback to A;-回滾至設(shè)置點ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11222222TCL語句：commit、rollback、savepoint總結(jié)改動數(shù)據(jù)庫中的數(shù)據(jù)后，普通運(yùn)用COMMIT顯示提交， 運(yùn)用ROLLBACK顯示回滾；留意DDL語句會隱式提交COMMI；TCL語句頻繁出現(xiàn)會降低效率；一個事務(wù)的開場標(biāo)志是INSERT、UPDATE、DELETE 語句的出現(xiàn)；一個事務(wù)的終了標(biāo)志是COMMIT、 ROLLBACK的

23、出現(xiàn)；ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11232323DCL語句：grant、revokegrant授予權(quán)限，revoke收回權(quán)限根底權(quán)限清單ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11242424DCL語句：grant、revoke授予權(quán)限grant語法grant privileges on object to users;例如： 創(chuàng)建sie用戶并在scott用戶下進(jìn)展如下操作：授指定表一個權(quán)限，給指定用戶grant select on emp to sie;授指定表多個權(quán)限，給指定用戶grant select,insert,update on emp to sie;授指

24、定表全部權(quán)限，給指定用戶grant all on emp to sie;授權(quán)指定表指定權(quán)限，給一切用戶grant select on emp to public;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11252525DCL語句：grant、revoke收回權(quán)限r(nóng)evoke語法revoke privileges on object from users;例如：scott用戶下進(jìn)展如下操作：從指定用戶收回指定表一個權(quán)限r(nóng)evoke select on emp from sie;從指定用戶收回指定表多個權(quán)限r(nóng)evoke insert,update,delete on emp from sie

25、;從指定用戶收回指定表全部權(quán)限r(nóng)evoke all on emp from sie;從一切用戶收回指定表指定權(quán)限r(nóng)evoke select on emp from public;ORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11262626DCL語句：grant、revoke總結(jié)新創(chuàng)建用戶沒有任何權(quán)限，登錄數(shù)據(jù)庫的權(quán)限也沒有；創(chuàng)建一個新的用戶至少授予的權(quán)限： connect、resource、unlimited tablespaceORACLE數(shù)據(jù)庫根本訪問語句.2022/7/11272727 Oracle數(shù)據(jù)庫根本訪問語句 PL/SQL 編程根底 Oracle數(shù)據(jù)庫高級查詢 目錄.2022/

26、7/11282828多表銜接的類型與實現(xiàn)內(nèi)銜接 等值內(nèi)銜接 inter join on 不等值內(nèi)銜接 自然銜接 natural join外銜接 左外銜接 left outer join on 右外銜接 right outer join on 全外銜接 fullouter join on交叉銜接 cross onORACLE數(shù)據(jù)庫高級查詢.2022/7/11292929內(nèi)銜接 等值銜接select * from sie_emp emp,sie_dept deptwhere emp.deptid = dept.deptid 自然銜接select emp.deptid, emp.* from sie

27、_emp emp natural join sie_dept dept注：同名字段前不能加表名進(jìn)展限制ORACLE數(shù)據(jù)庫高級查詢.2022/7/11303030外銜接 左外銜接select * from sie_emp emp , sie_dept deptwhere emp.deptid = dept.deptid+ 右外銜接select * from sie_emp emp , sie_dept deptwhere emp.deptid += dept.deptid ;全外銜接select * from sie_emp emp full outer join sie_dept dept o

28、n emp.deptid = dept.deptid ;ORACLE數(shù)據(jù)庫高級查詢.2022/7/11313131交叉銜接select * from sie_emp emp , sie_dept dept； 即笛卡爾積，行數(shù)為左邊的行數(shù)乘以右邊的行數(shù)。ORACLE數(shù)據(jù)庫高級查詢.2022/7/11323232子查詢概念在一個主SELECT、INSERT、UPDATE、DELETE語句中，嵌套的、用括號括起來的SELECT語句，稱為子查詢語法相關(guān)子查詢:子查詢中援用外部表非相關(guān)子查詢:子查詢中不援用外部表例如工資大于工號為1002員工工資的員工信息(不相關(guān)子查詢)Select *From emp

29、loyees emWhere em.salary (Select salary From employees pl Where pl.employee_number = 1002);ORACLE數(shù)據(jù)庫高級查詢.2022/7/11333333子查詢例如查詢有下采購訂單的員工(不相關(guān)子查詢) Select * From employees em, (Select Distinct employee_number From orders) vWhere em.employee_number = v.employee_number查詢有下采購訂單的員工(相關(guān)子查詢)Select * From empl

30、oyees emWhere em.employee_number In (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)ORACLE數(shù)據(jù)庫高級查詢.2022/7/11343434子查詢not EXISTS 用途EXISTS 判別子查詢能否有數(shù)據(jù)前往，有那么為TURE， 否那么為FALSE，EXISTS

31、也可用于INSERT、UPDATE、DELETE;語法SELECT columns FROM tables WHERE EXISTS ( subquery );例如SELECT * FROM suppliers sp WHERE EXISTS (select 1 from orders od where od.supplier_id =sp.supplier_id );SELECT * FROM suppliers sp WHERE NOT EXISTS (select * from orders od where od.supplier_id =sp.supplier_id );DELETE

32、 FROM suppliers sp WHERE EXISTS (select * from orders od where od.supplier_id =sp.supplier_id );ORACLE數(shù)據(jù)庫高級查詢.2022/7/11353535子查詢Exits 和 IN 的區(qū)別Exists 是存在，條件就判別為trueIn 是需求遍歷一切子查詢的值普通來說，需求子查詢的值比較少時，差別不大，但是，對于數(shù)據(jù)量大的情況，根本都要求用ExistsORACLE數(shù)據(jù)庫高級查詢.2022/7/11363636集合運(yùn)算集合運(yùn)算符:復(fù)合查詢UNIONUNION ALLINTERSECTMINUSORAC

33、LE數(shù)據(jù)庫高級查詢.2022/7/11373737union用途將多個SELECT語句的結(jié)果集加起來，去掉反復(fù)的數(shù)據(jù)(集合并運(yùn)算)，限制：參與UNION的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersUNIONselect supplier_idfrom orders;ORACLE數(shù)據(jù)庫高級查詢.2022/7/11383838union al

34、l用途將多個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 supplier_idfrom orders;ORACLE數(shù)據(jù)庫高級查詢.2022/7/11393939INTERSECT用途求多個SELECT的交集(集合交運(yùn)算)限制

35、：參與INTERSECT的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesINTERSECTselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersINTERSECTselect supplier_idfrom orders;ORACLE數(shù)據(jù)庫高級查詢.2022/7/11404040minus用途求在第一個SELECT，不在第二個SELECT的數(shù)據(jù)(集合減運(yùn)算)限制：參與MINUS的每個SELECT語句的相應(yīng)列必需是一樣數(shù)據(jù)類型語法select field1, field2, . field_nfrom tablesMINUSselect field1, fiel