MySQL多表查询

多表查询

1.什么是多表联合查询

前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。,可以解决复杂的数据查询问题，根据实际的需求可以调整查询结果的方式（就是使用多表查询的种类），以满足实际的需求。
在 MySQL 中，多表查询主要有交叉连接（笛卡尔积）、内连接、外连接、分组查询与子查询等5种。

2.创建两张不同的表

//一张员工表emp，一张部门表dept
mysql> select database();      //需要先进入数据库再创建表
+------------+
| database() |
+------------+
| hl         |
+------------+
1 row in set (0.00 sec)

//创建表一emp，empno表示员工编号、ename表示员工姓名、hiredate表示入职时间、deptno表示部门编号
mysql> create table emp(empno int not null primary key auto_increment,ename varchar(20),hiredate date,deptno int not null);       
Query OK, 0 rows affected (0.02 sec)

mysql> desc emp;                 //查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| empno    | int         | NO   | PRI | NULL    | auto_increment |
| ename    | varchar(20) | YES  |     | NULL    |                |
| hiredate | date        | YES  |     | NULL    |                |
| deptno   | int         | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

//这里的deptno表示部门编号、dname表示部门名称、loc表示部门所在地点
mysql> create table dept(deptno int not null,dname varchar(50),loc varchar(10));
Query OK, 0 rows affected (0.01 sec)    //创建表二dept

mysql> desc dept;                //查看表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int         | NO   |     | NULL    |       |
| dname  | varchar(50) | YES  |     | NULL    |       |
| loc    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> show tables;              //查看有哪些表
+--------------+
| Tables_in_hl |
+--------------+
| dept         |
| emp          |
+--------------+
2 rows in set (0.01 sec)

mysql> insert emp(ename,hiredate,deptno) values('zhangsan','2018-1-1',20),('lisi','2018-02-20',30),('wangwu','2019-02-22',30),('zhaoliu','2019-04-02',20),('tianqi','2019-09-28',30);
Query OK, 5 rows affected (0.01 sec) 
Records: 5  Duplicates: 0  Warnings: 0           //向表一emp插入数据

mysql> select * from emp;            //查看表中的数据
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
+-------+----------+------------+--------+
5 rows in set (0.00 sec)

mysql> insert dept(deptno,dname,loc) values(10,'accounting','beijing'),(20,'research','shanghai'),(30,'sales','hangzhou'),(40,'operations','shenzhen');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0           //向表二demp插入数据

mysql> select * from dept;             //查看表中数据
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

3.笛卡尔积（cross join）

交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。
例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：

• 两个集合相乘，不满足交换率，即 A×B≠B×A。
• A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

//语法：SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]; 或 SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
+-------+----------+------------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from emp cross join dept;      //把两个表中数据的所有组合形式排列下来
+-------+----------+------------+--------+--------+------------+----------+
| empno | ename    | hiredate   | deptno | deptno | dname      | loc      |
+-------+----------+------------+--------+--------+------------+----------+
|     1 | zhangsan | 2018-01-01 |     20 |     40 | operations | shenzhen |
|     1 | zhangsan | 2018-01-01 |     20 |     30 | sales      | hangzhou |
|     1 | zhangsan | 2018-01-01 |     20 |     20 | research   | shanghai |
|     1 | zhangsan | 2018-01-01 |     20 |     10 | accounting | beijing  |
|     2 | lisi     | 2018-02-20 |     30 |     40 | operations | shenzhen |
|     2 | lisi     | 2018-02-20 |     30 |     30 | sales      | hangzhou |
|     2 | lisi     | 2018-02-20 |     30 |     20 | research   | shanghai |
|     2 | lisi     | 2018-02-20 |     30 |     10 | accounting | beijing  |
|     3 | wangwu   | 2019-02-22 |     30 |     40 | operations | shenzhen |
|     3 | wangwu   | 2019-02-22 |     30 |     30 | sales      | hangzhou |
|     3 | wangwu   | 2019-02-22 |     30 |     20 | research   | shanghai |
|     3 | wangwu   | 2019-02-22 |     30 |     10 | accounting | beijing  |
|     4 | zhaoliu  | 2019-04-02 |     20 |     40 | operations | shenzhen |
|     4 | zhaoliu  | 2019-04-02 |     20 |     30 | sales      | hangzhou |
|     4 | zhaoliu  | 2019-04-02 |     20 |     20 | research   | shanghai |
|     4 | zhaoliu  | 2019-04-02 |     20 |     10 | accounting | beijing  |
|     5 | tianqi   | 2019-09-28 |     30 |     40 | operations | shenzhen |
|     5 | tianqi   | 2019-09-28 |     30 |     30 | sales      | hangzhou |
|     5 | tianqi   | 2019-09-28 |     30 |     20 | research   | shanghai |
|     5 | tianqi   | 2019-09-28 |     30 |     10 | accounting | beijing  |
+-------+----------+------------+--------+--------+------------+----------+
20 rows in set (0.00 sec)

