Oracle数据库学习(4)多表查询

一:笛卡尔积问题

1、笛卡尔积的现象:多个表里的所有记录都会相互匹配一次,如:表1有m条记录,表2有n条记录,则笛卡尔积现象会产生m*n条记录,如:select * from 表1,表2 

2.为了避免笛卡尔积,可以在WHERE加入有效的连接条件,如:select * from 表1,表2 where 表1.id=表2.id

 

二:Oracle的连接

1、内连接:

含义:合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

内连接示意图:

(1)等值连接

方式一:使用=关联表字段

语法:

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

  • 在 WHERE 子句中写入连接条件
  • 在表中有相同列时,在列名之前加上表名前缀

 

例1:两表连接:查询出公司员工的 id,last_name,depatment_id, department_name

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e ,DEPARTMENTS d WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID

 

例2:三表连接:查询出公司员工的 last_name, department_name, city

SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.CITY 
FROM EMPLOYEES e ,DEPARTMENTS d,LOCATIONS l 
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND d.LOCATION_ID=l.LOCATION_ID

 

(2)等值连接

方式二:使用JOIN ON关联字段

例1:两表连接:查询出公司员工的 id,last_name,depatment_id, department_name

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID

 

例2:三表连接:查询出公司员工的 last_name, department_name, city

SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_ID,d.DEPARTMENT_NAME,l.CITY 
FROM EMPLOYEES e JOIN DEPARTMENTS d ON  e.DEPARTMENT_ID=d.DEPARTMENT_ID 
JOIN LOCATIONS l ON d.LOCATION_ID=l.LOCATION_ID

(3)非等值连接

查询员工工资对应的档次

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.SALARY,j.GRADE_LEVEL from EMPLOYEES e,JOB_GRADES j WHERE e.SALARY BETWEEN j.LOWEST_SAL and j.HIGHEST_SAL

 

2、外连接

左外连接:left join 

含义:匹配左表全记录,右边满足条件的记录

示意图:

例子:通过左外连接查询

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e 
LEFT JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

 

右外连接:right join

含义:匹配右表全记录,左边满足条件的记录
示意图:

例子:通过右外连接查询

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e 
RIGHT  JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

满外连接:full join

含义:匹配左、右表全记录

示意图:

例子:通过满外连接查询

SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME FROM EMPLOYEES e 
FULL JOIN DEPARTMENTS d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

 

自连接

含义:表自身连接自身的一种方式

例子:查询员工姓"chen"的Manager信息

SELECT emp.LAST_NAME,manager.LAST_NAME,manager.SALARY,manager.EMAIL FROM EMPLOYEES emp JOIN EMPLOYEES manager ON emp.MANAGER_ID=manager.MANAGER_ID
WHERE LOWER(emp.LAST_NAME)='chen';

 

 

多表查询的案例:

1、显示所有员工的姓名,部门号和部门名称

方式一:使用"="关联

select last_name,e.department_id,department_name from employees e,departments d where e.department_id = d.department_id(+)

方式二:使用Join on关联

select last_name,e.department_id,department_name from employees e left outer join departments d on e.department_id = d.department_id

 

2、查询90号部门员工的job_id和90号部门的location_id

select distinct job_id,location_id from employees e left join departments d
on e.department_id = d.department_id
where d.department_id = 90

 

3、选择所有有奖金的员工的last_name , department_name , location_id , city

select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null

 

4、选择city在Toronto工作的员工的last_name , job_id , department_id , department_name

select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id

 

posted @   筱筱创  阅读(986)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示