mysql查询学生平均成绩及其名次_MySQL（学生表、教师表、课程表、成绩表）多表查询...

1、表架构

student(sid,sname,sage,ssex) 学生表

course(cid,cname,tid) 课程表

sC(sid,cid,score) 成绩表

teacher(tid,tname) 教师表

2、建表sql语句

1 CREATE TABLEstudent2 (3 sid INT PRIMARY KEY NOT NULL,4 sname VARCHAR(30),5 sage INT,6 ssex VARCHAR(8)7 )8

9 CREATE TABLEcourse10 (11 cid INT PRIMARY KEY NOT NULL,12 cname VARCHAR(30),13 tid INT

14 )15

16 CREATE TABLEsc17 (18 sid INT NOT NULL,19 cid INT NOT NULL,20 score INT

21 )22

23 CREATE TABLEteacher24 (25 tid INT PRIMARY KEY NOT NULL,26 tname VARCHAR(30)27 )

3、问题：

(1)查询“30001”课程的所有学生的学号与分数；

SELECT sid,score FROM sc WHERE cid="30001"

(2)查询“001”课程比“002”课程成绩高的所有学生的学号与分数；

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="30001") a,

(SELECT sid,score FROM sc WHERE cid="30002") bWHERE a.score>b.score AND a.sid=b.sid

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

SELECT sid,AVG(score)FROMscGROUP BY sid HAVING AVG(score)>60

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

SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数目", SUM(sc.score) AS"总分数"FROMstudent s, sc scWHERE s.sid=sc.sidGROUP BY s.sid

(5)查询姓“李