数据库
student表
student_id | student_name |
---|
course表
course_id | course_name |
---|
course_score
score_id | student_id | course_id | score |
---|
建表测试语句
CREATE TABLE IF NOT EXISTS student (
student_id varchar(255) NOT NULL,
student_name varchar(255) NOT NULL,
PRIMARY KEY (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS course (
course_id varchar(255) NOT NULL,
course_name varchar(255) NOT NULL,
PRIMARY KEY (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS course_score (
score_id varchar(255) NOT NULL,
student_id varchar(255) NOT NULL,
course_id varchar(255) NOT NULL,
score int(255) DEFAULT NULL,
PRIMARY KEY (score_id,student_id,course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES ('s1', '小明');
INSERT INTO student VALUES ('s2', '小宇');
INSERT INTO student VALUES ('s3', '小辉');
INSERT INTO course VALUES ('c1', '操作系统');
INSERT INTO course VALUES ('c2', '数据库');
INSERT INTO course VALUES ('c3', '计网');
INSERT INTO course_score VALUES ('1','s1','c1', 99);
INSERT INTO course_score VALUES ('2','s1','c2', 97);
INSERT INTO course_score VALUES ('3','s1','c3', 98);
INSERT INTO course_score VALUES ('4','s2','c1', 59);
INSERT INTO course_score VALUES ('5','s2','c2', 87);
INSERT INTO course_score VALUES ('6','s2','c3', 95);
INSERT INTO course_score VALUES ('7','s3','c1', 90);
INSERT INTO course_score VALUES ('8','s3','c2', 94);
INSERT INTO course_score VALUES ('9','s3','c3', 89);
小明各科按成绩排序输出课程名称和成绩
三表left join
SELECT course_name,score FROM
(( course_score LEFT JOIN student ON course_score.student_id = student.student_id)
LEFT JOIN course ON course_score.course_id = course.course_id )
WHERE student.student_name = "小明"
ORDER BY score desc
输出总成绩第一名的学生姓名和总分
SELECT student_name,SUM(score)
FROM
(course_score LEFT JOIN student on course_score.student_id = student.student_id)
GROUP BY course_score.student_id
ORDER BY SUM(score) DESC LIMIT 1;