yy笔试总结

数据库

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;