Oracle数据库基础SQL语句(三)—高级查询（多表查询）

Oracle的高级查询之多表查询

1. 多表查询：从多各表中获取数据

2. 笛卡尔集

注：在实际运行环境下，应避免使用笛卡尔全集，where中加入有效的连接条件

3. 等值连接

查询员工信息，要求显示：员工号，姓名，月薪，部门名称

SELECT e.EMPNO,e.ENAME,e.SAL,d.DNAME
from EMP e,DEPT d  //多表查询时一般习惯起个别名，不起也没关系
WHERE e.DEPTNO=d.DEPTNO; //=表示等值连接

4. 不等值连接

查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别

-- SELECT * from SALGRADE;//有五个级别（Oracle自带的表）
SELECT e.EMPNO,e.ENAME,e.SAL,s.GRADE
from EMP e,SALGRADE s 
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL;
//此处的between and 等价于>=和<=（但是要注意and前是小数，后边是大数） 明显的不等值条件

**

5. 外连接

**
按部门统计员工人数，要求显示：部门号，部门名称，人数

SELECT d.DEPTNO 部门号,d.DNAME 部门名称,COUNT(e.EMPNO) 人数
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO
GROUP BY d.DEPTNO,d.DNAME;

思考：答案对吗？

SELECT COUNT(*) FROM EMP;

emmmm…（抛去第一行，应该还有14才对，可上边才有13）

SELECT * FROM DEPT;

发现有四个部门，emmmmmm

外连接至此正式登场

外连接：把对于连接条件不成立的记录，仍然包含在最后的结果中
在Oracle数据库中分

• 左外链接：连接条件不成立时，等号左边的表依然被包含
• 右外链接：连接条件不成立时，等号右边的表依然被包含
  
  实例中应该用右外连接

SELECT d.DEPTNO 部门号,d.DNAME 部门名称,COUNT(e.EMPNO) 人数
FROM EMP e,DEPT d
WHERE e.DEPTNO(+)=d.DEPTNO//书写与叫法相反，右外连接在等号左边加上（+）
GROUP BY d.DEPTNO,d.DNAME;

**

6. 自连接

**
查询员工姓名和员工老板的姓名
核心：通过别名，将一张表视为多张表

SELECT e.ENAME 员工姓名,d.ENAME 老板姓名
from EMP e,EMP d  //核心
WHERE e.MGR=d.EMPNO;

思考：自连接最少把一张表看成两张表，得到的是一个笛卡尔全集，所以自连接不适合操作大表

解决方法：层次查询

7. 层次查询

层次查询：某些情况下可取代自连接，本质上是单表查询

SELECT LEVEL,EMPNO,ENAME,SAL,MGR  //伪列 进行层次查询时，会自动生成它，也就是树的深度
from EMP
CONNECT BY PRIOR EMPNO=MGR  //查询条件
START WITH EMPNO=7839  //此行说明开始查询的起始节点，如果是根节点可以写成START WITH MGR IS NULL
ORDER BY 1;  // 对第一行的level进行排序（默认为升序）

相对于自连接的缺点：不是那么直观，没有最好的，只有最合适的，以后工作中也应该多注意