MySQL多表连接查询

join多表查询

1.1 语法

2个表连接查询
select a.name,b.address from 
a join b
on a.id=b.id
where a.name='xxxxx'

2个表以上的连接查询
select a.name,b.address from
a join b
on a.id=b.id
join c
on c.uname=b.uno
where a.name='xxxxx'

1.2 查询一下世界上人口数量小于100w人的城市名,国家名,国土面积

SELECT city.name,country.name,country.surfacearea FROM
city JOIN country
ON city.countrycode=country.code
WHERE city.population<1000000;

1.3 查询城市shenyang,城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT country.name,city.population,country.surfacearea FROM
city JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';

1.4 N多表联动查询

查看每位老师讲课名称

SELECT teacher.tname,course.cname FROM
teacher JOIN course
ON teacher.tno=course.tno

统计zhang3学习了几门课

SELECT student.sname,COUNT(sc.cno) FROM
student JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3' GROUP BY student.sname;

查询oldguo老师教的学生名

SELECT teacher.tname,GROUP_CONCAT(student.sname) FROM
teacher JOIN course 
ON teacher.tno=course.tno
JOIN sc 
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo';

查询oldguo所教课程的平均分数

SELECT teacher.tname,AVG(sc.score) FROM
teacher JOIN course
ON teacher.tno=course.tno 
JOIN sc
ON course.cno=sc.cno
WHERE teacher.tname='oldguo'

每位老师所教课程的平均分,并按平均分排序

SELECT teacher.tname,AVG(sc.score) FROM
teacher JOIN course
ON teacher.tno=course.tno 
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname 
ORDER BY AVG(sc.score) DESC;

查询oldguo所教的不及格学生姓名

SELECT teacher.tname,student.sname,sc.score FROM
teacher JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo'
AND sc.score<60;

查询所有老师所教学生不及格的信息

SELECT teacher.tname,student.sname,sc.score FROM
teacher JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE sc.score<60;

查询平均成绩大于60分的同学的学号和平均成绩

SELECT student.sno,student.sname,AVG(sc.score)  FROM
student JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING AVG(sc.score)<60;

##聚合函数一定要在group by后面做条件,不能直接where avg(sc.score)<60