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