OracleSQL基础培训课件

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

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

3、ion Language(TL数据事务语言)第5页，共92页。目录 课程介绍 DML基础 SQL介绍 DCL基础 DDL基础 TL基础第6页，共92页。DML基础算术运算符关系运算符逻辑运算符SELECT语句 :查询数据集合运算符:复合查询SUBQUERY子查询LEVEL、PRIOR与CONNECT BY START WITHROWNUM与分页查询INSERT语句 :插入数据UDATE语句 :更新数据DELETE语句 :删除数据SELECT与INSERT、UPDATE、DELETE第7页，共92页。DML基础算术运算符号+ 加 1+1- 减 2-1* 乘 2*3/ 除 4/2| 字符连接 Be

4、ijing | 2008第8页，共92页。DML基础关系运算符号= 等于 ，1=1 不等于， 21 大于，21 小于， 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 是汇聚

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

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

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

8、upplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id;第32页，共92页。DML基础-JOINS多表关联-两表示例OUTER JOINSELECT sp.supplier_id, sp.supplier_name, od.order_dateFROM suppliers sp, orders odWHERE sp.supplier_id = od.supplier_id (+);第33页，共92页。DML基础-JOINS多表关联-三表示例Suppliers供应商表

9、Orders采购订单表员工表第34页，共92页。DML基础-JOINS多表关联-三表示例INNER 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;第35页，共92页。DML基础-JOINS多表关联-三表示例OUTER

10、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_id(+)And od.employee_number = em.employee_number(+);第36页，共92页。DML基础集合运算符:复合查询UNIONUNION ALLINTERSECTMINUS第37页，共92页。DML基础-复合查询-UNION用

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

12、N ALL的每个SELECT语句的相应列必须是相同数据类型语法select field1, field2, . field_nfrom tablesUNION ALLselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersUNION ALLselect supplier_idfrom orders;第39页，共92页。DML基础-复合查询-INTERSECT用途求多个SELECT的交集(集合交运算)限制：参与INTERSECT的每个SELECT语句的相应列必须是相同数据类型语法select fi

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

14、MINUSselect field1, field2, . field_nfrom tables;示例select supplier_idfrom suppliersMINUSselect supplier_idfrom orders;第41页，共92页。DML基础-SUBQUERY子查询概念在一个主SELECT、INSERT、UPDATE、DELETE语句中，嵌套的、用括号括起来的SELECT语句，称为子查询分类相关子查询:子查询中引用外部表非相关子查询:子查询中不引用外部表示例工资大于工号为1002员工工资的员工信息(不相关子查询)Select * From employees em Wh

15、ere em.salary (Select salary From employees pl Where pl.employee_number = 1002);第42页，共92页。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.d

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

17、 * From employees em Where Exists (Select 1 From orders od Where od.employee_number = em.employee_number)第44页，共92页。DML基础-level与CONNECT BYSTART WITH背景应用系统长通常有类似菜单、产品类别等有上下级关系的树形结构数据通常会设计为ID、PARENT_ID结构第45页，共92页。DML基础-level与CONNECT BYSTART WITH原理Oracle提供CONNECT BYSTART WITH用于类似结构查询提供关键字level在CONNECT B

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

19、ERT语句基本INSERT语句INSERT与SELECT第49页，共92页。DML基础-INSERT-基本INSERT语句用途将数据插入到指定表中语法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);第50页，共92页。DML基础-INSERT-

20、INSERT与SELECT用途将数据插入到指定表中语法INSERT INTO table(column-1, column-2, . column-n)SELECTColumn-1,column-2 . column-nFROM TablesWhERE predicates;示例将所在城市为Newark的客户生成供应商， 其中供应商supplier_id对应为客户account_no， supplier_name对应为customer_nameINSERT INTO suppliers(supplier_id, supplier_name)SELECT account_no, customer

21、_nameFROM customersWHERE city = Newark;第51页，共92页。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);第52页，共92页。DML基础UPDATE语句基本

22、UPDATE语句第53页，共92页。DML基础-UPDATE语句用途UPDATE用于更新指定表符合条件的数据;语法UPDATE table SET column1 = value1,column2 = value2 WHERE predicates;示例将名称为Smith的供应商名称更新为HP，同时在备注记录旧名称UPDATE suppliers SET supplier_name = HP,commets= Smith WHERE supplier_name = Smith;第54页，共92页。DML基础-UPDATE语句用途更新根据客户生产的供应商(客户account_no生成为suppl

23、ier_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);第55页，共92页。DML基础DELETE语句DELETE语句第56页，共92页。DML基础-DELETE语句用途DELETE用于将数据从指定表删除;语法DELETE FROM tables WHER

24、E predicates ;示例删除供应商为DELETE FROM suppliers WHERE supplier_name = Smath;删除没有采购订单的供应商Delete From suppliers sp Where Not Exists (Select od.order_id From orders od Where od.supplier_id = sp.supplier_id);第57页，共92页。DML基础-SELECT与INSERT、UPDATE、DELETE用途SELECT语句可以使用字符串拼凑的方法生成INSERT、UPDATE、DELETE语句，通常用于数据处理，比

