有你在真好 的个人博客
SQL中各种函数
阅读：2231 添加日期：2021/3/27 23:21:38 原文链接：https://www.toutiao.com/item/6581637301003092488/
SQL中各种函数

  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;
ICP备案号：苏ICP备14035786号-1 苏公网安备 32050502001014号