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

news/2025/2/11 10:00:00/

对函数的处理

新建一个成绩表

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/news/1571131.html

相关文章

【JavaEE进阶】Spring MVC(1)

欢迎关注个人主页:逸狼 创造不易,可以点点赞吗 如有错误,欢迎指出~ Maven Maven是⼀个项⽬管理⼯具,通过pom.xml⽂件的配置获取jar包,⽽不⽤⼿动去添加jar包. Maven提高了我们的开发效率,减少了bug,Maven提供的功能非常多,我们主…

LINUX——内核

引言 Linux 内核(Kernel)是操作系统的核心,负责管理计算机的硬件资源并为用户空间程序提供基础服务。它是 Linux 生态的“心脏”,驱动着从嵌入式设备到超级计算机的各类系统。理解 Linux 内核的设计原理和核心机制,是…

教程 | Hadoop极简部署指南(Docker-Compose版)

📦 前置环境准备 1. 安装Docker 安装依赖工具 sudo yum -y install yum-utils配置阿里云镜像源(国内加速) sudo yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo安装社区版Docker sudo yu…

如何开发Vue组件:简明教程

如何开发Vue组件:简明教程 什么是Vue组件?创建Vue组件的基本步骤示例:创建一个简单的按钮组件 什么是Vue组件? Vue.js是一个用于构建用户界面的渐进式框架。Vue组件是Vue应用的基础构建块,它们允许你将UI拆分为独立且…

前端项目打包完成后dist本地起node服务测试运行项目

1、新建文件夹 node-test 将打包dist 文件同步自定义本地服务文件夹node-test 中,安装依赖包。 npm install express serve-static cors 2、新创建服务文件js server.js 构建链接及端口 const express require(express); const path require(path); const co…

JavaScript:CPU缓存预取以及确定数据下直接更改数组length的好处

CPU缓存预取以及确定数据下直接更改数组length的好处 1. CPU 缓存预取(Cache Preloading):CPU 缓存预取:为什么反向填充栈能利用缓存预取: 2. 为什么可以直接改变数组的 length:数组的动态长度:…

深度优先搜索(DFS)——八皇后问题与全排列问题

( ^ _ ^ ) 数据结构好难哇(哭 1.BFS和DFS 数据结构空间性质DFSstackO(h)不具有最短性质BFSqueueO(2^h)具有最短路性质 空间上DFS占优势,但是BFS具有最短性 (若所有权重都是1,则BFS一定最短)&…

科技资讯杂志科技资讯杂志社科技资讯编辑部2024年第24期目录

学思践悟二十大 “枫桥经验”的思想政治教育内涵及启示——践行党的二十大精神 洪希彦; 1-330 构建符合党的二十大精神的高职院校劳动教育课程体系研究 李曼; 4-7 党的二十大精神引领下“隧道施工”课程思政探究 张志明;陈国辉; 8-10 新质生产力 新质生产力视域…