SQL自学，mysql从入门到精通 --- 第 5 天，对函数的处理

devtools/2025/2/12 3:02:38/

对函数的处理

新建一个成绩表

sql">root@mysqldb 09:39:  [d1]> create table score (-> name varchar(30),-> chinese int,-> math int,-> music int,-> team int,-> magic int,-> computer int-> );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 09:39:  [d1]> insert into score-> values-> ("1A","90","85","30","92","70","65"),-> ("2B","99","98","90","95","92","89"),-> ("3C","90","70","60","88","89","92"),-> ("4D","89","85","59","78","93","94"),-> ("5E","88","70","66","92","58","76");
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0-- 数据如下
root@mysqldb 09:44:  [d1]> select * from score;
+------+---------+------+-------+------+-------+----------+
| name | chinese | math | music | team | magic | computer |
+------+---------+------+-------+------+-------+----------+
| 1A   |      90 |   85 |    30 |   92 |    70 |       65 |
| 2B   |      99 |   98 |    90 |   95 |    92 |       89 |
| 3C   |      90 |   70 |    60 |   88 |    89 |       92 |
| 4D   |      89 |   85 |    59 |   78 |    93 |       94 |
| 5E   |      88 |   70 |    66 |   92 |    58 |       76 |
+------+---------+------+-------+------+-------+----------+
5 rows in set (0.00 sec)

COUNT 统计表中有多少行数据

sql">-- COUNT(*)：统计表中的所有行数，包括所有列的数据行
root@mysqldb 09:46:  [d1]> select count(*) from score;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)-- COUNT(column_name)：统计指定列中非 NULL 值的行数
root@mysqldb 09:47:  [d1]> select count(chinese) from score;
+----------------+
| count(chinese) |
+----------------+
|              5 |
+----------------+
1 row in set (0.00 sec)-- COUNT(DISTINCT column_name)：统计指定列中不同值的行数。
root@mysqldb 09:49:  [d1]> select count(distinct chinese) from score;
+-------------------------+
| count(distinct chinese) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)-- COUNT(DISTINCT column_name1, column_name2)：统计多个列中不同值的组合行数

SUM 返回某一列所有数值的总和

sql">-- 统计math列的总和
root@mysqldb 09:55:  [d1]> SELECT SUM(math) AS total_math FROM score;
+------------+
| total_math |
+------------+
|        408 |
+------------+
1 row in set (0.01 sec)-- 统计多个列数值的总和
root@mysqldb 09:56:  [d1]> SELECT SUM(math) total_math, SUM(team) total_team FROM score;
+------------+------------+
| total_math | total_team |
+------------+------------+
|        408 |        445 |
+------------+------------+
1 row in set (0.00 sec)-- 求数学成绩的平均分
root@mysqldb 09:57:  [d1]> SELECT SUM(MATH)/COUNT(name) FROM score;
+-----------------------+
| SUM(MATH)/COUNT(name) |
+-----------------------+
|               81.6000 |
+-----------------------+
1 row in set (0.00 sec)-- 上面的结果保留两位小数点
root@mysqldb 10:01:  [d1]> SELECT ROUND(SUM(MATH)/COUNT(name),2) FROM score;
+--------------------------------+
| ROUND(SUM(MATH)/COUNT(name),2) |
+--------------------------------+
|                          81.60 |
+--------------------------------+
1 row in set (0.00 sec)

AGV 返回某一列的平均值

sql">root@mysqldb 10:03:  [d1]> SELECT AVG(math) avg_math FROM score;
+----------+
| avg_math |
+----------+
|  81.6000 |
+----------+
1 row in set (0.00 sec)-- 上面的平均数查询结果保留1个小数点
root@mysqldb 10:05:  [d1]> SELECT ROUND(AVG(math),1) avg_math FROM score;
+----------+
| avg_math |
+----------+
|     81.6 |
+----------+
1 row in set (0.00 sec)

MAX 查询某一列中的最大值

sql">-- 查询math的最高分
root@mysqldb 10:06:  [d1]> SELECT MAX(math) FROM score;
+-----------+
| MAX(math) |
+-----------+
|        98 |
+-----------+
1 row in set (0.00 sec)

