MySQL语句练习题（持续更新~）

表名和字段

–1.学生表

????????Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别

–2.课程表

????????Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号

–3.教师表

????????Teacher(t_id,t_name) --教师编号,教师姓名

–4.成绩表

????????Score(s_id,c_id,s_score) --学生编号,课程编号,分数

?建表并插入数据

##  建表
-- 学生表
CREATE TABLE `Student`
(
    `s_id`    VARCHAR(20),
    `s_name`  VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex`   VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (`s_id`)
);

-- 课程表
CREATE TABLE `Course`
(
    `c_id`   VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id`   VARCHAR(20) NOT NULL,
    PRIMARY KEY (`c_id`)
);

-- 教师表
CREATE TABLE `Teacher`
(
    `t_id`   VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`t_id`)
);

-- 成绩表
CREATE TABLE `Score`
(
    `s_id`    VARCHAR(20),
    `c_id`    VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY (`s_id`, `c_id`)
);

-- 插入学生表测试数据
insert into Student
values ('01', '赵雷', '1990-01-01', '男');
insert into Student
values ('02', '钱电', '1990-12-21', '男');
insert into Student
values ('03', '孙风', '1990-05-20', '男');
insert into Student
values ('04', '李云', '1990-08-06', '男');
insert into Student
values ('05', '周梅', '1991-12-01', '女');
insert into Student
values ('06', '吴兰', '1992-03-01', '女');
insert into Student
values ('07', '郑竹', '1989-07-01', '女');
insert into Student
values ('08', '王菊', '1990-01-20', '女');

-- 插入课程表测试数据
insert into Course
values ('01', '语文', '02');
insert into Course
values ('02', '数学', '01');
insert into Course
values ('03', '英语', '03');

-- 插入教师表测试数据
insert into Teacher
values ('01', '张三');
insert into Teacher
values ('02', '李四');
insert into Teacher
values ('03', '王五');

-- 插入成绩表测试数据
insert into Score
values ('01', '01', 80);
insert into Score
values ('01', '02', 90);
insert into Score
values ('01', '03', 99);
insert into Score
values ('02', '01', 70);
insert into Score
values ('02', '02', 60);
insert into Score
values ('02', '03', 80);
insert into Score
values ('03', '01', 80);
insert into Score
values ('03', '02', 80);
insert into Score
values ('03', '03', 80);
insert into Score
values ('04', '01', 50);
insert into Score
values ('04', '02', 30);
insert into Score
values ('04', '03', 20);
insert into Score
values ('05', '01', 76);
insert into Score
values ('05', '02', 87);
insert into Score
values ('06', '01', 31);
insert into Score
values ('06', '03', 34);
insert into Score
values ('07', '02', 89);
insert into Score
values ('07', '03', 98);

练习题和SQL

##练习题和sql语句
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select stu.*, sc1.s_score 01_score, sc2.s_score 02_score
from student stu,
     score sc1,
     score sc2
where stu.s_id = sc1.s_id
  and stu.s_id = sc2.s_id
  and sc1.c_id = '01'
  and sc2.c_id = '02'
  and sc1.s_score > sc2.s_score;

# 也可以用这种
select a.*, b.s_score as 01_score, c.s_score as 02_score
from student a
         join score b on a.s_id = b.s_id and b.c_id = '01'
         left join score c on a.s_id = c.s_id and c.c_id = '02'
where b.s_score > c.s_score;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*, b.s_score 01_score, c.s_score 02_score
from student a
         join score b on a.s_id = b.s_id and b.c_id = '01'
         left join score c on c.s_id = a.s_id and c.c_id = '02'
where b.s_score < c.s_score;

# 也可以这种
select a.*, b.s_score 01_score, c.s_score c_score
from student a,
     score b,
     score c
where a.s_id = b.s_id
  and a.s_id = c.s_id
  and b.c_id = '01'
  and c.c_id = '02'
  and b.s_score < c.s_score;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id, a.s_name, avg(b.s_score)
from student a
         left join score b on a.s_id = b.s_id
group by a.s_id, a.s_name
having avg(b.s_score) > 60;

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
# 这一句只能查出成绩表中有成绩的学生的数据
# select a.s_id, a.s_name, avg(b.s_score)
# from student a
#          left join score b on a.s_id = b.s_id
# group by a.s_id
# having avg(b.s_score) < 60;

# 用 union
# UNION运算符用于组合两个或更多SELECT语句的结果集。
# UNION中的每个SELECT语句必须具有相同的列数
#
select a.s_id, a.s_name, avg(b.s_score)
from student a
         left join score b on a.s_id = b.s_id
group by a.s_id
having avg(b.s_score) < 60
union
select a.s_id, a.s_name, 0
from student a
where a.s_id not in (select distinct s_id from score);

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id, a.s_name, count(b.s_id) sum_course, sum(b.s_score)
from student a
         left join score b on a.s_id = b.s_id
group by a.s_id;

-- 6、查询"李"姓老师的数量
select count(*)
from teacher t
where t.t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息
-- 先查出姓名为“张三”的老师的t_id  01
-- 再查出t_id为01的课程c_id
-- 再判断score表 c_id
select a.*
from student a
         left join questions.score b on a.s_id = b.s_id
where b.c_id in (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));
-- 用这种好像也可以查出来
select a.*
from student a
         left join questions.score b on a.s_id = b.s_id
