文章目录
MySql函数
1.聚合函数
AVG()函数
AVG()函数,计算一组值的平均值。计算过程中忽略null值。
select avg(buyPrice) average_buy_price from products;
+-------------------+
| average_buy_price |
+-------------------+
| 54.395182 |
+-------------------+
COUNT()函数
返回表中的行数,例如,可以使用count()函数获取products表中的产品数量:
SELECT COUNT(*) AS Total FROM products;
+-------+
| Total |
+-------+
| 110 |
+-------+
1 row in set
SUM()函数
sum()
函数返回一组值的综合,sum()
函数忽略null
值。如果找不到匹配行,则sum()
函数返回null值。
要获得每个产品的总销售量,可以将SUM()函数与GROUP BY子句一起使用,如下所示:
mysql> SELECT productCode,sum(priceEach * quantityOrdered) total FROM
orderdetails GROUP by productCode;
+-------------+-----------+
| productCode | total |
+-------------+-----------+
| S10_1678 | 90157.77 |
| S700_3505 | 84992.25 |
****** 此处省略了一大波数据 ********
| S700_3962 | 78919.06 |
| S700_4002 | 71753.93 |
| S72_1253 | 42692.53 |
| S72_3212 | 47550.40 |
+-------------+-----------+
要更详细地查看结果,可以将orderdetails表连接到products表中,如下查询语句所示:
SELECT P.productCode,
P.productName,
SUM(priceEach * quantityOrdered) total
FROM orderdetails O
INNER JOIN products P ON O.productCode = P.productCode
GROUP by productCode
ORDER BY total;
+-------------+---------------------------------------------+-----------+
| productCode | productName | total |
+-------------+---------------------------------------------+-----------+
| S24_1937 | 1939 Chevrolet Deluxe Coupe | 28052.94 |
| S24_3969 | 1936 Mercedes Benz 500k Roadster | 29763.39 |
| S24_2972 | 1982 Lamborghini Diablo | 30972.87 |
| S24_2840 | 1958 Chevy Corvette Limited Edition | 31627.96 |
****** 此处省略了一大波数据 ************************************************
| S12_3891 | 1969 Ford Falcon | 152543.02 |
| S12_1099 | 1968 Ford Mustang | 161531.48 |
| S10_4698 | 2003 Harley-Davidson Eagle Drag Bike | 170686.00 |
| S10_1949 | 1952 Alpine Renault 1300 | 190017.96 |
| S12_1108 | 2001 Ferrari Enzo | 190755.86 |
| S18_3232 | 1992 Ferrari 360 Spider red | 276839.98 |
+-------------+---------------------------------------------+-----------+
MAX()函数
返回一组数中的最大值。
例如,可以使用MAX函数获取products表中最昂贵的产品,如下查询语句:
SELECT MAX(buyPrice) highest_price FROM products;
mysql> SELECT MAX(buyPrice) highest_price FROM products;
+---------------+
| highest_price |
+---------------+
| 103.42 |
+---------------+
1 row in set
MIN()函数
返回一组值中的最小值。
SELECT MIN(buyPrice) lowest_price FROM Products;
mysql> SELECT MIN(buyPrice) lowest_price FROM Products;
+--------------+
| lowest_price |
+--------------+
| 15.91 |
+--------------+
1 row in set
2.常用数学函数
函数 | 作用 | 函数 | 作用 |
---|---|---|---|
ceil() | 进一取整 | pi() | 圆周率 |
abs() | 取绝对值 | truncate() | 截取小数点后几位 |
floor() | 舍掉小数部分 | rand()/rand(x) | 0~1之间的随机数 |
power() | 幂运算 | mod() | 取余数 |
round() | 四舍五入 | sing(x) | 得到数字符号 |
exp() | 计算e的x次方 |
用法
##进一取整
select ceil(1.2);
##舍掉小数部分
select floor(2.9);
##四舍五入保留2位小数
select round(3.56789,2);
##截取小数点后3位
select truncate(3.456789,3);
##取10/3的余数
select 10 mod 3;
##
select id,username,ceil(salary) from user;
3.字符串常用函数
--char_length():得到字符串的字符数
select char_length('abc');
--length():得到字符串的长度(一个中文字符在utf8下占3个长度)
select length('abc');
--concat(s1,s2,...):将字符串合并成一个字符串(参数里有null最终结果为null)
select concat('a','b','c');
--concat_ws():以指定分隔符拼接字符串
select concat_ws('-','a','b','c',null); --null不起作用
select concat_ws(null,'a','b','c'); --以null作为分隔符结果为null
--upper()|ucase()|lower()|lcase():将字符串转换成大写或者小写
select upper('hello world'),ucase('hello world'),lower('HELLO WORLD'),lcase('HELLO WORLD');
--reverse():字符串的反转
select reverse('abc');
--left()|right():返回字符串的前几个字符或者后几个字符
select left('hello',2),right('hello',2);
--lpad()|rpad():用字符串填充到指定长度
select lpad('abc',10,'?'); --从'abc'左端用'?'填充到10位
--trim()|ltrim()|rtrim():去掉字符串两端的空格
select concat('*',trim(' abc '),'*');
--repeat():重复指定的次数
select repeat('hello',3);
--replace():替换指定的字符串
select replace('hello king','king','queen'); --将'king'替换成'queen'
--substring():截取字符串
select substring('abcdef',1,3); --从1开始截取3位,abc
--strcmp():比较字符串
select strcmp('a','b');
4.日期时间常用函数的使用
--返回当前日期
select curdate(),current_date();
--返回当前时间
select curtime(),current_time();
--返回当前的日期时间
select now(),current_timestamp(),sysdate();
--返回日期中的月份和月份的名称
select month('2017-02-19');
select month(current_date()),monthname(curdate());
--返回星期几
select dayname(now());
--返回一周内的第几天(1:星期天,2:星期一,...)
select dayofweek(now());
--返回一年中的第几个星期
select week(now());
--返回日期中的年份、月份、天、小时、分钟
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
--计算两个日期相差的天数
select datediff('2017-03-05','2017-03-01');
5.其他常用函数
--得到MySQL版本、当前服务器的连接数
select version(),connection_id();
--得到当前的数据库名
select database(),schema();
--得到当前登陆的用户
select user(),current_user(),system_user(),session_user();
--得到上一步插入操作产生auto_increment的值
select last_insert_id();
--加密
select md5('king');
--密码加密算法
select password('root');
timestampdiff()函数
TIMESTAMPDIFF(unit,begin,end);
TIMESTAMPDIFF
函数返回end - begin的结果,其中begin和end是DATE或DATETIME表达式。
TIMESTAMPDIFF函数允许其参数具有混合类型,例如,begin是DATE值,end可以是DATETIME值。 如果使用DATE值,则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end - begin)的结果的单位,表示为整数。 以下是有效单位:
MICROSECOND 微秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月份
QUARTER
YEAR 年份
MySQL TIMESTAMPDIFF函数示例
以下示例将以月份值的形式返回2018-06-01和2018-01-01的差值:
mysql> SELECT TIMESTAMPDIFF(MONTH, ‘2018-01-01’, ‘2018-06-01’) result;
如果您希望看到差值,只需要将unit参数从MONTH更改为DAY,如下所示:
mysql> SELECT TIMESTAMPDIFF(DAY, ‘2010-01-01’, ‘2010-06-01’) result;
使用MySQL TIMESTAMPDIFF函数计算年龄
mysql> SELECT TIMESTAMPDIFF(YEAR, ‘2010-01-01’,NOW()) AGE;
round函数
在mysql中,round函数用于数据的四舍五入,它有两种形式:
1、round(x,d) ,x指要处理的数,d是指保留几位小数
这里有个值得注意的地方是,d可以是负数,这时是指定小数点左边的d位整数位为0,同时小数位均为0;
2、round(x) ,其实就是round(x,0),也就是默认d为0;
DATE_ADD函数
DATE_ADD(date,INTERVAL expr type)`
执行日期运算。 date 是一个 DATETIME 或DATE值,用来指定起始时间。 expr 是一个表达式,用来指定从起始日期添加或减去的时间间隔值。 Expr是一个字符串;对于负值的时间间隔,它可以以一个 ‘-’开头。 type 为关键词,它指示了表达式被解释的方式。
关键词INTERVA及 type 分类符均不区分大小写。
从它的特性得知date是指定的日期,INTERVAL为关键词,expr是具体的时间间隔,type是时间单位。注意:type可以复合型的,比如YEAR_MONTH。如果type不是复合型的,DATE_ADD和DATE_SUB其实可以通用,因为expr可以为一个负数。可用的type如下表:
标识 | 单位 |
---|---|
MICROSECOND | 间隔单位:毫秒 |
SECOND | 间隔单位:秒 |
MINUTE | 间隔单位:分钟 |
HOUR | 间隔单位:小时 |
DAY | 间隔单位:天 |
WEEK | 间隔单位:星期 |
MONTH | 间隔单位:月 |
QUARTER | 间隔单位:季度 |
YEAR | 间隔单位:年 |
SECOND_MICROSECOND | 复合型,间隔单位:秒、毫秒,expr可以用两个值来分别指定秒和毫秒 |
MINUTE_MICROSECOND | 复合型,间隔单位:分、毫秒 |
MINUTE_SECOND | 复合型,间隔单位:分、秒 |
HOUR_MICROSECOND | 复合型,间隔单位:小时、毫秒 |
HOUR_SECOND | 复合型,间隔单位:小时、秒 |
HOUR_MINUTE | 复合型,间隔单位:小时分 |
DAY_MICROSECOND | 复合型,间隔单位:天、毫秒 |
DAY_SECOND | 复合型,间隔单位:天、秒 |
DAY_MINUTE | 复合型,间隔单位:天、分 |
DAY_HOUR | 复合型,间隔单位:天、小时 |
YEAR_MONTH | 复合型,间隔单位:年、月 |
对应复合型的type,需要使用引号对两个参数进行引用起来,中间用任何非数字字符作为间隔即可,并且不能使用负数。但是时间间隔只指定了一个值,那么也能正常工作,但是对应XXX_YYY使用的单位为YYY,也就相当于单一单位的type,同时可以使用负数。复合型的用法如下: |
2.测试脚本:
mysql> select date_add('2013-01-18', interval '1,2' YEAR_MONTH);
+---------------------------------------------------+
| date_add('2013-01-18', interval '1,2' YEAR_MONTH) |
+---------------------------------------------------+
| 2014-03-18 |
+---------------------------------------------------+
mysql> select date_add('2013-01-18', interval 1 YEAR_MONTH);
+-----------------------------------------------+
| date_add('2013-01-18', interval 1 YEAR_MONTH) |
+-----------------------------------------------+
| 2013-02-18 |
+-----------------------------------------------+
mysql> select date_add('2013-01-18', interval -1 YEAR_MONTH);
+------------------------------------------------+
| date_add('2013-01-18', interval -1 YEAR_MONTH) |
+------------------------------------------------+
| 2012-12-18 |
+------------------------------------------------+
IFNULL()函数
IFNULL(parameter1 , parameter2)
如果参数parameter1 不为空,返回parameter1 ,如果为空则返回parameter2
IF()函数的语法
- 表达式
IF(expr1,expr2,expr3)
如果expr1是TRUE (expr1 <> 0和expr1 <> NULL),则IF() 返回expr2。否则,它返回expr3。
LENGTH()
LENGTH(str)
和OCTET_LENGTH(str)
函数用于返回字符串的字节长度,例如:
SELECT LENGTH('MySQL字符串函数') AS len1, OCTET_LENGTH('MySQL字符串函数') AS len2;
len1|len2|
----+----+
20| 20|
在 utf8mb4 编码中,一个汉字字符占用 3 个字节。
另外,CHAR_LENGTH(str)
和CHARACTER_LENGTH(str)
函数用于返回字符串的字符长度,也就是字符个数。例如:
SELECT CHAR_LENGTH('MySQL字符串函数') AS len1, CHARACTER_LENGTH('MySQL字符串函数') AS len2;
len1|len2|
----+----+
10| 10|
BIT_LENGTH(str)
函数用于返回字符串的比特长度(比特数量),例如:
SELECT BIT_LENGTH('MySQL字符串函数') AS len;
len|
---+
160|
一个字节包含 8 个比特。
trim()函数
- 删除指定字符串中的空格
select trim(' bar ');
trim(' bar ')|
---+
bar|
- 删除指定的首字符
select trim(leading 'x' from 'xxxxbarxxx ')
trim(leading 'x' from 'xxxxbarxxx ')|
---+
'barxxx '|
- 删除指定的首尾字符
select trim(both 'x' from 'xxxxbarxxx')
trim(both 'x' from 'xxxxbarxxx')|
---+
'bar'|
- 删除指定尾字符
select trim(trailing 'x' from 'xxxxbarxxx')
trim(trailing 'x' from 'xxxxbarxxx')|
---+
'xxxxbar'|
GROUP_CONCAT()
GROUP_CONCAT
函数用于将GROUP BY
产生的同一个分组中的值连接起来,返回一个字符串结果
GROUP_CONCAT
函数首先根据GROUP BY
指定的列进行分组,将同一组的列显示出来,并且用分隔符分隔,由函数参数(字段名)决定要返回的列
GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])
说明:
(1) 使用DISTINCT可以排除重复值
(2) 如果需要对结果中的值进行排序,可以使用ORDER BY子句
(3) SEPARATOR '分隔符’是一个字符串值,默认为逗号
例子:
SELECT buyer,
GROUP_CONCAT(DISTINCT spending ORDER BY spending DESC SEPARATOR '+')
FROM spend
GROUP BY buyer;
buyer|GROUP_CONCAT(DISTINCT spending ORDER BY spending DESC SEPARATOR '+')|
----+-----------+
Jack| 110+100 |
Mark| 150 |
Odin| 70+60+50|
substring_index()
substring_index()
就是MySQL
中对数据进行截取的操作函数。使用参数格式如下:
substring_index(参数str,参数delim,参数count)
解读:substring_index(要截取的字段, 根据哪个字符截取, 截到第几个指定的字符位置**[写整数 可以是正整数也可以是负整数,正整数从左向右计算,负整数从右向左计算] **)
如:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('用户@@134543@@435345','@',3),'@',-1);
得到:134543
先截取从左往右第三个@之前的数据, 得到之后再截取从右往左第一个@之后的数据