MySQL函数、AVG | MIN | MAX | COUNT | SUM、慢查询

2023-12-29 23:52

本文主要是介绍MySQL函数、AVG | MIN | MAX | COUNT | SUM、慢查询,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

MySQL函数、慢查询

    • 1、函数
      • 1.日期函数
      • 2.两个日期的时间差
      • 3.查询距离时间的间隔时间
      • 4.常用的字符串函数
      • 5.常用的数学函数
      • 6.常用的聚合函数
    • 2、慢查询
      • 1.什么是慢查询?

从需求出发,在数据的操作过程中经常会有以下的问题;求和、最大值、最小值、四舍五入、日期、平均值等,而函数的出现正是为了解决这些问题。

1、函数

函数的分类:

  1. 数学函数:用作常规的数学运算
  2. 字符串函数:对于字符串类型的字段处理
  3. 日期时间函数:对于日期和时间类型的字段进行处理
  4. 聚合函数:常用于GROUP BY从句的SELECT查询中
  5. 自定义函数:根据实际需求自定义函数

1.日期函数

函数名返回值函数名返回值
CURDATE()返回当前的日期HOUR(time)返回time的小时值(0~23)
CURTIME()返回当前的时间MINUTE(time)返回time的分钟值(0~59)
NOW()返回当前的日期和时间SECOND(time)返回time的秒(0~59)
DAYOFWEEK(date)返回date所代表的一星期中的第几天(1~7)DATE_FORMAT(date,fmt)依照指定的fmt格式格式化日期date值
DAYOFMONTH(date)返回date是一个月的第几天(1~31)WEEK(date)返回日期date为一年中第几周(0~53)
DAYOFYEAR(date)返回date是一年的第几天(1~366)DAY(date)返回date的日
MONTH(date)返回date的月份值(1~12)YEAR(date)返回日期date的年份(1000~9999)
eg:
# 函数
-- 日期函数
select curdate()
select curtime()
select now()
select month(now())
select day(now())
select year(now())
select date_format(now(),'%a-%b-%c')

2.两个日期的时间差

DATEDIFF(expr1,expr2)只能算天的差select datediff(now(), '2020-9-1 15:30:30');select datediff('2020-9-1 15:30:30',now());
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)年月日时分秒的差
-- year
--month
-- DAYselect TIMESTAMPDIFF(DAY,now(),'2020-9-1 15:30:30')select TIMESTAMPDIFF(DAY,'2020-9-1 15:30:30',now())
-- HOURselect TIMESTAMPDIFF(HOUR,now(),'2020-9-1 15:30:30')select TIMESTAMPDIFF(HOUR,'2020-9-1 15:30:30',now())
-- MINUTEselect TIMESTAMPDIFF(MINUTE,now(),'2020-9-1 15:30:30')select TIMESTAMPDIFF(MINUTE,'2020-9-1 15:30:30',now())
-- SECONDselect TIMESTAMPDIFF(SECOND,now(),'2020-9-1 15:30:30')select TIMESTAMPDIFF(SECOND,'2020-9-1 15:30:30',now())

3.查询距离时间的间隔时间

-- 根据时间间隔计算时间
A.T_DATE = B.T_DATE+ interval 1 -- hour——即 A表时间 = B表时间 + 1小时,也可以为分钟、秒。
# 或者
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
时间间隔字面量说明
INTERVAL 1 YEAR时间间隔为1年
INTERVAL 2 Month时间间隔为2月
INTERVAL 3 DAY时间间隔为3天
INTERVAL 4 HOUR时间间隔为4小时
INTERVAL 25 MINUTE时间间隔为25分钟
INTERVAL 45 SECOND时间间隔为45秒
select NOW(), NOW() + INTERVAL 1 year 
select DATE_ADD(NOW(),INTERVAL 1 year)

4.常用的字符串函数