mysql> select * from emp cross join dept where emp.deptno=dept.deptno; //加上条件之后只把符合条件的数据查询出来
+-------+----------+------------+--------+--------+----------+----------+
| empno | ename    | hiredate   | deptno | deptno | dname    | loc      |
+-------+----------+------------+--------+--------+----------+----------+
|     1 | zhangsan | 2018-01-01 |     20 |     20 | research | shanghai |
|     2 | lisi     | 2018-02-20 |     30 |     30 | sales    | hangzhou |
|     3 | wangwu   | 2019-02-22 |     30 |     30 | sales    | hangzhou |
|     4 | zhaoliu  | 2019-04-02 |     20 |     20 | research | shanghai |
|     5 | tianqi   | 2019-09-28 |     30 |     30 | sales    | hangzhou |
+-------+----------+------------+--------+--------+----------+----------+
5 rows in set (0.00 sec)

4.内连接（inner join）

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

• INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
• ON 子句：用来设置内连接的连接条件。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN … ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。

//语法：SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
+-------+----------+------------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select e.empno,e.ename,d.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno;
+-------+----------+--------+----------+        //类似于笛卡尔积，把where换成了on，这里的on是连接条件，根据实际需
| empno | ename    | deptno | dname    |          求还可以在on后面添加一个查询条件where。
+-------+----------+--------+----------+       //这里的e和d表示表的简写
|     1 | zhangsan |     20 | research |
|     2 | lisi     |     30 | sales    |
|     3 | wangwu   |     30 | sales    |
|     4 | zhaoliu  |     20 | research |
|     5 | tianqi   |     30 | sales    |
+-------+----------+--------+----------+
5 rows in set (0.00 sec)

mysql> select * from emp e inner join dept d on e.deptno=d.deptno;
+-------+----------+------------+--------+--------+----------+----------+
| empno | ename    | hiredate   | deptno | deptno | dname    | loc      |
+-------+----------+------------+--------+--------+----------+----------+
|     1 | zhangsan | 2018-01-01 |     20 |     20 | research | shanghai |
|     2 | lisi     | 2018-02-20 |     30 |     30 | sales    | hangzhou |
|     3 | wangwu   | 2019-02-22 |     30 |     30 | sales    | hangzhou |
|     4 | zhaoliu  | 2019-04-02 |     20 |     20 | research | shanghai |
|     5 | tianqi   | 2019-09-28 |     30 |     30 | sales    | hangzhou |
+-------+----------+------------+--------+--------+----------+----------+
5 rows in set (0.00 sec)

5.外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接可以分为左外连接和右外连接2种，下面根据实例分别介绍左外连接和右外连接。

左外连接（left join）

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。以左表为基准，把左表满足条件的和不满足条件的都查询出来，右表只把满足条件的查出来，不满足的则为空。

语法说明如下:

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

语法：SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;

mysql> insert emp(ename,hiredate,deptno) values('zz','2019-11-11',50),('xx','2019-11-18',60);
Query OK, 2 rows affected (0.00 sec)         //插入两个条件，为后面的语句应用
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;
+-------+----------+--------+----------+
| empno | ename    | deptno | dname    |
+-------+----------+--------+----------+
|     1 | zhangsan |     20 | research |
|     2 | lisi     |     30 | sales    |
|     3 | wangwu   |     30 | sales    |
|     4 | zhaoliu  |     20 | research |
|     5 | tianqi   |     30 | sales    |
|     6 | zz       |   NULL | NULL     |
|     7 | xx       |   NULL | NULL     |
+-------+----------+--------+----------+
7 rows in set (0.00 sec)

右外连接（right join）

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。以右表为基准，把右表满足条件的和不满足条件的都查询出来，左表只把满足条件的查出来，不满足的则为空。

语法说明如下:

• 字段名：需要查询的字段名称。
• <表1><表2>：需要右连接的表名。
• RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
• ON 子句：用来设置右连接的连接条件，不能省略。

