PLSQL学习简易快速入门.docx
《PLSQL学习简易快速入门.docx》由会员分享,可在线阅读,更多相关《PLSQL学习简易快速入门.docx(42页珍藏版)》请在冰点文库上搜索。
PLSQL学习简易快速入门
课程 一 PL/SQL 基本查询与排序
本课重点:
1、写SELECT语句进行数据库查询
2、进行数学运算
3、处理空值
4、使用别名ALIASES
5、连接列
6、在SQLPLUS中编辑缓冲,修改SQLSCRIPTS
7、ORDERBY进行排序输出。
8、使用WHERE 字段。
一、写SQL 命令:
不区分大小写。
SQL 语句用数字分行,在SQLPLUS中被称为缓冲区。
最后以;或 / 结束语句。
也可以用RUN来执行语句
二、例1:
SQL> SELECTdept_id,last_name,manager_id FROMs_emp;
2:
SQL> SELECTlast_name,salary*12,commission_pct FROMs_emp;
对于数值或日期型的字段,可以进行相应的四则运算,优先级与标准的高级语言相同。
SQL>SELECTlast_name,salary,12*(salary+100) FROMs_emp;
三、列的别名ALIASES:
计算的时候特别有用;
紧跟着列名,或在列名与别名之间加“AS”;
如果别名中含有SPACE,特殊字符,或大小写,要用双引号引起。
例(因字体原因,读者请记住:
引号为英文双引号DoubleQuotation):
SQL>SELECTlast_name,salary,12*(salary+100) ”AnnualSalary” FROMs_emp;
四、连接符号:
||
连接不同的列或连接字符串
使结果成为一个有意义的短语:
SQL> SELECTfirst_name|| ’ ’ ||last_name|| ’, ’||title ”Employees” FROMs_emp
SQL> selectdivid||''||divnamefrompub_t_division_testwheresuperid='001'
效果如下图:
五、管理NULL值:
SQL> SELECTlast_name,title,salary*NVL(commission_pct,0)/100COMMFROMs_emp;
此函数使NULL转化为有意义的一个值,相当于替换NULL。
selectdivid,divname,NVL(addr,0)frompub_t_division_testwheresuperid='001'
效果如下图:
六、SQLPLUS的基本内容,请参考
七、ORDERBY 操作:
与其他SQL92标准数据库相似,排序如:
SELECTexpr FROMtable [ORDERBY{column,expr}[ASC|DESC]];
从Oracle7release 7.0.16开始,ORDERBY 可以用别名。
另:
通过位置判断排序:
SQL> SELECT last_name,salary*12 FROMs_emp ORDERBY2;
select * from pub_t_division_test where superid='001' order by 3
这样就避免了再写一次很长的表达式。
另:
多列排序:
SQL> SELECTlastname,dept_id,salary FROMs_emp ORDERBYdept_id,salaryDESC;
SQL>select*frompub_t_division_testwheresuperid='001' orderby1,3 desc
八、限制选取行:
SELECTexpr FROMtable [WHEREcondition(s)] [ORDERBYexpr];
例1:
SQL> SELECTfirst_name,last_name,start_dateFROMs_emp WHEREstart_dateBETWEEN ’09-may-91
AND ’17-jun-91’;
例2:
SQL>SELECTlast_nameFROMs_empWHERElast_nameLIKE ’_a%’;
//显示所有第二个字母为 a的last_name,第一个字母’_’为一个占位符
例3:
如果有列为NULL
SQL> 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经常用来排杂,也就是排除插入值的大小写混用的干扰,如:
SQL> SELECTfirst_name,last_nameFROM s_emp WHEREUPPER(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)
实例:
SQL> 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] 是一个伪表,有人称之为空表,但不确切。
SQL> SELECTSYSDATEFROMSYS.DUAL;
4、日期中应用的算术运算符
例:
SQL> 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’])
解释下面的例子:
SQL> 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) 返回本月的最后一天
SQL>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
字符转换为日期
这几个函数较为简单,但要多多实践,多看复杂的实例。
SQL> SELECTID,TO_CHAR(date_ordered,’MM/YY’)ORDEREDFROMs_ord WHEREsales_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、实例:
SQL>selectto_char(sysdate,'fmDDSPTH"of"MONTHYYYYAM')TODAYSFROMDUAL;
大小写没有什么影响,引号中间的是不参与运算。
实例 :
SQL>SELECTROUND(SALARY*1.25)FROMONE_TABLE;
意义:
涨25%工资后,去除小数位。
在现实操作中,很有意义。
5、混合实例:
SQL> SELECTlast_name,TO_CHAR(start_date,’fmDD ”of” MonthYYYY’)HIREDATEFROMs_emp
WHEREstart_dateLIKE ’%91’;
LAST_NAMEHIREDATE
Nagayama17ofJune1991
Urguhart18ofJanuary1991
Havel27ofFebruary1991
这里要注意:
fmDD 和 fmDDSPTH之间的区别。
SQL> SELECTid,total,date_orderedFROMs_ordWHEREdate_ordered=TO_DATE(’September7,1992’,’
Monthdd,YYYY’);
六、独立的函数嵌套
SQL> SELECTCONCAT(UPPER(last_name),SUBSTR(title,3)) ”VicePresidents” FROMs_emp
WHEREtitleLIKE ’VP%’;
* 嵌套可以进行到任意深度,从内向外计算。
例:
SQL> SELECTTO_CHAR(NEXT_DAY(ADD_MONTHS (date_ordered,6),’FRIDAY’),
’fmDay,Monthddth,YYYY’) ”New6MonthReview” FROMs_ordORDERBYdate_ordered;
SQL> SELECTlast_name,NVL(TO_CHAR(manager_id),’NoManager’)FROMs_emp
WHEREmanager_idIS NULL;
对于例子,大家重要的理解,并多做测试,并注意英文版和中文版在日期上的区别。
有些教材上的例子,不要盲目的相信其结果,实践后才有发言权,希望大家能够在学习的过程中不要忽略了
用,多想一想为什么实例要如此设计,在何种情况下应用此实例来解决问题。
这样,我们才真正掌握了知识。
=====================================================================
课程三 从多个表中提取数据
本课重点:
1、SELECTFROM 多个表,使用等连接或非等连接
2、使用外连接OUTERJOIN
3、使用自连接
注意:
以下实例中标点均为英文半角
一、连接的概念:
是指一个从多个表中的数据进行的查询。
连接一般使用表的主键和外键。
连接类型:
等连接、不等连接、外连接、自连接
二、Cartesianproduct :
指的是当JOIN条件被省略或无效时,所有表的行(交叉)都被SELECT出来的现象。
Cartesianproduct可以产生大量的记录,除非是你有意如此,否则应该加上某种条件限制。
SQL> SELECTname,last_nameFROMs_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, s_dept.name FROMs_emp,s_dept
WHEREs_emp.dept_id=s_dept.id;
注意:
表前缀的重要性:
SQL> 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、仅在这个查询中生效,一旦用了表别名,就不能再用表的原有的名字进行连接。
实例:
SQL> SELECTc.name ”CustomerName”,
c.region_id ”RegionID”,
r.name ”RegionName”
FROMs_customerc,s_regionr
WHEREc.region_id=r.id;
别名最多可以30个字符,但当然越少越好。
最好也能容易识别。
五、非等连接
非等连接一般用在没有明确的等量关系的两个表;
最简单的说:
非等连接就是在连接中没有“=”出现的连接。
SQL> 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
FROMtable1,table2
WHEREtable1.column=table2.column(+);
实例:
SQL> 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 的后面用同一个表的两个别名。
实例:
SQL> 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:
一个混合实例,说明所有问题:
SQL> SELECTAVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROMs_emp
WHEREUP