【MySQL】联合查询_mysql 联合查询

 

个人主页：♡喜欢做梦

欢迎  👍点赞  ➕关注  ❤️收藏  💬评论

---

目录

一、什么是联合查询

1.概念

2.语法要求 

3.示例

  4.为什么要使用联合查询

内连接

1.概念

2.语法

3.步骤：

外连接

1.概念

2.分类：

左外连接

1.定义：

2.语法 ：

3.示例： 

 右外连接

1.概念

 2.语法

 3.示例

全外连接

概念 

自连接

1.概念

2.示例： 

子查询

1.概念

2.分类：

1.单行子查询

概念

语法 

示例

注意事项

 2.多行子查询

语法：

3.多列子查询

概念：

示例： 

4.多行子查询和多列子查询的区别 

 合并查询

 1.概念：

根据表构建表

复制表结构

语法

示例： 

复制表信息 

语法

示例

---

一、什么是联合查询

1.概念

联合查询是一种将多个select语句的结果合并成一个结果集。允许垂直合并来自不同查询的数据，前提是这些查询具有相同数量的列且对应列的数据类型兼容。

2.语法要求 

•  列数一致：参与联合查询的每个select语句，其选择列表中的表达式数量必须相同。
• 数据类型兼容：对应位置的列数据类型要相同或者可自动转为相同类型。

3.示例

学生表和班级表

