我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL核心技术原理之:数据类型与函数

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL核心技术原理之:数据类型与函数

作者:禅与计算机程序设计艺术

1.简介

MySQL是一个开源关系型数据库管理系统,是最流行的关系型数据库管理系统之一。它是一种功能完备、性能卓越、可靠性高、适合各种应用场景的数据库。在本文中,将从数据类型、函数等多个方面详细探讨MySQL的数据结构及相关特性,并通过实例和图表展示核心算法原理,达到对MySQL各项功能的全面认识。

2.MySQL数据类型

2.1 数据类型概览

MySQL支持丰富的数据类型,包括整数类型、浮点数类型、字符串类型、日期时间类型等多种类型。如下所示:

数据类型描述
TINYINT有符号整数 -8+7或无符号整数0255
BOOLtrue/false值
BOOLEAN和BOOL相同,不过此类型已弃用
SMALLINT有符号整数 -32768~+32767
MEDIUMINT有符号整数 -8388608~+8388607
INT有符号整数 -2147483648~+2147483647
INTEGER和INT相同,不过此类型已弃用
BIGINT有符号整数 -9223372036854775808~+9223372036854775807
FLOAT浮点数类型(4字节)
DOUBLE浮点数类型(8字节),精度更高
DECIMAL(M,N)对DECIMAL或NUMERIC列进行精确计算的十进制数字类型。 M代表总共允许的最大位数,N代表小数点右侧的位数。 N的值范围为0至M之间。 例如:decimal(5,2)表示可以存储10^2-1个整数或小数。
DATE年、月、日组成的日期类型
TIME时、分、秒、微秒组成的时间类型
YEAR两位或四位整型数,表示年份
DATETIME年、月、日、时、分、秒组成的日期时间类型
TIMESTAMP自1970年1月1日起过去的秒数(时间戳)
VARCHAR(n)可变长字符串类型,最长长度为n,如varchar(20)
CHAR(n)定长字符串类型,最长长度为n,如char(20)
TEXT可变长字符串类型
BLOB二进制形式的长文本串

除了上述内置的数据类型外,还可以通过创建用户自定义的数据类型实现复杂的数据结构设计,比如定长数组、集合类型、对象类型等。

2.2 数据类型的选择

由于不同的业务需求和查询频率,不同的数据类型会更加有效地提升数据库的处理能力和效率。因此,在实际应用中应根据具体场景选择合适的数据类型。例如:

  • 如果需要存储金额,建议使用DECIMAL或FLOAT类型;
  • 如果需要存储图像、视频、文件,则可以使用BLOB或TEXT类型;
  • 如果需要快速排序和查找,则应该使用更快的索引;
  • 如果需要处理海量数据,则应选择更大的字段类型或更快的硬件配置。

2.3 MySQL数据类型使用规则

当我们创建表时,如果指定了数据类型,MySQL就会根据其数据类型约束其存储空间大小。数据类型越小,MySQL占用的存储空间就越少,查询速度也越快。一般情况下,为了便于维护,数据类型应遵循如下规则:

  1. 在大部分情况下,应尽量使用比较小的数据类型,这样可以减少磁盘空间的消耗,提高查询速度。
  2. 小数类型应避免使用FLOAT和DOUBLE类型,而是优先考虑使用DECIMAL或NUMERIC类型。
  3. 使用TIMESTAMP类型时,应注意时区设置是否正确。
  4. 对于那些经常作为关联键使用的字段,应尽可能使用较大的整数类型。

3.MySQL函数

MySQL支持丰富的函数,包括聚集函数、窗口函数、字符串函数、日期时间函数等。本节主要介绍一些核心的函数,并通过实例和图表展示核心算法原理。

3.1 聚集函数

聚集函数(aggregate function)用于计算一个或多个值的统计信息,返回单一值。主要包括以下几类:

  • 数学函数:min()、max()、sum()、avg()等。
  • 按列计算的函数:count()、group_concat()等。
  • 分组的函数:count()、sum()、avg()等。

这些函数都非常重要,因为它们能够帮助我们分析和汇总数据。下面介绍一下其中几个常用的函数:

