1. ####################
2. #各种各样的函数
3. ####################
4. CREATE TABLE SampleMath
5. (m NUMERIC (10,3), n INTEGER, p INTEGER);
7. INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
8. INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
9. INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
10. INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
11. INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
12. INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
13. INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
14. INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
15. INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
16. INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
17. INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
19. select * from SampleMath;
21. -- 1.常见算术函数
22. /*
23. 1.abs_col:ABS(m)的返回值（绝对值）
24. 2.mod_col:MOD(n,p)的返回值（n÷p的余数）
25. 3.round_col：ROUND(m,n)的返回值（四舍五入的结果
26. */
29. CREATE TABLE SampleStr
30. (str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40));
31. INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,'rt',NULL);
32. INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);
33. INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,'太郎' ,'是我');
34. INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' , NULL ,NULL);
35. INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL);
36. INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);
37. INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL);
38. INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL);
39. INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,'abc' ,'ABC');
40. INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' , 'abc' ,'ABC');
41. INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');
43. -- 2.常见字符串函数
44. #1.拼接concat()
45. SELECT str1, str2, str3, CONCAT(str1, str2, str3)
46. AS str_concat FROM SampleStr;
47. #2.length()
48. #3.lower() upper()
49. #4.REPLACE(对象字符串，替换前的字符串，替换后的字符串)
50. #5.substring（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
53. -- 3.常见日期函数
54. #1.select current_date 返回当前日期
55. #2.select current_time 返回当前时间
56. #3.select current_timestamp;返回当前时间和日期
57. #4.extract(日期元素 from 日期）
58. SELECT CURRENT_TIMESTAMP,
59. EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
60. EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
61. EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
62. EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
63. EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
64. EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
66. -- 4.转换函数
67. -- 类型转换&值的转换
68. -- 类型转换
69. #1.CAST（转换前的值 AS 想要转换的数据类型）
70. SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
71. SELECT CAST('2009-12-14' AS DATE) AS date_col;
73. -- 值的转换
74. #1. 将null转换其他值
75. -- COALESCE(数据1，数据 2，数据 3……)
76. -- 该函数会返回可变参数 A 中左侧开 始第1个不是 NULL的值
77. SELECT COALESCE(NULL, 1) AS col_1,
78. COALESCE(NULL, 'test', NULL) AS col_2,
79. COALESCE(NULL, NULL, '2009-11-01') AS col_3;
82. SELECT COALESCE(str2, 'NULL') FROM SampleStr;-- 将null变成了'null'
83. select str2 from samplestr;