//语法：SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on
e.deptno=d.deptno;
+-------+----------+--------+------------+
| empno | ename    | deptno | dname      |
+-------+----------+--------+------------+
|  NULL | NULL     |     10 | accounting |
|     4 | zhaoliu  |     20 | research   |
|     1 | zhangsan |     20 | research   |
|     5 | tianqi   |     30 | sales      |
|     3 | wangwu   |     30 | sales      |
|     2 | lisi     |     30 | sales      |
|  NULL | NULL     |     40 | operations |
+-------+----------+--------+------------+
7 rows in set (0.00 sec)

6.分组查询

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

使用 GROUP BY 关键字的语法格式如下：

GROUP BY  <字段名>

GROUP BY单独使用

单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。

下面根据 tb_students_info 表中的 sex 字段进行分组查询，SQL 语句和运行结果如下：

语法：select 字段 from 表名 group by 字段

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select ename,deptno from emp group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hl.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

//解决方案
mysql> select @@sql_mode
    -> ;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
port = 3306
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pPassw0rd@_
mysql> use hl;
Database changed
mysql> select ename,deptno from emp group by deptno;
+----------+--------+
| ename    | deptno |
+----------+--------+
| zhangsan |     20 |               //这里查询出来的结果是以第一个20为准
| lisi     |     30 |
| zz       |     50 |
| xx       |     60 |
+----------+--------+
4 rows in set (0.00 sec)

GROUP BY 与 GROUP_CONCAT()

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

mysql> create table student(id int not null primary key auto_increment,name varchar(50),age int,sex varchar(50));                         //这里是重新创建的一张表，以便于后面能清晰和准确的查询结果，更好的理解
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    |                |
| sex   | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert student(name,age,sex) values('liwei',22,'male'),('zhaohang',23,'male'),('liuyi',21,'female'),('wangwu',24,'male'),('sunrei',21,'female'),('zhaoyan',20,'female'),('tianqi',19,'male'),('zhangsan',20,'female');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | sex    |
+----+----------+------+--------+
|  1 | liwei    |   22 | male   |
|  2 | zhaohang |   23 | male   |
|  3 | liuyi    |   21 | female |
|  4 | wangwu   |   24 | male   |
|  5 | sunrei   |   21 | female |
|  6 | zhaoyan  |   20 | female |
|  7 | tianqi   |   19 | male   |
|  8 | zhangsan |   20 | female |
+----+----------+------+--------+
8 rows in set (0.00 sec)

语法：select 字段，group_concat(字段) from 表 group by 字段

mysql> select sex,group_concat(name) from student group by sex;
+--------+-------------------------------+
| sex    | group_concat(name)            |
+--------+-------------------------------+
| female | liuyi,sunrei,zhaoyan,zhangsan |
| male   | liwei,zhaohang,wangwu,tianqi  |
+--------+-------------------------------+
2 rows in set (0.00 sec)
//由结果可以看到，查询结果分为两组，sex 字段值为"女"的是一组，值为"男"的是一组，且每组的学生姓名都显示出来了。

mysql> select age,sex,group_concat(name) from student group by age,sex;
+------+--------+--------------------+
| age  | sex    | group_concat(name) |
+------+--------+--------------------+
|   19 | male   | tianqi             |
|   20 | female | zhaoyan,zhangsan   |
|   21 | female | liuyi,sunrei       |
|   22 | male   | liwei              |
|   23 | male   | zhaohang           |
|   24 | male   | wangwu             |
+------+--------+--------------------+
6 rows in set (0.00 sec)
//上面实例在分组过程中，先按照 age 字段进行分组，当 age 字段值相等时，再把 age 字段值相等的记录按照 sex 字段进行分组。

GROUP BY 与聚合函数

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

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

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | sex    |
+----+----------+------+--------+
|  1 | liwei    |   22 | male   |
|  2 | zhaohang |   23 | male   |
|  3 | liuyi    |   21 | female |
|  4 | wangwu   |   24 | male   |
|  5 | sunrei   |   21 | female |
|  6 | zhaoyan  |   20 | female |
|  7 | tianqi   |   19 | male   |
|  8 | zhangsan |   20 | female |
+----+----------+------+--------+
8 rows in set (0.00 sec)

//统计male和female的有多少个
mysql> select sex,count(sex) from student group by sex;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          4 |
| female |          4 |
+--------+------------+
2 rows in set (0.00 sec)

//统计male和female的平均年龄
mysql> select sex,avg(age) from student group by sex 
    -> ;
+--------+----------+
| sex    | avg(age) |
+--------+----------+
| male   |  22.0000 |
| female |  20.5000 |
+--------+----------+
2 rows in set (0.00 sec)

