多表联合查询

最新推荐文章于 2024-05-30 14:59:09 发布

xialulumm

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量134

点赞数

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/xialulumm/article/details/128892545

版权

交叉连接

语法

#SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
#SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
字段名：需要查询的字段名称。 
<表1><表2>：需要交叉连接的表名。 
WHERE 子句：用来设置交叉连接的查询条件

创建school库创建tb_students_info表，插入数据

mysql> create database school;
Query OK, 1 row affected (0.01 sec)
 
mysql> use school;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(50),age tinyint,sex varchar(10),height int,course_id int);
Query OK, 0 rows affected (0.01 sec)
mysql> desc tb_students_info;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | YES  |     | NULL    |                |
| age       | tinyint(4)  | YES  |     | NULL    |                |
| sex       | varchar(10) | YES  |     | NULL    |                |
| height    | int(11)     | YES  |     | NULL    |                |
| course_id | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
 
mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'Male',160,1),('Green',23,'Ma,le',158,2),('Henry',23,'Female',185,1),('Jane',22,'Male',162,3),('Jim',24,'Female',175,2),('John',21,'Male',)172,4),('Lily',22,'Male',165,4),('Susan',23,'Male',170,5),('Thomas',22,'Female',178,5),('Tom',23,'Female',165,5);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
+----+--------+------+--------+--------+-----------+
10 rows in set (0.00 sec)
 
mysql>

创建tb_course表

mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50));
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert tb_course(course_name) values('Java'),('Mysql'),('Python'),('Go'),('C++');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> select * from tb_crouse;
ERROR 1146 (42S02): Table 'school.tb_crouse' doesn't exist
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

不使用where子句查询出两张表中的笛卡尔积，返回5*10=50条记录，因为无用信息过多，不常见这种使用方法

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
+----+-------------+----+--------+------+--------+--------+-----------+
| id | course_name | id | name   | age  | sex    | height | course_id |
+----+-------------+----+--------+------+--------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Mysql       |  1 | Dany   |   25 | Male   |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | Male   |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | Male   |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | Male   |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | Male   |    158 |         2 |
|  2 | Mysql       |  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | Male   |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | Male   |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | Male   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | Female |    185 |         1 |
|  2 | Mysql       |  3 | Henry  |   23 | Female |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | Female |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | Female |    185 |         1 |
|  1 | Java        |  4 | Jane   |   22 | Male   |    162 |         3 |
|  2 | Mysql       |  4 | Jane   |   22 | Male   |    162 |         3 |
|  3 | Python      |  4 | Jane   |   22 | Male   |    162 |         3 |
|  4 | Go          |  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | C++         |  4 | Jane   |   22 | Male   |    162 |         3 |
|  1 | Java        |  5 | Jim    |   24 | Female |    175 |         2 |
|  2 | Mysql       |  5 | Jim    |   24 | Female |    175 |         2 |
|  3 | Python      |  5 | Jim    |   24 | Female |    175 |         2 |
|  4 | Go          |  5 | Jim    |   24 | Female |    175 |         2 |
|  5 | C++         |  5 | Jim    |   24 | Female |    175 |         2 |
|  1 | Java        |  6 | John   |   21 | Male   |    172 |         4 |
|  2 | Mysql       |  6 | John   |   21 | Male   |    172 |         4 |
|  3 | Python      |  6 | John   |   21 | Male   |    172 |         4 |
|  4 | Go          |  6 | John   |   21 | Male   |    172 |         4 |
|  5 | C++         |  6 | John   |   21 | Male   |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | Male   |    165 |         4 |
|  2 | Mysql       |  7 | Lily   |   22 | Male   |    165 |         4 |
|  3 | Python      |  7 | Lily   |   22 | Male   |    165 |         4 |
|  4 | Go          |  7 | Lily   |   22 | Male   |    165 |         4 |
|  5 | C++         |  7 | Lily   |   22 | Male   |    165 |         4 |
|  1 | Java        |  8 | Susan  |   23 | Male   |    170 |         5 |
|  2 | Mysql       |  8 | Susan  |   23 | Male   |    170 |         5 |
|  3 | Python      |  8 | Susan  |   23 | Male   |    170 |         5 |
|  4 | Go          |  8 | Susan  |   23 | Male   |    170 |         5 |
|  5 | C++         |  8 | Susan  |   23 | Male   |    170 |         5 |
|  1 | Java        |  9 | Thomas |   22 | Female |    178 |         5 |
|  2 | Mysql       |  9 | Thomas |   22 | Female |    178 |         5 |
|  3 | Python      |  9 | Thomas |   22 | Female |    178 |         5 |
|  4 | Go          |  9 | Thomas |   22 | Female |    178 |         5 |
|  5 | C++         |  9 | Thomas |   22 | Female |    178 |         5 |
|  1 | Java        | 10 | Tom    |   23 | Female |    165 |         5 |
|  2 | Mysql       | 10 | Tom    |   23 | Female |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | Female |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | Female |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | Female |    165 |         5 |
+----+-------------+----+--------+------+--------+--------+-----------+
50 rows in set (0.01 sec)

