MySQL数据库查询之多表查询总结

MySQL数据库查询之多表查询总结

在实际业务中，数据库往往并不仅仅只有一张表。为了更好地提高查询效率和查询结果的准确性，我们就需要用到多表查询了。

内连接（INNER JOIN）

内连接返回同时在两个表中存在的所有行，并且在连接的列上具有相同的值。它是最常用的一种多表查询方式，下面用一个示例来说明。

有两张表，分别是学生表（students）和成绩表（scores），这两个表之间存在外键关联，外键的字段名是student_id。

students表的结构如下：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

scores表的结构如下：

CREATE TABLE `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_id` int(11) NOT NULL COMMENT '学生id',
  `subject` varchar(20) NOT NULL COMMENT '课程',
  `score` int(11) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`),
  KEY `fk_scores_students` (`student_id`),
  CONSTRAINT `fk_scores_students` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';

现在我们要查询出scores表中每个学生的总分数和平均分数，以及学生的姓名和性别。可以使用如下SQL语句：

SELECT s.name, s.gender, SUM(score) AS total_score, AVG(score) AS avg_score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
GROUP BY sc.student_id;

这里使用了内连接来实现多表查询，s是students表的别名，sc是scores表的别名。INNER JOIN语句将两个表连接在一起，s.id和sc.student_id是连接的条件，SUM()和AVG()用来计算每个学生的总分数和平均分数，GROUP BY语句将结果按照student_id分组。

左连接（LEFT JOIN）

左连接返回所有左表中的记录以及右表中那些和左表中记录关联的记录。如果右表中没有匹配的记录，就返回NULL值。下面用一个示例来说明。

有两张表，分别是学生表（students）和选课表（courses），这两个表之间存在外键关联，外键的字段名是student_id。

students表的结构如下：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

courses表的结构如下：

CREATE TABLE `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_id` int(11) NOT NULL COMMENT '学生id',
  `course` varchar(20) NOT NULL COMMENT '课程',
  PRIMARY KEY (`id`),
  KEY `fk_courses_students` (`student_id`),
  CONSTRAINT `fk_courses_students` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='选课表';

现在我们要查询出每个学生选修的课程和未选的课程。可以使用如下SQL语句：

SELECT s.name, c.course
FROM students s
LEFT JOIN courses c ON s.id = c.student_id;

这里使用了左连接来实现多表查询，s是students表的别名，c是courses表的别名。LEFT JOIN语句将students表与courses表进行连接，s.id和c.student_id是连接的条件。由于要查询出每个学生选修的课和未选的课，所以这里只需要查询students表和courses表之间的关系即可。

总结

通过内连接和左连接，我们可以实现多张表之间的数据关联，提高查询效率和查询结果的准确性。需要注意的是，在使用多表查询时，我们需要谨慎考虑表之间的关系，尽可能地使用索引来提高查询效率。