Oracle SQL基础培训

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

2、,DCL基础,TL基础,SQL介绍,概述 SQL是Structured Query Language简写 SQL用于组织、访问和处理数据库中的数据标准的计算机语言 SQL具有第四代语言的特征，程序关注要告诉DBMS要得做什么操作，及其相应条件，至于如何得到数据，如何使用索引，如何关联多表，由DBMS完成 分类 Data Definition Language(DDL数据定义语言) Data Manipulation Language(DML数据操作语言) Data Control Language(DCL数据控制语言) Transaction Language(TL数据事务语言,目录,课程介绍

3、,DDL基础,SQL介绍,DML基础,DCL基础,TL基础,DDL基础,语句 CREATE 创建数据库对象 DROP 删除数据库对象 TRUNCATE 删除指定表全部数据，不可回滚 ALTER 修改数据库对象 COMMENT 给表、字段添加备注 RENAME重命名数据库对象 分类 表相关 视图相关 同义词相关,DDL基础,表 标准创建表语句 从查询创建表 修改表结构 删除表数据 删除表结构,DDL基础-表-标准建语句,用途 在数据库中建立表 语法 示例,DDL基础-表-标准建语句,示例 创建客户表并设置CUSTOMER_ID为主键,DDL基础-表-标准建语句,示例 创建部门表departmen

4、ts并设置DEPARTMENT_ID为主键 创建员工表eployees并设置EMPLOYEE_NUMBER为主键，DEPARTMENT_ID为外键，引用部门表主键DEPARTMENT_ID,DDL基础-表-从查询创建表,用途 在数据库中建立表，并将相应查询返回的数据插入至新建表中。但查询相关表的索引、主键等不会复制到新建表中 语法 CREATE table table_name as select query 示例 备份整个suppliers表数据到新建表supplier_20100303 CREATE table suppliers_20100303 as select * from sup

5、pliers,DDL基础-表-从查询创建表,示例 单表查询，按指定查询列创建表suppliers，并填充数据 多表关联，按指定查询列创建表suppliers，并填充数据 创建表单但不填充数据，再用脚本插入数据（数据处理时常用,DDL基础-表-修改表结构,用途 在数据库中对已有表增加备注 语法 示例,DDL基础-表-修改表结构,用途 在数据库中对已有表字段增加备注 语法 示例,DDL基础-表-修改表结构,用途 在数据库中对已有表重命名 语法 示例,DDL基础-表-修改表结构,用途 在数据库中对已有表添加字段 语法 示例,DDL基础-表-修改表结构,用途 在数据库中对已有表修改字段 语法 示例,D

6、DL基础-表-修改表结构,用途 在数据库中对已有表删除字段 语法 示例,DDL基础-表-删除表数据,用途 在数据库中删除指定表数据(全部数据，不可回滚) 语法 TRUNCATE TABLE table_name; 示例 TRUNCATE TABLE supplier,DDL基础-表-删除表结构,用途 在数据库中删除指定表结构 语法 DROP TABLE table_name; 示例 DROP TABLE supplier,DDL基础,视图 创建视图 修改视图 删除视图,DDL基础-视图-创建视图,用途 在数据库中创建视图 语法 示例 查询数据,DDL基础-视图-修改视图,用途 在数据库中修改或

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

8、DDL基础-视图-修改同义词,用途 在数据库中修改或者创建视图 语法 示例,DDL基础-表-删除同义词,用途 在数据库中删除指定同义词 语法 DROP SYNONYM synonym_name; 示例 DROP SYNONYM fnd_user,目录,课程介绍,DDL基础,SQL介绍,DML基础,DCL基础,TL基础,DCL基础,权限清单 授权语句 Grant Revoke,DCL基础-授权语句-Grant,语法 grant privileges on object to users; 示例 授指定表一个权限，给指定用户 grant select on suppliers to smithj;

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

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

11、 ROWNUM与分页查询 INSERT语句 :插入数据 UDATE语句 :更新数据 DELETE语句 :删除数据 SELECT与INSERT、UPDATE、DELETE,DML基础,算术运算符号 + 加 1+1 - 减 2-1 * 乘 2*3 / 除 4/2 | 字符连接 Beijing | 2008,DML基础,关系运算符号 = 等于 ，1=1 不等于， 21 大于，21 = 大于等于， 2=1 = 小于等于，1=2 IS NULL 为空，没有值，不是空字符串 IS NOT NULL 不为空，有值 BETWEEN AND 检索两值之间的内容，2 BETWEEN 1 AND 3 IN检索匹配列

12、表中的值 LIKE检索匹配字符样式的数据,DML基础,逻辑运算符号 AND 两个为真则结果为真 OR 一个为真即为真 NOT 取相反的逻辑值,DML基础,SELECT语句 完整SELECT语句 基本SELECT语句 ORDER BY从句 DISTINCT从句 WHERE从句 AND条件 OR条件 AND、OR复合条件 IN 与 NOT IN BETWEEN 与 NOT BETWEEN LIKE与NOT LIKE EXISITS 与 NOT EXISITS GROUP BY从句 HAVING从句 JOINS关联,DML基础-SELECT语句-完整SELECT语句,SELECT ALL | DIS

13、TINCT ON ( expression , . ) * | expression AS output_name , . INTO TEMPORARY | TEMP TABLE new_table FROM from_item , . WHERE condition GROUP BY expression , . HAVING condition , . UNION | INTERSECT | EXCEPT ALL select ORDER BY expression ASC | DESC | USING operator , . FOR UPDATE OF class_name , . L

14、IMIT count | ALL OFFSET | , start,DML基础-SELECT语句-基本SELECT语句,用途 SELECT语句用于查询数据库表或视图的数据 通常在表名或字段后加上 空格 as 别名，其中as可省略，用别名引用表，可节省长度; 语法 SELECT columns FROM tables WHERE predicates; 示例 查询所有供应商，返回所有列数据项 SELECT sp.* FROM suppliers sp; 查询所有供应商，返回指定列数据项 SELECT sp.supplier_name, sp.city as supplier_city FROM

15、suppliers sp,DML基础-SELECT语句-ORDER BY从句,用途 ORDER BY从句用于对SELECT语句返回的数据进行排序，ASC是升序，DESC是降序，默认是ASC，ORDER BY不能用于INSERT、UPDATE、DELETE语句中; 语法 SELECT columns FROM tables WHERE predicates ORDER BY column ASC/DESC; 示例 SELECT city FROM suppliers ORDER BY city desc; SELECT city FROM suppliers ORDER BY 1 desc; S

16、ELECT city,state FROM suppliers ORDER BY city desc,state asc,DML基础-SELECT语句-DISTINCT从句,用途 DISTINCT从句用于过滤重复数据; 语法 SELECT DISTINCT columns FROM tables WHERE predicates; 示例 查询所有供应商所在城市，城市名不重复 SELECT DISTINCT city FROM suppliers; 查询所有供应商所在城市、州，城市名、州名一组不重复SELECT DISTINCT city,state FROM suppliers,DML基础-S

17、ELECT语句-WHERE从句,用途 WHERE从句用于指定操作条件，还可以用于INSERT、UPDATE、DELETE语句中; 语法 SELECT columns FROM tables WHERE predicates; 示例 SELECT city FROM suppliers WHERE supplier_name = IBM,DML基础-SELECT语句-AND条件,用途 AND条件用于指定2个或者以上操作条件同时满足，与WHERE从句一起使用; 语法 SELECT columns FROM tables WHERE column1 = value1 AND column2 = va

18、lue2 AND column3 = value3; 示例 SELECT * FROM suppliers WHERE city = New York AND type = PC Manufacturer,DML基础-SELECT语句-OR条件,用途 OR条件与AND条件可同时与WHERE从句一起使用，用于指定复杂条件，通常需用括号指明关系; 语法 SELECT columns FROM tables WHERE column1 = value1 ORcolumn2 = value2 OR column3 = value3 ; 示例 SELECT * FROM suppliers WHERE

19、city = New York OR city = Newark,DML基础-SELECT语句-OR/AND条件复合,用途 OR条件与AND条件可同时与WHERE从句一起使用，用于指定复杂条件，通常需用括号指明关系; 语法 SELECT columns FROM tables WHERE column1 = value1 OR/AND column2 = value2 OR/AND column3 = value3 ; 示例 SELECT * FROM suppliers WHERE (city = Chicago AND name = IBM) OR (city = Seattle,DML基

20、础-SELECT语句-IN 与 NOT IN,用途 IN用于多个OR条件，NOT IN为IN的相反; 语法 SELECT columns FROM tables WHERE column1 in (value1, value2, . value_n); 示例 SELECT * FROM suppliers WHERE supplier_name IN ( IBM, Hewlett Packard, Microsoft); 与以下SQL返回相同数据：SELECT * FROM suppliers WHERE supplier_name = IBM OR supplier_name = Hewle

21、tt Packard OR supplier_name = Microsoft; SELECT * FROM suppliers WHERE supplier_name NOT IN ( IBM, Hewlett Packard, Microsoft,DML基础-SELECT语句-BETWEEN 与 NOT BETWEEN,用途 BETWEEN用于查询一个范围的数据; 语法 SELECT columns FROM tables WHERE column1 between value1 and value2; 示例 SELECT * FROM suppliers WHERE supplier_i

22、d between 10000 AND 10002; SELECT * FROM orders WHERE order_date between to_date (2003-01-01, yyyy-mm-dd) AND to_date (2003-12-31, yyyy-mm-dd); SELECT * FROM suppliers WHERE supplier_id NOT between 10001 AND 10002,DML基础-SELECT语句-LIKE与NOT LIKE,用途 LIKE用于模糊匹配查询，结合%与_一起使用，其中%匹配大于等于1个字符，_只匹配一个字符 使用!% esc

23、ape ! 或者 !_ escape ! 转义 % _; 语法 SELECT columns FROM tables WHERE column1 like %_value_%; 示例 查询名称以Hew开头(Hew前面没有其他任何字符，Hew后面是任意字符或没有字符)的供应商 Select * From suppliers Where supplier_name Like Hew%; 查询名称包含cro的供应商(名称中cro前后可能有任意字符，也可能没有字符) Select * From suppliers Where supplier_name Like %cro,DML基础-SELECT语句

24、-LIKE与NOT LIKE,示例 查询名称不是以Hew开头的供应商 Select * From suppliers Where supplier_name NOT Like Hew%; 查询名称为Smith或Smath或其他类似名称的供应商(名称中m与t之间有一个字符) Select * From suppliers Where supplier_name Like %Sm_th%; 查询名称是以H开头，%结尾，H与%之间有任意字符的供应商，如Hello% Select * From suppliers Where supplier_name Like H%!% escape !; 查询名称

25、是以H开头，_结尾，H与_之间有任意字符的供应商，如Hello_ Select * From suppliers Where supplier_name Like H%!_ escape,DML基础-SELECT语句- EXISTS 与 NOT EXISTS,用途 EXISTS 判断子查询是否有数据返回，有则为TURE， 否则为FALSE，EXISTS也可用于INSERT、UPDATE、DELETE; 语法 SELECT columns FROM tables WHERE EXISTS ( subquery ); 示例 SELECT * FROM suppliers sp WHERE EXIS

26、TS (select * 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 FROM suppliers sp WHERE EXISTS (select * from orders od where od.supplier_id =sp.supplier_id,DML基础-SELECT语句- GROUP B

27、Y从句,用途 GROUP BY用于将结果集按指定列分组，并进行聚合; 语法 SELECT column1, column2, . column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2, . column_n; aggregate_function 是汇聚函数，通常为SUM, COUNT, MIN, MAX 示例 查询每个部门及部门总工资,DML基础-SELECT语句- GROUP BY从句,示例 查询部门及其员工个数,查询部门及其最低员工工资,查询部门及其

28、最高员工工资,DML基础-SELECT语句- HAVING从句,用途 HAVING从句与GROUP BY从句一起使用，用于聚合条件的判断，如COUNT(*)1，因聚合条件不能在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 是

29、汇聚函数，通常为SUM, COUNT, MIN, MAX,AVG 示例 查询总工资大于35000的部门,DML基础-SELECT语句- GROUP BY从句,示例 查询员工个数大于5的部门及其员工个数,查询有最低工资为10000的部门及其最低员工工资,查询有最低工资为10000的部门及其最低员工工资,DML基础-SELECT语句- GROUP BY从句,直接聚合 COUNT、SUM、MIN、MAX、AVG可以不和GROUP BY一起使用，直接对查询数据直接进行聚合，但SELECT不能包含其他列 示例 查询员工总数 Select Count(*) From employees 查询员工平均工资

30、Select avg(salary) From employees 查询员工最高工资 Select max(salary) From employees 查询员工最低工资 Select min(salary) From employees 查询员工工资总数 Select sum(salary) From employees,DML基础-JOINS多表关联,用途 JOIN将多表数据关联起来，JOIN是关系型数据库的魅力所在 语法 SELECT columns FROM table1,table2,table3 WHERE predicates; 只要把几个表放于FROM子句后，这些表就会自动关联

31、起来 分类 CROSS-JOIN:不在WHERE从句指定任何关联条件，返回的记录数为各个表记录数的笛卡尔乘积。 INNER JOIN:在WHERE从句指定表之间关联条件，返回符合关联条件的指定的各个表记录数及其各列值; OUTER JOIN: 在WHERE从句指定表之间关联条件，返回符合关联条件的指定的各个表记录数及各列值，不符合关联条件的“外连表”列返回空,DML基础-JOINS多表关联-两表,示例 Suppliers供应商表,Orders采购订单表,DML基础-JOINS多表关联-两表,示例,INNER JOIN SELECT sp.supplier_id, sp.supplier_nam

32、e, od.order_date FROM suppliers sp, orders od WHERE sp.supplier_id = od.supplier_id,DML基础-JOINS多表关联-两表,示例,OUTER JOIN SELECT sp.supplier_id, sp.supplier_name, od.order_date FROM suppliers sp, orders od WHERE sp.supplier_id = od.supplier_id (,DML基础-JOINS多表关联-三表,示例 Suppliers供应商表 Orders采购订单表 员工表,DML基础-J

33、OINS多表关联-三表,示例,INNER JOIN SELECT sp.supplier_id, sp.supplier_name, od.order_date, od.order_date ,em.employee_name FROM suppliers sp, orders od, employees em WHERE sp.supplier_id = od.supplier_id And od.employee_number = em.employee_number,DML基础-JOINS多表关联-三表,示例,OUTER JOIN SELECT sp.supplier_id, sp.su

34、pplier_name, od.order_date, od.order_date ,em.employee_name FROM suppliers sp, orders od, employees em WHERE sp.supplier_id = od.supplier_id(+) And od.employee_number = em.employee_number(,DML基础,集合运算符:复合查询 UNION UNION ALL INTERSECT MINUS,DML基础-复合查询-UNION,用途 将多个SELECT语句的结果集加起来，去掉重复的数据(集合并运算)， 限制：参与UN

35、ION的每个SELECT语句的相应列必须是相同数据类型 语法 select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables; 示例 select supplier_id from suppliers UNION select supplier_id from orders,DML基础-复合查询-UNION ALL,用途 将多个SELECT语句的结果集加起来，不去掉重复的数据 限制：参与UNION ALL的每个SELECT语句的相应列必须是相同数据类型 语法 sele

36、ct field1, field2, . field_n from tables UNION ALL select field1, field2, . field_n from tables; 示例 select supplier_id from suppliers UNION ALL select supplier_id from orders,DML基础-复合查询-INTERSECT,用途 求多个SELECT的交集(集合交运算) 限制：参与INTERSECT的每个SELECT语句的相应列必须是相同数据类型 语法 select field1, field2, . field_n from t

37、ables INTERSECT select field1, field2, . field_n from tables; 示例 select supplier_id from suppliers INTERSECT select supplier_id from orders,DML基础-复合查询-MINUS,用途 求在第一个SELECT，不在第二个SELECT的数据(集合减运算) 限制：参与MINUS的每个SELECT语句的相应列必须是相同数据类型 语法 select field1, field2, . field_n from tables MINUS select field1, fi

38、eld2, . field_n from tables; 示例 select supplier_id from suppliers MINUS select supplier_id from orders,DML基础-SUBQUERY子查询,概念 在一个主SELECT、INSERT、UPDATE、DELETE语句中，嵌套的、用括号括起来的SELECT语句，称为子查询 语法 相关子查询:子查询中引用外部表 非相关子查询:子查询中不引用外部表 示例 工资大于工号为1002员工工资的员工信息(不相关子查询) Select * From employees em Where em.salary (Se

39、lect salary From employees pl Where pl.employee_number = 1002,DML基础-SUBQUERY子查询,示例 查询有下采购订单的员工(不相关子查询) 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

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

41、Exists (Select 1 From orders od Where od.employee_number = em.employee_number,DML基础-level与CONNECT BYSTART WITH,背景 应用系统长通常有类似菜单、产品类别等有上下级关系的树形结构数据 通常会设计为ID、PARENT_ID结构,DML基础-level与CONNECT BYSTART WITH,原理 Oracle提供CONNECT BYSTART WITH用于类似结构查询 提供关键字level在CONNECT BYSTART WITH查询返回数据所在树形结构层次，level值越大表示层级越深

42、 示例 查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显(缩进)，lpad函数参考后续教程介绍,DML基础-ROWNUM与分页查询,背景 应用系统在数据量大的时候，通常需要将数据分页显示,DML基础-ROWNUM与分页查询,原理 Oracle提供关键字rownum，在SELECT语句查询返回数据中表示是第几行 示例 查询“订单管理”及其下属菜单，按树形结构展现，使用lpad函数辅助效果更明显，并读取第1至第7行数据，选定部分要排好序,DML基础,INSERT语句 基本INSERT语句 INSERT与SELECT,DML基础-INSERT-基本INSERT语句,用途

43、将数据插入到指定表中 语法 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-INSERT与SELECT,用途 将数据插入到指定表中 语法 INSERT INTO table (column-1, column

44、-2, . column-n) SELECT Column-1,column-2 . column-n FROM Tables WhERE predicates; 示例 将所在城市为Newark的客户生成供应商， 其中供应商supplier_id对应为客户account_no， supplier_name对应为customer_name INSERT INTO suppliers (supplier_id, supplier_name) SELECT account_no, customer_name FROM customers WHERE city = Newark,DML基础-INSERT-INSERT与SELECT,示例 INSERT INTO clients (client_id, client_name, client_type) SELECT supplier_id, supplier_name, advertising FROM suppliers sp WHERE not exists (select * from clients cl where cl.client_id