25、如清除按一定条件重复数据，修正数据，调整数据等;语法SELECT column1, column2, . column_n, INSERT STATEMENT, insert_sql,UPDATE STATEMENT update_sql,DELETE STATEMENT delete_sql FROM tables WHERE predicates示例找到按名称、所属城市有重复的供应商数据，删除ID最小的数据第58页，共92页。DML基础-SELECT与INSERT、UPDATE、DELETE示例将每个部门工资低于平均部门平均工资的员工工资，增加1000第59页，共92页。目录 课程介绍 D

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

27、语句示例创建部门表departments并设置DEPARTMENT_ID为主键创建员工表eployees并设置EMPLOYEE_NUMBER为主键，DEPARTMENT_ID为外键，引用部门表主键DEPARTMENT_ID第65页，共92页。DDL基础-表-从查询创建表用途在数据库中建立表，并将相应查询返回的数据插入至新建表中。但查询相关表的索引、主键等不会复制到新建表中语法CREATE table table_name asselect query示例备份整个suppliers表数据到新建表supplier_20100303CREATE table suppliers_20100303ass

28、elect * from suppliers第66页，共92页。DDL基础-表-从查询创建表示例单表查询，按指定查询列创建表suppliers，并填充数据多表关联，按指定查询列创建表suppliers，并填充数据创建表单但不填充数据，再用脚本插入数据（数据处理时常用）第67页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表增加备注语法示例第68页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表字段增加备注语法示例第69页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表重命名语法示例第70页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表添加字段语

29、法示例第71页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表修改字段语法示例第72页，共92页。DDL基础-表-修改表结构用途在数据库中对已有表删除字段语法示例第73页，共92页。DDL基础-表-删除表数据用途在数据库中删除指定表数据(全部数据，不可回滚)语法TRUNCATE TABLE table_name;示例 TRUNCATE TABLE supplier;第74页，共92页。DDL基础-表-删除表结构用途在数据库中删除指定表结构语法DROP TABLE table_name;示例 DROP TABLE supplier;第75页，共92页。DDL基础视图创建视图修改视图删

30、除视图第76页，共92页。DDL基础-视图-创建视图用途在数据库中创建视图语法示例 查询数据第77页，共92页。DDL基础-视图-修改视图用途在数据库中修改或者创建视图语法示例 第78页，共92页。DDL基础-视图-删除视图用途在数据库中删除指定视图语法DROP VIEW view_name;示例 DROP VIEW sup_orders_v;第79页，共92页。DDL基础同义词创建同义词修改同义词删除同义词同义词概念同义词本身也是数据库对象，一般用于映射别的用户的对象，使用同义词可以屏蔽具体被映射对象的所属用户和真实对象名称同义词和视图类似，是一种映射，视图只能映射查询SQL，同义词可以映射

31、表、视图、存储过程、函数 、包、序列等第80页，共92页。DDL基础-同义词-创建同义词用途在数据库中使用别的用户(schema)的数据库对象语法示例 查询数据：第81页，共92页。DDL基础-视图-修改同义词用途在数据库中修改或者创建视图语法示例 第82页，共92页。DDL基础-表-删除同义词用途在数据库中删除指定同义词语法DROP SYNONYM synonym_name;示例 DROP SYNONYM fnd_user;第83页，共92页。目录 课程介绍 DML基础 SQL介绍 DCL基础 DDL基础 TL基础第84页，共92页。DCL基础权限清单授权语句GrantRevoke第85页，

32、共92页。DCL基础-授权语句-Grant语法grant privileges on object to users;示例授指定表一个权限，给指定用户grant select on suppliers to smithj;授指定表多个权限，给指定用户grant select,insert,update,delte on suppliers to smithj;授指定表全部权限，给指定用户grant all on suppliers to smithj;授权指定表指定权限，给所有用户grant select on suppliers to public;第86页，共92页。DCL基础-授权语句-

33、Revoke语法revoke privileges on object from users;示例从指定用户收回指定表一个权限revoke select on suppliers from smithj;从指定用户收回指定表多个权限revoke select,insert,update,delte on suppliers from smithj;从指定用户收回指定表全部权限revoke all on suppliers from smithj;从所有用户收回指定表指定权限revoke select on suppliers from public;第87页，共92页。目录 课程介绍 DML基

34、础 SQL介绍 DCL基础 DDL基础 TL基础第88页，共92页。TL基础COMMIT提交事务ROLLBACK回滚事务第89页，共92页。TL基础-授权语句-Commit用途在一系列DML后，向数据库提交事务，在执行COMMIT前所有事务按执行先后顺序可见 ，执行COMMIT后，所有DML操作最终效果才回生效并保持至数据库中。语法commit;示例执行插入后数据后，提交事务执行插入、更新数据后，提交事务第90页，共92页。DCL基础-授权语句-ROLLBACK用途显式回滚数据库提交事务，所有DML操作都回滚至最近一次commit后。语法rollback;示例执行插入后数据后，提交事务 查询数

