当前位置:Gxlcms > 数据库问题 > MySQL 查询练习

MySQL 查询练习

时间:2021-07-01 10:21:17 帮助过:29人阅读

查询“95031”班的学生人数。
SELECT COUNT(CLASS) as 9501班 FROM student WHERE class="95033";
查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM score WHERE CNO="3-105";
SELECT avg(degree) 平均分  from score GROUP BY cno HAVING cno = ‘3-105‘;
查询成绩表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT CNO,avg(degree) from score  GROUP BY CNO HAVING  COUNT(*)>5 and CNO like "3%";
查询最低分大于70,最高分小于90的学生编号 列
SELECT SNO  from score  GROUP BY SNO HAVING  min(DEGREE)>70 and MAX(DEGREE)<90;
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT  stu.* from  student stu LEFT JOIN score sc ON sc.SNO=stu.SNO and cno="3-105" and sc.DEGREE>
(SELECT degree FROM score WHERE CNO="3-105" AND SNO=109 )

查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT score.* from score INNER JOIN
(SELECT sno ,MAX(degree) as maxdegree from score GROUP BY sno HAVING COUNT(*) >1) temp
on score.sno = temp.sno and score.degree < temp.maxdegree;

查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列
SELECT sno,sname,sbirthday FROM student WHERE YEAR(SBIRTHDAY)=
(SELECT YEAR(SBIRTHDAY) from student WHERE SNO=107);

查询“张旭“教师任课的学生成绩
SELECT * from score WHERE cno in (SELECT cno from course WHERE tno = (SELECT tno from teacher WHERE tname =‘张旭‘));
查询出“计算机系“教师所教课程的成绩表。
SELECT * from score WHERE cno in (SELECT CNO from course WHERE TNO in (SELECT tno from teacher WHERE DEPART = ‘计算机系‘));
查询“计算机系”与“电子工程系“不同职称的教师的名字和职称。
SELECT tname ,prof from teacher WHERE DEPART = ‘计算机系‘ and  prof not in (SELECT prof from teacher WHERE DEPART = ‘电子工程系‘)
UNION
SELECT tname ,prof from teacher WHERE DEPART = ‘电子工程系‘ and  prof not in (SELECT prof from teacher WHERE DEPART = ‘计算机系‘);
查询所有教师和同学的名字、性别和生日.
SELECT sname as name , ssex as sex , sbirthday as birthday from student
UNION ALL #万一老师和学生姓名 性别 出生日相同呢 所以加了ALL
SELECT tname , tsex  , tbirthday as birthday from teacher;

 



MySQL 查询练习

标签:null   asc   having   charset   var   color   rem   structure   记录   

人气教程排行