MySQL (3) -- functions and predicates, mysql -- function predicates

MySQL (3) -- functions and predicates, mysql -- function predicates

As mentioned in the previous article, repeat it as a summary.

I. function 1. arithmetic functions

NUMERIC is a data type supported by most DBMS. It is used to specify the value size in the form of NUMBERIC (All digits, decimal places.

-- +-*/-- Remainder SELECT n, p, MOD (n, p) AS mod_col FROM SampleMath; -- absolute value SELECT m, ABS (m) AS abs_col FROM SampleMath; -- rounding SELECT m, n, ROUND (m, n) AS round_col FROM SampleMath;

2. String Functions

-- String concatenation SELECT str1, str2, str3, CONCAT (str1, str2, str3) AS str_concat FROM SampleStr -- String length select str1, LENGTH (str1) AS len_str FROM SampleStr; -- case-sensitive conversion SELECT str1, UPPER/LOWER (str1) AS low_str FROM SampleStr WHERE str1 IN ('abc', 'abc', 'abc ') -- REPLACE str2 in str1 with str3SELECT str1, str2, str3, REPLACE (str1, str2, str3) AS rep_str FROM SampleStr; -- string truncation FROM the starting position of the interception FOR the number of characters SELECT str1, SUBSTRING (str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;

3. Date Functions

-- Current date SELECT CURRENT_DATE; -- current time SELECT CURRENT_TIME; -- current timestamp SELECT CURRENT_TIMESTAMP; -- tract (year from CURRENT_TIMESTAMP) AS year, EXTRACT (month from CURRENT_TIMESTAMP) AS month, EXTRACT (day from CURRENT_TIMESTAMP) AS day, EXTRACT (hour from CURRENT_TIMESTAMP) AS hour, EXTRACT (minute from CURRENT_TIMESTAMP) AS minute, EXTRACT (second from CURRENT_TIMESTAMP) AS second;

4. conversion functions

-- Type conversion select cast ('200' as signed integer) AS int_col; select cast ('2017-12-14 'as date) AS date_col; -- convert NULL to other values -- return the first 1st non-NULL values on the left of the Variable Parameter select coalesce (NULL, 1) AS col_1, COALESCE (NULL, 'test', NULL) AS col_2, COALESCE (NULL, NULL, '2017-11-01 ') AS col_3;

5. Aggregate functions

COUNT, SUM, AVG, MAX, MIN

More functions and operators

Ii. predicates

A predicate is used to determine whether a record meets certain conditions ". If such a record exists, TRUE is returned. If the record does not exist, FALSE is returned ).

-- Partial consistent query LIKE -- Range Query BETWEEN -- determine whether it is nullis null, is not null--OR simple usage IN -- the subject of the predicate IS "record"... I don't understand! EXISTS

Iii. CASE expression

-- Format case when <evaluate expression> THEN <expression>... ELSE <expression> END

-- WHEN the CASE expression is searched, SELECT product_name, case when product_type = 'clothes 'then' A: '| product_type WHEN product_type = 'Office supplies 'then' B: '| product_type WHEN product_type = 'kitchen utensils 'then 'C:' | product_type else null end as abc_product_type FROM Product; -- SELECT product_name WHEN using a simple CASE expression, CASE product_type WHEN 'then' A: '| product_type WHEN 'Office supplies 'then' B:' | product_type WHEN 'kitchen utensils 'then' C: '| product_type else null end as abc_product_type FROM Product;

-- Use the IF expression instead of the CASE expression SELECT product_name, IF (product_type = 'hangzhou', CONCAT ('a: ', product_type), NULL) is null and product_type = 'Office supplies ', CONCAT (' B: ', product_type), IF (product_type = 'clothes', CONCAT ('a: ', product_type ), NULL) is null and product_type = 'kitchen utensils ', CONCAT ('C:', product_type), IF (product_type = 'clothes ', CONCAT ('a :', product_type), NULL) is null and product_type = 'Office supplies ', CONCAT (' B: ', product_type), IF (product_type = 'clothes', CONCAT ('a :', product_type), NULL) AS abc_product_type FROM Product;