MySQL数据库多表查询

程序员文章站 2022-05-10 19:33:33

...

1. 简介

多表查询就是将多个表的数据横向联合起来。多表查询的分类有：
1）内连接
2）外链接: 左外链接，右外连接
3）交叉连接
4）自然连接

2.多表查询

2.1 内连接【inner join】

语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
语法二：selcet 列名 from 表1,表2 where 表1.公共字段=表2.公共字段

例题：

方法一：
mysql> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname      | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文       | 女     |          80 |      58 |
| 李文才       | 男     |          50 |      90 |
| 欧阳俊雄     | 男     |          65 |      50 |
| 张秋丽       | 男     |          77 |      82 |
| 争青小子     | 男     |          56 |      48 |
+--------------+--------+-------------+---------+

方法二：
mysql> select stuname,stusex,writtenexam,labexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname      | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文       | 女     |          80 |      58 |
| 李文才       | 男     |          50 |      90 |
| 欧阳俊雄     | 男     |          65 |      50 |
| 张秋丽       | 男     |          77 |      82 |
| 争青小子     | 男     |          56 |      48 |
+--------------+--------+-------------+---------+

脚下留心：显示公共字段需要指定表名

不指定公共字段的表名会报错:
mysql> select stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
ERROR 1052 (23000): Column 'stuno' in field list is ambiguous

指定公共字段的表名：
mysql> select stuinfo.stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+--------------+--------+-------------+---------+
| stuno  | stuname      | stusex | writtenexam | labexam |
+--------+--------------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |          80 |      58 |
| s25302 | 李文才       | 男     |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |          65 |      50 |
| s25301 | 张秋丽       | 男     |          77 |      82 |
| s25318 | 争青小子     | 男     |          56 |      48 |
+--------+--------------+--------+-------------+---------+

多学一招：三个表的内连接如何实现？

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
inner join 表3 on 表2.公共字段=表3.公共字段

2.2 左外连接【left join】

以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。

语法：select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段

例题：

mysql> select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname      | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文       |          80 |      58 |
| 李文才       |          50 |      90 |
| 欧阳俊雄     |          65 |      50 |
| 张秋丽       |          77 |      82 |
| 争青小子     |          56 |      48 |
| 诸葛丽丽     |        NULL |    NULL |
| 梅超风       |        NULL |    NULL |
+--------------+-------------+---------+

2.3 右外连接【right join】

以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。

语法：select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段

例题：

mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname      | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文       |          80 |      58 |
| 李文才       |          50 |      90 |
| 欧阳俊雄     |          65 |      50 |
| 张秋丽       |          77 |      82 |
| 争青小子     |          56 |      48 |
| NULL         |          66 |      77 |
+--------------+-------------+---------+

2.4 交叉连接【cross join】

插入测试数据：

mysql> create table t1(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1,'tom'),(2,'berry');
Query OK, 2 rows affected (0.00 sec)

mysql> create table t2(
    -> id int,
    -> score int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (1,99),(2,88);

如果没有连接表达式，返回的是笛卡尔积：

mysql> select * from t1 cross join t2;
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    99 |
|    2 | berry |    1 |    99 |
|    1 | tom   |    2 |    88 |
|    2 | berry |    2 |    88 |
+------+-------+------+-------+

如果有连接表达式，等价于内连接：

mysql> select * from t1 cross join t2 where t1.id=t2.id;
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    99 |
|    2 | berry |    2 |    88 |
+------+-------+------+-------+

2.5 自然连接【natural】

自然连接，自动地通过判断条件连接，它是通过同名字段来判断的。

自然连接又分为：

自然内连接 natural join
自然左外连接 natural left join
自然右外连接 natural right join

例题：

# 自然内连接
mysql> select * from stuinfo natural join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           | s271811 |          80 |
  58 |
| s25302 | 李文才        | 男       |     31 |       3 | 上海          | s271813 |          50 |
  90 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | s271815 |          65 |
     50 |
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           | s271816 |          77 |
    82 |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           | s271819 |          56 |
     48 |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)

# 自然左外连接

mysql> select * from stuinfo natural left join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           | s271816 |          77
    82 |
| s25302 | 李文才        | 男       |     31 |       3 | 上海          | s271813 |          50 |
  90 |
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           | s271811 |          80 |
  58 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | s271815 |          65
     50 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           | NULL    |        NULL
   NULL |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           | s271819 |          56
     48 |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          | NULL    |        NULL |
ULL |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
7 rows in set (0.00 sec)

# 自然右外连接
mysql> select * from stuinfo natural right join stumarks;
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| stuNo  | examNo  | writtenExam | labExam | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| s25303 | s271811 |          80 |      58 | 李斯文        | 女      |     22 |       2 | 北京
     |
| s25302 | s271813 |          50 |      90 | 李文才        | 男       |     31 |       3 | 上海
     |
| s25304 | s271815 |          65 |      50 | 欧阳俊雄        | 男       |     28 |       4 | 天津
        |
| s25301 | s271816 |          77 |      82 | 张秋丽         | 男       |     18 |       1 | 北京
       |
| s25318 | s271819 |          56 |      48 | 争青小子        | 男       |     26 |       6 | 天津
        |
| s25320 | s271820 |          66 |      77 | NULL     | NULL   |   NULL |    NULL | NULL       |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
6 rows in set (0.00 sec)

自然连接结论：

表连接通过同名的字段来连接的
如果没有同名的字段返回笛卡尔积
会对结果进行整理，整理的规则如下

a)连接字段保留一个

b)连接字段放在最前面

c)左外连接左边在前，右外连接右表在前

2.6 using()

用来指定连接字段
using()也会对连接字段进行整理，整理方式和自然连接一样。
例题：

mysql> select * from stuinfo inner join stumarks using(stuno);
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)

上一篇：紫米65W PD氮化镓充电器曝光：小巧便携

下一篇：苹果彻底封杀有锁iPhone iccid卡贴解锁：一夜成砖

MySQL数据库多表查询

1. 简介

2.多表查询

2.1 内连接【inner join】

2.2 左外连接【left join】

2.3 右外连接【right join】

2.4 交叉连接【cross join】

2.5 自然连接【natural】

2.6 using()

MySQL查询语句过程和EXPLAIN语句基本概念及其优化

MySQL创建带特殊字符的数据库名称方法示例

MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法

MySQL慢查询之开启慢查询

用命令创建MySQL数据库(de1)的方法

MySQL慢查询之pt-query-digest分析慢查询日志

Mysql数据库表定期备份的实现详解

PHP开发环境配置(MySQL数据库安装图文教程)

mysql limit查询优化分析

Thinkphp使用mongodb数据库实现多条件查询方法