MySql查询语句实践

一、设计表

    1、设计表

    查询语句之前先设计四张表：student、teacher、course、score

    student：sid（学号）、sname(姓名)、sage（年龄）、ssex（性别）

    teacher：tid（老师编号）、tname（老师名字）

    course：cid（课程序号）、cname（课程名字）、tid（代课老师id）

    score：sid（学生id）、cid（课程id）、score（成绩）

    2、找表关系

    可以看到四张表之间是有联系的

    

二、查询语句实线

    1、查询（“001”课程比“002”课程成绩高）的所有的学生的学号。

        思路：先找出上过001课程的学生，再找出上过002课程的学生，再找出两门课都上过且001比002成绩高的学生。所以这里将SELECT sid,score FROM score WHERE cid='001'和SELECT sid,score FROM score WHERE cid='002'的结果集作为查询对象

SELECT a.sid AS '学号' 
FROM (SELECT sid,score FROM score WHERE cid='001')AS a,
(SELECT sid,score FROM score WHERE cid='002')AS b
WHERE a.sid=b.sid AND a.score>b.score;

    2、查询平均成绩大于等于60分的同学的学号和平均成绩

        思路：此处需要注意不加group by的话，没有分组，聚合函数只会以score表的数据计算，所以avg（score）只有一个结果，但是sid却有多个。所以此处应该使用group by，这样的话，avg(score)计算的是分组后每个组的平均成绩

SELECT sid,avg(score) 
FROM score 
GROUP BY sid HAVING AVG(score)>=60;

    3、查询所有同学的学号、姓名、选课数、总成绩

        思路：一般应该是能够将要查询的内容都联系在一起的情况下查询尽可能少的表

SELECT s.sid '学号',sname '姓名',COUNT(score) '选课数',AVG( score) '平均成绩'
FROM student s,score WHERE s.sid=score.sid	GROUP BY s.sid;

    4、查询姓“李”的老师的个数

SELECT count(tname) FROM teacher WHERE tname LIKE "李_%" ;

    5、查询没学过“叶平”老师的课程的同学的学号、姓名

    Not In：不在范围内

SELECT sid,sname 
FROM student 
WHERE sid NOT IN(SELECT score.sid FROM teacher,course,score where course.tid=teacher.tid AND teacher.tname='叶平' AND course.cid=score.cid);

    6、查询所有有挂科的同学的学号和姓名

SELECT s.sid '学号',sname '姓名' 
FROM student s,score o 
where s.sid=o.sid AND score<60 GROUP BY sname;

    7、查询至少一门课程与学生学号为“2”的同学所学课程相同的学生学号和姓名

SELECT s.sid,s.sname 
FROM student s,score o 
WHERE s.sid=o.sid AND o.cid IN (SELECT cid FROM score WHERE sid=2) AND s.sid<>2 GROUP BY s.sid;

    8、统计列印各个科目的成绩，各个科目按分数段 课程ID,课程名称，【100-85】，【85-70】，【70-60】，【60-0】

SELECT course.cid '课程id',course.cname '课程名称',SUM(CASE WHEN score.score>=85 AND score.score<=100 THEN 1 ELSE 0 END) '100~85',
SUM(CASE WHEN score.score>=70 AND score.score<85 THEN 1 ELSE 0 END) '85~70',SUM(CASE WHEN score.score>=60 AND score.score<70 THEN 1 ELSE 0 END) '70~60',
SUM(CASE WHEN score.score>0 AND score.score<60 THEN 1 ELSE 0 END) '60~0'
FROM course,score WHERE course.cid=score.cid GROUP BY course.cid;

    9、查询每门课程被选修的学生数

SELECT course.cid,COUNT(score.cid)
FROM course,score
WHERE course.cid=score.cid GROUP BY course.cid;

    10、查询出只选修了一门课程的学生的学号、姓名

SELECT student.sid '学号',student.sname '姓名'
FROM student,score
WHERE student.sid=score.sid GROUP BY student.sid HAVING COUNT(cid)=1;

    11、查询男生、女生的总人数

(SELECT student.ssex '性别',COUNT(ssex)
FROM student WHERE ssex='男')
UNION
(SELECT student.ssex '性别',COUNT(ssex)
FROM student WHERE ssex='女');

    12、姓李的师生名单

(SELECT sname FROM student where sname LIKE "李_%")
UNION
(SELECT tname FROM teacher where tname LIKE "李_%")

    13、查询出同名同姓的学生名单，并统计人数

SELECT sname ,COUNT(sname)
FROM student
GROUP BY sname HAVING COUNT(*)>1;

    14、查询每门课程的平均成绩，结果按平均成绩升序排列，如果平均成绩相同，就按课程号降序排列。

SELECT cid,AVG( score)
FROM score
GROUP BY cid ORDER BY AVG(score) ASC,cid DESC;

    15、查询平均成绩大于85的所有的学生的学号、姓名、平均成绩

SELECT student.sid,student.sname,AVG( score)
FROM student,score
WHERE student.sid=score.sid GROUP BY student.sid HAVING AVG( score)>85;

    16、查询课程名称为“离散数学”，且分数低于60分的学生姓名和分数

SELECT sname,score 
FROM student,score,course
where student.sid=score.sid AND score.cid=course.cid AND cname="离散数学" HAVING score<60;

    17、查询所有学生的选课情况：包括学号、课程号、姓名、课程名。

SELECT student.sid '学号',course.cid '课程号',sname '姓名',cname '课程名'
FROM student,course,score

select * from teacher left JOIN course on teacher.tid=course.tid;

WHERE student.sid=score.sid AND score.cid=course.cid;

    18、查询有学生不及格的课程的课程号和课程名，并按照课程号从大到小排列

SELECT course.cid '课程号',cname
FROM score,course
where course.cid=score.cid AND score<60 GROUP BY course.cid ORDER BY score.cid desc;

    19、查询课程号为003且成绩在80分以上的学生的学号、姓名

SELECT student.sid '学号',sname
FROM student,score
WHERE student.sid=score.sid AND cid='003' AND score>80;

    20、查询各个老师相应的学生总人数

SELECT course.tid,count(DISTINCT(sid))
FROM score,course
WHERE course.cid=score.cid GROUP BY course.tid;

    21、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT a.sid,a.cid,a.score
FROM score a,score b
WHERE a.sid=b.sid AND a.score=b.score AND a.cid<>b.cid;

    22、查询选“001”课程的所有学生的平均分（去掉一个最高分，去掉一个最低分）

SELECT ((SUM(score)-MIN(score)-MAX(score))/(COUNT(score)-2)) '平均成绩'
FROM score
WHERE cid='001';

    23、左连接版本

select * from teacher left JOIN course on teacher.tid=course.tid;

    24、右连接版本

select *from course right JOIN teacher on teacher.tid=course.tid;