【MySQL】查询进阶 | 聚合查询 | 联合查询 | 聚合函数 | 笛卡尔积 | 内连接 | 外链接 | 自连接 | 子查询 | 合并查询

翁佳明

已于 2024-05-16 09:44:39 修改

阅读量753

点赞数 11

分类专栏： MySQL 文章标签： mysql 数据库聚合查询查询联合查询外连接笛卡尔积

于 2024-05-16 09:44:23 首次发布

本文链接：https://blog.csdn.net/m0_64003319/article/details/138945589

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

查询进阶

1.聚合查询

表达式查询，是针对列和列之间进行运算。

聚合查询，是针对行和行之间进行运算

sql中提供了一些“聚合函数”,通过聚合函数来完成行与行之间的运算

1.聚合函数

函数名后面，不能乱加空格

在这里插入图片描述

count（）：查询结果集的行数

count得到的结果可以参与各种运算，或者搭配其他sql语句使用


mysql> select *from exam_result;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空 |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能 |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
|    6 | 孙权   |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明 |    75.0 | 65.0 |    30.0 |
+------+--------+---------+------+---------+
7 rows in set (0.00 sec)

mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        7 |
+----------+

mysql> select count(name) from exam_result;
-- 先查询到name的结果集，再判断name有几行
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+

mysql> insert into exam_result values(null,null,null,null,null);
-- 插入一行空数据后
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from exam_result;
-- null也占一行，进行统计 
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(name) from exam_result;
-- 遇到null,不进行统计
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+

指定列进行去重

mysql>  update exam_result set math = 98 where name = '猪悟能';

mysql> select * from exam_result;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空 |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能 |    88.0 | 98.0 |    90.0 |
|    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
|    6 | 孙权   |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明 |    75.0 | 65.0 |    30.0 |
| NULL | NULL   |    NULL | NULL |    NULL |
+------+--------+---------+------+---------+


mysql> select count(distinct math)from exam_result;
-- 统计数学成绩去重后的个数（null不算，重复的不算）
+----------------------+
| count(distinct math) |
+----------------------+
|                    6 |
+----------------------+

sum()求和

把这一列的若干行，进行求和算数运算（只能针对数字类型使用）

mysql> select sum(chinese) from exam_result;
-- 计算所有人语文成绩之和。（会自动排除null）
+--------------+
| sum(chinese) |
+--------------+
|        525.0 |
+--------------+

mysql> select sum(name) from exam_result;
-- 如果是字符串来参与运算，会进行警告
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 7 warnings (0.01 sec)

mysql> show warnings;
-- mysql会尝试把这一列转换成double,如果可以转成double,就可以进行运算。如果没有转成就会报错。
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '唐三藏' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '猪悟能' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '曹孟德' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '刘玄德' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: '宋公明' |
+---------+------+--------------------------------------------+

-- sum(表达式)
mysql> select sum(chinese+math+english) from exam_result;
-- 1，先把对于的列相加，得到一个临时表
-- 2.把这个临时表的结果，进行行与行之间的运算
+---------------------------+
| sum(chinese+math+english) |
+---------------------------+
|                    1549.5 |
+---------------------------+

avg （）求平均值

mysql> select avg(chinese) from exam_result;
+--------------+
| avg(chinese) |
+--------------+
|     75.00000 |
+--------------+

min() max() 求最大最小值

mysql> select min(chinese) from exam_result;
+--------------+
| min(chinese) |
+--------------+
|         55.5 |
+--------------+
1 row in set (0.01 sec)

mysql> select max(chinese) from exam_result;
+--------------+
| max(chinese) |
+--------------+
|         88.0 |
+--------------+
1 row in set (0.00 sec)

2.group by 子句

使用group by进行分组，再针对每个分组，分别进行聚合查询
针对指定的列进行分组，把这一列中值相同的行，分到一组中，得到若干个组。对这些组分别使用聚合函数。

mysql> select * from emp;
+----+------+----------+--------+
| id | name | role     | salary |
+----+------+----------+--------+
|  1 | 张三 | 程序员   |  10000 |
|  2 | 李四 | 程序员   |  12000 |
|  3 | 王五 | 程序员   |  13000 |
|  4 | 赵六 | 产品经理 |   9000 |
|  5 | 李白 | 产品经理 |   9500 |
|  6 | 周八 | 老板     | 100000 |
+----+------+----------+--------+
6 rows in set (0.00 sec)

mysql> select avg(salary) from emp;
+-------------+
| avg(salary) |
+-------------+
|  25583.3333 |
+-------------+
1 row in set (0.00 sec)

