mysql练习

一、    操作表

1.     查询“生物”课程比“物理”课程成绩高的所有学生的学号

select A.student_id from

   

                 (select student_id,number asbiology from score left join course on score.course_id = course.cid wherecourse.cname = 'biological') as A

   

                 left join

   

                 (select student_id,number  as ph from score left join course onscore.course_id = course.cid where course.cname = 'physics') as B

   

                 on A.student_id = B.student_idwhere ph >biology;

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

select student_id,avg(number) from score group bystudent_id ;

 

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

selectscore.student_id,sum(score.number),count(score.student_id),student.sname

    from

        score left join student onscore.student_id = student.sid 

    group by score.student_id

3.     查询姓“李”的老师的个数；

selectcount(tid) from teacher where tname like '李%';

4.     查询没学过“叶平”老师课的同学的学号、姓名；

SELECT sid fromstudent WHERE sid not IN(

 

SELECT DISTINCTB.student_id from(

SELECTstudent_id,tname from

(selectsname,student_id,course_id,teach_id,number from student LEFT JOIN score onstudent.sid=score.student_id)as A

LEFT JOIN

teacher ONA.teach_id=teacher.tid

)as B WHEREtname='叶平'

 

);

 

 

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

 SELECT *from

(

 SELECT sidfrom

(SELECT student_id as student_idA from score WHEREcourse_id=1)as A

 LEFT JOIN

student on student.sid=A.student_idA) as C

inner join

(

SELECT sid from

(SELECT student_id as student_idB from score WHEREcourse_id=2)as B

 LEFT JOIN

student on student.sid=B.student_idB)AS D onC.sid=D.sid;

1.     查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT sid from student WHERE sid not IN(

 

SELECT DISTINCT B.student_id from(

SELECT student_id,tname from

(select sname,student_id,course_id,teach_id,number from student LEFTJOIN score on student.sid=score.student_id)as A

LEFT JOIN

teacher ON A.teach_id=teacher.tid

)as B WHERE tname='叶平'

 

);

2.     查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT sname,student_id FROM

(select A.student_id from

                (select student_id,number as biology from score left join course onscore.course_id = course.cid where course.cid=1) as A

                left join

                (select student_id,number  as phfrom score left join course on score.course_id = course.cid where course.cid=2)as B

                on A.student_id = B.student_id where ph<biology

                )as C

 

LEFT JOIN

student on C.student_id=student.sid;

3.     查询有课程成绩小于60分的同学的学号、姓名；

 

SELECTstudent_id,sname from

(SELECTstudent_id,sname,number from score LEFT JOIN student on score.student_id=student.sidWHERE number<60 GROUP BY sname)as A;

    )

4.     查询没有学全所有课的同学的学号、姓名；

SELECT student_id,sname from student LEFT JOINscore on student.sid=score.student_id GROUP BY student_id

HAVING COUNT(course_id)<(SELECT COUNT(cid)fromcourse);