MIN 查询某一列中的最小值

sql">root@mysqldb 10:08:  [d1]> SELECT MIN(math) FROM score;
+-----------+
| MIN(math) |
+-----------+
|        70 |
+-----------+
1 row in set (0.00 sec)

日期时间函数

DATETIME

-- DATETIME ,时间格式为 "yy-mm-dd HH:MM:SS"root@mysqldb 10:34:  [d1]> CREATE TABLE project (-> task varchar(30),-> StartTime DATETIME,-> endtime DATETIME-> );
Query OK, 0 rows affected (0.10 sec)root@mysqldb 11:06:  [d1]> INSERT INTO project VALUES-> ("AA","2023-07-15 10:00:00","2023-08-01 00:00:00"),-> ("BB","2023-07-20 10:00:00","2023-08-02 10:00:00"),-> ("CC","2023-08-16 00:00:00","2023-08-25 00:00:00"),-> ("DD","2023-09-02","2023-09-06"),-> ("EE","2023-09-01 10:00:00","2023-09-05 00:00:00");
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0-- 省缺的HH:MM:SS 自动填充成00:00:00
root@mysqldb 11:06:  [d1]> SELECT * FROM project;
+------+---------------------+---------------------+
| task | StartTime           | endtime             |
+------+---------------------+---------------------+
| AA   | 2023-07-15 10:00:00 | 2023-08-01 00:00:00 |
| BB   | 2023-07-20 10:00:00 | 2023-08-02 10:00:00 |
| CC   | 2023-08-16 00:00:00 | 2023-08-25 00:00:00 |
| DD   | 2023-09-02 00:00:00 | 2023-09-06 00:00:00 |
| EE   | 2023-09-01 10:00:00 | 2023-09-05 00:00:00 |
+------+---------------------+---------------------+

DATE 时间格式为 “yy-mm-dd”

sql">root@mysqldb 11:06:  [d1]> CREATE TABLE project2 (-> task varchar(30),-> StartTime DATE,-> endtime DATE-> );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 11:12:  [d1]> INSERT INTO project2 VALUES-> ("AA","2023-07-15","2023-08-01"),-> ("BB","2023-07-20 10:00:00","2023-08-02 10:00:00"),-> ("CC","2023-08-16 00:00:00","2023-08-25 00:00:00"),-> ("DD","2023-09-02","2023-09-06"),-> ("EE","2023-09-01 10:00:00","2023-09-05 00:00:00");
Query OK, 5 rows affected, 3 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 3root@mysqldb 11:12:  [d1]> select * from project2;
+------+------------+------------+
| task | StartTime  | endtime    |
+------+------------+------------+
| AA   | 2023-07-15 | 2023-08-01 |
| BB   | 2023-07-20 | 2023-08-02 |
| CC   | 2023-08-16 | 2023-08-25 |
| DD   | 2023-09-02 | 2023-09-06 |
| EE   | 2023-09-01 | 2023-09-05 |
+------+------------+------------+
5 rows in set (0.00 sec)

DATA_ADD

sql">-- 将结束时间推迟1一个月
root@mysqldb 11:36:  [d1]> SELECT task, DATE_ADD(endtime, INTERVAL 1 MONTH) AS TC_endtime FROM project;
+------+---------------------+
| task | TC_endtime          |
+------+---------------------+
| AA   | 2023-09-01 00:00:00 |
| BB   | 2023-09-02 10:00:00 |
| CC   | 2023-09-25 00:00:00 |
| DD   | 2023-10-06 00:00:00 |
| EE   | 2023-10-05 00:00:00 |
+------+---------------------+
5 rows in set (0.00 sec)root@mysqldb 11:37:  [d1]> SELECT task, DATE_ADD(endtime, INTERVAL 1 MONTH) AS TC_endtime FROM project;
+------+---------------------+
| task | TC_endtime          |
+------+---------------------+
| AA   | 2023-09-01 00:00:00 |
| BB   | 2023-09-02 10:00:00 |
| CC   | 2023-09-25 00:00:00 |
| DD   | 2023-10-06 00:00:00 |
| EE   | 2023-10-05 00:00:00 |
+------+---------------------+
5 rows in set (0.00 sec)