使用where子句查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容

mysql> select * from tb_course cross join tb_students_info where tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+--------+--------+-----------+
| id | course_name | id | name   | age  | sex    | height | course_id |
+----+-------------+----+--------+------+--------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Mysql       |  2 | Green  |   23 | Male   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | Female |    185 |         1 |
|  3 | Python      |  4 | Jane   |   22 | Male   |    162 |         3 |
|  2 | Mysql       |  5 | Jim    |   24 | Female |    175 |         2 |
|  4 | Go          |  6 | John   |   21 | Male   |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | Male   |    165 |         4 |
|  5 | C++         |  8 | Susan  |   23 | Male   |    170 |         5 |
|  5 | C++         |  9 | Thomas |   22 | Female |    178 |         5 |
|  5 | C++         | 10 | Tom    |   23 | Female |    165 |         5 |
+----+-------------+----+--------+------+--------+--------+-----------+
10 rows in set (0.00 sec)

使用order by以course表的id排序

mysql> select * from tb_students_info cross join tb_course where tb_course.id = tb_students_info.course_id order by tb_course.id;
+----+--------+------+--------+--------+-----------+----+-------------+
| id | name   | age  | sex    | height | course_id | id | course_name |
+----+--------+------+--------+--------+-----------+----+-------------+
|  1 | Dany   |   25 | Male   |    160 |         1 |  1 | Java        |
|  3 | Henry  |   23 | Female |    185 |         1 |  1 | Java        |
|  5 | Jim    |   24 | Female |    175 |         2 |  2 | Mysql       |
|  2 | Green  |   23 | Male   |    158 |         2 |  2 | Mysql       |
|  4 | Jane   |   22 | Male   |    162 |         3 |  3 | Python      |
|  7 | Lily   |   22 | Male   |    165 |         4 |  4 | Go          |
|  6 | John   |   21 | Male   |    172 |         4 |  4 | Go          |
|  9 | Thomas |   22 | Female |    178 |         5 |  5 | C++         |
|  8 | Susan  |   23 | Male   |    170 |         5 |  5 | C++         |
| 10 | Tom    |   23 | Female |    165 |         5 |  5 | C++         |
+----+--------+------+--------+--------+-----------+----+-------------+
10 rows in set (0.00 sec)

单独列出学习某种语言的人的名单

mysql> select tb_students_info.name from tb_students_info cross join tb_course where tb_course.id = tb_students_info.course_id and tb_course.course_name = 'Go';
+------+
| name |
+------+
| John |
| Lily |
+------+
2 rows in set (0.00 sec)

内连接

#SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
字段名：需要查询的字段名称。 
<表1><表2>：需要内连接的表名。 
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。 
ON 子句：用来设置内连接的连接条件。

