MYSQL必练50题（第一天 ）

一、表和库的建立

create database mysql_test;
use mysql_test;
drop table student;
drop table score;
drop table course;
drop table teacher;
create table student(
		s_id int,
        s_name varchar(8),
        s_birth date,
        s_sex varchar(4)
);
insert into student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');

create table course (
	c_id int,
	c_name varchar(8),
	t_id int
);

insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

create table teacher (
	t_id int,
	t_name varchar(8)
);

insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');

create table score (
	s_id int,
	c_id int,
	s_score int
);
insert into score values
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

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

select s.* ,sc1.s_score,sc2.s_score 
from student s left join 
(select * from score where c_id = 1) as sc1
on s.s_id = sc1.s_id
inner join 
(select * from score where c_id = 2) as sc2
on s.s_id = sc2.s_id
where sc1.s_score>sc2.s_score;

执行结果

第二题
查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select s.* ,sc1.s_score,sc2.s_score 
from student s left join 
(select * from score where c_id = 1) as sc1
on s.s_id = sc1.s_id
inner join 
(select * from score where c_id = 2) as sc2
on s.s_id = sc2.s_id
where sc1.s_score<sc2.s_score;

第三题
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id,s.s_name,round(avg(sc.s_score),2) as avg_score
from student s inner join score sc on
s.s_id = sc.s_id group by sc.s_id having avg_score >=60;

第四题
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

select s.s_id,s.s_name,ifnull(round(avg(sc.s_score),2),"null") as avg_score
from student s left  join score sc on
s.s_id = sc.s_id group by sc.s_id having avg_score <60;

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

select s.s_id,s.s_name,count(sc.c_id),ifnull(sum(sc.s_score),0)
from student s left join score sc
on s.s_id = sc.s_id
group by sc.s_id;