MySQL的多表联合查询

一.多表查询简介:
连接查询:
交叉连接:
交叉连接:查询到的是两个表的笛卡尔积
语法:
select * from 表1 cross join 表2 on关联条件
内连接:(inner join , 其中inner可以省略)
显示内连接:在SQL中显示的调用inner join关键字
语法:
select * from 表1 inner join 表2 on 关联条件
隐式内连接:在SQL中没有调用inner join关键字
语法:
select * from 表1,表2 where 关联条件

外链接:(outer join , outer是可以省略的)
左外连接:
语法:select * from 表1 left outer join 表2 on 关联条件
右外连接:
语法:select * from 表1 right outer join 表2 on 关联条件
子查询:

子查询:一个查询语句条件需要依赖另一个语句的结果
主要关键字有:in,any,all和exis四个.其中比较常用的是in;

实例如下:
1.这里写代码片
– 1、查询平均成绩大于70分的同学的学号和平均成绩
SELECT s.id,AVG(sc.score) FROM student s,studentcourse sc WHERE s.id=sc.student_id GROUP BY s.id HAVING AVG(sc.score)>70;

– 2、查询所有同学的学号、姓名、选课数、总成绩
SELECT s.id,s.name,COUNT(c.id),SUM(sc.score) FROM student s,course c,studentcourse sc WHERE s.id=sc.student_id AND c.id=sc.course_id GROUP BY s.id;

– 3、查询没学过关羽老师课的同学的学号、姓名

– 分解:3.1所有选择该老师的学生
SELECT s.name,s.id FROM student s,course c,studentcourse sc,teacher t WHERE t.id=c.teacher_id AND s.id=sc.student_id AND c.id=sc.course_id AND t.id =1 GROUP BY s.id;

– 分解:3.2 去除3.1中的所有学生

SELECT s.name,s.id FROM student s WHERE s.id NOT IN (SELECT s.id FROM student s,course c,studentcourse sc,teacher t WHERE t.id=c.teacher_id AND s.id=sc.student_id AND c.id=sc.course_id AND t.name =’关羽’);

– 第二种方法:
SELECT id,NAME
FROM student
WHERE id NOT IN (SELECT student_id
FROM studentcourse
WHERE course_id IN (SELECT course.id
FROM teacher,course
WHERE teacher.id=course.teacher_id
AND teacher.name=’关羽’));

– 4、查询学过赵云老师所教课的同学的学号、姓名
SELECT s.name,s.id
FROM student s
WHERE s.id IN(SELECT student_id
FROM studentcourse
WHERE course_id IN(SELECT course.id
FROM teacher,course
WHERE teacher.id=course.teacher_id
AND teacher.name=’赵云’));

– 5、查询没有学三门课以上的同学的学号、姓名
SELECT id,NAME
FROM student
WHERE student.id NOT IN (SELECT s.id
FROM studentcourse sc,student s
WHERE sc.student_id=s.id GROUP BY s.id HAVING COUNT(sc.course_id)>=3);

– 6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

SELECT c.id AS ‘课程ID’,
MAX(sc.score) AS ‘最高分’,
MIN(sc.score)AS ‘最低分’
FROM studentcourse sc,course c
WHERE sc.course_id=c.id
GROUP BY c.id;

– 7、查询学生信息和平均成绩
SELECT s.id,s.name,s.city,s.age, c.name,sc.score,t.name
FROM student s,studentcourse sc,course c,teacher t
WHERE s.id=sc.student_id AND c.id=sc.course_id AND c.teacher_id=t.id GROUP BY s.id;

– 8、查询上海和北京学生数量
SELECT s.city,COUNT(*)
FROM student s
WHERE s.city IN(‘上海’,’北京’)GROUP BY s.city;

– 9、查询不及格的学生信息和课程信息
SELECT s.id,s.name,c.name,sc.score
FROM student s,studentcourse sc,course c
WHERE s.id=sc.student_id AND c.id=sc.course_id GROUP BY s.id HAVING sc.score<60;

– 10、统计每门课程学生选修人数（超过四人的进行统计)

SELECT c.id,c.name,COUNT(sc.course_id)
FROM course c,studentcourse sc
WHERE c.id=sc.course_id GROUP BY c.id HAVING COUNT(sc.course_id)>4;

– 11按平均成绩从低到高显示所有学生课程成绩，按如下形式显示： 学生ID,课程名,学生选课数,平均分；
SELECT s.name AS ‘学生’,
(SELECT sc.score FROM studentcourse sc WHERE sc.student_id=s.id AND sc.course_id=1) AS ‘骑马’ ,
(SELECT sc.score FROM studentcourse sc WHERE sc.student_id=s.id AND sc.course_id=2) AS ‘射箭’ ,
(SELECT sc.score FROM studentcourse sc WHERE sc.student_id=s.id AND sc.course_id=3) AS ‘忍术’ ,
(SELECT sc.score FROM studentcourse sc WHERE sc.student_id=s.id AND sc.course_id=4) AS ‘战术’ ,
(SELECT sc.score FROM studentcourse sc WHERE sc.student_id=s.id AND sc.course_id=5) AS ‘格斗’ ,
COUNT(sc.course_id) AS ‘选课数’,
AVG(sc.score) ‘平均分’
FROM student s,studentcourse sc,course c
WHERE s.id=sc.student_id AND c.id=sc.course_id
GROUP BY s.id
ORDER BY AVG(sc.score) DESC;

– 12.按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT c.id,c.name,AVG(sc.score),
SUM(CASE WHEN IFNULL(sc.Score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS ‘Percent(%)’
FROM studentcourse sc,course c
WHERE sc.course_id=c.id
GROUP BY c.id

– 13.查询不同老师所教不同课程平均分从高到低显示；
SELECT c.id,c.name,t.name,AVG(sc.score)
FROM studentcourse sc,course c,teacher t
WHERE c.id=sc.course_id AND c.teacher_id=t.id
GROUP BY c.id
ORDER BY AVG(sc.score) DESC;

– 14 检索射箭课程分数，按分数降序排列,显示:同学学号,姓名
SELECT s.id,s.name,AVG(sc.score)
FROM student s,studentcourse sc,course c
WHERE s.id=sc.student_id AND c.id=sc.course_id AND c.name=’射箭’
GROUP BY s.id
ORDER BY AVG(sc.score) DESC;

– 15.查询两门以上不及格课程的同学的学号及其平均成绩,显示:学生id,学生name,平均分
SELECT s.id,s.name,AVG(sc.score)
FROM student s,studentcourse sc,course c
WHERE s.id=sc.student_id AND sc.score<60 AND s.id IN
(
SELECT sc.student_id FROM studentcourse sc,course c
WHERE sc.course_id=c.id AND sc.score<60
GROUP BY sc.student_id HAVING COUNT(*)>2
)
GROUP BY s.id;

好多都是一样的,需要总结规律,以上,感觉学完之后好长时间不用就会忘得好快,写在这里,可以自己偶尔看看!