05 MySQL数据库--查询语句学习笔记

整理各种查询语句，需要查询数据请参考：

https://blog.csdn.net/chennuan1991/article/details/105240457

基础查询语句，请参考：

https://blog.csdn.net/chennuan1991/article/details/105250429

分组查询

01 查询每门课程的平均成绩

mysql> select avg(degree) from score group by cno;
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
|     76.3333 |
|     81.6667 |
+-------------+
3 rows in set (0.00 sec)

 02 查询score表中至少有2名学生选修，并以3开头的课程的平均分数

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

03  查询至少有2名男生的班号

mysql> select class from students where ssex ='男' group by class having count(*)>1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.00 sec)

04 查询students表中不姓王的同学记录

mysql> select * from students where sname not like '王%';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | birthday            | class |
+-----+--------+------+