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

发表于

「这是我参与11月更文挑战的第15天，活动详情查看：2021最后一次更文挑战」。

示例表

1
2
3
4
5
6
7
8
9
10
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 关键字进行给新列赋予别名。

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

2.执行算数计算

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

1
2
3
sql复制代码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) 获取字符串的长度。

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

日期和时间处理函数

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

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

数值处理函数

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

以 ABS() 函数为例

1
2
3
4
5
6
sql复制代码sql> SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+

三、数据聚集

聚集函数

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

1.AVG() 函数

查询平均 age 。

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

2.COUNT() 函数

两种使用方式：

  • COUNT(*) 对表中行的数目进行计数，包括空值。
1
2
sql复制代码mysql> SELECT COUNT(*) AS num_person
    -> FROM one_piece;
  • COUNT(column) 对特定列中非 NULL 行进行计数。
1
2
sql复制代码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 的行）。

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

组合聚集函数

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

1
2
3
4
5
sql复制代码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 )的人数。

1
2
3
sql复制代码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人以上的 海贼团名称 及 人数。

1
2
3
4
sql复制代码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 输出排序顺序 否

五、子查询

利用子查询进行过滤

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

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

注意：

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

作为计算字段使用子查询

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

1
2
3
4
5
6
sql复制代码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 列，在有可能混淆列名时必须使用这种语法。

六、表联结

自联结

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

1
2
3
4
5
sql复制代码mysql> SELECT name, bounty
    -> FROM one_piece
    -> WHERE pirates = (SELECT pirates
    ->                    FROM one_piece
    ->                    WHERE name = '乔巴');

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

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

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

等值联结

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

两表 table1， table2 中数据如下：

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

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

1
2
3
4
5
6
7
8
sql复制代码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条数据

内联结

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

1
2
3
4
5
6
7
8
sql复制代码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 |
+------+------+------+------+------+------+

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

自然联结

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

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

外联结

左外联结

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

1
2
3
4
5
6
7
8
9
sql复制代码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 。

1
2
3
4
5
6
7
8
9
sql复制代码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 |
+------+------+------+------+------+------+

四种联结对比图

内联结 自然联结（去重）
左外联结 右外联结

这就是今天要分享的内容，微信搜 Python新视野，每天带你了解更多有用的知识。更有整理的近千套简历模板，几百册电子书等你来领取哦！

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%