• MySql


    多表查询

    • 1. 多表连接查询
    • 2.符合条件连接查询
    • 3. 子查询

    数据准备

    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
    (200,'技术'),
    (201,'人力资源'),
    (202,'销售'),
    (203,'运营');
    
    insert into employee(name,sex,age,dep_id) values
    ('egon','male',18,200),
    ('alex','female',48,201),
    ('wupeiqi','male',38,201),
    ('yuanhao','female',28,202),
    ('nvshen','male',18,200),
    ('xiaomage','female',18,204)
    ;
    
    # 查看表结构和数据
    mysql> desc department;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    rows in set (0.19 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    |                |
    +--------+-----------------------+------+-----+---------+----------------+
    rows in set (0.01 sec)
    
    mysql> select * from department;
    +------+--------------+
    | id   | name         |
    +------+--------------+
    |  200 | 技术         |
    |  201 | 人力资源     |
    |  202 | 销售         |
    |  203 | 运营         |
    +------+--------------+
    rows in set (0.02 sec)
    
    mysql> select * from employee;
    +----+----------+--------+------+--------+
    | id | name     | sex    | age  | dep_id |
    +----+----------+--------+------+--------+
    |  1 | egon     | male   |   18 |    200 |
    |  2 | alex     | female |   48 |    201 |
    |  3 | wupeiqi  | male   |   38 |    201 |
    |  4 | yuanhao  | female |   28 |    202 |
    |  5 | nvshen   | male   |   18 |    200 |
    |  6 | xiaomage | female |   18 |    204 |
    +----+----------+--------+------+--------+
    rows in set (0.00 sec)
    数据创建

    1. 多表连接查询

    语法:

    select 字段列表 from 表1 inner|left|right join 表2  on 表1.字段 = 表2.字段;

    1.1 一个概念 : 笛卡尔积

    • 笛卡尔积
      mysql> select * from employee2,department;
      +----+----------+--------+------+--------+------+--------------+
      | id | name     | sex    | age  | dep_id | id   | name         |
      +----+----------+--------+------+--------+------+--------------+
      |  1 | egon     | male   |   18 |    200 |  200 | 技术         |
      |  1 | egon     | male   |   18 |    200 |  201 | 人力资源     |
      |  1 | egon     | male   |   18 |    200 |  202 | 销售         |
      |  1 | egon     | male   |   18 |    200 |  203 | 运营         |
      |  2 | alex     | female |   48 |    201 |  200 | 技术         |
      |  2 | alex     | female |   48 |    201 |  201 | 人力资源     |
      |  2 | alex     | female |   48 |    201 |  202 | 销售         |
      |  2 | alex     | female |   48 |    201 |  203 | 运营         |
      |  3 | wupeiqi  | male   |   38 |    201 |  200 | 技术         |
      |  3 | wupeiqi  | male   |   38 |    201 |  201 | 人力资源     |
      |  3 | wupeiqi  | male   |   38 |    201 |  202 | 销售         |
      |  3 | wupeiqi  | male   |   38 |    201 |  203 | 运营         |
      |  4 | yuanhao  | female |   28 |    202 |  200 | 技术         |
      |  4 | yuanhao  | female |   28 |    202 |  201 | 人力资源     |
      |  4 | yuanhao  | female |   28 |    202 |  202 | 销售         |
      |  4 | yuanhao  | female |   28 |    202 |  203 | 运营         |
      |  5 | nvshen   | male   |   18 |    200 |  200 | 技术         |
      |  5 | nvshen   | male   |   18 |    200 |  201 | 人力资源     |
      |  5 | nvshen   | male   |   18 |    200 |  202 | 销售         |
      |  5 | nvshen   | male   |   18 |    200 |  203 | 运营         |
      |  6 | xiaomage | female |   18 |    204 |  200 | 技术         |
      |  6 | xiaomage | female |   18 |    204 |  201 | 人力资源     |
      |  6 | xiaomage | female |   18 |    204 |  202 | 销售         |
      |  6 | xiaomage | female |   18 |    204 |  203 | 运营         |
      +----+----------+--------+------+--------+------+--------------+
      24 rows in set (0.11 sec)
    • 符合条件查询
      mysql> select * from employee,department where employee.dep_id = department.id;
      +----+---------+--------+------+--------+------+--------------+
      | id | name    | sex    | age  | dep_id | id   | name         |
      +----+---------+--------+------+--------+------+--------------+
      |  1 | egon    | male   |   18 |    200 |  200 | 技术         |
      |  2 | alex    | female |   48 |    201 |  201 | 人力资源     |
      |  3 | wupeiqi | male   |   38 |    201 |  201 | 人力资源     |
      |  4 | yuanhao | female |   28 |    202 |  202 | 销售         |
      |  5 | nvshen  | male   |   18 |    200 |  200 | 技术         |
      +----+---------+--------+------+--------+------+--------------+
      5 rows in set (0.01 sec)

    1.2 内连接

    mysql> select * from employee inner join department on employee.dep_id = department.id;
    +----+---------+--------+------+--------+------+--------------+
    | id | name    | sex    | age  | dep_id | id   | name         |
    +----+---------+--------+------+--------+------+--------------+
    |  1 | egon    | male   |   18 |    200 |  200 | 技术         |
    |  2 | alex    | female |   48 |    201 |  201 | 人力资源     |
    |  3 | wupeiqi | male   |   38 |    201 |  201 | 人力资源     |
    |  4 | yuanhao | female |   28 |    202 |  202 | 销售         |
    |  5 | nvshen  | male   |   18 |    200 |  200 | 技术         |
    +----+---------+--------+------+--------+------+--------------+
    5 rows in set (0.00 sec)

    通过上表可以看出,内连接是找到两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了匹配的结果

    (department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来) -> 与上面的符合条件查询相同

    1.3 左连接或右连接 (优先显示左表或者右表的全部记录)

    • 左连接
      mysql> select * from employee left join department on employee.dep_id = department.id;
      +----+----------+--------+------+--------+------+--------------+
      | id | name     | sex    | age  | dep_id | id   | name         |
      +----+----------+--------+------+--------+------+--------------+
      |  1 | egon     | male   |   18 |    200 |  200 | 技术         |
      |  5 | nvshen   | male   |   18 |    200 |  200 | 技术         |
      |  2 | alex     | female |   48 |    201 |  201 | 人力资源     |
      |  3 | wupeiqi  | male   |   38 |    201 |  201 | 人力资源     |
      |  4 | yuanhao  | female |   28 |    202 |  202 | 销售         |
      |  6 | xiaomage | female |   18 |    204 | NULL | NULL         |
      +----+----------+--------+------+--------+------+--------------+
      6 rows in set (0.00 sec)
    • 右连接
      mysql> select * from employee right join department on employee.dep_id = department.id;
      +------+---------+--------+------+--------+------+--------------+
      | id   | name    | sex    | age  | dep_id | id   | name         |
      +------+---------+--------+------+--------+------+--------------+
      |    1 | egon    | male   |   18 |    200 |  200 | 技术         |
      |    2 | alex    | female |   48 |    201 |  201 | 人力资源     |
      |    3 | wupeiqi | male   |   38 |    201 |  201 | 人力资源     |
      |    4 | yuanhao | female |   28 |    202 |  202 | 销售         |
      |    5 | nvshen  | male   |   18 |    200 |  200 | 技术         |
      | NULL | NULL    | NULL   | NULL |   NULL |  203 | 运营         |
      +------+---------+--------+------+--------+------+--------------+
      6 rows in set (0.00 sec)

    1.4 全外连接  (显示两个表中的全部记录)

    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 | egon     | male   |   18 |    200 |  200 | 技术         |
    |    5 | nvshen   | male   |   18 |    200 |  200 | 技术         |
    |    2 | alex     | female |   48 |    201 |  201 | 人力资源     |
    |    3 | wupeiqi  | male   |   38 |    201 |  201 | 人力资源     |
    |    4 | yuanhao  | female |   28 |    202 |  202 | 销售         |
    |    6 | xiaomage | female |   18 |    204 | NULL | NULL         |
    | NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |
    +------+----------+--------+------+--------+------+--------------+
    7 rows in set (0.01 sec)

    2. 符合条件连接查询

    • 找出年龄大于25岁的员工以及员工所在的部门
      mysql> select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age>25;
      +---------+--------------+
      | name    | name         |
      +---------+--------------+
      | alex    | 人力资源     |
      | wupeiqi | 人力资源     |
      | yuanhao | 销售         |
      +---------+--------------+
      3 rows in set (0.00 sec)
    • 找出年龄大于25岁的员工以及员工所在的部门,并且以age字段的升序方式显示。
      mysql> select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age>25 order by age asc;
      +---------+--------------+
      | name    | name         |
      +---------+--------------+
      | yuanhao | 销售         |
      | wupeiqi | 人力资源     |
      | alex    | 人力资源     |
      +---------+--------------+
      3 rows in set (0.00 sec)

    3. 子查询

    3.1 带in 关键字的子查询(练习题)

    • 查询平均年龄在25岁以上的部门的名称
      mysql> select dep_id from employee group by dep_id having avg(age)>25;
      +--------+
      | dep_id |
      +--------+
      |    201 |
      |    202 |
      +--------+
      2 rows in set (0.38 sec)
      
      mysql> select * 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   |
      +--------+
      | egon   |
      | nvshen |
      +--------+
      2 rows in set (0.00 sec)
    • 查看不足一人的部门名
      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

    3.2 带比较运算符的子查询

  • 相关阅读:
    Loj #6560 小奇取石子
    某谷 P5153 简单的函数
    某谷 P5159 WD与矩阵
    前端ajax访问 django 报错 POST http://127.0.0.1:8001/xxx 403 (Forbidden)
    python
    Java
    Java
    Java
    Java
    java web 向数据库插入中文数据乱码问题
  • 原文地址:https://www.cnblogs.com/wcx666/p/10559522.html
Copyright © 2020-2023  润新知