这几天由于测试需要经常在mysql中查数据，但是简单的select语句的用处基本不大，用的最多的是各种join的用法，也就是多表联查，下面简单的介绍一下：
常用的查询的话，大概也就3种，inner join和left join和right join
inner join：取2个表的交集
left join：以左边的表为基础，查询右边的表，在右边的表查询不到的数据，取NULL值
right join：与left join相反
多表查询
-
准备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) -
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) -
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) -
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) -
其他
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)