ORACLE第三章.ppt

1、第三章多表查询及SQL函数,课程目标,熟练掌握SQL语言进行多表查询 熟练掌握SQL语言进行子查询 会使用基本的数据库函数,SQL语言进行多表查询,多表的查询操作涉及到多个表的连接，如果不加相应的连接条件会出现“笛卡尔连接” 例如：select emp.empno,emp.ename,emp.deptno,dept.dname,dept.loc from scott.emp,scott.dept;,SQL语言进行多表查询,产生笛卡尔连接的原因是没有指明相应的连接条件，应该尽量避免笛卡尔连接的产生，会产生效率上的问题。,带连接条件的等值多表查询,例子1：请你选出雇员的名字，和雇员在部门的名字？

2、错误写法：select ename, deptno from emp;/这里选的是部门的编号，问题里是让选部门的名字 select dname, deptno from dept where deptno = 20;/选出了编号是20的这个人所在部门的名字 正确写法：select ename, dname from emp, dept where emp.deptno = dept.deptno;,带连接条件等值的多表查询,必须明确的指出重复字段是哪个表的 例如： select ename, dname, dept.deptno from emp, dept where emp.deptno

3、= dept.deptno; 注意：指定哪张表的deptno实际上对它有一个效率上的影响,带连接条件非等值的多表查询,例子2：求每个人的薪水值包括他的名字 select ename, sal from emp; 例子3：求每个人的薪水值，名字和他的薪水处于第几个级别(非等值连接) select ename, sal, grade from emp, salgrade where sal between losal and hisa select ename, sal, grade from emp, salgrade where sal = losal and sal = hisal;,带连接

4、条件非等值的多表查询,例子4：求出他的名字，求出他所在部门的名称，求出他的薪水等级 首先分析这几个数据在3张表里 Select ename, dname, grade from emp e, dept d, salgrade s Where e.deptno = d.deptno and e.sal between s.losal and s.hisal;,带连接条件非等值的多表查询,例子5：求出职位除PRESIDENT以外的所有雇员的名字，部门名称，薪水等级 select ename, dname, grade from emp e, dept d, salgrade s where e.d

5、eptno = d.deptno and e.sal = s.losal and e.sal PRESIDENT;,子查询,子查询内嵌在Select语句中的语句，子查询可以嵌套多层，子查询操作的数据表可以是不是真实的表，由其它SQL语句得到的临时表，子查询中不能包含ORDER BY 分组语句。,子查询,例子1：求谁挣的钱最多？ 错误的写法：select ename, max(sal) from emp; 正确的写法：select ename, sal from emp where sal = (select max(sal) from emp);,子查询,例子2：求出来有哪些工资位于所有人平均

6、工资之上 select ename, sal from emp where sal (select avg(sal) from emp);,子查询,例子3：按照部门进行分组之后挣钱最多的那个人的名字，薪水,部门编号？ select ename, sal, t.deptno from emp join(select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); *理解子查询的关键-把它当成一张表,子查询,求部门中哪些人的薪水最高 se

7、lect ename, sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);,自连接,自连接：为同一张表起不同的别名，然后当成两张表来用 例子1：求这个人的名字和他经理人的名字(自连接) select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;,常用SQL函数,1.Lower()函数 将字符串全部转换成小写

8、 例子1：将雇员中名字含有A或a的人员全部显示出来 Select ename from emp where ename like %A%or ename like%a%; Select ename from emp where lower(ename) like %a%;,常用SQL函数,2.Upper()函数将字符串全部转换为大写 例子2：将雇员中名字含有A或a的人员全部显示出来 Select ename from emp where upper(ename) like %A%;,常用SQL函数,round()函数 对参数值进行四舍五入的操作 例子1：对23.652进行四舍五入操作 Sele

9、ct round(23.652) from dual; 24 可以指定四舍五入到小数点后几位 例子2：对23.652四舍五入到小数点后2位 Select round(23.652, 2) from dual; 23.65,常用SQL函数,8.to_date()函数 将特定的字符串转换成日期格式，这个函数有两个参数 第一参数：自定义的日期字符串 第二参数：指定这个字符串的格式 例子1：将1981年3月2日中午以后入职的雇员信息取出： Select * from emp where hiredate to_date(1981-03-02 12:00:00, YYYY-MM-DD HH12:MI:S

10、S);,常用SQL函数,To_char()有两个参数： 第一个参数：需要进行转换的日期或数字 第二个参数：特定的转换格式，对于数字有一下几个格式可以指定： 9 代表数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示 0 代表一位数字，如果该位没有数字则强制显示0 $ 显示美元符号 L 显示本地货币符号 . 显示小数点 , 显示千分位符号,常用SQL函数,例子1：select ename,to_char(sal,$99,999.9999) salary from emp 例子2：select to_char(sal, $00,000.0000) salary from emp

11、 where ename = ALLEN; $01,600.0000,常用SQL函数,对于日期：to_char()可以指定为下面的常用格式： 格式控制符 含义YYYY、YY - 代表4位，2位数字的年份 MM - 用数字表示的月份 MON - 月份的缩写对中文月份来说就是全称 DD - 数字表示的日,常用SQL函数,DY-星期的缩写，对中文的星期来说就是全称 HH24、HH12 - 12小时或者24小时进制下的时间 MI - 分钟数 SS - 秒数,常用SQL函数,有了这些格式，就可以把日期自定义为任何格式 例子1：select to_char(sysdate, YYYY-MM-DD-HH24

12、:MI:SS) from dual; 2008-04-04,常用SQL函数,nvl()函数 用来处理空值，这个函数有两个参数： 第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值 例子：求每个员工每年的年收入(12个月的薪水+津贴) 因为comm的值为null，想要得到正确的结果，必须讲null值转换为0 Select ename, sal*12+nvl(comm, 0) from emp;,常用SQL函数,substr()函数 例1 从第一个字符开始，截取到字符结束 select substr(hello,1,3) from dual; 例2。第三个参数

13、可以不写，表示到字符结束。 select sustr(ename,2) from emp;,常用SQL函数,mod(m,n)函数 用来求M除N后的余数 例1 Select mgr,mod(mgr,100) from scott.emp;,常用SQL函数,.组函数包括5个函数： (1).avg(): 求平均值 (2).max()：求最大值 (3).min()：求最小值 (4).sum()：求总和 (5).count()：求记录的数量,常用SQL函数,例子1：求薪水的总和、平均值、最大值和最小值 Select sum(sal), avg(sal), max(sal), min(sal) from

14、emp; 例子2：求emp表中记录的数量 Select count(*) from emp; Count()可以对单独字段使用，得到的是所有非空记录的数量,常用SQL函数,例子3：求comm字段中所有非空记录的数量 Select count(comm) from emp; Count()可以和distinct一起使用，得到所有唯一值记录的数量,常用SQL函数,例子4：求emp表中deptno唯一的数量 Select count(distinct deptno) from emp; 注意：函数名不是在所有数据库中通用,总结,SQL语言进行多表查询 SQL语言进行子查询 基本的数据库函数,作业,平均薪水最高的部门编号与名称 ？,作业,1.先求每个部门的平均薪水 select avg(sal), deptno from emp group by deptno; 2.拿出最高的值 select max(avg_sal) from (select avg(sal) avg_sal,