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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性