MySQL 内置函数 持续更新

MySQL时间格式

转换为时间戳

 FROM_UNIXTIME(m.`add_time`, '%Y-%m-%d %H:%i:%S') AS add_time

now()：获取当前时间；

例：SELECT now(); 

unix_timestamp()：将时间转换为时间戳；
例： SELECT unix_timestamp(now()); 

MySQL取整函数

四舍五入 round（）/round（‘值’，小数点位数）

round(((po.gross_weight-po.tare_weight)-(pc.weight_deduction*0.001)),2) as actual_tonnage,

向下取整 FLOOR()

FLOOR(FLOOR(p.price * (((po.gross_weight-po.tare_weight)-(pc.weight_deduction*0.001))) - pc.sealing_tube)) as actual_price

向上取整 CEILING () 暂无应用

CASE 语法

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

            (case 
            when l.amount_status = 0 then '待审核' 
            when l.amount_status = 1 then '待付款' 
            when l.amount_status = 2 then '审核不通过' 
            else '已付款' end) as status

语句

SELECT
	sum( p.price ),
	p.id,
	USER.NAME,
	count( qr.id ) AS car_count,
	sum( round( ( qr.gross_weight - qr.tare_weight ), 2 ) ) AS net_weight,
	sum(
	round( ock.price * ( ( qr.gross_weight - qr.tare_weight ) - ( ock.weight_deduction * 0.001 ) ), 2 ) 
	) AS no_invoice_price,
	MAX( p.price ) AS max_price,
	MIN( p.price ) AS min_price,
	cast( avg( p.price ) AS DECIMAL ( 18, 2 ) ) AS avg_price,
	sum( ock.weight_deduction ) AS weight_deduction,
	count( p.logistics_id ) AS sum_wl,
	sum( p.logistics_price ) AS wl_price 
FROM
	minda_purchase_order p
	LEFT JOIN minda_sys_user USER ON USER.id = p.operator_id
	LEFT JOIN minda_purchase_order_qrcode qr ON qr.purchase_order_id = p.id
	LEFT JOIN minda_purchase_order_check ock ON ock.order_id = p.id 
GROUP BY p.operator_id WHERE p.create_time = time()

count()：函数返回指定列的值的数目

sum():函数返回数值列的总数

MAX(): 函数返回指定列的最大值

MIN(): 函数返回指定列的最小值

AVG() 函数返回数值列的平均值

CAST()：将给定表达式转换为指定数据类型的函数

CAST(expr AS CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR)
CAST(expr AS CHAR(n) | CHARACTER(n) | VARCHAR(n) )
CAST(expr AS CHAR VARYING | CHARACTER VARYING)
CAST(expr AS INT | INTEGER | BIGINT | SMALLINT | TINYINT)
CAST(expr AS DEC | DECIMAL | NUMERIC)
CAST(expr AS DEC(p[,s]) | DECIMAL(p[,s]) | NUMERIC(p[,s]) )
CAST(expr AS DOUBLE)
CAST(expr AS MONEY | SMALLMONEY)
CAST(expr AS DATE)
CAST(expr AS TIME)
CAST(expr AS POSIXTIME)
CAST(expr AS TIMESTAMP | DATETIME | SMALLDATETIME)
CAST(expr AS BIT)
CAST(expr AS BINARY | BINARY VARYING | VARBINARY)
CAST(expr AS BINARY(n) | BINARY VARYING(n) | VARBINARY(n) )
CAST(expr AS GUID)

参数

• expr - SQL表达式，通常是表的文字或数据字段。
• n - 一个整数，指示要返回的最大字符数。
• 如果n小于expr数据，返回的数据将被截断为n个字符。
• 如果n大于expr数据，则不执行填充。
• p,s - 可选 - p=精度(最大总位数)，整数形式。
• s=刻度(十进制数字的最大值)，用整数表示。
• 如果未指定比例，则默认为15。