广告位联系
返回顶部
分享到

MySQL函数全面总结详解示例分析教程

Mysql 来源:互联网 作者:佚名 发布时间:2024-02-16 09:09:17 人浏览
摘要

1.MySQL中关于函数的说明 概念:类似java、python中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名; 好处:Ⅰ隐藏了实现细节;Ⅱ提高代码的重用性; 调用:select 函数名(实参列表

1.MySQL中关于函数的说明

"概念":类似java、python中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名;

"好处":Ⅰ隐藏了实现细节;Ⅱ提高代码的重用性;

"调用":select 函数名(实参列表) [from 表];

"特点":Ⅰ叫什么(函数名);Ⅱ干什么(函数功能);

"分类":Ⅰ单行函数;Ⅱ分组函数;

"什么是单行函数:":作用于表中的每一行记录,一条记录出来一个结果;

"什么是聚合函数:":作用于一行或者多行,最终返回一个结果;

2.单行函数分类

字符函数;

数学函数;

日期函数;

其他函数;

流程控制函数;

3.字符函数

1)length(str):获取参数值的字节个数;

对于utf8字符集来说,一个英文占1个字节;一个中文占3个字节;

对于gbk字符集来说,一个英文占1个字节;一个中文占2个字节;

操作如下 :

在这里插入图片描述

2) concat(str1,str2,…):拼接字符串;

操作如下:

在这里插入图片描述

3)upper(str):将字符中的所有字母变为大写;

操作如下:

在这里插入图片描述

4)lower(str) :将字符中所有字母变为小写;

操作如下:

在这里插入图片描述

5)substr(str,start,len):从start位置开始截取字符串,len表示要截取的长度; 没有指定len长度:表示从start开始起,截取到字符串末尾。指定了len长度:表示从start开始起,截取len个长度。

操作如下:

在这里插入图片描述

6)instr(str,要查找的子串) :返回子串第一次出现的索引,如果找不到,返回0; 当查找的子串存在于字符串中:返回该子串在字符串中【第一次】出现的索引。当查找的子串不在字符串中:返回0。

操作如下:

在这里插入图片描述

7)trim(str):去掉字符串前后的空格; 该函数只能去掉字符串前后的空格,不能去掉字符串中间的空格。

操作如下:

在这里插入图片描述

8)lpad(str,len,填充字符):用指定的字符,实现对字符串左填充指定长度;

操作如下:

在这里插入图片描述

9)rpad(str,len,填充字符):用指定的字符,实现对字符串右填充指定长度;

操作如下:

在这里插入图片描述

10) replace(str,子串,另一个字符串):将字符串str中的字串,替换为另一个字符串;

操作如下:

在这里插入图片描述

4.数学函数

1)round(x,[保留的位数]) :四舍五入; 当对正数进行四舍五入:按照正常的计算方式,四舍五入即可。当对负数进行四舍五入:先把符号丢到一边,对去掉负号后的正数进行四舍五入,完成以后,再把这个负号,补上即可。

操作如下:

在这里插入图片描述

2)ceil(x):向上取整,返回>=该参数的最小整数。(天花板函数) 天花板函数,在excel,python中均存在这个函数。你就想象一下你家的天花板,把这个数字丢到天花板上,求的是大于等于这个数字的最小整数。

操作如下:

在这里插入图片描述

3)floor(x):向下取整,返回<=该参数的最大整数。(地板函数) 地板函数:在excel,python中均存在这个函数。你就想象一下你家的地板,把这个数字丢到地板上,求的是小于等于这个数字的最大整数。

操作如下:

在这里插入图片描述

4)truncate(x,D) :截断; truncate函数,也是在excel,python中均存在,含义基本都是一致的。但是这个函数理解起来有点难,我这里带着大家好好学习一下。

理解如下:

