mysql中的函数

最新推荐文章于 2024-01-27 23:06:40 发布
最新推荐文章于 2024-01-27 23:06:40 发布
阅读量205 收藏
点赞数
分类专栏： Mysql学习 文章标签： mysql 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43761240/article/details/125252287
版权

函数

数据处理函数/单行处理函数

数据处理函数又被称为单行处理函数

单行处理函数的特点：一个输入对应一个输出。

和多行处理函数相对的是：多行处理函数。

多行处理函数的特点：多个输入对应一个输出。

在这里插入图片描述
在这里插入图片描述

lower转小写
mysql> select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
14 rows in set (0.09 sec)

mysql> select lower(ename) as name from emp;
+--------+
| name   |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.04 sec)
substr截取字符串
mysql> select substr(ename,1,1) as ename from emp;
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+
14 rows in set (0.06 sec)
concat拼接字符串
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+
14 rows in set (0.03 sec)
首字母转小写
mysql> select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename)-1)) as result from emp;
+--------+
| result |
+--------+
| sMITH  |
| aLLEN  |
| wARD   |
| jONES  |
| mARTIN |
| bLAKE  |
| cLARK  |
| sCOTT  |
| kING   |
| tURNER |
| aDAMS  |
| jAMES  |
| fORD   |
| mILLER |
+--------+
14 rows in set (0.05 sec)
length 查询长度
mysql> select length(ename) from emp;
+---------------+
| length(ename) |
+---------------+
|             5 |
|             5 |
|             4 |
|             5 |
|             6 |
|             5 |
|             5 |
|             5 |
|             4 |
|             6 |
|             5 |
|             5 |
|             4 |
|             6 |
+---------------+
14 rows in set (0.05 sec)
去除空格
mysql> select * from emp where ename = trim('  SMITH');
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.50 sec)
ifnull函数

计算每个员工的年薪

mysql> select ename,(sal+comm)*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  | NULL     |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | NULL     |
| MARTIN | 31800.00 |
| BLAKE  | NULL     |
| CLARK  | NULL     |
| SCOTT  | NULL     |
| KING   | NULL     |
| TURNER | 18000.00 |
| ADAMS  | NULL     |
| JAMES  | NULL     |
| FORD   | NULL     |
| MILLER | NULL     |
+--------+----------+
14 rows in set (0.05 sec)

因为comm存在null，而null参与的所有运算结果为null。

使用ifnull

如果津贴comm为null，将comm当做0处理

mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.05 sec)
case…when…then…when…then…else…end

当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%，其它正常。

mysql> select ename,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.05 sec)

分组函数（多行处理函数）

多行处理函数的特点：输入多行，最终输出一行

​ 五个：

  1. count 计数

  2. sum 求和

  3. avg 平均值

  4. max 最大值

  5. min 最小值

    注意：

    分组函数在使用的时候必须进行分组，然后使用。

    如果没有对数据进行分组，整张表默认为一组。

找出最高工资
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.19 sec)
找出最低工资
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.11 sec)
计算工资和
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.12 sec)
计算平均工资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.02 sec)
计算员工数量总和
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+
1 row in set (0.02 sec)

注意：分组函数自动忽略null，你不需要提前对null进行处理（count也会忽略null，不会对null计数）

count(*) 和count(具体字段)的区别
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.29 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.02 sec)

count(具体字段)：表示统计该字段下所有不为null的元素的总和。

count(*)：统计表当中的总行数。(只要有一行数据count则++)

分组函数组合使用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.02 sec)

分组查询（非常重要）

什么是分组查询

在实际应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

这个时候我们需要使用分组查询，怎么进行分组查询呢？

select… from… group by…；

select…

from…

where…

group by…

order by…

上面语句关键字执行顺序

  1. from

  2. where

  3. group by

  4. select

  5. order by

    分组函数必须在分组后面执行

    mysql> select ename,sal from emp where sal>min(sal);
1111 - Invalid use of group function

    该语句中先执行where语句而group by在where后面执行，所以错误。

