专栏首页TechBoxMYSQL回顾(多表查询相关)

MYSQL回顾(多表查询相关)

前言

简单的数据我们可以直接从一个表中获取,但在真实的项目中查询符合条件的数据通常需要牵扯到多张表,这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上是多表连接查询+过滤条件。子查询是将一个查询语句嵌套在另一个查询语句中,内层查询语句的查询结果作为外层查询语句的数据源。

准备

# 建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

# 插入数据
insert into department values
(1,'技术'),
(2,'财务'),
(3,'法律’);

# 此处省略员工表数据...

多表连接查询

语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接

在介绍多表查询的时候,有必要先介绍下交叉连接,如下select * from employee, department;查询语句就是交叉连接查询,可以看出,同一个数据在在employee表和department表交叉连接之后产生了重复记录,其重复个数取决于department表的记录个数。所以最后交叉连接之后的记录个数是:count(employee) * count(department),即笛卡尔积。通常情况下,笛卡尔积的结果在工作中无实际意义,我们需要在笛卡尔积的基础上进行筛选,找到employee.dep_id = department.id的那条记录。

mysql> select * from employee, department;

+----+-------+------+--------+----------+-----------+--------+------+--------+
| id | name  | age  | sex    | position | salary    | dep_id | id   | name   |
+----+-------+------+--------+----------+-----------+--------+------+--------+
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    1 | 技术   |
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    2 | 财务   |
|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    1 | 技术   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    2 | 财务   |
|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    1 | 技术   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    2 | 财务   |
|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    3 | 法律   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    1 | 技术   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    2 | 财务   |
|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    3 | 法律   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    1 | 技术   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    2 | 财务   |
|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    3 | 法律   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    1 | 技术   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    2 | 财务   |
|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |    3 | 法律   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    1 | 技术   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    2 | 财务   |
|  7 | emmy  |   27 | female | RD       |      9999 |      1 |    3 | 法律   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    1 | 技术   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    2 | 财务   |
|  8 | emmy  |   23 | female | finance  |      5000 |      2 |    3 | 法律   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    1 | 技术   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    2 | 财务   |
|  9 | lucy  |   45 | female | finance  |     10000 |      2 |    3 | 法律   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    1 | 技术   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    2 | 财务   |
| 10 | emmi  |   20 | female | finance  |     20000 |      2 |    3 | 法律   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    1 | 技术   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    2 | 财务   |
| 11 | james |   18 | male   | NULL     |      3000 |   NULL |    3 | 法律   |
+----+-------+------+--------+----------+-----------+--------+------+--------+
33 rows in set (0.00 sec)

内连接

内连接只会连接两张表匹配的行,即取交集。找两张表公共部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;

+----+-------+------+--------+--------+

| id | name  | age  | sex    | name   |

+----+-------+------+--------+--------+

|  1 | jack  |   20 | male   | 法律   |

|  2 | mark  |   22 | male   | 法律   |

|  3 | hank  |   25 | male   | 法律   |

|  4 | nick  |   39 | male   | 法律   |

|  5 | jenny |   26 | female | 法律   |

|  6 | tony  |   35 | male   | 技术   |

|  7 | emmy  |   27 | female | 技术   |

|  8 | emmy  |   23 | female | 财务   |

|  9 | lucy  |   45 | female | 财务   |

| 10 | emmi  |   20 | female | 财务   |

+----+-------+------+--------+--------+

10 rows in set (0.00 sec)

上述内连接查询语句等同于:

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name 
from employee,department 
where employee.dep_id=department.id;

外连接

外连接分为左连接、右连接、全外连接

左连接

左连接在内连接的基础上优先显示左表全部记录。即左连接=内连接+左表未符合条件的记录

#以左表为准,即找出所有员工信息,当然包括没有部门的员工
#本质就是:在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+-------+-------------+
| id | name  | depart_name |
+----+-------+-------------+
|  6 | tony  | 技术        |
|  7 | emmy  | 技术        |
|  8 | emmy  | 财务        |
|  9 | lucy  | 财务        |
| 10 | emmi  | 财务        |
|  1 | jack  | 法律        |
|  2 | mark  | 法律        |
|  3 | hank  | 法律        |
|  4 | nick  | 法律        |
|  5 | jenny | 法律        |
| 11 | james | NULL        |
+----+-------+-------------+
11 rows in set (0.00 sec)

右连接

又连接在内连接的基础上优先显示右表的内容。即右连接==内连接+右表未符合条件的记录

#以右表为准,即找出所有部门信息,包括没有员工的部门
#本质就是:在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-------+-------------+
| id   | name  | depart_name |
+------+-------+-------------+
|    1 | jack  | 法律        |
|    2 | mark  | 法律        |
|    3 | hank  | 法律        |
|    4 | nick  | 法律        |
|    5 | jenny | 法律        |
|    6 | tony  | 技术        |
|    7 | emmy  | 技术        |
|    8 | emmy  | 财务        |
|    9 | lucy  | 财务        |
|   10 | emmi  | 财务        |
+------+-------+-------------+
10 rows in set (0.00 sec)

可以发现,左表(employee表)的第11条记录没有被查询出来

全外连接

全外连接会在内连接查询的基础上显示左表和右表的全部记录

mysql> select * from employee left join department on employee.dep_id = department.id
    -> union
    -> select * from employee right join department on employee.dep_id = department.id
    -> ;
+------+-------+------+--------+----------+-----------+--------+------+--------+
| id   | name  | age  | sex    | position | salary    | dep_id | id   | name   |
+------+-------+------+--------+----------+-----------+--------+------+--------+
|    6 | tony  |   35 | male   | RD       | 100000000 |      1 |    1 | 技术   |
|    7 | emmy  |   27 | female | RD       |      9999 |      1 |    1 | 技术   |
|    8 | emmy  |   23 | female | finance  |      5000 |      2 |    2 | 财务   |
|    9 | lucy  |   45 | female | finance  |     10000 |      2 |    2 | 财务   |
|   10 | emmi  |   20 | female | finance  |     20000 |      2 |    2 | 财务   |
|    1 | jack  |   20 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|    2 | mark  |   22 | male   | lawyer   |    888889 |      3 |    3 | 法律   |
|    3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |    3 | 法律   |
|    4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |    3 | 法律   |
|    5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |    3 | 法律   |
|   11 | james |   18 | male   | NULL     |      3000 |   NULL | NULL | NULL   |
| NULL | NULL  | NULL | NULL   | NULL     |      NULL |   NULL | NULL | 人力   |
+------+-------+------+--------+----------+-----------+--------+------+--------+
12 rows in set (0.00 sec)

符合条件链接查询

mysql> select employee.name, employee.age, department.name from employee inner join department
    -> on employee.dep_id=department.id
    -> where age > 30
    -> order by age asc;
+------+------+--------+
| name | age  | name   |
+------+------+--------+
| tony |   35 | 技术   |
| nick |   39 | 法律   |
| lucy |   45 | 财务   |
+------+------+--------+
3 rows in set (0.00 sec)

子查询

子查询是将一个查询语句的嵌套在另一个查询语句中 内层查询语句的查询结果作为外层查询语句的数据源 子查询中可以包含 IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字 注意被嵌套的查询语句需要用( )包裹

查询员工部门平均年龄大于30的部门信息

mysql> select * from department
    -> where id in
    -> (select dep_id from employee group by dep_id having avg(age) > 30);
+------+--------+
| id   | name   |
+------+--------+
|    1 | 技术   |
+------+--------+
1 row in set (0.00 sec)

查询技术部员工姓名

mysql> select name from employee
    -> where dep_id in
    -> (select id from department where name="技术");
+------+
| name |
+------+
| tony |
| emmy |
+------+
2 rows in set (0.00 sec)

查询无员工的部门名(子查询得到的是所有人的部门id,需要disctinct去除)

mysql> select name from department
    -> where id not in
    -> (select distinct dep_id from employee);

带比较运算符的子查询

查询大于所有人平均年龄的员工名和年龄

mysql> select * from employee where age > (select avg(age) from employee);
+----+------+------+--------+----------+-----------+--------+
| id | name | age  | sex    | position | salary    | dep_id |
+----+------+------+--------+----------+-----------+--------+
|  4 | nick |   39 | male   | lawyer   |   4438890 |      3 |
|  6 | tony |   35 | male   | RD       | 100000000 |      1 |
|  9 | lucy |   45 | female | finance  |     10000 |      2 |
+----+------+------+--------+----------+-----------+--------+
3 rows in set (0.00 sec)

不能这样:在前面没有group by的时后面不能使用分组函数 mysql> select * from employee where age > avg(age); ERROR 1111 (HY000): Invalid use of group function

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。 而是返回一个真假值。True或False 当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询

mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);

