Oracle学习——第三讲（函数及连接）

Oracle 通用表

/*

emp 表                             dept  部门表                 salgrade 表

empno 员工编号               deptno  部门编号          grade  工资等级

ename  员工姓名             dname  部门名称          losal    工资范围下限

job       职位                      loc 工作地点                 hisal   工资范围上限

mgr     经理编号

hiredate  入职日期

sal        薪金

comm  奖金

deptno  部门编号

*/

Oracle实例演练

--需要用到双引号“”的地方：

--1、取列别名，需要区分大小写

--2、包含空格

--3、包含特殊字符

--4、转换函数中需要特意添加的字符（to_char, to_number, to_date）

--在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，

--如果工资大于等于1000并小于2000，税率为10％，

-- 如果工资大于等于2000并小于3000，税率为15％，

--如果工资大于等于3000，税率为20％.

--第一种

Select ename,sal,(case trunc(sal/1000)

                                             When 0 then 0

                                                When 1 then sal*0.1

                                                    When 2 then sal*0.15

                                                       Else sal*0.2 end) 应交税款

From emp

--第二种

SELECT ename, sal, DECODE(trunc(sal/1000),

                                             0,0,

                                             1, sal * 0.1,

                                             2, sal * 0.15,

                                             sal * 0.2) 应交税款

From emp

--自身连接

--查询员工姓名，以及他的直接上级姓名

SELECT e.ename, m.ename

FROM emp e, emp m

WHERE e.mgr = m.empno

1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号

SELECT e.ename, e.empno, m.ename, m.empno

FROM emp e, emp m, dept d

WHERE e.mgr = m.empno

AND e.deptno = d.deptno

AND loc IN('NEW TORK', 'CHICAGO')

2.第上一题的基础上，添加没有经理的员工King,并按照员工编号排序。

SELECT e.ename, e.empno, m.ename, m.empno

FROM emp e, emp m, dept d

WHERE e.mgr = m.empno(+)

AND e.deptno = d.deptno

AND loc IN('NEW TORK', 'CHICAGO')

ORDER BY e.empno ASC

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

SELECT empno, ename, dname

FROM emp e, dept d

WHERE e.deptno = d.deptno(+)

--交叉连接(即笛卡尔积现象)

--cross join

SELECT ename， dname

FROM emp

CROSS join dept

--自然连接

--natural join

--用两张表当中的完全相同（同名称，同数据类型）的列作等值连接

SELECT ename, dname

FROM emp

natural join dept

--using子句(连接过程中指定哪一列)

--备注：只能写列名（不能用别名，前缀）

SELECT ename, dname

FROM emp

join dept using (deptno)

--on子句(用于指定连接条件，类似于where, 其他条件用WHERE)

--查询员工姓名，工资等级以及所在的部门名称，经理姓名

SELECT e.ename 员工姓名, dname 部门名称, grade 工资等级, m.ename 经理姓名

FROM emp e

join dept d

on e.deptno = d.deptno               

JOIN salgrade

ON sal between losal and hisal   

JOIN emp m

on e.mgr = m.empno

WHERE loc = 'CHICAGO'     

 --左外连接

 --left outer join

 --以左边的表为主表，如果右边的表匹配不上，右边出空行

 --以emp表为主表，无论匹配情况如何，都显示出来（相当于右边dept  加（+））

 SELECT ename, dname

 FROM emp e left outer join dept d

 on e.deptno = d.deptno

 --右外连接

 --right outer join

  SELECT ename, dname

 FROM dept d right outer join emp e 

 on e.deptno = d.deptno

 --全外连接

 --full outer join(相当于两边都加（+）：两边都是主表)

   SELECT ename, dname

 FROM dept d right outer join emp e 

 on e.deptno = d.deptno

 --显示员工KING和FORD管理的员工姓名及其经理姓名

 -- 员工姓名  经理姓名  经理的经理姓名

  SELECT e.ename 员工姓名, m.ename 经理姓名, mm.ename 经理的经理姓名

  FROM emp e, emp m, emp mm

  WHERE e.mgr = m.empno

  AND m.mgr =  mm.empno(+)

  AND m.ename IN('KING', 'FORD')

  --利用左外连接或右外连接(以e，m为主表)

  SELECT e.ename 员工姓名, m.ename 经理姓名, mm.ename 经理的经理姓名

  FROM emp e

  join emp m

  on e.mgr = m.empno

  LEFT OUTER JOIN emp mm

  ON m.mgr =  mm.empno

  WHERE m.ename IN('KING', 'FORD')

  --分组函数

 -- SELECT FROM  WHERE  ORDER BY四个子句

 --常用的分组函数

 --MAX  MIN(参数可以为任意类型)

 --查询员工工资的最大值

 SELECT MAX(sal), MIN(sal)

 FROM emp

 SELECT MAX(hiredate), MIN(hiredate)

 FROM emp

 SELECT MAX(ename), MIN(ename)

 FROM emp

 --SUM   AVG(仅针对数值型):空值不参与运算

 SELECT SUM(sal), AVG(sal)

 FROM emp

 where deptno = 10

 SELECT AVG(NVL(comm, 0))    --15条记录

 FROM emp

 --COUNT(返回记录的条数):空值不参与运算

 SELECT COUNT(*)

 FROM emp

 WHERE deptno = 10

 SELECT COUNT(comm)

 FROM emp

 --查询员工的工作有几种

 SELECT COUNT(DISTINCT job)

 FROM emp

 --查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT COUNT(empno), MAX(sal), MIN(sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

AND loc = 'CHICAGO'

--查询每个部门的平均工资？

--GROUP BY 按照...进行分组

--除了分组函数外，SELECT后，必须要写进去

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

--查询每个部门的平均工资，并且按着升序排序

SELECT d.deptno, dname, AVG(sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.deptno, dname

ORDER BY AVG(sal) ASC

--查询每个部门每个岗位的工资总和

SELECT deptno, job, SUM(sal)

FROM emp

GROUP BY deptno, job

--3.查询每个经理所管理的人数，经理编号，经理

--姓名，要求包括没有经理的人员信息。

SELECT COUNT(e.empno), e.mgr, m.ename

FROM emp e, emp m

WHERE e.mgr = m.empno(+)

GROUP bY e.mgr, m.ename

--排除组信息

--WHERE子句中不可出现分组函数: 指定分组前的条件

--HAVING 子句                :限定分组后条件

--执行顺序：1-6

SELECT deptno, AVG(sal)          5

FROM emp                                 1

WHERE deptno <> 20               2

GROUP BY deptno                     3

Having AVG(sal) > 2500            4            

ORDER BY AVG(sal)                   6

--查询部门平均工资大于2000，且人数大于2的

--部门编号，部门名称，部门人数，部门平均工资，

--并按照部门人数升序排序。

SELECT d.deptno, dname, COUNT(empno), AVG(sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.deptno, dname

Having AVG(sal) > 2000 AND COUNT(empno) > 2

ORDER BY COUNT(empno) ASC

--插入一行数据(有待考证)

INSERT INTO emp(empno)  VALUES  (9999)

--删除一行

DELETE  FROM emp  WHERE empno = 9555