MySQL多表连接查询实例

1 查询时要用到的表以及表中数据

（1）学生表（student）

表中的数据如下：

（2）课程表（course）

表中的数据如下：

（3）教师表（teacher）

表中数据如下：

（4）成绩表（score）

表中数据如下：

（5）教师—课程表（teach）

表中数据如下：

表之间的关系：
1、学生与课程之间是多对多(m:n)的关系：一名学生要学习多门课程，一门课程有多名学生学习。
2、教师与课程之间是多对多(m:n)的关系：一个老师可以上多门课程，一门课程可以由多个老师上

2 实例

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

/*方法1：以下方法更加易于理解*/
SELECT
	a.*, /*查询a表中所有内容*/
	b.s_score AS "语文", /*查询b表中s_score字段下的数据并将查询结果命名为“语文”*/
	c.s_score AS "数学" /*查询c表中s_score字段下的数据并将查询结果命名为“数学”*/
FROM
	student AS a, /*student表命名为a表*/
	score AS b, /*score表命名为b表*/
	score AS c /*score表命名为c表*/
WHERE
	a.s_id = b.s_id /*a表中学号与b表中学号相同*/
	AND a.s_id = c.s_id /*a表中学号与c表中学号相同*/
	AND b.c_id = "01" /*b表中的课程号为01*/
	AND c.c_id = "02" /*c表中的课程号为02*/
	AND b.s_score > c.s_score; /*01的成绩大于02的成绩*/

/*方法2：以下方法效率更高*/
SELECT
	a.*, /*查询a表中所有内容*/
	b.s_score AS "语文", /*查询b表中s_score字段下的数据并将查询结果命名为“语文”*/
	c.s_score AS "数学" /*查询c表中s_score字段下的数据并将查询结果命名为“数学”*/
FROM
	student a /*student表命名为a表*/
	JOIN score b ON a.s_id = b.s_id /*a表与b表连接，条件是a表中学号等于b表中学号*/
	AND b.c_id = "01" /*b表中的课程号为01*/
	LEFT JOIN score c ON a.s_id = c.s_id /*a表与c表建立左连接，条件是a表中学号等于c表中学号*/
	AND c.c_id = "02" /*c表中的课程号为02*/
WHERE
	b.s_score > c.s_score; /*01的成绩大于02的成绩*/

注意：
1、join表示连接，与on连用，on后面加连接的条件
2、left join表示左连接，左表中的信息全部显示出来，再把符合条件的信息显示出来

2、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	a.s_id, /*查询学生编号*/
	a.s_name, /*查询学生姓名*/
	COUNT( b.c_id ) AS "选课总数", /*查询课程号的数量作为选课总数*/
	SUM( b.s_score ) AS "总成绩" /*查询成绩之和作为总成绩*/
FROM
	student a /*student表命名为a表*/
	LEFT JOIN score b ON a.s_id = b.s_id /*a表与b表左连接，连接条件是a表的学号等于b表的学号*/
GROUP BY
	a.s_id;/*按照学号进行分组*/

3、查询"李"姓老师的数量

SELECT
	COUNT( t_id ) /*查询教师编号的数量*/
FROM
	teacher 
WHERE
	t_name LIKE "李%"; /*模糊查询*/

4、查询学过“张三”老师的课的同学的信息

/*方法1*/
SELECT
	* 
FROM
	student 
WHERE
	s_id IN (
	SELECT
		s_id 
	FROM
		score 
	WHERE
	c_id IN ( SELECT c_id FROM teach WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = "张三" ) ) 
	)
/*1、从教师表（teacher）中查询出来张三老师的教师编号*/
/*2、从授课表（teach）中查询出张三老师教的课程*/
/*3、从成绩表中（score）中查询出来学习了该课程的学生的学号*/
/*4、从学生表（student）中根据学生的学号查询出来学生的信息*/

/*方法2*/
SELECT
	a.* 
FROM
	student a
	JOIN score b ON a.s_id = b.s_id 
WHERE
	b.c_id IN ( SELECT c_id FROM teach WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = "张三" ) )

5、查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息

SELECT
	* 
FROM
	student 
WHERE
	s_id IN ( SELECT s_id FROM score WHERE c_id = "01" ) 
	AND s_id NOT IN ( SELECT s_id FROM score WHERE c_id = "02" );