MySQL数据库之多表查询inner join内连接
内连接
-
规则
- 返回两个表的公共记录
-
语法
inner join...on语法select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
where语法select * from 表1，表2 where 表1.公共字段=表2.公共字段
MariaDB [sel]> select * from grades inner join resume on grades.name=resume.name;
+-------+---------+------+----+-------+-----------+
| name | chinese | math | id | name | skill |
+-------+---------+------+----+-------+-----------+
| Sunny | 93 | 96 | 1 | Sunny | php |
| Jerry | 97 | 91 | 3 | Jerry | php,mysql |
+-------+---------+------+----+-------+-----------+
# `2 rows in set (0.008 sec)`
MariaDB [sel]> select * from grades,resume where grades.name=resume.name;
+-------+---------+------+----+-------+-----------+
| name | chinese | math | id | name | skill |
+-------+---------+------+----+-------+-----------+
| Sunny | 93 | 96 | 1 | Sunny | php |
| Jerry | 97 | 91 | 3 | Jerry | php,mysql |
+-------+---------+------+----+-------+-----------+
# `2 rows in set (0.001 sec)`
-- 相同的字段只显示一次
mysql> select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s inner join stumarks m on s.stuno=m.stuno;
+--------+----------+--------+-------------+---------+
| stuno | stuname | stusex | writtenexam | labexam |
+--------+----------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 80 | 58 |
| s25302 | 李文才 | 男 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 77 | 82 |
| s25318 | 争青小子 | 男 | 56 | 48 |
+--------+----------+--------+-------------+---------+
# `5 rows in set (0.00 sec)`
- 内连接中inner可以省略
select * from 表1 join 表2 on 表1.公共字段=表2.公共字段
MariaDB [sel]> select * from grades join resume on grades.name=resume.name;
+-------+---------+------+----+-------+-----------+
| name | chinese | math | id | name | skill |
+-------+---------+------+----+-------+-----------+
| Sunny | 93 | 96 | 1 | Sunny | php |
| Jerry | 97 | 91 | 3 | Jerry | php,mysql |
+-------+---------+------+----+-------+-----------+
# `2 rows in set (0.001 sec)`
- 如何实现三表查询
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段
