【mysql】mysql练习

mysql练习

1、准备

构建数据表
mysql> select * from Student;
+------------+-------------+---------------------+---------------+
| StudentNum | StudentName | StudentAge          | StudentGender |
+------------+-------------+---------------------+---------------+
| 01         | zhaolei     | 1990-01-01 00:00:00 | M             |
| 02         | qiandian    | 1990-12-21 00:00:00 | M             |
| 03         | sunfeng     | 1990-05-20 00:00:00 | M             |
| 04         | liyun       | 1990-08-06 00:00:00 | M             |
| 05         | zhoumei     | 1991-12-01 00:00:00 | F             |
| 06         | wulan       | 1992-03-01 00:00:00 | F             |
| 07         | zhengzhu    | 1989-07-01 00:00:00 | F             |
| 08         | wangju      | 1990-01-20 00:00:00 | F             |
+------------+-------------+---------------------+---------------+

mysql> select * from Course;
+-----------+------------+------------+
| CourseNum | CourseName | TeacherNum |
+-----------+------------+------------+
| 01        | Chinese    | 02         |
| 02        | Match      | 01         |
| 03        | English    | 03         |
+-----------+------------+------------+

mysql> select * from Teacher;
+------------+-------------+
| TeacherNum | TeacherName |
+------------+-------------+
| 01         | zhangsan    |
| 02         | lisi        |
| 03         | wangwu      |
+------------+-------------+

mysql> select * from StudentScores;
+------------+-----------+--------+
| StudentNum | CourseNum | Scores |
+------------+-----------+--------+
| 01         | 02        |   90.0 |
| 01         | 03        |   99.0 |
| 02         | 01        |   70.0 |
| 02         | 02        |   60.0 |
| 02         | 03        |   80.0 |
| 01         | 01        |   80.0 |
| 03         | 01        |   80.0 |
| 03         | 02        |   80.0 |
| 03         | 03        |   80.0 |
| 04         | 01        |   50.0 |
| 04         | 02        |   30.0 |
| 04         | 03        |   20.0 |
| 05         | 01        |   76.0 |
| 05         | 02        |   87.0 |
| 06         | 01        |   31.0 |
| 06         | 03        |   34.0 |
| 07         | 02        |   89.0 |
| 07         | 03        |   98.0 |
+------------+-----------+--------+

二、练习

1、查找左右的课程1（Chinese）比课程2（math）分数高的学生信息和分数

select course1.StudentNum, course1.Scores as score1, course2.Scores as score2
from 
(select * from StudentScores where CourseNum = "01") course1
left join
(select * from StudentScores where CourseNum = "02") course2
on course1.StudentNum = course2.StudentNum
where course1.Scores > course2.Scores

select Student.*, scoreinfo.* from
Student 
right join 
(select course1.StudentNum as studentNum, course1.Scores as score1, course2.Scores as score2
from 
(select * from StudentScores where CourseNum = "01") course1
left join
(select * from StudentScores where CourseNum = "02") course2
on course1.StudentNum = course2.StudentNum
where course1.Scores > course2.Scores) scoreinfo 
on Student.StudentNum = scoreinfo.StudentNum

2、查询同时存在课程1和课程2的学生情况
select * from 
(select * from StudentScores where CourseNum = "01") course1
inner join
(select * from StudentScores where CourseNum = "02") course2
on course1.StudentNum = course2.StudentNum

3、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from 
(select * from StudentScores where CourseNum = "01") course1
left join
(select * from StudentScores where CourseNum = "02") course2
on course1.StudentNum = course2.StudentNum

4、查询不存在" 01 "课程但存在" 02 "课程的情况
select * from StudentScores where CourseNum = "02" and StudentNum not in 
(select StudentNum from StudentScores where CourseNum = "01")

5、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select avgInfo.StudentNum, Student.StudentName, avgInfo.avgScore
from 
(select StudentNum, AVG(Scores) as avgScore
from StudentScores
group by StudentNum
having AVG(Scores) > 60) avgInfo
left join 
Student
on
avgInfo.StudentNum = Student.StudentNum

6、查询在StudentScores表存在成绩的学生信息
select * from Student
where StudentNum in
(select distinct StudentNum from StudentScores)

