Mysql基础篇：必知必会（中）-阿里云开发者社区

示例表

mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
复制代码

接着上篇继续！

一、创建计算字段

1.拼接字段

将 name ， sex 两列进行合并。并通过 AS 关键字进行给新列赋予别名。

mysql> SELECT Concat(name, '(', sex, ')') AS new_column
    -> FROM one_piece;
复制代码

2.执行算数计算

通过 quantity （数量）、 price （价格）来计算 total_price （总价）

mysql> SELECT quantity, price,
    -> quantity * price AS total_price
    -> FROM test
复制代码

二、函数

常用文本处理函数

函数	说明
LEFT(str, length)	返回指定长度的字符串的左边部分
RIGHT(str, length)	返回指定长度的字符串右边部分
LTRIM(str)	去掉字符串左边的空格
RTRIM(str)	去掉字符串右边的空格
LOWER(str)	将字符串转换为小写
UPPER(str)	将字符串转换为大写
LENGTH(str)	返回字符串的长度

使用 LENGTH(str) 获取字符串的长度。

mysql> SELECT name, LENGTH(name) AS length
    -> FROM one_piece;
复制代码

日期和时间处理函数

查询在 2000年出生的人员信息。

mysql> SELECT *
    -> FROM test
    -> WHERE YEAR(brithday)=2000;
复制代码

数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
SIN()	返回一个角度的正弦
TAN()	返回一个角度的正切
PI()	返回圆周率
EXP()	返回一个数的指数值
SQRT()	返回一个数的平方根

以 ABS() 函数为例

sql> SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+
复制代码

三、数据聚集

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

1.AVG() 函数

查询平均 age 。

mysql> SELECT AVG(age) AS avg_age
    -> FROM one_piece
复制代码

2.COUNT() 函数

两种使用方式：

COUNT(*) 对表中行的数目进行计数，包括空值。

mysql> SELECT COUNT(*) AS num_person
    -> FROM one_piece;
复制代码

COUNT(column) 对特定列中非 NULL 行进行计数。

mysql> SELECT COUNT(name) AS num_name
    -> FROM one_piece;
复制代码

3.MAX() & MIN() 函数

当 column 列为数值列， MAX(column) / MIN(column) 返回 column 列中的最大值 / 最小值。

当 column 列为文本数据， MAX(column) / MIN(column) 返回 column 列数据排序后的最后一行 / 最前面的行。

4.SUM() 函数

SUM() 用来返回指定列值的和（总计）（忽略列值为 NULL 的行）。

mysql> SELECT SUM(price * quantity) AS total_price
    -> FROM test
复制代码

组合聚集函数

计算 one_piece 表中数据的条数，年龄的最小值、最大值和平均值。

mysql> SELECT COUNT(*) AS num_person,
    -> MIN(age) AS age_min,
    -> MAX(age) AS age_max,
    -> AVG(age) AS age_avg
    -> FROM one_piece;
复制代码

四、数据分组

数据分组

使用分组将数据分为多个逻辑组，对每个组进行聚集计算。例：统计各个海贼团( pirates )的人数。

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates;
复制代码

group by注意事项:

GROUP BY 可以嵌套使用。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

过滤分组

使用 HAVING 子句在数据分组后进行过滤。

查询海贼团人数在500人以上的海贼团名称及人数。

mysql> SELECT pirates, COUNT(*) AS num_person
    -> FROM one_piece
    -> GROUP BY pirates
    -> HAVING COUNT(*) >= 500;
复制代码

WHERE 与 HAVING 的主要区别：

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

SELECT 子句顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

五、子查询

利用子查询进行过滤

现在查询草帽海贼团的排名信息。

mysql> SELECT rank
    -> FROM rank_info
    -> WHERE id IN (SELECT id
    ->                     FROM one_piece
    ->                     WHERE pirates = '草帽海贼团');
复制代码

注意：

在 SELECT 语句中，子查询总是从内向外处理。
作为子查询的 SELECT 语句只能查询单个列。检索多个列会报错。

作为计算字段使用子查询

查询海贼团排名和任务信息，首先从 one_piece 表中根据 id 检索出排名信息，再统计每个冒险团的人数。

mysql> SELECT rank,
    -> (SELECT COUNT(*)
    ->  FROM one_piece AS oe
    ->  WHERE oe.id = ro.id) AS num_person
    -> FROM rank_info AS ro
    -> ORDER BY rank;
复制代码

注意：上面的例子中使用的是 oe.id 和 ro.id ，而不是直接使用 id ，因为在两个表中都有 id 列，在有可能混淆列名时必须使用这种语法。

六、表联结

自联结

假如现在有人不知道乔巴所属的海贼团, 想要知道乔巴所属海贼团的所有成员名称与赏金。先看一下子查询的方式：

mysql> SELECT name, bounty
    -> FROM one_piece
    -> WHERE pirates = (SELECT pirates
    ->                    FROM one_piece
    ->                    WHERE name = '乔巴');
复制代码

接下来使用自联结的方式：

mysql> SELECT c1.name, c1.bounty
    -> FROM Customers AS c1, Customers AS c2
    -> WHERE c1.pirates = c2.pirates
    ->  AND c2.name = '乔巴';
复制代码

通常情况下，自联结的方式比子查询的方式要快很多。

等值联结

联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。联结不是物理实体。换句话说，它在实际的数据库表中并不存在。它只在查询执行期间存在。

两表 table1， table2 中数据如下：

table1                      table2
+------+------+------+      +------+------+------+
| A    | B    | C    |      | C    | D    | E    |
+------+------+------+      +------+------+------+
|    1 |    2 |    3 |      |    2 |    3 |    4 |
|    4 |    5 |    6 |      |    6 |    7 |    8 |
+------+------+------+      +------+------+------+
复制代码

现在通过表联结，获取两个表中的数据。

mysql> SELECT *
    -> FROM table1 AS t1, table2 AS t2
    -> WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+
复制代码

注意：上例中WHERE 中限制了联结条件，如果没有条件的话，返回的结果就是两表的笛卡尔积，返回 6 × 9 共 54条数据

内联结

上面的联结准确来说是等值联结，也可以称为内联结，它还有另一种语法。返回的结果以上面相同。

mysql> SELECT *
    -> FROM table1 AS t1 INNER JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+
复制代码

一般内联结可以用如下图进行表示，取两个表关联字段相同的部分。

自然联结

自然连接是一种特殊的等值连接，它在两个关系表中自动比较相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

mysql> SELECT *
    -> FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C    | A    | B    | D    | E    |
+------+------+------+------+------+
|    6 |    4 |    5 |    7 |    8 |
+------+------+------+------+------+
复制代码

外联结

左外联结

左外联结，左表( table1 )的记录将会全部表示出来，而右表( table2 )只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。

mysql> SELECT *
    -> FROM table1 AS t1 LEFT JOIN table2 AS t2
    ->   ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
|    1 |    2 |    3 | NULL | NULL | NULL |
+------+------+------+------+------+------+
复制代码

右外联结

右外联结，右表( table2 )的记录将会全部表示出来，而右左表( table1 )只会显示符合搜索条件的记录。左表记录不足的地方均为 NULL 。

mysql> SELECT *
    -> FROM table1 AS t1 RIGHT JOIN table2 AS t2
    ->  ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
| NULL | NULL | NULL |    2 |    3 |    4 |
+------+------+------+------+------+------+
复制代码

Mysql基础篇：必知必会（中）