mysql> select role,avg(salary) from emp group by role;
-- 1.先根据role进行分组
-- 2.再针对每组来求平均值
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 产品经理 |   9250.0000 |
| 程序员   |  11666.6667 |
| 老板     | 100000.0000 |
+----------+-------------+

如果分组之后，不使用聚合函数。此时的结果就是查询出每一组中的某个代表数据。

搭配条件

要区分好是分组前的条件，还是分组后的条件。

mysql>  select role,avg(salary)from emp where name!='张三' group by role;
-- 查询每个岗位的平均工资，但是排除张三
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 产品经理 |   9250.0000 |
| 程序员   |  12500.0000 |
| 老板     | 100000.0000 |
+----------+-------------+
3 rows in set (0.01 sec)

3.having

在对分组后的结果再次进行条件过滤时，不能使用where语句，而是需要使用having
having 一般写在 group by的后面

-- 查询每个岗位的平均工资，但是排除平均工资超过两万的结果

mysql>  select role,avg(salary)from emp group by role having avg(salary)<20000;
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 产品经理 |   9250.0000 |
| 程序员   |  11666.6667 |
+----------+-------------+

在group by 中，可以一个sql同时完成这两类条件的筛选

-- 查询每个岗位的平均薪资，不算张三，并且保留 平均值<2万的结果 
mysql>  select role,avg(salary)  from emp where name !='张三' group by role having avg(salary)<20000;
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 产品经理 |   9250.0000 |
| 程序员   |  12500.0000 |
+----------+-------------+

2.联合查询

一次需要在多张表中进行查询。

联合查询/多表查询：是对多张表的数据取笛卡尔积

笛卡尔积

在这里插入图片描述

笛卡尔积是通过排列组合的方式，得到一个更大的表
笛卡尔积的列数，是这两个表的列数相加。
笛卡尔积的行数，是这两个表的行数相乘。

笛卡尔积列举了所有可能的情况。其中，有些数据可能是非法的（无意义的数据）。在进行多表查询的时候，就需要把有意义的数据筛选出来，过滤掉无意义的数据。筛序的条件叫做连接条件。

