SQL语句学习

创建school数据库

create database school;
use school;

创建四张表

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);


create table teacher (
t_id varchar(10),
t_name varchar(20)
);

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

往表里插值

insert into student (s_id, s_name, s_age, s_sex)
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' , '郑竹' , '1989-07-01' , '女'),
        ('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '语文' , '02'),
        ('02' , '数学' , '01'),
        ('03' , '英语' , '03');

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

insert into score (s_id, c_id, score)
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);

看下建好的四张表

 创建一张总总表

create table total(
select a.s_id as s_id,a.s_name as s_name,a.s_age as s_age,a.s_sex as s_sex,
b.c_id as c_id,b.score as score,c.t_id as t_id,d.t_name as t_name
from student a
left join
score  b on a.s_id=b.s_id
left join
course c on b.c_id=c.c_id
left join
teacher d on c.t_id=d.t_id
);
select * from total;

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

select a.s_id as s_id,score1,score2 from
(select s_id, score as score1 from score where c_id='01') a
inner join
(select s_id, score as score2 from score where c_id='02') b
on a.s_id=b.s_id
where score1>score2;

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

select a.s_id as s_id,score1,score2 from
(select s_id, score as score1 from score where c_id='01') a
inner join
(select s_id, score as score2 from score where c_id='02') b
on a.s_id=b.s_id
where score1<score2;

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

select student.s_id as s_id,student.s_name as s_name,b.avg_score as avg_score from student 
right join 
(select s_id,avg(score) as avg_score from score
group by s_id having avg_score>60) b
on student.s_id=b.s_id;

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

select student.s_id as s_id,student.s_name as s_name,b.avg_score as avg_score from student 
right join 
(select s_id,avg(score) as avg_score from score
group by s_id having avg_score<60) b
on student.s_id=b.s_id;

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

select s_id, s_name, count(c_id) as c_num, sum(score) as total_score
from total
group by s_id ;

 6、查询”李”姓老师的数量

select count(t_name) from teacher
where t_name like '李%';

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

select distinct s_id,s_name,s_age,s_sex
from total
where t_name='张三';

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

select * from student
where s_id not in
(select distinct s_id
from total
where t_name='张三');

 9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息

select * from student
where s_id in
(select s_id from score where c_id='01')
and s_id in
(select s_id from score where c_id='02');

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

select * from student
where s_id in
(select s_id from score where c_id='01')
and s_id not in
(select s_id from score where c_id='02');