热门标签
当前位置: article > 正文
【MySQL】_5.MySQL的联合查询_mysql联合查询
作者：weixin_40725706 | 2024-02-09 13:40:39
赞
踩
mysql联合查询
目录
2.3 示例3：查询所有同学的科目及各科成绩，及同学的个人信息
联合查询也称为多表查询，是将多个表联合到一起进行查询；
1. 笛卡尔积
笛卡尔积是联合查询的基础，笛卡尔积其实就是一种排列组合，把两张表的记录尽可能地排列组合出n种情况：
以两张表：班级表与学生表为例，计算这两个表的笛卡尔积：
笛卡尔积就是得到了一张更大的表，其列数为两个表列数之和，行数为两个表列数之积；
试在testdemo1数据库下创建以下表：
- mysql> show tables;
- +---------------------+
- | Tables_in_testdemo1 |
- +---------------------+
- | classes |
- | course |
- | score |
- | student |
- +---------------------+
- 4 rows in set (0.00 sec)
表的结构与内容分别为：
（1）student表：
- mysql> desc student;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | sn | varchar(20) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | qq_mail | varchar(20) | YES | | NULL | |
- | classes_id | int(11) | YES | | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 5 rows in set (0.00 sec)
-
- 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.00 sec)
（2）classes表：
- mysql> desc classes;
- +-------+--------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+--------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | YES | | NULL | |
- | desc | varchar(100) | YES | | NULL | |
- +-------+--------------+------+-----+---------+----------------+
- 3 rows in set (0.00 sec)
-
- mysql> select* from classes;
- +----+-------------------+-----------------------------------------------+
- | id | name | desc |
- +----+-------------------+-----------------------------------------------+
- | 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 |
- | 2 | 中文系2019级3班 | 学习了中国传统文学 |
- | 3 | 自动化2019级5班 | 学习了机械自动化 |
- +----+-------------------+-----------------------------------------------+
- 3 rows in set (0.00 sec)
（3）course表：
- mysql> desc course;
- +-------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+----------------+
- 2 rows in set (0.00 sec)
-
- mysql> select* from course;
- +----+--------------+
- | id | name |
- +----+--------------+
- | 1 | Java |
- | 2 | 中国传统文化 |
- | 3 | 计算机原理 |
- | 4 | 语文 |
- | 5 | 高阶数学 |
- | 6 | 英文 |
- +----+--------------+
- 6 rows in set (0.00 sec)
（4）score表：
- mysql> desc score;
- +------------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+--------------+------+-----+---------+-------+
- | score | decimal(3,1) | YES | | NULL | |
- | student_id | int(11) | YES | | NULL | |
- | course_id | int(11) | YES | | NULL | |
- +------------+--------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
-
- 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 |
- +-------+------------+-----------+
- 20 rows in set (0.00 sec)
在该数据库中四张表，三个实体：学生、班级、课程；
其中学生和班级是一对多关系，学生和课程是多对多关系（成绩表是关联表），班级和课程之间没有直接的关联关系；
2. 内连接
内连接表示语法有两种：
第一种：
select [列名],[列名]... form [表1],[表2] where 条件；
第二种：
select [列名],[列名] from [表1] join [表2] on 条件;2.1 示例1：查询许仙同学的成绩
（“许仙”是名字在student表中，“成绩”在score表中，位于不同的表中，需要进行联合查询）
将student表与score表进行笛卡尔积——>删去无效数据——>按照许仙名字来筛选——>必要时对结果进行精简；
（1）计算student表和score表的笛卡尔积：
mysql> select* from student,score;（2）根据两个表的关联列是否对应，删去无效数据：
mysql> select* from student, score where id = student_id;注：当联合查询的关联列名重名时，可以使用表名.列名进行指定，即上文SQL指令也可以写为：
mysql> select* from student, score where student.id = score.student_id;在实际开发中，更建议采用这种写法，避免当表多列多的情况下产生混淆；
（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.course_id, score.score from student, score
- -> where student.id = score.student_id and student.name = "许仙";
- +------+-----------+-------+
- | name | course_id | score |
- +------+-----------+-------+
- | 许仙 | 1 | 67.0 |
- | 许仙 | 3 | 23.0 |
- | 许仙 | 5 | 56.0 |
- | 许仙 | 6 | 72.0 |
- +------+-----------+-------+
- 4 rows in set (0.00 sec)
注：计算笛卡尔积即其筛选方式有两种：
第一种：
select* from [表名],[表名] where [条件];第二种：
select* from [表名] join [表名] on [条件]; 故而上文的SQL语句也可以写为：
- mysql> select student.name, score.course_id, score.score from
- -> student join score
- -> on student.id = score.student_id and student.name="许仙";
2.2 示例2： 查询所有同学的总成绩，及同学的个人信息
（查询信息关系到学生表与成绩表）
将student表和score表进行笛卡尔积计算——>根据联合列学生id删去无效信息——>根据学生name或id进行分组并根据分组情况对score进行求和
（1）将student表和score表进行笛卡尔积计算并对无效信息进行删除：
mysql> select* from student,score where student.id = score.student_id;（2）根据学生id进行分组，根据分组使用聚合函数sum进行聚合计算总成绩：
- mysql> select student.name, sum(score.score)from student,score where student.id = score.student_id group by id;
- +------------+------------------+
- | name | sum(score.score) |
- +------------+------------------+
- | 黑旋风李逵 | 300.0 |
- | 菩提老祖 | 119.5 |
- | 白素贞 | 200.0 |
- | 许仙 | 218.0 |
- | 不想毕业 | 118.0 |
- | 好好说话 | 178.0 |
- | tellme | 172.0 |
- +------------+------------------+
- 7 rows in set (0.00 sec)
2.3 示例3：查询所有同学的科目及各科成绩，及同学的个人信息
（同学姓名在student表中，科目信息在course表中，各科成绩在score表中）
- mysql> select student.name, course.name, score.score
- -> from student, course, score
- -> where student.id = score.student_id
- -> and score.course_id = course.id;
- +------------+--------------+-------+
- | name | name | score |
- +------------+--------------+-------+
- | 黑旋风李逵 | Java | 70.5 |
- | 黑旋风李逵 | 计算机原理 | 98.5 |
- | 黑旋风李逵 | 高阶数学 | 33.0 |
- | 黑旋风李逵 | 英文 | 98.0 |
- | 菩提老祖 | Java | 60.0 |
- | 菩提老祖 | 高阶数学 | 59.5 |
- | 白素贞 | Java | 33.0 |
- | 白素贞 | 计算机原理 | 68.0 |
- | 白素贞 | 高阶数学 | 99.0 |
- | 许仙 | Java | 67.0 |
- | 许仙 | 计算机原理 | 23.0 |
- | 许仙 | 高阶数学 | 56.0 |
- | 许仙 | 英文 | 72.0 |
- | 不想毕业 | Java | 81.0 |
- | 不想毕业 | 高阶数学 | 37.0 |
- | 好好说话 | 中国传统文化 | 56.0 |
- | 好好说话 | 语文 | 43.0 |
- | 好好说话 | 英文 | 79.0 |
- | tellme | 中国传统文化 | 80.0 |
- | tellme | 英文 | 92.0 |
- +------------+--------------+-------+
- 20 rows in set (0.00 sec)
3. 外连接
内连接与外连接都是进行笛卡尔积计算，但是细节之处仍有差别：
3.1 情况一：两个表数据一一对应
基于以下数据库与表：
- mysql> select* from student;
- +------+------+
- | id | name |
- +------+------+
- | 1 | 张三 |
- | 2 | 李四 |
- | 3 | 王五 |
- +------+------+
- 3 rows in set (0.00 sec)
-
- mysql> select* from score;
- +------------+-------+
- | student_id | score |
- +------------+-------+
- | 1 | 90 |
- | 2 | 80 |
- | 3 | 70 |
- +------------+-------+
- 3 rows in set (0.00 sec)
内连接指令为：
mysql> select name, score from student join score on student.id = score.student_id;左外连接指令为：
mysql> select name, score from student left join score on student.id = score.student_id;右外连接指令为：
mysql> select name, score from student left join score on student.id = score.student_id;以上三条指令的查询结果均为：
- +------+-------+
- | name | score |
- +------+-------+
- | 张三 | 90 |
- | 李四 | 80 |
- | 王五 | 70 |
- +------+-------+
即：当两个表的数据一一对应（即两个表的记录在彼此表中都有体现）时，内连接与外连接的查询结果是相同的；
3.2 情况二：两个表数据并非一一对应
基于以下数据库和表：
- mysql> select* from student;
- +------+------+
- | id | name |
- +------+------+
- | 1 | 张三 |
- | 2 | 李四 |
- | 3 | 王五 |
- +------+------+
- 3 rows in set (0.00 sec)
-
- mysql> select* from score;
- +------------+-------+
- | student_id | score |
- +------------+-------+
- | 1 | 90 |
- | 2 | 80 |
- | 4 | 70 |
- +------------+-------+
- 3 rows in set (0.00 sec)
内连接指令与查询结果为：
- mysql> select name, score from student join score on student.id = score.student_id;
- +------+-------+
- | name | score |
- +------+-------+
- | 张三 | 90 |
- | 李四 | 80 |
- +------+-------+
- 2 rows in set (0.00 sec)
左外连接指令与查询结果为：
- mysql> select name, score from student left join score on student.id = score.student_id;
- +------+-------+
- | name | score |
- +------+-------+
- | 张三 | 90 |
- | 李四 | 80 |
- | 王五 | NULL |
- +------+-------+
- 3 rows in set (0.00 sec)
右外连接指令与查询结果为：
- mysql> select name, score from student right join score on student.id = score.student_id;
- +------+-------+
- | name | score |
- +------+-------+
- | 张三 | 90 |
- | 李四 | 80 |
- | NULL | 70 |
- +------+-------+
- 3 rows in set (0.00 sec)
即：当两个表的数据并非一一对应时，内连接只显示两个表中都有体现的数据；
注：（1）当两个表数据并非一一对应，进行外连接时，左外连接是以左表为准，右表没有对应的数据，则以空值填充；
右外连接是以右表为准，左表没有对应数据，以空值填充；
（2）join on针对多个表进行的语句为：
select* form 表1 join 表2 on 条件1 join 表4 on 条件2，如：
- mysql> select* from student join score on student.id = score.student_id
- -> join course on course.id =score.course_id;
但是每次join on 语句都只计算两个表的笛卡尔积；
4. 自连接
自连接是表自身与自身做笛卡尔积，在SQL中进行条件查询，都是指定某一列或多个列之间进行关系运算，无法进行行与行之间的运算，在某些情况下需要对行与行之间进行关系运算，就要使用到自连接。自连接的本质是将行转为列；
示例：显示所有“课程id为3”比“课程id为1”成绩高的成绩信息：
（成绩信息在score表中）
（1）对score进行自连接（别名求笛卡尔积）并删除无效信息：
mysql> select* from score as s1, score as s2 where s1.student_id = s2.student_id;（2）选出第一列id=1的课程与第二列id=3的课程：
- mysql> select* from score as s1, score as s2
- -> where s1.student_id = s2.student_id
- -> and s1.course_id = 1
- -> and s2.course_id = 3;
（该结果表示有三个同学同时选修了这两门课程）
（3）增加左列成绩小于右列成绩条件，SQL指令与查询结果为：
- mysql> select* from score as s1,score as s2
- -> where s1.student_id = s2.student_id
- -> and s1.course_id = 1
- -> and s2.course_id = 3
- -> and s1.score < s2.score;
- +-------+------------+-----------+-------+------------+-----------+
- | score | student_id | course_id | score | student_id | course_id |
- +-------+------------+-----------+-------+------------+-----------+
- | 70.5 | 1 | 1 | 98.5 | 1 | 3 |
- | 33.0 | 3 | 1 | 68.0 | 3 | 3 |
- +-------+------------+-----------+-------+------------+-----------+
- 2 rows in set (0.00 sec)
注：（1）不能直接进行自连接：
- mysql> select* from score,score;
- ERROR 1066 (42000): Not unique table/alias: 'score'
需要为表指定两个别名，即：
mysql> select* from score as s1, score as s2;5. 子查询（嵌套查询）
子查询是指嵌入其他SQL语句中的select语句，即将多个查询语句合并为一个语句；
5.1 子查询分类
（1）单行子查询：查询结果只有一条记录；
（2）多行子查询：查询结果为多条记录；
5.2 单行子查询示例1：查询不想毕业同学的同班同学
（1）分步查询SQL指令及查询结果为：
- mysql> select classes_id from student where name="不想毕业";
- +------------+
- | classes_id |
- +------------+
- | 1 |
- +------------+
- 1 row in set (0.00 sec)
-
- mysql> select name from student where classes_id =1;
- +------------+
- | name |
- +------------+
- | 黑旋风李逵 |
- | 菩提老祖 |
- | 白素贞 |
- | 许仙 |
- | 不想毕业 |
- +------------+
- 5 rows in set (0.00 sec)
（2）子查询SQL指令及查询结果为：
- mysql> select name from student where classes_id = (select classes_id from student where name="不想毕业");
- +------------+
- | name |
- +------------+
- | 黑旋风李逵 |
- | 菩提老祖 |
- | 白素贞 |
- | 许仙 |
- | 不想毕业 |
- +------------+
- 5 rows in set (0.00 sec)
即将条件查询的某一个值替换为一个select查询语句；
5.3 多行子查询示例2：查询语文或英语课程的信息成绩
先查询出两个课程的课程id，再根据course_id在score表中查询；
（1）分步查询SQL指令及查询结果为：
- mysql> select id from course where name="语文" or name="英文";
- +----+
- | id |
- +----+
- | 4 |
- | 6 |
- +----+
- 2 rows in set (0.00 sec)
-
- mysql> select* from score where course_id in(4,6);
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 98.0 | 1 | 6 |
- | 72.0 | 4 | 6 |
- | 43.0 | 6 | 4 |
- | 79.0 | 6 | 6 |
- | 92.0 | 7 | 6 |
- +-------+------------+-----------+
- 5 rows in set (0.00 sec)
（2）子查询SQL指令及查询结果为：
- mysql> select* from score where course_id in(select id from course where name="语文" or name="英文");
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 98.0 | 1 | 6 |
- | 72.0 | 4 | 6 |
- | 43.0 | 6 | 4 |
- | 79.0 | 6 | 6 |
- | 92.0 | 7 | 6 |
- +-------+------------+-----------+
- 5 rows in set (0.00 sec)
6. 合并查询
合并查询就是将两个查询语句的结果合并到一起；
6.1 示例1：查询id=3或者名字为英文的课程
（1）使用逻辑或实现查询：
- mysql> select* from course where id<3 or name="英文";
- +----+--------------+
- | id | name |
- +----+--------------+
- | 1 | Java |
- | 2 | 中国传统文化 |
- | 6 | 英文 |
- +----+--------------+
- 3 rows in set (0.00 sec)
（2）使用union关键字进行合并查询：
- mysql> select* from course where id<3 union select* from course where name="英文";
- +----+--------------+
- | id | name |
- +----+--------------+
- | 1 | Java |
- | 2 | 中国传统文化 |
- | 6 | 英文 |
- +----+--------------+
- 3 rows in set (0.00 sec)
注：（1）union与逻辑或的区别：
逻辑或只能对一张表的查询结果进行合并，但union可以对多张表的查询结果进行合并（要求多个结果的列须对应）；
（2）union与union all的区别：
使用union关键字对多个查询结果进行合并时会自动去重，但unionall不会去重；
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/72173
推荐阅读
相关标签