mysql> create table classes(id int primary key auto_increment,name varchar(20), `desc` varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(id int primary key auto_increment,sn varchar(20),name varchar(20), qq_mail varchar(20),classes_id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table course(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(score decimal(3,1),student_id int,course_id int);
Query OK, 0 rows affected (0.01 sec)

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);


mysql> show tables;
+-------------------+
| Tables_in_day5_14 |
+-------------------+
| classes           |
| course            |
| score             |
| student           |
+-------------------+
-- 学生表、班级表、课程表

-- 分数表（学生和课程之间的联系表）

-- 学生表
mysql> select * from student;
+----+-------+------------+------------------+------------+
| id | sn    | name       | qq_mail          | classes_id |
+----+-------+------------+------------------+------------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |          1 |
|  2 | 00835 | 菩提老祖   | NULL             |          1 |
|  3 | 00391 | 白素贞     | NULL             |          1 |
|  4 | 00031 | 许仙       | xuxian@qq.com    |          1 |
|  5 | 00054 | 不想毕业   | NULL             |          1 |
|  6 | 51234 | 好好说话   | say@qq.com       |          2 |
|  7 | 83223 | tellme     | NULL             |          2 |
|  8 | 09527 | 老外学中文 | foreigner@qq.com |          2 |
+----+-------+------------+------------------+------------+
-- 班级表
mysql> select * from classes;
+----+-------------------+-----------------------------------------------+
| id | name              | desc                                          |
+----+-------------------+-----------------------------------------------+
|  1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 |
|  2 | 中文系2019级3班   | 学习了中国传统文学                            |
|  3 | 自动化2019级5班   | 学习了机械自动化                              |
+----+-------------------+-----------------------------------------------+

-- 课程表
mysql> select * from course;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  3 | 计算机原理   |
|  4 | 语文         |
|  5 | 高阶数学     |
|  6 | 英文         |
+----+--------------+
-- 分数表
mysql> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+

1.内连接

查询许仙同学的成绩

1.先把student和score两张表进行笛卡尔积

mysql>  select * from student,score;
+----+-------+------------+------------------+------------+-------+------------+-----------+
| id | sn    | name       | qq_mail          | classes_id | score | student_id | course_id |
+----+-------+------------+------------------+------------+-------+------------+-----------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |          1 |  70.5 |          1 |         1 |
|  2 | 00835 | 菩提老祖   | NULL             |          1 |  70.5 |          1 |         1 |
|  3 | 00391 | 白素贞     | NULL             |          1 |  70.5 |          1 |         1 |
|  4 | 00031 | 许仙       | xuxian@qq.com    |          1 |  70.5 |          1 |         1 |
-- --------     --------------------------------------- - - - - - - -  - -   - --- - - - -
|  6 | 51234 | 好好说话   | say@qq.com       |          2 |  92.0 |          7 |         6 |
|  7 | 83223 | tellme     | NULL             |          2 |  92.0 |          7 |         6 |
|  8 | 09527 | 老外学中文 | foreigner@qq.com |          2 |  92.0 |          7 |         6 |
+----+-------+------------+------------------+------------+-------+------------+-----------+
160 rows in set (0.01 sec)

-- 一共160行（20*8）5列（2+3），中间省略

2.加上连接条件，筛选出有效数据

学生id在两个表中同时出现。筛选条件为：学生表的id = 分数表的student_id

 select * from student,score where id = student_id;
 -- 但是如果两个表的列名一样，则无法进行区分
 
  select * from student,score where student.id = score.student_id;
  -- 所以用表名.列名进行区分。
  +----+-------+------------+-----------------+------------+-------+------------+-----------+
| id | sn    | name       | qq_mail         | classes_id | score | student_id | course_id |
+----+-------+------------+-----------------+------------+-------+------------+-----------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  98.5 |          1 |         3 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  33.0 |          1 |         5 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  98.0 |          1 |         6 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |  60.0 |          2 |         1 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |  59.5 |          2 |         5 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  33.0 |          3 |         1 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  68.0 |          3 |         3 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  99.0 |          3 |         5 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  23.0 |          4 |         3 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  56.0 |          4 |         5 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  72.0 |          4 |         6 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |  81.0 |          5 |         1 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |  37.0 |          5 |         5 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  56.0 |          6 |         2 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  43.0 |          6 |         4 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  79.0 |          6 |         6 |
|  7 | 83223 | tellme     | NULL            |          2 |  80.0 |          7 |         2 |
|  7 | 83223 | tellme     | NULL            |          2 |  92.0 |          7 |         6 |
+----+-------+------------+-----------------+------------+-------+------------+-----------+

3.结合需求，进一步添加条件，针对结果进行筛选。


mysql>  select * from student,score where student.id = score.student_id and student.name = '许仙';
+----+-------+------+---------------+------------+-------+------------+-----------+
| id | sn    | name | qq_mail       | classes_id | score | student_id | course_id |
+----+-------+------+---------------+------------+-------+------------+-----------+
|  4 | 00031 | 许仙 | xuxian@qq.com |          1 |  67.0 |          4 |         1 |
|  4 | 00031 | 许仙 | xuxian@qq.com |          1 |  23.0 |          4 |         3 |
|  4 | 00031 | 许仙 | xuxian@qq.com |          1 |  56.0 |          4 |         5 |
|  4 | 00031 | 许仙 | xuxian@qq.com |          1 |  72.0 |          4 |         6 |
+----+-------+------+---------------+------------+-------+------------+-----------+

4.针对查询到的列，进行精简。只保留需求。

 mysql>  select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';
+------+-------+
| name | score |
+------+-------+
| 许仙 |  67.0 |
| 许仙 |  23.0 |
| 许仙 |  56.0 |
| 许仙 |  72.0 |
+------+-------+

查询所有同学的总成绩

用聚合函数sum对多行数据进行加和，还需要按照同学来进行分组

1.先将学生表和分数表进行笛卡尔积

mysql> select * from student,score;

2.指定连接条件 student.id = score.student_id


mysql> select * from student,score where student.id = score.student_id;
+----+-------+------------+-----------------+------------+-------+------------+-----------+
| id | sn    | name       | qq_mail         | classes_id | score | student_id | course_id |
+----+-------+------------+-----------------+------------+-------+------------+-----------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  98.5 |          1 |         3 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  33.0 |          1 |         5 |
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  98.0 |          1 |         6 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |  60.0 |          2 |         1 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |  59.5 |          2 |         5 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  33.0 |          3 |         1 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  68.0 |          3 |         3 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  99.0 |          3 |         5 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  23.0 |          4 |         3 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  56.0 |          4 |         5 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  72.0 |          4 |         6 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |  81.0 |          5 |         1 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |  37.0 |          5 |         5 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  56.0 |          6 |         2 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  43.0 |          6 |         4 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  79.0 |          6 |         6 |
|  7 | 83223 | tellme     | NULL            |          2 |  80.0 |          7 |         2 |
|  7 | 83223 | tellme     | NULL            |          2 |  92.0 |          7 |         6 |
+----+-------+------------+-----------------+------------+-------+------------+-----------+

3.先精简列


mysql> select student.name,score.score from student,score where student.id = score.student_id;
+------------+-------+
| name       | score |
+------------+-------+
| 黑旋风李逵 |  70.5 |
| 黑旋风李逵 |  98.5 |
| 黑旋风李逵 |  33.0 |
| 黑旋风李逵 |  98.0 |
| 菩提老祖   |  60.0 |
| 菩提老祖   |  59.5 |
| 白素贞     |  33.0 |
| 白素贞     |  68.0 |
| 白素贞     |  99.0 |
| 许仙       |  67.0 |
| 许仙       |  23.0 |
| 许仙       |  56.0 |
| 许仙       |  72.0 |
| 不想毕业   |  81.0 |
| 不想毕业   |  37.0 |
| 好好说话   |  56.0 |
| 好好说话   |  43.0 |
| 好好说话   |  79.0 |
| tellme     |  80.0 |
| tellme     |  92.0 |
+------------+-------+

4.对上述结果，进行group by聚合查询

mysql> select student.name,sum(score.score) from student,score where student.id = score.student_id group by student.name  ;
+------------+------------------+
| name       | sum(score.score) |
+------------+------------------+
| tellme     |            172.0 |
| 不想毕业   |            118.0 |
| 好好说话   |            178.0 |
| 白素贞     |            200.0 |
| 菩提老祖   |            119.5 |
| 许仙       |            218.0 |
| 黑旋风李逵 |            300.0 |
+------------+------------------+

查询所有同学的每门课程和分数：

1.学生表、课程表、分数表三张表进行笛卡尔积

mysql> select * from student,course,score;

2.指定连接条件筛选数据。三个表涉及到两个连接条件。

score.student_id = student,id

score.course_id = course.id

mysql> select * from student,course,score where student.id = score.student_id and score.course_id = course.id ;

3.精简列


mysql> select student.name,course.name,score.score from student,course,score where student.id = score.student_id and score.course_id = course.id ;
-- 进行取别名
mysql> select student.name as studentName,course.name as courseName ,score.score from student,course,score where student.id = score.student_id and score.course_id = course.id ;
+-------------+--------------+-------+
| studentName | courseName   | score |

2.外连接

左外连接：左侧的表完全显示 (left join)

右外连接：右侧的表完全显示 (right jion)

如果两个表中，里面的记录都是存在对应关系，内连接和外连接的结果就是一致的

如果存在不对于的记录，内连接和外连接就会出现差别。

mysql> insert into student2 values(1,'张三'),(2,'李四'),(3,'王五');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score2 value(1,90),(2,80),(4,70);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from student2;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from score2;
+------+-------+
| id   | score |
+------+-------+
|    1 |    90 |
|    2 |    80 |
|    4 |    70 |
+------+-------+

1.内连接：

mysql>  select * from student2,score2 where student2.id = score2.id;
-- 内连接写法一
+------+------+------+-------+
| id   | name | id   | score |
+------+------+------+-------+
|    1 | 张三 |    1 |    90 |
|    2 | 李四 |    2 |    80 |
+------+------+------+-------+

mysql>  select * from student2 [inner] join score2 on student2.id = score2.id;
-- 内连接写法二
+------+------+------+-------+
| id   | name | id   | score |
+------+------+------+-------+
|    1 | 张三 |    1 |    90 |
|    2 | 李四 |    2 |    80 |
+------+------+------+-------+

2.左外连接

mysql>  select * from student2 left join score2 on student2.id = score2.id;
+------+------+------+-------+
| id   | name | id   | score |
+------+------+------+-------+
|    1 | 张三 |    1 |    90 |
|    2 | 李四 |    2 |    80 |
|    3 | 王五 | NULL |  NULL |
+------+------+------+-------+

王五的数据只在左侧表（student2）存在
以左侧表为基准，保证左侧表的每个记录都体现在结果中。如果在右侧表中不存在，就填成null

3.右外链接

mysql>  select * from student2 right join score2 on student2.id = score2.id;
+------+------+------+-------+
| id   | name | id   | score |
+------+------+------+-------+
|    1 | 张三 |    1 |    90 |
|    2 | 李四 |    2 |    80 |
| NULL | NULL |    4 |    70 |
+------+------+------+-------+

以右侧表为基准，保证右侧表的每个数据，都会出现在最终结果里。如果左侧表中不存在，对于的列填null

3.自连接

一张表，自己和自己进行笛卡尔积。

有的时候，需要进行与行之间的比较。而sql只能进行列和列之间的比较。通过自连接，把行关系转换成列关系

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

mysql> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
mysql> select * from course;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  3 | 计算机原理   |
|  4 | 语文         |
|  5 | 高阶数学     |
|  6 | 英文         |
+----+--------------+
-- 看3比1高的成绩

要将行关系，转换成列关系，

 mysql> select * from score,score;
ERROR 1066 (42000): Not unique table/alias: 'score'
-- 不能直接用，需要设置别名
mysql> select * from score as s1,score as s2;

mysql> select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id = 1;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  70.5 |          1 |         1 |
|  68.0 |          3 |         3 |  33.0 |          3 |         1 |
|  23.0 |          4 |         3 |  67.0 |          4 |         1 |
+-------+------------+-----------+-------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id = 1 and s1.score>s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  70.5 |          1 |         1 |
|  68.0 |          3 |         3 |  33.0 |          3 |         1 |
+-------+------------+-----------+-------+------------+-----------+
2 rows in set (0.00 sec)

mysql> select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id = 1 and s1.score>s2.score;
+------------+-------+-------+
| student_id | score | score |
+------------+-------+-------+
|          1 |  98.5 |  70.5 |
|          3 |  68.0 |  33.0 |
+------------+-------+-------+

4.子查询

也叫嵌套查询：嵌入到其他sql语句中select语句。（套娃）

单行子查询

返回一行记录的子查询

查询与“不想毕业” 同学的同班同学：

mysql> select * from student;
+----+-------+------------+------------------+------------+
| id | sn    | name       | qq_mail          | classes_id |
+----+-------+------------+------------------+------------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com  |          1 |
|  2 | 00835 | 菩提老祖   | NULL             |          1 |
|  3 | 00391 | 白素贞     | NULL             |          1 |
|  4 | 00031 | 许仙       | xuxian@qq.com    |          1 |
|  5 | 00054 | 不想毕业   | NULL             |          1 |
|  6 | 51234 | 好好说话   | say@qq.com       |          2 |
|  7 | 83223 | tellme     | NULL             |          2 |
|  8 | 09527 | 老外学中文 | foreigner@qq.com |          2 |
+----+-------+------------+------------------+------------+
8 rows in set (0.01 sec)

mysql> select * from student where classes_id = (select classes_id from student where name='不想毕业');
-- 第二个select 返回的结果是必须是一行记录
+----+-------+------------+-----------------+------------+
| id | sn    | name       | qq_mail         | classes_id |
+----+-------+------------+-----------------+------------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |
|  3 | 00391 | 白素贞     | NULL            |          1 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |
+----+-------+------------+-----------------+------------+

多行子查询

返回多行记录的子查询

查询“语文”或“英文”课程的成绩信息

1.先通过课程的名字，找到课程的id

2.通过课程id在分数表中进行查询

in

mysql> select * from score where course_id in (select id from course where name = '语文' or name = '英文');
mysql> select score.student_id,score.course_id,score.score from score where score.course_id in (select id from course where name = '语文' or name = '英文');
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
|          1 |         6 |  98.0 |
|          4 |         6 |  72.0 |
|          6 |         4 |  43.0 |
|          6 |         6 |  79.0 |
|          7 |         6 |  92.0 |
+------------+-----------+-------+

5.合并查询

把多个sql的结果集，合并到一起。

union

前后查询的结果集，字段必须一致
用于取两个不同表的结果集的并集。会自动去掉重复行。

查询id小于3，或者名字为“英文”的课程

mysql> select * from course where  id<3 or name = '英文';
-- or只能一个表
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  6 | 英文         |
+----+--------------+
mysql> select * from course where  id<3 union select * from course where name = '英文';
-- union 可以合并两个不同表的结果集
-- 两个表列的个数和类型必须一致。
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Java         |
|  2 | 中国传统文化 |
|  6 | 英文         |
+----+--------------+

如果不需要去重，使用union all

点击移步博客主页，欢迎光临~

偷cyk的图

翁佳明

关注

11
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】查询进阶 | 聚合查询 | 联合查询 | 聚合函数 | 笛卡尔积 | 内连接 | 外链接 | 自连接 | 子查询 | 合并查询

其中，有些数据可能是非法的（无意义的数据）。在进行多表查询的时候，就需要把有意义的数据筛选出来，过滤掉无意义的数据。筛序的条件叫做连接条件。有的时候，需要进行与行之间的比较。针对指定的列进行分组，把这一列中值相同的行，分到一组中，得到若干个组。筛选条件为：学生表的id = 分数表的student_id。如果两个表中，里面的记录都是存在对应关系，内连接和外连接的结果就是一致的。把这一列的若干行，进行求和算数运算（只能针对数字类型使用）如果存在不对于的记录，内连接和外连接就会出现差别。
复制链接

扫一扫