更新时间:2022-08-16 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('Huawee') 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')

表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

不支持的函数

表4 函数的限制

函数

限制条件

函数

暂不支持row_count()函数。