資料庫函式
■ MySQL提供了實作各種功能的函式
■ 常用的函式分類
- 數學函式
- 聚合函式
- 字串函式
- 日期時間函式
一、數學函式
常用的數學函式
abs(x)：回傳x的絕對值
rand() ：回傳0到1的亂數
mod(x， y) ：回傳x除以y以后的余數
power(x， y)“：回傳x的y次方
round(x) ：回傳離x最近的整數
round(x， y)：保留x的y位小數四舍五入后的值
sqrt(x) ：回傳x的平方根
truncate(x， y)： 回傳數字x截斷為y位小數的值
ceil(x) ：回傳大于或等于x的最小整數
floor(x)： 回傳小于或等于x的最大整數
greatest(x 1， x 2…) ：回傳集合中最大的值
least(x 1， x 2…)： 回傳集合中最小的值
實體：
回傳-99的絕對值
mysql> select abs(-99);
+---------+
| abs(-99) |
+---------+
| 99 |
+---------+
1 row in set (0.00 sec)
回傳0到1的亂數
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.9157036365156096 |
+--------------------+
1 row in set (0.00 sec)
回傳x除以y以后的余數
mysql> select mod(8,3);
+----------+
| mod(8,3) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
回傳2的5次方
mysql> select power(2,5);
+------------+
| power(2,5) |
+------------+
| 32 |
+------------+
1 row in set (0.00 sec)
回傳離7.2最近的整數
mysql> select round(7.2);
+------------+
| round(7.2) |
+------------+
| 7 |
+------------+
1 row in set (0.00 sec)
回傳16的平方根
mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
保留亂數的兩位小數，四舍五入后的值
mysql> select truncate(rand(),2);
+--------------------+
| truncate(rand(),2) |
+--------------------+
| 0.61 |
+--------------------+
1 row in set (0.01 sec)
保留1.234的兩位小數
mysql> select round(1.234,2);
+----------------+
| round(1.234,2) |
+----------------+
| 1.23 |
+----------------+
1 row in set (0.01 sec)
回傳大于或等于x的最小整數， 回傳數字x截斷為y位小數的值
mysql> select ceil(2.3),floor(2.4);
+-----------+------------+
| ceil(2.3) | floor(2.4) |
+-----------+------------+
| 3 | 2 |
+-----------+------------+
1 row in set (0.00 sec)
回傳集合中最小的值，回傳集合中最大的值
mysql> select least(12,43,22),greatest(13,24,11);
+-----------------+--------------------+
| least(12,43,22) | greatest(13,24,11) |
+-----------------+--------------------+
| 12 | 24 |
+-----------------+--------------------+
1 row in set (0.00 sec)二、聚合函式
■ 對表中資料記錄進行集中概述而設計的一類函式
■ 常用的聚合函式
avg() ：回傳指定列的平均值
count() ：回傳指定列中非NULL值的個數
min() ：回傳指定列的最小值
max() ：回傳指定列的最大值
sum() ：回傳指定列的所有值之和
實體：以下表為例
mysql> select * from a_player;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 88 |
| 2 | lisi | 89 |
| 3 | wangwu | 67 |
| 4 | zhaoliu | 90 |
| 5 | xuli | 80 |
| 6 | keke | 75 |
+----+----------+-------+
6 rows in set (0.00 sec)
mysql> select avg(score) from a_player;
+------------+
| avg(score) |
+------------+
| 81.5000 |
+------------+
1 row in set (0.00 sec)
mysql> select count(name) from a_player;
+-------------+
| count(name) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
mysql> select min(score) from a_player;
+------------+
| min(score) |
+------------+
| 67 |
+------------+
1 row in set (0.00 sec)
mysql> select max(score) from a_player;
+------------+
| max(score) |
+------------+
| 90 |
+------------+
1 row in set (0.00 sec)
mysql> select sum(score) from a_player;
+------------+
| sum(score) |
+------------+
| 489 |
+------------+
1 row in set (0.00 sec)三、字串函式
常用的字串函式
length(x) ：回傳字串x的長度
trim() ：回傳去除指定格式的值
concat(x， y)： 將提供的引數x和y拼接成一個字串
upper(x) ：將字串x的所有字母變成大寫字母
lower(x) ：將字串x的所有字母變成小寫字母
left(x， y) ：回傳字串x的前y個字符
right(x， y) ：回傳字串x的后y個字符
repeat(x， y) ：將字串x重復y次
space(x)： 回傳x個空格
replace(x， y， z) ：將字串z替代字串x中的字串y
strcmp(x， y) ：比較x和y， 回傳的值可以為-1， 0， 1
substring(x， y， z)： 獲取從字串x中的第y個位置開始長度為z的字串
reverse(x)將字串x反轉
實體：
回傳字串x的長度
mysql> select length ('adf');
+----------------+
| length ('adf') |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
回傳去除指定格式的值
l> select trim(' adf');
+---------------+
| trim(' adf') |
+---------------+
| adf |
+---------------+
1 row in set (0.00 sec)
拼接成一個字串
mysql> select concat('adc','wef');
+---------------------+
| concat('adc','wef') |
+---------------------+
| adcwef |
+---------------------+
1 row in set (0.00 sec)
將字串x的所有字母變成大寫字母
mysql> select upper ('qqq');
+---------------+
| upper ('qqq') |
+---------------+
| QQQ |
+---------------+
1 row in set (0.00 sec)
將字串x的所有字母變成小寫字母
mysql> select lower ('AQ');
+--------------+
| lower ('AQ') |
+--------------+
| aq |
+--------------+
1 row in set (0.00 sec)
回傳字串x的前2個字符
mysql> select left('wecwc',2);
+-----------------+
| left('wecwc',2) |
+-----------------+
| we |
+-----------------+
1 row in set (0.00 sec)
回傳字串的后3個字符
mysql> select right('wecwc',3);
+------------------+
| right('wecwc',3) |
+------------------+
| cwc |
+------------------+
1 row in set (0.00 sec)
mysql> select concat(left('adw',2),right('wccev',4));
+----------------------------------------+
| concat(left('adw',2),right('wccev',4)) |
+----------------------------------------+
| adccev |
+----------------------------------------+
1 row in set (0.00 sec)
將字串d重復4次
mysql> select repeat ('d',4);
+-----——）-----------+
| repeat ('d',4) |
+----------------+
| dddd |
+----------------+
1 row in set (0.01 sec)
回傳3個空格
mysql> select space (3);
+-----------+
| space (3) |
+-----------+
| |
+-----------+
1 row in set (0.00 sec)
用字串ee替代字串abcd中的ab
mysql> select replace('abcd','ab','ee');
+---------------------------+
| replace('abcd','ab','ee') |
+---------------------------+
| eecd |
+---------------------------+
1 row in set (0.00 sec)
比較x和y， 回傳的值可以為-1（前者小于后者），0（等于）， 1（大于）
mysql> select strcmp('a','b');
+--------------+
| strcmp('a','b') |
+--------------+
| -1 |
+--------------+
1 row in set (0.00 sec)
獲取從字串中的第2個位置開始長度為2的字串
mysql> select substring('abcd',2,2);
+-----------------------+
| substring('abcd',2,2) |
+-----------------------+
| bc |
+-----------------------+
1 row in set (0.00 sec)
將字串x反轉
mysql> select reverse('abcde');
+------------------+
| reverse('abcde') |
+------------------+
| edcba |
+------------------+
1 row in set (0.00 sec)
mysql> select upper(reverse('abcde'));
+-------------------------+
| upper(reverse('abcde')) |
+-------------------------+
| EDCBA |
+-------------------------+
1 row in set (0.00 sec)四、日期時間函式
常用時間函式：
實體：
mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate() | curtime() | now() |
+------------+-----------+---------------------+
| 2020-11-04 | 14:41:54 | 2020-11-04 14:41:54 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)
mysql> select month('2020-11-02'),week('2020-11-02'),hour('21:11');
+---------------------+--------------------+---------------+
| month('2020-11-02') | week('2020-11-02') | hour('21:11') |
+---------------------+--------------------+---------------+
| 11 | 44 | 21 |
+---------------------+--------------------+---------------+
1 row in set (0.00 sec)
mysql> select minute(now()),second(now());
+---------------+---------------+
| minute(now()) | second(now()) |
+---------------+---------------+
| 34 | 41 |
+---------------+---------------+
1 row in set (0.01 sec)
mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
| 4 | 4 | 309 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/205112.html
標籤：其他