大数据学习第九天（mysql查询已选/没选的课程）

1、创建表和添加数据

create database day1 charset utf8;
use day1;
create table student
(
    id   int unsigned primary key auto_increment not null,
    name varchar(20)                             not null comment '学生姓名'
);
insert into student
values (0, '张三'),
       (0, '李四'),
       (0, '王五'),
       (0, '赵六'),
       (0, '田七');
create table course
(
    id   int unsigned primary key auto_increment not null,
    name varchar(20)                             not null comment '课程名称'
);
insert into course
values (0, '语文'),
       (0, '数学'),
       (0, '英语'),
       (0, '物理'),
       (0, '化学'),
       (0, '历史'),
       (0, '地理');
create table stu_cour
(
    stu_id  int comment '学生id',
    cour_id int comment '课程id',
    primary key (stu_id, cour_id)
);

insert into stu_cour
values (1, 1),
       (1, 2),
       (1, 3),
       (1, 4),
       (2, 1),
       (2, 2),
       (2, 3),
       (2, 5),
       (3, 1),
       (3, 2),
       (3, 3),
       (3, 7),
       (4, 1),
       (4, 2),
       (4, 3),
       (4, 4),
       (5, 1),
       (5, 2),
       (5, 3),
       (5, 5);

2、表的样子

course表

stu_cour表

student表

3、题目

1）查询已经被选过的课程有哪些

select distinct a.name
from course a
         left join stu_cour b on a.id = b.cour_id
where b.cour_id in (select id from course);

2）查询还没有被选过的课程有哪些

select name
from course
where course.name not in (select distinct a.name
                          from course a
                                   left join stu_cour b on a.id = b.cour_id
                          where b.cour_id in (select id from course));
select * from course;

这两个查询折磨了半个小时，必须把这俩晒一晒