马哥博客作业第十三周

1、如何将 hellodb_innodb.sql导入到数据库中
mysql -uroot -pcentos < /data/mysql/hellodb_innodb.sql

2、在学生表中，查询年龄大于25岁，且为男性的同学的名字和年龄
SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M';

3、在学生表中，以ClassID为分组依据，查询显示每组的平均年龄
SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID;

4、显示第2题中平均年龄大于30的分组及平均年龄
SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(Age) > 30;

5、显示以L开头的名字的同学的信息
SELECT * FROM students WHERE Name LIKE 'L%';

6、显示老师ID非空的同学的相关信息
SELECT * FROM students WHERE TeacherID IS NOT NULL;

7、students表中，查询以年龄排序后的数据，并且显示年龄最大的前10位同学的信息
SELECT * FROM students ORDER BY Age DESC LIMIT 10;

8、students表中，查询年龄大于等于20岁，小于等于25岁的同学的信息
SELECT * FROM students WHERE Age >= 20 AND Age <= 25;
SELECT * FROM students WHERE Age BETWEEN 20 AND 25;

9、以ClassID分组，显示每班的同学的人数
SELECT ClassID,COUNT(*) FROM students GROUP BY ClassID;

10、以ClassID分组，显示其平均年龄大于25的班级
SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(Age) > 25;

11、以Gender分组，显示其年龄之和
SELECT Gender,SUM(Age) FROM students GROUP BY Gender;

12、以Gender分组，显示各组中年龄大于25的学员的年龄之和
SELECT Gender,ClassID,SUM(Age) FROM students WHERE Age > 25 GROUP BY Gender,ClassID;

多表查询：

将students、scores、Courses三张表的相关字段通过inner join创建视图
CREATE VIEW v_st_sc_co AS (SELECT st.Name,co.Course,sc.Score FROM students st inner join scores sc on st.StuID=sc.StuID inner join courses co on sc.CourseID=co.CourseID);

1. 显示前5位同学的姓名、课程及成绩
SELECT * FROM v_st_sc_co ORDER BY Score DESC LIMIT 5;

2. 显示其成绩高于80的同学的名称及课程
SELECT Name,Course FROM v_st_sc_co WHERE Score > 80;

3. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
SELECT Name,avg(Score) FROM v_st_sc_co GROUP BY Name ORDER BY avg(Score) DESC LIMIT 3;

4. 显示每门课程课程名称及学习了这门课的同学的个数
SELECT Course,count(*) FROM v_st_sc_co GROUP BY Course;

5. 显示其年龄大于平均年龄的同学的名字
SELECT Name FROM students WHERE Age > (SELECT avg(Age) FROM students);

6. 显示其学习的课程为第1、2，4或第7门课的同学的名字
SELECT st.Name,sc.CourseID FROM students st inner join Scores sc on st.StuID=sc.StuID WHERE sc.CourseID in (1,2,4,7) ORDER BY CourseID;

7. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
SELECT st.Name,st.ClassID,st.Age,b.aAge FROM students st inner join (SELECT ClassID,avg(Age) aAge FROM students GROUP BY ClassID HAVING count(*) >= 3) b on st.ClassID=b.ClassID WHERE st.Age > b.aAge ORDER BY ClassID;

8. 统计各班级中年龄大于全校同学平均年龄的同学
SELECT * FROM students WHERE Age > (SELECT avg(Age) FROM students) and ClassID IS NOT NULL ORDER BY ClassID;