SQL练习题以及一些思路

原博主文章：https://blog.youkuaiyun.com/fashion2014/article/details/78826299，不定时更新中。

• 创建表

  --创建学生表
  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 `teacher`(
  	`t_id` VARCHAR(20),
  	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
  	PRIMARY KEY(`t_id`)
  );
  --创建课程表
  CREATE TABLE `cource`(
  	`c_id` VARCHAR(20),
  	`c_name` VARCHAR(20) NOT NULL DEFAULT ' ',
  	`t_id` VARCHAR(20) NOT NULL,
  	PRIMARY KEY (`c_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' , '赵雷' , '1990-01-01' , '男');
  insert into `student` values('02' , '钱电' , '1990-12-21' , '男');
  insert into `student` values('03' , '孙风' , '1990-05-20' , '男');
  insert into `student` values('04' , '李云' , '1990-08-06' , '男');
  insert into `student` values('05' , '周梅' , '1991-12-01' , '女');
  insert into `student` values('06' , '吴兰' , '1992-03-01' , '女');
  insert into `student` values('07' , '郑竹' , '1989-07-01' , '女');
  insert into `student` values('08' , '王菊' , '1990-01-20' , '女');
  --教师表测试数据
  insert into `teacher` values('01' , '张三');
  insert into `teacher` values('02' , '李四');
  insert into `teacher` values('03' , '王五');
  --课程表测试数据
  insert into `cource` values('01' , '语文' , '02');
  insert into `cource` values('02' , '数学' , '01');
  insert into `cource` values('03' , '英语' , '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);

•  第一个问题：查询"01"课程比"02"课程成绩高的学生的信息及课程分数

   原作者的写法：

select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a 
	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' or c.c_id = NULL where b.s_score>c.s_score
	
--也可以这样写
select a.*,b.s_score as 01_score,c.s_score as 02_score from student a,score b,score c 
	where a.s_id=b.s_id 
	and a.s_id=c.s_id 
	and b.c_id='01' 
	and c.c_id='02' 
	and b.s_score>c.s_score

原作者的写法很棒，但是对于像我这样的菜鸟来说看起来还是很吃力的，不妨分步来做。

--分步：
--第一步，查询所有学生信息
SELECT * from `student`;
--第二步，查询学生信息和课程01的成绩
SELECT stu.*,sco01.s_score AS 语文 FROM `student` stu JOIN `score` sco01 ON stu.s_id=sco01.s_id WHERE sco01.c_id = '01';
--第三步，查询学生信息和课程02的成绩
SELECT stu.*,sco02.s_score AS 数学 FROM `student` stu JOIN `score` sco02 ON stu.s_id=sco02.s_id WHERE sco02.c_id = '02';
第四步，查询学生信息和课程01,02的成绩并通过where条件筛选符合条件的
SELECT stu.*,sco01.s_score AS 语文,sco02.s_score AS 数学 FROM `student` stu 
JOIN `score` sco01 ON stu.s_id=sco01.s_id AND sco01.c_id = '01'
JOIN `score` sco02 ON stu.s_id=sco02.s_id AND sco02.c_id = '02' 
where sco01.s_score > sco02.s_score;

结果： 

---

 

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

与第一个问题类似，不懂原作者为什么这么出题目。

---

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

 原作者的写法：

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score 
from student b 
join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score >=60;

作者写的很清楚啦，注意一下要点：

--涉及到求平均数用ROUND函数四舍五入并保留两位小数
--mysql5.7以后必须用group by 将select中所有的列名包含进去，否则报错
--有聚合函数所以要用having过滤，where是不能有聚合函数的