-- 创建学生表mysql> create table students( -> id bigint auto_increment primary key, -> name varchar(20), -> class_id bigint);Query OK, 0 rows affected (0.08 sec)-- 创建班级表mysql> create table class( -> class_id bigint auto_increment primary key, -> class_name varchar(20));Query OK, 0 rows affected (0.08 sec)-- 添加学生信息mysql> insert into students(name,class_id) values -> (\'小丽\',1),(\'莉莉\',1),(\'王刚\',3),(\'张亮\',2);Query OK, 4 rows affected (0.08 sec)Records: 4 Duplicates: 0 Warnings: 0-- 查询学生表中的信息mysql> select * from students;+----+--------+----------+| id | name | class_id |+----+--------+----------+| 1 | 小丽 | 1 || 2 | 莉莉 | 1 || 3 | 王刚 | 3 || 4 | 张亮 | 2 |+----+--------+----------+4 rows in set (0.00 sec)-- 添加课程mysql> insert into class(class_name) values (\'篮球\'),(\'羽毛球\'),(\'排球\');Query OK, 3 rows affected (0.12 sec)Records: 3 Duplicates: 0 Warnings: 0-- 查询课程表中的课程mysql> select * from class;+----------+------------+| class_id | class_name |+----------+------------+| 1 | 篮球 || 2 | 羽毛球 || 3 | 排球 |+----------+------------+3 rows in set (0.00 sec)

  4.为什么要使用联合查询

可以将多个表或者不同查询结果集聚集到一起。

示例：

-- 查询学生表和班级表中的结果mysql> select * from students,class;+----+--------+----------+----------+------------+| id | name | class_id | class_id | class_name |+----+--------+----------+----------+------------+| 1 | 小丽 | 1 | 1 | 篮球 || 1 | 小丽 | 1 | 2 | 羽毛球 || 1 | 小丽 | 1 | 3 | 排球 || 2 | 莉莉 | 1 | 1 | 篮球 || 2 | 莉莉 | 1 | 2 | 羽毛球 || 2 | 莉莉 | 1 | 3 | 排球 || 3 | 王刚 | 3 | 1 | 篮球 || 3 | 王刚 | 3 | 2 | 羽毛球 || 3 | 王刚 | 3 | 3 | 排球 || 4 | 张亮 | 2 | 1 | 篮球 || 4 | 张亮 | 2 | 2 | 羽毛球 || 4 | 张亮 | 2 | 3 | 排球 |+----+--------+----------+----------+------------+12 rows in set (0.00 sec)

内连接

1.概念

定义：在关系型数据库中用于组合或多个表中记录的操作，他会返回满足连接条件的记录。只有当两个表中的相关值匹配时，对应的记录才会被包含在结果集中。

2.语法

-- 写法1select 字段1，字段2... from 表1[别名1]，表2 [别名2] where 连接条件 and 其他条件；-- 写法2select 字段1,字段2... from 表1[别名1] [inner] join 表2 [别名2] on 连接条件 where 其他条件；

 示例：查找学生表和班级表中，班级编号相对应的学生姓名与学生课程

-- 写法1mysql> select students.name,class.class_name from students,class where students.class_id = class.class_id;+--------+------------+| name | class_name |+--------+------------+| 小丽 | 篮球 || 莉莉 | 篮球 || 王刚 | 排球 || 张亮 | 羽毛球 |+--------+------------+4 rows in set (0.00 sec)-- 写法2mysql> select students.name,class.class_name from students join class on students.class_id=class.class_id;+--------+------------+| name | class_name |+--------+------------+| 小丽 | 篮球 || 莉莉 | 篮球 || 王刚 | 排球 || 张亮 | 羽毛球 |+--------+------------+4 rows in set (0.00 sec)

3.步骤：

1. 确定要查询的表有哪些，取表的笛卡尔积；
2. 根据表与表的主外键联系，确定过滤条件；
3. 确定结果集过滤条件；
4. 精减查询字段。

-- 取学生表和班级表可能出现的所有结果(笛卡尔积)mysql> select * from students,class;+----+--------+----------+----------+------------+| id | name | class_id | class_id | class_name |+----+--------+----------+----------+------------+| 1 | 小丽 | 1 | 1 | 篮球 || 1 | 小丽 | 1 | 2 | 羽毛球 || 1 | 小丽 | 1 | 3 | 排球 || 2 | 莉莉 | 1 | 1 | 篮球 || 2 | 莉莉 | 1 | 2 | 羽毛球 || 2 | 莉莉 | 1 | 3 | 排球 || 3 | 王刚 | 3 | 1 | 篮球 || 3 | 王刚 | 3 | 2 | 羽毛球 || 3 | 王刚 | 3 | 3 | 排球 || 4 | 张亮 | 2 | 1 | 篮球 || 4 | 张亮 | 2 | 2 | 羽毛球 || 4 | 张亮 | 2 | 3 | 排球 |+----+--------+----------+----------+------------+12 rows in set (0.00 sec)-- 确定过滤条件mysql> select * from students,class where students.class_id = class.class_id;+----+--------+----------+----------+------------+| id | name | class_id | class_id | class_name |+----+--------+----------+----------+------------+| 1 | 小丽 | 1 | 1 | 篮球 || 2 | 莉莉 | 1 | 1 | 篮球 || 3 | 王刚 | 3 | 3 | 排球 || 4 | 张亮 | 2 | 2 | 羽毛球 |+----+--------+----------+----------+------------+4 rows in set (0.00 sec)-- 精减字段mysql> select students.name,class.class_name from students,class where students.class_id = class.class_id;+--------+------------+| name | class_name |+--------+------------+| 小丽 | 篮球 || 莉莉 | 篮球 || 王刚 | 排球 || 张亮 | 羽毛球 |+--------+------------+4 rows in set (0.00 sec)

外连接

1.概念

外连接： 是关系型数据库中用于组合两个或者多个表中记录的操作，他在连接表时至少会保留一个表中的所有行，即使另一个表中没有匹配的行。

外连接分为左外连接，右外连接和全外连接。MySQL不支持全外连接。这里就不写全外连接了。

2.分类：

左外连接

1.定义：

左外连接：返回左表中的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为null；

2.语法 ：

select 字段1，字段2 from 表1 [别名1] left join 表2 [别名2] on 连接条件;

3.示例： 

-- 插入学生信息，在班级表中没有的编号mysql> insert into students values(5,\'五五\',5);Query OK, 1 row affected (0.06 sec)mysql> select * from students;+----+--------+----------+| id | name | class_id |+----+--------+----------+| 1 | 小丽 | 1 || 2 | 莉莉 | 1 || 3 | 王刚 | 3 || 4 | 张亮 | 2 || 5 | 五五 | 5 |+----+--------+----------+5 rows in set (0.00 sec)-- 左外连接：表1中的数据完全显示，即使表2没有相匹配的数据mysql> select s.id,s.name,c.class_name from students s left join class c on s.class_id=c.class_id;+----+--------+------------+| id | name | class_name |+----+--------+------------+| 1 | 小丽 | 篮球 || 2 | 莉莉 | 篮球 || 3 | 王刚 | 排球 || 4 | 张亮 | 羽毛球 || 5 | 五五 | NULL |+----+--------+------------+5 rows in set (0.00 sec)-- 内连接：只显示表1和表2中相匹配的mysql> select students.name,class.class_name from students join class on students.class_id=class.class_id;+--------+------------+| name | class_name |+--------+------------+| 小丽 | 篮球 || 莉莉 | 篮球 || 王刚 | 排球 || 张亮 | 羽毛球 |+--------+------------+4 rows in set (0.00 sec)

 右外连接

1.概念

右外连接：与左外连接相反，返回右表中的所有记录和左表匹配的记录。如果左表中没有匹配的记录，这结果集中对应字段会显示为null；

 2.语法

select 字段1，字段2 from 表1 [别名1] right join 表2 [别名2] on 连接条件

 3.示例

mysql> insert into class(class_id,class_name) values(4,\'乒乓球\');Query OK, 1 row affected (0.04 sec)mysql> select* from class;+----------+------------+| class_id | class_name |+----------+------------+| 1 | 篮球 || 2 | 羽毛球 || 3 | 排球 || 4 | 乒乓球 |+----------+------------+4 rows in set (0.00 sec)mysql> select s.id,s.name,c.class_name from students s right join class c on s.class_id=c.class_id;+------+--------+------------+| id | name | class_name |+------+--------+------------+| 1 | 小丽 | 篮球 || 2 | 莉莉 | 篮球 || 3 | 王刚 | 排球 || 4 | 张亮 | 羽毛球 || NULL | NULL | 乒乓球 |+------+--------+------------+5 rows in set (0.00 sec)-- 内连接：只显示表1和表2中相匹配的mysql> select students.name,class.class_name from students join class on students.class_id=class.class_id;+--------+------------+| name | class_name |+--------+------------+| 小丽 | 篮球 || 莉莉 | 篮球 || 王刚 | 排球 || 张亮 | 羽毛球 |+--------+------------+4 rows in set (0.00 sec)

全外连接

概念 

全外连接：结果左外连接和右外连接的特点，返回左右表中的所有记录。如果某一边表中没有匹配的记录，这结果集中对应字段会显示为null。

有的mysql不支持，这里我就不多写啦。 

---

自连接

1.概念

自连接：表与自身进行连接的操作。在查询的时候可以使用where条件对结果进行过滤，或者进行行与行之间的比较。做表连接的时候为表其不同的别名。

2.示例： 

-- 创建学生成绩表mysql> create table student_score( -> id bigint, -> english decimal(5,2), -> chinese decimal(5,2), -> math decimal(5,2));Query OK, 0 rows affected (0.07 sec)-- 插入信息mysql> insert into student_score values(1,78,85,96),(3,96,84,66),(2,87,77,99);Query OK, 3 rows affected (0.03 sec)Records: 3 Duplicates: 0 Warnings: 0-- 查询表结果mysql> select * from student_score;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+3 rows in set (0.00 sec)-- 取笛卡尔积：自己与自己连接mysql> select * from student_score s1,student_score s2;+------+---------+---------+-------+------+---------+---------+-------+| id | english | chinese | math | id | english | chinese | math |+------+---------+---------+-------+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 | 1 | 78.00 | 85.00 | 96.00 || 3 | 96.00 | 84.00 | 66.00 | 1 | 78.00 | 85.00 | 96.00 || 2 | 87.00 | 77.00 | 99.00 | 1 | 78.00 | 85.00 | 96.00 || 1 | 78.00 | 85.00 | 96.00 | 3 | 96.00 | 84.00 | 66.00 || 3 | 96.00 | 84.00 | 66.00 | 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 | 3 | 96.00 | 84.00 | 66.00 || 1 | 78.00 | 85.00 | 96.00 | 2 | 87.00 | 77.00 | 99.00 || 3 | 96.00 | 84.00 | 66.00 | 2 | 87.00 | 77.00 | 99.00 || 2 | 87.00 | 77.00 | 99.00 | 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+------+---------+---------+-------+9 rows in set (0.00 sec)-- id要相同mysql> select * from student_score s1,student_score s2 where s1.id=s2.id;+------+---------+---------+-------+------+---------+---------+-------+| id | english | chinese | math | id | english | chinese | math |+------+---------+---------+-------+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 | 1 | 78.00 | 85.00 | 96.00 || 3 | 96.00 | 84.00 | 66.00 | 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 | 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+------+---------+---------+-------+3 rows in set (0.00 sec)-- 确认过滤条件：英语成绩大于语文成绩的信息mysql> select * from student_score s1,student_score s2 where s1.id=s2.id and s1.english>s2.chinese;+------+---------+---------+-------+------+---------+---------+-------+| id | english | chinese | math | id | english | chinese | math |+------+---------+---------+-------+------+---------+---------+-------+| 3 | 96.00 | 84.00 | 66.00 | 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 | 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+------+---------+---------+-------+2 rows in set (0.00 sec)

子查询

1.概念

子查询：子查询是一个select语句的结果当做别一个select语句的条件，也叫做嵌套查询。 外部的查询叫做主查询，内部的查询叫做子查询。

2.分类：

1.单行子查询

概念

单行子查询：嵌套的查询只返回一行数据。

语法 

select * from 表名 where 列1 =(select 列1 from 表名 where 条件);

示例

-- 查询英语成绩为78的学生idmysql> select id from student_score where english=78;+------+| id |+------+| 1 |+------+1 row in set (0.00 sec)-- 查询学生成绩表中英语成绩为78的学生id的所有信息-- 子查询：select id from student_score where english=78，表示从student_score中找出英语成绩等于78的学生的id-- 主查询：select * from  student_score where id=（子查询结果）表示查询student_score表中所有列，筛选条件是id值等于子查询返回的值mysql> select * from student_score where id=(select id from student_score where english=78);+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 |+------+---------+---------+-------+1 row in set (0.01 sec)

注意事项

• 数据类型要匹配：子查询中返回的数据类型要与主查询返回结果一致，否则可能为空或者报错；

-- 返回的是student_score中英语成绩为子查询id值的记录，数据不匹配，返回为空mysql> select * from student_score where english=(select id from student_score where english=78);Empty set (0.00 sec)

•  子查询结果具有唯一性:要确保子查询返回的结果唯一；

-- 查询英语成绩为78的信息-- 有两条结果mysql> select english from student_score where english=78;+---------+| english |+---------+| 78.00 || 78.00 |+---------+2 rows in set (0.00 sec)-- 再一次进行子查询-- 发生报错：子查询结果不唯一mysql> select * from student_score where english=(select english from student_score where english=78);ERROR 1242 (21000): Subquery returns more than 1 row

在单行查询中子查询只能的结果具有唯一性，如果我们想要多个子查询结果应该怎么办？我们这里有多行子查询结果可以解决该问题。 

 2.多行子查询

1.概念：

多列子查询 ：嵌套的查询返回多行数据。

语法：

select * from 表名 where 列1 [not] in(select 列1 from 表名 where 条件);

• 与单行子查询的差别：把=改成in 

-- 查询英语成绩为78的学生id值的所有列mysql> select * from student_score where id in(select id from student_score where english=78 );+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 7 | 78.00 | 88.00 | 54.00 |+------+---------+---------+-------+2 rows in set (0.00 sec)-- 查询英语成绩不是78的学生id值的所有列：加上notmysql> select * from student_score where id not in(select id from student_score where english=78 );+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+2 rows in set (0.01 sec)

3.多列子查询

概念：

多列子查询：返回多列的数据，外层嵌套与嵌套的内层循环要匹配。

示例： 

-- 查询学生成绩表信息：mysql> select* from student_score;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 || 7 | 78.00 | 88.00 | 54.00 || 5 | 96.00 | 84.00 | 66.00 |+------+---------+---------+-------+5 rows in set (0.01 sec)-- 在创建一个成绩表mysql> create table scores( -> id bigint auto_increment primary key, -> english decimal(5,2), -> chinese decimal(5,2), -> math decimal(5,2));Query OK, 0 rows affected (0.13 sec)-- 插入信息mysql> insert into scores values(55,45,66),(77,53,84),(78,88,54);ERROR 1136 (21S01): Column count doesn\'t match value count at row 1mysql> insert into scores(english,chinese,math) values(55,45,66),(77,53,84),(78,88,54);Query OK, 3 rows affected (0.04 sec)Records: 3 Duplicates: 0 Warnings: 0-- 查询新的成绩表信息mysql> select* from scores;+----+---------+---------+-------+| id | english | chinese | math |+----+---------+---------+-------+| 1 | 55.00 | 45.00 | 66.00 || 2 | 77.00 | 53.00 | 84.00 || 3 | 78.00 | 88.00 | 54.00 |+----+---------+---------+-------+3 rows in set (0.00 sec)-- 查询学生成绩表中与新成绩表相匹配的数据mysql> select * from student_score where (english,math) in (select english,math from scores);+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 7 | 78.00 | 88.00 | 54.00 |+------+---------+---------+-------+1 row in set (0.00 sec)

4.多行子查询和多列子查询的区别 

区别 多行子查询 多列子查询 功能 重点在于返回多行数据，通常用于处理单列的多个值 重点在于返回多列数据，通常用于主查询中多列值的组合比较 数据匹配 基于单列的多个值进行的 基于多列的组合进行的 使用场景 用于一组值的筛选 依据多个列的条件进行筛选

---

 合并查询

 1.概念：

合并查询：合并多个查询（select）结果到一个结果集中，可以使用集合操作符union，union all。

使用union或者union all合并结果 

• union：取两个结果的并集，如果出现查询结果相同的结果，会自动去重；
• union all： 取两个结果的并集，如果出现查询结果相同的结果，不会自动去重；

示例： 

-- 查看学生成绩表结果mysql> select * from student_score;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 3 | 96.00 | 84.00 | 66.00 || 2 | 87.00 | 77.00 | 99.00 || 7 | 78.00 | 88.00 | 54.00 || 5 | 96.00 | 84.00 | 66.00 |+------+---------+---------+-------+5 rows in set (0.00 sec)-- 查看学生成绩表中英语成绩为78的信息mysql> select * from student_score where english =78;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 7 | 78.00 | 88.00 | 54.00 |+------+---------+---------+-------+2 rows in set (0.00 sec)--查看学生成绩表中id为2的信息mysql> select * from student_score where id =2;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+1 row in set (0.00 sec)-- union的使用-- 查看学生成绩表中英语成绩为78的信息和id为2的信息mysql> select * from student_score where english =78 union select * from student_score where id =2;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 7 | 78.00 | 88.00 | 54.00 || 2 | 87.00 | 77.00 | 99.00 |+------+---------+---------+-------+3 rows in set (0.00 sec)-- 有三条结果-- --查看学生成绩表中id为1的信息mysql> select * from student_score where id =1;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 |+------+---------+---------+-------+1 row in set (0.00 sec)-- 查看学生成绩表中英语成绩为78的信息和id为1的信息mysql> select * from student_score where english =78 union select * from student_score where id =1;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 7 | 78.00 | 88.00 | 54.00 |+------+---------+---------+-------+2 rows in set (0.00 sec)-- 只有两条结果-- 原因：union具有去重效果-- union all：不具有去重的效果mysql> select * from student_score where english =78 union all select * from student_score where id =1;+------+---------+---------+-------+| id | english | chinese | math |+------+---------+---------+-------+| 1 | 78.00 | 85.00 | 96.00 || 7 | 78.00 | 88.00 | 54.00 || 1 | 78.00 | 85.00 | 96.00 |+------+---------+---------+-------+3 rows in set (0.00 sec)

根据表构建表

复制表结构

语法

create table 目标表名 like 源表名;

示例： 

-- 查看班级表结构mysql> desc class;+------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+----------------+| class_id | bigint(20) | NO | PRI | NULL | auto_increment || class_name | varchar(20) | YES | | NULL | |+------------+-------------+------+-----+---------+----------------+2 rows in set (0.01 sec)-- 根据班级表构建一个新的表mysql> create table new_class like class;Query OK, 0 rows affected (0.06 sec)-- 查看新的班级表结构mysql> desc new_class;+------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+----------------+| class_id | bigint(20) | NO | PRI | NULL | auto_increment || class_name | varchar(20) | YES | | NULL | |+------------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

复制表信息 

语法

insert into 目标表名 select * from 源表名;

这个我就是我在上一篇文章里面新增插入查询结果的，如果想看更详细一点的语法，可以去看看嘞，这里就简单写一下啦。 

示例

-- 查看班级信息mysql> select * from class;+----------+------------+| class_id | class_name |+----------+------------+| 1 | 篮球 || 2 | 羽毛球 || 3 | 排球 || 4 | 乒乓球 |+----------+------------+4 rows in set (0.00 sec)-- 复制班级表中的信息到新的班级表中mysql> insert into new_class select * from class;Query OK, 4 rows affected (0.02 sec)Records: 4 Duplicates: 0 Warnings: 0-- 查看新的班级表信息：与旧班级表一直mysql> select * from new_class;+----------+------------+| class_id | class_name |+----------+------------+| 1 | 篮球 || 2 | 羽毛球 || 3 | 排球 || 4 | 乒乓球 |+----------+------------+4 rows in set (0.00 sec)

各关键词的执行向后顺序：from>on>join>where>group by>with>having>select>distinct>order by>limit