MySQL (iii)--functions and predicates

The previous article has been involved, here as a summary of the reorganization.

First, function 1, arithmetic function

NUMERIC is a data type that is supported by most DBMS, specifying the size of a numeric value by Numberic (the total number of digits, decimal digits).

-- +-*/--余数SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;--绝对值SELECT m, ABS(m) AS abs_col FROM SampleMath;--四舍五入SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;

2. String functions

--字符串拼接SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat FROM SampleStr--字符串长度SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;--大小写转换SELECT str1, UPPER/LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc')--字符串替换str1中的str2换为str3SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr; --字符串截取 FROM截取的起始位置FOR截取的字符数SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;

3. Date function

--当前日期SELECT CURRENT_DATE;--当前时间SELECT CURRENT_TIME;--当前时间戳SELECT CURRENT_TIMESTAMP;--截取日期元素SELECT CURRENT_TIMESTAMP,       EXTRACT(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 function

--类型转换SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;SELECT CAST('2009-12-14' AS DATE) AS date_col;--将 NULL 转换为其他值--返回可变参数中左侧开始第 1 个不是 NULL 的值SELECT COALESCE(NULL, 1)                  AS col_1,       COALESCE(NULL, 'test', NULL)       AS col_2,       COALESCE(NULL, NULL, '2009-11-01') AS col_3;

5. Aggregation function

COUNT, SUM, AVG, MAX, MIN

More functions and operators

Second, predicate

The function of a predicate is "to determine whether there is a record that satisfies a certain condition". If such a record exists, it returns True (True) and returns False if it does not exist (false).

--部分一致查询LIKE--范围查询BETWEEN--判断是否为NULLIS NULL、IS NOT NULL--OR 的简便用法IN--谓词的主语是“记录”...没懂！EXISTS

Third, Case expression

--格式CASE WHEN <求值表达式> THEN <表达式>     WHEN <求值表达式> THEN <表达式>     WHEN <求值表达式> THEN <表达式>       .       .       .     ELSE <表达式>END

 --using the Search case expression, select product_name when product_type = ' clothes ' Then ' A: ' | |product_type When product_type = ' office supplies ' then ' B: ' | |product_type when product_type = ' kitchen utensils ' TH EN ' C: ' | |product_type ELSE NULL END as Abc_product_type from product;--using a simple CASE expression select Product_n Ame, case product_type when ' clothes ' 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;  

--使用IF代替CASE表达式SELECT  product_name,       IF( IF( IF(product_type = '衣服',                   CONCAT('A ：', product_type), NULL)               IS NULL AND product_type = '办公用品',                   CONCAT('B ：', product_type),           IF(product_type = '衣服',              CONCAT('A ：', product_type), NULL))                  IS NULL AND product_type = '厨房用具',                     CONCAT('C ：', product_type),                  IF( IF(product_type = '衣服',                       CONCAT('A ：', product_type), NULL)               IS NULL AND product_type = '办公用品',                  CONCAT('B ：', product_type),           IF(product_type = '衣服',              CONCAT('A ：', product_type),         NULL))) AS abc_product_type FROM Product;

MySQL (iii)--functions and predicates