MySQL在macOS上的使用——包括几种常用的语法（JOIN、UNION的区别、GROUP BY等）及实践结果

本文链接：https://blog.csdn.net/zhyueln/article/details/93504801

文章目录

数据基础1.0
JOIN and UNION
COUNT函数的统计基础
- COUNT函数实现带条件统计
SELECT语句中容易出错的点（GROUP BY）
- 数据基础2.0
- 基本的SQL语句

数据基础1.0

实验环境：macOS 10.14.5；
software：terminal

启动MySQL

$ mysql.server start
Starting MySQL
.. SUCCESS!

建立数据库连接

$ mysql -uroot -p
Enter password: ********
//输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

选择目标数据库zhData

mysql> use zhData
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

首先，确认本文章中的数据基础，即两张基础表。
1、创建表a create

mysql> create table a(id int, num int);
Query OK, 0 rows affected (0.01 sec)

2、插入数据 insert

mysql>  insert into a values (1,100),(2,200),
(3,300),(4,300),(8,null),(9,null); 
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

最终，表a如下

mysql> select * from a;
+------+------+
| id   | num  |
+------+------+
|    1 |  100 |
|    2 |  200 |
|    3 |  300 |
|    4 |  300 |
|    8 | NULL |
|    9 | NULL |
+------+------+
6 rows in set (0.00 sec)

同样的，创建表b同时插入数据。
表b如下

mysql> select * from b;
+------+------+
| id   | num  |
+------+------+
|    1 |  100 |
|    2 |  200 |
|    3 |  300 |
|    3 | 1000 |
|    5 |  300 |
|    6 |  500 |
|    7 |  400 |
+------+------+
6 rows in set (0.00 sec)

JOIN and UNION

两张表的联接，最常用的就是JOIN和UNION。

JOIN包括 inner join（也就是join），left join，right join；
UNION包括 union，union all（全联接）。

inner join – 内联接

两张表，按照id进行联接。若两表的id相同，则取出进行拼接（只联接匹配的行）。

mysql> select * from a join b on a.id = b.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |  100 |    1 |  100 |
|    2 |  200 |    2 |  200 |
|    3 |  300 |    3 | 1000 |
|    3 |  300 |    3 |  300 |
+------+------+------+------+
4 rows in set (0.00 sec)

left join

两张表，按照id进行联接。

以左表作为基准，取出左表中所有id，去匹配右表中的数据。
若右表存在相同的id，则取出进行联接。
若右表中不存在相同的id，则相应id中，右表数据返回NULL进行联接。

mysql> select * from a left join b on a.id = b.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |  100 |    1 |  100 |
|    2 |  200 |    2 |  200 |
|    3 |  300 |    3 | 1000 |
|    3 |  300 |    3 |  300 |
|    4 |  300 | NULL | NULL |
|    8 | NULL | NULL | NULL |
|    9 | NULL | NULL | NULL |
+------+------+------+------+
7 rows in set (0.00 sec)

right join

两张表，按照id进行联接。

以右表作为基准，取出右表中所有id，去匹配左表中的数据。
若左表存在相同的id，则取出进行联接。
若左表中不存在相同的id，则相应id中，左表数据返回NULL进行联接。

mysql> select * from a right join b on a.id = b.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |  100 |    1 |  100 |
|    2 |  200 |    2 |  200 |
|    3 |  300 |    3 | 1000 |
|    3 |  300 |    3 |  300 |
| NULL | NULL |    5 |  300 |
| NULL | NULL |    6 |  500 |
| NULL | NULL |    7 |  400 |
+------+------+------+------+
7 rows in set (0.00 sec)

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
也就是说，利用UNION合并时，必须是对SELECT后的结果进行操作。

同时，

UNION 内部的 SELECT 语句必须拥有相同数量的列。
列也必须拥有相似的数据类型。
每条 SELECT 语句中的列的顺序必须相同。(union无法自我判断列名)

UNION 也有两种联接方式：

1. union

UNION是将两张表去重后按行合并(会降低效率)。
(在MySQL中，对SELECT结果去重可以采用DISTINCT)

mysql> select * from a 
		union
		select * from  b;
+------+------+
| id   | num  |
+------+------+
|    1 |  100 |
|    2 |  200 |
|    3 |  300 |
|    4 |  300 |
|    8 | NULL |
|    9 | NULL |
|    3 | 1000 |
|    5 |  300 |
|    6 |  500 |
|    7 |  400 |
+------+------+
10 rows in set (0.00 sec)

2. union all – 全联接

