mysql-阿里-50道题目解答

 use test;

 create table stu(
	s_id varchar(10) primary key,
    s_name varchar(10),
    s_birth date,
    s_sex varchar(10)
 );
 insert into stu values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
 
 create table co(
	c_id varchar(10) primary key,
    c_name varchar(10),
    t_id varchar(10)
 );
 
insert into co values
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');

create table te(
	t_id varchar(10) primary key,
    t_name varchar(10)
);

insert into te values
('01' , '张三'),
('02' , '李四'),
('03' , '王五');

create table sc(
	s_id varchar(10),
    c_id varchar(10),
    score int
);

insert into sc values
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

show tables;
 
-- 1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select * from stu;
select * from sc;
select score, s_id, c_id from sc where c_id = '01';
select score, s_id, c_id from sc where c_id = '02';
select s_id from sc where (select score from sc where c_id = '01') >
(select score from sc where c_id = '02');


select * from (
select zz.score1, zz.score2, stu.s_id s_id, stu.s_name, stu.s_birth, stu.s_sex from 
(select qq.score score1, qq.s_id s_id, qq.c_id c_id1, aa.score score2,
aa.s_id s_id2, aa.c_id c_id2 from
(select score, s_id, c_id from sc where c_id = '01') as qq
left join 
(select score, s_id, c_id from sc where c_id = '02') as aa
on qq.s_id = aa.s_id) as zz
join stu on zz.s_id = stu.s_id
) as ww
where 
score1 > score2;

-- 官方解答...不一定非要连接完一张表再加条件啊，可以边连接边加条件啊。t1.score>t2.score
-- 
select * from sc where c_id = '01';
select * from sc where c_id = '02';

select * from 
(select * from sc where c_id = '01') t1
join (select * from sc where c_id = '02') t2 on t1.s_id = t2.s_id;

-- (1)还需再连接学生表
select * from 
(select * from sc where c_id = '01') t1
join (select * from sc where c_id = '02') t2 on t1.s_id = t2.s_id
join stu on t1.s_id =stu.s_id
where t1.score>t2.score; 
-- (2)
select stu.*, t1.c_id, t1.score, t2.c_id, t2.score from 
(select * from sc where c_id = '01') t1
join (select * from sc where c_id = '02') t2 on t1.s_id = t2.s_id
join stu on t1.s_id =stu.s_id
where t1.score>t2.score; 
-- (3)
select stu.*, sc.c_id, sc.score from 
(select * from sc where c_id = '01') t1
join (select * from sc where c_id = '02') t2 on t1.s_id = t2.s_id
join stu on t1.s_id =stu.s_id
join sc on stu.s_id = sc.s_id
where t1.score>t2.score; 
-- ---------------------------------------------------------------------------
-- --2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select stu.*, sc.c_id, sc.score from 
(select * from sc where c_id = '01') t1
join (select * from sc where c_id = '02') t2 on t1.s_id = t2.s_id
join stu on t1.s_id =stu.s_id
join sc on stu.s_id = sc.s_id
where t1.score<t2.score; 

-- ---------------------------------------------------------------------------
-- --3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select * from sc;

select t1.s_id,  stu.s_name, t1.avg_score from 
(select s_id, avg(score) avg_score from sc 
group by s_id
having avg(score)>=60) as t1
join stu on stu.s_id = t1.s_id;

-- 注：先连接，直接加附加条件,这是最简单的
select stu.s_id, s_name, avg(score) from stu 
left join sc on stu.s_id = sc.s_id
group by stu.s_id -- 有group by就要开始聚合了
having avg(score)>=60;

-- 4. 查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩
select t1.s_id,  stu.s_name, t1.avg_score from 
(select s_id, avg(score) avg_score from sc 
group by s_id
having avg(score)<60) as t1
join stu on stu.s_id = t1.s_id;

-- 注：先连接，直接加附加条件,这是最简单的
select stu.s_id, s_name, avg(score) from stu 
left join sc on stu.s_id = sc.s_id
group by stu.s_id -- 有group by就要开始聚合了
having avg(score)<60;

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


-- 6、查询"李"姓老师的数量
select count(t_name) from te where t_name like "李%";


-- --7、查询学过"张三"老师授课的同学的信息
select stu.*, te.t_name from te
left join co on te.t_id = co.t_id
left join sc on co.c_id = sc.c_id
left join stu on stu.s_id = sc.s_id
where te.t_name = "张三";

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

-- 29、查询名字中含有"风"字的学生信息
select * from stu where s_name like "%风%";

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 注意max等函数都是在where之前执行的，所以不能放在where后，可以放在select后
select * from (select stu.*, te.t_name, score from te
left join co on te.t_id = co.t_id
left join sc on co.c_id = sc.c_id 
left join stu on stu.s_id = sc.s_id
where te.t_name = "张三") as t1
where t1.score = (
select max(t.score) from (
select stu.*, te.t_name, score from te
left join co on te.t_id = co.t_id
left join sc on co.c_id = sc.c_id 
left join stu on stu.s_id = sc.s_id
where te.t_name = "张三"
) as t);

-- 如果用排序order by limit 1会导致如果出现两个最高值一样的学生，第二个最高值就出不来了

 

这里需要注意的点：max函数是在where之前执行的。