Mysql 练习题 第一部分

Mysql 练习题 第一部分

首先说明Mysql 版本：
8.0.15
题目数据主要参考了:
https://www.cnblogs.com/zhangkaimin/p/11052469.html
我自己的表字段如下：
course 表：

CREATE TABLE `course` (
  `courseId` varchar(10) DEFAULT NULL,
  `courseName` varchar(10) DEFAULT NULL,
  `teacherId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

scores 表：

CREATE TABLE `scores` (
  `stuId` varchar(10) DEFAULT NULL,
  `courseId` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

student表：

CREATE TABLE `student` (
  `stuId` varchar(10) DEFAULT NULL,
  `stuName` varchar(10) DEFAULT NULL,
  `stuAge` int(11) DEFAULT NULL,
  `stuSex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

teacher表：

CREATE TABLE `teacher` (
  `teacherId` varchar(10) DEFAULT NULL,
  `teacherName` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

部分题目有甄误 注释等

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

– 1.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score 课程01的分数,c.score 课程02的分数 from student a , scores b , scores c
where a.stuId = b.stuId and a.stuId = c.stuId and b.courseId = '01' and c.courseId = '02' and b.score > c.score;

– 1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select a.* , b.score 课程01的分数,c.score 课程02的分数 from student a
left join scores b on a.stuId = b.stuId and b.courseId = '01'
left join scores c on a.stuId = c.stuId and c.courseId = '02'
where b.score > COALESCE(c.score,-100);
-- 甄误： isnull(c.score)

– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

– 2.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score 课程01的分数,c.score 课程02的分数 from student a , scores b , scores c
where a.stuId = b.stuId and a.stuId = c.stuId and b.courseId = '01' and c.courseId = '02' and b.score < c.score;

– 2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况

select a.* , b.score 课程01的分数 ,c.score 课程02的分数 from student a
left join scores b on a.stuId = b.stuId and b.courseId = '01'
left join scores c on a.stuId = c.stuId and c.courseId = '02'
where COALESCE(b.score,-100) < c.score; 
-- 甄误： isnull(b.score,0)

– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.stuId , a.stuName ,cast(avg(b.score) as decimal(18,2)) as avg_score
from student a , scores b
where a.stuId = b.stuId
group by a.stuId , a.stuName
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.stuId;
-- select * from tba ,tbb where tba.id = tbb.id 等价于select * from tba join tbb on tba.id = tbb.id

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

– 4.1、查询在sc表存在成绩的学生信息的SQL语句。

select a.stuId , a.stuName ,cast(avg(b.score) as decimal(18,2)) as avg_score
from student a , scores b
where a.stuId = b.stuId
group by a.stuId , a.stuName
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.stuId;

– 4.2、查询在sc表中不存在成绩的学生信息的SQL语句。

select a.stuId , a.stuName ,cast(avg(b.score) as decimal(18,2)) as avg_score
from student a left join scores b
on a.stuId = b.stuId
group by a.stuId , a.stuName
having COALESCE(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.stuId;

– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

– 5.1、查询所有有成绩的SQL。

select a.stuId 学生编号 , a.stuName 学生姓名 , count(DISTINCT b.courseId) 选课总数, sum(b.score) 所有课程的总成绩
from student a , scores b
where a.stuId = b.stuId
group by a.stuId,a.stuName
order by a.stuId;

– 5.2、查询所有(包括有成绩和无成绩)的SQL。

select a.stuId 学生编号 , a.stuName 学生姓名 , count(DISTINCT b.courseId) 选课总数, sum(b.score) 所有课程的总成绩
from student a left join scores b
on a.stuId = b.stuId
group by a.stuId,a.stuName
order by a.stuId;

– 6、查询"李"姓老师的数量
– 方法1

select count(DISTINCT teacherName) 李姓老师的数量 from teacher where teacherName like '李%';
-- 正则 左侧匹配

– 方法2

select count(DISTINCT teacherName) 李姓老师的数量 from teacher where left(teacherName,1) = '李';

– 7、查询学过"张三"老师授课的同学的信息

select distinct student.* from student , scores , course , teacher
where student.stuId = scores.stuId and scores.courseId = course.courseId 
      and course.teacherId = teacher.teacherId and teacher.teacherName = '张三'
order by student.stuId;

select distinct a.* from student a 
left join scores b on a.stuId = b.stuId
left join course c on b.courseId = c.courseId
left join teacher d on c.teacherId = d.teacherId
where d.teacherName = '张三'
order by a.stuId;

– 8、查询没学过"张三"老师授课的同学的信息

select m.* from student m 
	where m.stuId not in 
		(select distinct scores.stuId from scores , course , teacher 
			where scores.courseId = course.courseId and course.teacherId = teacher.teacherId and teacher.teacherName = '张三'
		) 
order by m.stuId;

– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
– 方法1

select student.* from student , scores 
where student.stuId = scores.stuId and scores.courseId = '01' 
and exists (Select 1 from scores SC_2 where SC_2.stuId = scores.stuId and SC_2.courseId = '02') order by student.stuId;

– 方法2

select student.* from student , scores 
where student.stuId = scores.stuId and scores.courseId = '02' 
and exists (Select 1 from scores SC_2 where SC_2.stuId = scores.stuId and SC_2.courseId = '01') order by student.stuId;
--  exits 1 不用指定主键

select student.* from student , scores 
where student.stuId = scores.stuId and scores.courseId = '02' 
and student.stuId in (Select SC_2.stuId from scores SC_2 where SC_2.stuId = scores.stuId and SC_2.courseId = '01') order by student.stuId;
-- xx in  select xx 要指定主键

– 方法3

select m.* from student m where stuId in
(
select stuId from
(
	select distinct stuId from scores where courseId = '01'
	union all
	select distinct stuId from scores where courseId = '02'
) t group by stuId having count(1) = 2
)
order by m.stuId;

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

– 方法1

select student.* from student , scores 
where student.stuId = scores.stuId and scores.courseId = '01' 
and not exists (Select 1 from scores SC_2 where SC_2.stuId = scores.stuId and SC_2.courseId = '02') order by student.stuId;

– 方法2

select student.* from student , scores 
where student.stuId = scores.stuId and scores.courseId = '01' 
and student.stuId not in (Select SC_2.stuId from scores SC_2 where SC_2.stuId = scores.stuId and SC_2.courseId = '02') order by student.stuId;