函数
支持的函数
|
函数表达式 |
示例 |
|---|---|
|
IN |
SELECT * FROM Products WHERE vendor_id IN ( 'V000001', 'V000010' ) ORDER BY product_price; |
|
NOT IN |
SELECT product_id, product_name FROM Products WHERE NOT vendor_id IN ('V000001', 'V000002') ORDER BY product_id; |
|
BETWEEN |
SELECT id, product_id, product_name, product_price FROM Products WHERE id BETWEEN 000005 AND 000034 ORDER BY id; |
|
NOT…BETWEEN |
SELECT product_id, product_name FROM Products WHERE NOT vendor_id BETWEEN 'V000002' and 'V000005' ORDER BY product_id; |
|
IS NULL |
SELECT product_name FROM Products WHERE product_price IS NULL; |
|
IS NOT NULL |
SELECT id, product_name FROM Products WHERE product_price IS NOT NULL ORDER BY id; |
|
AND |
SELECT * FROM Products WHERE vendor_id = 'V000001' AND product_price <= 4000 ORDER BY product_price; |
|
OR |
SELECT * FROM Products WHERE vendor_id = 'V000001' OR vendor_id = 'V000009'; |
|
NOT |
SELECT product_id, product_name FROM Products WHERE NOT vendor_id = 'V000002'; |
|
LIKE |
SELECT * FROM Products WHERE product_name LIKE 'NAME%' ORDER BY product_name; |
|
NOT LIKE |
SELECT * FROM Products WHERE product_name NOT LIKE 'NAME%' ORDER BY product_name; |
|
CONCAT |
SELECT product_id, product_name, Concat( product_id , '(', product_name ,')' ) AS product_test FROM Products ORDER BY product_id; |
|
+ |
SELECT 3 * 2+5-100/50; |
|
- |
SELECT 3 * 2+5-100/50; |
|
* |
SELECT order_num, product_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num BETWEEN 000009 AND 000028 ORDER BY order_num; |
|
/ |
SELECT 3 * 2+5-100/50; |
|
UPPER |
SELECT id, product_id, UPPER(product_name) FROM Products WHERE id > 10 ORDER BY product_id; |
|
LOWER |
SELECT id, product_id, LOWER(product_name) FROM Products WHERE id <= 10 ORDER BY product_id; |
|
SOUNDEX |
SELECT * FROM Vendors WHERE SOUNDEX(vendor_name) = SOUNDEX('test') ORDER BY vendor_name; |
|
IFNULL |
SELECT IFNULL(product_id, 0) FROM Products; |
|
函数表达式 |
示例 |
支持范围 |
|---|---|---|
|
DAY() |
SELECT * FROM TAB_DATE WHERE DAY(date)=21; SELECT * FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); |
- |
|
MONTH() |
SELECT * FROM TAB_DATE WHERE MONTH(date)=12; SELECT * FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); |
- |
|
YEAR() |
SELECT * FROM TAB_DATE WHERE YEAR(date)=2018; SELECT * FROM TAB_DATE WHERE date='2018-12-21'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22'); |
- |
|
TIME() |
SELECT * FROM TAB_DATE WHERE TIME(date)='01:02:03'; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的时间、日期时间表达式或字符串。 |
|
TIME_TO_SEC() |
SELECT * FROM TAB_DATE WHERE TIME_TO_SEC(date)=3603; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:00:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:00:03'); |
参数需为合法的时间、日期时间表达式或字符串。 |
|
SEC_TO_TIME() |
SELECT * FROM TAB_DATE WHERE SEC_TO_TIME(date)='00:01:00'; SELECT * FROM TAB_DATE WHERE date=60; INSERT INTO TAB_DATE(id,date) VALUES(1,60); |
参数需为数值或可转化为数值的字符串。 |
|
SECOND() |
SELECT * FROM TAB_DATE WHERE SECOND(date)=3; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的时间、日期时间表达式或字符串。 |
|
MINUTE() |
SELECT * FROM TAB_DATE WHERE MINUTE(date)=2; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的时间、日期时间表达式或字符串。 |
|
HOUR() |
SELECT * FROM TAB_DATE WHERE HOUR(date)=1; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的时间、日期时间表达式或字符串。 |
|
DAYNAME() |
SELECT * FROM TAB_DATE WHERE DAYNAME(date)='Friday'; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
MONTHNAME() |
SELECT * FROM TAB_DATE WHERE MONTHNAME(date)='January'; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
LAST_DAY() |
SELECT * FROM TAB_DATE WHERE LAST_DAY(date)='2021-01-31'; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
DAYOFWEEK() |
SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
DAYOFMONTH() |
SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
DAYOFYEAR() |
SELECT * FROM TAB_DATE WHERE DAYOFYEAR(date)=365; SELECT * FROM TAB_DATE WHERE date='2021-12-31 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
WEEKOFYEAR() |
SELECT * FROM TAB_DATE WHERE WEEKOFYEAR(date)=53; SELECT * FROM TAB_DATE WHERE date='2021-12-31 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03'); |
参数需为合法的日期、日期时间表达式或字符串。 |
|
DATE_ADD( date, INTERVAL expr unit ) |
SELECT * FROM TAB_DATE WHERE DATE_ADD(date，INTERVAL 1 YEAR)='2022-01-01 01:02:03'; SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03'); |
|
|
DATE_SUB(date, INTERVAL expr unit) |
SELECT * FROM TAB_DATE WHERE DATE_SUB(date，INTERVAL -1 DAY)='2021-01-02 01:02:03'; SELECT * FROM TAB_DATE WHERE date='2021-01-02 01:02:03'; INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-02 01:02:03'); |
|
|
函数表达式 |
示例 |
支持范围 |
|---|---|---|
|
SQRT() |
SELECT id, product_price, SQRT(product_price) AS price_sqrt FROM Products WHERE product_price < 4000 ORDER BY product_price; |
- |
|
AVG() |
SELECT AVG(product_price) AS avg_product FROM Products; |
- |
|
COUNT() |
SELECT COUNT(*) AS num_product FROM Products; |
- |
|
MAX() |
SELECT id, product_id, product_name, MAX(product_price) AS max_price FROM Products ORDER BY id; |
- |
|
MIN() |
SELECT id, product_id, product_name, MIN(product_price) AS min_price FROM Products ORDER BY id; |
- |
|
SUM() |
SELECT SUM(product_price) AS sum_product FROM Products; |
- |
|
ROUND() |
SELECT ROUND(product_price) AS round_product FROM Products; |
参数需为数值或可转化为数值的字符串。 |
|
SIN() |
SELECT SIN(x) AS sin_x FROM math_tbl; |
参数需为数值或可转化为数值的字符串。 |
|
COS() |
SELECT COS(x) AS cos_x FROM math_tbl; |
参数需为数值或可转化为数值的字符串。 |
|
TAN() |
SELECT TAN(x) AS tan_x FROM math_tbl; |
参数需为数值或可转化为数值的字符串。 |
|
COT() |
SELECT COT(x) AS cot_x FROM math_tbl; |
参数需为数值或可转化为数值的字符串。 |
|
FLOOR() |
SELECT FLOOR(product_price) AS floor_product FROM Products; |
参数需为数值或可转化为数值的字符串。 |
|
CEILING() |
SELECT CEILING(product_price) AS ceiling_product FROM Products; |
参数需为数值或可转化为数值的字符串。 |
|
ABS() |
SELECT ABS(x) AS abs_x FROM math_tbl; |
参数的范围为-9223372036854775807到9223372036854775807，超过此范围会报错。 |
|
LOG() |
SELECT LOG(x) AS log_x FROM math_tbl; |
参数需为大于0的数值或可转化为相应数值的字符串。 |
|
LN() |
SELECT LN(x) AS ln_x FROM math_tbl; |
参数需为大于0的数值或可转化为相应数值的字符串。 |
|
EXP() |
SELECT EXP(x) AS exp_x FROM math_tbl; |
参数的范围为-∞到709，超过此范围会报错。 |
|
函数表达式 |
示例 |
支持范围 |
|---|---|---|
|
TRIM() |
SELECT TRIM(' hello, world ') AS trim_character FROM Character; |
参数需为字符串相关类型。 |
使用前，请先查看该函数是否在支持范围，建议使用支持范围内的函数。如果在范围外使用，返回结果与MySQL中的返回结果可能不一致。
不支持的函数
|
函数 |
限制条件 |
|---|---|
|
ROW_COUNT() |
DDM 暂不支持ROW_COUNT()函数。 |
|
COMPRESS() |
DDM 暂不支持COMPRESS()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
SHA() |
DDM 暂不支持SHA()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
SHA1() |
DDM 暂不支持SHA1()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
MD5() |
DDM 暂不支持MD5()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
AES_ENCRYPT() |
DDM 暂不支持AES_ENCRYPT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
AES_DECRYPT() |
DDM 暂不支持AES_DECRYPT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
YEARWEEK() |
DDM 暂不支持YEARWEEK()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数。 |
|
TIME_FORMAT() |
DDM暂不支持TIME_FORMAT()函数。如果无法确认函数是否能下推到RDS，请不要使用该函数，建议使用DATE_FORMAT()函数。 |
