Oracle进阶培训ppt课件

1、360。企业管理信息化处理方案提供商赛意信息科技 白金級协作同伴 课程主题讲师：周梦荣 日期：2021.3.2022/7/11222 Oracle数据库根本访问语句 PL/SQL编程根底 Oracle数据库高级查询 目录.2022/7/11333SQL语句分类数据操作语句 Data Manipulation Language)： DML语句包括：select 、insert、delete、update 数据定义语句 Data Definition Language) ： DDL语句包括：create、alter、drop 、truncate 事务控制语句Transaction Control

2、Language)： TCL语句包括：commit、rollback、savepoint 数据控制语句Data Control Language)： DCL语句包括：grant、revokeORACLE数据库根本访问语句.2022/7/11444DML语句：select 、insert、delete、update DML语句用途及根底语法：SELECT语句用途：用于查询表或视图的数据；SELECT语句语法：SELECT columns FROM tables WHERE predicates;INSERT语句用途：将数据插入到指定表中；INSERT语句语法：INSERT INTO table

3、(column-1, column-2, . column-n) VALUES (value-1, value-2, . value-n);ORACLE数据库根本访问语句.2022/7/11555DML语句：select 、insert、delete、update DML语句用途及根底语法：UPDATE语句用途：用于更新指定表符合条件的数据；UPDATE语句语法： UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;DELETE语句用途：用于删除指定表符合条件的数据；DELETE语句语法： DELETE FRO

4、M tables WHERE predicates ;ORACLE数据库根本访问语句.2022/7/11666DML语句：select 、insert、delete、update 例如：在SCOTT用户进展如下操作：1、查询emp表的一切员工的一切信息；2、查询emp表的一切员工的工号、姓名、根本薪水；3、查询工号为7839的员工的工号、姓名、根本薪水；4、插入一个员工信息：工号:8000,姓名：SIE，职位：SALESMAN，部门：30；5、将工号为8000的员工的上级指点修正为7920；6、删除工号为8000的员工。ORACLE数据库根本访问语句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用户进展如下操作：7、查询20部门的一切员工信息：部门称号，员工姓名，根本薪水；8、统计20部门的员工人数及平均根本薪水；9、查询不含员工的部门信息：部门称号；ORACLE数据库根本访问语句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 总结select语句是难点，需求多练习，常用select语句的类型有：单表查询、多表查询、子查询、集合操作、树构造查询、聚合函数、分析函数、自定义函数；Insert、delete、update三类改动要想存入库中必需求运用commit语句，放弃改动

8、时要运用rollback语句； Insert时留意数据类型、独一约束、非空约束等；delete、update运用时，一定要确保WHERE条件的正确性；留意NULL值，判别空用IS NULL ，判别不空用 IS NOT NULL。ORACLE数据库根本访问语句.2022/7/11999DDL语句：create、alter、drop 、truncate DDL语句用途及语法create语句用途：创建数据库对象，如表、视图create语句语法:创建表根本语法：create table table_name (column_a datatype null/not null, column_b data

9、type null/not null, );根据查询语句创建表根本语法： CREATE table table_name as select query；创建视图根本语法： create or replace view view_name as select columns from table where predicates;ORACLE数据库根本访问语句.2022/7/11101010DDL语句：create、alter、drop 、truncate DDL语句用途及语法alter语句用途：修正数据库对象，普通用于修正表构造alter语句语法:修正表构造根本语法： 添加字段：alter

10、table table_name add 删除字段：alter table table_name drop cloumn . 重命名字段： alter table table_name rename column to 修正表字段(数据类型、NOT NULL约束) alter table table_name modify 添加约束： alter table table_name add constraint ORACLE数据库根本访问语句.2022/7/11111111DDL语句：create、alter、drop 、truncate DDL语句用途及语法drop语句用途：删除数据库对象。d

11、rop语句语法: drop 对象类型 对象称号;ORACLE数据库根本访问语句DDL语句用途及语法truncate语句用途：删除表中一切数据，并释放所占用的空间。运用truncate表删除数据时不需求提交，也不能回滚。删除表中一切数据时运用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 备注内容; 给表中某个字段加备注语法： comment on column table.column_name is 备注内容;RENAME语句用途：重命名数据库对象称号，常用于重命名表名RENAME语句语法: 重命名表名语法： RENAME old_name TO new_name ORACLE数据库根本访问语句.2022/7/11131313DDL语句：create、alter、drop 、truncate 例如：在SCOTT用户下进展如下操作：1、运用根本语法创建 sie_emp表ORACLE数据库根本访问语句字段名数据类型Emp_noVARCHAR2(30)

