一、单表查询1、一般查询。2、聚合函数、排序 3、别名。4、分组。5、分组过滤。6、限制显示条目。7、杂项。二、多表查询1、联结查询。2、子查询。3、联合查询。数据库版本：5.5.46-MariaDB说明一下这几张表，这是在上马哥课程的时候给的生成表的sql备份文件。在文章最后我把它放到附件中。注意：在linux上表名是区分大小写的。如果搞不清语句顺序请看：help select一、单表查询1、一般查询MariaDB [hellodb]> SELECT * FROM students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.05 sec)MariaDB [hellodb]> SELECT StuID,Name,Age FROM students WHERE Age > 25;+-------+--------------+-----+| StuID | Name | Age |+-------+--------------+-----+| 3 | Xie Yanke | 53 || 4 | Ding Dian | 32 || 5 | Yu Yutong | 26 || 6 | Shi Qing | 46 || 13 | Tian Boguang | 33 || 24 | Xu Xian | 27 || 25 | Sun Dasheng | 100 |+-------+--------------+-----+7 rows in set (0.02 sec)SELECT中的WHERE子句就是一个布尔条件表达式，来判断行是否区配表达式。只要返回的为真，也就是不为0，则WHERE子句就为真，就会显示匹配的行。布尔条件表达式操作符：= 等于，用于数值或字符都可以。<=> 也是等值比较，不过不会跟空产生意外情况。是跟空值比较的安全方式。<> 不等于，这个就只能用于数值了。< <= > >= 空字符跟空是不一样的。 空字符也是一种字符串，也是有自己的ASCII码和值的。 IS NULL 判断是否为空IS NOT NULL 判断是否为不空LIKE 模糊匹配，支持通配符，% 百分号表示任意个任意字符。_ 下划线任意单个字符。在能用等值比较或不等值比较的情况下不要用LIKE，性能差的多。RLIKE，REGEXP 支持使用正则表达式。性能更低。LIKE, RLIKE只能用来做字符的比较。也可以完整的匹配数值，不过也没有意义。IN判断指定的字段的值是否在给定的列表中， IN （‘abc','cc')BETWEEN AND 判断指定的字段是否在给定的范围之间。如 x>=20 AND x<=40 这种，可以用 X BETWEEN 20 AND 40 组合条件： NOT ，!AND ,，&&OR，|| 注意： 在mysql中只要是字符型的在使用的时候都要加引号，而如果是数值型的，一定不能加引号。例1：IS NULL， 判断ClassID字段为空的记录。只显示Name,Age,ClassID.MariaDB [hellodb]> SELECT Name,Age,ClassID FROM students WHERE ClassID IS NULL;+-------------+-----+---------+| Name | Age | ClassID |+-------------+-----+---------+| Xu Xian | 27 | NULL || Sun Dasheng | 100 | NULL |+-------------+-----+---------+2 rows in set (0.00 sec)例2：用LIKE来模糊匹配Name字段所有以X开头的行。%通配任意个任意字符。MariaDB [hellodb]> SELECT Name FROM students WHERE Name LIKE 'X%';+-------------+| Name |+-------------+| Xie Yanke || Xi Ren || Xu Zhu || Xue Baochai || Xiao Qiao || Xu Xian |+-------------+6 rows in set (0.00 sec)下面的效果跟上面的相同。这里是用正则表达式匹配的。1MariaDB [hellodb]> SELECT Name FROM students WHERE Name RLIKE '^X.*';例3：IN。下面是查找ClassID是1或3或5的记录。只显示Name和ClassID字段。MariaDB [hellodb]> SELECT Name,ClassID FROM students WHERE ClassID IN (1,3,5);+--------------+---------+| Name | ClassID |+--------------+---------+| Shi Potian | 1 || Yu Yutong | 3 || Shi Qing | 5 || Xi Ren | 3 || Yue Lingshan | 3 || Wen Qingqing | 1 || Lu Wushuang | 3 || Xu Zhu | 1 || Xiao Qiao | 1 |+--------------+---------+9 rows in set (0.00 sec)MariaDB [hellodb]> 例4：BETWEEN AND ，匹配一个范围。年龄在30到50之间。MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age BETWEEN 30 AND 50;+--------------+-----+| Name | Age |+--------------+-----+| Ding Dian | 32 || Shi Qing | 46 || Tian Boguang | 33 |+--------------+-----+3 rows in set (0.00 sec)MariaDB [hellodb]>例5：组合AND，gender为m，并且，Age大于30或等于20。这个括号是一定要有的，不然就变成“gender为M并且Age大于30，或者Age等于20。MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20);+-------+--------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+| 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 6 | Shi Qing | 46 | M | 5 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+--------------+-----+--------+---------+-----------+5 rows in set (0.01 sec)想以年龄排序。可以用ORDER BY [ASC|DESC|字段]ASC表示升序， DESC表示降序。 默认是ASC以年龄降序排列。MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20) ORDER BY Age DESC;+-------+--------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+| 25 | Sun Dasheng | 100 | M | NULL | NULL || 3 | Xie Yanke | 53 | M | 2 | 16 || 6 | Shi Qing | 46 | M | 5 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 4 | Ding Dian | 32 | M | 4 | 4 |+-------+--------------+-----+--------+---------+-----------+5 rows in set (0.01 sec)MariaDB [hellodb]>这些只是一般的查询，如果要统计数据，就要用聚合函数了。2、聚合函数。话说想统计下一共有多少人，或者女的有多少，男的有多少，平均年龄，最小最大年龄。SUM(), AVG(), MAX(), MIN(), COUNT()分别是求和、平均值、最大、最小、统计个数。这几个是常用到的。例6：SUM()，全体同学年龄总和。可以用WHERE加上条件，如男同学的年龄总和。MariaDB [hellodb]> SELECT SUM(Age) FROM students;+----------+| SUM(Age) |+----------+| 685 |+----------+1 row in set (0.00 sec)也可以显示其它字段，不过也只是一行。例7：AVG()，全体同学的年龄平均值。MariaDB [hellodb]> SELECT AVG(Age) FROM students;+----------+| AVG(Age) |+----------+| 27.4000 |+----------+1 row in set (0.00 sec)例8：COUNT(), 统计一共多少学生。COUNT后面有的会使用*。COUNT(*)，这样也可以，不过性能差点。MariaDB [hellodb]> SELECT COUNT(Name) FROM students;+-------------+| COUNT(Name) |+-------------+| 25 |+-------------+1 row in set (0.00 sec)我们也可以不让它显示上面的字段名称，给它换一个名称。3、AS 别名。MariaDB [hellodb]> SELECT COUNT(Name) AS CC FROM students;+----+| CC |+----+| 25 |+----+1 row in set (0.00 sec)还有表也可以有别名，在多表查询的时候再来说说。下面男同学的最小年龄，并用别名显示。MariaDB [hellodb]> SELECT MIN(Age) AS Min_M FROM students WHERE gender = 'M';+-------+| Min_M |+-------+| 19 |+-------+1 row in set (0.00 sec)可不可以一次性男女分开显示各自的最小年龄。那就要用分组了。可以按性别gender来分组。这样函数就会分别计算各组的数据。4、分组。GROUP BY 字段名以字段的值分组。同一个值一个组。然后再通过用聚合函数来统计不同组中的信息。现在以gender分组，也就是两组。函数分别计算两个组。不过下面这个有点缺陷，不知道哪是女的，哪个是男的。MariaDB [hellodb]> SELECT MIN(Age) FROM students GROUP BY gender;+----------+| MIN(Age) |+----------+| 17 || 19 |+----------+2 rows in set (0.00 sec)MariaDB [hellodb]>下面再显示出来性别字段。MariaDB [hellodb]> SELECT MIN(Age),gender FROM students GROUP BY gender;+----------+--------+| MIN(Age) | gender |+----------+--------+| 17 | F || 19 | M |+----------+--------+2 rows in set (0.00 sec)MariaDB [hellodb]>例：显示不同班级的学生个数MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students GROUP BY classID;+-------------+---------+| Count(Name) | ClassID |+-------------+---------+| 2 | NULL || 4 | 1 || 3 | 2 || 4 | 3 || 4 | 4 || 1 | 5 || 4 | 6 || 3 | 7 |+-------------+---------+8 rows in set (0.00 sec)MariaDB [hellodb]>不显示没有班级的。WHERE在分组之前先进行过滤，然后把数据再给GROUP BY来进行分组。MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY classID;+-------------+---------+| Count(Name) | ClassID |+-------------+---------+| 4 | 1 || 3 | 2 || 4 | 3 || 4 | 4 || 1 | 5 || 4 | 6 || 3 | 7 |+-------------+---------+7 rows in set (0.00 sec)MariaDB [hellodb]>例9：各个班级的平均年龄。MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;+----------+---------+| AVG(age) | ClassID |+----------+---------+| 63.5000 | NULL || 20.5000 | 1 || 36.0000 | 2 || 20.2500 | 3 || 24.7500 | 4 || 46.0000 | 5 || 20.7500 | 6 || 19.6667 | 7 |+----------+---------+8 rows in set (0.00 sec)MariaDB [hellodb]>加上排序呢：MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID ORDER BY AVG(age);+----------+---------+| AVG(age) | ClassID |+----------+---------+| 19.6667 | 7 || 20.2500 | 3 || 20.5000 | 1 || 20.7500 | 6 || 24.7500 | 4 || 36.0000 | 2 || 46.0000 | 5 || 63.5000 | NULL |+----------+---------+8 rows in set (0.00 sec)意思就是在分组之后，把各个分组重新排序了。以各个组的age字段的平均值来排序。回来看分组：如果不想显示平均年龄小于等于25的，怎么办呢。5、分组过滤。HAVING 用于对分组做条件过滤。普及：WHERE是对表中的每一行做过滤，单位是行。 而HAVING是对每一个组做过滤，单位是组。如：MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID HAVING AVG(age)>25;+----------+---------+| AVG(age) | ClassID |+----------+---------+| 63.5000 | NULL || 36.0000 | 2 || 46.0000 | 5 |+----------+---------+3 rows in set (0.00 sec)MariaDB [hellodb]>HAVING拿到手的都是一组一组的数据，所以也要求下平均值。然后不匹配的组，就刷掉。到了SELECT那里，它求一下平均值是为了显示。这是两个不同的部分。如果想找age小于AVG(age)之类的结果，这里是查不出来的。在子查询部分。例10：显示最少有3个同学的班级和该班级的人数。MariaDB [hellodb]> SELECT ClassID,Count(Name) FROM students GROUP BY ClassID HAVING Count(Name) >= 3;+---------+-------------+| ClassID | Count(Name) |+---------+-------------+| 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 6 | 4 || 7 | 3 |+---------+-------------+6 rows in set (0.00 sec)MariaDB [hellodb]>这个表小，这样显示还可以，但是如果有上千上万个的条目，一下子显示出来就有点夸张了，占网络带宽不说，一下子出来这么多，也看不完啊。6、限制显示条目的数量。LIMIT只显示3行。在最后加上limit 3就可以了。MariaDB [hellodb]> SELECT * FROM students LIMIT 3;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 |+-------+-------------+-----+--------+---------+-----------+3 rows in set (0.00 sec)这是从头开始，显示3行。如果想从中间开始。下面这个是从第5行开始，显示3行。MariaDB [hellodb]> SELECT * FROM students LIMIT 5,3;+-------+-----------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-----------+-----+--------+---------+-----------+| 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL |+-------+-----------+-----+--------+---------+-----------+3 rows in set (0.00 sec)7、杂项。DISTINCT : 指定的结果相同的只显示一次。在SELECT 语句后面。SQL_CACHE : 缓存此条语句至查询缓存中。SQL_NO_CACHE: 说明不缓存此条语句。简单的例子说明下第一个吧。如果显示都有哪些年龄的同学，除了用分组外。还可以用DISTINCT。只不过只能显示一个字段。age字段重复的就不显示，并且排序。 不排序也没有关系。MariaDB [hellodb]> SELECT DISTINCT age FROM students ORDER BY age;+-----+| age |+-----+| 17 || 18 || 19 || 20 || 21 || 22 || 23 || 25 || 26 || 27 || 32 || 33 || 46 || 53 || 100 |+-----+15 rows in set (0.00 sec)到这里单表查询就完了。我们来看看这么多语句它的执行流程。SELECT语句的执行流程：FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --> LIMIT首先是FROM获取表数据，然后WHERE筛选，再然后GROUP BY来分组，再然后HAVING给组再来一下过滤，再然后就是ORDER BY给剩下的组或是整张表的行排序，再然后才是SELECT把最终整理好的数据计算或者直接显示出来，当然到达客户端还要经过LIMIT限制。二、多表查询。我们知道关系型数据库就是为了降低冗余，所以都是把内容记录到多张表中，我们在查询的时候要把多张表连起来才能查到所有数据。说明一下，因为表的内容都贴出来的话就太多了，所以这里就只举例子了，具体的表内容，朋友们自己下载看吧。MariaDB [hellodb]> SHOW TABLES;+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || scores || students || teachers || toc |+-------------------+7 rows in set (0.00 sec)1、联结查询联结查询： 先将几张表join起来， 然后再根据join以后所产生的表，来进行查询。有：交叉联结、自然联结、外联结、自联结。交叉联结：就是各个表的各字段的值相乘的关系。各种连结，各种交叉。这里也只是提一下。直接FROM表就是了。1MariaDB [hellodb]> SELECT * FROM students,coc,classes;自然联结：又叫内联结或等值联结，两张表要有相同的字段可以建立联结。用WHERE 联结条件。一般情况下都是使用自然联结。举例子先，上面的students表中有学生信息，而classes表中有班级的名称。现在想显示学生的名子和所对应的班级名称。那么就需要student和classes建立结结。正好它们都有classID班级编号。所以：MariaDB [hellodb]> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;+---------------+----------------+| Name | Class |+---------------+----------------+| Shi Zhongyu | Emei Pai || Shi Potian | Shaolin Pai || Xie Yanke | Emei Pai || Ding Dian | Wudang Pai |**23 rows in set (0.00 sec)中间省略了，不然太多。 上面用WHERE来做两个表的等值条件。把字段全部显示出来看看：MariaDB [hellodb]> SELECT * FROM students,classes WHERE students.ClassID = classes.ClassID;+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 || 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 || 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 || 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 || 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 || 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 || 7 | Xi Ren | 19 | F | 3 | NULL | 3 | QingCheng Pai | 11 |ClassID都是相等的。这里还有一个问题就是，在多表连结的时候会有多个字段一样的，所以在写的时候要把表名也给写上，就是这种格式students.ClassID之类的。但是有的表名又很长，这个时候就可以用别名了。在FROM后面的表名后面使用AS。FROM 表名 AS 别名MariaDB [hellodb]> SELECT * FROM students AS STU,classes AS CLA WHERE STU.ClassID = CLA.ClassID;+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 || 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 || 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 || 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 || 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 || 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |多表连结也简单，就是表多了以后会晕乎。所以主要问题就是要熟悉自己的各种表。有内连结，自然就有外连结。内连结把表的字段的数值与另一张表连接起来，但是并不是所有记录都可以连接起来，比如上面的students表中还有两个人没有显示出来，因为他们没有班级。+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+如这两位仁兄。那么如果我也想把这两个显示出来。就要用到外连接了。外联结：外联结又分为“左外连结和右外连结”。其实意思都一样，就是以哪个为主，主表所有的都显示出来，别一张表如果对不上就为NULL。左外联结 以左表为基准，右表没有的为NULL.left_tb LEFT JOIN right_tb ON 连接条件右外联结 以右表为基准，左表没有的为NULL.left_tb RIGHT JOIN right_tb ON 连接条件全外联结 以两个表为基准，哪个没有哪个为NULL. mysql中没有。MariaDB [hellodb]> SELECT * FROM students LEFT JOIN classes ON students.ClassID=classes.ClassID;+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | Ming Jiao | 27 || 19 | Xue Baochai | 18 | F | 6 | NULL | 6 | Lianshan Pai | 27 || 20 | Diao Chan | 19 | F | 7 | NULL | 7 | Ming Jiao | 27 || 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | Lianshan Pai | 27 || 22 | Xiao Qiao | 20 | F | 1 | NULL | 1 | Shaolin Pai | 10 || 23 | Ma Chao | 23 | M | 4 | NULL | 4 | Wudang Pai | 12 || 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+就是这样的了。打个比方，如果classes的表有额外的ClassID,如8，9，10之类的，而students表中没有，那么也是不会显示出来的。因为左外连接是以左表为准，管你右边的表有什么。而右外连接就是以右边的表为准了。当然在写表的时候把classes表写左边也是一样的。这些表还可以作三个表甚至四个表连接的操作。比如加上成绩。大家就自己试试吧。2、子查询：在查询中嵌套的查询。用于WHERE中的子查询1、用于比较表达式中的子查询。子查询的返回值只能有一个2、用于EXISTS中的子查询,判断存在与否。3、用于IN中的子查询,判断存在于指定的列表中。4、用于FROM中的子查询，SELECT * FROM (SELECT clause) AS alias。这里一定要用别名。5、在SELECT中也可以用子语句的值来作为一个字段。先解决查询age>AVG(age)的问题。为什么在上面那里不能用，因为这种写法就是错的。一行还是一组呢。1、放到WHERE后，WHERE语句的数据是一行一行的，age是可以表示当前行的age值。但是AVG(age)就有问题了，它只能放在GROUP BY后面来计算组的平均值，或是SELECT后面全表的平均值。2、放到HAVING后面，同样的问题。是一组数据。如果要查询就要用子查询先计算平均值。查询所有同学年龄大于平均年龄的。MariaDB [hellodb]> SELECT Name,Age FROM students WHERE age > (SELECT AVG(age) FROM students);+--------------+-----+| Name | Age |+--------------+-----+| Xie Yanke | 53 || Ding Dian | 32 || Shi Qing | 46 || Tian Boguang | 33 || Sun Dasheng | 100 |+--------------+-----+5 rows in set (0.00 sec)延伸一下：显示平均年龄：MariaDB [hellodb]> SELECT Name,Age,(SELECT AVG(age) FROM students) AS avg_age FROM students WHERE age > (SELECT AVG(age) FROM students);+--------------+-----+---------+| Name | Age | avg_age |+--------------+-----+---------+| Xie Yanke | 53 | 27.4000 || Ding Dian | 32 | 27.4000 || Shi Qing | 46 | 27.4000 || Tian Boguang | 33 | 27.4000 || Sun Dasheng | 100 | 27.4000 |+--------------+-----+---------+5 rows in set (0.00 sec)那么再延伸一下，显示在各个班级内同学，大于班级内年龄平均值的。有点复杂，我这里是这样作的。第一步：求出各个班内的平均年龄。MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;+----------+---------+| AVG(age) | ClassID |+----------+---------+| 63.5000 | NULL || 20.5000 | 1 || 36.0000 | 2 || 20.2500 | 3 || 24.7500 | 4 || 46.0000 | 5 || 20.7500 | 6 || 19.6667 | 7 |+----------+---------+8 rows in set (0.00 sec)第二步：以上面这个结果与students表建立连接。MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age),ClassID FROM students GROUP BY ClassID) AS avg_age WHERE students.ClassID=avg_age.ClassID;+-------+---------------+-----+--------+---------+-----------+----------+---------+| StuID | Name | Age | Gender | ClassID | TeacherID | AVG(age) | ClassID |+-------+---------------+-----+--------+---------+-----------+----------+---------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 36.0000 | 2 || 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 || 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 || 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 || 6 | Shi Qing | 46 | M | 5 | NULL | 46.0000 | 5 || 7 | Xi Ren | 19 | F | 3 | NULL | 20.2500 | 3 || 8 | Lin Daiyu | 17 | F | 7 | NULL | 19.6667 | 7 || 9 | Ren Yingying | 20 | F | 6 | NULL | 20.7500 | 6 || 10 | Yue Lingshan | 19 | F | 3 | NULL | 20.2500 | 3 || 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 || 12 | Wen Qingqing | 19 | F | 1 | NULL | 20.5000 | 1 || 13 | Tian Boguang | 33 | M | 2 | NULL | 36.0000 | 2 || 14 | Lu Wushuang | 17 | F | 3 | NULL | 20.2500 | 3 || 15 | Duan Yu | 19 | M | 4 | NULL | 24.7500 | 4 || 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 || 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 || 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 || 19 | Xue Baochai | 18 | F | 6 | NULL | 20.7500 | 6 || 20 | Diao Chan | 19 | F | 7 | NULL | 19.6667 | 7 || 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 || 22 | Xiao Qiao | 20 | F | 1 | NULL | 20.5000 | 1 || 23 | Ma Chao | 23 | M | 4 | NULL | 24.7500 | 4 |+-------+---------------+-----+--------+---------+-----------+----------+---------+23 rows in set (0.00 sec)第三步：这里就直接作判断就可以了。MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age) AS avg_age_col,ClassID FROM students GROUP BY ClassID) AS avg_age_tab WHERE students.ClassID=avg_age_tab.ClassID AND Age > avg_age_col ORDER BY students.ClassID;+-------+---------------+-----+--------+---------+-----------+-------------+---------+| StuID | Name | Age | Gender | ClassID | TeacherID | avg_age_col | ClassID |+-------+---------------+-----+--------+---------+-----------+-------------+---------+| 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 || 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 || 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 || 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 || 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 || 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 || 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 || 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 || 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 |+-------+---------------+-----+--------+---------+-----------+-------------+---------+9 rows in set (0.00 sec)MariaDB [hellodb]>再来一个：要疯了。这个我这样写总觉得有点复杂了。不知道大家有没有简略点的。如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？MariaDB [hellodb]> SELECT SQL_NO_CACHE * FROM (SELECT AVG(age) AS A,ClassID FROM students WHERE ClassID IN (SELEct ClassID FROM students GROUP BY ClassID HAVING COUNT(*) >= 3) GROUP BY ClassID) AS s,students WHERE students.ClassID=s.ClassID AND age > A;+---------+---------+-------+---------------+-----+--------+---------+-----------+| A | ClassID | StuID | Name | Age | Gender | ClassID | TeacherID |+---------+---------+-------+---------------+-----+--------+---------+-----------+| 20.5000 | 1 | 2 | Shi Potian | 22 | M | 1 | 7 || 36.0000 | 2 | 3 | Xie Yanke | 53 | M | 2 | 16 || 24.7500 | 4 | 4 | Ding Dian | 32 | M | 4 | 4 || 20.2500 | 3 | 5 | Yu Yutong | 26 | M | 3 | 1 || 20.7500 | 6 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 20.5000 | 1 | 16 | Xu Zhu | 21 | M | 1 | NULL || 24.7500 | 4 | 17 | Lin Chong | 25 | M | 4 | NULL || 19.6667 | 7 | 18 | Hua Rong | 23 | M | 7 | NULL || 20.7500 | 6 | 21 | Huang Yueying | 22 | F | 6 | NULL |+---------+---------+-------+---------------+-----+--------+---------+-----------+9 rows in set (0.00 sec)MariaDB [hellodb]>有人说mysql中对子查询的优化不好，所以子查询也要少用。3、联合查询：把两个或多个查询语句的结果合并起来。UNION这个简单，就是一个结果附加在了另一个结果的下面。叠加起来了。SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;把后面的语句结果连接在前面结果的下面。UNION 可以有多个，可以连接多个查询结果。各个查询结果的字段数要相同。