取tb_students_info的name和tb_course的course_name 且满足tb_students_info的course_id = tb_course的id (文章后面的s,c分别为两个表的别名)

mysql> select s.name,c.course_name from tb_students_info as s inner join tb_course as c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | Mysql       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | Mysql       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
+--------+-------------+
10 rows in set (0.00 sec)
 
按照course_name来排序
mysql> select s.name,c.course_name from tb_students_info as s inner join tb_course as c on s.course_id = c.id order by c.course_name;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Thomas | C++         |
| Tom    | C++         |
| Susan  | C++         |
| John   | Go          |
| Lily   | Go          |
| Dany   | Java        |
| Henry  | Java        |
| Green  | Mysql       |
| Jim    | Mysql       |
| Jane   | Python      |
+--------+-------------+
10 rows in set (0.00 sec)

外连接

左连接

#SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。 
<表1><表2>：需要左连接的表名。 
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。 
ON 子句：用来设置左连接的连接条件，不能省略。
#"表1"为基表，"表2"为参考表。

在tb_course表插入Html，不赋予Html对应的course_id

mysql> insert tb_course(course_name) value('Html');
Query OK, 1 row affected (0.00 sec)
 
#以tb_students_info表为基础查询tb_students_info表的course_id 匹配 tb_course表的id  没有显示Html，因为Html没有对应的course_id
mysql> select * from tb_students_info left join tb_course on tb_students_info.course_id = tb_course.id;
+----+--------+------+--------+--------+-----------+------+-------------+
| id | name   | age  | sex    | height | course_id | id   | course_name |
+----+--------+------+--------+--------+-----------+------+-------------+
|  1 | Dany   |   25 | Male   |    160 |         1 |    1 | Java        |
|  2 | Green  |   23 | Male   |    158 |         2 |    2 | Mysql       |
|  3 | Henry  |   23 | Female |    185 |         1 |    1 | Java        |
|  4 | Jane   |   22 | Male   |    162 |         3 |    3 | Python      |
|  5 | Jim    |   24 | Female |    175 |         2 |    2 | Mysql       |
|  6 | John   |   21 | Male   |    172 |         4 |    4 | Go          |
|  7 | Lily   |   22 | Male   |    165 |         4 |    4 | Go          |
|  8 | Susan  |   23 | Male   |    170 |         5 |    5 | C++         |
|  9 | Thomas |   22 | Female |    178 |         5 |    5 | C++         |
| 10 | Tom    |   23 | Female |    165 |         5 |    5 | C++         |
+----+--------+------+--------+--------+-----------+------+-------------+
10 rows in set (0.00 sec)

添加Liming，但赋予不存在的course_id和course_name

mysql> insert tb_students_info(name,age,sex,height,course_id) value('Liming',22,'Male',180,7);
Query OK, 1 row affected (0.00 sec)
 
再次以tb_students_info表为基础查询tb_students_info表的course_id 匹配 tb_course表的id 因为没有对应的course_id和course_name,所以会显示null
mysql> select * from tb_students_info left join tb_course on tb_students_info.course_id = tb_course.id;
+----+--------+------+--------+--------+-----------+------+-------------+
| id | name   | age  | sex    | height | course_id | id   | course_name |
+----+--------+------+--------+--------+-----------+------+-------------+
|  1 | Dany   |   25 | Male   |    160 |         1 |    1 | Java        |
|  2 | Green  |   23 | Male   |    158 |         2 |    2 | Mysql       |
|  3 | Henry  |   23 | Female |    185 |         1 |    1 | Java        |
|  4 | Jane   |   22 | Male   |    162 |         3 |    3 | Python      |
|  5 | Jim    |   24 | Female |    175 |         2 |    2 | Mysql       |
|  6 | John   |   21 | Male   |    172 |         4 |    4 | Go          |
|  7 | Lily   |   22 | Male   |    165 |         4 |    4 | Go          |
|  8 | Susan  |   23 | Male   |    170 |         5 |    5 | C++         |
|  9 | Thomas |   22 | Female |    178 |         5 |    5 | C++         |
| 10 | Tom    |   23 | Female |    165 |         5 |    5 | C++         |
| 11 | Liming |   22 | Male   |    180 |         7 | NULL | NULL        |
+----+--------+------+--------+--------+-----------+------+-------------+
11 rows in set (0.00 sec)
 