MYSQL中可以把一个查询语句用括号括起来使用as起一个别名当做一个表使用 查询每个职位最新入职的员工

SELECT
    *
FROM
    emp AS t1
INNER JOIN (
    SELECT
        post,
        max(hire_date) max_date
    FROM
        emp
    GROUP BY
        post
) AS t2 ON t1.post = t2.post
WHERE
    t1.hire_date = t2.max_date;

查询语句关键字执行顺序

一个完整的mysql的查询语句如下:

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

其关键字执行顺序如下:

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MYSQL回顾(表关系相关)

    比如有两个表,分别是书籍表和出版社表。书籍和出版社是典型的多对一关系,即一本书只能由一个出版社出版,一个出版社可以出版多本书。则书籍表应该有一个外键press_...

    VV木公子
  • 为什么不能在init和dealloc函数中使用accessor方法前言为什么不能在init中调用accessor为什么不能在dealloc中调用accessor结尾

    VV木公子
  • 跟着官方文档学习3D Touch前言(一)app外3D Touch—Home Screen Quick Actions(二)app内的3D Touch - Peek and Pop名词解释

    VV木公子
  • SpringMVC+Hibernate +MySql+ EasyUI实现CRUD(一)

    最新项目下载地址 访问地址 ? 1.基于easyui的 增 删 改 查 2.基于poi的导出excel 3.基于 SpringMVC HandlerInte...

    小帅丶
  • Spring+SpringMVC+Hibernate简单整合(转)

    SpringMVC又一个漂亮的web框架,他与Struts2并驾齐驱,Struts出世早而占据了一定优势,下面同样做一个简单的应用实例,介绍SpringMVC的...

    yaohong
  • Hibernate多表关系配置

    映射文件中普通字段段的配置不用写上外键,在一的一方配置文件写上set标签name属性为关联属性名,里面还有key标签有column属性关系中的外键即另一个表的外...

    木瓜煲鸡脚
  • django+pymysql搭建一个管理系统(一)

    小小咸鱼YwY
  • Python Sleep休眠函数

    def fun(name):     write_name="command %s failed!\n" % name     print write_n...

    py3study
  • django管理系统代码优化-分组(二)

    优化前代码:https://www.cnblogs.com/pythonywy/p/11345626.html

    小小咸鱼YwY
  • ICLR 2020 | 受启诺奖研究,利用格网细胞学习多尺度表达(视频解读)

    本文对由美国加州大学圣巴巴拉分校完成的,被ICLR 2020录用的论文《Multi-Scale Representation Learning for Spat...

    AI科技评论

扫码关注云+社区

领取腾讯云代金券