Oracle SQL基础培训

1、1Oracle SQL 基础培训基础培训2目录目录u 课程介绍u DDL基础u SQL介绍u DML基础u DCL基础u TL基础3课程介绍课程介绍教程概述 本教程假设读者已了解关系型数据库基本原理，明白表、视图、主键、索引、外键、约束、关联等基本概念 本教程定位是Oracle SQL简明、实用教程，偏向于SQL开发，若进一步学习Oracle数据库设计，请参阅其他教程或书籍文档 课程目标是完成课程后可进行项目中大部分Oracle SQL开发适用对象 学习过标准SQL，未使用过Oracle数据库的读者 适用过SQL Server或其他数据库，未使用过Oracle数据库的读者4目录目录u 课程介绍

2、u DDL基础u SQL介绍u DML基础u DCL基础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 DDL基础u SQL介绍u DML基础u DCL基础u TL基础7DDLDDL基础基础语句CREATE 创建数据库对象DROP 删除数据库对象TRUNCATE 删除指定表全部数据，不可回滚ALTER 修改数据库对象COMMENT 给表、字段添加备注RENAME重命名数据库对象分类表相关视图相关同义词相关8DDLDDL基础基础表标准创建表语句从查询创建表修改表结构删除表数据删除表结构9DDLDDL基础基础- -表表- -标准建语句标准建语句用途在数据库中建立表语法示例10DDLDDL基础基础- -表表- -标准建语句标准建语句示例创

4、建客户表并设置CUSTOMER_ID为主键11DDLDDL基础基础- -表表- -标准建语句标准建语句示例创建部门表departments并设置DEPARTMENT_ID为主键创建员工表eployees并设置EMPLOYEE_NUMBER为主键，DEPARTMENT_ID为外键，引用部门表主键DEPARTMENT_ID12DDLDDL基础基础- -表表- -从查询创建表从查询创建表用途在数据库中建立表，并将相应查询返回的数据插入至新建表中。但查询相关表的索引、主键等不会复制到新建表中语法CREATE table table_name asselect query示例 备份整个suppliers

5、表数据到新建表supplier_20100303CREATE table suppliers_20100303asselect * from suppliers13DDLDDL基础基础- -表表- -从查询创建表从查询创建表示例单表查询，按指定查询列创建表suppliers，并填充数据多表关联，按指定查询列创建表suppliers，并填充数据创建表单但不填充数据，再用脚本插入数据（数据处理时常用）14DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表增加备注语法示例15DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表字段增加备注语法示例

6、16DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表重命名语法示例17DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表添加字段语法示例18DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表修改字段语法示例19DDLDDL基础基础- -表表- -修改表结构修改表结构用途在数据库中对已有表删除字段语法示例20DDLDDL基础基础- -表表- -删除表数据删除表数据用途在数据库中删除指定表数据(全部数据，不可回滚)语法TRUNCATE TABLE table_name;示例 TRUNCATE TABLE supp

7、lier;21DDLDDL基础基础- -表表- -删除表结构删除表结构用途在数据库中删除指定表结构语法DROP TABLE table_name;示例 DROP TABLE supplier;22DDLDDL基础基础视图创建视图修改视图删除视图23DDLDDL基础基础- -视图视图- -创建视图创建视图用途在数据库中创建视图语法示例 查询数据24DDLDDL基础基础- -视图视图- -修改视图修改视图用途在数据库中修改或者创建视图语法示例 25DDLDDL基础基础- -视图视图- -删除视图删除视图用途在数据库中删除指定视图语法DROP VIEW view_name;示例 DROP VIEW

8、sup_orders_v;26DDLDDL基础基础同义词创建同义词修改同义词删除同义词同义词概念同义词是一个数据库对象的别名，和视图类似，是一种映射关系视图只能映射查询，同义词可以映射表、视图、存储过程、函数 、包、序列等同义词本身也是数据库对象，一般用于使用别的用户的对象，使用同义词可以屏蔽具体被使用对象的所属用户和真实名称27DDLDDL基础基础- -同义词同义词- -创建同义词创建同义词用途在数据库中创建别的用户(schema)的数据库对象语法示例 查询数据28DDLDDL基础基础- -视图视图- -修改同义词修改同义词用途在数据库中修改或者创建视图语法示例 29DDLDDL基础基础-

9、-表表- -删除同义词删除同义词用途在数据库中删除指定同义词语法DROP SYNONYM synonym_name;示例 DROP SYNONYM fnd_user;30目录目录u 课程介绍u DDL基础u SQL介绍u DML基础u DCL基础u TL基础31DCLDCL基础基础权限清单授权语句GrantRevoke32DCLDCL基础基础- -授权语句授权语句-Grant-Grant语法grant privileges on object to users;示例授指定表一个权限，给指定用户grant select on suppliers to smithj;授指定表多个权限，给指定用户g

