从永远到永远-sql语句-（自己的sql能力真的菜的感人。。。）

我连续两天的面试中考到了sql，银行的项目，对sql的要求肯定是比较高的。我清楚地知道自己的sql水平，大约黑铁1到黄铜5左右，显然不能满足程序员标准。从今天开始，每周写一个sql语句加强sql能力。

网上找了经典sql50题https://www.cnblogs.com/Diyo/p/11424844.html，开始今天的学习。
以下是测试用表：

/*
SQLyog Ultimate v9.62 
MySQL - 5.5.55 : Database - mysql_study
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mysql_study` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql_study`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `c_id` varchar(20) NOT NULL DEFAULT '',
  `c_name` varchar(20) NOT NULL DEFAULT '',
  `t_id` varchar(20) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */

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

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `c_id` varchar(20) NOT NULL DEFAULT '',
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `score` */

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

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `s_name` varchar(20) NOT NULL DEFAULT '',
  `s_brith` varchar(20) NOT NULL DEFAULT '',
  `s_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`s_id`,`s_name`,`s_brith`,`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','女');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `t_id` varchar(20) NOT NULL DEFAULT '',
  `t_name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

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

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

有一些基本原则，要遵守。
https://www.cnblogs.com/Jessy/p/3527091.html
sql中最多的肯定是查询，查询中用到的关键词主要包含六个，并且他们的顺序依次为
select–from–where–group by–having–order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from–where–group by–having–select–order by,
from:需要从哪个数据表检索数据

where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

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

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT stu.*,sc1.s_score,sc2.s_score FROM
student stu LEFT JOIN score sc1 ON stu.s_id=sc1.s_id AND sc1.c_id=01
LEFT JOIN score sc2 ON stu.s_id=sc2.s_id AND sc2.c_id=02
WHERE sc1.s_score > sc2.s_score

分析：第一题我就没写出来，对照答案，分析以下sql思路。首先确定select后边内容，然后肯定要做关联，只有可能有学生没有成绩，但是绝对不会出现有成绩对应不到学生，所以选择leftjoin。on后边接的连接条件。分别leftjoin两次score表以两颗成绩做查询条件。最后补充查询条件"01"课程比"02"课程成绩高。

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

#我的答案
SELECT * FROM (SELECT stu.`s_id`,stu.`s_name`,AVG(sc1.`s_score`) av
FROM 
student stu LEFT JOIN score sc1 ON stu.`s_id`=sc1.`s_id`
GROUP BY stu.`s_id`
HAVING AVG(sc1.`s_score`))t
WHERE t.av>=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 ROUND(AVG(a.s_score),2)>=60;

分析：
round函数有无倒是无所谓，是否四舍五入罢了。最重要体现出我对having的理解不够到位，having除了执行顺序不同于where之外也可以首先where条件判断的功能，我却又写了个嵌套判断势必影响效率。
avg这种分组函数，要结合group by的使用。
3.

我的：
 SELECT  stu.`s_id`,stu.`s_name`,AVG(sc.s_score) 
 FROM student stu LEFT JOIN score sc ON stu.`s_id`= sc.s_id     
 GROUP BY stu.`s_id`
 HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score)IS NULL
网上：
 SELECT  stu.`s_id`,stu.`s_name`,AVG(sc.s_score) 
 FROM student stu LEFT JOIN score sc ON stu.`s_id`= sc.s_id     
 GROUP BY stu.`s_id`
 HAVING AVG(sc.s_score)<60
 UNION
 SELECT stu.`s_id`,stu.`s_name`,0 AS avg_score
 FROM student stu 
 WHERE stu.`s_id` NOT IN (
	SELECT DISTINCT s_id FROM score
 )

分析：玛德，好像不大对。
我的结果执行完：

union 是合并的意思，而且不去重。主要就是看union后边的数据如下，给了一个默认0的值，然后与上边合并。