masql多表查询（答案更新）

masql多表查询

表之间的关系

代码实现

– 创建表

 CREATE TABLE cource(
 coid INT PRIMARY KEY,
 coname VARCHAR(13)
 );

– 添加数据

 INSERT INTO cource VALUES(1,"JAVA");
 INSERT INTO cource VALUES(2,"PHP");
 INSERT INTO cource VALUES(3,"C++");

– 创建表

 CREATE TABLE classes (
 clid INT PRIMARY KEY,
 clname VARCHAR(3),
 clnum INT
 );

–添加数据

 INSERT INTO classes VALUES(1,'01班',20);
 INSERT INTO classes VALUES(2,'02班',30);
 INSERT INTO classes VALUES(3,'03班',32);
 INSERT INTO classes VALUES(4,'04班',41);

–创建表

 CREATE TABLE student (
 sid INT PRIMARY KEY,
 sname VARCHAR(4),
 ssex VARCHAR(1),
 birthday VARCHAR(10),
 clid INT,
  FOREIGN KEY (clid) REFERENCES classes(clid)
 );

–添加数据

INSERT INTO student VALUES(1,"张三",'男','1990-09-01',1);
 INSERT INTO student VALUES(2,"李四",'女','1991-02-13',1);
 INSERT INTO student VALUES(3,"王五",'男','1990-03-12',1);
 INSERT INTO student VALUES(4,"赵六",'男','1992-02-12',2);
 INSERT INTO student VALUES(5,"田七",'男','1994-05-21',2);
 INSERT INTO student VALUES(6,"张武",'女','1990-06-17',2);
 INSERT INTO student VALUES(7,"张老七",'女','1990-04-12',3);
 INSERT INTO student VALUES(8,"王老师",'女','1990-07-16',3);
 INSERT INTO student VALUES(9,"李六",'男','1990-09-12',NULL);

–创建表

 CREATE TABLE student_cource(
 scid INT PRIMARY KEY AUTO_INCREMENT,
 sid INT,
 FOREIGN KEY (sid) REFERENCES student(sid),
 coid INT ,
 FOREIGN KEY (coid) REFERENCES cource(coid),
 score INT
 );

–添加数据

INSERT INTO student_cource VALUES(1,1,1,85);
 INSERT INTO student_cource VALUES(2,1,3,72);
 INSERT INTO student_cource VALUES(NULL,2,2,82);
 INSERT INTO student_cource VALUES(NULL,2,3,65);
 INSERT INTO student_cource VALUES(NULL,3,1,71);
 INSERT INTO student_cource VALUES(NULL,3,2,75);
 INSERT INTO student_cource VALUES(NULL,3,3,68);
 INSERT INTO student_cource VALUES(NULL,4,1,72);
 INSERT INTO student_cource VALUES(NULL,4,2,64);
 INSERT INTO student_cource VALUES(NULL,5,5,91);
 INSERT INTO student_cource VALUES(NULL,5,3,82);
 INSERT INTO student_cource VALUES(NULL,6,1,74);
 INSERT INTO student_cource VALUES(NULL,6,2,48);
 INSERT INTO student_cource VALUES(NULL,7,2,73);
 INSERT INTO student_cource VALUES(NULL,7,3,82);
 INSERT INTO student_cource VALUES(NULL,8,1,65);
 INSERT INTO student_cource VALUES(NULL,8,2,80);
 INSERT INTO student_cource VALUES(NULL,9,1,81);
 INSERT INTO student_cource VALUES(NULL,9,2,91);
 INSERT INTO student_cource VALUES(NULL,9,3,78);

相关操作

a. 查询班级名称，和班级总人数
b. 查询学生的姓名和学生所选的总课程平均成绩
c. 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名
d. 查询平均成绩大于80分的学生的总数
e. 查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩

答案

– a. 查询班级名称，和班级总人数

 /*select t.`clname`,count(s.`sid`)
 from classes t,student s
 where s.`clid`=t.`clid`
 group by t.clid;*/-- 错误实例 原因此方法为内连接只能显示交集部分，此处应该使用左外连接或右外连接。

SELECT t.`clname` 班级名称,COUNT(s.`sid`) 班级人数
FROM classes t LEFT JOIN student s
ON s.`clid`=t.`clid`
GROUP BY t.`clid`;

– b. 查询学生的姓名和学生所选的总课程平均成绩

 SELECT a.`sname`,AVG(b.`score`) 平均分
 FROM student a RIGHT JOIN student_cource b
 ON a.`sid`=b.`sid`
 GROUP BY b.`sid`;

– c. 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名

 SELECT a.sname 姓名,COUNT(b.sid) 选课数
 FROM student a , student_cource b
 WHERE a.`sid`=b.`sid` 
 GROUP BY b.sid HAVING COUNT(b.sid)>2;

– d. 查询平均成绩大于80分的学生的总数

SELECT COUNT(t.人数) 平均成绩大于80分的人数
 FROM (SELECT a.`sname` 人数
 FROM student a RIGHT JOIN student_cource b
 ON a.`sid`=b.`sid`
 GROUP BY b.`sid` HAVING AVG(b.`score`)>80
 ) t;