MySQL实践

①从学生表当中查询所有数据

SELECT * FROM student；

②从学生表当中查询学生的姓名、学号、年龄、性别

SELECT stu_name,stu_age,stu_code,stu_gender FROM student；

③从学生表中查询年龄大于18岁的学生

SELECT * FROM student；
SELECT * FROM student WHERE stu_age>18；

④从学生表中查询年龄大于18岁的女学生

SELECT * FROM student；
SELECT * FROM student WHERE stu_age>18 and stu_gender=女；

⑤从学生表中查询年龄大于18岁的女学生并且按照年龄降序，班级升序的条件排序

SELECT * FROM student；
SELECT * FROM student WHERE stu_age>18 and stu_gender=女；
ORDER BY stu_age DESC；
ORDER BY class_id (ASC)；

⑥从学生表中查询年龄大于18岁的女学生或者是年龄小于18的男学生；

SELECT * FROM student；
SELECT * FROM student WHERE stu_age>18,stu_gender=女 OR stu_age<18,stu_gender=男；

⑦从学生表中查询学生最大、最小的年龄和学生总数，学生平均年龄

SELECT MAX(age), MIN(age), COUNT(*), AVE(age) FROM student;

⑧从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄

SELECT class_id,MAX(age), MIN(age), COUNT(*), AVE(age) FROM student ;
GROUP BY class_id;

⑨从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄,筛选出班级学生总数大于5的数据结果

SELECT class_id,MAX(age), MIN(age), COUNT(*), AVE(age) FROM student ;
GROUP BY class_id;
HAVING TOTAL > 5;

⑩从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄,并且查询结果按照班级排序。

SELECT class_id,MAX(age), MIN(age), COUNT(*), AVE(age) FROM student ;
GROUP BY class_id;
ORDER BY class_id (ASC);

⑪从学生表中查询班级名称和每个班学生最大、最小的年龄和学生总数，学生平均年龄,并且查询结果按照班级排序。

SELECT class_name, MAX(age), MIN(age), COUNT(*), AVE(age) FROM student ;
INNER JOIN class ON class.class_id = student.class_id;
GROUP BY student.class_id ;
ORDER BY student.class_id ASC;

⑫罗列出每个班级的学生信息，按班级排序

SELECT student.class_id, class.class_name, student.code, student.name FROM student ;
INNER JOIN class ON class.class_id = student.class_id;
ORDER BY student.class_idASC;

⑬查询学生人数大于5个的班级信息，并且按照班级排序

SELECT class_id,class_name FROM class;
WHERE (SELECT COUNT(*) FROM student WHERE student.class_id = class.class_id)>5 ;
ORDER BY class_id ASC;