PLSQL循序渐进全面学习教程Word文件下载.docx
《PLSQL循序渐进全面学习教程Word文件下载.docx》由会员分享,可在线阅读,更多相关《PLSQL循序渐进全面学习教程Word文件下载.docx(46页珍藏版)》请在冰豆网上搜索。
||
连接不同的列或连接字符串
使结果成为一个有意义的短语:
SELECTfirst_name||’’||last_name||’,’||title”Employees”FROMs_emp
selectdivid||'
'
||divnamefrompub_t_division_testwheresuperid='
001'
效果如下图:
五、管理NULL值:
SELECTlast_name,title,salary*NVL(commission_pct,0)/100COMMFROMs_emp;
此函数使NULL转化为有意义的一个值,相当于替换NULL。
selectdivid,divname,NVL(addr,0)frompub_t_division_testwheresuperid='
六、SQLPLUS的基本内容,请参考<
SQLPLUS简单实用精髓篇>
七、ORDERBY操作:
与其他SQL92标准数据库相似,排序如:
SELECTexpr FROMtable [ORDERBY{column,expr}[ASC|DESC]];
从Oracle7release7.0.16开始,ORDERBY可以用别名。
另:
通过位置判断排序:
SELECTlast_name,salary*12FROMs_empORDERBY2;
select*frompub_t_division_testwheresuperid='
orderby3
这样就避免了再写一次很长的表达式。
多列排序:
SELECTlastname,dept_id,salaryFROMs_empORDERBYdept_id,salaryDESC;
orderby1,3desc
八、限制选取行:
SELECTexpr FROMtable [WHEREcondition(s)] [ORDERBYexpr];
例1:
SELECTfirst_name,last_name,start_dateFROMs_empWHEREstart_dateBETWEEN’09-may-91
AND’17-jun-91’;
例2:
SELECTlast_nameFROMs_empWHERElast_nameLIKE’_a%’;
//显示所有第二个字母为a的last_name,第一个字母’_’为一个占位符
例3:
如果有列为NULL
SELECTid,name,credit_ratingFROMs_customerWHEREsales_rep_idISNULL;
优先级:
Order EvaluatedOperator
1 Allcomparisonoperators (=,<
>
>
=,<
<
=,IN,LIKE,ISNULL,BETWEEN)
2 AND
3 OR
总结:
我们今天主要学习了如何进行查询SELECT操作,具体的组合查询与子查询将在以后的课堂中学习,同时希望大家可以工作、学习中多多摸索,实践!
======================================================================
课程二PL/SQL查询行函数
1、掌握各种在PL/SQL中可用的ROW函数
2、使用这些函数的基本概念
3、SELECT语句中使用函数
4、使用转换函数
注意:
以下实例中标点均为英文半角
一、FUNCTION的作用:
进行数据计算,修改独立的数据,处理一组记录的输出,不同日期显示格式,进行数据类型转换
函数分为:
单独函数(ROW)和分组函数
可以嵌套、可以在SELECT,WHERE,和ORDERBY中出现。
语法:
function_name(column|expression,[arg1,arg2,...])
二、字符型函数
1、LOWER转小写
2、UPPER
3、INITCAP 首字母大写
4、CONCAT 连接字符,相当于||
5、SUBSTR SUBSTR(column|expression,m[,n])
6、LENGTH 返回字符串的长度
7、NVL 转换空值
其中,1、2经常用来排杂,也就是排除插入值的大小写混用的干扰,如:
SELECTfirst_name,last_nameFROMs_empWHEREUPPER(last_name)=’PATEL’;
FIRST_NAMELAST_NAME
Vikram Patel
Radha Patel
三、数学运算函数
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)
实例:
SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMSYS.DUAL;
四、ORACLE日期格式和日期型函数:
1、默认格式为DD-MON-YY.
2、SYSDATE是一个求系统时间的函数
3、DUAL['
dju:
el]是一个伪表,有人称之为空表,但不确切。
SELECTSYSDATEFROMSYS.DUAL;
4、日期中应用的算术运算符
例:
SELECTlast_name,(SYSDATE-start_date)/7WEEKSFROMs_empWHEREdept_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’])
解释下面的例子:
SELECTid,start_date,MONTHS_BETWEEN(SYSDATE,start_date)TENURE,
ADD_MONTHS(start_date,6)REVIEW
FROMs_empWHEREMONTHS_BETWEEN(SYSDATE,start_date)<
48;
我们看到:
MONTHS_BETWEEN(SYSDATE,start_date)<
48,说明至今工作未满一年的员工。
LAST_DAY(restock_date)返回本月的最后一天
selectround(sysdate,'
MONTH'
)fromdual
ROUND(SYSD
----------
01-11月-01
round(sysdate,'
YEAR'
)=01-1月-02
ROUND之后的值比基值大的最小符合值,大家可以用更改系统时间的方法测试,以15天为分界线,也是
非常形象的四舍五入,而TRUNC恰好相反,是对现有的日期的截取。
五、转换函数:
1、TO_CHAR
使一个数字或日期转换为CHAR
2、TO_NUMBER
把字符转换为NUMBER
3、TO_DATE
字符转换为日期
这几个函数较为简单,但要多多实践,多看复杂的实例。
SELECTID,TO_CHAR(date_ordered,’MM/YY’)ORDEREDFROMs_ordWHEREsales_rep_id=11;
转换时,要注意正确的缺省格式:
SELECTTO_DATE('
03-MAR-92'
)CORRECTFROMDUAL;
//正确
031092'
//不正确
031095'
'
MMDDYY'
)ERRORRFROMDUAL
输出3月10日
DDMMYY'
输出10月3日
4、实例:
selectto_char(sysdate,'
fmDDSPTH"
of"
MONTHYYYYAM'
)TODAYSFROMDUAL;
大小写没有什么影响,引号中间的是不参与运算。
实例:
SELECTROUND(SALARY*1.25)FROMONE_TABLE;
意义:
涨25%工资后,去除小数位。
在现实操作中,很有意义。
5、混合实例:
SELECTlast_name,TO_CHAR(start_date,’fmDD”of”MonthYYYY’)HIREDATEFROMs_emp
WHEREstart_dateLIKE’%91’;
LAST_NAMEHIREDATE
Nagayama17ofJune1991
Urguhart18ofJanuary1991
Havel27ofFebruary1991
这里要注意:
fmDD和fmDDSPTH之间的区别。
SELECTid,total,date_orderedFROMs_ordWHEREdate_ordered=TO_DATE(’September7,1992’,’
Monthdd,YYYY’);
六、独立的函数嵌套
SELECTCONCAT(UPPER(last_name),SUBSTR(title,3))”VicePresidents”FROMs_emp
WHEREtitleLIKE’VP%’;
*嵌套可以进行到任意深度,从内向外计算。
SELECTTO_CHAR(NEXT_DAY(ADD_MONTHS (date_ordered,6),’FRIDAY’),
’fmDay,Monthddth,YYYY’)”New6MonthReview”FROMs_ordORDERBYdate_ordered;
SELECTlast_name,NVL(TO_CHAR(manager_id),’NoManager’)FROMs_emp
WHEREmanager_idISNULL;
对于例子,大家重要的理解,并多做测试,并注意英文版和中文版在日期上的区别。
有些教材上的例子,不要盲目的相信其结果,实践后才有发言权,希望大家能够在学习的过程中不要忽略了
用,多想一想为什么实例要如此设计,在何种情况下应用此实例来解决问题。
这样,我们才真正掌握了知识。
=====================================================================
课程三从多个表中提取数据
1、SELECTFROM多个表,使用等连接或非等连接
2、使用外连接OUTERJOIN
3、使用自连接
注意:
一、连接的概念:
是指一个从多个表中的数据进行的查询。
连接一般使用表的主键和外键。
连接类型:
等连接、不等连接、外连接、自连接
二、Cartesianproduct:
指的是当JOIN条件被省略或无效时,所有表的行(交叉)都被SELECT出来的现象。
Cartesianproduct可以产生大量的记录,除非是你有意如此,否则应该加上某种条件限制。
SELECTname,last_nameFROMs_dept,s_emp;
300rowsselected.其中一个表12行,一个表25行。
三、简单连接查询:
SELECTtable.column,table.column...
FROMtable1,table2
WHEREtable1.column1=table2.column2;
如:
SELECTs_emp.last_name,s_emp.dept_id, s_dept.nameFROMs_emp,s_dept
WHEREs_emp.dept_id=s_dept.id;
表前缀的重要性:
SELECTs_dept.id”DepartmentID”,
s_region.id”RegionID”,
s_region.name”RegionName”
FROMs_dept,s_region
WHEREs_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、仅在这个查询中生效,一旦用了表别名,就不能再用表的原有的名字进行连接。
SELECTc.name”CustomerName”,
c.region_id”RegionID”,
r.name”RegionName”
FROMs_customerc,s_regionr
WHEREc.region_id=r.id;
别名最多可以30个字符,但当然越少越好。
最好也能容易识别。
五、非等连接
非等连接一般用在没有明确的等量关系的两个表;
最简单的说:
非等连接就是在连接中没有“=”出现的连接。
SELECTe.ename,e.job,e.sal,s.grade
FROMempe,salgrades WHEREe.salBETWEENs.losalANDs.hisal;
说明:
Createanon-equijointoevaluateanemployee’ssalarygrade.Thesalary必须在另一个表中最高
和最低之间。
其他操作符<
=>
=也可以实现,但是BETWEEN是非常简单实用的。
BETWEEN....AND是指闭区间的,这点要注意,请大家测试。
六、外连接
语法结构:
SELECTtable.column,table.column
WHEREtable1.column=table2.column(+);
SELECTe.last_name,e.id,c.name
FROMs_empe,s_customerc
WHEREe.id(+)=c.sales_rep_id
ORDERBYe.id;
显示.....,即使有的客户没有销售代表。
*可以理解为有+号的一边出现了NULL,也可以做为合法的条件。
外连接的限制:
1、外连接符只能出现在信息缺少的那边。
2、在条件中,不能用IN或者OR做连接符。
七、自连接
同一个表中使用连接符进行查询;
FROM的后面用同一个表的两个别名。
SELECTworker.last_name||’worksfor’||
manager.last_name
FROMs_empworker,s_empmanager
WHEREworker.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]
[ORDERBYcolumn];
实例1:
一个混合实例,说明所有问题:
SELECTAVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROMs_emp
WHEREUPPER(title)LIKE’SALES%’;
AVG(SALARY)MAX(SALARY)MIN(SALARY)SUM(SALARY)
--------------------------------------------
1476 1525 1400 7380
很多函数,我们在讲函数的已经向大家介绍过,但在此为何叫分组函数呢,主要是因为它们可以与GROUP
BY来形成对不同组的计算,相当于在很多值中进行挑选。
*MINMAX函数可以接任何数据类型。
如果是MIN(last_name),MAX(last_name),返回的是什么呢?
千万记住,不是指LAST_NAME的长度,而是指在FIRST字母的前后顺序,第一个相同,然后比较第二个,
如:
xdopt
>
cssingkdkdk >
adop