13、Emp_nameVARCHAR2(30) sexCHAR(1)Dept_noVARCHAR2(30) Base_salaryNUMBER-创建表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用户下进展如下操作：2、创建 sie_dept表，采用查询scott.dept表的方式，创建

14、时不拷贝scott.dept表中的数据。ORACLE数据库根本访问语句字段名数据类型dept_nonumber(2) dept_nameVARCHAR2(14) -创建表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用户下进展如下操作：3、修正sie_dept表： a、给字段dept_no添加主键约束； b、字段dept_no数据类型修正为V

15、ARCHAR2(30)； c、字段dept_name修正为NOT NULL约束； d、删除字段dept_name上的NOT NULL约束； e、字段dept_name上添加独一约束；ORACLE数据库根本访问语句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用户下进展如下操作：4、在sie_emp表的emp_no字段上创建独一索引；5、修正sie_emp表： a、字段emp_no添加主键约束； b、添加字段职位job、上级指点(mgr); c、字段sex更名为gender; e、字段salary添加check约束，salary=1500 f、字段dept_no添加

17、外键，参考表SIE_DEPT ORACLE数据库根本访问语句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用户下进展如下操作：6、根本查询员工编号、姓名、部门称号的查询语句， 创建视图sie_emp_v；7、删除sie_emp表dept_no字段上的外键约束；8、删除sie_emp表job字段；9、删除视图sie_emp_v;10、删除表sie_dept;

19、ORACLE数据库根本访问语句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 总结DDL语句会隐式提交，本质是先COMMIT，再DDL语句；创建主键约束、独一约束时会默许创建独一索引；主键约束的字段或字段组合不允许NULL值；独一约束的字段允许NULL值；表中新增字段会出如今表列的最后面；实践开发中普通不创建CHECK、FOREIGN KEY约束；创建表时业务中常为NULL值的列普通放在最后；索引并不是越多越好，索引即有优点又有缺陷；养成好的命名习惯、养成运用别名的习惯。ORACLE数据库根本访问语句.2022/7/11191919TCL语句：commit、rollback、savepointTCL语句用途及语法COMMIT语句用途：提交事物。在一系列

21、DML后，向数据库提交事务，在执行COMMIT前一切事务按执行先后顺序可见 ，执行COMMIT后，一切DML操作最终效果才会生效并坚持至数据库中。COMMIT语句语法：commit;ORACLE数据库根本访问语句.2022/7/11202020TCL语句：commit、rollback、savepointTCL语句用途及语法ROLLBACK语句用途：回滚事物。显式回滚数据库事务，一切DML操作都回滚至最近一次commit后。ROLLBACK语句语法： ROLLBACK;ORACLE数据库根本访问语句.2022/7/11212121TCL语句：commit、rollback、savepointT

22、CL语句用途及语法savepoint语句用途：实现部分回滚，回滚至某个设置点。savepoint语句语法： savepoint A; -设置回滚点 . rollback to A;-回滚至设置点ORACLE数据库根本访问语句.2022/7/11222222TCL语句：commit、rollback、savepoint总结改动数据库中的数据后，普通运用COMMIT显示提交， 运用ROLLBACK显示回滚；留意DDL语句会隐式提交COMMI；TCL语句频繁出现会降低效率；一个事务的开场标志是INSERT、UPDATE、DELETE 语句的出现；一个事务的终了标志是COMMIT、 ROLLBACK的

23、出现；ORACLE数据库根本访问语句.2022/7/11232323DCL语句：grant、revokegrant授予权限，revoke收回权限根底权限清单ORACLE数据库根本访问语句.2022/7/11242424DCL语句：grant、revoke授予权限grant语法grant privileges on object to users;例如： 创建sie用户并在scott用户下进展如下操作：授指定表一个权限，给指定用户grant select on emp to sie;授指定表多个权限，给指定用户grant select,insert,update on emp to sie;授指