35、据第91页，共92页。1、不是井里没有水，而是你挖的不够深。不是成功来得慢，而是你努力的不够多。2、孤单一人的时间使自己变得优秀，给来的人一个惊喜，也给自己一个好的交代。3、命运给你一个比别人低的起点是想告诉你，让你用你的一生去奋斗出一个绝地反击的故事，所以有什么理由不努力!4、心中没有过分的贪求，自然苦就少。口里不说多余的话，自然祸就少。腹内的食物能减少，自然病就少。思绪中没有过分欲，自然忧就少。大悲是无泪的，同样大悟无言。缘来尽量要惜，缘尽就放。人生本来就空，对人家笑笑，对自己笑笑，笑着看天下，看日出日落，花谢花开，岂不自在，哪里来的尘埃!25、你不能拼爹的时候，你就只能去拼命！26、如果

36、人生的旅程上没有障碍，人还有什么可做的呢。27、我们无法选择自己的出身，可是我们的未来是自己去改变的。励志名言：比别人多一点执着，你就会创造奇迹28、伟人之所以伟大，是因为他与别人共处逆境时，别人失去了信心，他却下决心实现自己的目标。29、人生就像一道漫长的阶梯，任何人也无法逆向而行，只能在急促而繁忙的进程中，偶尔转过头来，回望自己留下的蹒跚脚印。30、时间，带不走真正的朋友；岁月，留不住虚幻的拥有。时光转换，体会到缘分善变；平淡无语，感受了人情冷暖。有心的人，不管你在与不在，都会惦念；无心的情，无论你好与不好，只是漠然。走过一段路，总能有一次领悟；经历一些事，才能看清一些人。31、我们无法选

37、择自己的出身，可是我们的未来是自己去改变的。32、命好不如习惯好。养成好习惯，一辈子受用不尽。33、比别人多一点执着，你就会创造奇迹。50、想像力比知识更重要。不是无知，而是对无知的无知，才是知的死亡。51、对于最有能力的领航人风浪总是格外的汹涌。52、思想如钻子，必须集中在一点钻下去才有力量。53、年少时，梦想在心中激扬迸进，势不可挡，只是我们还没学会去战斗。经过一番努力，我们终于学会了战斗，却已没有了拼搏的勇气。因此，我们转向自身，攻击自己，成为自己最大的敌人。54、最伟大的思想和行动往往需要最微不足道的开始。55、不积小流无以成江海，不积跬步无以至千里。56、远大抱负始于高中，辉煌人生起

38、于今日。57、理想的路总是为有信心的人预备着。58、抱最大的希望，为最大的努力，做最坏的打算。59、世上除了生死，都是小事。从今天开始，每天微笑吧。60、一勤天下无难事，一懒天下皆难事。61、在清醒中孤独，总好过于在喧嚣人群中寂寞。62、心里的感觉总会是这样，你越期待的会越行越远，你越在乎的对你的伤害越大。63、彩虹风雨后，成功细节中。64、有些事你是绕不过去的，你现在逃避，你以后就会话十倍的精力去面对。65、只要有信心，就能在信念中行走。66、每天告诉自己一次，我真的很不错。67、心中有理想 再累也快乐68、发光并非太阳的专利，你也可以发光。69、任何山都可以移动，只要把沙土一卡车一卡车运走

39、即可。70、当你的希望一个个落空，你也要坚定，要沉着！71、生命太过短暂，今天放弃了明天不一定能得到。72、只要路是对的，就不怕路远。73、如果一个人爱你、特别在乎你，有一个表现是他还是有点怕你。74、先知三日，富贵十年。付诸行动，你就会得到力量。75、爱的力量大到可以使人忘记一切，却又小到连一粒嫉妒的沙石也不能容纳。1、这世上，没有谁活得比谁容易，只是有人在呼天抢地，有人在默默努力。2、当热诚变成习惯，恐惧和忧虑即无处容身。缺乏热诚的人也没有明确的目标。热诚使想象的轮子转动。一个人缺乏热诚就象汽车没有汽油。善于安排玩乐和工作，两者保持热诚，就是最快乐的人。热诚使平凡的话题变得生动。3、起点低怕什么，大不了加倍努力。人生就像一场马拉松比赛，拼的不是起点，而是坚持的耐力和成长的速度。只要努力不止，进步也会不止。4、如果你不相信努力和时光，那么时光第一个就会辜负你。不要去否定你的过去，也不要用你的过去牵扯你的未来。不是因为有希望才去努力，而是努力了，才能看到希望。5、人生每天都要笑，生活的下一秒发生什么，我们谁也不知道。所以，放下心里的纠结，放下脑中的烦恼