oacle数据库培训文档五.docx
《oacle数据库培训文档五.docx》由会员分享，可在线阅读，更多相关《oacle数据库培训文档五.docx（41页珍藏版）》请在冰点文库上搜索。
oacle数据库培训文档五
PLSql培训教程
课程一、PL/SQL基本查询与排序
本课重点：
1、写SELECT语句进行数据库查询
2、进行数学运算
3、处理空值
4、使用别名ALIASES
5、连接列
6、在SQLPLUS中编辑缓冲，修改SQLscriptS
7、ORDERBY进行排序输出。
8、使用WHERE字段。
1．写SQL命令要求：
1）不区分大小写。
2）SQL语句用数字分行，在SQLPLUS中被称为缓冲区。
3）最后以；或/结束语句。
4）也可以用RUN来执行语句
2、例
1：
SQL>SELECTdept_id,last_name,manager_id
2FROMs_emp;
2：
SQL>SELECTlast_name,salary*12,commission_pct
2FROMs_emp;
对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。
SQL>SELECTlast_name,salary,12*（salary+100）
2FROMs_emp;
3、列的别名ALIASES：
计算的时候特别有用；
紧跟着列名，或在列名与别名之间加“AS”；
如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。
例（因字体原因，读者请记住：
引号为英文双引号DoubleQuotation）：
SQL>SELECTlast_name,salary,
212*（salary+100）”AnnualSalary”
3FROMs_emp;
4、连接符号：
||
连接不同的列或连接字符串
使结果成为一个有意义的短语：
SQL>SELECTfirst_name||’’||last_name
2||’,’||title”Employees”
3FROMs_emp;
5、管理NULL值：
SQL>SELECTlast_name,title,
2salary*NVL（commission_pct,0）/100COMM
3FROMs_emp;
此函数使NULL转化为有意义的一个值，相当于替换NULL。
6、ORDERBY操作：
与其他SQL92标准数据库相似，排序如：
SELECTexpr
FROMtable
[ORDERBY[ASC|DESC]];
从Oracle7release7.0.16开始，ORDERBY可以用别名。
另：
通过位置判断排序：
SQL>SELECTlast_name,salary*12
2FROMs_emp
3ORDERBY2;
这样就避免了再写一次很长的表达式。
另：
多列排序：
SQL>SELECTlastname,dept_id,salary
2FROMs_emp
3ORDERBYdept_id,salaryDESC;
7、限制选取行：
SELECTexpr
FROMtable
[WHEREcondition（s）]
[ORDERBYexpr];
例1：
SQL>SELECTfirst_name,last_name,start_date
2FROMs_emp
3WHEREstart_dateBETWEEN’09-may-91’
4AND’17-jun-91’;
例2：
SQL>SELECTlast_name
2FROMs_emp
3WHERElast_nameLIKE’_a%’; //显示所有第二个字母为a的last_name
例3：
如果有列为NULL
SQL>SELECTid,name,credit_rating
2FROMs_customer
3WHEREsales_rep_idISNULL;
优先级：
Order EvaluatedOperator
1 Allcomparisonoperators （=,<>,>,>=,<,<=,IN,LIKE,ISNULL,BETWEEN）
2 AND
3 OR
课程二PL/SQLPL/SQL查询行函数
本课重点：
1、掌握各种在PL/SQL中可用的ROW函数
2、使用这些函数的基本概念
3、SELECT语句中使用函数
4、使用转换函数
注意：
以下实例中标点均为英文半角
1、FUNCTION的作用：
进行数据计算，修改独立的数据，处理一组记录的输出，不同日期显示格式，进行数据类型转换
函数分为：
单独函数（ROW）和分组函数
注意：
可以嵌套、可以在SELECT,WHERE,和ORDERBY中出现。
语法：
function_name（column|expression,[arg1,arg2,...]）
2、字符型函数
1、LOWER转小写
2、UPPER
3、INITCAP 首字母大写
4、CONCAT 连接字符，相当于||
5、SUBSTR SUBSTR（column|expression,m[,n]）
6、LENGTH 返回字符串的长度
7、NVL 转换空值
其中，1、2经常用来排杂，也就是排除插入值的大小写混用的干扰，如：
SQL>SELECTfirst_name,last_name
2FROMs_emp
3WHEREUPPER（last_name）=’PATEL’;
FIRST_NAMELAST_NAME
----------------------------------------
Vikram Patel
Radha Patel
3、数学运算函数
1、ROUND
四舍五入：
ROUND（45.923，2） =45.92
ROUND（45.923，0） =46
ROUND（45.923，-1） =50
2、TRUNC
截取函数
TRUNC（45.923，2）=45.92
TRUNC（45.923）=45
TRUNC（45.923，-1）=40
3、MOD 余除
MOD（1600，300）
实例：
SQL>SELECTROUND（45.923,2）,ROUND（45.923,0）,
2ROUND（45.923,-1）
3FROMSYS.DUAL;
4、ORACLE日期格式和日期型函数：
1、默认格式为DD-MON-YY.
2、SYSDATE是一个求系统时间的函数
3、DUAL［'dju:
el]是一个伪表，有人称之为空表，但不确切。
SQL>SELECTSYSDATE
2FROMSYS.DUAL;
4、日期中应用的算术运算符
例：
SQL>SELECTlast_name,（SYSDATE-start_date）/7WEEKS
2FROMs_emp
3WHEREdept_id=43;
DATE+NUMBER=DATE
DATE-DATE=NUMBEROFDAYS
DATE+（NUMBER/24）=加1小时
5、函数：
MONTHS_BETWEEN（date1,date2）月份间隔，可正，可负，也可是小数
ADD_MONTHS（date,n） 加上N个月，这是一个整数，但可以为负
NEXT_DAY（date,‘char’）如：
NEXT_DAY（restock_date,’FRIDAY’）,从此日起下个周五。
ROUND（date[,‘fmt’]）
TRUNC（date[,‘fmt’]）
解释下面的例子：
SQL>SELECTid,start_date,
2MONTHS_BETWEEN（SYSDATE,start_date）TENURE,
3ADD_MONTHS（start_date,6）REVIEW
4FROMs_emp
5WHEREMONTHS_BETWEEN（SYSDATE,start_date）<48;
我们看到：
MONTHS_BETWEEN（SYSDATE,start_date）<48，说明至今工作未满一年的员工。
LAST_DAY（restock_date）返回本月的最后一天
SQL>selectround（sysdate,'MONTH'）fromdual
ROUND（SYSD
----------
01-11月-01
round（sysdate,'YEAR'）=01-1月-02
ROUND之后的值比基值大的最小符合值，大家可以用更改系统时间的方法测试，以15天为分界线，也是非常形象的四舍五入，而TRUNC恰好相反，是对现有的日期的截取。
6、转换函数：
1）、TO_CHAR
使一个数字或日期转换为CHAR
2）、TO_NUMBER
把字符转换为NUMBER
3）、TO_DATE
字符转换为日期
这几个函数较为简单，但要多多实践，多看复杂的实例。
SQL>SELECTID,TO_CHAR（date_ordered,’MM/YY’）ORDERED
2FROMs_ord
3WHEREsales_rep_id=11;
转换时，要注意正确的缺省格式：
SELECTTO_DATE（'03-MAR-92'）CORRECTFROMDUAL；//正确
SELECTTO_DATE（'031092'）CORRECTFROMDUAL；//不正确
SELECTTO_DATE（'031095','MMDDYY'）ERRORRFROMDUAL
输出3月10日
SELECTTO_DATE（'031095','DDMMYY'）ERRORRFROMDUAL
输出10月3日
4、实例：
selectto_char（sysdate,'fmDDSPTH"of"MONTHYYYYAM'）TODAYSFROMDUAL;
TODAYS
--------------------------------
SIXTEENTHof11月2001下午
大小写没有什么影响，引号中间的是不参与运算。
实例：
SELECTROUND（SALARY*1.25）FROMONE_TABLE；
意义：
涨25%工资后，去除小数位。
在现实操作中，很有意义。
5、混合实例：
SQL>SELECTlast_name,TO_CHAR（start_date,
2’fmDD”of”MonthYYYY’）HIREDATE
3FROMs_emp
4WHEREstart_dateLIKE’%91’;
LAST_NAMEHIREDATE
--------------------------------
Nagayama17ofJune1991
Urguhart18ofJanuary1991
Havel27ofFebruary1991
这里要注意：
fmDD和fmDDSPTH之间的区别。
SQL>SELECTid,total,date_ordered
2FROMs_ord
3WHEREdate_ordered=
4TO_DATE（’September7,1992’,’Monthdd,YYYY’）;
7、独立的函数嵌套
SQL>SELECTCONCAT（UPPER（last_name）,
2SUBSTR（title,3））”VicePresidents”
3FROMs_emp
4WHEREtitleLIKE’VP%’;
*嵌套可以进行到任意深度，从内向外计算。
例：
SQL>SELECTTO_CHAR（NEXT_DAY（ADD_MONTHS
2（date_ordered,6）,’FRIDAY’）,
3’fmDay,Monthddth,YYYY’）
4”New6MonthReview”
5FROMs_ord
6ORDERBYdate_ordered;
SQL>SELECTlast_name,
2NVL（TO_CHAR（manager_id）,’NoManager’）
3FROMs_emp
4WHEREmanager_idISNULL;
对于例子，大家重要的理解，并多做测试，并注意英文版和中文版在日期上的区别。
有些教材上的例子，不要盲目的相信其结果，实践后才有发言权，希望大家能够在学习的过程中不要忽略了用，
多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。
这样，我们才真正掌握了知识。
课程三、从多个表中提取数据
本课重点：
1、SELECTFROM多个表，使用等连接或非等连接
2、使用外连接OUTERJOIN
3、使用自连接
注意：
以下实例中标点均为英文半角
一、连接的概念：
是指一个从多个表中的数据进行的查询。
连接一般使用表的主键和外键。
连接类型：
等连接、不等连接、外连接、自连接
二、Cartesianproduct：
指的是当JOIN条件被省略或无效时，所有表的行（交叉）都被SELECT出来的现象。
Cartesianproduct可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。
SQL>SELECTname,last_name
2FROMs_dept,s_emp;
300rowsselected.其中一个表12行，一个表25行。
三、简单连接查询：
SELECTtable.column,table.column...
FROMtable1,table2
WHEREtable1.column1=table2.column2;
如：
SQL>SELECTs_emp.last_name,s_emp.dept_id,
2s_dept.name
3FROMs_emp,s_dept
4WHEREs_emp.dept_id=s_dept.id;
注意：
表前缀的重要性：
SQL>SELECTs_dept.id”DepartmentID”,
2s_region.id”RegionID”,
3s_region.name”RegionName”
4FROMs_dept,s_region
5WHEREs_dept.region_id=s_region.id;
在WHERE段中，如果没有前缀，两个表中都有ID字段，就显得的模棱两可，AMBIGUOUS。
这在实际中应该尽量避免。
WHERE字段中，还可以有其他的连接条件，如在上例中，加上：
INITCAP（s_dept.last_name）=’Menchu’;
再如：
WHEREs_emp.dept_id=s_dept.idANDs_dept.region_id=s_region.idANDs_mission_pct>0;
四、表别名ALIAS：
1、使用别名进行多表查询。
2、仅在这个查询中生效，一旦用了表别名，就不能再用表的原有的名字进行连接。
实例：
SQL>SELECTc.name”CustomerName”,
2c.region_id”RegionID”,
3r.name”RegionName”
4FROMs_customerc,s_regionr
5WHEREc.region_id=r.id;
别名最多可以30个字符，但当然越少越好。
最好也能容易识别。
五、非等连接
非等连接一般用在没有明确的等量关系的两个表；
最简单的说：
非等连接就是在连接中没有“=”出现的连接。
SQL>SELECTe.ename,e.job,e.sal,s.grade
2FROMempe,salgrades
3WHEREe.salBETWEENs.losalANDs.hisal;
说明：
Createanon-equijointoevaluateanemployee’ssalarygrade.Thesalary必须在另一个表中最高和最低之间。
其他操作符<=>=也可以实现，但是BETWEEN是非常简单实用的。
BETWEEN....AND是指闭区间的，这点要注意，请大家测试。
六、外连接
语法结构：
SELECTtable.column,table.column
FROMtable1,table2
WHEREtable1.column=table2.column（+）;
实例：
SQL>SELECTe.last_name,e.id,c.name
2FROMs_empe,s_customerc
3WHEREe.id（+）=c.sales_rep_id
4ORDERBYe.id;
显示.....，即使有的客户没有销售代表。
*可以理解为有+号的一边出现了NULL，也可以做为合法的条件。
外连接的限制：
1、外连接符只能出现在信息缺少的那边。
2、在条件中，不能用IN或者OR做连接符。
七、自连接
同一个表中使用连接符进行查询；
FROM的后面用同一个表的两个别名。
实例：
SQL>SELECTworker.last_name||’worksfor’||
2manager.last_name
3FROMs_empworker,s_empmanager
4WHEREworker.manager_id=manager.id;
意味着：
一个员工的经理ID匹配了经理的员工号，但这个像绕口令的连接方式并不常用。
以后我们会见到一种子查询：
selectlast_namefroms_empwheresalary=（selectmax（salary）froms_emp）
也可以看作是一种变向的自连接，但通常我们将其
课程四组函数
本课重点：
1、了解可用的组函数
2、说明每个组函数的使用方法
3、使用GROUPBY
4、通过HAVING来限制返回组
注意：
以下实例中标点均为英文半角
一、概念：
组函数是指按每组返回结果的函数。
组函数可以出现在SELECT和HAVING字段中。
GROUPBY把SELECT的结果集分成几个小组。
HAVING来限制返回组，对RESULTSET而言。
二、组函数：
（#号的函数不做重点）
1、AVG
2、COUNT
3、MAX
4、MIN
5、STDDEV #
6、SUM
7、VARIANCE #
语法：
SELECTcolumn,group_function
FROMtable
[WHEREcondition]
[GROUPBYgroup_by_expression]
[HAVINGgroup_condition]
升级会员 