MIN()和MAX()函数

MIN()函数用于获取一张表中的最小值,MAX()函数用于获取最大值。例如,我们想找出一张表中订单数量最少的用户,就可以用如下SQL语句:

SELECT user_id FROM orders WHERE order_amount = (    SELECT MIN(order_amount) FROM orders);

上面的语句中,子查询SELECT MIN(order_amount) FROM orders用来找到最小的订单金额,然后把这个结果作为WHERE条件匹配到对应的用户ID。这种方法非常灵活,可以在不用循环遍历所有记录的情况下,直接定位到唯一的最小值。

SUM()和AVG()函数

SUM()函数用于求和,AVG()函数用于平均值计算。例如,我们想计算一张表中的订单总额,就可以用如下SQL语句:

SELECT SUM(order_amount) AS total_income FROM orders;

AVG()函数的用法类似,只是不需要指定别名:

SELECT AVG(order_amount) AS avg_order_amount FROM orders;

COUNT()函数

COUNT()函数用于统计满足某个条件的记录数量。例如,我们想统计一张表中的用户数量,就可以用如下SQL语句:

SELECT COUNT(*) AS num_users FROM users;

上面语句的意思是,从users表中统计所有的记录数量,并将这个数量赋值给num_users列。

3.2 GROUP BY 语句

GROUP BY语句通常和聚集函数一起使用,用来将结果划分为若干个分组,每个分组对应一组聚集函数计算的结果。例如,假设有一张orders表,我们要查看每一天的订单数量和总金额,就可以用如下SQL语句:

SELECT DATE(date_created), COUNT(*), SUM(order_amount) FROM orders GROUP BY DATE(date_created);

上面的语句中,DATE()函数用于提取订单创建时间的日期部分,GROUP BY语句按照日期分组,然后分别执行COUNT(*)和SUM(order_amount)两个聚集函数,统计每天的订单数量和总金额。

3.3 HAVING 语句

HAVING语句也可以和聚集函数一起使用,但和WHERE语句不同的是,HAVING语句只用于过滤分组后的结果。例如,假设有一张orders表,我们要查看每月的订单平均金额,但是要求总金额大于10万,就可以用如下SQL语句:

SELECT MONTH(date_created), AVG(order_amount) AS monthly_avg FROM orders GROUP BY MONTH(date_created)HAVING SUM(order_amount) > 10000;

上面语句的意思是,按照订单创建时间的月份分组,计算每月的订单平均金额,同时过滤掉总金额小于等于10万的月份。

3.4 窗口函数

窗口函数(window function)是一个特别强大的功能,它提供了对结果集中特定行进行排名、计算差异值等操作的方法。窗口函数支持多种操作,如RANK()、DENSE_RANK()、ROW_NUMBER()、AVG()、STDDEV()等。窗口函数可以帮助我们进行一些复杂的查询,例如,计算当前销售额的前三名客户。

下面介绍一些常用的窗口函数:

RANK()函数

RANK()函数用于对数据集进行排名,它会自动给相同分数的记录分配相同的排名。例如,我们想查看每个销售额的排名,就可以用如下SQL语句:

SELECT customer_name, sale_amount,     RANK() OVER (ORDER BY sale_amount DESC) AS rankFROM sales;

上面的语句首先选取customer_name、sale_amount三个列,然后使用OVER()子句声明窗口,然后用ORDER BY对sale_amount进行降序排序。最后调用RANK()函数,它会自动给相同的sale_amount值进行递增排名。

DENSE_RANK()函数

DENSE_RANK()函数和RANK()函数类似,不同的是,DENSE_RANK()函数不会跳过相同分数的记录,而是会重新排序。例如,假设销售额的分数为1、1、2、2、3、3,那么RANK()函数的输出顺序可能是1、1、3、3、5、5,而DENSE_RANK()函数的输出顺序则是1、2、3、4、5、6。

ROW_NUMBER()函数

ROW_NUMBER()函数用于根据排序条件对行号进行编号,编号的序列从1开始,并且重复的编号不会跳过。例如,我们想给销售额按照降序排序,然后给每个销售额的序号,就可以用如下SQL语句:

