MySQL语法入门（一）
根本运算符应用
SELECT 1 + 2;-- 3SELECT 1 / 0;-- nullSELECT 1 + NULL;-- nullSELECT '2' * '4';-- 8SELECT '2f' + 3;-- 5SELECT 2.2 + 2;-- 4.2SELECT 1 = 2;-- 0SELECT 1 = 1;-- 1SELECT 1 = NULL;-- nullSELECT 1 <=> NULL;-- 0SELECT 'hello' = 'HELLO';-- 1SELECT 'hello' = ' HELLO ';-- 0SELECT BINARY 'hello' = 'HELLO';-- 0SELECT 'a' > 'b';-- 0SELECT 'b' > 'a';-- 1SELECT 'b' BETWEEN 'a' AND 'c';-- 1SELECT 'a' BETWEEN 'a' AND 'c';-- 1SELECT 'c' BETWEEN 'a' AND 'c';-- 1 >= <=SELECT NOT 'b' BETWEEN 'a' AND 'c';-- 0SELECT NOT 'a' BETWEEN 'a' AND 'c';-- 0SELECT NOT 'c' BETWEEN 'a' AND 'c';-- 0 >= <=SELECT 3 IN (3 , 4, 5);-- 1SELECT 1 IN (3 , 4, 5);-- 0SELECT NULL IN (3 , 4, 5);-- nullSELECT NULL IN (3 , 4, 5, NULL);-- nullSELECT 'hello' LIKE 'he%';-- 1SELECT 'hel%' LIKE 'hello';-- 0SELECT 1 BETWEEN 1 AND 2;SELECT NOT 1;-- 0SELECT ! 1;-- 0SELECT (1 > 2) AND (19 > 10);-- 0SELECT (11 > 2) && (19 > 10);-- 1SELECT (1 > 2) OR (19 > 10);-- 1SELECT (21 > 2) || (19 > 10);-- 1SELECT (1 > 2) XOR (19 > 10);-- 1SELECT (21 > 2) XOR (19 > 10);-- 0SELECT 1 | 2;-- 3;001 | 010 -> 011SELECT 3 >> 1;-- 1; 11 >> 1 -> 1SELECT 3 << 1;-- 6; 11 << 1 -> 110SELECT 3 ^ 2;-- 1; 11 ^ 10 -> 01
根本数学函数应用
SELECT ABS(- 5);-- 5SELECT ABS(- 5.8);-- 5.8SELECT CEIL(1.2);-- 2SELECT CEILING(1.2);-- 2SELECT CEIL(1.6);-- 2SELECT CEILING(1.6);-- 2SELECT CEIL(- 1.2);-- -1SELECT CEILING(- 1.2);-- -1SELECT CEIL(- 1.6);-- -1SELECT CEILING(- 1.6);-- -1SELECT FLOOR(1.2);-- 1SELECT FLOOR(- 1.2);-- -2SELECT GREATEST(1, 4, 6);-- 6SELECT LEAST(1, 3, 2);-- 1SELECT MOD(10, 6);-- 4SELECT PI();-- 3.141593SELECT RAND();-- 0.9857855839522421SELECT ROUND(22.3563, 2);-- 22.36SELECT TRUNCATE(12.4562, 2);-- 12.45SELECT SIGN(1);-- 1 负数SELECT SIGN(0);-- 0 0SELECT SIGN(- 1.3);-- -1 正数SELECT POWER(2, 4);-- 16 SELECT POW(2, 4);-- 16SELECT EXP(1);-- 2.718281828459045SELECT EXP(2);-- '7.38905609893065'SELECT SQRT(4);-- 2SELECT SQRT(5);-- 2.23606797749979SELECT BIN(10);-- 1010SELECT OCT(10);-- 12SELECT HEX(10);-- ASELECT OCT(10);-- 12SELECT OCT(10);-- 12
根本字符串函数应用
SELECT LENGTH('hello');-- 5SELECT LCASE('helLO');-- helloSELECT LOWER('helLO');-- helloSELECT UCASE('hello');-- HELLOSELECT UPPER('hello');-- HELLOSELECT STRCMP('hello', 'yes');-- -1SELECT STRCMP('zworld', 'yes');-- 1SELECT POSITION('yes' IN 'yesman');-- 1SELECT REPLACE('hello', 'l', 'a');-- heaaoSELECT INSERT('hello world', 2, 3, 'bb');-- hbbo worldSELECT INSERT('hello world', 2, 1, 'bb');-- hbbllo worldSELECT CONCAT('hello', 'world');-- helloworldSELECT CONCAT_WS('@', 'hello', 'world');-- hello@worldSELECT LEFT('hello', 2);-- heSELECT RIGHT('hello', 2);-- loSELECT LPAD('hello', 7, 'a');-- aahello 右边填充指定字符 长度 7 需大于原字符串长度SELECT RPAD('hello', 7, 'b');-- hellobb 左边填充指定字符 长度 7 需大于原字符串长度SELECT LTRIM(' hello');-- helloSELECT RTRIM('hello ');-- helloSELECT TRIM(' hello ');-- helloSELECT SUBSTRING('hello', 2, 3);-- ellSELECT ASCII('0');-- 48SELECT ASCII('A');-- 65SELECT ASCII('a');-- 97
根本日期工夫函数应用
SELECT NOW();-- 2022-01-06 22:31:15SELECT CURTIME();-- 25:31:49SELECT CURDATE();-- 2022-01-06SELECT YEAR(20220106);-- 2022SELECT YEAR('20220106');-- 2022SELECT YEAR('2022-01-06');-- 2022SELECT YEAR('2022/01/06');-- 2022SELECT MONTH('2022/01/06');-- 1SELECT MONTHNAME('2022/01/06');-- JanuarySELECT DAYOFYEAR('2022/01/06');-- 6SELECT DAYOFWEEK('2022/01/06');-- 5SELECT DAYNAME('2022/01/06');-- ThursdaySELECT WEEK('2022/01/06');-- 1SELECT HOUR('25:31:49');-- 25SELECT MINUTE('25:31:49');-- 31SELECT SECOND('25:31:49');-- 49SELECT DATE_ADD(NOW(), INTERVAL 2 MONTH);-- 2022-03-06 22:40:55SELECT DATE_ADD(NOW(), INTERVAL 3 DAY);-- 2022-01-09 22:41:30SELECT DATE_SUB(NOW(), INTERVAL 3 YEAR);-- 2019-01-06 22:42:11