函数名返回值函数名返回值
ASCII(char)返回字符的ASCII码值LTRIM(str)从字符串str中切掉开头的空格
BIT_LENGTH(str)返回字符串的比特长度TRIM(str)去除字符串首部和尾部的所有空格
CONCAT(s1,s2…,sn)将s1,s2…,sn连接成字符串QUOTE(str)用反斜杠转义str中的单引号
CONCAT_WS(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串,并用sep字符间隔POSITION(substr in str)返回子串substr在字符串str中第一次出现的位置,从1开始
LOWER(str)返回将字符串str中所有字符改变为小写后的结果UPPER(str)返回将字符串str中所有字符转变为大写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符STRCMP(s1,s2)比较字符串s1和s2
LENGTH(s)返回字符串str中的字符数REVERSE(str)返回颠倒字符串str的结果
-- 字符函数
select concat('hello','你好','!')
select concat_ws(',','hello','你好','!')

5.常用的数学函数

函数名返回值函数名返回值
ABS(x)返回x的绝对值LOG(x,y)返回x的以y为底的对数
BIN(x)返回x的二进制MOD(x,y)返回x/y的模(余数)
CEILING(x)返回大于x的最小整数值PI()返回pi的值(圆周率)
EXP(x)返回值e(自然对数的底)的x次方SIGN(x)返回代表数字x的符号的值
FLOOR(x)返回小于x的最大整数值ROUND(x,y)返回参数x的四舍五入的有y位小数的值
GREATEST(x1,x2,…,xn)返回集合中最大的值RAND()返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成
LEAST(x1,x2,…,xn)返回集合中最小的值SQRT(x)返回一个数的平方根
LN(x)返回x的自然对数TRUNCATE(x,y)返回数字x截短为y位小数的结果
-- 数学函数
-- 向上取整
select ceiling(3.111)
-- 向下取整
select floor(3.111)
-- 四舍五入,保留一位小数
select round(3.988,1)
-- 截断 结果3.99
select truncate(3.998,2)

6.常用的聚合函数

函数名返回值
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col)返回由属于一组的列值连接组合而成的结果

2、慢查询

1.什么是慢查询?

MySQL默认10秒内没有响应SQL结果,则为慢查询;这个时间是可以修改的。

MySQL的慢查询的操作

-- 显示到mysql数据库的连接数
show status like 'connections';
-- 查看慢查询的状态
Show variables like '%slow_query%';
-- 设置慢查询的到表 mysql.slow_log
set global log_output='TABLE';
-- 设置慢查询的时间
set global long_query_time=3;
-- 开启慢查询
set global slow_query_log='ON';
-- 慢查询的次数
show status like 'slow_queries';
-- 慢查询记录
select * From mysql.slow_log ;
-- 慢查询sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log
-- 关闭慢查询
set global slow_query_log='OFF';

show index FROM student执行该语句;结果中的cardinality是覆盖项;cardinality/count(*) 越接近1 越好 越接近0 越差;或是distinct(ename) / count(*);两者的结果相近。index_type是索引类型:使用的是Btree / B+tree

在这里插入图片描述

当我们在查询前能可以先估计查询究竟要涉及多少行、使用哪些索引、运行时间,mysql提供了相应的功能和语法来实现该功能。在SQL语句前加一个"EXPLAIN"即可。 比如我们要分析如下SQL语句:

explain select * from table where table.id = 1

类似于下面的表:

在这里插入图片描述

EXPLAIN列的解释:

table

显示这一行的数据是关于哪张表的

type

这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL

说明:不同连接类型的解释(按照效率高低的顺序排序)

system:表只有一行:system表。这是const连接类型的特殊情况。

const :表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待。

eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用。

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况。

index:这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)。

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免。

possible_keys

显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息。是Using temporary和Using filesort,意思MYSQL根本不能使用索

引,结果是检索会很慢

说明:extra列返回的描述的意义

Distinct :一旦mysql找到了与行相联合匹配的行,就不再搜索了。

Not exists :mysql优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。

Range checked for each Record(index map:#) :没有找到理想的索引,因此对从前面表中来的每一个行组合,mysql检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort :看到这个的时候,查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using index :列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary :看到这个的时候,查询需要优化了。这里,mysql需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上。

Where used :使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题。

这篇关于MySQL函数、AVG | MIN | MAX | COUNT | SUM、慢查询的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/551083

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

活用c4d官方开发文档查询代码

当你问AI助手比如豆包,如何用python禁止掉xpresso标签时候,它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就