黑马程序员技术交流社区

标题: 【石家庄校区】SQL多表查询详解 [打印本页]

---

作者: wqertiorquoer    时间: 2018-6-27 13:56
标题: 【石家庄校区】SQL多表查询详解

什么是多表连接

       数据库是由多张表组成的,单表查询可以满足基本的查询工作,但是在复杂的应用场景下仍然有不足.多表联合查询就出现了,作为关系型数据库的SQL提供了功能非常强大的多表查询功能,SQL使得多张表格之前形成有效的数据联系.

多表连接的好处

1.      减少数据冗余,去除掉多余的数据,可以通过建立表之间的外键连接关系完成.

2.      使得数据更新正确,不能因为表之间存在关系后,使得纪录出现不正常的数据

3.      查询更加灵活,虽然操作稍微复杂下,如果熟悉后就能使我们更快速获得我们想要的数据

表与表之间的关系

Ÿ   一对一的关系
例:一个学号对应一个学生
Ÿ   一对多的关系
例:一个公司对应多个员工
Ÿ   多对多的关系
例:一个学生可以选择多门课程,一门课程可以被多个学生选择

什么是多表查询

      简单的说,多表查询就是当你查询一张表不能满意需求,需要从多张表中取出某些数据的时候,你就需要使用多表查询来联合两张或多张表的数据.

如何使用多表查询

       和单表查询类似,根据不同的查询方式,分为子查询和连接查询,而子查询和连接查询根据表于表之间连接方式不同又有多种不同的查询方式

连接查询

Ÿ   交叉连接
Ÿ   内连接
    显示内连接
    隐式内连接
Ÿ   外连接
    左外连接
    右外连接
子查询
Ÿ   in
Ÿ   exists
Ÿ   any
Ÿ   all

下面我们一一来说明一下各种查询方式的不同
先创建表和表结构及数据:

//先创建一个表 t_dept

create table t_dept(

         _idint primarykey,

         deptnoint(11),

         dnamevarchar(20),

         loc varchar(50));

insert into t_dept (_id,deptno,dname,loc) values ('1','10','ACCOUNTING','NEWYORK'),

('2','20','RESEARCH','DALLAS'),

('3','30','SALES','CHICAGO'),

('4','40','OPERATIONS','BOSTON');

交叉查询—Cross Join(不常用)

使用语法
l  select * from 表1 cross join 表2;
l  select * from 表1,表2;

查询结果:两张表的笛卡尔积

select * fromt_employee,t_dept;

结果如下


[td]  

mpno	ename	job	mgr	hiredate	sal	comm	deptno	_id	deptno	dname	loc
7369	SMITH	CLERK	7902	2011-03-12	800	null	20	1	10	ACCOUNTING	NEW YORK
7369	SMITH	CLERK	7902	2011-03-12	800	null	20	2	20	RESEARCH	DALLAS
7369	SMITH	CLERK	7902	2011-03-12	800	null	20	3	30	SALES	CHICAGO
7369	SMITH	CLERK	7902	2011-03-12	800	null	20	4	40	OPERATIONS	BOSTON
7499	ALLEN	SALESMAN	7698	2012-03-12	1600	300	30	1	10	ACCOUNTING	NEW YORK
7499	ALLEN	SALESMAN	7698	2012-03-12	1600	300	30	2	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	7698	2012-03-12	1600	300	30	3	30	SALES	CHICAGO
7499	ALLEN	SALESMAN	7698	2012-03-12	1600	300	30	4	40	OPERATIONS	BOSTON

从结果可以看出，两张表中的每条记录都进行了重组，导致最终查询结果的条数为两张表中的记录数的乘积，这样的结果积在数学上称为笛卡尔积。简单比喻下就是所有可能的组合都进行了组合。

内连接(inner join)

Ÿ   显示内连接：在SQL中显示的调用innerjoin关键字

select * from table1 inner join table2 on [condition];

Ÿ   隐式内连接：在SQL中没有调用innerjoin关键字

select * from table1,tabl2where [condition];

代码实现
隐式内连接
select t1.ename,t1.job,t2.ename from      t_employee t1,      t_employee t2 where        t1.mgr = t2.empno;

显示外连接
select t1.ename,t1.job,t2.enamefrom t_employee t1inner join t_employee t2 on t1.mgr = t2.empno;

·       查询每个员工的编号、姓名、职位、部门名称和部门所在位置

selectt1.empno,t1.ename,t1.job,t2.dname,t2.loc

from t_employee t1

inner join t_dept t2 on t1. deptno =t2.deptno;

---------------------------------- or------------------------------------select t1.empno,t1.ename,t1.job,t2.dname,t2.locfrom t_employee t1,t_dept t2 where t1.deptno = t2.deptno;

·       查询每个员工的编号、姓名、基本工资、职位、领导姓名、部门名称和部门所在位置

select t1.empno,t1.ename,t1.sal,t1.job,t2.ename asmgr_name,t3.dname,t3.loc
from t_employee t1,t_employee t2,t_dept t3 where t1.mgr = t2.empno andt1.deptno = t3.deptno;

外连接

Ÿ   左外连接
select * from table1 left outer join table2 on condition;
Ÿ   右外连接
select * from table1 right outer join table2 on condition;

左外连接
返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

Ÿ   查询每个员工的姓名、职位和领导姓名

代码示例

select t1.ename,t1.job,t2.ename as mgr_name

from t_employee t1

left join t_employee t2 on t1.mgr = t2.empno;

结果
[td]  

ename	job	mgr_name
SCOTT	ANALYST	JONES
KING	PRESIDENT	null

右外连接
恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

Ÿ   查询每个员工的编号、姓名、职位和部门名称和部门位置

selectt1.empno,t1.ename,t1.job,t2.dname,t2.loc

from t_employee t1

right join t_dept t2 on t1.deptno =t2.deptno;

结果
[td]  

empno	ename	job	dname	loc
7900	JAMES	CLERK	SALES	CHICAGO
null	null	null	OPERATIONS	BOSTON

什么是子查询
子查询说通俗一点，即是嵌套查询,就是说一个语句需要从另一个语句的查询结果中再次进行筛选,从而获得想要的数据,举个例子,比如说查询比SIMITH工资还要高的员工共信息,我们要先获取SIMITH的工资是多少,然后在根据SIMITH的工资找到工资高于SIMITH的员工信息,可以看下代码是如何实现的

select *

from t_employee t

where t.sal > (

          select sal

          from t_employee

          where ename = 'SMITH');

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2