"参考下面的示例图,体会如下文字"
1)D是正数,操作的是小数点右侧的小数部分。 
      D=1,直接从第1个位置处,砍掉后面的部分。
      D=2,直接从第2个位置处,砍掉后面的部分。
 ......
  

2)D是0,直接去掉小数部分。

3)D是负数,操作的是小数点左侧的整数部分。
   D=-1,直接从-1位置处,先砍掉后面的小数部分,
   并且"从当前位置起(包括当前位置),后面整数部分替换为0"。
   D=-2,直接从-2位置处,先砍掉后面的小数部分,
   并且"从当前位置起(包括当前位置),后面整数部分替换为0"。

示例图:

在这里插入图片描述

操作如下:

在这里插入图片描述

5)mod(被除数,除数) :取余; 当被除数为正数,结果就是正数。当被除数为负数,结果就是负数。

操作如下:

在这里插入图片描述

5.日期时间函数

日期的含义:指的是我们常说的年、月、日。

时间的含义:指的是我们常说的时、分、秒。

在讲述下面函数之前,我们先补充一个知识,不同时间格式符表示什么含义呢?

在这里插入图片描述

1)now() :返回系统当前的日期和时间;

操作如下:

在这里插入图片描述

2)curdate() :只返回系统当前的日期,不包含时间;

操作如下:

在这里插入图片描述

3)curtime() :只返回系统当前的时间,不包含日期;

操作如下:

在这里插入图片描述

4)获取日期和时间中年、月、日、时、分、秒; 

 获取年份:year();

获取月份:month();

获取日:day();

获取小时:hour();

获取分钟:minute();

获取秒数:second();

5)weekofyear() :获取当前时刻所属的周数;

操作如下:

在这里插入图片描述

6)quarter() :获取当前时刻所属的季度;

操作如下:

在这里插入图片描述

7)str_to_date() :将日期格式的字符串,转换成指定格式的日期;

操作如下:

在这里插入图片描述

8)date_format() :将日期转换成日期字符串; %Y-%m-%d返回的月份是01,02…这样的格式。%Y-%c-%d返回的月份是1,2…这样的格式。

操作如下:

在这里插入图片描述

9)date_add() + interval :向前、向后偏移日期和时间;

在这里插入图片描述

操作如下:

在这里插入图片描述

10)last_day() :提取某个月最后一天的日期;

操作如下:

在这里插入图片描述

11)datediff(end_date,start_date) :计算两个时间相差的天数;

操作如下:

在这里插入图片描述

12)timestampdiff(unit,start_date,end_date) :计算两个时间返回的年/月/天数;

unit参数是确定(start_date,end_date)结果的单位,表示为整数,以下是有效单位:

year:年份

month:月份

day:天

hour:小时

minute 分钟

second:秒

microsecond:微秒

week:周数

quarter:季度

YEAR:年份

操作如下:

在这里插入图片描述

6.其它常用系统函数

在这里插入图片描述

7.流程控制函数

1)if函数:实现if-else的效果;

在这里插入图片描述

2)ifnull函数:判断值是否为null,是null用指定值填充;

在这里插入图片描述

3)case…when函数的三种用法;

case … when共有三种用法,我相信自己的总结会相当全面。希望大家一定要好好研究这几种用法,都是很有用的。

等值判断:类似于java中switch case的效果;

区间判断:类似于python中if-elif-else的效果;

case … when和聚合函数联用;

① case … when用作等值判断的语法格式;

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

操作如下:

在这里插入图片描述

② case … when用作区间判断的语法格式;

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

操作如下:

在这里插入图片描述

③ case…when与聚合函数的联用

在这里插入图片描述

利用上述原始表,完成如下问题:

-- 18、查询各科成绩最高分、最低分和平均分,以如下形式显示:
-- 课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为:>=60,中等为:70-80,优良为:80-90,优秀为:>=90

操作如下:

1

2

3

4

5

6

7

8

9

select sc.c,cname,

max(score) 最高分,min(score) 最低分,avg(score) 平均分,

