一. 常用的函式分類
? 數學函式
? 聚合函式
? 字串函式
? 日期時間函式
二. 常用的數學函式
1.rand() 回傳0到1的亂數
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.2819034413266028 |
+--------------------+
1 row in set (0.00 sec)
- mod(x,y) 回傳x除以y以后的余數:
mysql> select mod(7,3);
+----------+
| mod(7,3) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
3.round(x) 回傳離x最近的整數
mysql> select round(1.8);
+------------+
| round(1.8) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
4.round(x,y) 保留x的y位小數四舍五入后的值:
mysql> select round(1.78,1);
+---------------+
| round(1.78,1) |
+---------------+
| 1.8 |
+---------------+
1 row in set (0.00 sec)
5.truncate(x,y) 回傳數字x截斷為y位小數的值
mysql> select truncate(3.12345,3);
+---------------------+
| truncate(3.12345,3) |
+---------------------+
| 3.123 |
+---------------------+
1 row in set (0.00 sec)
6.ceil(x) 回傳大于或等于x的最小整數
mysql> select ceil(1.09); // 向下取整
+------------+
| ceil(1.09) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
7.floor(x) 回傳小于或等于x的最大整數
mysql> select floor(1.08);
+-------------+
| floor(1.08) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
三．聚合函式
- avg() 回傳指定列的平均值
mysql> select avg(id) as '平均值' from xc_user;
+--------------------+
| 平均值 |
+--------------------+
| 49.333333333333336 |
+--------------------+
1 row in set (0.00 sec)
2.count() 回傳指定列中非NULL值的個數
mysql> select count(id) as '非null個數' from xc_user;
+------------+
| 非null個數 |
+------------+
| 6 |
+------------+
1 row in set (0.00 sec)
- min() 回傳指定列的最小值，max() 回傳指定列的最大值：
mysql> select min(id) '最小值', max(id) '最大值' from xc_user;
+--------+--------+
| 最小值 | 最大值 |
+--------+--------+
| 46 | 52 |
+--------+--------+
1 row in set (0.00 sec)
4.sum() 回傳指定列的所有值之和
mysql> select sum(id) '和' from xc_user;
+------+
| 和 |
+------+
| 296 |
+------+
1 row in set (0.00 sec)
四．常用的字串函式
- length(x) 回傳字串x的長度
mysql> select length('abjcoiyuit');
+----------------------+
| length('abjcoiyuit') |
+----------------------+
| 10 |
+----------------------+
1 row in set (0.00 sec)
2.trim() 回傳去除指定格式的值(只能去除前后字符的空格，中間的不行)
mysql> select trim(' gh ty ');
+--------------------+
| trim(' gh ty ') |
+--------------------+
| gh ty |
+--------------------+
1 row in set (0.00 sec)
4.concat(x,y) 將提供的引數x和y拼接成一個字串
mysql> select concat('x','hfiwo');
+---------------------+
| concat('x','hfiwo') |
+---------------------+
| xhfiwo |
+---------------------+
1 row in set (0.00 sec)
5.upper(x) 將字串x的所有字母變成大寫字母；lower(x) 將字串x的所有字母變成小寫字母
mysql> select upper('anf'), lower('AHIBD');
+--------------+----------------+
| upper('anf') | lower('AHIBD') |
+--------------+----------------+
| ANF | ahibd |
+--------------+----------------+
1 row in set (0.00 sec)
6.left(x,y) 回傳字串x的前y個字符，right(x,y) 回傳字串x的后y個字符
mysql> select left('feiofghwv',3), right('feiowv',2);
+---------------------+-------------------+
| left('feiofghwv',3) | right('feiowv',2) |
+---------------------+-------------------+
| fei | wv |
+---------------------+-------------------+
1 row in set (0.00 sec)
7.replace(x,y,z) 將字串z替代字串x中的字串y
mysql> select replace('tress','ss','aa');
+----------------------------+
| replace('tress','ss','aa') |
+----------------------------+
| treaa |
+----------------------------+
1 row in set (0.00 sec)
8.strcmp(x,y) 比較x和y，回傳的值可以為-1,0,1
mysql> select strcmp(67,98);
+---------------+
| strcmp(67,98) |
+---------------+
| -1 |
+---------------+
1 row in set (0.00 sec)
mysql> select strcmp(2,1);
+-------------+
| strcmp(2,1) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(3,3);
+-------------+
| strcmp(3,3) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
9.substring(x,y,z) 獲取從字串x中的第y個位置開始長度為z的字串
mysql> select substring('mysqldiwnwknfs',3,4);
+---------------------------------+
| substring('mysqldiwnwknfs',3,4) |
+---------------------------------+
| sqld |
+---------------------------------+
1 row in set (0.00 sec)
10.reverse(x) 將字串x反轉
mysql> select reverse('adciwo');
+-------------------+
| reverse('adciwo') |
+-------------------+
| owicda |
+-------------------+
1 row in set (0.00 sec)
五．日期時間函式
- curdate() 回傳當前時間的年月日
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-09-13 |
+------------+
1 row in set (0.00 sec)
- curtime() 回傳當前時間的時分秒
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 00:51:01 |
+-----------+
1 row in set (0.00 sec)
- now() 回傳當前時間的日期和時間
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-09-13 00:51:26 |
+---------------------+
1 row in set (0.00 sec)
- month(x) 回傳日期x中的月份值
mysql> select month('2020-6-26');
+--------------------+
| month('2020-6-26') |
+--------------------+
| 6 |
+--------------------+
1 row in set (0.00 sec)
- week(x) 回傳日期x是年度第幾個星期
mysql> select week('2020-6-26');
+-------------------+
| week('2020-6-26') |
+-------------------+
| 25 |
+-------------------+
1 row in set (0.04 sec)
- hour(x) 回傳x中的小時值
mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
- minute(x) 回傳x中的分鐘值
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
| 54 |
+-------------------+
1 row in set (0.00 sec)
- second(x) 回傳x中的秒鐘值
mysql> select second(curtime());
+-------------------+
| second(curtime()) |
+-------------------+
| 29 |
+-------------------+
1 row in set (0.00 sec)
- dayofweek(x) 回傳x是星期幾，1星期日，2星期一
mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
10.dayofmonth(x) 計算日期x是本月的第幾天
mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
| 13 |
+-----------------------+
1 row in set (0.00 sec)
11.dayofyear(x) 計算日期x是本年的第幾天
mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
| 257 |
+----------------------+
1 row in set (0.00 sec)
歡迎留言討論，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/34414.html
標籤：其他
上一篇：一圖了解范式判斷