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 studentADD FOREIGN KEY(classid)REFERENCES class(id);ALTER TABLE gradeADD 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;