sum(case when score>60 then 1 else 0 end)/count(*) 及格率,

sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) 中等率,

sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) 优良率,

sum(case when score>=90 then 1 else 0 end)/count(*) 优秀率

from sc left join course

on sc.c = course.c

group by sc.c;

结果如下:

在这里插入图片描述

8.聚合函数

1)聚合函数的功能和分类;

① 聚合函数的功能;

用作统计使用,又称为聚合函数或统计函数或组函数。

② 聚合函数的分类; sum 求和avg 平均值max 最大值min 最小值count 计算个数

2)聚合函数的简单使用

在这里插入图片描述

3)五个聚合函数中传入的参数,所支持的数据类型有哪些?

mysql不是强类型的编程语言。也就是说,有些语句执行结果可能不报错,但是执行结果无实际意义,因此,我们也认为是不正确的。

① 测试数据;

1

2

3

4

5

6

7

8

9

10

11

12

13

"建表语句"

create table test(

    id int primary key auto_increment,

    name varchar(20) not null,

    sal int,

    birth date)charset=utf8;

 

"插入数据"

 insert into test(name,sal,birth) values

 ("Zoo",6500,'1993.3.20'),

 ("Hobby",4000,'1997.6.10'),

 ("Aline",5500,'2000.5.1'),

 ("Bob",10000,'2008.10.1');

② sum()函数和avg()函数:传入整型/小数类型才有意义;

在这里插入图片描述

结论如下:

sum()函数和avg()函数对于字符串类型、日期/时间类型的计算都没有太大意义。因此,sum()函数和avg()函数,我们只用来对小数类型和整型进行求和。

③ max()函数和min()函数:传入整型/小数类型、日期/时间类型意义较大;

在这里插入图片描述

结论如下:

max()和min()中传入的是"整型/小数类型",计算的是数值的最大值和最小值。max()和min()中传入的是"日期类型",max()计算的最大值是离我们最近的那个日期,min()计算的最小值是离我们最远的那个日期,这个可以记一下。max()和min()中传入的是字符串类型,max()计算的最大值是按照英文字母顺序显示的,min()计算的最小值也是按照英文字母顺序显示的,意义不太大。

 ④ count()函数:可以传入任何数据类型,但是碰到null要注意;

在这里插入图片描述

结论如下:

    count()函数可以传入任何数据类型,表示对行计数。
    "但是下面的知识点需要特别注意的"
    首先看看count(sal) ,count(birth)这两句表示的是什么意思?这两句分别表示的是
对sal列字段、birth列字段的行数,进行统计。由于其中有一条记录是null值,因此使用count()
函数计数的时候,会忽略掉null行。
    其次,对于count(*)表示的是统计【整个表】有多少行,这个肯定是对原始数据的行数的正确
统计,只要整张表某一行有一个列字段的值不是null,count(*)就会认为该行为1行。当然要是一
整行都是null值,你也没必要插入这条记录。

总结:

   当某个字段列中没有null值,则"count(列字段)=count(*)。"
   当某个字段列中有null值,则"count(列字段)<count(*)。"
   因此,假如你想统计的是整张表的行数,请用count(*)。

其实所有的分组函数都忽略null值的,但上面那个count()函数碰到null值要特别注意。

⑤ count()函数碰到null值需要特别注意;

在这里插入图片描述

结论如下:

对于avg(sal)求平均值来说,(6500+4000+5500+10000)/4=6500。

对于后面这个sum()/count(*)求平均值来说,(6500+4000+5500+10000)/5=5200。

好好体会上述例子,有时候某人成绩虽然记录的是null,但是你仍然有5个人存在,所以你要考虑一下怎么使用合适的函数,达到你想要的结果。