10、rant select,insert,update,delte on suppliers to smithj;授指定表全部权限，给指定用户grant all on suppliers to smithj;授权指定表指定权限，给所有用户grant select on suppliers to public;33DCLDCL基础基础- -授权语句授权语句-Revoke-Revoke语法revoke privileges on object from users;示例从指定用户收回指定表一个权限revoke select on suppliers from smithj;从指定用户收回指定表多个权限

11、revoke select,insert,update,delte on suppliers from smithj;从指定用户收回指定表全部权限revoke all on suppliers from smithj;从所有用户收回指定表指定权限revoke select on suppliers from public;34目录目录u 课程介绍u DDL基础u SQL介绍u DML基础u DCL基础u TL基础35DMLDML基础基础 算术运算符 关系运算符 SELECT语句 :查询数据 集合运算符:复合查询 SUBQUERY子查询 LEVEL、PRIOR与CONNECT BY START

12、WITH ROWNUM与分页查询 INSERT语句 :插入数据 UDATE语句 :更新数据 DELETE语句 :删除数据 SELECT与INSERT、UPDATE、DELETE36DMLDML基础基础算术运算符号+ 加 1+1- 减 2-1* 乘 2*3/ 除 4/2| 字符连接 Beijing | 200837DMLDML基础基础关系运算符号= 等于 ，1=1 不等于， 21 大于，21 小于， 1= 大于等于， 2=1= 小于等于，11，因聚合条件不能在WHERE指明;语法SELECT column1, column2, . column_n, aggregate_function (ex

13、pression) FROM tables WHERE predicates GROUP BY column1, column2, . column_n;HAVING aggregate_condition1 . condition_n;aggregate_function 是汇聚函数，通常为SUM, COUNT, MIN, MAX,AVG示例 查询总工资大于35000的部门56DMLDML基础基础-SELECT-SELECT语句语句- GROUP BY- GROUP BY从句从句示例 查询员工个数大于5的部门及其员工个数 查询有最低工资为10000的部门及其最低员工工资 查询有最低工资为10

14、000的部门及其最低员工工资57DMLDML基础基础-SELECT-SELECT语句语句- GROUP BY- GROUP BY从句从句直接聚合 COUNT、SUM、MIN、MAX、AVG可以不和GROUP BY一起使用，直接对查询数据直接进行聚合，但SELECT不能包含其他列示例查询员工总数Select Count(*) From employees查询员工平均工资Select avg(salary) From employees查询员工最高工资Select max(salary) From employees查询员工最低工资Select min(salary) From employees

15、查询员工工资总数Select sum(salary) From employees58DMLDML基础基础-JOINS-JOINS多表关联多表关联用途JOIN将多表数据关联起来，JOIN是关系型数据库的魅力所在语法SELECT columns FROM table1,table2,table3 WHERE predicates;只要把几个表放于FROM子句后，这些表就会自动关联起来分类CROSS-JOIN:不在WHERE从句指定任何关联条件，返回的记录数为各个表记录数的笛卡尔乘积。INNER JOIN:在WHERE从句指定表之间关联条件，返回符合关联条件的指定的各个表记录数及其各列值;OUTE

16、R JOIN: 在WHERE从句指定表之间关联条件，返回符合关联条件的指定的各个表记录数及各列值，不符合关联条件的“外连表”列返回空;59DMLDML基础基础-JOINS-JOINS多表关联多表关联- -两表两表示例 Suppliers供应商表 Orders采购订单表;60DMLDML基础基础-JOINS-JOINS多表关联多表关联- -两表两表示例 INNER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.suppl

17、ier_id;61DMLDML基础基础-JOINS-JOINS多表关联多表关联- -两表两表示例 OUTER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id (+);62DMLDML基础基础-JOINS-JOINS多表关联多表关联- -三表三表示例 Suppliers供应商表 Orders采购订单表 员工表63DMLDML基础基础-JOINS-JOINS多表关联多表关联- -三表三表示例 INNE

18、R 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_idAnd od.employee_number = em.employee_number;64DMLDML基础基础-JOINS-JOINS多表关联多表关联- -三表三表示例 OUTER JOINSELECT sp.supplier_id, sp.suppli

19、er_name, od.order_date, od.order_date ,em.employee_nameFROM suppliers sp, orders od, employees emWHERE sp.supplier_id = od.supplier_id(+)And od.employee_number = em.employee_number(+);65DMLDML基础基础集合运算符:复合查询UNIONUNION ALLINTERSECTMINUS66DMLDML基础基础- -复合查询复合查询-UNION-UNION用途将多个SELECT语句的结果集加起来，去掉重复的数据(集合

20、并运算)，限制：参与UNION的每个SELECT语句的相应列必须是相同数据类型语法select field1, field2, . field_nfrom tablesUNIONselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersUNIONselect supplier_idfrom orders;67DMLDML基础基础- -复合查询复合查询-UNION ALL-UNION ALL用途将多个SELECT语句的结果集加起来，不去掉重复的数据限制：参与UNION ALL的每个SELECT语句的

