3表联查sql语句

************************************学生表Student*******************************************

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuId` varchar(255) NOT NULL,
  `stuName` varchar(255) NOT NULL,
  PRIMARY KEY (`stuId`)

)

INSERT INTO `student` VALUES ('100', '张三');
INSERT INTO `student` VALUES ('101', '李四');
INSERT INTO `student` VALUES ('102', '王五');

INSERT INTO `student` VALUES ('103', '张三');

**********************************课程表Course*********************************************

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `courseId` varchar(255) NOT NULL,
  `courseName` varchar(255) NOT NULL,
  PRIMARY KEY (`courseId`)

)

INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');

INSERT INTO `course` VALUES ('3', '英语');

*************************************成绩表Score*****************************************

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `stuId` varchar(255) NOT NULL,
  `courseId` varchar(255) NOT NULL,
  `score` varchar(255) DEFAULT NULL

)

INSERT INTO `score` VALUES ('100', '1', '10');
INSERT INTO `score` VALUES ('100', '2', '20');
INSERT INTO `score` VALUES ('100', '3', '30');
INSERT INTO `score` VALUES ('101', '1', '40');
INSERT INTO `score` VALUES ('101', '2', '50');
INSERT INTO `score` VALUES ('101', '3', '60');
INSERT INTO `score` VALUES ('102', '1', '70');
INSERT INTO `score` VALUES ('102', '2', '80');
INSERT INTO `score` VALUES ('103', '2', '110');

INSERT INTO `score` VALUES ('103', '3', '120');

*************************************练习****************************************

1.查询李四的数学成绩

SELECT score from score where stuId in(SELECT stuId from student where stuName='李四')

AND courseId in(SELECT courseId from course WHERE courseName='数学')

结果：

2.查询每个学生的平均成绩

SELECT student.stuId,student.stuName, avg(score.score) from student,score where student.stuId = score.stuId

GROUP BY score.stuId ORDER BY avg(score.score) DESC

3.查询所有学生的成绩

SELECT a.*,score.score from (SELECT *from student
FULL  JOIN course ) as a 
LEFT JOIN score ON score.courseId = a.courseId and score.stuId = a.stuId

ORDER BY a.stuId,a.courseId