mysql多表联合查询
目录1.1 交叉连接
#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>
#插入表tb_students_info数据
mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'Male',160,1),('Green',23,'Male',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)
#插入tb_course表数据
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)
#使用 CROSS JOIN 查询出两张表中的笛卡尔积 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)
#查询 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)
#更换位置查询结果相同
mysql> select * from tb_course cross join tb_students_info where tb_course.id = tb_students_info.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)
mysql> select * from tb_students_info cross join tb_course where tb_course.id = tb_students_info.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)
#使用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)
1.2 内连接
#SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
字段名：需要查询的字段名称。
<表1><表2>：需要内连接的表名。
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
ON 子句：用来设置内连接的连接条件。
#tb_students_info别名为s tb_course别名为c 取s的name和c的course_name值 条件是s的course_id = c的id;
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)
1.3 外连接
1.3.1 左外连接
#SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。
<表1><表2>：需要左连接的表名。
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
ON 子句：用来设置左连接的连接条件，不能省略。
#"表1"为基表，"表2"为参考表。
#在tb_course表插入Html
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 没有显示6号Html 因为基础表中没有6
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
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)
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)
#再次以tb_students_info表为基础查询tb_students_info表的course_id 匹配 tb_course表的id 因为没有 值会为空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为tb_students_info表 c为tb_course表 以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)
1.3.2 右外连接
#SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
字段名：需要查询的字段名称。
<表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。
#与左连接相反，右连接以"表2"为基表，"表1"为参考表。
#s为tb_students_info表 c为tb_course表 以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)
1.4 分组查询
GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
#GROUP BY <字段名>
<字段名>表示需要分组的字段名称，多个字段时用逗号隔开
1.4.1 单独使用
#根据 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)
1.4.2 GROUP BY 与 GROUP_CONCAT
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)
#输入names就会显示names
mysql> select sex, group_concat(name) names from tb_students_info group by sex;
+--------+----------------------------------------+
| sex | names |
+--------+----------------------------------------+
| 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 字段进行分组查询
#先按照 age 字段进行分组，当 age 字段值相等时，再把 age 字段值相等的记录按照 sex 字段进行分组 优先age来分
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)
1.4.3 GROUP BY 与聚合函数
#聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。
#GROUP BY 关键字常和聚合函数一起使用。
COUNT() 用来统计记录的条数；
SUM() 用来计算字段值的总和；
AVG() 用来计算字段值的平均值；
MAX() 用来查询字段的最大值；
MIN() 用来查询字段的最小值。
#显示sex 字段值为"女"的记录是一组；sex 字段值为"男"的记录是一组。
mysql> select sex,COUNT(sex) from tb_students_info group by sex;
+--------+------------+
| sex | COUNT(sex) |
+--------+------------+
| Female | 4 |
| Male | 7 |
+--------+------------+
2 rows in set (0.37 sec)
1.4.4 GROUP BY 与 WITH ROLLUP
#WITH POLLUP 关键字
用来在所有记录的最后加上一条记录<这条记录是上面所有记录的总和>，即统计记录数量。
#GROUP_CONCAT(name) 显示了每个分组的 name 字段值。
#同时，最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。
mysql> select sex,group_concat(name) from tb_students_info group by sex with rollup;
+--------+-------------------------------------------------------------+
| sex | group_concat(name) |
+--------+-------------------------------------------------------------+
| Female | Henry,Jim,Thomas,Tom |
| Male | Dany,Green,Jane,John,Lily,Susan,Liming |
| NULL | Henry,Jim,Thomas,Tom,Green,Dany,Jane,John,Lily,Susan,Liming |
+--------+-------------------------------------------------------------+
3 rows in set (0.35 sec)
1.5 子查询
#子查询指将一个查询语句嵌套在另一个查询语句中。
#子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，可以进行多层嵌套。子查询经常出现在 WHERE 子句中。
WHERE <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
IN和NOT IN：当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
EXISTS和NOT EXISTS：用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
#显示学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。
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.42 sec)
#查询出 tb_course 表中课程为 Java 的 id，显示符合条件的 id 字段的值为 1。
mysql> select id from tb_course where course_name = 'Java';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
#外层的SELECT查询称为父查询，圆括号中查询称为子查询（子查询必须在圆括号内）。
#MySQL在处理上例的SELECT语句时，会先执行子查询，再执行父查询。
mysql> select name from tb_students_info where course_id in (1);
+-------+
| name |
+-------+
| Dany |
| Henry |
+-------+
2 rows in set (0.00 sec)
#运行结果与上面的例子刚好相反，没有学习Java的是除了Dany和 Henry之外的人。
mysql> select name from tb_students_info where course_id not in (select id from tb_coursee where course_name = 'Java');
+--------+
| name |
+--------+
| Green |
| Jane |
| Jim |
| John |
| Lily |
| Susan |
| Thomas |
| Tom |
| Liming |
+--------+
9 rows in set (0.39 sec)
#显示在tb_course表和tb_students_info表中查询出所有学习Python的姓名
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)
#显示在tb_course表和tb_students_info表中查询没有学习Python的人
mysql> select name from tb_students_info where course_id <> (select id from tb_course wherre course_name = 'Python');
+--------+
| name |
+--------+
| Dany |
| Green |
| Henry |
| Jim |
| John |
| Lily |
| Susan |
| Thomas |
| Tom |
| Liming |
+--------+
10 rows in set (0.00 sec)
#查询tb_course表中是否存在id=1的值
#EXISTS可以和其它查询条件一起使用，条件表达式与EXISTS之间用AND和OR 连接。
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 | Ma,le | 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)
#查询tb_course表中是否存在id=1的值，如果有，就查询出tb_students_info表中age字段大于24的记录。（子查询可以通过表连接完成，但子查询的SQL语句更容易理解阅读和编写。）
mysql> select * from tb_students_info where age>24 and exists(select course_name from tb_ccourse where id=1);
+----+------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+------+------+------+--------+-----------+
| 1 | Dany | 25 | Male | 160 | 1 |
+----+------+------+------+--------+-----------+
1 row in set (0.01 sec)
#查询tb_course表中是否存在id=2的数据，如果有，就查询tb_students_info表中age字段大于24的记录
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)
#查询height最高的
mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+--------+--------+-----------+
| 1 | Dany | 25 | Male | 160 | 1 |
| 2 | Green | 23 | Ma,le | 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 name,height from tb_students_info where height = (select max(height) from tbb_students_info);
+-------+--------+
| name | height |
+-------+--------+
| Henry | 185 |
+-------+--------+
1 row in set (0.00 sec)
#查询tb_students_info表的人名，人数，平均年龄，height根据tb_students_info的姓名分组
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.36 sec)
#查询tb_students_info表的name，人数，平均age，根据height进行降序
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)
#查询班级height最矮3个人的名字，人数，平均年龄，身高
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)