多表查询(连接查询):
连接查询就是多张数据表进行连接然后查询数据,连接查询分为内连接查询和外连接查询
内连接查询:只有满足条件的数据才会被显示
外连接查询:可以控制不满足条件的数据是否显示
如果要从多张数据表中查询数据,那么就要使用到多表查询
③SELECT *
① FROM 数据来源,数据来源...(可以有多个)
② WHERE 过滤条件
④ ORDER BY 排序的字段ASC|DESC
统计emp表中的数据量
使用统计函数(COUNT(*|字段))
SELECT COUNT(*) AS 人数
FROM emp;
统计dept表中的数据量
SELECT COUNT(*) AS 部门数
FROM dept;
实现多表查询:
SELECT *
FROM emp,dept;
我们查询了两张数据表,本质的目的就是查询雇员的信息以及该雇员的部门信息
查询除了56条数据,其中emp表有14条数据,dept表有4条数据,
4 * 14 = 56,最终多表查询出的数据就是两张表的数量的乘积。
这样的数据很显然存在重复,这种现象叫做笛卡尔积,
在查询的时候消除笛卡尔积,只需增加一个WHERE子句
过滤两张数据表中的关联字段
消除两张数据表的笛卡尔积:
SELECT *
FROM emp,dept
WHERE emp.deptno=dept.deptno;
这种方法只是在显示的时候去掉了笛卡尔积的重复数据,
在数据库执行查询指令的时候,本质上是没有消除笛卡尔积的,
所以在开发中有一个规则:
数据量比较大的时候不要使用连续查询(多表查询),
因为笛卡尔积会产生很大的性能耗费,
数据量小的时候可以使用多表查询
连续查询使用别名:
SELECT *
FROM emp e,dept d
WHERE e.deptno=d.deptno;
别名简化了sql,让其在引用的时候变得简洁
也发现能在WHERE子句中引用FROM子句中定义的别名,
证明了WHERE子句在FROM字句后面执行
查询每个雇员的编号、姓名、职位、工资、部门名称、部门位置:
SELECT empno,ename,job,sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
查询每个雇员的编号、姓名、职位、工资、工资等级 :
SELECT empno,ename,job,sal,grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
查询每个雇员的编号,姓名,职位,工资,工资等级,部门位置和名称:
SELECT empno,ename,job,sal,grade,dname,loc
FROM emp e,salgrade s,dept d
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
为emp表增加一条数据:
INSERT INTO emp(empno,ename,job,sal)
VALUES(1001,'班长','清洁工',3000.00);
查询出雇员的编号,姓名,职位,薪资,部门的位置和部门编号:
SELECT empno,ename,job,sal,loc,d.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno;
查询不到emp表中的班长信息,dept表中的40号部门也没有显示,原因是以上的多表查询是一个内连接查询,需要满足过滤条件的数据才会被显示
外连接查询:
左外连接查询:可以让左表不满足条件的数据显示
右外连接查询:可以让右表不满足条件的数据显示
全外连接查询:可以让左表和右表不满足条件的数据都显示
左外连接查询:
SELECT empno,ename,job,sal,loc,d.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);
右外连接查询:
SELECT empno,ename,job,sal,loc,d.deptno
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;
在过滤语句中使用’+'是oracle中特有的方法
内连接查询:还可以使用关键字INNER JOIN实现
SELECT *
FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
查询每个雇员的编号,姓名,职位,部门名称,部门位置,工资等级:
SELECT empno,ename,job,dname,loc,grade
FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
通用的外连接查询:
使用通用的外连接查询实现左外连接查询:
SELECT *
FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
使用通用的外连接查询实现右外连接查询:
SELECT *
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
全外连接查询:
SELECT *
FROM emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno;
总结:
内连接查询的两种方式:
1.直接在FROM之后跟上多张数据表,在WHERE条件语句中去除笛卡尔积
2.使用INNER JOIN 进行连接,在ON之后去掉笛卡尔积
通用的外连接查询:
左外连接:使用 LEFT OUTER JOIN 实现,在ON之后取出笛卡尔积
右外连接:使用 RIGHT OUTER JOIN 实现,在ON之后取出笛卡尔积
全外连接:使用 FULL OUTER JOIN 实现,在ON之后取出笛卡尔积