21、相应列必须是相同数据类型语法select field1, field2, . field_nfrom tablesUNION ALLselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersUNION ALLselect supplier_idfrom orders;68DMLDML基础基础- -复合查询复合查询-INTERSECT-INTERSECT用途求多个SELECT的交集(集合交运算)限制：参与INTERSECT的每个SELECT语句的相应列必须是相同数据类型语法select field1

22、, field2, . field_nfrom tablesINTERSECTselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersINTERSECTselect supplier_idfrom orders;69DMLDML基础基础- -复合查询复合查询-MINUS-MINUS用途求在第一个SELECT，不在第二个SELECT的数据(集合减运算)限制：参与MINUS的每个SELECT语句的相应列必须是相同数据类型语法select field1, field2, . field_nfrom t

23、ablesMINUSselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersMINUSselect supplier_idfrom orders;70DMLDML基础基础-SUBQUERY-SUBQUERY子查询子查询概念 在一个主SELECT、INSERT、UPDATE、DELETE语句中，嵌套的、用括号括起来的SELECT语句，称为子查询语法 相关子查询:子查询中引用外部表 非相关子查询:子查询中不引用外部表示例 工资大于工号为1002员工工资的员工信息(不相关子查询)Select * Fr

24、om employees em Where em.salary (Select salary From employees pl Where pl.employee_number = 1002);71DMLDML基础基础-SUBQUERY-SUBQUERY子查询子查询示例 查询有下采购订单的员工(不相关子查询)Select * From employees em, (Select Distinct employee_number From orders) v Where em.employee_number = v.employee_number 查询所有部门及其部门员工工资总和(相关子查询)

25、Select dp.department_id, dp.department_name, (Select Sum(salary) From employees pl Where pl.department_id = dp.department_id) total_salary From departments dp72DMLDML基础基础-SUBQUERY-SUBQUERY子查询子查询示例 查询有下采购订单的员工(相关子查询)Select * From employees em Where em.employee_number In (Select Distinct employee_numb

26、er From orders) 查询有下采购订单的员工(相关子查询)Select * From employees em Where Exists (Select 1 From orders od Where od.employee_number = em.employee_number)73DMLDML基础基础-level-level与与CONNECT BYSTART WITHCONNECT BYSTART WITH背景应用系统长通常有类似菜单、产品类别等有上下级关系的树形结构数据 通常会设计为ID、PARENT_ID结构74DMLDML基础基础-level-level与与CONNECT B

27、YSTART WITHCONNECT BYSTART WITH原理 Oracle提供CONNECT BYSTART WITH用于类似结构查询 提供关键字level在CONNECT BYSTART WITH查询返回数据所在树形结构层次，level值越大表示层级越深 示例 查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显(缩进)，lpad函数参考后续教程介绍75DMLDML基础基础-ROWNUM-ROWNUM与分页查询与分页查询背景应用系统在数据量大的时候，通常需要将数据分页显示76DMLDML基础基础-ROWNUM-ROWNUM与分页查询与分页查询原理 Oracle提

28、供关键字rownum，在SELECT语句查询返回数据中表示是第几行 示例 查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显，并读取第1至第7行数据，选定部分要排好序77DMLDML基础基础INSERT语句基本INSERT语句INSERT与SELECT78DMLDML基础基础-INSERT-INSERT-基本基本INSERTINSERT语句语句用途 将数据插入到指定表中语法INSERT INTO table(column-1, column-2, . column-n)VALUES(value-1, value-2, . value-n);示例Insert Into

29、suppliers (supplier_id, supplier_name, city, type)Values (53553, TestInsertSupplier, Detroit, Cooking);79DMLDML基础基础-INSERT-INSERT-INSERT-INSERT与与SELECTSELECT用途将数据插入到指定表中语法INSERT INTO table(column-1, column-2, . column-n)SELECTColumn-1,column-2 . column-nFROM TablesWhERE predicates;示例 将所在城市为Newark的客户

30、生成供应商， 其中供应商supplier_id对应为客户account_no， supplier_name对应为customer_nameINSERT INTO suppliers(supplier_id, supplier_name)SELECT account_no, customer_nameFROM customersWHERE city = Newark;80DMLDML基础基础-INSERT-INSERT-INSERT-INSERT与与SELECTSELECT示例INSERT INTO clients(client_id, client_name, client_type)SELE

31、CT supplier_id, supplier_name, advertisingFROM suppliers spWHERE not exists (select * from clients clwhere cl.client_id = sp.supplier_id);81DMLDML基础基础UPDATE语句基本UPDATE语句82DMLDML基础基础-UPDATE-UPDATE语句语句用途 UPDATE用于更新指定表符合条件的数据;语法 UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;示例 将名称为

32、Smith的供应商名称更新为HP，同时在备注记录旧名称UPDATE suppliers SET supplier_name = HP,commets= Smith WHERE supplier_name = Smith;83DMLDML基础基础-UPDATE-UPDATE语句语句用途更新根据客户生产的供应商(客户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);84DMLDML基础基