以s表为基础表查询course_id匹配c表的id c表没有对应值 所有为空null值
mysql> select s.name,c.course_name from tb_students_info s left join tb_course c on s.course_id=c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | Mysql       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | Mysql       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| Liming | NULL        |
+--------+-------------+
11 rows in set (0.00 sec)

右连接

#SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。 
<表1><表2>：需要右连接的表名。 
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。
#与左连接相反，右连接以"表2"为基表，"表1"为参考表。

以c表为基础表查询id匹配s表的course_id c表没有对应值 所有为空null值
mysql> select s.name,c.course_name from tb_students_info s right join tb_course c on s.course_id=c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | Mysql       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | Mysql       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| NULL   | Html        |
+--------+-------------+
11 rows in set (0.00 sec)

分组查询

GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

语法

#GROUP BY  <字段名>
<字段名>表示需要分组的字段名称，多个字段时用逗号隔开

单独使用

#根据 tb_students_info 表中的 sex 字段进行分组查询 只显示两条分为男女，因为只匹配一条，所以男女各匹配一条后结束
mysql> SELECT name,sex FROM tb_students_info GROUP BY sex;
+-------+--------+
| name  | sex    |
+-------+--------+
| Henry | Female |
| Dany  | Male   |
+-------+--------+
2 rows in set (0.00 sec)

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

#把性别sex男，女的名字都列出来
mysql> select sex, group_concat(name) from tb_students_info group by sex;
+--------+----------------------------------------+
| sex    | group_concat(name)                     |
+--------+----------------------------------------+
| Female | Henry,Jim,Thomas,Tom                   |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+----------------------------------------+
2 rows in set (0.00 sec)

根据 tb_students_info 表中的 age 和 sex 字段进行分组查询

mysql> select age,sex,group_concat(name) names from tb_students_info group by age,sex;
+------+--------+------------------+
| age  | sex    | names            |
+------+--------+------------------+
|   21 | Male   | John             |
|   22 | Female | Thomas           |
|   22 | Male   | Jane,Lily,Liming |
|   23 | Female | Henry,Tom        |
|   23 | Male   | Green,Susan      |
|   24 | Female | Jim              |
|   25 | Male   | Dany             |
+------+--------+------------------+
7 rows in set (0.00 sec)

GROUP BY 与聚合函数
在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。

聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。

tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。

<code class="language-plaintext hljs">mysql> select sex,count(sex) from tb_students_info group by sex;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| Female |          4 |
| Male   |          7 |
+--------+------------+
2 rows in set (0.00 sec)
 
mysql> select sex 性别,count(sex)  个数 from tb_students_info group by sex;
+--------+--------+
| 性别   | 个数   |
+--------+--------+
| Female |      4 |
| Male   |      7 |
+--------+--------+
2 rows in set (0.00 sec)
 
mysql></code>

GROUP BY 与 WITH ROLLUP
WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和：

<code class="language-plaintext hljs">mysql> select sex,group_concat(name) name from tb_students_info group by sex with rollup;
+--------+-------------------------------------------------------------+
| sex    | name                                                        |
+--------+-------------------------------------------------------------+
| Female | Henry,Jim,Thomas,Tom                                        |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming                      |
| NULL   | Henry,Jim,Thomas,Tom,Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> select sex,group_concat(name) name from tb_students_info group by sex;
+--------+----------------------------------------+
| sex    | name                                   |
+--------+----------------------------------------+
| Female | Henry,Jim,Thomas,Tom                   |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+----------------------------------------+
2 rows in set (0.00 sec)
 
mysql> </code>

