图片.png
图片.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个的班级信息，并且按照班级排序
