mysql select 查询

大番茄 2015年11月03日 321次浏览

以前在51cto上写的,51cto不显示2016年以前的文章了,就放到这里以备以后查看。
而且可能是因为以前对一些东西不熟悉, 或者是版本问题。 总之发现了一些错误。


SELECT语句的执行流程:

FROM --> WHERE  --> GROUP BY --> HAVING  --> ORDER BY --> SELECT --> LIMIT

WHERE,是一行一行的过滤。 HAVING是一组一组的过滤。

首先是FROM获取表数据,然后WHERE筛选,再然后GROUP BY来分组,再然后HAVING给组再来一下过滤,再然后就是ORDER BY给剩下的组或是整张表的行排序,再然后才是SELECT把最终整理好的数据计算或者提取,当然到达客户端还要经过LIMIT限制。


一、单表查询

1、一般查询。2、聚合函数、排序 3、别名。4、分组。5、分组过滤。6、限制显示条目。7、杂项。

二、多表查询

1、联结查询。2、子查询。3、联合查询。


数据库版本:5.5.46-MariaDB


这几张表,是几年前在看马哥视频的时候给的sql文件。好像是下面这个文件。

test.sql


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子句就为真,就会显示匹配的行。

布尔条件表达式操作符:
=	等于,用于数值或字符都可以。
<=>	也是等值比较,不过可以等于null。
<>	不等于。
!=      不等于, 一般都用这个。
<	
<=	
>	
>=	

空字符跟空是不一样的。 空字符也是一种字符串,也是有自己的ASCII码和值的。

IS NULL	判断是否为空
IS NOT NULL	判断是否为不空
模糊匹配

LIKE
支持通配符,% 百分号表示任意个任意字符。_ 下划线任意单个字符。在能用等值比较或不等值比较的情况下不要用LIKE,性能差的多。

RLIKEREGEXP
支持使用正则表达式。性能更低。LIKE, RLIKE 一般用来做字符的比较。
也可以匹配数值,不过一般不会这样用。

范围匹配

IN
判断指定的字段的值是否在给定的列表中, IN (‘abc','cc')

BETWEEN AND
判断指定的字段是否在给定的范围之间。

如 x>=20 AND x<=40

X BETWEEN 20 AND 40  
组合条件
NOT ,!
AND ,,&&
OR,||          

注意: 在mysql中只要是字符型的在使用的时候都要加引号,而如果是数值型的,可加可不加。


例子

例1IS 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)

下面的效果跟上面的相同。这里是用正则表达式匹配的。

MariaDB [hellodb]> SELECT Name FROM students WHERE Name RLIKE '^X.*';

例3IN。下面是查找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]>

例4BETWEEN 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()

分别是求和、平均值、最大、最小、统计个数。这几个是常用到的。


例6SUM(),全体同学年龄总和。可以用WHERE加上条件,如男同学的年龄总和。

MariaDB [hellodb]> SELECT SUM(Age) FROM students;
+----------+
| SUM(Age) |
+----------+
|      685 |
+----------+
1 row in set (0.00 sec)

也可以显示其它字段,不过也只是一行。


例7AVG(),全体同学的年龄平均值。

MariaDB [hellodb]> SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)


例8COUNT(), 统计一共多少学生。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表就是了。

MariaDB [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 * 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 可以有多个,可以连接多个查询结果。
各个查询结果的字段数要相同。

后加的:

EXPLAIN 可以有帮于查看语句执行的状态。如:是否使用索引键查询。

MariaDB [hellodb]> EXPLAIN SELECT * FROM students\G