mysql建表以及子查询

mysql子查询

1.创建数据库
create database school2;
2.切换/使用数据库
use school2;
3.创建表，插入数据
create table student(
sid int primary key not null ,
sname varchar(10) not null,
sbirth date not null,
ssex varchar(1) not null
);
insert into student values(1,‘罗永浩’,‘1972-06-01’,‘男’),
(2,‘李佳奇’,‘1992-08-02’,‘男’),
(3,‘薇娅’,‘1985-09-03’,‘女’)
;
select *from student;

create table course(
cid int primary key not null,
cname varchar(20) not null,
tid int not null
);

insert into course values(1,‘培训英语’,1),(2,‘直播卖货’,2);
select *from course;

create table teacher(
tid int primary key not null,
tname varchar(10) not null

);

insert into teacher values(1,‘新东方老板—俞敏洪’),(2,‘今日头条老板’);
select *from teacher;

create table scores(
sid int not null,
cid int not null,
sscore int not null
);

insert into scores values(1,1,99),(1,2,75),(2,1,50),(2,2,95),(3,1,64),(3,2,93);

select *from scores;
4.查询
#1、横向显示学生所学课程的成绩（例如：学生姓名，学生课程1的分数，学生课程2的分数）提示：子查询
select student.sname as ‘学生姓名’,S.sscore as ‘课程1-英语’,V.sscore as ‘课程2-直播’ from student
left join scores as S on student.sid=S.sid and S.cid=1
left join scores as V on student.sid=S.sid and V.cid=2;

#2、找出直播带货课程的成绩，比培训英语成绩高的学生。提示：子查询
select S1.sid as ‘学生编号’,student.sname as ‘学生姓名’,S1.sscore as ‘培训英语’,V1.sscore as ‘直播卖货’ from
(select S.sid,S.sscore from scores as S where S.cid=1) as S1
left join
(select V.sid,V.sscore from scores as V where V.cid=2) as V1 on S1.sid=V1.sid
left join student on student.sid=S1.sid
where S1.sscore < V1.sscore;

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select student.sid as ‘学生编号’ ,sname as ‘学生姓名’,avg(sscore) as ‘平均成绩’ from student
left join scores on student.sid=scores.sid
group by student.sid having avg(sscore)>=60;

select S.sid,S.sname,V.avgscore from student as S
left join (select S.sid,avg(S.sscore) as avgscore from scores as S group by S.sid having avg(S.sscore)>60) as V
on S.sid=V.sid;

#4、查询在教师-今日头条老板上过课的学生的个人信息
1）
select student.sid,sname,sbirth,ssex from student left join scores on student.sid=scores.sid
where cid =(select cid from course
left join teacher on course.tid=teacher.tid
where tname=‘今日头条老板’);

2）
select *from student as S
left join scores as SC on S.sid=SC.sid
left join course as C on C.cid=SC.cid
left join teacher as T on T.tid=C.tid
where T.tname=‘今日头条老板’;

#5、查询比学生-罗永浩在课程直播卖货的成绩高的学生信息

select * from student
left join scores on student.sid=scores.sid
left join course on course.cid=scores.cid
left join teacher on teacher.tid=course.tid
where sscore >(select sscore from scores
left join course on scores.cid=course.cid
left join student on scores.sid=student.sid
where cname=‘直播卖货’ and sname=‘罗永浩’)
and cname=‘直播卖货’;

#6、查询不同教师教授的课程的平均分。（例如：教师，课程1的平均分，课程2的平均分）
select tname as ‘教师’,s.(avg(sscore)) ,v.(avg(sscore)) from teacher
left join course on course.tid=course.tid
left join scores on scores.cid=course.cid
left join scores as s on student.sid=S.sid and S.cid=1
left join scores as v on student.sid=S.sid and V.cid=2

group by tname
;