计算每个工作岗位的工资总和
mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.02 sec)
计算每个工作岗位的平均薪资
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.06 sec)
计算每个工作岗位的最高薪资
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.14 sec)
找出每个部门，不同工作岗位的最高工资总和（两个字段联合分组）
mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.03 sec)
使用having可以对分完组的数据进一步进行过滤

having不能单独使用，必须和group by 联合使用

找出每个部门最高工资，要求显示最高工资大于3000的
//找出每个部门的最高工资
mysql> select deptno,max(sal) from emp group by deptno; 
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+
3 rows in set (0.03 sec)
//显示最高工资大于3000
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000; 
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.13 sec)
//以上sql语句效率低，可以先找出大于3000的然后进行分组。
mysql> select deptno,max(sal) from emp where sal>3000 group by deptno; 
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.11 sec)

优化策略：where和having，优先选择where，where实在完成不了的，再选择having

where完成不了的

找出每个部门平均工资，要求显示平均薪资高于2500的

mysql> select deptno,avg(sal) from emp group by deptno; 
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.02 sec)
mysql> select deptno,avg(sal) from emp  group by deptno having  avg(sal)>2500; 
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.03 sec)

where完成不了的

找出每个部门平均工资，要求显示平均薪资高于2500的

mysql> select deptno,avg(sal) from emp group by deptno; 
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.02 sec)
mysql> select deptno,avg(sal) from emp  group by deptno having  avg(sal)>2500; 
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.03 sec)
沐雨~橙风
关注
专栏目录
输出一行n个星号
weixin_46191082的博客
02-21 1万+
题目：从键盘输入一个整数（n<80），输出一行n个星号。 #include <stdio.h> void print_star(int n) { int i; for(i=0;i<n;i++) printf("*"); } int main() { int n; printf("Enter n(<80):"); scanf("%d",&n); ...
mysql数据库如何定义函数_数据库系列之mysql 自定义函数
weixin_29065659的博客
01-28 1779
自定义函数 (user-defined function UDF)就是用一个象ABS() 或 CONCAT()这样的固有(内建)函数一样作用的新函数去扩展MySQL。所以UDF是对MySQL功能的一个扩展创建和删除自定义函数语法:创建UDF:CREATE[AGGREGATE]FUNCTIONfunction_name(parameter_name type,[parameter_name ty...
从键盘上任意输入一个数，并输出相应行数的＊号，例如输入5，则显示5行＊号组成一个三角形
热门推荐
u010912122的专栏
10-22 1万+
例如输入5，则输出结果如下：       *    ***   *****  ******* *********      #include int main(int argc, const char * argv[]) {     int n,i,j;     scanf("%d",&n);     for (i=1; i     {
MySQL常用基本命令(自整理)
qq_43511592的博客
08-25 386
1、MySQL连接方式 mysql.exe/mysql [-h主机地址] [-P端口] -u用户名 -p密码 1）通常端口都可以默认：mysql默认端口通常是3306 2）密码的输入可以先输入-p，直接换行，然后再以密文方式输入密码 2、GRANT介绍 mysql>grant all privileges ON *.* TO 'root'@'%' -->identified by '数据库密码' with grant option; GRANT命令说明：...
基本数据类型和表达式（下）7-3 输入一个整数，在一行中输出相应个数的星号。 (5 分)
qq_63782675的博客
11-11 588
输入样例: 5 结尾无空行 输出样例: ***** 结尾无空行 我的答案： #include <stdio.h> #include <math.h> int main() { int n, i; scanf("%d",&n); for(i=1;i<=n;i++) { printf("*"); } return 0; } ...
Sql Server 字段结果显示为*
weixin_61070080的博客
08-04 912
Sql serve 字段结果显示为*
Mysql函数手册.rar_MySQL函数手册_VZI_mysql 函数手册
09-15
此外，VZI版的MySQL函数手册可能会有额外的注解、实用技巧和常见问题解答，这些都是作者在实际工作中积累的经验，对于新手和有经验的开发者来说都非常有价值。 在使用手册时，应结合实际的数据库操作环境，通过阅读...
MySQL自定义函数简单用法示例
12-15
本文将通过实例介绍如何在MySQL中创建和使用自定义函数，包括基本用法、分支结构和循环结构。 首先，我们来看一个简单的自定义函数示例——将日期时间格式转换为中文格式： ```sql DELIMITER $$ -- 定义新的语句...
MySQL8.0 函数官方文档 functions.html
05-21
MySQL8.0 函数官方文档 functions.html
mysql常用函数汇总.chm
08-14
Mysql的常用函数整体, 从网上收集的一些常用函数, 进行汇总整体成文档 MySQL控制流函数： CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default CASE [test] WHEN...
Mysql中的运算符详解。
最新发布
qq_58626489的博客
01-27 1511
总结Mysql运算符详细含义及用法。
MySQL-数据处理函数
WDNMDnm的博客
10-25 2836
MySQL-数据处理函数数据处理函数常见的单行处理函数常见的分组函数分组查询 数据处理函数 数据处理函数又称为单行处理函数；单行处理函数特点：一个输入对应一个输出。和单行处理函数相对的是多行处理函数：多个输入对应一个输出。 常见的单行处理函数 Lower 转换小写 upper 转换大写 substr 取子串 用法：substr(被截取的字符串，起始下标，截取的长度) concat 字符串拼接 length 取长度 trim 去空格 str_to_date 将字符串转换成日期 date_format
Oracle 字符函数
xty88645的博客
11-19 83
字符函数是oracle中最常用的函数，我们来看看有哪些字符函数： lower(char)：将字符串转化为小写的格式。upper(char)：将字符串转化为大写的格式。 length(char)：返回字符串的长度。substr(char,m,n)：取字符串的子串n代表取n个的意思，不是取到第n个 replace(char1,search_string,replace_string)  问题：...
数据库SQL Server2012笔记（八）——Statement与PreparedStatement的区别，JDBC方式操作数据库
shangqing1123的博客
08-25 1117
1、Statement与PreparedStatement的区别 1）都可用于  把sql语句从java程序中发送到制定数据库，并执行sql语句。 2）区别 直接使用Statement，驱动程序一般不会对sql语句做处理，而直接交给数据库。使用PreparedStatement，形成预编译的过程，并且会对语句做字符集的转换（至少在sql  server中如此）。好处：对于多次重复执行
数据库函数
www_zxf_csdn123的博客
06-21 509
函数 日期函数 获得年月日 获得时分秒 获得时间戳 在日期的基础上加上日期 在日期的基础上减去日期 计算两个日期之间相差多少天 案例1： 创建一张表，记录生日 添加当前 日期 案例2： 创建一个留言表 插入数据 显示所有留言信息，发布日期只显示日期，不用显示时间 查询在2分钟内发布的帖子 字符串函数 案例： 获取emp表的ename列的字符集 select charset(ename) from EMP; 要求显示student表中的信息，显示格式：“XX的语文是XX分，数学是
修改Mysql字段为首字母大写
三水小鱼干的博客
07-24 1445
修改某字段的内容，使其首字符大写，用于英文姓名 首先解释用到的函数：   CONCAT(str1,str2) 字符连接函数   UPPER(str) 将字符串改为大写字母   LOWER(str) 将字符串改为小写字母   LENGTH(str) 判定字符串长度   SUBSTRING(str,a,b) 提取字段中的一段，从字符串str的第a位开始提取，提取b个字符   LEFT(str,n) 提取字符串最左边的n个字符   RIGHT(
Hive查询及 Hive排序
csdnwindnorth的博客
09-17 996
查询 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select 全表和特定列查询： 全表查询 select * from emp; 选择特定列查询 select empno, ename from emp; 注意： （1）SQL 语言大小写不敏感。 （2）SQL 可以写在一行或者多行。 （3）关键字不能被缩写也不能...
查询首字母大写的所有雇员的姓名
小小的花园里面挖呀挖呀～
12-27 7088
select ENAME from EMP where ASCII(SUBSTRING(ENAME,1,1)) >= 65 and ASCII(SUBSTRING(ENAME,1,1))
MYSQL中函数不存在怎么弄
04-06
如果在MySQL中找不到所需的函数，可以考虑以下几个步骤： 1. 检查MySQL版本：确保你使用的MySQL版本支持该函数。一些较老的版本可能不支持某些函数，或者需要使用特定的语法或参数。 2. 检查函数名称：确认函数的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值