⑥ count(1),count(0)表示的是啥意思呢?

    无论是sum(1),sum(0),count(1),count(0),avg(1),avg(0),原理都是一样的,相当于在原表中新增一列。

    其次,我们知道where后面接的是【逻辑值】,当使用where 1和where 0原理也还是一样,也相当于在原表中新增一列。

    我们只需要记住在mysql中:"非0即为true,0为false"。也就是说,下面的所有是1的地方,你可以换成任何非0数字,都是可以的。

原理图如下:

在这里插入图片描述

测试一下:

在这里插入图片描述

⑦ count(*)计数的效率问题;

MYISAM存储引擎下,count(*)的效率高。INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)效率要高一些。综上所述:优先使用count(*)。

4)聚合函数和group by的使用“最重要”;

关于这个知识点,我们将会在后面的知识点中进行讲述。在这里我们只需要记住一句话:当SQL语句中使用了group by分组函数后,select后面的字段必须是group by后面的字段 + 聚合函数的使用。


版权声明 : 本文内容来源于互联网或用户自行发布贡献,该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权, 违法违规的内容, 请发送邮件至2530232025#qq.cn(#换@)举报,一经查实,本站将立刻删除。
原文链接 :
相关文章
  • MySQL本地版本升级超详细教程(从5.5.20升到8.0.21)

    MySQL本地版本升级超详细教程(从5.5.20升到8.0.21)
    写在前面 升级本地mysql,当前5.5.20,升级前先备份本地的数据库文件 以防数据丢失 一、卸载本地的旧版本 1. 停止MySQL服务 win+R 打开运行,
  • MySQL数据库的多种连接方式及工具介绍

    MySQL数据库的多种连接方式及工具介绍
    一、MySQl命令行客户端 在Mysql安装完成后,Mysql命令行会自动配置在计算机上,在菜单中找到MySql 8.0 Command Line Client,输入密码即可进入。(密
  • MySQL函数全面总结详解示例分析教程

    MySQL函数全面总结详解示例分析教程
    1.MySQL中关于函数的说明 概念:类似java、python中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名; 好处:Ⅰ隐藏了实现细节;Ⅱ
  • MySQL一键安装Shell脚本的实现
    一、脚本说明 1、linux系统版本 EL6, EL7, EL8, and EL9-based platforms (for example, the corresponding versions of Oracle Linux, Red Hat Enterprise Linux, and CentOS),本脚
  • mysql乐观锁和悲观锁的具体使用介绍

    mysql乐观锁和悲观锁的具体使用介绍
    悲观锁介绍(百科): 悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保
  • mysql中TINYINT取值范围的介绍

    mysql中TINYINT取值范围的介绍
    项目场景: 最近在开发过程中,看见表设计中是thinyint字段,但对于它的范围产生了好奇 问题描述: 当我们填写超过128数值的时候,该字段
  • SQL触发器定义与使用介绍
    比如:现需要向学生表中插入新的学生数据。但在插入学生数据的时,需要同时检查老师表里的数据。如果插入学生的老师不在老师表里,
  • Window环境下MySQL UDF提权介绍

    Window环境下MySQL UDF提权介绍
    UDF(Userdefined function)可以翻译为用户自定义函数,其为mysql的一个拓展接口,可以为Mysql增添一些函数,对MySQL的功能进行扩充,然后就可以在
  • 华为云云数据库MySQL的体验流程介绍

    华为云云数据库MySQL的体验流程介绍
    一、MySQL相关概念 1.什么是MySQL MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,属于Oracle旗下产品。MySQL是最流行的关系型数据库
  • Mysql tinyint(1)与tinyint(4)的区别介绍
    什么是tinyint(M)? 先来了解下mysql中字符串类型varchar(M)和数值类型tinyint(M)的区别? 字符串列类型:varchar(M)而言,M是字段中可以存储的最大字
  • 本站所有内容来源于互联网或用户自行发布,本站仅提供信息存储空间服务,不拥有版权,不承担法律责任。如有侵犯您的权益,请您联系站长处理!
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计