常用且有用的sql练习（还在更新中。。。）

                                           今天也要努力学习

sql语句的掌握对于从事数据工作或者开发工作又或者是无关人员都是非常重要的  ，因为多掌握一门技术你就有机会做更多的事，哈哈。

我练习的数据库是mysql（数据库都是大同小异的）

  提供练习的数据：
  --学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
  --课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
  --教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
  --成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
  --插入学生表测试数据
insert into Student values('01' , 'wuyue' , '1997-01-01' , '男');
insert into Student values('02' , '你' , '1997-12-21' , '男');
insert into Student values('03' , '我' , '1997-05-20' , '男');
insert into Student values('04' , '他' , '1997-08-06' , '男');
insert into Student values('05' , '隔壁的wuyue' , '1997-12-01' , '女');
insert into Student values('06' , '隔壁的你' , '1997-03-01' , '女');
insert into Student values('07' , '隔壁的我' , '1987-07-01' , '女');
insert into Student values('08' , '隔壁的他' , '1997-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '体育' , '02');
insert into Course values('02' , '影视' , '01');
insert into Course values('03' , '音乐' , '03');

  --教师表测试数据
insert into Teacher values('01' , '科比');
insert into Teacher values('02' , '胡歌');
insert into Teacher values('03' , '周杰伦');

  --成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

 

开始练习：
-- 1查询"01"课程比"02"课程成绩高的学生的信息及课程分数    
select a.*,b.c_id,b.s_score,c.c_id,c.s_score
from student a
LEFT JOIN score b on a.s_id = b.s_id and b.c_id='01'
left JOIN score c on a.s_id = c.s_id and c.c_id='02'
WHERE b.s_score > c.s_score

测试结果：

-- 2查询平均成绩大于等于60的学生编号和学生姓名和学生成绩

select student.s_id,student.s_name,Round(AVG(score.s_score),2) avgscore
from student,score
WHERE score.s_id=student.s_id
group by student.s_id,student.s_name
HAVING AVG(score.s_score)>60

测试结果：

-- 3查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
    select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avgscore from
    student b
    left join score a on b.s_id = a.s_id
    GROUP BY b.s_id,b.s_name HAVING avgscore <60
    UNION
    SELECT c.s_id,c.s_name,0 as avgscore
    from student c
    where c.s_id not in (
    SELECT DISTINCT s_id from score
    )

  测试结果：

-- 4查询学过"胡歌"老师授课的同学的信息
select a.*
from student a
JOIN score b
on a.s_id = b.s_id
where b.c_id in(
SELECT c_id from course where t_id =(
select t_id from teacher where t_name = '胡歌'
))

测试结果：

-- 5查询没学过"胡歌"老师授课的同学的信息
select * from
student c
where c.s_id not in(
select a.s_id from student a
join score b on a.s_id=b.s_id where b.c_id in(
select a.c_id from
course a
join teacher b
on a.t_id = b.t_id where t_name ='胡歌'));

测试结果：

 -- 6查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 
select a.* from
student a
where a.s_id in (select s_id from score where c_id='01' )
and a.s_id not in(select s_id from score where c_id='02')

测试结果：

           

-- 7查询没有学全所有课程的同学的信息
select *
from student
where s_id not in(
select s_id from score t1  
group by s_id having count(*) =(select count(distinct c_id)  from course))

测试结果：

-- 8查询不同老师所教不同课程平均分从高到低显示         
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score
from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;

测试结果：

、

-- 9查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select * from student where s_id in(
    select distinct a.s_id
    from score a where a.c_id in(
    select a.c_id from score a where a.s_id='01')
)

-- 10查询不同老师所教不同课程平均分从高到低显示
    select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score
    from     course a
    left join score b on a.c_id=b.c_id
    left join teacher c on a.t_id=c.t_id
    GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;