with rollup 统计所有的名单

计算平均年龄

<code class="language-plaintext hljs">mysql> select avg(age) from tb_students_info;
+----------+
| avg(age) |
+----------+
|  22.7273 |
+----------+
1 row in set (0.00 sec)
 
mysql></code>

查询男女的平均年龄

<code class="language-plaintext hljs">mysql> select sex,avg(age) from tb_students_info group by sex;
+--------+----------+
| sex    | avg(age) |
+--------+----------+
| Female |  23.0000 |
| Male   |  22.5714 |
+--------+----------+
2 rows in set (0.00 sec)
 
mysql> </code>

子查询
子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

语法格式：

<codeclass="language-plaintext hljs">WHERE <表达式><操作符> (子查询)</code>

操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

IN ；NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反

EXISTS ；NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名

<code class="language-plaintext hljs">mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | Liming |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql> select id from tb_course where course_name = 'Java';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
 
mysql> select name from tb_students_info where course_id in (select id from tb_course where course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)
 
mysql></code>

括号里的值可以有多个

<code class="language-plaintext hljs">mysql> select id from tb_course where course_name = 'Java' or course_name = 'Go' ;
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.00 sec)
 
mysql>
mysql> select name from tb_students_info where course_id in (select id from tb_course where course_name = 'Java' or course_name = 'Go');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| John  |
| Lily  |
+-------+
4 rows in set (0.00 sec)
 
mysql></code>

在 SELECT 语句中使用 NOT IN 关键字，查询没有学习Java和Go的

<code class="language-plaintext hljs">mysql> select name from tb_students_info where course_id not in (select id from tb_course where course_name ='Java' or course_name = 'Go');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| Susan  |
| Thomas |
| Tom    |
| Liming |
+--------+
7 rows in set (0.00 sec)
 
mysql> </code>

外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询

使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名

<code class="language-plaintext hljs">mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | Html        |
+----+-------------+
6 rows in set (0.00 sec)
 
mysql>
mysql> select name from tb_students_info where course_id = (select id from tb_course where course_name = 'Python');
+------+
| name |
+------+
| Jane |
+------+
1 row in set (0.00 sec)
 
mysql> </code>

使用<>运算符(不等于)，在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名

（!= 也可以实现<>的效果，!表示取反!=意思是不等于）

<code class="language-plaintext hljs">mysql> select name from tb_students_info where course_id <> (select id from tb_course where course_name = 'Pythhon');
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| Liming |
+--------+
10 rows in set (0.00 sec)
 
mysql></code>
<code class="language-plaintext hljs">mysql> select name from tb_students_info where course_id != (select id from tb_course where course_name = 'Pythhon');
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| Liming |
+--------+
10 rows in set (0.00 sec)
 
mysql></code>

查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录,如果不存在就显示为空

<code class="language-plaintext hljs">mysql> select * from tb_students_info where exists (select course_name from tb_course where id = 1);
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | Liming |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql> select * from tb_students_info where exists (select course_name from tb_course where id = 8);
Empty set (0.00 sec)
 
mysql> </code>
tb_course 表中存在 id=1 的记录，因此 EXISTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录

EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接

<code class="language-plaintext hljs">mysql> select * from tb_students_info where exists (select course_name from tb_course where id = 8)or not exists (select course_name from tb_course where id = 8);
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | Liming |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql>    ##命令能执行成功是因为后面not exists (select course_name from tb_course where id = 8)是成立的</code>

查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录

<code class="language-plaintext hljs">mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | Liming |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql> select * from tb_students_info where age > 24 and exists (select course_name from tb_course where id =1);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | Male |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)
 
mysql> </code>
结果显示，从 tb_students_info 表中查询出了一条记录，这条记录的 age 字段取值为 25。内层查询语句从 tb_course 表中查询到记录，返回 TRUE。外层查询语句开始进行查询。根据查询条件，从 tb_students_info 表中查询 age 大于 24 的记录。

