文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql-多表查询

2023-01-30 22:04

关注

准备工作:准备两张表,部门表(department)、员工表(employee)

mysql> create database db2;

Query OK, 1 row affected (0.00 sec)


mysql> use db2;

Database changed


创建表

mysql> create table department(id int,name varchar(20));

Query OK, 0 rows affected (0.17 sec)


mysql> 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);

Query OK, 0 rows affected (0.16 sec)


插入数据

mysql> insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');

Query OK, 4 rows affected (0.06 sec)

Records: 4  Duplicates: 0  Warnings: 0


mysql> insert into employee(name,sex,age,dep_id) values('zhangsan','male',18,200),('lisi','female',48,201),('wangwu','male',38,201),('zhuliu','female',28,202),('chenqi','male',18,200),('wangba','female',18,204);

Query OK, 6 rows affected (0.01 sec)

Records: 6  Duplicates: 0  Warnings: 0


查看表结构和数据

mysql> desc department;

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

| Field | Type        | Null | Key | Default | Extra |

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

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)


mysql> select * from department;

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

| id   | name         |

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

|  200 | 技术         |

|  201 | 人力资源     |

|  202 | 销售         |

|  203 | 运营         |

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

4 rows in set (0.00 sec)


mysql> desc employee;

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

| Field  | Type                  | Null | Key | Default | Extra          |

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

| id     | int(11)               | NO   | PRI | NULL    | auto_increment |

| name   | varchar(20)           | YES  |     | NULL    |                |

| sex    | enum('male','female') | NO   |     | male    |                |

| age    | int(11)               | YES  |     | NULL    |                |

| dep_id | int(11)               | YES  |     | NULL    |                |

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

5 rows in set (0.00 sec)


mysql> select * from  employee;

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

| id | name     | sex    | age  | dep_id |

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

|  1 | zhangsan | male   |   18 |    200 |

|  2 | lisi     | female |   48 |    201 |

|  3 | wangwu   | male   |   38 |    201 |

|  4 | zhuliu   | female |   28 |    202 |

|  5 | chenqi   | male   |   18 |    200 |

|  6 | wangba   | female |   18 |    204 |

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

6 rows in set (0.00 sec)

ps:观察两张表,发现department表中id=203部门在employee中没有对应的员工,发现employee中id=6的员工在department表中没有对应关系


多表连接查询

两张表的准备工作已完成,比如现在我要查询的员工信息以及该员工所在的部门。从该题中,我们看出既要查员工又要查该员工的部门,肯定要将两张表进行连接查询,多表连接查询

重点:外链接语法

语法:

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


(1)先看第一种情况交叉连接:不适用任何匹配条件。生成笛卡尔积(关于笛卡尔积的含义,大家百度自行补脑)

mysql> select * from employee,department;

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

| id | name     | sex    | age  | dep_id | id   | name         |

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

|  1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|  1 | zhangsan | male   |   18 |    200 |  201 | 人力资源     |

|  1 | zhangsan | male   |   18 |    200 |  202 | 销售         |

|  1 | zhangsan | male   |   18 |    200 |  203 | 运营         |

|  2 | lisi     | female |   48 |    201 |  200 | 技术         |

|  2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|  2 | lisi     | female |   48 |    201 |  202 | 销售         |

|  2 | lisi     | female |   48 |    201 |  203 | 运营         |

|  3 | wangwu   | male   |   38 |    201 |  200 | 技术         |

|  3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|  3 | wangwu   | male   |   38 |    201 |  202 | 销售         |

|  3 | wangwu   | male   |   38 |    201 |  203 | 运营         |

|  4 | zhuliu   | female |   28 |    202 |  200 | 技术         |

|  4 | zhuliu   | female |   28 |    202 |  201 | 人力资源     |

|  4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|  4 | zhuliu   | female |   28 |    202 |  203 | 运营         |

|  5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|  5 | chenqi   | male   |   18 |    200 |  201 | 人力资源     |

|  5 | chenqi   | male   |   18 |    200 |  202 | 销售         |

|  5 | chenqi   | male   |   18 |    200 |  203 | 运营         |

|  6 | wangba   | female |   18 |    204 |  200 | 技术         |

|  6 | wangba   | female |   18 |    204 |  201 | 人力资源     |

|  6 | wangba   | female |   18 |    204 |  202 | 销售         |