//统计male和female的年龄总和
mysql> select sex,sum(age) from student group by sex;
+--------+----------+
| sex    | sum(age) |
+--------+----------+
| male   |       88 |
| female |       82 |
+--------+----------+
2 rows in set (0.00 sec)

//查询出male和female中哪个年龄是最大的
mysql> select sex,max(age) from student group by sex;
+--------+----------+
| sex    | max(age) |
+--------+----------+
| male   |       24 |
| female |       21 |
+--------+----------+
2 rows in set (0.00 sec)

//查询出male和female中哪个年龄是最小的
mysql> select sex,min(age) from student group by sex;
+--------+----------+
| sex    | min(age) |
+--------+----------+
| male   |       19 |
| female |       20 |
+--------+----------+
2 rows in set (0.00 sec)

GROUP BY 与 WITH ROLLUP

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

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | sex    |
+----+----------+------+--------+
|  1 | liwei    |   22 | male   |
|  2 | zhaohang |   23 | male   |
|  3 | liuyi    |   21 | female |
|  4 | wangwu   |   24 | male   |
|  5 | sunrei   |   21 | female |
|  6 | zhaoyan  |   20 | female |
|  7 | tianqi   |   19 | male   |
|  8 | zhangsan |   20 | female |
+----+----------+------+--------+
8 rows in set (0.00 sec)

mysql> select sex,group_concat(name) from student group by sex with rollup;   //最后一行是把所有名字都统计出来
+--------+------------------------------------------------------------+
| sex    | group_concat(name)                                         |
+--------+------------------------------------------------------------+
| female | liuyi,sunrei,zhaoyan,zhangsan                              |
| male   | liwei,zhaohang,wangwu,tianqi                               |
| NULL   | liuyi,sunrei,zhaoyan,zhangsan,liwei,zhaohang,wangwu,tianqi |
+--------+------------------------------------------------------------+
3 rows in set (0.00 sec)

7.子查询

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

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

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

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

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

mysql> select * from emp;
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | beijing  |
|     20 | research   | shanghai |
|     30 | sales      | hangzhou |
|     40 | operations | shenzhen |
+--------+------------+----------+
4 rows in set (0.00 sec)

//in表示等于，也可以用=表示
mysql> select ename from emp where deptno in (select deptno from dept where dname = 'research');
+----------+
| ename    |
+----------+
| zhangsan |
| zhaoliu  |
+----------+
2 rows in set (0.00 sec)

//先执行后面（）里面的
mysql> select deptno from dept where dname = 'research';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

mysql> select ename from emp where deptno in(20);
+----------+
| ename    |
+----------+
| zhangsan |
| zhaoliu  |
+----------+
2 rows in set (0.00 sec)

//not in 表示不等于 也可以用!=表示
mysql> select ename from emp where deptno not in (select deptno from dept where dname = 'research');
+--------+
| ename  |
+--------+
| lisi   |
| wangwu |
| tianqi |
| zz     |
| xx     |
+--------+
5 rows in set (0.00 sec)

//<>表示不等于
mysql> select ename from emp where deptno <> (select deptno from dept where dname = 'research');
+--------+
| ename  |
+--------+
| lisi   |
| wangwu |
| tianqi |
| zz     |
| xx     |
+--------+
5 rows in set (0.00 sec)

//这里的exists表示后面的数据存在就会列出前面所有的数据，反之，不会
mysql> select * from emp where exists(select dname from dept where deptno=20);
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

mysql> select * from emp where not exists(select dname from dept where deptno=20);
Empty set (0.00 sec)

mysql> select * from emp where not exists(select dname from dept where deptno=100);
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     2 | lisi     | 2018-02-20 |     30 |
|     3 | wangwu   | 2019-02-22 |     30 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
|     5 | tianqi   | 2019-09-28 |     30 |
|     6 | zz       | 2019-11-11 |     50 |
|     7 | xx       | 2019-11-18 |     60 |
+-------+----------+------------+--------+
7 rows in set (0.00 sec)

//这里必须满足后面exists查询出来的数据成立才可以查询出前面的数据
mysql> select * from emp where deptno <= 20 and exists(select dname from dept w
here deptno=20);
+-------+----------+------------+--------+
| empno | ename    | hiredate   | deptno |
+-------+----------+------------+--------+
|     1 | zhangsan | 2018-01-01 |     20 |
|     4 | zhaoliu  | 2019-04-02 |     20 |
+-------+----------+------------+--------+
2 rows in set (0.00 sec)

//一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。