CONCAT

sql">root@mysqldb 16:33:  [d1]> SELECT name, num, CONCAT(name, num) AS new_name FROM cars_price;
+---------+------+-----------+
| name    | num  | new_name  |
+---------+------+-----------+
| changan |   23 | changan23 |
| jili    |   24 | jili24    |
| adi     |   22 | adi22     |
| wlai    |   21 | wlai21    |
| lke     |   20 | lke20     |
| bchi    |   23 | bchi23    |
+---------+------+-----------+
6 rows in set (0.00 sec)

UPPER 将参数所有字母转换成大写

sql">root@mysqldb 16:42:  [d1]> SELECT name,UPPER(name) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| changan | CHANGAN  |
| jili    | JILI     |
| adi     | ADI      |
| wlai    | WLAI     |
| lke     | LKE      |
| bchi    | BCHI     |
+---------+----------+
6 rows in set (0.00 sec)-- 只将首字符变成大写
root@mysqldb 16:48:  [d1]> SELECT name, CONCAT(-> UPPER(SUBSTRING(name, 1, 1)), -> LOWER(SUBSTRING(name,2))-> ) -> AS newname_column -> FROM cars_price;
+---------+----------------+
| name    | newname_column |
+---------+----------------+
| changan | Changan        |
| jili    | Jili           |
| adi     | Adi            |
| wlai    | Wlai           |
| lke     | Lke            |
| bchi    | Bchi           |
+---------+----------------+
6 rows in set (0.00 sec)-- 将name字段的值全部改成大写
root@mysqldb 17:19:  [d1]> UPDATE cars_price -> SET name = UPPER(name);
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0root@mysqldb 17:20:  [d1]> select * from cars_price;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| JILI    |   24 | black  |    14 | zdzx |
| ADI     |   22 | red    |    13 | sdfx |
| WLAI    |   21 | green  |    12 | acd  |
| LKE     |   20 | white  |    11 | aa   |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
6 rows in set (0.00 sec)

LOWER 将字段的值变成小写

sql">root@mysqldb 17:21:  [d1]> SELECT name,LOWER(name) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | changan  |
| JILI    | jili     |
| ADI     | adi      |
| WLAI    | wlai     |
| LKE     | lke      |
| BCHI    | bchi     |
+---------+----------+
6 rows in set (0.00 sec)

LPAD 将字符串扩充到指定的长度，并指定用什么字符填充，LPAD，第一个参数需要处理的字符，第二个参数指定的长度，第三个参数指定填充的字符

sql">root@mysqldb 17:22:  [d1]> SELECT name, LPAD(name,10,"*") FROM cars_price;
+---------+-------------------+
| name    | LPAD(name,10,"*") |
+---------+-------------------+
| CHANGAN | ***CHANGAN        |
| JILI    | ******JILI        |
| ADI     | *******ADI        |
| WLAI    | ******WLAI        |
| LKE     | *******LKE        |
| BCHI    | ******BCHI        |
+---------+-------------------+
6 rows in set (0.00 sec)

RPAD 同 LPAD 用法

sql">root@mysqldb 17:44:  [d1]> SELECT name, RPAD(name,10,"-") FROM cars_price;
+---------+-------------------+
| name    | RPAD(name,10,"-") |
+---------+-------------------+
| CHANGAN | CHANGAN---        |
| JILI    | JILI------        |
| ADI     | ADI-------        |
| WLAI    | WLAI------        |
| LKE     | LKE-------        |
| BCHI    | BCHI------        |
+---------+-------------------+
6 rows in set (0.00 sec)

LTRIM 和 RTRIM

