施坤的博客
0%

mysql查询练习

发表于 更新于 分类于 阅读次数： Valine：

环境搭建

库表准备

  • 创建一个数据库
1
CREATE DATABASE Educational;
  • 创建学生表
1
2
3
4
5
6
7
8
9
10
use Educational 

CREATE TABLE student 
(
ID CHAR(9) PRIMARY KEY, # PRIMARY KEY表示id为主键
name VARCHAR(10),
gender CHAR(6), # 性别
birthdate date # 出生年月
)

  • 创建课程表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE course
(
# 课程号,主键
courseID CHAR(5) PRIMARY key,
# 课程名
coursename VARCHAR(36),
# 课程简介
Syllabus text,
# 课时
hours INT,
# 学分
credit TINYINT,
# 开课学期
semester text
);
  • 创建一个成绩表，将学生表和课程表进行关联
1
2
3
4
5
6
7
8
9
10
CREATE TABLE Score 
(
# 学号,关联学生表中id,作为外键
ID CHAR(9) REFERENCES student(ID),
# 课程号.关联课程表中id,作为外键
courseID CHAR(5) REFERENCES course(CourseID),
# 成绩
Results DECIMAL(4,1) CHECK(results BETWEEN 0 AND 100)

)
  • 插入数据,学生表
1
2
3
4
5
6
7
INSERT INTO student VALUES('202201001','汤姆猫','男','2000-01-01');
INSERT INTO student VALUES('202201002','风火轮','男','1998-11-03');
INSERT INTO student VALUES('202201003','闪光弹','女','2000-08-21');
INSERT INTO student VALUES('202201004','降世拳','女','2010-04-11');
INSERT INTO student VALUES('202201005','流星腿','女','2010-09-23');
INSERT INTO student VALUES('202201006','闪闪光','女','2010-02-02');
INSERT INTO student VALUES('202201007','汤汤水','女','1999-03-29');

image-20230612112157798

  • 插入数据，课程表
1
2
3
4
5
6
INSERT INTO course VALUES('10001','英语', '英语简介介绍',28,20,'2023-07-01');
INSERT INTO course VALUES('10002','数学', '英语简介介绍',32,17,'2023-07-01');
INSERT INTO course VALUES('10003','语文', '语文简介介绍',29,25,'2023-07-01');
INSERT INTO course VALUES('10004','物理', '物理简介介绍',21,20,'2023-08-05')
INSERT INTO course VALUES('10005','媒体', '摄像媒体简介介绍',13,12,'2023-08-01');
INSERT INTO course VALUES('10006','java', 'java简介介绍',17,5,'2023-08-11')
  • 插入数据，成绩表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
INSERT INTO Score VALUES('202201001','10001',89);
INSERT INTO Score VALUES('202201002','10001',76);
INSERT INTO Score VALUES('202201003','10001',99);
INSERT INTO Score VALUES('202201004','10001',88);
INSERT INTO Score VALUES('202201005','10001',59);
INSERT INTO Score VALUES('202201006','10001',69);

INSERT INTO Score VALUES('202201001','10002',80);
INSERT INTO Score VALUES('202201002','10002',77);
INSERT INTO Score VALUES('202201003','10002',92);
INSERT INTO Score VALUES('202201004','10002',83);
INSERT INTO Score VALUES('202201005','10002',56);
INSERT INTO Score VALUES('202201006','10002',100);

INSERT INTO Score VALUES('202201001','10003',81);
INSERT INTO Score VALUES('202201002','10003',72);
INSERT INTO Score VALUES('202201003','10003',63);
INSERT INTO Score VALUES('202201004','10003',81);
INSERT INTO Score VALUES('202201005','10003',55);
INSERT INTO Score VALUES('202201006','10003',93);

INSERT INTO Score VALUES('202201001','10004',82);
INSERT INTO Score VALUES('202201002','10004',71);
INSERT INTO Score VALUES('202201003','10004',96);
INSERT INTO Score VALUES('202201004','10004',75);
INSERT INTO Score VALUES('202201005','10004',58);
INSERT INTO Score VALUES('202201006','10004',89);

INSERT INTO Score VALUES('202201001','10005',91);
INSERT INTO Score VALUES('202201002','10005',86);
INSERT INTO Score VALUES('202201003','10005',97);
INSERT INTO Score VALUES('202201004','10005',38);
INSERT INTO Score VALUES('202201005','10005',61);
INSERT INTO Score VALUES('202201006','10005',18);

