Mysql学习笔记(三)——单表查询

说在前面：本篇笔记里面的表在上一篇中已经创建，直接在student，course，sc三张表中进行查询。

1.概述

语句格式
SELECT [ALL|DISTINCT] <目标列表达式>
[，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

SELECT子句：指定要显示的属性列
FROM子句：指定查询对象(基本表或视图)
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING短语：筛选出只有满足指定条件的组
ORDER BY子句：对查询结果表按指定列值的升序或降序排序

2.查询若干列

查询指定列

[例1] 查询全体学生的学号与姓名。
SELECT Sno，Sname
FROM Student；

mysql> select sno,sname from student;
+-------+--------------+
| sno   | sname        |
+-------+--------------+
| 95001 | 张三         |
| 95002 | 李四         |
| 95003 | 王五         |
| 95004 | 马六         |
| 95005 | 苏三         |
| 95006 | 刘七         |
| 95007 | 刘三姐       |
| 95008 | 欧阳锋       |
| 95009 | 欧阳大侠     |
| 95010 | 陈东         |
| 95011 | 张成民       |
+-------+--------------+
11 rows in set (0.00 sec)

该句的执行过程是，从student表中取出一个元组，取出元组在属性Sno和Sname上的值，形成一个新的元组进行输出，这样遍历所有元组。

目标列表达式中的各个列的先后顺序可以与表中的顺序不一致，可以根据自己的需要改变列的显示顺序。

[例2] 查询全体学生的姓名、学号、所在系。
SELECT Sname，Sno，Sdept
FROM Student；

mysql> select Sname,sno,sdept from student;
+--------------+-------+-------+
| Sname        | sno   | sdept |
+--------------+-------+-------+
| 张三         | 95001 | CS    |
| 李四         | 95002 | IS    |
| 王五         | 95003 | MA    |
| 马六         | 95004 | CS    |
| 苏三         | 95005 | IS    |
| 刘七         | 95006 | IS    |
| 刘三姐       | 95007 | IS    |
| 欧阳锋       | 95008 | MA    |
| 欧阳大侠     | 95009 | MA    |
| 陈东         | 95010 | IS    |
| 张成民       | 95011 | CS    |
+--------------+-------+-------+
11 rows in set (0.00 sec)

[例3] 查询全体学生的详细记录。(查询全部列)
SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student；
或
SELECT *
FROM Student；

mysql> select * from student;     # '*'就代表所有属性，即所有列
+-------+--------------+------+------+-------+
| Sno   | Sname        | Ssex | Sage | Sdept |
+-------+--------------+------+------+-------+
| 95001 | 张三         | 男   |   21 | CS    |
| 95002 | 李四         | 男   |   23 | IS    |
| 95003 | 王五         | 男   |   19 | MA    |
| 95004 | 马六         | 女   |   20 | CS    |
| 95005 | 苏三         | 女   |   20 | IS    |
| 95006 | 刘七         | 女   |   19 | IS    |
| 95007 | 刘三姐       | 女   |   23 | IS    |
| 95008 | 欧阳锋       | 男   |   24 | MA    |
| 95009 | 欧阳大侠     | 男   |   23 | MA    |
| 95010 | 陈东         | 男   |   19 | IS    |
| 95011 | 张成民       | 男   |   19 | CS    |
+-------+--------------+------+------+-------+
11 rows in set (0.00 sec)

查询经过计算的值

SELECT子句的<目标列表达式> 不仅可以是表中的属性也可以是表达式、算术表达式、字符串常量、函数、列别名等。

[例4] 查全体学生的姓名及其出生年份。
SELECT Sname，2019-Sage
FROM Student；

mysql> select Sname,2019-Sage from student;
+--------------+-----------+
| Sname        | 2019-Sage |
+--------------+-----------+
| 张三         |      1998 |
| 李四         |      1996 |
| 王五         |      2000 |
| 马六         |      1999 |
| 苏三         |      1999 |
| 刘七         |      2000 |
| 刘三姐       |      1996 |
| 欧阳锋       |      1995 |
| 欧阳大侠     |      1996 |
| 陈东         |      2000 |
| 张成民       |      2000 |
+--------------+-----------+
11 rows in set (0.00 sec)

[例5] 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

SELECT Sname，’Year of Birth:’，2019-Sage，LCASE(Sdept)
FROM Student；

mysql> select Sname,'Year of Birth: ',2019-Sage,LCASE(Sdept) from student;
+--------------+-----------------+-----------+--------------+
| Sname        | Year of Birth:  | 2019-Sage | LCASE(Sdept) |
+--------------+-----------------+-----------+--------------+
| 张三         | Year of Birth:  |      1998 | cs           |
| 李四         | Year of Birth:  |      1996 | is           |
| 王五         | Year of Birth:  |      2000 | ma           |
| 马六         | Year of Birth:  |      1999 | cs           |
| 苏三         | Year of Birth:  |      1999 | is           |
| 刘七         | Year of Birth:  |      2000 | is           |
| 刘三姐       | Year of Birth:  |      1996 | is           |
| 欧阳锋       | Year of Birth:  |      1995 | ma           |
| 欧阳大侠     | Year of Birth:  |      1996 | ma           |
| 陈东         | Year of Birth:  |      2000 | is           |
| 张成民       | Year of Birth:  |      2000 | cs           |
+--------------+-----------------+-----------+--------------+
11 rows in set (0.00 sec)

这时候可以发现并没有我们想象的那样把出生年份作为列名。

[例5.1] 使用列别名改变查询结果的列标题
SELECT Sname NAME，’Year of Birth:’ BIRTH，2019-Sage BIRTHDAY， LCASE(Sdept) DEPARTMENT FROM Student；

mysql> select Sname NAME,'Year of Birth:' BIRTH,2019-Sage BIRTHDAY,LCASE(Sdept) Department
 from Student;
+--------------+----------------+----------+------------+
| NAME         | BIRTH          | BIRTHDAY | Department |
+--------------+----------------+----------+------------+
| 张三         | Year of Birth: |     1998 | cs         |
| 李四         | Year of Birth: |     1996 | is         |
| 王五         | Year of Birth: |     2000 | ma         |
| 马六         | Year of Birth: |     1999 | cs         |
| 苏三         | Year of Birth: |     1999 | is         |
| 刘七         | Year of Birth: |     2000 | is         |
| 刘三姐       | Year of Birth: |     1996 | is         |
| 欧阳锋       | Year of Birth: |     1995 | ma         |
| 欧阳大侠     | Year of Birth: |     1996 | ma         |
| 陈东         | Year of Birth: |     2000 | is         |
| 张成民       | Year of Birth: |     2000 | cs         |
+--------------+----------------+----------+------------+
11 rows in set (0.00 sec)

可以发现，select后面目标列表达式后面+空格+列别名，就可以将列名显示为规定的别名。

3.查询若干元组

消除取值重复的行

在SELECT子句中使用DISTINCT短语

[例6] 查询选修了课程的学生学号。
(1) SELECT Sno

  FROM SC;
    或(默认 ALL)
    SELECT ALL Sno
    FROM SC;

mysql> select Sno from sc;
+-------+
| Sno   |
+-------+
| 95001 |
| 95005 |
| 95011 |
| 95001 |
| 95002 |
| 95003 |
| 95005 |
| 95001 |
| 95002 |
| 95004 |
| 95005 |
| 95001 |
| 95004 |
| 95005 |
+-------+
14 rows in set (0.00 sec)

可以发现，这并不是我们想要的，因为这里面有重复的行，即一个学号选了多门课程，应该只出现一个学号。

(2) SELECT DISTINCT Sno

     FROM SC;

mysql> select distinct Sno from sc;  #加上distinct就可以消除取值相同的行
+-------+
| Sno   |
+-------+
| 95001 |
| 95002 |
| 95003 |
| 95004 |
| 95005 |
| 95011 |
+-------+
6 rows in set (0.00 sec)

注意 DISTINCT短语的作用范围是所有目标列
例：查询选修课程的各种成绩
错误的写法:
SELECT DISTINCT Cno，DISTINCT Grade
FROM SC;
正确的写法:
SELECT DISTINCT Cno，Grade
     FROM SC; 

查询满足条件的元组

WHERE子句常用的查询条件

在WHERE子句的<比较条件>中使用比较运算符
=，>，<，>=，<=，!= 或 <>，!>，!<，逻辑运算符NOT + 比较运算符

[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname，Sage
FROM Student

WHERE Sage < 20；或

SELECT Sname，Sage
FROM Student
WHERE NOT Sage >= 20；

mysql> select sname,sage from student where NOT sage >=20;
+-----------+------+
| sname     | sage |
+-----------+------+
| 王五      |   19 |
| 刘七      |   19 |
| 陈东      |   19 |
| 张成民    |   19 |
+-----------+------+
4 rows in set (0.00 sec)

使用谓词 BETWEEN … AND …
```
NOT BETWEEN  …  AND  …
```

[例10] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECT Sname，Sdept，Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23；

mysql> select Sname,Sdept,Sage from student where Sage between 20 and 23;
+--------------+-------+------+
| Sname        | Sdept | Sage |
+--------------+-------+------+
| 张三         | CS    |   21 |
| 李四         | IS    |   23 |
| 马六         | CS    |   20 |
| 苏三         | IS    |   20 |
| 刘三姐       | IS    |   23 |
| 欧阳大侠     | MA    |   23 |
+--------------+-------+------+

[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄。

SELECT Sname，Sdept，Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23；

mysql> select Sname,Sdept,Sage from student where Sage not between 20 and 23;
+-----------+-------+------+
| Sname     | Sdept | Sage |
+-----------+-------+------+
| 王五      | MA    |   19 |
| 刘七      | IS    |   19 |
| 欧阳锋    | MA    |   24 |
| 陈东      | IS    |   19 |
| 张成民    | CS    |   19 |
+-----------+-------+------+
5 rows in set (0.00 sec)

确定集合

使用谓词 IN <值表>, NOT IN <值表>
```
      <值表>：用逗号分隔的一组取值
```

[例12]查询信息系（IS）、数学系（MA）和计

算机科学系（CS）学生的姓名和性别。

SELECT Sname，Ssex
FROM Student
WHERE Sdept IN ( ‘IS’，’MA’，’CS’ );

mysql> select Sname,Ssex from student where Sdept in('IS','MA','CS');
+--------------+------+
| Sname        | Ssex |
+--------------+------+
| 张三         | 男   |
| 李四         | 男   |
| 王五         | 男   |
| 马六         | 女   |
| 苏三         | 女   |
| 刘七         | 女   |
| 刘三姐       | 女   |
| 欧阳锋       | 男   |
| 欧阳大侠     | 男   |
| 陈东         | 男   |
| 张成民       | 男   |
+--------------+------+
11 rows in set (0.00 sec)

[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
SELECT Sname，Ssex
FROM Student
```
 WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' );
```
1
2
mysql> select Sname,Ssex from student where Sdept NOT in('IS','MA','CS');
Empty set (0.00 sec)
发现查询的数据为空。

字符串匹配

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]
<匹配串>：指定匹配模板

  匹配模板：固定字符串或含通配符的字符串
  当匹配模板为固定字符串时，
  可以用 = 运算符取代 LIKE 谓词
  用 != 或 <>运算符取代 NOT LIKE 谓词

% (百分号) 代表任意长度（长度可以为0）的字符串
例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串

_ (下横线) 代表任意单个字符
例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配.

[例14] 查询学号为95001的学生的详细情况。

 SELECT *    
 FROM  Student  
 WHERE  Sno LIKE '95001'；

等价于：

SELECT *
FROM Student
WHERE Sno = ‘95001’；

mysql> select * from student where Sno='95001';
+-------+--------+------+------+-------+
| Sno   | Sname  | Ssex | Sage | Sdept |
+-------+--------+------+------+-------+
| 95001 | 张三   | 男   |   21 | CS    |
+-------+--------+------+------+-------+
1 row in set (0.00 sec)

A 匹配模板为含通配符的字符串

[例15] 查询所有姓刘学生的姓名、学号和性别。
SELECT Sname，Sno，Ssex
FROM Student
WHERE Sname LIKE ‘刘%’；

mysql> select Sname,Sno,Ssex from student where Sname like '刘%';
+-----------+-------+------+
| Sname     | Sno   | Ssex |
+-----------+-------+------+
| 刘七      | 95006 | 女   |
| 刘三姐    | 95007 | 女   |
+-----------+-------+------+
2 rows in set (0.01 sec)

匹配模板为含通配符的字符串（续）

[例16] 查询姓“刘”且全名为三个汉字的学生的姓名。

  SELECT Sname
  FROM   Student
  WHERE  Sname LIKE ‘刘_ _'；

mysql> select Sname from student where Sname like '刘__';
# 这里两个下划线间没有空格，上面例子只是显示提示要打两个空格
+-----------+
| Sname     |
+-----------+
| 刘三姐    |
+-----------+
1 row in set (0.00 sec)

匹配模板为含通配符的字符串（续）

[例17] 查询名字中第2个字为“三”字的学生的姓名和学号。

  SELECT Sname，Sno
  FROM Student
  WHERE Sname LIKE ‘_三%'；

mysql> select Sname,Sno from student where Sname like '_三%';
+-----------+-------+
| Sname     | Sno   |
+-----------+-------+
| 张三      | 95001 |
| 苏三      | 95005 |
| 刘三姐    | 95007 |
+-----------+-------+
3 rows in set (0.00 sec)

匹配模板为含通配符的字符串（续）

[例18] 查询所有不姓刘的学生姓名。

  SELECT Sname，Sno，Ssex
  FROM Student
  WHERE Sname NOT LIKE '刘%'；

mysql> select Sname from student where Sname NOT LIKE '刘%';
+--------------+
| Sname        |
+--------------+
| 张三         |
| 李四         |
| 王五         |
| 马六         |
| 苏三         |
| 欧阳锋       |
| 欧阳大侠     |
| 陈东         |
| 张成民       |
+--------------+
9 rows in set (0.00 sec)

B 使用换码字符将通配符转义为普通字符

当用户要查询的字符串本身就含有 % 或 _ 时，要使用\ 对通配符进行转义

[例19] 查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname LIKE ‘DB\_DESIGN’;

mysql> select Cno,Ccredit from course where Cname LIKE 'DB\_DESIGN';
+-----+---------+
| Cno | Ccredit |
+-----+---------+
| 2   |       2 |
+-----+---------+
1 row in set (0.00 sec)

使用换码字符将通配符转义为普通字符(续)

[例20] 查询以”DB_”开头，且倒数第3个字符为 I的课程的详细情况。

  SELECT  *
  FROM   Course
  WHERE  Cname LIKE  'DB\\\_%I_ _ _'；

mysql> select * from course where Cname LIKE 'DB\_%I__';
+-----+-----------+------+---------+
| Cno | Cname     | Cpno | Ccredit |
+-----+-----------+------+---------+
| 2   | DB_DESIGN | 3    |       2 |
+-----+-----------+------+---------+
1 row in set (0.00 sec)

C 涉及空值的查询

使用谓词 IS NULL 或 IS NOT NULL
“IS NULL” 不能用 “= NULL” 代替
[例21] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno，Cno
FROM SC
WHERE Grade IS NULL；

mysql> select Sno,Cno from SC where Grade IS NULL;
+-------+-----+
| Sno   | Cno |
+-------+-----+
| 95003 | 2   |
| 95004 | 3   |
| 95011 | 1   |
+-------+-----+
3 rows in set (0.00 sec)

[例22] 查所有有成绩的学生学号和课程号。

SELECT Sno，Cno
FROM SC
WHERE Grade IS NOT NULL；

mysql> select Sno,Cno from SC where Grade IS NOT NULL;
+-------+-----+
| Sno   | Cno |
+-------+-----+
| 95001 | 1   |
| 95001 | 2   |
| 95001 | 3   |
| 95001 | 4   |
| 95002 | 2   |
| 95002 | 3   |
| 95004 | 4   |
| 95005 | 1   |
| 95005 | 2   |
| 95005 | 3   |
| 95005 | 4   |
+-------+-----+

D 多重条件的查询

用逻辑运算符AND和 OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
可用来实现多种其他谓词
[NOT] IN
[NOT] BETWEEN … AND …

[例23] 查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname
  FROM  Student
  WHERE Sdept= 'CS' AND Sage<20；

mysql> select Sname from student where Sdept='CS' AND Sage < 20;
+-----------+
| Sname     |
+-----------+
| 张成民     |
+-----------+
1 row in set (0.00 sec)

4.对查询结果排序

使用ORDER BY子句

可以按一个或多个属性列排序
升序：ASC；降序：DESC；缺省值为升序
当排序列含空值时
ASC：排序列为空值的元组最先显示
DESC：排序列为空值的元组最后显示

[例24] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECT Sno，Grade
FROM SC
WHERE Cno= ‘ 3 ‘
ORDER BY Grade DESC；

mysql> select Sno,Grade from SC where Cno='3' order by Grade DESC;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95001 |    91 |
| 95005 |    90 |
| 95002 |    80 |
| 95004 |  NULL |
+-------+-------+
4 rows in set (0.00 sec)

[例24-补充] 查询选修了3号课程且成绩最高的学生的学号及其成绩。

SELECT Sno, Grade FROM SC WHERE Cno=’3’ ORDER BY Grade DESC limit 1;

SELECT Sno, Grade FROM SC WHERE Cno=’3’ ORDER BY Grade DESC limit 0,1;

mysql> select Sno,Grade from SC where Cno='3' order by grade desc limit 1;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95001 |    91 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select Sno,Grade from SC where Cno='3' order by grade desc limit 0,1;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95001 |    91 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select Sno,Grade from SC where Cno='3' order by grade desc limit 1,1;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95005 |    90 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select Sno,Grade from SC where Cno='3' order by grade desc limit 2,1;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95002 |    80 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select Sno,Grade from SC where Cno='3' order by grade desc limit 1,2;
+-------+-------+
| Sno   | Grade |
+-------+-------+
| 95005 |    90 |
| 95002 |    80 |
+-------+-------+
2 rows in set (0.00 sec)

limit A，B 代表从第A行开始数，一共数B行（如果要取第一行，那么就要从第零行开始取一行）

多属性排序

[例25] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT *
FROM Student
ORDER BY Sdept，Sage DESC；

mysql> select * from student order by Sdept,Sage DESC;
+-------+--------------+------+------+-------+
| Sno   | Sname        | Ssex | Sage | Sdept |
+-------+--------------+------+------+-------+
| 95001 | 张三         | 男   |   21 | CS    |
| 95004 | 马六         | 女   |   20 | CS    |
| 95011 | 张成民       | 男   |   19 | CS    |
| 95002 | 李四         | 男   |   23 | IS    |
| 95007 | 刘三姐       | 女   |   23 | IS    |
| 95005 | 苏三         | 女   |   20 | IS    |
| 95006 | 刘七         | 女   |   19 | IS    |
| 95010 | 陈东         | 男   |   19 | IS    |
| 95008 | 欧阳锋       | 男   |   24 | MA    |
| 95009 | 欧阳大侠     | 男   |   23 | MA    |
| 95003 | 王五         | 男   |   19 | MA    |
+-------+--------------+------+------+-------+
11 rows in set (0.00 sec)

先对系进行字母升序(默认) 之后在对每一个系的进行年龄降序

5.使用集函数

5类主要集函数
计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）
计算平均值
AVG（[DISTINCT|ALL] <列名>）

求最大值
MAX（[DISTINCT|ALL] <列名>）
```
求最小值
```
MIN（[DISTINCT|ALL] <列名>）
DISTINCT短语：在计算时要取消指定列中的重复值
ALL短语：不取消重复值
ALL为缺省值

[例26] 查询学生总人数。

SELECT COUNT(*)
FROM  Student；

mysql> select COUNT(*) from Student;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

[例27] 查询选修了课程的学生人数。

 SELECT COUNT(DISTINCT Sno)
 FROM SC；

注：用DISTINCT以避免重复计算学生人数

mysql> select COUNT(DISTINCT Sno) from SC;
+---------------------+
| COUNT(DISTINCT Sno) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)

[例28] 计算1号课程的学生平均成绩。

SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 '；

mysql> select AVG(Grade) from SC where Cno = '1';
+------------+
| AVG(Grade) |
+------------+
|    91.5000 |
+------------+
1 row in set (0.00 sec)

[例29] 查询选修1号课程的学生最高分数。

 SELECT MAX(Grade)
 FROM SC
 WHERE Cno= ' 1 '；

mysql> select MAX(Grade) from sc where Cno = '1';
+------------+
| MAX(Grade) |
+------------+
|         93 |
+------------+
1 row in set (0.00 sec)

6.对查询结果进行分组

使用GROUP BY子句分组
细化集函数的作用对象
未对查询结果分组，集函数将作用于整个查询结果
对查询结果分组后，集函数将分别作用于每个组

[例30] 求选课表中各个被选课程号及相应的选课人数。

SELECT Cno，COUNT(Sno)
FROM    SC
GROUP BY Cno；

mysql> select Cno,COUNT(Sno) from SC GROUP BY Cno;
+-----+------------+
| Cno | COUNT(Sno) |
+-----+------------+
| 1   |          3 |
| 2   |          4 |
| 3   |          4 |
| 4   |          3 |
+-----+------------+
4 rows in set (0.00 sec)

先对Cno进行分组，一样的分为一组，在对每一组中的Sno进行计数，得出选课人数

GROUP BY子句的作用对象是查询的中间结果表
分组方法：按指定的一列或多列值分组，值相等的为一组。
一般要与集函数配合使用，以细化集函数（count，sum，avg，max，min）的作用对象。
使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

使用HAVING短语筛选最终输出结果

[例31] 查询选修了3门以上课程的学生学号。

 SELECT Sno
 FROM  SC
 GROUP BY Sno
 HAVING  COUNT(*) >=3；

mysql> select Sno from sc GROUP BY Sno HAVING COUNT(*) >=3;
+-------+
| Sno   |
+-------+
| 95001 |
| 95005 |
+-------+
2 rows in set (0.00 sec)

[例32] 查询有3门以上课程是90分以上的

学生的学号及（90分以上的）课程数

SELECT Sno, COUNT(*)
FROM SC
WHERE Grade>=90
GROUP BY Sno
HAVING COUNT(*)>=3;

mysql> select Sno,count(*) from SC where Grade>=90 GROUP BY Sno HAVING COUNT(*)>=3;
+-------+----------+
| Sno   | count(*) |
+-------+----------+
| 95001 |        4 |
| 95005 |        3 |
+-------+----------+
2 rows in set (0.00 sec)

先把成绩>=90分以上的元组选出来，按照学号分组，之后在每个组进行筛选，选出个数大于等于3的组。

只有满足HAVING短语指定条件的组才输出
HAVING短语与WHERE子句的区别：作用对象不同
WHERE子句作用于基表，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组。
查询语句的select 和group by ,having 子句是集函数唯一出现的地方，在where 子句中不能使用集函数
在没有集函数的情况下，DISTINCT和GROUP BY的结果没有区别

SELECT Sno FROM SC GROUP BY Sno;

SELECT DISTINCT Sno FROM SC;
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select Sno from Sc GROUP BY Sno;
+-------+
| Sno |
+-------+
| 95001 |
| 95002 |
| 95003 |
| 95004 |
| 95005 |
| 95011 |
+-------+
6 rows in set (0.00 sec)

用了group by，则select之后的字段除了集函数外都必须出现在group by中，你可以少于group by中的字段，但不能包含group by中没有的字段

SELECT Sno,Cno FROM SC GROUP BY Sno;

mysql> select Sno,Cno from sc GROUP BY Sno;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains no
naggregated column 'school.sc.Cno' which is not functionally dependent on columns in GROUP
 BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT Sno,count(Cno) FROM SC GROUP BY Sno;

mysql> select Sno,count(Cno) from sc GROUP BY Sno;
+-------+------------+
| Sno   | count(Cno) |
+-------+------------+
| 95001 |          4 |
| 95002 |          2 |
| 95003 |          1 |
| 95004 |          2 |
| 95005 |          4 |
| 95011 |          1 |
+-------+------------+
6 rows in set (0.00 sec)

GROUP BY 对多个字段的查询

SELECT Cno,Sno FROM SC GROUP BY Cno,Sno;

SELECT Sno,Cno FROM SC GROUP BY Sno,Cno;

mysql> select Cno,Sno from sc group by Cno,Sno;
+-----+-------+
| Cno | Sno   |
+-----+-------+
| 1   | 95001 |
| 1   | 95005 |
| 1   | 95011 |
| 2   | 95001 |
| 2   | 95002 |
| 2   | 95003 |
| 2   | 95005 |
| 3   | 95001 |
| 3   | 95002 |
| 3   | 95004 |
| 3   | 95005 |
| 4   | 95001 |
| 4   | 95004 |
| 4   | 95005 |
+-----+-------+
14 rows in set (0.00 sec)

mysql> select Sno,Cno from sc group by Sno,Cno;
+-------+-----+
| Sno   | Cno |
+-------+-----+
| 95001 | 1   |
| 95001 | 2   |
| 95001 | 3   |
| 95001 | 4   |
| 95002 | 2   |
| 95002 | 3   |
| 95003 | 2   |
| 95004 | 3   |
| 95004 | 4   |
| 95005 | 1   |
| 95005 | 2   |
| 95005 | 3   |
| 95005 | 4   |
| 95011 | 1   |
+-------+-----+
14 rows in set (0.00 sec)

查询每个系男生和女生的平均年龄
select sdept,ssex,avg(sage) from student group by sdept,ssex;

mysql> select Sdept,Ssex,avg(sage) from student group by Sdept,Ssex;
+-------+------+-----------+
| Sdept | Ssex | avg(sage) |
+-------+------+-----------+
| CS    | 女   |   20.0000 |
| CS    | 男   |   20.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| MA    | 男   |   22.0000 |
+-------+------+-----------+
5 rows in set (0.00 sec)

GROUP BY 和 ORDER BY 同时使用

group by 比order by先执行，order by不会对group by 内部进行排序，而是对组进行排序，所以ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中。

如果group by后只有一条记录，那么order by 将无效

查询每个系男生和女生的平均年龄，按照所在系的降序排列
select sdept, ssex, avg(sage) from student group by sdept, ssex order by sdept desc;

mysql> select Sdept,Ssex,AVG(Sage) from student group by Sdept,Ssex order by Sdept desc;
+-------+------+-----------+
| Sdept | Ssex | AVG(Sage) |
+-------+------+-----------+
| MA    | 男   |   22.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| CS    | 女   |   20.0000 |
| CS    | 男   |   20.0000 |
+-------+------+-----------+
5 rows in set (0.00 sec)

```shell
mysql> select sdept, ssex, avg(sage) from student group by sdept, ssex having avg(sage)>19
order by sdept desc, ssex desc;
+———-+———+—————-+
| sdept | ssex | avg(sage) |
+———-+———+—————-+
| MA | 男 | 22.0000 |
| IS | 男 | 21.0000 |
| IS | 女 | 20.6667 |
| CS | 男 | 20.0000 |
| CS | 女 | 20.0000 |
+———-+———+—————-+
5 rows in set (0.00 sec)


- ```shell
  mysql> select sdept, ssex, avg(sage) from student group by sdept, ssex order by AVG(Sage) asc;
  +-------+------+-----------+
  | sdept | ssex | avg(sage) |
  +-------+------+-----------+
  | CS    | 女   |   20.0000 |
  | CS    | 男   |   20.0000 |
  | IS    | 女   |   20.6667 |
  | IS    | 男   |   21.0000 |
  | MA    | 男   |   22.0000 |
  +-------+------+-----------+
  5 rows in set (0.00 sec)

mysql> select sdept, ssex, avg(sage) from student group by sdept, ssex having avg(sage)>19
 order by sdept desc, AVG(Sage) asc;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| MA    | 男   |   22.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| CS    | 男   |   20.0000 |
| CS    | 女   |   20.0000 |
+-------+------+-----------+
5 rows in set (0.00 sec)

7.结尾

本篇笔记针对单表查询做了很多详细的例子，基本涵盖了所有的单表查询操作，后面一篇将分享连接查询表，如果觉得对你有帮助，请点个赞鼓励我更上一层楼！