sql">root@mysqldb 17:47:  [d1]> SELECT name,LTRIM(name) FROM cars_price;
+---------+-------------+
| name    | LTRIM(name) |
+---------+-------------+
| CHANGAN | CHANGAN     |
| JILI    | JILI        |
| ADI     | ADI         |
| WLAI    | WLAI        |
| LKE     | LKE         |
| BCHI    | BCHI        |
+---------+-------------+
6 rows in set (0.00 secroot@mysqldb 17:48:  [d1]> SELECT name,LPAD(LTRIM(name),20,"*") FROM cars_price;
+---------+--------------------------+
| name    | LPAD(LTRIM(name),20,"*") |
+---------+--------------------------+
| CHANGAN | *************CHANGAN     |
| JILI    | ****************JILI     |
| ADI     | *****************ADI     |
| WLAI    | ****************WLAI     |
| LKE     | *****************LKE     |
| BCHI    | ****************BCHI     |
+---------+--------------------------+
6 rows in set (0.00 sec)root@mysqldb 17:52:  [d1]>  SELECT name,RPAD(RTRIM(name),20,"*") FROM cars_price;
+---------+--------------------------+
| name    | RPAD(RTRIM(name),20,"*") |
+---------+--------------------------+
| CHANGAN | CHANGAN*************     |
| JILI    | JILI****************     |
| ADI     | ADI*****************     |
| WLAI    | WLAI****************     |
| LKE     | LKE*****************     |
| BCHI    | BCHI****************     |
+---------+--------------------------+
6 rows in set (0.00 sec)

REPLACE 需要三个参数，第一个是要搜索的字符串，第二个是搜索的字符，第三个是替换的字符

sql">root@mysqldb 17:56:  [d1]> SELECT name, REPLACE(name,"I","Z") FROM cars_price;
+---------+-----------------------+
| name    | REPLACE(name,"I","Z") |
+---------+-----------------------+
| CHANGAN | CHANGAN               |
| JILI    | JZLZ                  |
| ADI     | ADZ                   |
| WLAI    | WLAZ                  |
| LKE     | LKE                   |
| BCHI    | BCHZ                  |
+---------+-----------------------+
6 rows in set (0.00 sec)

SUBSTR 允许将目标字符串的一部分输出。需要三个参数，第一个参数为目标字符串，第二个字符串是将要输出的字符串的起点，第三个是要输出的字符串的长度。

sql">root@mysqldb 10:09:  [d1]> SELECT name, SUBSTR(name, 2, 2) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | HA       |
| JILI    | IL       |
| ADI     | DI       |
| WLAI    | LA       |
| LKE     | KE       |
| BCHI    | CH       |
+---------+----------+
6 rows in set (0.00 sec)-- 如果第二个参数是负数，将从尾部开始向前定位值负数的绝对值的位置
root@mysqldb 10:12:  [d1]> SELECT name, SUBSTR(name, -3, 2) as new_name FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | GA       |
| JILI    | IL       |
| ADI     | AD       |
| WLAI    | LA       |
| LKE     | LK       |
| BCHI    | CH       |
+---------+----------+
6 rows in set (0.00 sec)root@mysqldb 10:14:  [d1]> SELECT name, CONCAT(-> SUBSTR(name, 1, 2),-> "-",-> SUBSTR(name,3,2)-> )-> AS new_name-> FROM cars_price;
+---------+----------+
| name    | new_name |
+---------+----------+
| CHANGAN | CH-AN    |
| JILI    | JI-LI    |
| ADI     | AD-I     |
| WLAI    | WL-AI    |
| LKE     | LK-E     |
| BCHI    | BC-HI    |
+---------+----------+
6 rows in set (0.00 sec)

INSTR 函数

sql">root@mysqldb 10:31:  [d1]> select name,instr(name,"A") FROM cars_price;
+---------+-----------------+
| name    | instr(name,"A") |
+---------+-----------------+
| CHANGAN |               3 |
| JILI    |               0 |
| ADI     |               1 |
| WLAI    |               3 |
| LKE     |               0 |
| BCHI    |               0 |
+---------+-----------------+
6 rows in set (0.00 sec)

LENGTH 返回字符串的长度

sql">root@mysqldb 10:32:  [d1]> SELECT name, LENGTH(name) FROM cars_price;
+---------+--------------+
| name    | LENGTH(name) |
+---------+--------------+
| CHANGAN |            7 |
| JILI    |            4 |
| ADI     |            3 |
| WLAI    |            4 |
| LKE     |            3 |
| BCHI    |            4 |
+---------+--------------+
6 rows in set (0.00 sec)

http://www.ppmy.cn/devtools/158089.html

相关文章

《从安全到定制：软件私有化部署业务实战案例解析》

《从安全到定制：软件私有化部署业务实战案例解析》

数字化转型时代，企业如何守护数字资产？私有化部署的进阶指南 在数字化转型的浪潮下，数据已成为企业的核心资产。然而，近年来频发的数据泄露事件——从医疗机构的患者信息外泄，到金融行业的交易数据被盗——让越来越多…
阅读更多...
从零到一：基于Rook构建云原生Ceph存储的全面指南（上）

从零到一：基于Rook构建云原生Ceph存储的全面指南（上）

文章目录 一.Rook简介二.Rook与Ceph架构2.1 Rook结构体系2.2 Rook包含组件1）Rook Operator2）Rook Discover3）Rook Agent 2.3 Rook与kubernetes结合的架构图如下2.4 ceph特点2.5 ceph架构2.6 ceph组件 三.Rook部署Ceph集群3.1 部署条件3.3 获取…
阅读更多...
docker和docker compose版本太低问题的解决方案

docker和docker compose版本太低问题的解决方案

你的 docker-compose 运行时遇到了 KeyError: ContainerConfig 错误，这通常与以下几个问题有关： Docker Compose 版本过旧Docker 版本过旧已有的容器缓存损坏Docker daemon 可能存在问题 解决方案 1. 确保 Docker 版本符合要求 运行以下命令查看 Dock…
阅读更多...
Java实现状态模式

Java实现状态模式

一、简介 1、定义 状态模式 (State Pattern)是一种行为型设计模式，允许对象在内部状态改变时改变它的行为。通过定义状态接口和具体状态类，将对象的状态和行为分开，使得状态改变时可以改变行为。 2、状态模式的结构 状态模式涉及以下几个角…
阅读更多...
Linux 僵尸进程与孤儿进程

Linux 僵尸进程与孤儿进程

Linux 僵尸进程与孤儿进程 1. 僵尸进程 (Zombie Process)1.1 什么是僵尸进程1.2 僵尸进程的产生1.3 僵尸进程的危害1.4 如何处理僵尸进程 2. 孤儿进程 (Orphan Process)2.1 什么是孤儿进程2.2 孤儿进程的特点2.3 孤儿进程的应用 在 Linux 系统中，进程是资源分配和调…
阅读更多...
【图片转换PDF】多个文件夹里图片逐个批量转换成多个pdf软件，子文件夹单独合并转换，子文件夹单独批量转换，基于Py的解决方案

【图片转换PDF】多个文件夹里图片逐个批量转换成多个pdf软件，子文件夹单独合并转换，子文件夹单独批量转换，基于Py的解决方案

建筑设计公司在项目执行过程中，会产生大量的设计图纸、效果图、实景照片等图片资料。这些资料按照项目名称、阶段、专业等维度存放在多个文件夹和子文件夹中。 操作需求：为了方便内部管理和向客户交付完整的设计方案，公司需要将每个项目文件…
阅读更多...
我准备做一个24H的摄像机模拟器，用录像视频模拟实时画面，如果能支持时间水印就更好了

我准备做一个24H的摄像机模拟器，用录像视频模拟实时画面，如果能支持时间水印就更好了

之前我不是搞了一个摄像机模拟器吗《用EasyRTSPServer模拟摄像机RTSP流实现RTSP摄像机模拟器 》，搞的比较简单，就是用视频文件模拟摄像机的画面，那个只能简单用来做个IPC模拟，给开发者用用或者给调研的人看看可行性，实…
阅读更多...
Transformer基础 多头自注意力机制

Transformer基础 多头自注意力机制

# 1. **自注意力机制**：Transformer通过自注意力机制能够高效地计算序列内所有元素之间的关系，这使得模型能够捕捉到长距离依赖，无论这些依赖的距离有多远。 # 2. **并行化处理**：与RNN不同，Transformer可以同时处理整…
阅读更多...
最新文章

Copyright @ 2022~2023