MySQL花式查询

创建一个学生表：stdents(id 姓名，性别，班级id)
创建一个班级表：class(id 班级名)
创建一个成绩表grade(id 学生id 科目 成绩)
数据根据下面的问题，自行插入；
问题（写出sql语句）：

1. 查找出每个班级的总人数（班级名，班级总人数）
2. 查找出某一个学生的所有课程名称和对应的课程成绩
3. 查找出每一个科目的成绩最高的学生名和对应的成绩
4. 查找出每一个科目的成绩最低的学生名和对应的成绩
5. 查找出每一个科目的平均分
6. 查找出每一个科目的总分
7. 查找出某一个班级的某一个课程的所有学生的姓名和成绩，并且按成绩从高到低排序
8. 查找出某一个班级的班级名和对应的学生名字
9. 查找出当某个课程成绩大于90分的所有的学生的姓名，性别，班级名称

准备

//建库
CREATE DATABASE schools;

USE schools;
//创建学生表
CREATE TABLE student(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(40) NOT NULL,
sex varchar(20) NOT NULL,
classid int NOT NULL
);

//创建班级表
CREATE TABLE class(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(30) NOT NULL
);

//创建成绩表
CREATE TABLE grade(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
stuid int NOT NULL,
subject varchar(40) NOT NULL,
num int NOT NULL
);

//添加外键
ALTER TABLE student
ADD FOREIGN KEY(classid)
REFERENCES class(id);

ALTER TABLE grade
ADD FOREIGN KEY(stuid)
REFERENCES student(id);

//插入数据
INSERT INTO class (name)
VALUES('16060206'),('16060207'),('16060208'),('16060209');

INSERT INTO student(name,sex,classid)
VALUES('xiaoming','man','1'),('xiaohong','woman','1'),('xiaoli','woman','2'),('xiaoqiang','man','2'),('xiaoliang','woman','3'),('xiaoyao','woman','3'),('xiaofeng','man','4'),('xiaoyu','man','4');

INSERT INTO grade(stuid,subject,num)
VALUES('1','chinese','91'),('1','math','94'),('2','chinese','84'),('2','math','94'),('3','chinese','57'),('3','math','86'),('4','chinese','97'),('4','math','83'),('5','chinese','91'),('5','math','78'),('6','chinese','91'),('6','math','78'),('7','chinese','91'),('7','math','98'),('8','chinese','91'),('8','math','78');

建好的表

问题

//查找出每个班级的总人数（班级名，班级总人数）
SELECT class.name,COUNT(student.classid) FROM class,student WHERE class.id = student.classid GROUP BY class.id;

//查找出某一个学生的所有课程名称和对应的课程成绩
SELECT subject ,num FROM grade WHERE stuid = 1;	//id查找
SELECT grade.subject,grade.num FROM grade,student WHERE student.name='xiaoming' and grade.stuid=student.id;	//姓名查找

//.查找出每一个科目的成绩最高的学生名和对应的成绩
/*有一个子语句 查出各科的最高成绩 然后在grade表里查出考出最高成绩的学生id 在student表里查出学生姓名*/
SELECT student.name,g.num,g.sub FROM grade,student,(SELECT max(num) AS num,subject AS sub FROM grade GROUP BY subject) AS g WHERE grade.num = g.num AND student.id = grade.stuid;

//查找出每一个科目的成绩最低的学生名和对应的成绩
//原理同上
SELECT student.name,g.num,g.sub FROM grade,student,(SELECT min(num) AS num,subject AS sub FROM grade GROUP BY subject) AS g WHERE grade.num = g.num AND student.id = grade.stuid;

//查找出每一个科目的平均分
SELECT avg(num),subject FROM grade GROUP BY subject;

//查找出每一个科目的总分
SELECT sum(num),subject FROM grade GROUP BY subject;

//查找出某一个班级的某一个课程的所有学生的姓名和成绩，并且按成绩从高到低排序
/*有一个子语句 查找出对应课程的所有成绩和学生id 然后在student表和class表里查到指定班级id的学生id ORDER BY clo ASC 升序排列*/
SELECT class.name,g.subject,student.name,g.num FROM student,class,(select num,stuid,subject FROM grade WHERE subject='math')AS g WHERE student.id = g.stuid AND class.id = student.classid AND student.classid =1 ORDER BY g.num ASC;

//查找出某一个班级的班级名和对应的学生名字
SELECT class.name,student.name FROM class,student WHERE class.id=1 AND class.id=student.classid;

//查找出当某个课程成绩大于90分的所有的学生的姓名，性别，班级名称
SELECT class.name,student.name,student.sex,g.sub,g.num FROM student,class,(SELECT subject as sub,num,stuid FROM grade WHERE num>90 AND subject='math')AS g WHERE student.id=g.stuid AND class.id=student.classid;