新乡营销网站建设公司,百度网站优点,省级门户网站建设,上海闵行网总言 主要内容#xff1a;介绍了MySQL中常用的基本函数。一些聚合函数、时间日期函数、字符串函数、数字函数等。 文章目录 总言1、聚合函数1.1、汇总1.2、COUNT()函数1.2.1、基本说明1.2.2、使用演示 1.3、SUM( )函数1.3.1、基本说明1.3.2、使用演示 1.4、AVG( )函…总言 主要内容介绍了MySQL中常用的基本函数。一些聚合函数、时间日期函数、字符串函数、数字函数等。 文章目录 总言1、聚合函数1.1、汇总1.2、COUNT()函数1.2.1、基本说明1.2.2、使用演示 1.3、SUM( )函数1.3.1、基本说明1.3.2、使用演示 1.4、AVG( )函数1.4.1、基本说明1.4.2、使用演示 1.5、MIN( )函数、MAX( )函数1.5.1、基本说明1.5.2、使用演示 2、时间日期函数2.1、汇总部分2.2、curdate()、current_date()、current_time()、current_timestamp()、now()2.3、date()、date_add()、date_sub()、datediff()2.3.1、date()2.3.2、date_add()、date_sub()2.3.3、datediff ()、timediff() 3、字符串函数3.1、汇总部分3.1、charset()3.2、concat()、concat_ws()3.3、length()3.4、substring ()3.5、left()、right()3.5、instr()3.6、replace()3.7、ucase()、upper()、lcase()、lower()3.8、strcmp()3.9、ltrim()、rtrim()、trim() 4、数学函数4.1、总言4.2、abs()4.3、bin()4.4、hex()4.5、conv()4.6、ceiling()、floor()4.7、format()4.8、rand ()4.9、mod() 5、其它函数5.1、user()5.2、md5()5.3、password()5.4、database()5.5、ifnull() 1、聚合函数
1.1、汇总 1、什么是聚合函数特点 聚合统计一定是直接或者间接统试列方向的某些数据。列的属性相同 2、常见聚合函数
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值不是数字没有意义 1.2、COUNT()函数
1.2.1、基本说明 COUNT()函数用于统计表中符合特定条件的所有行语法如下
COUNT(DISTINCT expression) --去重返回不包含NULL值唯一行数。
COUNT(expression) --不去重返回不包含NULL值的行数。
COUNT(*) --包含NULL值相关说明 1、该函数返回类型为BIGINT 如果没有找到匹配的行则COUNT()函数返回0。 2、count(*)函数返回由SELECT语句返回的结果集中的行数包含NULL和非NULL值的行即所有行。count(*)是 SQL92 定义的标准统计行数的语法跟数据库无关跟 NULL 和非 NULL 无关。 3、count(1)计算结果集中的所有行并将数字1作为参数传递给函数。在大多数数据库系统中包括MySQLCOUNT(1)和COUNT(*)的行为是相同的都是计算所有行。 使用COUNT(1)通常是为了提高查询性能尤其是在某些数据库系统中因为数字1是一个常量不会增加任何额外的解析时间。
1.2.2、使用演示 1、基础使用演示 2、distinct位置与聚合函数在select中的执行顺序说明 1.3、SUM( )函数
1.3.1、基本说明 SUM( )函数可以计算一组值或表达式的总和语法如下
SUM([DISTINCT] expression) ---distinct是可选项。说明 1、若在没有返回匹配行SELECT语句中使用SUM函数则SUM函数返回NULL而不是0。 2、对NULL值的处理SUM()函数会忽略列中的NULL值只有非NULL值会被计算进总和中。 1.3.2、使用演示 1、基本演示其中多列求和操作如下
SELECT SUM(column1 column2) FROM table_name; --将返回column1和column2两列的总和。2、存在NULL的演示结果 1.4、AVG( )函数
1.4.1、基本说明 AVG()函数可以用于计算一组值或表达式的平均值。语法如下
AVG([DISTINCT] expression) ---distinct是可选项。说明 1、NULL值的处理AVG()函数会忽略列中的NULL值只有非NULL值会被计算进平均值中。 2、多列求平均可以使用AVG()函数对多个列进行操作。
1.4.2、使用演示 1、基本演示求某项科目的平均成绩。 2、求总成绩的平均值、关于NULL说明 1.5、MIN( )函数、MAX( )函数
1.5.1、基本说明 MySQL的MIN()和MAX()函数是用于查找表中的最小值和最大值的聚合函数语法如下
MAX([DISTINCT] expr)
MIN([DISTINCT] expr) 说明 1、忽略空值。当列中包含NULL值时MIN()和MAX()将忽略这些值并返回非NULL值中的最小值。如果所有值都是NULLMIN()函数将返回NULL。 2、可对多列操作。求多列中的最大值或最小值。
1.5.2、使用演示 求各科成绩的最高分、最低分 求总成绩的最高分、最低分 2、时间日期函数
2.1、汇总部分 扩展学习MYSQL日期函数。 时间日期函数主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
函数名称描述current_ date ()返回当前日期1999-10-24current_ time ()返回当前时间19:59:02current_ t timestamp ()返回当前时间戳自动转为日期和时间1999-10-24 19:59:02date (datetime)返回datetime参数的日期部分date_add (date interval d_value_type)计算起始日期 date 加上一个时间段后的日期。interval后的数值单位可以是year、minute、second、daydate_sub(date interval d_value_type)从日期date中减去指定的时间间隔。interval后的数值单位可以是year、minute、second、daydatediff (date1 date2)计算两个日期的差单位是天now()当前日期时间 除了上述部分还有其它日期函数。
2.2、curdate()、current_date()、current_time()、current_timestamp()、now() 1、基本演示 这几个函数都是用于获取日期、时间的函数。当执行查询时如果未指定日期部分这些函数将返回当天的日期或时间。 CURDATE() 函数按 YYYY-MM-DD 或 YYYYMMDD 格式返回系统的当前日期。与 CURRENT_DATE() 函数完全相同。 CURRENT_TIME() 函数按 hh:mm:ss 格式返回系统的当前时间。与 CURTIME() 函数完全相同。 CURRENT_TIMESTAMP() 函数按 YYYY-MM-DD hh:mm:ss 格式返回当前时间和日期。与 NOW() 函数完全相同。 N 表示把日期转为 yyyyMMdd 格式 N把时间转为 hhmmss 格式 N。 2、在表格中使用 以下为一个演示 2.3、date()、date_add()、date_sub()、datediff()
2.3.1、date() 1、基本介绍 说明从日期时间表达式中提取日期部分并返回。
DATE(expr)参数expr是必需的。可以是一个日期或日期时间表达式。 返回值返回指定的日期或者日期时间表达式中的日期部分。PS若返回 NULL情况有二①指定的表达式不是一个合法的日期或者日期时间。②参数为 NULL。 2、相关演示 2.3.2、date_add()、date_sub() 1、基本介绍 DATE_SUB() 函数 在指定的日期/时间上减去指定到时间间隔加并返回新的日期/时间。 DATE_ADD() 函数 在指定的日期/时间上加上指定到时间间隔加并返回新的日期/时间。
DATE_SUB(date, INTERVAL value unit)
DATE_ADD(date, INTERVAL value unit)参数介绍 date必需的。需要操作的起始日期。 value必需的。用于确定从起始日期减去时间/日期间隔。正数和负数都是允许的。 unit必需的。时间/日期间隔的单位。时间/日期间隔的单位可以是以下值中的一个
YEAR 年、QUARTER 季、MONTH 月、
WEEK 周、DAY 日、
HOUR 时、MINUTE 分、SECOND 秒、MICROSECOND 微秒、
YEAR_MONTH 、
DAY_HOUR 、DAY_MINUTE 、DAY_SECOND 、DAY_MICROSECOND 、
HOUR_MINUTE 、HOUR_SECOND 、HOUR_MICROSECOND 、
MINUTE_SECOND、MINUTE_MICROSECOND 、SECOND_MICROSECOND 返回值 指定的日期/时间添加指定的时间间隔并返回一个新的日期/时间。返回值和参数有关
【返回 DATE】 date 是 DATE 类型且时间间隔为 YEAR, MONTH 或者 DAY。
【返回 TIME】date 是 TIME 类型且计算只涉及 HOURS, MINUTES 和 SECONDS 部分。
【返回 DATETIME】date 是 DATE 类型并且时间间隔为 HOURS, MINUTES 或者 SECONDS。date 是 DATETIME 类型。date 是 TIME 类型并且时间间隔为 YEAR, MONTH 或者 DAY。
【其他情况下返回字符串】2、相关演示 演示date_add()函数其它时间单位类似。 演示date_sub()函数其它时间单位类似。 2.3.3、datediff ()、timediff() 1、基本介绍 DATEDIFF() 函数 返回两个日期值之间的天数。
DATEDIFF(date1, date2)参数说明 date1、date2是必需的。可以是一个日期或者日期时间表达式。 返回值 返回两个日期值之间的天数。PS若返回 NULL①指定的表达式不是一个合法的日期或者日期时间。②参数为 NULL。 TIMEDIFF() 函数 返回两个时间之间的差值。
TIMEDIFF(time1, time2)参数说明 time1、time2是必需的。可以是一个时间或者日期时间表达式。 返回值 返回两个时间之间的差值格式为HH:MM:SSPS返回 NULL①指定的表达式不是一个合法的时间或者日期时间。②参数为 NULL。 2、相关演示 对datediff() 对timediff() 3、字符串函数
3.1、汇总部分 扩展学习MYSQL字符串函数详解 MySQL提供了许多字符串函数用于处理和操作字符串数据。
函数名称描述charset (str)返回字符串字符集concat(string2 [, …])连接字符串instr (string, substring)返回substring在string中出现的位置没有返回0ucase (string2)转换成大写lcase(string2)转换成小写left (string2 length)从string2中的左边起取length个字符length (string)string的长度replace(strsearch_str, replace_str)在str中用replace_str替换search_strstrcmp(string1, string2)逐字符比较两字符串大小substring(str, position [, length])从str的postion开始取length个字符ltrim (string)、rtrim(string)、trim (string)去除前空格或后空格 3.1、charset() 1、基本介绍 CHARSET() 函数返回指定的字符串的字符集。
CHARSET(str)参数 str必需的。 一个字符串。 返回值返回指定的字符串的字符集。如果参数为 NULL CHARSET() 函数将返回 binary。 2、相关演示 基本演示如下 该函数作用举例可以手动确认一下乱码的表格一般都是因为编码不匹配所以导致的乱码 3.2、concat()、concat_ws() 1、基本介绍 CONCAT()函数需要一个或多个字符串参数并将它们连接成一个字符串。CONCAT()函数需要至少一个参数否则会引起错误。
CONCAT(string1, string2, ..., stringN)参数说明 string1, string2, ..., stringN 必需的至少应指定一个字符串拼接中若有一个为 NULL, 则返回 NULL 返回值 返回拼接后的字符串。
只有一个参数返回参数本身。
若有一个参数为NULL返回NULL。无参数时报错
ERROR 1582 (42000): Incorrect parameter count in the call to native function CONCAT。CONCAT_WS()函数是上述一种特殊形式能将两个或多个字符串值与预定义的分隔符相连接。
CONCAT_WS(separator, string1, string2, ..., stringN)参数说明 separator分隔符必须的。您可以使用单个字符也可以使用字符串。 string1, string2, ..., stringN必需的至少应指定一个字符串如果拼接的参数为 NULL, 则会被忽略 返回值 返回使用分隔符连接后的多个字符串。
如果 separator 为 NULL则返回 NULL。
如果 string1, string2, ..., stringN 中有 NULL则会被 CONCAT_WS() 函数忽略。如果没有指定 string1, string2, ..., stringNMySQL 将会报错
ERROR 1582 (42000): Incorrect parameter count in the call to native function CONCAT_WS。2、相关演示 基本演示如下 在表格中使用如下 3.3、length() 1、基本介绍 LENGTH() 函数与 OCTET_LENGTH() 函数相同返回 以字节为单位的字符串长度 。如果想返回以字符为单位计算长度可以使用 CHAR_LENGTH() 函数或者 CHARACTER_LENGTH() 函数。
LENGTH(string)参数说明string是必需的。需要计算长度的字符串。参数可以是其他的类型比如数字或者日期等LENGTH() 函数会首先将其转为字符串再计算长度。 返回值 返回指定字符串的以字节为单位的长度即字节的数量。当参数为 NULL 时 返回 NULL。 2、相关演示 3.4、substring () 1、基本介绍 SUBSTRING() 函数从一个字符串中返回一个从指定位置开始的指定长度的子字符串。 SUBSTR() 等同于 SUBSTRING() 函数。 该函数有4种形式
SUBSTRING(str, pos)
SUBSTRING(str FROM pos)
SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos FOR len)参数说明 str必需的从中提取子字符串的原字符串。 pos必需的开始提取的位置。它可以是正数或者负数。如果是正数则从字符串的开头为起始位置确定开始提取的位置提取子字符串。如果是负数则从字符串的结尾为起始位置确定开始提取的位置提取子字符串。 len可选的。是一个正整数表示需要截取的子字符串的长度这里的单位是字符数。如果不指定则提取到原字符串的结尾。 返回值 当 pos 为 0 或者超过原字符串的长度时 返回空串 。 当 pos 为负数时从字符串尾部向前计数。 当 pos len 超过原字符串的长度时从pos位置提取到原字符串的结尾。 当参数为 NULL 时 返回 NULL。 2、相关演示 演示一 演示二 3.5、left()、right() 1、基本介绍 LEFT() 函数从指定字符串的左侧返回指定数量的字符组成的字符串。 RIGHT() 函数从指定字符串的右侧返回指定数量的字符组成的字符串。
LEFT(string, length)
RIGHT(string, length)参数说明 string必需的。需要从中提取字符的字符串。 length必需的。是一个正整数指定要提取的字符数量长度单位字符。 返回值情况说明
返回原字符串情况length大于string的长度。
返回空串情况length为小于等于0。
返回 NULL情况任意一个参数为 NULL。2、相关演示 3.5、instr() 1、基本介绍 INSTR() 函数 返回一个子字符串在一个字符串中第一次出现的位置的数字索引。 INSTR() 函数是不区分大小写与具有两个参数的 LOCATE() 函数的和 POSITION() 功能相同。
INSTR(str, substr)参数说明
str必需的。 被搜索的字符串。
substr必需的。 在 str 中搜索的子串。返回值 该函数返回子字符串 substr 在字符串 str 中的位置的数字索引。数字索引从 1 开始。如果在 str 中找不到 substrINSTR() 函数将返回 0。当任意一个参数为 NULL 时 INSTR() 函数将返回 NULL。 2、相关演示 3.6、replace() 1、基本介绍 REPLACE() 函数将字符串中出现的所有子字符串替换为新的子字符串。 REPLACE() 函数是基于字符的替换并且替换字符串时是区分大小写的。
REPLACE(str, from_str, to_str)参数说明 str必需的原字符串。 from_str必需的被替换的子字符串。 to_str必需的用来替换的新子字符串。 返回值 返回 str 中的所有 from_str 被 to_str 替换后的字符串。(PS当任意一个参数为 NULL 时 REPLACE() 函数将返回 NULL 2、相关演示 3.7、ucase()、upper()、lcase()、lower() 1、基本介绍 Ucase → UpperCase大写字母。Lcase → LowerCase小写字母。 UCASE() 和UPPER() 函数能将指定字符串转为大写并返回。LCASE() 和LOWER() 函数刚好相反将字符串转换为小写。
UPPER(str);
UCASE(str);
LOWER(str);
LCASE(str);参数说明str必需的。 被处理的字符串。 返回值 当参数 str 为 NULL 时 UPPER() 函数将返回 NULL 2、相关演示 3.8、strcmp() 1、基本介绍 STRCMP() 函数基于字符串采用的字符排列规则collation比较两个字符串并返回比较结果。
STRCMP(string1, string2)参数说明string1、string2必需的。参与比较的字符串。 返回值 两个字符串比较后的结果。
如果string1和string2相同则STRCMP函数将返回0。
如果string1小于string2则STRCMP函数将返回-1。
如果string1大于string2则STRCMP函数将返回1。
返回 NULL情况任意个参数为 NULL。2、相关演示 3.9、ltrim()、rtrim()、trim() 1、基本介绍 LTRIM() 函数删除指定字符串的前导空格并返回没有前导空格的字符串。 RTRIM() 函数 删除字符串的尾随空格并返回没有尾随空格的字符串。 TRIM() 函数 同时删除字符串的前导和尾随空格。
LTRIM(str)
RTRIM(str)
TRIM(str)参数说明 str是必需的。表示需要删除空格的字符串。 返回值 若参数为 NULL则返回空值。 2、相关演示 存在意义举例这些函数可以帮助去除字符串中的多余字符使数据更加整洁。例如在执行数据库查询时可以去除字符串字段的开头或结尾中不必要的空格又如在将数据导入数据库之前有时也需要进行数据清洗和预处理等等。 4、数学函数
4.1、总言 扩展学习MYSQL数字函数详解
函数名称描述abs (number)绝对值函数bin (decimal_number)十进制转换二进制hex (decimal_number)转换成十六进制conv (number, from_base, to_base)进制转换ceiling (number)向上去整floor (number)向下去整format (number, decimal_ places)格式化保留小数位数rand ()返回随机浮点数范围[0.0, 1.0)mod (numberdenominator)取模求余 4.2、abs() 1、基本介绍 ABS() 函数返回指定数字的绝对值。
ABS(number)参数说明number是必需的是需要计算绝对值的数字。 返回值返回数字的绝对正值。如果参数 number 为字符串ABS() 将按照如下规则尝试转为数字
如果以数字开头则将开头的数字部分转为数字。
如果不能转为数字则按照 0 处理。
返回 NULL情况number 为 NULL。2、相关演示 4.3、bin() 1、基本介绍 BIN() 函数 用于将数字参数转换为二进制字符串。
BIN(N)参数 N 是需要转换的数字。 返回值 返回二进制值的字符串形式相当于 CONV(N,10,2)。 如果 N 为 NULL则返回 NULL。 2、相关演示 4.4、hex() 1、基本介绍 HEX()函数用于将数字或字符串参数转换为十六进制表示的字符串。
HEX(str)
HEX(N)参数 对于字符串参数 strHEX() 返回 str 的十六进制字符串表示形式。其中 str 中每个字符的每个字节都转换为两个十六进制数字多字节字符变为两位以上的数字。此操作的逆操作由 UNHEX() 函数执行。 对于数字参数 NHEX() 返回 N 的十六进制字符串表示形式被视为 longlong (BIGINT) 数字。 这相当于 CONV(N,10,16)。 该操作的逆操作由 CONV(HEX(N),16,10) 执行。 2、相关演示 4.5、conv() 1、基本介绍 CONV() 函数将数字从一个进制转为另一个进制比如从 10 进制转为 2 进制。
CONV(num, from_base, to_base)参数说明 num必填需要转换的数字。 from_base必填当前数字num使用的进制从 2 到 36。 to_base必填。 数字num将转为的进制从 2 到 36。 返回值 将数字从一个进制转为另一个进制。若任意一个参数为 NULL则返回 NULL。 2、相关演示 4.6、ceiling()、floor() 1、基本介绍 CEILING() 函数返回大于或等于指定数字的最小整数值等同于 CEIL() 函数。 FLOOR() 函数返回小于或等于指定数字的最大整数值。
CEILING(number)
FLOOR(number)返回值 number 为 NULL时返回 NULL。 2、相关演示 4.7、format() 1、基本介绍 FORMAT() 函数将数字格式设置为 “#,###,###.##”四舍五入到指定的小数位数然后以字符串形式返回结果。
FORMAT( number , decimal_places )参数值 number 必填。被格式化的数字 decimal_places 必填。数字的小数位数 number。 如果此参数为 0则此函数返回不带小数点的字符串。 2、相关演示 4.8、rand () 1、基本介绍 RAND() 函数返回一个 0 (包含) 和 1 (不包含) 之间的随机数。
RAND()
RAND(seed)参数说明 seed是可选的用于产生随机数的种子。
rand()无参数产生的随机数是随机的不可重复的
rand(n)有参数相当于指定随机数生产的种子这种情况产生的随机数是可重复的。返回值 一个 0 (包含) 和 1 (不包含) 之间的随机数。若想返回其他区间比如 m 和 n的随机数请使用以下公式
m 和 n 之间的随机数 RAND() * (n - m) m
m 和 n 之间的随机整数 FLOOR(RAND() * (n - m 1) m)2、相关演示 演示rand函数含参和无参的情况 其它演示 随机取样 4.9、mod() 1、基本介绍 MOD() 函数返回一个数字除以另一个数字的余数。
MOD(number1, number2)
number1 MOD number2
number1 % number2参数说明
number1必需的。 被除数。
number2必需的。 除数。返回值 一个数字除以另一个数字的余数。返回 NULL情况①number2 等于 0。②number1 为 NULL。 2、相关演示 5、其它函数
5.1、user() 1、基本介绍 USER() 函数 返回 MySQL 连接的当前用户名和主机名
USER();备注: 这个函数相当于 SESSION_USER() 函数和 SYSTEM_USER() 函数。 3、相关演示 mysql数据库中存在user表。这里先了解后续会介绍 5.2、md5() 1、基本介绍 MD5() 函数计算一个给定字符串的 MD5 摘要并将结果作为一个 32 位的由十六进制字符组成的字符串返回。
MD5(str)参数 str是必需的表示需要计算 MD5 值的字符串。 返回值 MD5() 函数返回指定字符串 str 的 MD5 摘要它是一个 32 位的由十六进制字符组成的字符串。如果任意参数为 NULL MD5() 函数返回 NULL。 2、相关演示 MD5Message Digest Algorithm 5是一种广泛使用的加密哈希函数它将任意长度的数据作为输入并返回一个固定长度的哈希值。 PSMD5 已被认为在密码学上是不安全的它容易受到碰撞攻击collision attacks。对于需要高安全性的应用建议使用更安全的哈希函数如 SHA-256 或 bcrypt等等。 5.3、password() 1、基本介绍 介绍 password()函数将输入的字符串进行加密并返回加密后的结果。加密过程使用MySQL内部的密码算法进行处理该算法是不可逆的也就是说无法通过加密后的结果还原出原始字符串。
PASSWORD(str)返回值 password()函数的返回值是一个长度为41的字符串。该字符串包含了加密算法的标识以及加密后的结果。可以将该加密后的结果存储在数据库中以保证用户密码的安全性。 注意事项 1、PASSWORD() 函数在 MySQL 5.7.6 及更高版本中已被弃用。取而代之的是使用插件 caching_sha2_password 或 mysql_native_password 进行身份验证。因此如果你正在使用较新的 MySQL 版本建议不要使用 PASSWORD() 函数而是使用插件进行身份验证。 2、由于 PASSWORD() 函数是 MySQL 特有的与其他数据库系统不兼容。 2、相关演示 5.4、database() 1、基本介绍 DATABASE() 函数以字符串形式返回当前的数据库的名称。
DATABASE()返回值 DATABASE() 函数返回一个 UTF8 字符串它是当前的数据库名称。如果还未选择数据库DATABASE() 函数将返回 NULL。 2、相关演示 用途举例当忘记当前所处数据库是可使用其查看。 5.5、ifnull() 1、基本介绍 MySQL中IFNULL() 函数是一个 if…else…的函数。类似于三目运算符如果第一个参数为 NULL则返回第二个参数否则返回第一个参数。
IFNULL(expr1, expr2)此函数相当于 IF() 和 ISNULL() 函数的结合 IF(ISNULL(expr1), expr2, expr1)。 参数
expr1必需的。判断此表达式是否为 NULL。
expr2必需的。当 expr1 为 NULL 时返回 expr2。返回值 如果 expr1 为 NULLIFNULL() 函数返回 expr1否则返回 expr2。 2、相关演示
