MySQL基础必备语句

关于学生，课程，成绩，教师表

• student(学号#,姓名,性别,年龄)
• course(课程号#，课程名，教师号#)
• score(学号#，课程号#，成绩)
• teacher(教师号#，教师名)

查询“001”课程比“002”课程成绩高的所有学生的学号

select a.stuNo from score a,score b
where a.cNo='c001' and b.cNo='c002' and a.stuNo=b.stuNo and a.score>b.score

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

select stuNo,avg(score)from score
group by stuNo
having avg(score)>60

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

select a.stuNo,a.stuName,count(cNo),sum(score) from student a,score b
where a.stuNo=b.stuNo
group by a.stuNo,a.stuName

查询姓“赵”的老师的个数

select count(tName),tName from teacher
where tName like '赵%'
group by tName

查询没学过“某某”老师课的同学的学号、姓名

select stuNo,stuName from student
where stuNo not in
(select a.stuNo from student a,score b where a.stuNo=b.stuNo and cNo in
(select d.cNo from teacher c,course d where c.tNo=d.tNo and c.tName='钱市保'))

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select a.stuNo,a.stuName from student a,score b,score c
where a.stuNo=b.stuNo and b.stuNo=c.stuNo and b.cNo='c001' and c.cNo='c002';

查询学过“某某”老师所教的所有课的同学的学号、姓名

select stuNo,stuName from student
where stuNo in (select stuNo from score a,course b,teacher c
where a.cNo=b.cNo and b.tNo=c.tNo and c.tName='钱市保'
group by stuNo
having count(a.cNo)>=(select count(cNo) from course d,teacher e
where d.tNo=e.tNo and e.tName='钱市保'));

老师所教课程为一门课：

select stuNo,stuName from student
where stuNo in (select a.stuNo from student a,score b where a.stuNo=b.stuNo and b.cNo in
(select cNo from teacher c,course d where c.tNo=d.tNo and c.tName='钱市保'));