|  6 | wangba   | female |   18 |    204 |  203 | 运营         |

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

24 rows in set (0.11 sec)


(2)内连接:只连接匹配的行

找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了匹配的结果,department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来

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 | zhangsan |   18 | male   | 技术         |

|  2 | lisi     |   48 | female | 人力资源     |

|  3 | wangwu   |   38 | male   | 人力资源     |

|  4 | zhuliu   |   28 | female | 销售         |

|  5 | chenqi   |   18 | male   | 技术         |

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

5 rows in set (0.01 sec)


上述sql等同于

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

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

| id | name     | age  | sex    | name         |

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

|  1 | zhangsan |   18 | male   | 技术         |

|  2 | lisi     |   48 | female | 人力资源     |

|  3 | wangwu   |   38 | male   | 人力资源     |

|  4 | zhuliu   |   28 | female | 销售         |

|  5 | chenqi   |   18 | male   | 技术         |

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

5 rows in set (0.00 sec)


(3)外链接之左连接:优先显示左表全部记录

以左表为准,即找出所有员工信息,当然包括没有部门的员工 ,本质就是:在内连接的基础上增加左边有,右边没有的结果

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  |

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

|  1 | zhangsan | 技术         |

|  5 | chenqi   | 技术         |

|  2 | lisi     | 人力资源     |

|  3 | wangwu   | 人力资源     |

|  4 | zhuliu   | 销售         |

|  6 | wangba   | NULL         |

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

6 rows in set (0.00 sec)


(4) 外链接之右连接:优先显示右表全部记录

以右表为准,即找出所有部门信息,包括没有员工的部门

本质就是:在内连接的基础上增加右边有,左边没有的结果

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 | zhangsan | 技术         |

|    2 | lisi     | 人力资源     |

|    3 | wangwu   | 人力资源     |

|    4 | zhuliu   | 销售         |

|    5 | chenqi   | 技术         |

| NULL | NULL     | 运营         |

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

6 rows in set (0.00 sec)


(5) 全外连接:显示左右两个表全部记录

外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

注意:mysql不支持全外连接 full JOIN

强调:mysql可以使用此种方式间接实现全外连接

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     | sex    | age  | dep_id | id   | name         |

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

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    6 | wangba   | female |   18 |    204 | NULL | NULL         |

| NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |

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

7 rows in set (0.00 sec)


mysql> select * from employee left join department on employee.dep_id = department.id union all select * from employee right join department on employee.dep_id = department.id;

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

| id   | name     | sex    | age  | dep_id | id   | name         |

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

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    6 | wangba   | female |   18 |    204 | NULL | NULL         |

|    1 | zhangsan | male   |   18 |    200 |  200 | 技术         |

|    2 | lisi     | female |   48 |    201 |  201 | 人力资源     |

|    3 | wangwu   | male   |   38 |    201 |  201 | 人力资源     |

|    4 | zhuliu   | female |   28 |    202 |  202 | 销售         |

|    5 | chenqi   | male   |   18 |    200 |  200 | 技术         |

| NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |

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

12 rows in set (0.00 sec)


注意 union与union all的区别:union会去掉相同的纪录



符合条件连接查询

示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

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

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

| name   | name         |

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

| lisi   | 人力资源     |

| wangwu | 人力资源     |

| zhuliu | 销售         |

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

3 rows in set (0.00 sec)


示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示

mysql> select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;

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

| id | name   | age  | name         |

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

|  4 | zhuliu |   28 | 销售         |

|  3 | wangwu |   38 | 人力资源     |

|  2 | lisi   |   48 | 人力资源     |

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

3 rows in set (0.00 sec)



子查询

1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、<等


例子:

(1)带in关键字的子查询

查询平均年龄在25岁以上的部门名

mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); 

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

| id   | name         |

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

|  201 | 人力资源     |

|  202 | 销售         |

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

2 rows in set (0.00 sec)


查看技术部员工姓名

mysql> select name from employee where dep_id in (select id from department where name='技术');

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

| name     |

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

| zhangsan |

| chenqi   |

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

2 rows in set (0.05 sec)


查看不足1人的部门名

mysql> select name from department where id not in (select dep_id from employee group by dep_id);

+--------+

| name   |

+--------+

| 运营   |

+--------+