7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select Student.StudentNum, StudentName, courseNum, courseTotal
from
Student
left join
(select StudentNum, count(Scores) as courseNum, SUM(Scores) as courseTotal
from StudentScores group by StudentNum) courseInfo
on 
Student.StudentNum = courseInfo.StudentNum

8、查询姓“li"的老师的数量
select count(*)
from Teacher 
where Teacher.TeacherName like "li%"

9、查询学过“zhangsan“老师的课的同学信息
select * from
(select 
StudentNum 
from StudentScores
where CourseNum in 
(select CourseNum from Course where TeacherNum in 
(select
TeacherNum 
from Teacher 
where TeacherName = "zhangsan") 
)) courseInfo
left join 
Student 
on Student.StudentNum = courseInfo.StudentNum
group by Student.StudentNum

10、查询没有学全部课程（chinese/match/english）的学生信息
select * from Student
where StudentNum in
(select StudentNum
from 
StudentScores
group by StudentNum
having count(Scores) < 3)

11、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from Student 
where StudentNum in
(select StudentNum from StudentScores
where CourseNum in
(select CourseNum
from
StudentScores 
where StudentNum = "01")
group by StudentNum)

12、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

13、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from Student
where StudentNum not in
(select distinct StudentNum from
StudentScores 
where CourseNum in
(select CourseNum from Course where TeacherNum in 
(select
TeacherNum 
from Teacher 
where TeacherName = "zhangsan")))

14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select Student.StudentName, scoresInfo.StudentNum, scoresInfo.avgScore
from
(select StudentNum, AVG(Scores) as avgScore
from 
StudentScores
where Scores < 60
group by StudentNum
having COUNT(Scores >=2)) scoresInfo
left join 
Student
on
Student.StudentNum = scoresInfo.StudentNum

15、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select Student.*, Scores
from 
Student 
inner join
(select StudentNum, Scores
from 
StudentScores
where CourseNum  = "01" and Scores < 60) scoresInfo
on Student.StudentNum = scoresInfo.StudentNum
order by Scores desc

16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select StudentScores.*, avg
from 
StudentScores
left join
(
select StudentNum, AVG(Scores) as avg
from StudentScores
group by StudentNum
)avgInfo
on StudentScores.StudentNum = avgInfo.StudentNum
order by avg desc

17、查询各科成绩最高分、最低分和平均分
select CourseNum, MAX(Scores), MIN(Scores), AVG(Scores)
from 
StudentScores
group by CourseNum

18、以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Course.CourseNum, Course.CourseName, students_num_per_course, max, min, avg, jige_lv, zhongdeng_lv, youliang_lv, youxiu_lv
from
Course
left join
(select CourseNum, MAX(Scores) as max, MIN(Scores) as min, AVG(Scores) as avg, count(*) as students_num_per_course
from StudentScores group by CourseNum) StaticInfo
on Course.CourseNum = StaticInfo.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=60 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as jige_lv 
from StudentScores group by CourseNum) jigelv_table
on Course.CourseNum = jigelv_table.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=70 and Scores < 80 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as zhongdeng_lv 
from StudentScores group by CourseNum) zhongdenglv_talbe
on Course.CourseNum = zhongdenglv_talbe.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=80 and Scores < 90 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as youliang_lv 
from StudentScores group by CourseNum) youlianglv_talbe
on Course.CourseNum = youlianglv_talbe.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=90 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as youxiu_lv 
from StudentScores group by CourseNum) youxiulv_talbe
on Course.CourseNum = youxiulv_talbe.CourseNum
order by students_num_per_course desc, Course.CourseNum asc

19、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

20、按各科成绩进行排序，并显示排名， Score 重复时合并名次

21、查询学生的总成绩，并进行排名，总分重复时保留名次空缺

22、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

