MySQL命令【关联】

内连接

inner join，⼜叫内连接的部分，主要是获取两个表中字段匹配关系的表。查询关联字段共同拥有的数据

查询两个表共同拥有的数据（inner join）

mysql> use employees;
Database changed
mysql> select e.first_name,e.last_name,e.gender,s.salary from employees as e inner join salaries as s on e.emp_no=s.emp_no where s.salary <40000 limit 3;
+------------+-----------+--------+--------+
| first_name | last_name | gender | salary |
+------------+-----------+--------+--------+
| Shahaf     | Famili    | M      |  39935 |
| Divier     | Reistad   | F      |  39520 |
| Pradeep    | Makrucki  | M      |  39765 |
+------------+-----------+--------+--------+
3 rows in set (0.01 sec)

左连接

获取左表所有记录，获取左边数据表所有符合要求的字段数据信息

获取左表所有记录（left join）

mysql> use anruo;
Database changed
mysql> select p.name,p.age,p.age,w.company,w.salary from person as p left join work as w on p.code=w.code limit 5;
+---------+------+------+---------+--------+
| name    | age  | age  | company | salary |
+---------+------+------+---------+--------+
| zhangli |   24 |   24 | alibaba |   1000 |
| acd     |   23 |   23 | zczczc  |   1500 |
+---------+------+------+---------+--------+
2 rows in set (0.01 sec)

三表之间的查询

mysql> select p.name,p.age,p.sex,w.company,w.salary,info.type,info.isSafe from person as p left join work as w on p.code=w.code left join workinfo as info on w.workID=info.workID limit 5;
+---------+------+------+---------+--------+-----------+--------+
| name    | age  | sex  | company | salary | type      | isSafe |
+---------+------+------+---------+--------+-----------+--------+
| zhangli |   24 | boy  | alibaba |   1000 | Full-time | safe   |
| acd     |   23 | boy  | zczczc  |   1500 | NULL      | NULL   |
+---------+------+------+---------+--------+-----------+--------+
2 rows in set (0.00 sec)

右链接

获取右表所有记录的信息，获取右边数据表所有的数据信息

获取右表所有记录（right join）

mysql> select w.company,w.salary,info.type,info.issafe from work as w right join workinfo as info on w.workID=info.workID;
+---------+--------+-----------+--------+
| company | salary | type      | issafe |
+---------+--------+-----------+--------+
| alibaba |   1000 | Full-time | safe   |
+---------+--------+-----------+--------+
1 row in set (0.00 sec)

通过子查询，来进行查询

mysql> select * from person where code in (select code from work where code=1001);
+----+------+---------+------+------+
| id | code | name    | age  | sex  |
+----+------+---------+------+------+
|  1 | 1001 | zhangli |   24 | boy  |
+----+------+---------+------+------+
1 row in set (0.00 sec)