MYSQL 50题其一

作为一个优秀的开发，SQL基本快忘干净了，这真是一个罪过！以后要用到了，每天学一点，慢慢的也会是SQL Boy！

先了解什么是join（inner join），left join，right join，full join，union， union all
join内连接，根据相同的键使得两个表连接起来，取得是交集，即两个表都有这个键的数据
left join：左连接，两个表以左边的表为基准取出连接的数据，如果右边表的数据没有匹配的，那么右边的数据为空也会返回
right join： 有连接，以右边的表为基准，即使左边的数据没有匹配，左边的数据为空也会返回
full join： 全连接，全部的都数据都会返回，只有表中有数据不管是不是匹配，都会返回
union： 表示的是两个查询的连接，要求是两个select有相同的列，但是重复的只会保留一个
union all: 表示的是两个查询的连接，即使两个select有相同的列，也全部返回

创建表：

– 创建数据库

CREATE DATABASE `test` character SET utf8 COLLATE utf8_general_ci;

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' , '赵雷' , '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 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"课程成绩高的学生的信息及课程分数
   先选出所有01课程的成绩，这个地方没有用left join和right join，是因为left join 以左边的为基础，那么右边的成绩表如果有某一个学生的成绩为空的话，也会显示出来是空，因为查询的是01 课程比02课程大的的成绩，01为空的话 直接不符合，就过滤掉了

SELECT *
FROM student AS s
JOIN score AS sc
ON s.s_id = sc.s_id AND sc.c_id = '01'

接着和选出这写学生的02课程的成绩，这个时候用的是left join，以所有01课程的表为基础连接，这个时候02课程的成绩是可以为空的，因为02缺考，肯定比01课程的成绩小

SELECT *
FROM student AS s
JOIN score AS sc
ON s.s_id = sc.s_id AND sc.c_id = '01'
LEFT JOIN score sc2
ON s.s_id = sc2.s_id AND sc2.c_id ='02'

接着接上 过滤条件就好了，所以完成的SQL：

SELECT s.s_name,sc.c_id,sc.s_score,sc2.c_id,sc2.s_score
FROM student AS s
JOIN score AS sc
ON s.s_id = sc.s_id AND sc.c_id = '01'
LEFT JOIN score sc2
ON s.s_id = sc2.s_id AND sc2.c_id ='02'
WHERE sc.s_score > sc2.s_score OR sc2.s_score IS NULL

结果：

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
   这次的查询01课程是可以缺考的，所以01的成绩可以为空，但是02的不能为空，所以我们可以以学生表为基准选出01的课程，在内联02的课程

SELECT s.s_name,sc1.c_id,sc1.s_score,sc2.c_id,sc2.s_score
FROM student AS s
LEFT JOIN score as sc1
ON s.s_id = sc1.s_id AND sc1.c_id = '01'

JOIN score as sc2
ON s.s_id = sc2.s_id AND sc2.c_id = '02'

WHERE sc1.s_score < sc2.s_score OR sc1.s_score IS NULL

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
   这个里面需要用一个求平均数的函数 AVG，还有一个辅助函数ROUND，这个函数的意思是求取数值小数点后面的位数，比如 ROUND(num,2),求取两位数。

所以这个题目不难，需要聚合根据s_id进行分组，求平均值即可。一种是先将学生表和分数表连接之后，分组，求平均值。 还有一种是先求取score的平均值，然后和学生表关联：

SELECT s.s_id, s.s_name, ROUND(AVG(sc.s_score),2) AS aver
FROM student as s 
JOIN score sc 
ON s.s_id = sc.s_id 
GROUP BY s.s_id 
HAVING aver > 60

SELECT s.*, result.avg_score FROM student AS s
RIGHT JOIN
(SELECT s_id, ROUND(AVG(s_score),2) AS avg_score
FROM score GROUP BY s_id HAVING avg_score > 60) AS result
ON s.s_id = result.s_id

4. 查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩
   需要用的COUNT表示统计行数，SUM表示某列的和。 既然聚合，那么肯定有分组，所以现根据学生Id进行分组

SELECT s.*, COUNT(sc.c_id) AS "课程数", SUM(sc.s_score) AS "总分"
FROM student AS s 
LEFT JOIN score as sc 
ON s.s_id = sc.s_id
GROUP BY s.s_id

5. 查询‘李’姓老师的数量
   这个简单，在老师表中模糊匹配，求数量即可
   
   欢迎关注我的微信公众号： 北风中独行的蜗牛

参考：https://blog.csdn.net/fashion2014/article/details/78826299