Mysql常用函数

2019-02-04 本文已影响102人若兮缘

COUNT()

COUNT(column_name) #返回指定列的值的数目(NULL不计入)
COUNT(*) #返回表中的记录数
COUNT(DISTINCT column_name) #返回指定列的不同值的数目

-- 查询客户信息中不重复的密码个数
SELECT COUNT(DISTINCT password) FROM customer

AVG()

返回数值列的平均值。NULL值不包括在计算中，即使是中文或者字符串也能计算
语法：AVG(column_name)、AVG(DISTINCT column_name)

-- 查询积分平均值，结果参考：511.00145755597015
SELECT AVG(integral) FROM customer
SELECT AVG(DISTINCT integral) FROM customer

MAX()

返回一列中的最大值。NULL值不包括在计算中，也可用于字符列
语法：MAX(column_name)

SELECT MAX(integral) FROM customer
SELECT MAX(username) FROM customer

注意：使用max函数查询一个字符串类型的整数数据时，因为字符串类型大小比较是先比较首字符，然后依次往后进行比较。如id列的数据:10000，9999。使用max查出来的最大值并不是10000，而是9999。
解决方法：max(id+0)

MIN()

返回一列中的最小值。NULL值不包括在计算中，也可用于字符列
语法：MIN(column_name)

SELECT MIN(integral) FROM customer
SELECT MIN(username) FROM customer

SUM()

返回数值列的总数(总额)
语法：SUM(column_name)

-- 结果参考：2191174.25
SELECT SUM(integral) FROM customer

LOAD_FILE()

读入文件并且作为一个字符串返回文件内容，后面的路径可以是单引号、0x、char转换的字符。文件无法找到，路径不完整，没有权限，长度大于max_allowed_packet会返回null
语法：LOAD_FILE(file_name)

SELECT LOAD_FILE('C:/Windows/win.ini')

CAST()

将字段(或表达式)转换成指定类型，type的取值如下：
BINARY[(N)]、CHAR[(N)]
DECIMAL、SIGNED [INTEGER]
TIME、UNSIGNED [INTEGER]
DATE、DATETIME
语法：CAST(expr AS type)

-- 根据积分值排序(将varchar类型的double数据转换成小数再进行排序)
SELECT * from customer ORDER BY CAST(integral AS DECIMAL)

CONVERT()

将字段(或表达式)转换成指定类型，type的取值同上
语法：CONVERT(expr,type)

-- 查询指定用户的积分的排名位置
SELECT count(*) from customer where integral <=
(SELECT integral from customer where mobile = '180XXXX')
 ORDER BY CONVERT(integral,DECIMAL)

UUID()

返回生成一个UUID值，前三组数字从时间戳中生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个IEEE 802节点标点值，保证空间唯一。使用UUID函数，可以生成时间、空间上都独一无二的值

-- 24ee26ad-fdaa-11e7-9abf-000c29c5d936 (36位)
SELECT UUID()
-- ac08e2cefdac11e79abf000c29c5d936 (32位)
SELECT UUID()  SELECT REPLACE(UUID(),'-','') AS id

IF()

expr1为条件，如果expr1 == true，输出(或执行)expr2，否则输出expr3
语法：IF(expr1,expr2,expr3)

-- A
SELECT IF(2,'A','B') 
-- B
SELECT IF(FALSE,'A','B')

ISNULL()

如果expr不是NULL，ISNULL(expr)为false，如果使用select ISNULL(expr)，输出0
如果expr是NULL，ISNULL(expr)为true，如果使用select ISNULL(expr)，输出1
判断空值也可使用is null
语法：ISNULL(expr)

-- 如果查询某个字段为NULL则返回0，否则返回该字段值
SELECT IF (ISNULL(column_name), 0 ,column_name)

IFNULL()

如果expr1为NULL，则输出expr2，如果expr1不为NULL，则输出expr1。
效果与CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同
语法：IFNULL(expr1,expr2)

-- A
SELECT IFNULL(NULL,'A')

NULLIF()

如果expr1 = expr2，那么返回值为NULL，否则返回值为expr1
语法：NULLIF(expr1,expr2)

-- NULL
SELECT NULLIF(1,1)
-- 2
SELECT NULLIF(2,1)

INET_ATON()

返回一个代表该地址数值的整数，参数为一个作为字符串的网络地址的"点地址"。
在存储由INET_ATON()产生的值时，推荐你使用INT UNSIGNED列(表示范围更大)
INET_ATON(n1.n2.n3.n4)，数字按照 n1×2^24 + n2×2^16 + n3×2^8 + n4 进行计算

-- 4294967295
SELECT INET_ATON('255.255.255.255')
-- 2130706433
SELECT INET_ATON('127.0.0.1')
-- 2130706433
SELECT INET_ATON('127.1')

INET_NTOA()

给定一个数字网络地址(4或8比特)，返回作为字符串的该地址的点地址表示
语法：INET_NTOA(expr)

-- 127.0.0.1
SELECT INET_NTOA(2130706433)
-- 209.207.224.40
SELECT INET_NTOA(3520061480)