1、创建好数据库
create database text
use text
--学生表 (students)
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
major VARCHAR(50)
);
--课程表 (courses)
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50),
teacher_name VARCHAR(50)
);
--选课表 (enrollments)
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
grade DECIMAL(4, 1),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
--插入学生数据
INSERT INTO students (student_id, name, age, major) VALUES
(1, '张三', 20, '计算机科学'),
(2, '李四', 21, '数学'),
(3, '王五', 19, '物理');
--插入课程数据
INSERT INTO courses (course_id, course_name, teacher_name) VALUES
(1, '数据库原理', '赵老师'),
(2, '计算机网络', '钱老师'),
(3, '高等数学', '孙老师');
--插入选课数据
INSERT INTO enrollments (enrollment_id, student_id, course_id, grade) VALUES
(1, 1, 1, 85.5),
(2, 1, 2, 90.0),
(3, 2, 1, 78.0),
(4, 2, 3, 92.0),
(5, 3, 2, 88.5);
所有表查询结果如图：
题目1：查询选修了“数据库原理”课程的学生姓名。
select name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on e.course_id=c.course_id and course_name='数据库原理'
结果：
题目2：查询张三的所有课程成绩。
select grade
from students s
inner join enrollments e
on s.student_id=e.student_id and name='张三'
结果：
题目3：查询每位学生的姓名、年龄和平均成绩。
select name,age,AVG(grade)平均成绩
from students s
inner join enrollments e
on s.student_id=e.student_id
group by name,age
结果：
题目4：查询年龄大于20岁的学生所选修的课程名称。
select course_name
from courses c
inner join enrollments e
on c.course_id=e.course_id
inner join students s
on s.student_id=e.student_id and age>20
结果：
题目5：查询“赵老师”所教课程的名称以及选修这些课程的学生姓名。
select course_name,name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on c.course_id=e.course_id and teacher_name='赵老师'
结果：
题目6：查询选修了至少两门课的学生姓名
select name
from students s
inner join enrollments e
on s.student_id=e.student_id
group by e.student_id,name
having COUNT(e.student_id)>=2
结果：
题目7：查询成绩高于90分的学生姓名和课程名称。
select name,course_name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on e.course_id=c.course_id and grade>90
结果：
题目8：查询每位老师所教课程的平均成绩
select AVG(grade) 平均成绩,teacher_name
from enrollments e
inner join courses c
on e.course_id=c.course_id
group by c.teacher_name
结果：
题目9：查询成绩在80分以上且选课数量不少于2门的学生姓名和平均成绩
select name,AVG(grade) 平均成绩
from students s
inner join enrollments e
on s.student_id=e.student_id and grade>=80
group by e.student_id,name
having COUNT(e.student_id)>=2
结果：
题目10：查询每个专业选修了最多课程的学生姓名。
select name,major
from students s
inner join enrollments e
on s.student_id=e.student_id
group by major,name
having COUNT(e.student_id)
结果：
