Java Web开发_MySQL_多表查询（Day8）

目录

系列文章

前言

多表查询

一、连接查询

1、内连接

2、外连接

二、子查询

1、标量子查询

2、列子查询

3、行子查询

4、表子查询

小结

---

系列文章

Java Web开发（Day1）

Java Web开发（Day2）

Java Web开发_异步处理以及前端中Vue框架的简单使用（Day3）

Java Web开发_Maven以及SpringBootWeb入门（Day4）

Java Web开发_请求响应与分层解耦（Day5）

Java Web开发_数据库_MySQL（Day6）

Java Web开发_MySQL_DQL_多表设计（Day7）

前言

此博客主要记录在学习黑马程序员2023版JavaWeb开发课程的一些笔记，方便总结以及复习。

多表查询

多表查询：从多张表中查询数据。

select * from [表1],[表2],[表3],[...];

直接查询是将两个表运用笛卡尔积组合在一起。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

代码实例操作：

select *
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;

根据查询的形式可以将多表查询分为两大类：连接查询、子查询。

一、连接查询

1、内连接

隐式内连接： select  字段列表   from   表 1 , 表 2   where   条件 ... ;

显式内连接： select  字段列表   from   表 1  [ inner ]  join 表 2  on  连接条件 ... ;

内连接：相当于查询A、B交集部分数据

代码实例操作：

-- 内连接 查询的是两表该属性有连接的部分，如果是没有连接的话会直接省略
select tb_emp.name, tb_dept.name
from tb_dept,
     tb_emp
where tb_emp.dept_id = tb_dept.id;

-- 起别名
select e.name, d.name
from tb_emp e,
     tb_dept d
where e.dept_id = d.id;

-- 显示内连接,inner可不写
select tb_emp.name, tb_dept.name
from tb_emp
         inner join tb_dept on tb_emp.dept_id = tb_dept.id;

2、外连接

外连接和内连接的主要区别就是：内连接是根据双方有的值进行一个连接，而外连接中，以对照表的情况，选择其中一边的值进行连接，可以接受对照表中的值为null。

• 左外连接：查询左表所有数据(包括两张表交集部分数据)

左外连接： select  字段列表   from   表 1  left  [ outer ]   join 表 2  on 连接条件 ... ;

• 右外连接：查询右表所有数据(包括两张表交集部分数据)

右外连接： select  字段列表   from   表 1  right  [ outer ]   join 表 2  on  连接条件 ... ;

代码实例操作：

-- 外连接
-- 左外连接
select e.name, d.name
from tb_emp e
         left join tb_dept d on e.dept_id = d.id;

-- 右外连接
select e.name, d.name
from tb_emp e
         right join tb_dept d on e.dept_id = d.id;

select e.name, d.name
from tb_dept d
         left join tb_emp e on e.dept_id = d.id;

二、子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

select  *  from   t1   where  column1 =  ( select  column1  from  t2  … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

可以再细分为四种：

1. 标量子查询：子查询返回的结果为单个值
2. 列子查询：子查询返回的结果为一列
3. 行子查询：子查询返回的结果为一行
4. 表子查询：子查询返回的结果为多行多列

1、标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），是最简单的形式；常用的操作符：=   <>    >     >=     <    <=     。

代码实例操作：

-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
select *
from tb_emp
where dept_id = (select id
                 from tb_dept
                 where name = '教研部');
-- a. 查询教研部的部门id
select id
from tb_dept
where name = '教研部';
-- b. 再查询该部门id下的员工信息
select *
from tb_emp
where dept_id = 2;

-- B. 查询在 "方东白" 入职之后的员工信息
select *
from tb_emp
where entrydate > (select entrydate from tb_emp where name = '方东白');

-- a. 查询 方东白 的入职时间
select entrydate
from tb_emp
where name = '方东白';

-- b. 查询在 "方东白" 入职之后的员工信息
select *
from tb_emp
where entrydate > 2012 - 11 - 01;

2、列子查询

子查询返回的结果是一列（可以是多行）；常用的操作符：in  、not  、 in等。

代码实例操作：

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
select *
from tb_emp
where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
-- a. 查询 "教研部" 和 "咨询部" 的部门ID - tb_dept
select id
from tb_dept
where name = '教研部'
   or name = '咨询部';

-- b. 根据部门ID, 查询该部门下的员工信息 - tb_emp
select *
from tb_emp
where dept_id in (3, 2);

3、行子查询

子查询返回的结果是一行（可以是多列）；常用的操作符：=  、<> 、in 、not  in。

代码实例操作：

-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
-- 方式一,使用了两次查询效率较低
select *
from tb_emp
where entrydate = (select entrydate from tb_emp where name = '韦一笑')
  and job = (select job from tb_emp where name = '韦一笑');
-- 方式二
select *
from tb_emp
where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑');

-- a. 查询 "韦一笑" 的入职日期 及 职位
select entrydate, job
from tb_emp
where name = '韦一笑';

-- b. 查询与其入职日期 及 职位都相同的员工信息 ;
select *
from tb_emp
where entrydate = '2007-01-01'
  and job = 2;

4、表子查询

子查询返回的结果是多行多列，常作为临时表；常用的操作符：in。

 代码实例操作：

-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a. 查询入职日期是 "2006-01-01" 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';

-- b. 查询这部分员工信息及其部门名称 - tb_dept
select e.* , d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;

小结

先前有提到，在表结构中，物理外键对数据库开发很不友好，在阿里巴巴中更甚已经直接禁止使用物理外键了，而这节课学习的多表查询可以近似地理解为逻辑外键，我们查询的时候可以通过选择表的属性来达到我们想要查询的数据，那既然如此，熟悉多表查询的语法就变得至关重要。