mysql查询学习笔记【二】

一. 查询最高分

查询score表中最高分的学生的学号和课程号

select sno,cno from score where degree=(select max(degree) from score);

查询结果：

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+

这个语句是嵌套了一下，我们做一个分析：
如果我们想知道最高分是多少，我们需要：select max(degree) from score;
我们看一下执行这个语句会出现什么？

mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
|          92 |
+-------------+

我们会得到一个最高分92。然后我们需要查询这个成绩为92的学生的学号和课程号
那么就是select sno,cno from score where degree=92;
那我们把这两个语句合起来就是：
select sno,cno from score where degree=(select max(degree) from score);
大概就是这样！
排序做法
其实如果说最高分只有一个的话，我们还可以用排序实现，但是如果最高分有多个的话排序就会有一点bug，但是我们还是学习一下排序：
我们先让按照成绩降序排列：

mysql> select * from score order by degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
+-----+-------+--------+

然后我们只需要取出最上面的那一条记录就好，在查询结果中取出几条记录应该怎么办呢？
这里用到了limit 关键字，具体用法：
limit num1,num2;其中num1是从第一条记录开始跳过几条记录开始，num2是连续取多少条，例如：
limit 0,1：从第一条记录跳过0条也就是第一条开始，连续取一条，就是第一条记录
limit 1,2：从第一条记录跳过1条也就是第二条记录开始，连续取两条，也就是第二条第三条记录。
我们回到最高分这个问题：
我们按照降序排列取出第一条记录就好了：

select * from score order by degree desc limit 0,1;

结果如下：

mysql> select * from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+

二. 分组查询

查询每门课的平均成绩
如果我们要查询一门成绩的平均值，比如说要查询3-105这门课要怎么办呢？

select avg(degree) from score where cno=‘3-105’;

我们就会得到：

mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+

但是如果课程数很多很多的话，一个一个的去查询就太慢了，这时候我们就用到了分组查询，具体用法：

select cno,avg(degree) from score group by cno;

我们就会得到：

mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

group by xxx；就是对xxx进行分组，如果我们想对每个学生算他们的每个人的平均分，就是按照学号进行分组：

mysql> select sno,avg(degree) from score group by sno;
+-----+-------------+
| sno | avg(degree) |
+-----+-------------+
| 103 |     87.6667 |
| 105 |     80.6667 |
| 109 |     75.0000 |
+-----+-------------+

三. 分组条件与模糊查询

查询score表中至少有两名学生选修并且以3开头的课程的平均分数
我们刚刚学了怎么查询平均分数：

mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

那么接下来需要解决的就是筛选至少有两名学生选修和以3开头的课程
先说至少有两名学生选修，我们查询一下每门课有多少个学生选修：count（cno）然后记上>=2就可以了，也就是count(cno) >= 2;
然后就是以3开头的课程：这里用到了模糊查询like（我个人理解是格式匹配）
cno like’3%'这个%就是一个适配符，他可以匹配任意的字符，也就是3后面是什么都行。
然后把他们连接起来就好了

select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like’3%’;

其中这个having就相当于之前的where，where过滤行，having过滤分组，也就是在group by后面要有什么查询条件要跟having。
接下来看下查询结果：

mysql> select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like'3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+