23、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select Course.CourseNum, Course.CourseName, bujige, jige, lianghao, youxiu
from
Course
left join
(select CourseNum,(convert((sum(case when Scores<60 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as bujige
from StudentScores group by CourseNum) jigelv_table
on Course.CourseNum = jigelv_table.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=60 and Scores < 70 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as jige
from StudentScores group by CourseNum) zhongdenglv_talbe
on Course.CourseNum = zhongdenglv_talbe.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=70 and Scores < 85 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as lianghao 
from StudentScores group by CourseNum) youlianglv_talbe
on Course.CourseNum = youlianglv_talbe.CourseNum
left join
(select CourseNum,(convert((sum(case when Scores>=85 then 1 else 0 end)*1.00)/COUNT(*)*100, DECIMAL)) as youxiu 
from StudentScores group by CourseNum) youxiulv_talbe
on Course.CourseNum = youxiulv_talbe.CourseNum

24、查询各科成绩前三名的记录

25、查询每门课程被选修的学生数
select CourseNum, count(*)
from StudentScores
group by CourseNum;

26、查询出只选修两门课程的学生学号和姓名
select StudentNum, StudentName 
from Student
where StudentNum in
(select StudentNum
from StudentScores
group by StudentNum
having (count(distinct(CourseNum)) = 2))

27、查询男生、女生人数
select count(*)
from Student
group by StudentGender

28、查询名字中含有"feng"字的学生信息
select * 
from Student 
where StudentName like "%feng%"

29、查询同名同性学生名单，并统计同名人数
select StudentName, count(StudentNum) as num 
from
Student 
group by StudentName
having count(StudentNum) > 1

30、查询 1990 年出生的学生名单
select * from 
Student 
where YEAR(StudentAge) = "1990"

31、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select CourseNum, AVG(Scores) as avg
from StudentScores
group by CourseNum
order by avg desc, CourseNum asc

32、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select Student.StudentNum, Student.StudentName, avg
from 
Student
inner join
(select StudentNum, AVG(Scores) as avg
from StudentScores
group by StudentNum
having avg >= 85) avgInfo
on Student.StudentNum = avgInfo.StudentNum

33、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select StudentName, Scores
from Student
right join
(select StudentNum, Scores
from StudentScores
where CourseNum = "02" and Scores < 60) mathInfo
on Student.StudentNum = mathInfo.StudentNum

34、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select Student.*, CourseName, Scores
from Student
left join
(select StudentNum, CourseName, Scores
from 
Course
right join 
StudentScores
on Course.CourseNum = StudentScores.CourseNum) course_scores_info
on Student.StudentNum = course_scores_info.StudentNum

35、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Student.StudentNum, Student.StudentName, info.CourseName, info.Scores
from 
Student 
inner join
(select StudentNum, Scores, Course.CourseNum, CourseName
from 
Course
inner join
(select StudentNum, Scores, CourseNum
from StudentScores
where StudentNum not in
(select distinct StudentNum
from StudentScores
where Scores < 70)) studentInfo
on Course.CourseNum = studentInfo.CourseNum) info
on Student.StudentNum = info.StudentNum

36、查询不及格的课程
select Course.CourseNum, Course.CourseName
from 
Course
inner join
(select distinct CourseNum
from StudentScores
where Scores < 60) fail_info
on Course.CourseNum = fail_info.CourseNum

37、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select Student.StudentName, Student.StudentNum
from Student
right join
(select StudentNum
from StudentScores
where Scores >= 80 and CourseNum = "01") info
on Student.StudentNum = info.StudentNum

38、求每门课程的学生人数
select CourseNum, count(*) as num_per_course
from
StudentScores
group by CourseNum

39、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select Student.*, Scores
from
Student
right join
(select StudentNum, Scores
from 
StudentScores
where CourseNum in
(select CourseNum 
from Course
where TeacherNum in
(select TeacherNum 
from Teacher
where TeacherName = "zhangsan"))
order by Scores desc limit 1) info
on Student.StudentNum = info.StudentNum

40、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

41、查询每门功成绩最好的前两名

42、统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select count(*) as num_of_student
from 
StudentScores
group by CourseNum
having count(*) > 5

43、检索至少选修两门课程的学生学号
select StudentNum
from 
StudentScores
group by StudentNum
having count(Scores) >= 2

44、查询选修了全部课程的学生信息
select StudentNum
from 
StudentScores
group by StudentNum
having count(Scores) >= 3

45、查询各学生的年龄，只按年份来算
select StudentName, YEAR(StudentAge)
from 
Student