mysql一些查询操作

use hongxing
show TABLES
desc score
desc student
insert into student values
(18,'肖丹','female',2),
(19,'刘小','male',2),
(20,'田五','female',2)
insert into score values
(57,4,18,83),
(58,4,19,91),
(59,4,20,95)

查询所有学生的姓名，学号，选课数，总成绩
SELECT st.st_id,st.st_name,count(st.st_id) as kemushu,sum(sc.num) as zongchengji from student as st INNER JOIN score as sc on st.st_id = sc.student_id GROUP BY st.st_id

查询姓李老师的个数
SELECT count(teacher.tname) from teacher WHERE teacher.tname like "李%"

查询没有报李萍老师课程的学生姓名
SELECT * from student
INNER JOIN score on student.st_id = score.student_id
INNER JOIN course on score.course_id = course.co_id
INNER JOIN teacher on course.teacher_id=teacher.tid
WHERE teacher.tname="李萍老师"
两张表整合
SELECT student.st_name FROM student WHERE student.st_name not in (SELECT student.st_name from student
INNER JOIN score on student.st_id = score.student_id
INNER JOIN course on score.course_id = course.co_id
INNER JOIN teacher on course.teacher_id=teacher.tid
WHERE teacher.tname="李萍老师")

查询品德比美术课程高的学生学号:
1先查询品德课程每一个学生的分数2在查询美术课程的每一个学生的分数3.两张表整合在一起比较
SELECT score.student_id,score.num FROM score INNER JOIN course on score.course_id =course.co_id where course.cname="品德"
SELECT score.student_id,score.num FROM score INNER JOIN course on score.course_id =course.co_id where course.cname="美术"

SELECT t1.student_id FROM
(SELECT score.student_id,score.num FROM score INNER JOIN course on score.course_id =course.co_id where course.cname="品德") as t1
INNER JOIN (SELECT score.student_id,score.num FROM score INNER JOIN course on score.course_id =course.co_id where course.cname="美术")as t2 on t1.student_id= t2.student_id WHERE t1.num > t2.num

查询没有同时选修物理美术和品德课程的学生姓名 1.先查询选修品德或者美术的学生
SELECT student.st_name from student inner join score on student.st_id=score.student_id
INNER JOIN course on score.course_id = course.co_id
WHERE course.cname="品德" or course.cname="美术" GROUP BY student.st_id
HAVING count(student.st_id)<2

查询挂科超过两门（包括两门）的学生姓名和班级
SELECT student.st_id,student.class_id from score INNER JOIN student on score.student_id = student.st_id where score.num < 60 GROUP BY student.st_id HAVING count(student.st_id)=2

SELECT class.cpation,t3.st_name FROM class INNER JOIN (SELECT student.st_name,student.class_id from score INNER JOIN student on score.student_id = student.st_id where score.num < 60 GROUP BY student.st_id HAVING count(student.st_id)=2) as t3 on class.cid=t3.class_id

查询选修了所有课程的学生姓名
SELECT count(course.co_id) from course
SELECT student.st_id,student.st_name from student INNER JOIN score on student.st_id = score.student_id
GROUP BY student.st_id HAVING count(student.st_id)=(SELECT count(course.co_id) from course)

查询李萍老师教的课程的所有学生(id)的成绩记录
SELECT score.student_id as 品德,score.num as 分数 FROM teacher INNER JOIN course on teacher.tid = course.teacher_id
INNER JOIN score on score.course_id=course.co_id WHERE teacher.tname="李萍老师"

查询全部学生都选修了的课程ID和课程名
SELECT score.student_id from score INNER JOIN course on score.course_id = course.co_id GROUP BY score.student_id
HAVING count(score.student_id)=4

查询每门课程被选修的次数
第一种是子查询
SELECT * FROM (SELECT course.co_id,course.cname FROM course) as t0 INNER JOIN
(SELECT course_id,count(course_id)FROM score GROUP BY course_id) as t1
on t0.co_id= t1.course_id
第二种联合查询
SELECT course.cname,count(score.course_id) FROM score INNER JOIN course on score.course_id =course.co_id GROUP BY score.course_id

查询只选修了一门课程的学生姓名和学号
SELECT student.st_id,student.st_name FROM score INNER JOIN student on score.student_id = student.st_id
GROUP BY student.st_id HAVING count(student.st_id)=1

查询所有学生考出的成绩并安从高到底排序（成绩去重）并显示每个分数段人数
SELECT score.num,count(score.num) FROM score GROUP BY score.num ORDER BY score.num desc

查询平均成绩大于85的学生姓名和平均成绩
SELECT student.st_name as 姓名,avg(score.num) as 平均成绩 FROM score INNER JOIN student on score.student_id = student.st_id GROUP BY student.st_id HAVING avg(score.num) >85

查询品德成绩不及格的学生姓名和对应的品德分数
SELECT student.st_name as 学生姓名,score.num as 品德分数 FROM student INNER JOIN score on student.st_id=score.student_id INNER JOIN course on score.course_id =course.co_id WHERE course.cname="品德" and score.num <60

查询在所有选修了李萍老师课程的学生中平均成绩最高的学生 ,LIMIT 1 只显示第一个数据 2 就是只显示前2个
SELECT student.st_name,avg(score.num) FROM student INNER JOIN score on student.st_id = score.student_id INNER JOIN course on score.course_id=course.co_id INNER JOIN teacher on course.teacher_id =teacher.tid WHERE teacher.tname="李萍老师" GROUP BY student.st_id
ORDER BY avg(score.num) desc LIMIT 1