ORACLE第三章

1、第三章多表查询及第三章多表查询及SQLSQL函数函数熟练掌握熟练掌握SQL语言进行多表查询语言进行多表查询熟练掌握熟练掌握SQL语言进行子查询语言进行子查询会使用基本的数据库函数会使用基本的数据库函数SQL语言进行多表查询语言进行多表查询多表的查询操作涉及到多个表的连接，如果不加相多表的查询操作涉及到多个表的连接，如果不加相应的连接条件会出现应的连接条件会出现“笛卡尔连接笛卡尔连接”例如：例如：select emp.empno,emp.ename,emp.deptno,dept.dname,dept.loc from scott.emp,scott.dept;SQL语言进行多表查询语言进行多表

2、查询产生笛卡尔连接的原因是没有指明相应产生笛卡尔连接的原因是没有指明相应的连接条件，应该尽量避免笛卡尔连接的连接条件，应该尽量避免笛卡尔连接的产生，会产生效率上的问题。的产生，会产生效率上的问题。带连接条件的等值多表查询带连接条件的等值多表查询例子例子1：请你选出雇员的名字，和雇员在部：请你选出雇员的名字，和雇员在部门的名字？门的名字？错误写法：错误写法：select ename, deptno from emp;/这里选的是部门的编号，问题里是让这里选的是部门的编号，问题里是让选部门的名字选部门的名字select dname, deptno from dept where deptno =

3、20;/选出了编号是选出了编号是20的这的这个人所在部门的名字个人所在部门的名字正确写法：正确写法：select ename, dname from emp, dept where emp.deptno = dept.deptno;带连接条件等值的多表查询带连接条件等值的多表查询必须明确的指出重复字段是哪个表的必须明确的指出重复字段是哪个表的 例如：例如：select ename, dname, dept.deptno from emp, dept where emp.deptno = dept.deptno;注意：注意：指定哪张表的指定哪张表的deptno实际上对它有实际上对它有一个效率上的

4、影响一个效率上的影响带连接条件非等值的多表查询带连接条件非等值的多表查询例子例子2：求每个人的薪水值包括他的名字：求每个人的薪水值包括他的名字 select ename, sal from emp;例子例子3：求每个人的薪水值，名字和他的：求每个人的薪水值，名字和他的薪水处于第几个级别薪水处于第几个级别(非等值连接非等值连接)select ename, sal, grade from emp, salgrade where sal between losal and hisaselect ename, sal, grade from emp, salgradewhere sal = losal

5、 and sal = s.losal and e.sal = s.hisal and job PRESIDENT;子查询子查询子查询内嵌在子查询内嵌在Select语句中的语句，子查询语句中的语句，子查询可以嵌套多层，子查询操作的数据表可以是可以嵌套多层，子查询操作的数据表可以是不是真实的表，由其它不是真实的表，由其它SQL语句得到的临时语句得到的临时表，子查询中不能包含表，子查询中不能包含ORDER BY 分组语分组语句。句。子查询子查询例子例子1：求谁挣的钱最多？：求谁挣的钱最多？错误的写法：错误的写法：select ename, max(sal) from emp;正确的写法：正确的写法：

6、select ename, sal from emp where sal = (select max(sal) from emp);子查询子查询例子例子2：求出来有哪些工资位于所有人：求出来有哪些工资位于所有人平均工资之上平均工资之上 select ename, sal from emp where sal (select avg(sal) from emp);子查询子查询例子例子3：按照部门进行分组之后挣钱最多的：按照部门进行分组之后挣钱最多的那个人的名字，薪水那个人的名字，薪水,部门编号？部门编号？select ename, sal, t.deptno from emp join(sele

7、ct max(sal) max_sal, deptno from emp group by deptno) ton (emp.sal = t.max_sal and emp.deptno = t.deptno);*理解子查询的关键理解子查询的关键-把它当成一张表把它当成一张表子查询子查询求部门中哪些人的薪水最高求部门中哪些人的薪水最高select ename, sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) ton (emp.sal = t.max_sal and emp.deptno =

8、 t.deptno); 自连接自连接自连接：为同一张表起不同的别名，然自连接：为同一张表起不同的别名，然后当成两张表来用后当成两张表来用例子例子1：求这个人的名字和他经理人的：求这个人的名字和他经理人的名字名字(自连接自连接)select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;常用SQL函数 1.Lower()函数函数 将字符串全部转换成将字符串全部转换成小写小写例子例子1：将雇员中名字含有：将雇员中名字含有A或或a的人员全部显示出来的人员全部显示出来Select ename from emp where

9、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进行四舍五入操作进行四舍五入操作Sel

10、ect 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日中午以后入职日中午以后入职的

11、雇员信息取出：的雇员信息取出：Select * from emp where hiredate to_date(1981-03-02 12:00:00, YYYY-MM-DD HH12:MI:SS);常用SQL函数 To_char()有两个参数：有两个参数：第一个参数：需要进行转换的日期或数字第一个参数：需要进行转换的日期或数字第二个参数：特定的转换格式，对于数字有第二个参数：特定的转换格式，对于数字有一下几个格式可以指定：一下几个格式可以指定：9 代表数字，如果该位没有数字则不进行代表数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显显示，但对于小数点后面的部分仍会强制显示

12、示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 where ename = ALLEN; $01,600.0000常用SQL函数 对于日期：对于日期：to_char()可以指定为下面的常用格式

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

14、select to_char(sysdate, YYYY-MM-DD-HH24:MI:SS) from dual; 2008-04-04常用SQL函数 nvl()函数函数用来处理空值，这个函数有两个参数：用来处理空值，这个函数有两个参数：第一参数：字段名或表达式，如果这个参数第一参数：字段名或表达式，如果这个参数值为值为null，就返回第二参数值，否则返回第，就返回第二参数值，否则返回第一参数值一参数值例子：求每个员工每年的年收入例子：求每个员工每年的年收入(12个月的个月的薪水薪水+津贴津贴)因为因为comm的值为的值为null，想要得到正确的结，想要得到正确的结果，必须讲果，必须讲null

15、值转换为值转换为0Select ename, sal*12+nvl(comm, 0) from emp;常用SQL函数 substr()函数函数例例1 从第一个字符开始，截取到字符结束从第一个字符开始，截取到字符结束 select substr(hello,1,3) from dual;例例2。第三个参数可以不写，表示到字符结。第三个参数可以不写，表示到字符结束。束。 select sustr(ename,2) from emp;常用SQL函数 mod(m,n)函数函数用来求用来求M除除N后的余数后的余数例例1Select mgr,mod(mgr,100) from scott.emp;常用S

16、QL函数 .组函数包括组函数包括5个函数：个函数：(1).avg(): 求平均值求平均值(2).max()：求最大值：求最大值(3).min()：求最小值：求最小值(4).sum()：求总和：求总和(5).count()：求记录的数量：求记录的数量常用SQL函数 例子例子1：求薪水的总和、平均值、最大：求薪水的总和、平均值、最大值和最小值值和最小值Select sum(sal), avg(sal), max(sal), min(sal) from emp; 例子例子2：求：求emp表中记录的数量表中记录的数量Select count(*) from emp;Count()可以对单独字段使用，得

17、到的是所可以对单独字段使用，得到的是所有非空记录的数量有非空记录的数量 常用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.拿出最高的值拿出最高的值