初始化表

mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(200),
    -> order_id int,
    -> sex enum('male', 'female')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table orders(
    -> id int primary key auto_increment,
    -> info varchar(250),
    -> create_time datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | int                   | NO   | PRI | NULL    | auto_increment |
| name     | varchar(200)          | YES  |     | NULL    |                |
| order_id | int                   | YES  |     | NULL    |                |
| sex      | enum('male','female') | YES  |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc orders;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| info        | varchar(250) | YES  |     | NULL    |                |
| create_time | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into user (name, order_id, sex) values
    -> ('ronie', 1, 'male'),
    -> ('kelly', 2, 'female'),
    -> ('mike', null, 'male');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into orders (info, create_time) values 
    -> ('fish', current_date()),
    -> ('meat', current_date());
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+-------+----------+--------+
| id | name  | order_id | sex    |
+----+-------+----------+--------+
|  1 | ronie |        1 | male   |
|  2 | kelly |        2 | female |
|  3 | mike  |     NULL | male   |
+----+-------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+----+------+---------------------+
| id | info | create_time         |
+----+------+---------------------+
|  1 | fish | 2022-10-10 00:00:00 |
|  2 | meat | 2022-10-10 00:00:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)

内连接查询

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。使用WHERE子句定义连接条件比较简单明了，而INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件，而且WHERE子句在某些时候会影响查询的性能

-- 完全限定表名（格式为“表名.列名”）
-- 使用where查询
mysql> select user.id, name, sex, orders.info from user, orders where user.order_id = orders.id;
+----+-------+--------+------+
| id | name  | sex    | info |
+----+-------+--------+------+
|  1 | ronie | male   | fish |
|  2 | kelly | female | meat |
+----+-------+--------+------+
2 rows in set (0.00 sec)

-- 使用内连接查询
mysql> select user.id, name, sex, orders.info from user inner join orders on user.order_id = orders.id;
+----+-------+--------+------+
| id | name  | sex    | info |
+----+-------+--------+------+
|  1 | ronie | male   | fish |
|  2 | kelly | female | meat |
+----+-------+--------+------+
2 rows in set (0.00 sec)

外连接查询

LEFT JOIN左连接：左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from user left join orders on user.order_id=orders.id;
+---------+----------+-------+--------+------+
| user_id | order_id | name  | sex    | info |
+---------+----------+-------+--------+------+
|       1 |        1 | ronie | male   | fish |
|       2 |        2 | kelly | female | meat |
|       3 |     NULL | mike  | male   | NULL |
+---------+----------+-------+--------+------+
3 rows in set (0.00 sec)

mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders left join user on user.order_id=orders.id;
+---------+----------+-------+--------+------+
| user_id | order_id | name  | sex    | info |
+---------+----------+-------+--------+------+
|       1 |        1 | ronie | male   | fish |
|       2 |        2 | kelly | female | meat |
+---------+----------+-------+--------+------+
2 rows in set (0.00 sec)

RIGHT JOIN右连接：右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from user right join orders on user.order_id=orders.id;
+---------+----------+-------+--------+------+
| user_id | order_id | name  | sex    | info |
+---------+----------+-------+--------+------+
|       1 |        1 | ronie | male   | fish |
|       2 |        2 | kelly | female | meat |
+---------+----------+-------+--------+------+
2 rows in set (0.00 sec)

mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders right join user on user.order_id=orders.id;
+---------+----------+-------+--------+------+
| user_id | order_id | name  | sex    | info |
+---------+----------+-------+--------+------+
|       1 |        1 | ronie | male   | fish |
|       2 |        2 | kelly | female | meat |
|       3 |     NULL | mike  | male   | NULL |
+---------+----------+-------+--------+------+
3 rows in set (0.00 sec)

mysql> 

复合条件连接查询

mysql> select user.id as user_id, orders.id as order_id, name, sex, orders.info from orders right join user on user.order_id=orders.id order by name desc;
+---------+----------+-------+--------+------+
| user_id | order_id | name  | sex    | info |
+---------+----------+-------+--------+------+
|       1 |        1 | ronie | male   | fish |
|       3 |     NULL | mike  | male   | NULL |
|       2 |        2 | kelly | female | meat |
+---------+----------+-------+--------+------+
3 rows in set (0.00 sec)