SELECT customer_name, sale_amount,     ROW_NUMBER() OVER (ORDER BY sale_amount DESC) AS row_numberFROM sales;

同样地,先声明窗口,然后使用ROW_NUMBER()函数给每个sale_amount值进行排名,编号从1开始。

3.5 字符串函数

字符串函数(string function)用于操作字符串,主要包括字符处理函数和文本处理函数。

字符处理函数

字符处理函数主要包括concat()、insert()、replace()、trim()、left()、right()、substring()等函数。

CONCAT()函数

CONCAT()函数用于连接两个或多个字符串。例如,我们想将姓氏和名字组合成一个完整的名字,就可以用如下SQL语句:

SELECT concat(last_name, ',', first_name) AS full_name FROM customers;

上面的语句中,concat()函数接受两个参数,第一个参数为last_name,第二个参数为逗号和空格,最后生成full_name列,包含了完整的名字。

INSERT()函数

INSERT()函数用于向字符串插入子串。例如,我们想将customer_phone列中的星号替换为'-',就可以用如下SQL语句:

UPDATE customers SET customer_phone=REPLACE(customer_phone, '*', '-') WHERE customer_phone LIKE '%*%';

上面的语句中,REPLACE()函数用于替换customer_phone中的星号,LIKE '%*%'用于筛选出customer_phone中含有星号的记录。

REPLACE()函数

REPLACE()函数用于替换字符串中的某些字符。例如,我们想将customer_email列中的@替换为' AT ',就可以用如下SQL语句:

UPDATE customers SET customer_email=REPLACE(customer_email, '@','AT ') WHERE customer_email LIKE '%@%';

同样地,LIKE '%@%'用于筛选出customer_email中含有'@'的记录。

TRIM()函数

TRIM()函数用于删除字符串两端的空白字符。例如,我们想删除顾客的手机号码末尾的空格,就可以用如下SQL语句:

UPDATE customers SET customer_phone=TRIM(customer_phone) WHERE customer_phone <> '';

TRIM()函数的参数为空白字符,所以这里的'<>'就是说不为空白字符。

LEFT()函数和RIGHT()函数

LEFT()函数和RIGHT()函数用于截取字符串的一端。例如,我们想从顾客的手机号码中间位置截取后四位,就可以用如下SQL语句:

SELECT SUBSTRING(customer_phone, LENGTH(customer_phone)-3, 4) AS last_four FROM customers;

上面的语句中,SUBSTRING()函数用于从customer_phone中截取最后三位字符,然后再减去三个来得到最后四位,最后生成last_four列。

SUBSTRING()函数

SUBSTRING()函数用于提取字符串的一部分。例如,我们想从顾客的手机号码中间位置截取后四位,就可以用如下SQL语句:

SELECT SUBSTRING(customer_phone, 7, 4) AS middle_four FROM customers;

上面的语句中,SUBSTRING()函数的第一个参数为customer_phone,第二个参数为7,第三个参数为4,表示提取中间的四位。

文本处理函数

文本处理函数主要包括LENGTH()、LOWER()、UPPER()、LPAD()、RPAD()、INSTR()、REGEXP_REPLACE()等函数。

LENGTH()函数

LENGTH()函数用于返回字符串的长度。例如,我们想查看顾客的手机号码长度,就可以用如下SQL语句:

SELECT customer_name, LENGTH(customer_phone) AS phone_length FROM customers;
LOWER()函数和UPPER()函数

LOWER()函数用于将字符串转换为小写,UPPER()函数用于将字符串转换为大写。例如,我们想将顾客的电子邮件地址全部转换为小写,就可以用如下SQL语句:

UPDATE customers SET customer_email=LOWER(customer_email) WHERE customer_email IS NOT NULL;

上面的语句中,LOWER()函数将NULL值忽略。

LPAD()函数和RPAD()函数

LPAD()函数和RPAD()函数用于左填充和右填充字符串,使得字符串的总长度达到指定的长度。例如,我们想将顾客的手机号码左边补零,才能保持统一的格式,就可以用如下SQL语句:

SELECT LPAD(customer_phone, 12, '0') AS padded_phone FROM customers;

上面的语句中,LPAD()函数的第一个参数为customer_phone,第二个参数为12,第三个参数为'0',表示填充0到总长度为12的字符。

INSTR()函数

INSTR()函数用于查找子串出现的位置。例如,我们想知道顾客的姓名首字母在哪个位置,就可以用如下SQL语句:

SELECT customer_name, INSTR(customer_name,'') + 1 AS initial_position FROM customers;

上面的语句中,INSTR()函数的第一个参数为customer_name,第二个参数为' ',表示查找空格字符的位置。然后再加1得到初始位置。

REGEXP_REPLACE()函数

REGEXP_REPLACE()函数用于替换字符串中的子串,它的语法形式如下:

REGEXP_REPLACE(str, pattern, repl[, position [,occurrences]])

其中,pattern表示正则表达式模式,repl表示替换字符串,position表示搜索的起始位置,默认为0,occurrences表示搜索次数,默认是全局替换。例如,我们想把顾客的姓名中带括号的内容替换为空白字符,就可以用如下SQL语句:

UPDATE customers SET customer_name=REGEXP_REPLACE(customer_name, '\(.*?\)', '') WHERE customer_name LIKE '%(...)%';

上面的语句中,REGEXP_REPLACE()函数的第一个参数为customer_name,第二个参数为'(.*?)',表示匹配括号中的任意内容,第三个参数为空白字符'',表示替换为空白字符。LIKE '%(...)%'用于筛选出包含括号的姓名。

4.MySQL函数和数据的结合应用

通过结合不同的函数,我们可以对MySQL的表格数据进行各种操作。举例来说,假设有一个订单表orders,里面包含订单号、客户名称、商品名称、购买价格、数量、订单金额、下单日期。如果我们想统计最近三个月的订单数量、金额、商品种类分布,就可以用如下SQL语句:

SELECT date_format(date_created,'%Y-%m'), count(*) as order_count,   sum(order_amount) as amount,  group_concat(distinct product_name ORDER BY product_name ASC SEPARATOR '/') as distinct_productsFROM orders o JOIN products p ON o.product_id = p.product_idWHERE date_created >= DATE_SUB(NOW(), INTERVAL 3 MONTH)GROUP BY date_format(date_created,'%Y-%m');

上面的语句中,首先用JOIN命令连接orders和products表,根据产品ID关联订单和产品。然后用WHERE子句过滤出近三个月的订单,用GROUP BY语句按照订单创建日期的月份进行分组。最后用aggregate函数计算订单数量、订单金额、商品种类分布。group_concat()函数用于合并产品名称,separator参数用于分隔不同产品名称。

5.未来发展趋势与挑战

随着互联网应用的普及和发展,数据库正在成为企业级应用中不可缺少的组件。随着云平台的发展,数据库服务的需求也越来越强烈。但与此同时,我们也要看到数据库技术正在逐步走向成熟,新版本更新迭代很快,但不幸的是,其性能仍然无法跟上应用发展的脚步。因此,如何提升数据库的处理性能,以及优化运行效率、稳定性,仍然是数据库领域的一个重要课题。

来源地址:https://blog.csdn.net/universsky2015/article/details/132798232

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL核心技术原理之:数据类型与函数

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

Python数据类型转换函数的原理与方法的深入探究

深入了解Python数据类型转换函数的原理与方法,需要具体代码示例Python是一种简洁而强大的编程语言,它提供了丰富的数据类型和灵活的类型转换函数,使得数据处理更加便捷。在本文中,我们将深入探讨Python中的数据类型转换函数的原理和使
Python数据类型转换函数的原理与方法的深入探究
2024-01-20

mysql数据类型和字段属性原理与用法详解

本文实例讲述了mysql数据类型和字段属性。分享给大家供大家参考,具体如下: 本文内容:数据类型数值类型整数型浮点型定点型日期时间类型字符串类型补充:显示宽度与zerofll记录长度字段属性空\不为空值:NULL、NOT NULL主键:pr
2022-05-22

编程热搜

目录