mysql多表联查

半日闲 2020年07月17日 62次浏览

这几天由于测试需要经常在mysql中查数据,但是简单的select语句的用处基本不大,用的最多的是各种join的用法,也就是多表联查,下面简单的介绍一下:
常用的查询的话,大概也就3种,inner joinleft joinright join
inner join:取2个表的交集

left join:以左边的表为基础,查询右边的表,在右边的表查询不到的数据,取NULL
image.png
right join:与left join相反
image.png

多表查询

  1. 准备2张表

    root@DESKTOP-1RP80G5:~$ mysql -uroot -p123456
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.01 sec)
    
    MariaDB [(none)]> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MariaDB [test]> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | runoob_tbl     |
    | tcount_tbl     |
    +----------------+
    2 rows in set (0.00 sec)
    
    MariaDB [test]> select * from `runoob_tbl`;
    +-----------+---------------+---------------+-----------------+
    | runoob_id | runoob_title  | runoob_author | submission_date |
    +-----------+---------------+---------------+-----------------+
    |         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      |
    |         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      |
    |         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      |
    |         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      |
    |         5 | 学习 C        | FK            | 2017-04-05      |
    +-----------+---------------+---------------+-----------------+
    5 rows in set (0.00 sec)
    
    MariaDB [test]> select * from `tcount_tbl`;
    +---------------+--------------+
    | runoob_author | runoob_count |
    +---------------+--------------+
    | 菜鸟教程      |           10 |
    | RUNOOB.COM    |           20 |
    | Google        |           22 |
    +---------------+--------------+
    3 rows in set (0.00 sec)
    
  2. inner join用法

    1. inner join的查询是查询交集,如果不符合限制条件将返回空
    MariaDB [test]> select `runoob_id`,`runoob_title` from `runoob_tbl` inner join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author` where `runoob_tbl`.`runoob_id`=1;
    +-----------+--------------+
    | runoob_id | runoob_title |
    +-----------+--------------+
    |         1 | 学习 PHP     |
    +-----------+--------------+
    1 row in set (0.00 sec)
    2. 对比以上,当存在限制条件`tcount_tbl`.`runoob_author`='Google'时,`tcount_tbl`和`runoob_tbl`中无符合条件的数据,因此查询后,仍然只有1条返回
    MariaDB [test]> select `runoob_id`,`runoob_title` from `runoob_tbl` inner join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author` where `runoob_tbl`.`runoob_id`=1 or `tcount_tbl`.`runoob_author`='Google';
    +-----------+--------------+
    | runoob_id | runoob_title |
    +-----------+--------------+
    |         1 | 学习 PHP     |
    +-----------+--------------+
    1 row in set (0.01 sec)
    
  3. left join用法

    1. 以左边的表为查询基准,在右边的表查询不到,则对应数据返回NULL
    MariaDB [test]> select `runoob_id`,`runoob_title`,`tcount_tbl`.`runoob_author` from `runoob_tbl` left join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author`;
    +-----------+---------------+---------------+
    | runoob_id | runoob_title  | runoob_author |
    +-----------+---------------+---------------+
    |         1 | 学习 PHP      | 菜鸟教程      |
    |         2 | 学习 MySQL    | 菜鸟教程      |
    |         3 | 学习 Java     | RUNOOB.COM    |
    |         4 | 学习 Python   | RUNOOB.COM    |
    |         5 | 学习 C        | NULL          |
    +-----------+---------------+---------------+
    5 rows in set (0.00 sec)
    
  4. right join用法

    1. 同上相反
    MariaDB [test]> select `runoob_id`,`runoob_title`,`tcount_tbl`.`runoob_author` from `runoob_tbl` right join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author`;
    +-----------+---------------+---------------+
    | runoob_id | runoob_title  | runoob_author |
    +-----------+---------------+---------------+
    |         1 | 学习 PHP      | 菜鸟教程      |
    |         2 | 学习 MySQL    | 菜鸟教程      |
    |         3 | 学习 Java     | RUNOOB.COM    |
    |         4 | 学习 Python   | RUNOOB.COM    |
    |      NULL | NULL          | Google        |
    +-----------+---------------+---------------+
    5 rows in set (0.00 sec)
    
  5. 其他mysql相关语句

    1. 查看建表的语句
    MariaDB [test]> show create table `tcount_tbl`;
    +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table      | Create Table                                                                                                                                                       |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | tcount_tbl | CREATE TABLE `tcount_tbl` (
      `runoob_author` varchar(255) NOT NULL DEFAULT '',
      `runoob_count` int(11) NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    2. 组合条件查询
    MariaDB [test]> select * from (select `runoob_id`,`runoob_title`,`tcount_tbl`.`runoob_author` from `runoob_tbl` right join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author`) as f where`runoob_id`=4;
    +-----------+---------------+---------------+
    | runoob_id | runoob_title  | runoob_author |
    +-----------+---------------+---------------+
    |         4 | 学习 Python   | RUNOOB.COM    |
    +-----------+---------------+---------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select * from (select `runoob_id`,`runoob_title`,`tcount_tbl`.`runoob_author` from `runoob_tbl` right join `tcount_tbl` on `runoob_tbl`.`runoob_author`=`tcount_tbl`.`runoob_author`) as f wheref.`runoob_id`=4;
    +-----------+---------------+---------------+
    | runoob_id | runoob_title  | runoob_author |
    +-----------+---------------+---------------+
    |         4 | 学习 Python   | RUNOOB.COM    |
    +-----------+---------------+---------------+
    1 row in set (0.01 sec)