更新时间:2022-05-07 GMT+08:00
分享

函数

支持的函数

表1 操作符函数

函数表达式

示例

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('Huawei') ORDER BY vendor_name

IFNULL

SELECT IFNULL(product_id, 0) FROM Products;

说明:
  • 3月20日之前已经创建的实例,拆分表不支持IFNULL与聚合函数的嵌套函数调用,如:select IFNULL(sum(yan),0) from shenhai,结果会和预期不一样。
  • 3月20日之后的实例,拆分表只支持IFNULL与聚合函数的一层嵌套函数调用。
表2 时间日期函数

函数表达式

示例

支持范围

DAY()

SELECT * FROM TAB_DT WHERE DAY(dt)=21

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

MONTH()

SELECT * FROM TAB_DT WHERE MONTH(dt)=12

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

YEAR()

SELECT * FROM TAB_DT WHERE YEAR(dt)=2018

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

DAYOFYEAR()

SELECT * FROM TAB_DT WHERE DAYOFYEAR(dt)=365

SELECT * FROM TAB_DT WHERE dt='2018-12-31'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

DAYOFWEEK()

SELECT * FROM TAB_DT WHERE DAYOFWEEK(dt)=6

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

WEEKOFYEAR()

SELECT * FROM TAB_DT WHERE WEEKOFYEAR(dt)=51

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

-

TIME()

SELECT * FROM TAB_DT WHERE TIME(dt)='01:02:03'

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的时间、日期时间表达式或字符串。

TIME_TO_SEC()

SELECT * FROM TAB_DT WHERE TIME_TO_SEC(dt)=3603

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:00:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:00:03')

参数需为合法的时间、日期时间表达式或字符串。

SEC_TO_TIME()

SELECT * FROM TAB_DT WHERE SEC_TO_TIME(dt)='00:01:00'

SELECT * FROM TAB_DT WHERE dt=60

INSERT INTO TAB_DT(id,dt) VALUES(1,60)

参数需为数值或可转化为数值的字符串。

SECOND()

SELECT * FROM TAB_DT WHERE SECOND(dt)=3

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的时间、日期时间表达式或字符串。

MINUTE()

SELECT * FROM TAB_DT WHERE MINUTE(dt)=2

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的时间、日期时间表达式或字符串。

HOUR()

SELECT * FROM TAB_DT WHERE HOUR(dt)=1

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的时间、日期时间表达式或字符串。

DAYNAME()

SELECT * FROM TAB_DT WHERE DAYNAME(dt)='Friday'

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

MONTHNAME()

SELECT * FROM TAB_DT WHERE MONTHNAME(dt)='January'

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

LAST_DAY()

SELECT * FROM TAB_DT WHERE LAST_DAY(dt)='2021-01-31'

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

DAYOFWEEK()

SELECT * FROM TAB_DT WHERE DAYOFWEEK(dt)=6

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

DAYOFMONTH()

SELECT * FROM TAB_DT WHERE DAYOFWEEK(dt)=6

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

DAYOFYEAR()

SELECT * FROM TAB_DT WHERE DAYOFYEAR(dt)=365

SELECT * FROM TAB_DT WHERE dt='2021-12-31 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-12-31 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

WEEKOFYEAR()

SELECT * FROM TAB_DT WHERE WEEKOFYEAR(dt)=53

SELECT * FROM TAB_DT WHERE dt='2021-12-31 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-12-31 01:02:03')

参数需为合法的日期、日期时间表达式或字符串。

DATE_ADD(

date, INTERVAL expr unit

)

SELECT * FROM TAB_DT WHERE DATE_ADD(dt,INTERVAL 1 YEAR)='2022-01-01 01:02:03'

SELECT * FROM TAB_DT WHERE dt='2021-01-01 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-01 01:02:03')

  • date参数需为合法的时间、日期、日期时间表达式或字符串。
  • expr为从date开始加减运算的间隔值,要求为整数或可转化为整数的字符串。
  • unit为单位,暂时可选SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。
  • 当date为日期类型时,"1000-01-01"是下边界。在运算时如果存在越界行为,运算可能出错。

DATE_SUB(date, INTERVAL expr unit)

SELECT * FROM TAB_DT WHERE DATE_SUB(dt,INTERVAL -1 DAY)='2021-01-02 01:02:03'

SELECT * FROM TAB_DT WHERE dt='2021-01-02 01:02:03'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2021-01-02 01:02:03')

  • date参数需为合法的时间、日期、日期时间表达式或字符串。
  • expr为从date开始加减运算的间隔值,要求为整数或可转化为整数的字符串。
  • unit为单位,暂时可选SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR。
  • 当date为日期类型时,"1000-01-01"是下边界。在运算时如果存在越界行为,运算可能出错。
表3 数学函数

函数表达式

示例

支持范围

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的数值或可转化为相应数值的字符串。

表4 字符串函数

函数表达式

示例

支持范围

TRIM()

SELECT TRIM(' hello, world ') AS trim_character FROM Character

参数需为字符串相关类型。

有支持范围的函数请在支持范围内使用,支持范围外的用法不保证与mysql的行为一致。

不支持的函数

表5 函数的限制

函数

限制条件

ROW_COUNT()

暂不支持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,请不要使用该函数。

分享:

    相关文档

    相关产品

关闭导读