作业
# 创建班级表
create table class (
cls_id int auto_increment primary key,
cls_name varchar(10) not null default ''
);
insert into class (cls_name) values ('三年二班'), ('一年三班'), ('二年一班');
# 创建老师表
create table teacher (
t_id int auto_increment primary key,
t_name varchar(10) not null default ''
);
insert into teacher (t_name) values ('罗老师'), ('厨子'), ('乔帮主');
#创建学生表
create table student (
s_id int auto_increment primary key,
s_name varchar(10) not null default '',
gender enum('男', '女') not null default '男',
class_id int not null default 1,
constraint fk_std_cls foreign key (class_id) references class(cls_id)
);
insert into student (s_name, gender, class_id ) values ('雷军', '男', 1), ('余大嘴', '男', 2), ('黄章', '女', 3);
# 创建课程表
create table course (
c_id int auto_increment primary key,
c_name varchar(10) not null default '',
teacher_id int not null default 1,
constraint fk_course_teacher foreign key (teacher_id ) references teacher(t_id)
);
insert into course (c_name, teacher_id ) values ('Python', 3), ('C', 1), ('Go',2);
# 创建成绩表
create table score (
scr_id int auto_increment primary key,
student_id int not null default 1,
course_id int not null default 1,
scr_num int not null default 0,
constraint fk_scr_std foreign key (student_id ) references student(s_id),
constraint fk_scr_crs foreign key (course_id ) references course(c_id)
);
insert into score (student_id, course_id, scr_num) values (1, 3, 80), (2, 1, 70), (1, 2, 60);
# 班级
+--------+----------+
| cls_id | cls_name |
+--------+----------+
| 1 | 三年二班 |
| 2 | 一年三班 |
| 3 | 二年一班 |
+--------+----------+
# 老师
+------+--------+
| t_id | t_name |
+------+--------+
| 1 | 罗老师 |
| 2 | 厨子 |
| 3 | 乔帮主 |
+------+--------+
# 学生
+------+--------+--------+----------+
| s_id | s_name | gender | class_id |
+------+--------+--------+----------+
| 1 | 雷军 | 男 | 1 |
| 2 | 余大嘴 | 男 | 2 |
| 3 | 黄章 | 女 | 3 |
+------+--------+--------+----------+
# 课程
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
| 1 | Python | 3 |
| 2 | C | 1 |
| 3 | Go | 2 |
+------+--------+------------+
# 分数
+--------+------------+-----------+---------+
| scr_id | student_id | course_id | scr_num |
+--------+------------+-----------+---------+
| 1 | 1 | 3 | 80 |
| 2 | 2 | 1 | 70 |
| 3 | 1 | 2 | 60 |
+--------+------------+-----------+---------+
- 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
mysql> select distinct s_name, student_id from score left join student on student_id=student.s_id where scr_num > 60;
+--------+------------+
| s_name | student_id |
+--------+------------+
| 雷军 | 1 |
| 余大嘴 | 2 |
+--------+------------+
2 rows in set (0.00 sec)
mysql> select t_name, count(t_name) as crs_count from course left join teacher on teacher_id=teacher.t_id group by t_name;
+--------+-----------+
| t_name | crs_count |
+--------+-----------+
| 乔帮主 | 1 |
| 厨子 | 1 |
| 罗老师 | 1 |
+--------+-----------+
3 rows in set (0.00 sec)
mysql> select * from student left join class on class_id=class.cls_id;
+------+--------+--------+----------+--------+----------+
| s_id | s_name | gender | class_id | cls_id | cls_name |
+------+--------+--------+----------+--------+----------+
| 1 | 雷军 | 男 | 1 | 1 | 三年二班 |
| 2 | 余大嘴 | 男 | 2 | 2 | 一年三班 |
| 3 | 黄章 | 女 | 3 | 3 | 二年一班 |
+------+--------+--------+----------+--------+----------+
3 rows in set (0.00 sec)
mysql> select gender, count(gender) from student group by gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| 男 | 2 |
| 女 | 1 |
+--------+---------------+
2 rows in set (0.00 sec)
- 获取所有学习 "Python" 的学生的学号, 成绩和姓名
mysql> select c_name, s_id, s_name, scr_num from score left join student on student_id=student.s_id left join course on course_id=course.c_id where c_name='Python';
+--------+------+--------+---------+
| c_name | s_id | s_name | scr_num |
+--------+------+--------+---------+
| Python | 2 | 余大嘴 | 70 |
+--------+------+--------+---------+
1 row in set (0.00 sec)
mysql> select s_id, s_name, avg(scr_num) from score left join student on student_id=student.s_id group by s_name having avg(scr_num) > 60;
+------+--------+--------------+
| s_id | s_name | avg(scr_num) |
+------+--------+--------------+
| 2 | 余大嘴 | 70.0000 |
| 1 | 雷军 | 70.0000 |
+------+--------+--------------+
2 rows in set (0.00 sec)
mysql> select t_name, count(t_name) from teacher group by t_name having t_name like '罗%';
+--------+---------------+
| t_name | count(t_name) |
+--------+---------------+
| 罗老师 | 1 |
+--------+---------------+
mysql> select s_id, s_name, scr_num from score left join student on student_id=student.s_id where scr_num < 70;
+------+--------+---------+
| s_id | s_name | scr_num |
+------+--------+---------+
| 1 | 雷军 | 60 |
+------+--------+---------+
1 row in set (0.00 sec)
- 查询各科成绩最高和最低的分：以如下形式显示：课程名，最高分，最低分；
mysql> select c_name, max(scr_num), min(scr_num) from score left join course on course_id=course.c_id group by c_name;
+--------+--------------+--------------+
| c_name | max(scr_num) | min(scr_num) |
+--------+--------------+--------------+
| C | 60 | 60 |
| Go | 80 | 80 |
| Python | 70 | 70 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from student where s_name like '雷%';
+------+--------+--------+----------+
| s_id | s_name | gender | class_id |
+------+--------+--------+----------+
| 1 | 雷军 | 男 | 1 |
+------+--------+--------+----------+
1 row in set (0.00 sec)
- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> select c_name, avg(scr_num) from score left join course on course_id=course.c_id group by c_name order by avg(scr_num) asc, c_id desc;
+--------+--------------+
| c_name | avg(scr_num) |
+--------+--------------+
| C | 60.0000 |
| Python | 70.0000 |
| Go | 80.0000 |
+--------+--------------+
3 rows in set (0.00 sec)
- 查询课程编号为3且课程成绩在70分以上的学生的学号和姓名
mysql> select s_id, s_name from score left join student on student_id=student.s_id left join course on course_id=course.c_id where c_id=3 and scr_num > 70;
+------+--------+
| s_id | s_name |
+------+--------+
| 1 | 雷军 |
+------+--------+
1 row in set (0.00 sec)
- 查询“2”课程分数小于70，按分数降序排列的同学学号
mysql> select s_id from score left join student on student_id=student.s_id left join course on course_id=course.c_id where c_id=2 and scr_num < 70 order by scr_num desc;
+------+
| s_id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> delete from score where student_id=2 and course_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from score;
+--------+------------+-----------+---------+
| scr_id | student_id | course_id | scr_num |
+--------+------------+-----------+---------+
| 1 | 1 | 3 | 80 |
| 3 | 1 | 2 | 60 |
+--------+------------+-----------+---------+
2 rows in set (0.00 sec)