Oracle进阶培训.ppt

360。企业管理信息化解决方案提供商,白金級合作伙伴,课程主题日期：2015.3,2,2,2,Oracle数据库基本访问语句,PL/SQL编程基础,Oracle数据库高级查询,目录,3,3,3,SQL语句分类数据操作语句（DataManipulationLanguage)：DML语句包括：select、insert、delete、update数据定义语句（DataDefinitionLanguage)：DDL语句包括：create、alter、drop、truncate事务控制语句（TransactionControlLanguage)：TCL语句包括：commit、rollback、savepoint数据控制语句（DataControlLanguage)：DCL语句包括：grant、revoke,ORACLE数据库基本访问语句,4,4,4,DML语句：select、insert、delete、updateDML语句用途及基础语法：SELECT语句用途：用于查询表或视图的数据；SELECT语句语法：SELECTcolumnsFROMtablesWHEREpredicates;INSERT语句用途：将数据插入到指定表中；INSERT语句语法：INSERTINTOtable(column-1,column-2,.column-n)VALUES(value-1,value-2,.value-n);,ORACLE数据库基本访问语句,5,5,5,DML语句：select、insert、delete、updateDML语句用途及基础语法：UPDATE语句用途：用于更新指定表（符合条件）的数据；UPDATE语句语法：UPDATEtableSETcolumn1=value1,column2=value2WHEREpredicates;DELETE语句用途：用于删除指定表（符合条件）的数据；DELETE语句语法：DELETEFROMtablesWHEREpredicates;,ORACLE数据库基本访问语句,6,6,6,DML语句：select、insert、delete、update示例：在SCOTT用户进行如下操作：1、查询emp表的所有员工的所有信息；2、查询emp表的所有员工的工号、姓名、基本薪水；3、查询工号为7839的员工的工号、姓名、基本薪水；4、插入一个员工信息：工号:8000,姓名：SIE，职位：SALESMAN，部门：30；5、将工号为8000的员工的上级领导修改为7920；6、删除工号为8000的员工。,ORACLE数据库基本访问语句,SELECT*FROMempe;SELECTempno,ename,salFROMemp;SELECTe.empno,e.ename,e.salFROMempeWHEREe.empno=7839;INSERTINTOEMP(empno,ename,job,deptno)VALUES(8000,SIE,SALESMAN,30);,COMMIT;UPDATEempSETMGR=7920WHEREempno=8000;COMMIT;DELETEFROMempWHEREempno=8000;COMMIT;,7,7,7,DML语句：select、insert、delete、update示例：在SCOTT用户进行如下操作：7、查询20部门的所有员工信息：部门名称，员工姓名，基本薪水；8、统计20部门的员工人数及平均基本薪水；9、查询不含员工的部门信息：部门名称；,ORACLE数据库基本访问语句,SELECTD.DNAME,E.ENAME,E.SALFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOANDD.DEPTNO=20;,SELECTD.DNAME,COUNT(*)emp_qty,AVG(SAL)avg_salFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOANDD.DEPTNO=20GROUPBYd.dname;,SELECTD.DNAMEFROMDEPTDWHERENOTEXISTS(SELECT1FROMEMPEWHEREE.DEPTNO=D.DEPTNO);,8,8,8,DML语句：select、insert、delete、update总结select语句是难点，需要多练习，常用select语句的类型有：单表查询、多表查询、子查询、集合操作、树结构查询、聚合函数、分析函数、自定义函数；Insert、delete、update三类改动要想存入库中必须要使用commit语句，放弃改动时要使用rollback语句；Insert时注意数据类型、唯一约束、非空约束等；delete、update使用时，一定要确保WHERE条件的正确性；注意NULL值，判断空用ISNULL，判断不空用ISNOTNULL。,ORACLE数据库基本访问语句,9,9,9,DDL语句：create、alter、drop、truncateDDL语句用途及语法create语句用途：创建数据库对象，如表、视图create语句语法:创建表基本语法：createtabletable_name(column_adatatypenull/notnull,column_bdatatypenull/notnull,);根据查询语句创建表基本语法：CREATEtabletable_nameasselectquery；创建视图基本语法：createorreplaceviewview_nameasselectcolumnsfromtablewherepredicates;,ORACLE数据库基本访问语句,10,10,10,DDL语句：create、alter、drop、truncateDDL语句用途及语法alter语句用途：修改数据库对象，一般用于修改表结构alter语句语法:修改表结构基本语法：增加字段：altertabletable_nameadd删除字段：altertabletable_namedropcloumn.重命名字段：altertabletable_namerenamecolumnto修改表字段(数据类型、NOTNULL约束)altertabletable_namemodify增加约束：altertabletable_nameaddconstraint,ORACLE数据库基本访问语句,11,11,11,DDL语句：create、alter、drop、truncateDDL语句用途及语法drop语句用途：删除数据库对象。drop语句语法:drop对象类型对象名称;,ORACLE数据库基本访问语句,DDL语句用途及语法truncate语句用途：删除表中所有数据，并释放所占用的空间。使用truncate表删除数据时不需要提交，也不能回滚。删除表中所有数据时使用truncate比delete高效。truncate语句语法:truncatetabletable_name;,12,12,12,DDL语句：create、alter、drop、truncateDDL语句用途及语法COMMENT语句用途：给表或表的字段加备注信息COMMENT语句语法:给表加备注语法：commentontabletable_nameis备注内容;给表中某个字段加备注语法：commentoncolumntable.column_nameis备注内容;RENAME语句用途：重命名数据库对象名称，常用于重命名表名RENAME语句语法:重命名表名语法：RENAMEold_nameTOnew_name,ORACLE数据库基本访问语句,13,13,13,DDL语句：create、alter、drop、truncate示例：在SCOTT用户下进行如下操作：1、使用基本语法创建sie_emp表,ORACLE数据库基本访问语句,-创建表sie_empCREATETABLESIE_EMP(emp_noVARCHAR2(30),emp_nameVARCHAR2(30),sexCHAR(1),dept_noVARCHAR2(30),base_salaryNUMBER);,14,14,14,DDL语句：create、alter、drop、truncate示例：在SCOTT用户下进行如下操作：2、创建sie_dept表，采用查询scott.dept表的方式，创建时不拷贝scott.dept表中的数据。,ORACLE数据库基本访问语句,-创建表sie_deptCREATETABLEsie_dept(dept_no,dept_name)ASSELECTdeptno,dnameFROMdeptWHERE1=2,15,15,15,DDL语句：create、alter、drop、truncate示例：在SCOTT用户下进行如下操作：3、修改sie_dept表：a、给字段dept_no添加主键约束；b、字段dept_no数据类型修改为VARCHAR2(30)；c、字段dept_name修改为NOTNULL约束；d、删除字段dept_name上的NOTNULL约束；e、字段dept_name上增加唯一约束；,ORACLE数据库基本访问语句,ALTERTABLEsie_deptADDCONSTRAINTsie_dept_pkPRIMARYKEY(dept_no);ALTERTABLEsie_deptMODIFY(dept_noVARCHAR2(30);,ALTERTABLEsie_deptMODIFY(dept_nameNOTNULL);ALTERTABLEsie_deptMODIFY(dept_nameNULL);ALTERTABLEsie_deptADDCONSTRAINTsie_dept_uq1UNIQUE(dept_name);,16,16,16,DDL语句：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=1500f、字段dept_no增加外键，参考表SIE_DEPT,ORACLE数据库基本访问语句,CREATEUNIQUEINDEXsie_emp_u1ONsie_emp(emp_no);ALTERTABLEsie_empADDCONSTRAINTsie_emp_pkPRIMARYKEY(emp_no);,ALTERTABLEsie_empADD(jobVARCHAR2(30),mgrVARCHAR(30);ALTERTABLEsie_empRENAMECOLUMNsexTOgender;,ALTERTABLEsie_empADDCONSTRAINTsie_emp_ck1CHECK(salary=1500);ALTERTABLEsie_empADDCONSTRAINTsie_emp_fk1FOREIGNKEY(dept_no)REFERENCESsie_dept(dept_no);,17,17,17,DDL语句：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;,ORACLE数据库基本访问语句,CREATEVIEWsie_emp_vASSELECTse.emp_no,se.emp_name,sd.dept_nameFROMsie_empse,sie_deptsdWHEREse.dept_no=sd.dept_no,ALTERTABLEsie_empDROPCONSTRAINTsie_emp_fk1;ALTERTABLEsie_empDROPCOLUMNjob;DROPVIEWsie_emp_v;DROPTABLEsie_emp;,18,18,18,DDL语句：create、alter、drop、truncate总结DDL语句会隐式提交，实质是先COMMIT，再DDL语句；创建主键约束、唯一约束时会默认创建唯一索引；主键约束的字段（或字段组合）不允许NULL值；唯一约束的字段允许NULL值；表中新增字段会出现在表列的最后面；实际开发中一般不创建CHECK、FOREIGNKEY约束；创建表时业务中常为NULL值的列一般放在最后；索引并不是越多越好，索引即有优点又有缺点；养成好的命名习惯、养成使用别名的习惯。,ORACLE数据库基本访问语句,19,19,19,TCL语句：commit、rollback、savepointTCL语句用途及语法COMMIT语句用途：提交事物。在一系列DML后，向数据库提交事务，在执行COMMIT前所有事务按执行先后顺序可见，执行COMMIT后，所有DML操作最终效果才会生效并保持至数据库中。COMMIT语句语法：commit;,ORACLE数据库基本访问语句,20,20,20,TCL语句：commit、rollback、savepointTCL语句用途及语法ROLLBACK语句用途：回滚事物。显式回滚数据库事务，所有DML操作都回滚至最近一次commit后。ROLLBACK语句语法：ROLLBACK;,ORACLE数据库基本访问语句,21,21,21,TCL语句：commit、rollback、savepointTCL语句用途及语法savepoint语句用途：实现部分回滚，回滚至某个设置点。savepoint语句语法：savepointA;-设置回滚点.rollbacktoA;-回滚至设置点,ORACLE数据库基本访问语句,22,22,22,TCL语句：commit、rollback、savepoint总结改动数据库中的数据后，一般使用COMMIT显示提交，使用ROLLBACK显示回滚；注意DDL语句会隐式提交（COMMI）；TCL语句频繁出现会降低效率；一个事务的开始标志是INSERT、UPDATE、DELETE语句的出现；一个事务的结束标志是COMMIT、ROLLBACK的出现；,ORACLE数据库基本访问语句,23,23,23,DCL语句：grant、revokegrant授予权限，revoke收回权限基础权限清单,ORACLE数据库基本访问语句,24,24,24,DCL语句：grant、revoke授予权限grant语法grantprivilegesonobjecttousers;示例：创建sie用户并在scott用户下进行如下操作：授指定表一个权限，给指定用户grantselectonemptosie;授指定表多个权限，给指定用户grantselect,insert,updateonemptosie;授指定表全部权限，给指定用户grantallonemptosie;授权指定表指定权限，给所有用户grantselectonemptopublic;,ORACLE数据库基本访问语句,25,25,25,DCL语句：grant、revoke收回权限revoke语法revokeprivilegesonobjectfromusers;示例：scott用户下进行如下操作：从指定用户收回指定表一个权限revokeselectonempfromsie;从指定用户收回指定表多个权限revokeinsert,update,deleteonempfromsie;从指定用户收回指定表全部权限revokeallonempfromsie;从所有用户收回指定表指定权限revokeselectonempfrompublic;,ORACLE数据库基本访问语句,26,26,26,DCL语句：grant、revoke总结新创建用户没有任何权限，登录数据库的权限也没有；创建一个新的用户至少授予的权限：connect、resource、unlimitedtablespace,ORACLE数据库基本访问语句,27,27,27,Oracle数据库基本访问语句,PL/SQL编程基础,Oracle数据库高级查询,目录,28,28,28,多表连接的类型与实现内连接等值内连接（interjoinon）不等值内连接自然连接（naturaljoin）外连接左外连接（left（outer）joinon）右外连接（right（outer）joinon）全外连接（full（outer）joinon）交叉连接（crosson）,ORACLE数据库高级查询,29,29,29,内连接等值连接select*fromsie_empemp,sie_deptdeptwhereemp.deptid=dept.deptid自然连接selectemp.deptid,emp.*fromsie_empempnaturaljoinsie_deptdept注：同名字段前不能加表名进行限制,ORACLE数据库高级查询,30,30,30,外连接左外连接select*fromsie_empemp,sie_deptdeptwhereemp.deptid=dept.deptid（+）右外连接select*fromsie_empemp,sie_deptdeptwhereemp.deptid（+）=dept.deptid;全外连接select*fromsie_empempfullouterjoinsie_deptdeptonemp.deptid=dept.deptid;,ORACLE数据库高级查询,31,31,31,交叉连接select*fromsie_empemp,sie_deptdept；即笛卡尔积，行数为左边的行数乘以右边的行数。,ORACLE数据库高级查询,32,32,32,子查询概念在一个主SELECT、INSERT、UPDATE、DELETE语句中，嵌套的、用括号括起来的SELECT语句，称为子查询语法相关子查询:子查询中引用外部表非相关子查询:子查询中不引用外部表示例工资大于工号为1002员工工资的员工信息(不相关子查询)Select*FromemployeesemWhereem.salary(SelectsalaryFromemployeesplWherepl.employee_number=1002);,ORACLE数据库高级查询,33,33,33,子查询示例查询有下采购订单的员工(不相关子查询)Select*Fromemployeesem,(SelectDistinctemployee_numberFromorders)vWhereem.employee_number=v.employee_number查询有下采购订单的员工(相关子查询)Select*FromemployeesemWhereem.employee_numberIn(SelectDistinctemployee_numberFromorders)查询有下采购订单的员工(相关子查询)Select*FromemployeesemWhereExists(Select1FromordersodWhereod.employee_number=em.employee_number),ORACLE数据库高级查询,34,34,34,子查询（not）EXISTS用途EXISTS判断子查询是否有数据返回，有则为TURE，否则为FALSE，EXISTS也可用于INSERT、UPDATE、DELETE;语法SELECTcolumnsFROMtablesWHEREEXISTS(subquery);示例SELECT*FROMsuppliersspWHEREEXISTS(select1fromordersodwhereod.supplier_id=sp.supplier_id);SELECT*FROMsuppliersspWHERENOTEXISTS(select*fromordersodwhereod.supplier_id=sp.supplier_id);DELETEFROMsuppliersspWHEREEXISTS(select*fromordersodwhereod.supplier_id=sp.supplier_id);,ORACLE数据库高级查询,35,35,35,子查询Exits和IN的区别Exists是存在，条件就判断为trueIn是需要遍历所有子查询的值一般来说，需要子查询的值比较少时，差别不大，但是，对于数据量大的情况，基本都要求用Exists,ORACLE数据库高级查询,36,36,36,集合运算集合运算符:复合查询UNIONUNIONALLINTERSECTMINUS,ORACLE数据库高级查询,37,37,37,union用途将多个SELECT语句的结果集加起来，去掉重复的数据(集合并运算)，限制：参与UNION的每个SELECT语句的相应列必须是相同数据类型语法selectfield1,field2,.field_nfromtablesUNIONselectfield1,field2,.field_nfromtables;示例selectsupplier_idfromsuppliersUNIONselectsupplier_idfromorders;,ORACLE数据库高级查询,38,38,38,unionall用途将多个SELECT语句的结果集加起来，不去掉重复的数据限制：参与UNIONALL的每个SELECT语句的相应列必须是相同数据类型语法selectfield1,field2,.field_nfromtablesUNIONALLselectfield1,field2,.field_nfromtables;示例selectsupplier_idfromsuppliersUNIONALLselectsupplier_idfromorders;,ORACLE数据库高级查询,39,39,39,INTERSECT用途求多个SELECT的交集(集合交运算)限制：参与INTERSECT的每个SELECT语句的相应列必须是相同数据类型语法selectfield1,field2,.field_nfromtablesINTERSECTselectfield1,field2,.field_nfromtables;示例selectsupplier_idfromsuppliersINTERSECTselectsupplier_idfromorders;,ORACLE数据库高级查询,40,40,40,minus用途求在第一个SELECT，不在第二个SELECT的数据(集合减运算)限制：参与MINUS的每个SELECT语句的相应列必须是相同数据类型语法selectfield1,field2,.field_nfromtablesMINUSselectfield1,field2