MySQL之DQL查询数据练习

图片.png

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

SELECT * FROM student;

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

SELECT `stu_name` AS 姓名，`stu_code` AS 学号, `stu_age` AS 年龄 ,`stu_gender` AS性别 FROM student AS 学生表；

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

SELECT stu_name FROM student WHERE stu_age > 18;

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

SELECT stu_name FROM student WHERE stu_age > 18 AND stu_gender = ‘女’;

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

SELECT stu_name FROM student 
WHERE stu_age > 18 AND stu_gender = ‘女’
ORDER BY stu_age DESC,class_id ASC;

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

SELECT stu_name FROM student
WHERE (stu_age > 18 AND stu_gender = ‘女’) OR (stu_age < 18 AND stu_gender = ‘男’);

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

SELECT max(stu_age), min(stu_age), count(*), avg(stu_age) FROM student;

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

SELECT class_id,max(stu_age), min(stu_age), count(*), avg(stu_age) FROM student 
GROUP BY class_id;

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

SELECT class_id,max(stu_age), min(stu_age), count(*) as total, avg(stu_age) FROM student 
GROUP BY class_id 
HAVING total > 5;

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

SELECT class_id,max(stu_age), min(stu_age), count(*), avg(stu_age) FROM student 
GROUP BY class_id 
ORDER BY class_id ASC;

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

SELECT class_name, max(stu_age), min(stu_age), count(*), avg(stu_age) FROM student 
ORDER BY student.class_id ASC;

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

SELECT student.class_id, classes.class_name, student.code, student.name 
FROM student 
ORDER BY student.class_id ASC;

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