查询

  • 基础语法
1
2
3
4
5
6
7
select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

  • 关于group by 分组的理解

    • 合并数据，比如张三有两条数据分别为100,20，合并后就成了120
    • 在使用聚合函数，如sum,avg等需要分组，比如张三有对应两条数据为10和20，李四对应两条数据为10和10，使用avg 求平均值时

    不分组：

    1
    2
    3
    4
    5
    6
    select name,avg(num),count(1) from table 
    # 若不分组就是整张表的平均值，即：（10+20+10+10）/4=12.5
    #  count(1)，则是统计表中数据行，上述计算出为4
    # 而这里的name则是读取表中一条数据，张三

    张三  12.5  4

    分组：

    1
    2
    3
    4
    5
    select name,avg(num),count(1) from table group name
    # 首先对张三、李四进行分组，各自的数据为10+20=30、10+10=20
    # 然后对分组后的数据进行平均计算，30/2=15、10/2=10
    张三 15 2
    李四 10 2

实践

  • 不及格的学生姓名，性别，科目，分数
1
SELECT student.name, student.gender,course.coursename, score.Results FROM score, student,course WHERE score.ID=student.ID AND score.courseID=course.courseID and Results <=60

image-20230614154352090

  • 查询所有学生共参加了几门考试的信息，并且分数大于80，一定要分组,不分组就是查询的共有多少人参加了考试
1
SELECT student.ID,student.name,COUNT(1) FROM student,score WHERE student.ID = score.ID AND score.Results>80 GROUP BY student.ID

image-20230614154417658

  • 计算当前学生所有成绩的总分，使用的左连接
1
SELECT student.name,sum(score.Results),COUNT(1) FROM score LEFT JOIN student on score.ID=student.ID GROUP BY student.ID

image-20230614154453362

  • 计算当前学生所有成绩的总分大于300的信息，分组后，使用having sum
1
2
SELECT student.name,sum(score.Results),COUNT(1) FROM score LEFT JOIN student on score.ID=student.ID GROUP BY student.ID HAVING SUM(score.Results)>90

image-20230614154545101

  • 查询当前分数最高的学生名字、科目。注意这里and results= 使用了子查询
1
SELECT student.name,course.coursename,results from course,score, student WHERE course.courseID=score.courseID AND student.ID=score.ID and results=(SELECT MAX(score.Results) FROM score)

image-20230614160341440

  • 查询各个科目的平均成绩
1
select course.coursename,avg(score.Results),COUNT(1) FROM course  JOIN score ON course.courseID = score.courseID GROUP BY course.courseID

image-20230614160434587

  • 查询某个学生共参加了几门考试
1
2
SELECT student.name,count(score.Results) FROM student,score WHERE student.name="汤姆猫" AND student.ID=score.ID

  • 查看学生成绩总分为前三的数据。先对学生进行分组，然后用sum计算所有分数，然后取前3条数据
1
SELECT stu.name, sum(sc.Results) FROM student stu ,score sc WHERE sc.ID=stu.ID GROUP BY stu.ID ORDER BY sum(sc.Results) DESC LIMIT 3

image-20230614160553477

  • 查询各科成绩前三的学生信息，这个自己写不出来，用chatgpt写的
1
2
3
4
5
6
7
8
9
10
11
SELECT s.name AS student_name, c.coursename AS course_name, sc.Results AS course_score
FROM score sc
JOIN student s ON sc.ID = s.ID -- 连接score表和student表，获取学生姓名
JOIN course c ON sc.courseID = c.courseID -- 连接score表和course表，获取课程名称
WHERE (
  SELECT COUNT(DISTINCT Results) -- 查询比当前成绩高的不同分数的数量
  FROM score sc2 
  WHERE sc2.courseID = sc.courseID AND sc2.Results > sc.Results 
) < 3 -- 只保留比当前成绩高的不同分数小于3个的记录，即前三名
ORDER BY c.coursename, sc.Results DESC; -- 按照课程名称和成绩降序排序

image-20230614173746372

  • 查询所有课程最高分数的学生信息，用查询各科成绩前三的语句，把<3给为<1即可