1 row in set (0.00 sec)



(2)带比较运算符的子查询

比较运算符:=、!=、>、>=、<、<=、<>

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

mysql> select name,age from employee where age > (select avg(age) from employee);

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

| name   | age  |

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

| lisi   |   48 |

| wangwu |   38 |

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

2 rows in set (0.00 sec)


查询大于部门内平均年龄的员工名、年龄

思路:

       (1)先对员工表(employee)中的人员分组(group by),查询出dep_id以及平均年龄。

       (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。

       (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。

mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

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

| name | age  |

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

| lisi |   48 |

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

1 row in set (0.00 sec)



(3)带EXISTS关键字的子查询

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

当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询


department表中存在dept_id=203,Ture

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

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

| id | name     | sex    | age  | dep_id |

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

|  1 | zhangsan | male   |   18 |    200 |

|  2 | lisi     | female |   48 |    201 |

|  3 | wangwu   | male   |   38 |    201 |

|  4 | zhuliu   | female |   28 |    202 |

|  5 | chenqi   | male   |   18 |    200 |

|  6 | wangba   | female |   18 |    204 |

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

6 rows in set (0.00 sec)


department表中存在dept_id=205,False

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

Empty set (0.00 sec)



小练习:

查询每个部门最新入职的那位员工

创建表

mysql> create table info(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') nott null default 'male',age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment vaarchar(100),salary double(15,2),office int, depart_id int);

Query OK, 0 rows affected (0.10 sec)


查看表结构

mysql> desc info;

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

| Field        | Type                  | Null | Key | Default | Extra          |

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

| id           | int(11)               | NO   | PRI | NULL    | auto_increment |

| name         | varchar(20)           | NO   |     | NULL    |                |

| sex          | enum('male','female') | NO   |     | male    |                |

| age          | int(3) unsigned       | NO   |     | 28      |                |

| hire_date    | date                  | NO   |     | NULL    |                |

| post         | varchar(50)           | YES  |     | NULL    |                |

| post_comment | varchar(100)          | YES  |     | NULL    |                |

| salary       | double(15,2)          | YES  |     | NULL    |                |

| office       | int(11)               | YES  |     | NULL    |                |

| depart_id    | int(11)               | YES  |     | NULL    |                |

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

10 rows in set (0.00 sec)


插入记录

三个部门:教学,销售,运营

mysql> insert into info(name,sex,age,hire_date,post,salary,office,depart_id) values

     ('zhangsan','male',18,'20170301','公关部',7300.33,401,1), 

     ('lisi','male',78,'20150302','teacher',1000000.31,401,1),

     ('wangwu','male',81,'20130305','teacher',8300,401,1),

     ('zhuliu','male',73,'20140701','teacher',3500,401,1),

     ('chenqi','male',28,'20121101','teacher',2100,401,1),

     ('wangba','female',18,'20110211','teacher',9000,401,1),

     ('tianjiu','male',18,'19000301','teacher',30000,401,1),

     ('成龙','male',48,'20101111','teacher',10000,401,1),

     

     ('歪歪','female',48,'20150311','sale',3000.13,402,2),

     ('丫丫','female',38,'20101101','sale',2000.35,402,2),

     ('丁丁','female',18,'20110312','sale',1000.37,402,2),

     ('星星','female',18,'20160513','sale',3000.29,402,2),

     ('格格','female',28,'20170127','sale',4000.33,402,2),

     

     ('张野','male',28,'20160311','operation',10000.13,403,3), 

     ('程咬金','male',18,'19970312','operation',20000,403,3),

     ('程咬银','female',18,'20130311','operation',19000,403,3),

     ('程咬铜','male',18,'20150411','operation',18000,403,3),

     ('程咬铁','female',18,'20140512','operation',17000,403,3)

     ;

Query OK, 18 rows affected (0.00 sec)

Records: 18  Duplicates: 0  Warnings: 0


mysql> select t1.post,t1.name,t1.hire_date from info as t1 inner join (select post,max(hire_date) as new_date from info group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_date;

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

| post      | name     | hire_date  |

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

| 公关部    | zhangsan | 2017-03-01 |

| teacher   | lisi     | 2015-03-02 |

| sale      | 格格     | 2017-01-27 |

| operation | 张野     | 2016-03-11 |

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

4 rows in set (0.01 sec)


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