UNION ALL是将两张表追加合并(不去重，类似于python中的concat函数）。

mysql> select * from a
		union all 
		select * from  b;
+------+------+
| id   | num  |
+------+------+
|    1 |  100 |
|    2 |  200 |
|    3 |  300 |
|    4 |  300 |
|    8 | NULL |
|    9 | NULL |
|    1 |  100 |
|    2 |  200 |
|    3 | 1000 |
|    5 |  300 |
|    6 |  500 |
|    7 |  400 |
|    3 |  300 |
+------+------+
13 rows in set (0.00 sec)

在日常的数据拉取工作中，我们经常需要对数据进行一般性统计，例如需要得到选择某门课程的学生有多少人等类似的计算。这种情况下我们可以采用COUNT函数。

COUNT函数的统计基础

count()函数的基础：count对值为NULL的记录不计数。

利用count(*)可以得到：a表的数据总条数为6。

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

利用count(num)可以得到：a表中num非空的数量为4。

mysql> select count(num) num_notNULL from a;
+-------------+
| num_notNULL |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

在实际应用的过程中，除了简单的使用count函数进行全量数据统计，我们通常会遇到更为复杂的情况，即需要在一定的判别条件下，对数据进行统计。如我需要知道num大于200的id数量等，就进行条件判别再进行统计。通常我们可以采用where条件写成：

mysql> select count(*) from a where num >200;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

当然这是可行的，但是在这里我们将给出另一种统计方法，即利用count函数直接实现带条件的统计。

COUNT函数实现带条件统计

COUNT带条件统计有三种实现方案

1. count( expr or null )

count函数中使用条件表达式加or null来实现条件统计。

当条件expr不满足时，函数返回count(null)，count函数在遇到NULL时计数为0。也就是说在条件不满足时，count函数返回0。

mysql> select count(num>200 or null) from a;
+--------------------------+
| count(num>200 or null) |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

2. count(if (expr1,expr2,null ))

count函数中使用if条件表达式来实现条件统计。

同样利用的是当count函数遇到NULL时不计数的特点。

count(if(expr1,expr2,null))理解为，当满足条件expr1时，返回expr2，否则返回null。

mysql> select count(if(num>200,1,null)) from a;
+---------------------------+
| count(if(num>200,1,null)) |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

3. count(case when expr1 then expr2 end)

count函数中使用case when条件表达式来实现条件统计。

同样利用的是当count函数遇到NULL时不计数的特点。

case when 函数句法使用：
(1)

CASE value WHEN [compare_value] THEN result 
[WHEN [compare_value] THEN result ...] [ELSE result] 
END

当value=compare_value时，返回result，如有多个条件，依次判断。若都不满足，且有ELSE条件，则返回ELSE后的result，若没有ELSE条件，则返回NULL。
(2)

CASE WHEN [condition] THEN result
[WHEN [condition] THEN result ...][ELSE result] 
END

当满足condition时，返回result，如有多个条件，依次判断。若都不满足，且有ELSE条件，则返回ELSE后的result，若没有ELSE条件，则返回NULL。

如：

mysql> select count(case when num>200 then 1 end) from a;
+-------------------------------------+
| count(case when num>200 then 1 end) |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
1 row in set (0.00 sec)

SELECT语句中容易出错的点（GROUP BY）

在MySQL使用的不熟练时，进行数据拉取经常会遇到各样的坑。这里梳理出几个常遇到的问题作为参考。

数据基础2.0

先给出基础表student

mysql> select * from student;
+-------------+-----------+----------+-------+-----+
| sid         | sname     | major    | tid   | sex |
+-------------+-----------+----------+-------+-----+
| 16451082123 | ZH        | math     | 17531 | 女  |
| 16451082124 | L         | computer | 17532 | 男  |
| 16451082125 | Z         | math     | 17533 | 女  |
| 16451082126 | M         | English  | 17532 | 女  |
| 16451082127 | ZT        | Chinese  | 17534 | 女  |
| 16451082128 | 王力宏     | math     | 17531 | 男  |
+-------------+-----------+----------+-------+-----+
6 rows in set (0.00 sec)

基本的SQL语句

基本的SQL选择语句模版

mysql> SELECT 
			major,  count(sid) `选修的学生数` 
		FROM 
			student  
		WHERE 
			major IN ( 'math', 'computer' )  
		GROUP BY 
			major  
		HAVING 
			选修的学生数 > 2;
+-------+----------------+
| major | 选修的学生数     |
+-------+----------------+
| math  |              3 |
+-------+----------------+
1 row in set (0.00 sec)

需要注意的几个点：

`选修的学生数` ---- 该引号``用于列名的使用及命名。
major IN ( ‘math’, ‘computer’ ) 或 major= ‘math’ ---- 该引号''用于where等条件的判断语句中。
除聚集计算语句（sum, count…）外，select语句中每个列都必须在group by子句中给出。也就是说，在有group by子句时，查询结果除了group by对象，剩下的应该都是聚合后的计算结果。

那么给出SQL语句后，具体的执行逻辑又是怎么一回事呢？

以下给出SQL语句的一般执行顺序。

函数	备注
FROM	查找的目标表或结果集或视图
WHERE	全表数据做筛选，先过滤单表／视图／结果集（再JOIN），返回第1个结果集（`过滤行`）
GROUP BY	对第`1`个结果集进行分组，一般结合`聚合函数`，返回第2个结果集
select	对第`2`个结果集中的 `每个组`执行 `select语句`，有几个组就执行几次，返回第3个结果集
HAVING	对第`3`个结果集进行筛选可以是对值的判断，也可以是聚集函数的判断（`过滤组`）
ORDER BY	对结果排序，`[asc] 升序`，`[desc] 降序`
LIMIT	获取条目的`限定`。ex1. LIMIT 5 == LIMIT 0, 5 结果的前五行；ex2. 想要第2到第4条数据：LIMIT1,3