[大数据]mysql练习2（对学生、专业课程的相关查询）

1.根据专业把年纪最大年龄的学员信息查询出来

SELECT * FROM student GROUP BY stu_major ORDER BY stu_age DESC LIMIT 3;

2.把每个学院的人数统计出来

SELECT stu_college,COUNT(*) FROM student GROUP BY stu_college;

3.查询女性，课程为计科的学生id及姓名

SELECT stu_id,stu_name FROM student WHERE stu_major = '计科' AND stu_sex = '女';

4.查询信工学院有哪些专业

SELECT stu_major FROM student WHERE stu_college = '信工学院';

5.按照id将信工学院学生降序排序

SELECT *FROM student WHERE stu_college = '信工学院' ORDER BY stu_id DESC;

6.计算计科男学生的平均年龄（四舍五入）

SELECT ROUND(AVG(stu_age)) FROM student WHERE stu_major = '计科' AND stu_sex = '男';

7.查询信工学院男学生数量

SELECT COUNT(*)FROM student WHERE stu_college = '信工学院' AND stu_sex = '男';

8.查询计科学生年龄最大的三位学生信息

SELECT * FROM student WHERE stu_major = '计科' ORDER BY stu_age DESC LIMIT 3;

9.查询id倒数第二位是2的的学生信息

SELECT * FROM student WHERE stu_id LIKE '%2_';

10.求学分总和

SELECT SUM(cs_credit) FROM course;

11.筛选出course表中的选修课并按id编号升序排列

SELECT cs_id,cs_name FROM course ORDER BY cs_id ASC;

12.把信工学院按年龄（向上取整）升序排序

SELECT *FROM student WHERE stu_college = '信工学院' ORDER BY CEIL(stu_age) ASC;

13.查询年龄大于20或者姓张的学生信息

SELECT *FROM student WHERE stu_age >20 OR stu_name LIKE '张%';

14.各学院的平均年龄

SELECT stu_college,AVG(stu_age)FROM student GROUP BY stu_college

15.查出姓名为两个字的学生

SELECT *FROM student WHERE LENGTH(stu_name)=6;

16.显示男生多少人女生多少人

SELECT COUNT(*),stu_sex FROM student GROUP BY stu_sex;

17.各个专业按年龄降序显示学生信息

SELECT *FROM student ORDER BY stu_major,stu_age DESC;

18.根据选修类型来降序排序每门学科

SELECT cs_name,cs_type FROM course ORDER BY cs_type DESC;

19.显示出所有带名字带张字的学生并按年龄降序排列

SELECT *FROM student WHERE stu_name LIKE '%张%' ORDER BY stu_age DESC;

20…查出有哪些课程名称并以课程学分降序排列

SELECT cs_name,cs_credit FROM course ORDER BY cs_credit DESC;