mysql80怎么新建查询_Mysql基本的一些查询操作

/*查询选修课程‘3-105’且成绩在60到80之间的所有记录。*/

SELECT * FROM result WHERE CNO='3-105' AND GRADE > 60 AND GRADE<80;

/*查询成绩为85、86或88的记录。*/

SELECT * FROM result WHERE GRADE>=85 AND GRADE<=88;

/* 查询95031班的学生人数*/

SELECT count(*) AS '人数95031班级' FROM student WHERE CLASS='95031';

/* 查询至少有5名学生选修并以3开头的课程的平均成绩*/

SELECT avg(GRADE) from result where CNO like '3%' AND CNO HAVING count(*)>=5;

/* 查询最低分大于70，最高分小于90的NO列*/

SELECT NO FROM result WHERE GRADE

SELECT max(GRADE) FROM result WHERE GRADE<90

) AND GRADE>(

SELECT min(GRADE) FROM result WHERE GRADE>70

);

/* 查询95033班学生所选课程的平均分*/

SELECT avg(GRADE) AS '95033班级平均分' FROM result WHERE NO IN (

SELECT NO FROM student WHERE CLASS='95033'

);

/* 查询选修‘3-105’课程的成绩高于‘109’号同学成绩的所有同学的记录*/

SELECT * FROM result WHERE CNO='3-105' AND GRADE >(

SELECT GRADE FROM result WHERE CNO='3-105' AND NO=109

);

/* 查询与学号为108的同学同岁的所有学生的NO、NAME和AGE*/

SELECT NO,NAME,AGE FROM student WHERE AGE=(

SELECT AGE FROM student WHERE NO=108

) AND NO <> 108;

/* 查询“张旭”教师任课的课程号，选修其课程学生的学号和成绩*/

SELECT NO,GRADE FROM result WHERE CNO = (

SELECT CNO FROM subject WHERE TNO = (

SELECT TNO FROM teacher WHERE NAME='张旭'

)

);

/* 查询选修其课程的学生人数多于5人的教师姓名*/

SELECT NAME FROM teacher WHERE TNO=(

SELECT TNO FROM subject WHERE CNO=(

SELECT CNO FROM result GROUP BY CNO HAVING count(*)>5

)

);

/* 查询“计算机系”与“电子工程系”不同职称的教师的姓名和职称*/

SELECT NAME,PROF FROM teacher WHERE DEPART='计算机系' AND PROF NOT IN (

SELECT PROF FROM teacher WHERE DEPART='电子工程'

);

SELECT NAME,PROF FROM teacher WHERE DEPART='电子工程' AND PROF NOT IN (

SELECT PROF FROM teacher WHERE DEPART='计算机系'

);

/* 查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”课程的同学的CNO、NO 、GRADE并按GRADE从高到低次序排列*/

SELECT NO,CNO,GRADE FROM result WHERE CNO='3-105' AND GRADE>ANY(

SELECT GRADE FROM result WHERE CNO='3-245'

) ORDER BY GRADE DESC ;

/* 列出所有教师和同学的NAME 、SEX 、AGE*/

SELECT NAME,SEX,AGE FROM student UNION SELECT NAME,SEX,AGE FROM teacher;

/* 查询成绩比该课程平均成绩低的学生的成绩表*/

SELECT * FROM result a WHERE GRADE

SELECT avg(GRADE) FROM result b WHERE a.CNO=b.CNO

);

/* 列出所有任课教师的NAME和DEPART*/

SELECT NAME,DEPART FROM teacher WHERE TNO IN (

SELECT TNO FROM subject

);

/* 列出所有未讲课教师的NAME和DEPART*/

SELECT NAME,DEPART FROM teacher WHERE TNO NOT IN (

SELECT TNO FROM subject WHERE CNO IN (

SELECT CNO FROM result

)

);

/* 列出至少有2名男生的班号*/

SELECT CLASS FROM student WHERE SEX='男' GROUP BY CLASS HAVING count(*)>1;

/* 查询不姓“王”的学生记录*/

SELECT * FROM student WHERE NAME NOT LIKE '王%';

/* 查询每门课最高分的学生的NO、CNO、CRADE*/

SELECT NO,CNO,GRADE FROM result WHERE GRADE GROUP BY CNO HAVING max(GRADE);

/* 查询与“李军”同性别并同班的同学NAME*/

SELECT NAME FROM student WHERE SEX=(

SELECT SEX FROM student WHERE NAME='李军'

) AND CLASS=(

SELECT CLASS FROM student WHERE NAME='李军'

) AND NAME<>'李军';

/* 查询“男”教师及其所上的课程*/

SELECT teacher.NAME,subject.CNAME FROM teacher,subject WHERE teacher.TNO=subject.TNO AND teacher.SEX='男';

/* 查询选修“计算机导论”课程的“男”同学的成绩表*/

SELECT * FROM result WHERE CNO=(

SELECT CNO FROM subject WHERE CNAME='计算机导论'

) AND NO IN (

SELECT NO FROM student WHERE SEX='男'

);

/* 把选修高等数学课不及格的成绩全改为60*/

UPDATE result SET GRADE=60 WHERE GRADE<60 AND CNO=(

SELECT CNO FROM subject WHERE CNAME='高等数学'

);

/* 把低于总平均成绩的女同学成绩提高5%*/

SELECT @avg:=(SELECT avg(GRADE) FROM result);

UPDATE result SET GRADE=GRADE*(1+0.05) WHERE GRADE

SELECT NO FROM student WHERE SEX='女'

);

/* 在基本表SC中修改计算机导论的成绩，若成绩小于等于75分时提高5%,若成绩大于75时提高4%*/

UPDATE result SET GRADE=GRADE*(1+0.05) WHERE GRADE<=75 AND CNO=(

SELECT CNO FROM subject WHERE CNAME='计算机导论'

);

UPDATE result SET GRADE=GRADE*(1+0.04) WHERE GRADE>75 AND CNO=(

SELECT CNO FROM subject WHERE CNAME='计算机导论'

);

/* 查询“张旭”教师任课的课程号，选修其课程学生的学号和成绩*/

SELECT r.NO,r.GRADE,t.TNO FROM result AS r,subject AS s,teacher AS t WHERE t.NAME='张旭' AND r.CNO=s.CNO AND s.TNO=t.TNO;

/* 检索学号比李同学大，而年龄比他小的学生姓名*/

SELECT NAME FROM student WHERE NO>(

SELECT NO FROM student WHERE NAME='李军'

) AND AGE<=(

SELECT AGE FROM student WHERE NAME='李军'

);