欢迎 👍点赞 ➕关注 ❤️收藏 💬评论
目录
一、什么是联合查询
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.语法
-- 写法1
select 字段1，字段2... from 表1[别名1]，表2 [别名2] where 连接条件 and 其他条件；
-- 写法2
select 字段1,字段2... from 表1[别名1] [inner] join 表2 [别名2] on 连接条件 where 其他条件；示例：查找学生表和班级表中，班级编号相对应的学生姓名与学生课程
-- 写法1
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)
-- 写法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)3.步骤：
- 确定要查询的表有哪些，取表的笛卡尔积；
- 根据表与表的主外键联系，确定过滤条件；
- 确定结果集过滤条件；
- 精减查询字段。
-- 取学生表和班级表可能出现的所有结果(笛卡尔积)
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的学生id
mysql> 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值的所有列：加上not
mysql> 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 1
mysql> 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
