MySQL常用语句整理

表：

 查询语句

-- 查询姓“猴”的学生名单
SELECT * from student WHERE 姓名 like "猴%";
-- 查询姓“孟”老师的个数
SELECT COUNT(教师姓名) FROM teacher WHERE 教师姓名 LIKE "孟%";
-- 查询课程编号为“0002”的总成绩
SELECT SUM(成绩) from score WHERE 课程号=00002;
-- 查询选了课程的学生人数
SELECT COUNT(DISTINCT 学号) as 学生人数 from score;
-- 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
SELECT 课程号, min(成绩) as 最低分, max(成绩) as 最高分 from score GROUP BY 课程号;
-- 查询每门课程被选修的学生数
SELECT 课程号, count(学号) as 学生数 from score  GROUP BY  课程号;
-- 查询男生、女生人数
SELECT 性别, count(*) from student GROUP BY 性别;
-- 查询平均成绩大于60分学生的学号和平均成绩
SELECT 学号, avg(成绩) as 平均成绩 from score GROUP BY 学号 HAVING AVG(成绩) > 60;
-- 查询至少选修两门课程的学生学号
SELECT 学号, count(课程号) as 选修科目数 from score GROUP BY 学号 HAVING COUNT(课程号)>=2;
-- 查询同名同姓学生名单并统计同名人数
SELECT 姓名, count(*) as 同名人数 from student GROUP BY 姓名 HAVING count(*)>=2;
-- 查询不及格的课程并按课程号从大到小排列
SELECT 课程号,成绩 from score WHERE 成绩 < 60 ORDER BY 课程号 DESC;
-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT 课程号, avg(成绩) as 平均成绩 from score GROUP BY 课程号 HAVING AVG(成绩)  ORDER BY 平均成绩 asc, 课程号 desc;
-- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT 学号,成绩 from score WHERE 课程号=00004 and 成绩 < 60 ORDER BY 成绩 desc;
-- 统计每门课程的学生选修人数(超过2人的课程才统计)，输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT 课程号,count(学号) as 选修人数 from score GROUP BY 课程号 having COUNT(学号) > 2 ORDER BY count(学号) desc, 课程号 asc;
-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT 学号, AVG(成绩) as 平均成绩 from score WHERE 成绩 < 60 GROUP BY 学号 having COUNT(课程号) > 2 ;
-- 查询学生的总成绩并进行排名
SELECT 学号, SUM(成绩) as 总成绩 from score GROUP BY 学号 ORDER BY 总成绩 asc;
-- 查询平均成绩大于60分的学生的学号和平均成绩
SELECT 学号, AVG(成绩) as 平均成绩 from score  GROUP BY 学号 having AVG(成绩)>60;
-- 查询所有课程成绩小于60分学生的学号、姓名
SELECT 学号,姓名 FROM student WHERE 学号 in (SELECT 学号 from score WHERE 成绩 < 60);
-- 查询学全没有选所有课的学生的学号、姓名
SELECT 学号,姓名 from student WHERE 学号 in (SELECT 学号 from score GROUP BY 学号 having COUNT(课程号) < (SELECT COUNT(课程号) from course));
-- 查询出只选修了两门课程的全部学生的学号和姓名
SELECT 学号,姓名 from student WHERE 学号 in (SELECT 学号 from score GROUP BY 学号 having COUNT(课程号) = 2);
-- 查找1990年出生的学生名单
SELECT 姓名,出生日期 from student where YEAR(出生日期) = '1990';
-- 查询各学生的年龄
SELECT 学号, TIMESTAMPDIFF(MONTH,出生日期,NOW())/12 as 年龄 FROM student;
-- 找出本月过生日的学生
SELECT 学号 FROM student where MONTH(出生日期) = MONTH(CURRENT_DATE);
-- 查询所有学生的学号、姓名、选课数、总成绩
SELECT a.学号, a.姓名, COUNT(b.课程号) as 选课数, SUM(b.成绩) as 总成绩 from student as a LEFT JOIN score as b on a.学号=b.学号 GROUP BY a.学号; 
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT a.学号, a.姓名, AVG(b.成绩) as 平均成绩 from student as a LEFT JOIN score as b on a.学号=b.学号 GROUP BY a.`学号` having 平均成绩>85;
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT a.学号, a.姓名, c.课程号, c.课程名称 from student as a INNER JOIN score as b on a.`学号` = b.学号 INNER JOIN course as c on b.`课程号`=c.`课程号`;
-- 查询出每门课程的及格人数和不及格人数
SELECT 课程号, SUM(case WHEN 成绩<60 then 1 else 0 end) as 不及格人数, SUM(case when 成绩>=60 THEN 1 else 0 end) as 及格人数 from score GROUP BY 课程号;
-- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
SELECT a.课程号,b.课程名称,SUM(case WHEN a.成绩 BETWEEN 85 and 100 then 1 else 0 end) as '[100-85]', SUM(case when a.成绩 BETWEEN 70 AND 85 then 1 else 0 END) as '[85-70]',
SUM(case WHEN a.成绩 BETWEEN 60 AND 70 then 1 ELSE 0 end) as '[70-60]', SUM(case when a.成绩 < 60 then 1 else 0 end) as '[<60]' from score as a RIGHT JOIN course as b on 
a.`课程号`=b.`课程号` GROUP BY a.`课程号`, b.`课程名称`;
-- 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|
SELECT a.学号, a.姓名, b.课程号, b.成绩 FROM student as a LEFT JOIN score as b on a.学号=b.`学号` WHERE b.`课程号`=0003 and b.`成绩`>80;
-- 检索"0001"课程分数小于60，按分数降序排列的学生信息
SELECT a.*, b.成绩 FROM student as a INNER JOIN score as b on a.学号=b.学号 WHERE b.`课程号` =00001 AND b.`成绩`<60 ORDER BY b.`成绩` DESC;
-- 查询不同老师所教不同课程平均分从高到低显示
SELECT a.教师号,a.教师姓名, AVG(c.成绩) as 平均分 from teacher as a INNER JOIN course as b on a.教师号=b.教师号 INNER JOIN score as c on b.课程号=c.课程号 GROUP BY a.教师号 ORDER BY AVG(c.`成绩`) DESC;
-- 查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.姓名, b.成绩 from student as a INNER JOIN score as b on a.学号=b.学号 inner join course as c on b.课程号=c.课程号 where b.成绩<60 and c.课程名称='数学';
-- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT a.姓名, b.课程名称, c.成绩 from student as a inner join score as c on a.学号=c.学号 inner join course as b on b.课程号=c.课程号  WHERE c.`成绩` > 70;
-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.学号, b.姓名, AVG(a.成绩) as 平均成绩 from score as a inner join student as b on a.学号=b.学号 WHERE a.`成绩` < 60 GROUP BY a.学号 having COUNT(a.`学号`) > 2;