where b.c_id = (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));
-- 用这种好像也可以查出来
select a.*
from student a
         left join questions.score b on a.s_id = b.s_id
         left join questions.course c on b.c_id = c.c_id
         left join questions.teacher t on c.t_id = t.t_id
where t_name = '张三';

-- 8、查询没学过"张三"老师授课的同学的信息
-- 先查出姓名为“张三”的老师的t_id  01
-- 再查出t_id为01的课程c_id
-- 再判断score表 c_id
select *
from student c
where c.s_id not in (select a.s_id
                     from student a
                              join score b on a.s_id = b.s_id
                     where b.c_id in (select a.c_id
                                      from course a
                                               join teacher b on a.t_id = b.t_id
                                      where t_name = '张三'));
-- 可以这么写
select c.*
from student c
where c.s_id not in (select a.s_id
                     from student a
                              left join questions.score b on a.s_id = b.s_id
                     where b.c_id =
                           (select c_id from course where t_id = (select t_id from teacher where t_name = '张三')));

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.*
from student a,
     score b,
     score c
where a.s_id = b.s_id
  and a.s_id = c.s_id
  and b.c_id = '01'
  and c.c_id = '02';
-- 也可以这么写
select a.*
from student a
         left join score b on a.s_id = b.s_id
         left join score s on a.s_id = s.s_id
where b.c_id = '01' && s.c_id = '02';

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 自己想的逻辑，多此一举了，条件里不需要连接查询，
-- select * from student a, score b where a.s_id = b.s_id and b.c_id = '01' ;
-- select * from student a, score b where a.s_id = b.s_id and b.c_id = '02';
-- select c.* from student c where c.s_id not in (select a.s_id from student a, score b where a.s_id = b.s_id and b.c_id = '02') and c.s_id in (select a.s_id from student a, score b where a.s_id = b.s_id and b.c_id = '01') ;
-- 直接查score表查出满足条件的s_id即可
select a.*
from student a
where a.s_id in (select s_id from score where c_id = '01')
  and a.s_id not in (select s_id from score where c_id = '02');

-- 11、查询没有学全所有课程的同学的信息
-- 查出所有课程数：select count(*) from course;
-- 根据s_id分组查出课程数量小于全部课程数量的学生
select a.*
from student a
         left join score b on a.s_id = b.s_id
group by a.s_id
having count(b.c_id) < (select count(*) from course);
-- 也可以这么写，查出学所有课程学生的s_id，判断不在里面的
select *
from student
where s_id not in (select s_id from score t1 group by s_id having count(*) = (select count(distinct c_id) from course));

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- 查询学号为01的同学信息：select * from student a left join score b on a.s_id = b.s_id where a.s_id = '01';
select distinct c.*
from student c
         left join score d on c.s_id = d.s_id
where d.c_id in (select b.c_id
                 from student a
                          left join score b on a.s_id = b.s_id
                 where a.s_id = '01')
  and d.s_id != '01';
-- 也可以这么写
select *
from student
where s_id in (select distinct a.s_id from score a where a.c_id in (select a.c_id from score a where a.s_id = '01'))
  and s_id != '01';

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT *
FROM Student
WHERE s_id IN (SELECT s_id
               FROM Score
               GROUP BY s_id
               HAVING COUNT(s_id) = (
                   #下面的语句是找到'01'同学学习的课程数
                   SELECT COUNT(c_id)
                   FROM Score
                   WHERE s_id = '01'))
  AND s_id NOT IN (
    #下面的语句是找到学过‘01’同学没学过的课程，有哪些同学。并排除他们
    SELECT s_id
    FROM Score
    WHERE c_id IN (
        #下面的语句是找到‘01’同学没学过的课程
        SELECT DISTINCT c_id
        FROM Score
        WHERE c_id NOT IN (
            #下面的语句是找出‘01’同学学习的课程
            SELECT c_id
            FROM Score
            WHERE s_id = '01'))
    GROUP BY s_id) #下面的条件是排除01同学
  AND s_id NOT IN ('01');
-- 也可以这样
SELECT t3.*
FROM (SELECT s_id,
             group_concat(c_id ORDER BY c_id) group1
      FROM score
      WHERE s_id > '01'
      GROUP BY s_id) t1
         INNER JOIN (SELECT group_concat(c_id ORDER BY c_id) group2
                     FROM score
                     WHERE s_id = '01'
                     GROUP BY s_id) t2 ON t1.group1 = t2.group2
         INNER JOIN student t3 ON t1.s_id = t3.s_id;


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 先查张三老师的t_id,在course表的c_id
-- select t_id from teacher where t_name = '张三';
-- select c_id from course where t_id = '01';
-- select s_id from score where c_id = '02';
select s_name
from student
where s_id not in (select s_id
                   from score
                   where c_id =
                         (select c_id from course where t_id = (select t_id from teacher where t_name = '张三')));

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 查询有两门或两门以上不及格的s_id: select s_id from score where s_score < 60 GROUP BY s_id having count(*) >= 2;
select a.s_id, a.s_name, AVG(b.s_score)
from student a
         left join score b on a.s_id = b.s_id
where a.s_id in (select s_id from score where s_score < 60 GROUP BY s_id having count(*) >= 2)
GROUP BY a.s_id;