24、定表全部权限，给指定用户grant all on emp to sie;授权指定表指定权限，给一切用户grant select on emp to public;ORACLE数据库根本访问语句.2022/7/11252525DCL语句：grant、revoke收回权限revoke语法revoke privileges on object from users;例如：scott用户下进展如下操作：从指定用户收回指定表一个权限revoke select on emp from sie;从指定用户收回指定表多个权限revoke insert,update,delete on emp from sie

25、;从指定用户收回指定表全部权限revoke all on emp from sie;从一切用户收回指定表指定权限revoke select on emp from public;ORACLE数据库根本访问语句.2022/7/11262626DCL语句：grant、revoke总结新创建用户没有任何权限，登录数据库的权限也没有；创建一个新的用户至少授予的权限： connect、resource、unlimited tablespaceORACLE数据库根本访问语句.2022/7/11272727 Oracle数据库根本访问语句 PL/SQL 编程根底 Oracle数据库高级查询 目录.2022/

26、7/11282828多表衔接的类型与实现内衔接 等值内衔接 inter join on 不等值内衔接 自然衔接 natural join外衔接 左外衔接 left outer join on 右外衔接 right outer join on 全外衔接 fullouter join on交叉衔接 cross onORACLE数据库高级查询.2022/7/11292929内衔接 等值衔接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注：同名字段前不能加表名进展限制ORACLE数据库高级查询.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数据库高级查询.2022/7/11313131交叉衔接select * from sie_emp emp , sie_dept dept； 即笛卡尔积，行数为左边的行数乘以右边的行数。ORACLE数据库高级查询.2022/7/11323232子查询概念在一个主SELECT、INSERT、UPDATE、DELETE语句中，嵌套的、用括号括起来的SELECT语句，称为子查询语法相关子查询:子查询中援用外部表非相关子查询:子查询中不援用外部表例如工资大于工号为1002员工工资的员工信息(不相关子查询)Select *From emp

29、loyees emWhere em.salary (Select salary From employees pl Where pl.employee_number = 1002);ORACLE数据库高级查询.2022/7/11333333子查询例如查询有下采购订单的员工(不相关子查询) Select * From employees em, (Select Distinct employee_number From orders) vWhere em.employee_number = v.employee_number查询有下采购订单的员工(相关子查询)Select * From empl

30、oyees emWhere 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_number)ORACLE数据库高级查询.2022/7/11343434子查询not EXISTS 用途EXISTS 判别子查询能否有数据前往，有那么为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数据库高级查询.2022/7/11353535子查询Exits 和 IN 的区别Exists 是存在，条件就判别为trueIn 是需求遍历一切子查询的值普通来说，需求子查询的值比较少时，差别不大，但是，对于数据量大的情况，根本都要求用ExistsORACLE数据库高级查询.2022/7/11363636集合运算集合运算符:复合查询UNIONUNION ALLINTERSECTMINUSORAC

33、LE数据库高级查询.2022/7/11373737union用途将多个SELECT语句的结果集加起来，去掉反复的数据(集合并运算)，限制：参与UNION的每个SELECT语句的相应列必需是一样数据类型语法select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersUNIONselect supplier_idfrom orders;ORACLE数据库高级查询.2022/7/11383838union al

34、l用途将多个SELECT语句的结果集加起来，不去掉反复的数据限制：参与UNION ALL的每个SELECT语句的相应列必需是一样数据类型语法select field1, field2, . field_nfrom tablesUNION ALLselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersUNION ALLselect supplier_idfrom orders;ORACLE数据库高级查询.2022/7/11393939INTERSECT用途求多个SELECT的交集(集合交运算)限制

35、：参与INTERSECT的每个SELECT语句的相应列必需是一样数据类型语法select field1, field2, . field_nfrom tablesINTERSECTselect field1, field2, . field_nfrom tables;例如select supplier_idfrom suppliersINTERSECTselect supplier_idfrom orders;ORACLE数据库高级查询.2022/7/11404040minus用途求在第一个SELECT，不在第二个SELECT的数据(集合减运算)限制：参与MINUS的每个SELECT语句的相应列必需是一样数据类型语法select field1, field2, . field_nfrom tablesMINUSselect field1, fiel