查询 tb_course 表中是否存在 id=2 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录

<code class="language-plaintext hljs">mysql> select * from tb_students_info where age > 24 and exists (select course_name from tb_course where id = 2);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | Male |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)
 
mysql></code>
结果显示，明明没有大于24岁学习课程1的人但还是执行了出来，说明后面exists (select course_name from tb_course where id = 2)是一个条件，主要还前面是select * from tb_students_info where age > 24（查询 tb_students_info 表中 age 字段大于 24 ）

查询班级身高最高的学生

<code class="language-plaintext hljs">mysql> select name,height from tb_students_info where height = (select max(height) from tb_students_info);
+-------+--------+
| name  | height |
+-------+--------+
| Henry |    185 |
+-------+--------+
1 row in set (0.00 sec)
 
mysql></code>
<code class="language-plaintext hljs">mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Male   |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | Liming |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql></code>

查询班级年龄最小的学生姓名和性别

<code class="language-plaintext hljs">mysql> select name,sex,age from tb_students_info where age = (select min(age)from tb_students_info);
+------+------+------+
| name | sex  | age  |
+------+------+------+
| John | Male |   21 |
+------+------+------+
1 row in set (0.00 sec)
 
mysql></code>

查询 tb_students_info表的名字，人数，平均年龄，身高根据tb_students_info的姓名分组

<code class="language-plaintext hljs">mysql> select s.name,count(*),avg(s.age),s.height from tb_students_info s,tb_course c where s.course_id = c.id group by s.name;
+--------+----------+------------+--------+
| name   | count(*) | avg(s.age) | height |
+--------+----------+------------+--------+
| Dany   |        1 |    25.0000 |    160 |
| Green  |        1 |    23.0000 |    158 |
| Henry  |        2 |    24.0000 |    185 |
| Jane   |        1 |    22.0000 |    162 |
| Jim    |        1 |    24.0000 |    175 |
| John   |        1 |    21.0000 |    172 |
| Lily   |        1 |    22.0000 |    165 |
| Susan  |        1 |    23.0000 |    170 |
| Thomas |        1 |    22.0000 |    178 |
| Tom    |        1 |    23.0000 |    165 |
+--------+----------+------------+--------+
10 rows in set (0.00 sec)
 
mysql></code>

查询 tb_students_info表的名字，人数，平均年龄，身高根据身高进行降序排序

<code class="language-plaintext hljs">mysql> select s.name,count(*),avg(s.age),s.height from tb_students_info s,tb_course c where s.course_id = c.id group by s.name order by s.height desc;
+--------+----------+------------+--------+
| name   | count(*) | avg(s.age) | height |
+--------+----------+------------+--------+
| Henry  |        2 |    24.0000 |    185 |
| Thomas |        1 |    22.0000 |    178 |
| Jim    |        1 |    24.0000 |    175 |
| John   |        1 |    21.0000 |    172 |
| Susan  |        1 |    23.0000 |    170 |
| Lily   |        1 |    22.0000 |    165 |
| Tom    |        1 |    23.0000 |    165 |
| Jane   |        1 |    22.0000 |    162 |
| Dany   |        1 |    25.0000 |    160 |
| Green  |        1 |    23.0000 |    158 |
+--------+----------+------------+--------+
10 rows in set (0.00 sec)
 
mysql> </code>

查询班级身高最矮三个学生的名字，人数，平均年龄，身高

<code class="language-plaintext hljs">mysql> select s.name,count(*),avg(s.age),s.height from tb_students_info s,tb_course c where s.course_id = c.id group by s.name order by s.height limit 3;
+-------+----------+------------+--------+
| name  | count(*) | avg(s.age) | height |
+-------+----------+------------+--------+
| Green |        1 |    23.0000 |    158 |
| Dany  |        1 |    25.0000 |    160 |
| Jane  |        1 |    22.0000 |    162 |
+-------+----------+------------+--------+
3 rows in set (0.00 sec)
 
mysql> </code>