数据库之多表查询

阅读目录

一、介绍

文章内容:

  • 多表连接查询
  • 复合条件连接查询
  • 子查询

准备表和记录

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

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

#插入数据
insert into dep values
(200,技术),
(201,人力资源),
(202,销售),
(203,运营)
(205,‘美工‘); insert into emp(name,sex,age,dep_id) values (
jason,male,18,200), (egon,female,48,201), (kevin,male,18,201), (nick,male,28,202), (owen,male,18,203), (jerry,female,18,204);

二、多表连接查询

#重点:外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT|union JOIN 表2
    ON 表1.字段 = 表2.字段;
"""
    inner join  内连接
    left join   左连接
    right join  右连接
    union       全连接
"""

交叉连接,不适用于任何匹配条件,生成笛卡尔积

mysql> select * from dep,emp;     # 结果   笛卡尔积,是把员工表中的每条记录和部门表中的每条记录映射一遍
+------+--------------+----+-------+--------+------+--------+
| id   | name         | id | name  | sex    | age  | dep_id |
+------+--------------+----+-------+--------+------+--------+
|  200 | 技术         |  1 | jason | male   |   18 |    200 |
|  201 | 人力资源     |  1 | jason | male   |   18 |    200 |
|  202 | 销售         |  1 | jason | male   |   18 |    200 |
|  203 | 运营         |  1 | jason | male   |   18 |    200 |
|  200 | 技术         |  2 | egon  | female |   48 |    201 |
|  201 | 人力资源     |  2 | egon  | female |   48 |    201 |
|  202 | 销售         |  2 | egon  | female |   48 |    201 |
|  203 | 运营         |  2 | egon  | female |   48 |    201 |
|  200 | 技术         |  3 | kevin | male   |   18 |    201 |
|  201 | 人力资源     |  3 | kevin | male   |   18 |    201 |
|  202 | 销售         |  3 | kevin | male   |   18 |    201 |
|  203 | 运营         |  3 | kevin | male   |   18 |    201 |
|  200 | 技术         |  4 | nick  | male   |   28 |    202 |
|  201 | 人力资源     |  4 | nick  | male   |   28 |    202 |
|  202 | 销售         |  4 | nick  | male   |   28 |    202 |
|  203 | 运营         |  4 | nick  | male   |   28 |    202 |
|  200 | 技术         |  5 | owen  | male   |   18 |    203 |
|  201 | 人力资源     |  5 | owen  | male   |   18 |    203 |
|  202 | 销售         |  5 | owen  | male   |   18 |    203 |
|  203 | 运营         |  5 | owen  | male   |   18 |    203 |
|  200 | 技术         |  6 | jerry | female |   18 |    204 |
|  201 | 人力资源     |  6 | jerry | female |   18 |    204 |
|  202 | 销售         |  6 | jerry | female |   18 |    204 |
|  203 | 运营         |  6 | jerry | female |   18 |    204 |
+------+--------------+----+-------+--------+------+--------+
24 rows in set (0.00 sec)

mysql>
#那如何low的得到两个表拼接的内容
mysql> select * from emp,dep where emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

mysql>

inner join内连接:只连接匹配的行

# 只拼接两张表中公有的数据部分

mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

mysql> 

left join左连接:优先显示左表全部记录

# 左表所有的数据都展示出来 没有对应的项就用NULL

mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

mysql>

right join右连接:优先显示右表全部记录

# 右表所有的数据都展示出来 没有对应的项就用NULL

mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 美工         |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

mysql> 

union:全连接 左右两表所有的数据都展示出来

select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

mysql> select * from emp left join dep on emp.dep_id = dep.id
    -> union
    -> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 美工         |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)

mysql>

三、符合条件连接查询

#示例1:以内连接的方式查询emp和dep表,并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select emp.name as "员工姓名",dep.name as "部门名称" from emp inner join dep     on emp.dep_id = dep.id     where age > 25;
+--------------+--------------+
| 员工姓名     | 部门名称     |
+--------------+--------------+
| egon         | 人力资源     |
| nick         | 销售         |
+--------------+--------------+
2 rows in set (0.00 sec)

mysql>

#示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示
mysql> select emp.id,emp.name,emp.age,dep.name from emp,dep
    ->     where emp.dep_id = dep.id
    ->     and age > 25
    ->     order by age asc;
+----+------+------+--------------+
| id | name | age  | name         |
+----+------+------+--------------+
|  4 | nick |   28 | 销售         |
|  2 | egon |   48 | 人力资源     |
+----+------+------+--------------+
2 rows in set (0.00 sec)

mysql> 

四、子查询

"""
子查询就是我们平时解决问题的思路
    分步骤解决问题
        第一步
        第二步
        ...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
    1 先获取部门的id号
    2 再去员工表里面筛选出对应的员工
    select id from dep where name=技术 or name = 人力资源;
    
    select name from emp where dep_id in (200,201);
    
    #上面两步等同于子查询:
    select * from emp where dep_id in (select id from dep where name=技术 or name = 人力资源);

mysql> select * from emp where dep_id in (select id from dep where name=技术 or name = 人力资源);
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   18 |    201 |
+----+-------+--------+------+--------+
3 rows in set (0.00 sec)

mysql>

总结:

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联

"""
多表查询就两种方式
    先拼接表再查询
    子查询 一步一步来
"""

 

数据库之多表查询

上一篇:CentOS7安装MySQL


下一篇:instanceof实例和prototype