MYSQL中的函數（第十五課）

MySQL functions

MySQL函數

In this part of the MySQL tutorial, we will cover MySQL built-in functions.

MySQL built-in functions can be categorised into several groups.

在MySQL教程的這一部分中，我們將介紹MySQL內置函數。

MySQL內置函數可以分為幾組。

• Mathematical functions 數學函數
• Aggregate functions 聚合函數
• String functions 字符串函數
• Date and time functions 日期和時間函數
• System Functions 系統函數

Here we show only a portion of all MySQL functions. To get the full list of available functions, consult the MySQL reference manual.

在這裡，我們僅顯示所有MySQL函數的一部分。 要獲取可用功能的完整列表，請查閱MySQL參考手冊。

Mathematical functions

數學函數

MySQL supports multiple mathematical functions.

MySQL支持多種數學函數。

<code>mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.786536605829873 |
+-------------------+
/<code>

The RAND() function returns a random number from the <0, 1> interval.

RAND（）函數從<0，1>間隔返回一個隨機數。

<code>
mysql
> 
SELECT
 
ABS
(-
3
), 
PI
(), 
SIN
(
0.5
);
+
---------
+
----------
+ 
-------------------
+
| 
ABS
(-
3
) | 
PI
()     | 
SIN
(
0.5
)          |
+
---------
+
----------
+
-------------------
+
|       
3
 | 
3
.141593
 | 
0
.479425538604203
 |
+
---------
+
----------
+
-------------------
+
/<code>

The ABS() function returns the absolute value of a number. The PI() function gives the value of PI. And the SIN() function computes the sine of an argument.

ABS（）函數返回數字的絕對值。 PI（）函數提供PI的值。 SIN（）函數計算參數的正弦值。

<code>
mysql
> 
SELECT
 
BIN 
(
22
), 
OCT
(
22
), 
HEX
(
22
);
+
---------
+
---------
+
---------
+
| 
BIN
(
22
) | 
OCT
(
22
) | 
HEX
(
22
) |
+
---------
+
---------
+
---------
+
| 
10110
   | 
26
      | 
16
      |
+
---------
+
---------
+
---------
+
/<code>

We use functions to give binary, octal and hexadecimal representation of decimal 22.

我們使用函數給出十進制22的二進制，八進制和十六進制表示形式。

<code>
mysql
> 
SELECT
 
CEIL
(
11.256
), 
FLOOR
(
11.256
), 
ROUND
(
11.256
, 
2
);
+
--------------
+
---------------
+
------------------
+
| 
CEIL
(
11.256
) | 
FLOOR
(
11.256
) | 
ROUND
(
11.256
, 
2
) |
+
--------------
+
---------------
+
------------------ 
+
|           
12
 |            
11
 |            
11
.26
 |
+
--------------
+
---------------
+
------------------
+
/<code>

The CEIL() function rounds the value to the smallest following integer. The FLOOR() function rounds the value to the largest previous integer. The ROUND() returns a number rounded to a specified number of decimal places.

CEIL（）函數將值舍入為最小的後續整數。 FLOOR（）函數將值舍入為最大的先前整數。 ROUND（）返回一個四捨五入到指定小數位數的數字

趕緊動手試試吧！

<code>
mysql
> 
SELECT
 
POW
(
3
, 
3
), 
SQRT
(
9
);
+
-----------
+
--------- 
+
| 
POW
(
3
, 
3
) | 
SQRT
(
9
) |
+
-----------
+
---------
+
|        
27
 |       
3
 |
+
-----------
+
---------
+
/<code>

The power and the square root functions.

冪和平方根函數。

<code>mysql> SELECT DEGREES(2*PI());
+-----------------+
| DEGREES(2*PI()) |
+-----------------+
|             360 |
+-----------------+
/<code>

The DEGREES() function computes degrees from radians.

DEGREES（）函數根據弧度計算度數。

Aggregate functions

Aggregate functions operate on sets of values.

<code>mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id |
 Name       
| Cost   |
+----+------------+--------+
|  1 |
 Audi       
|  52642 |
|  2 |
 Mercedes   
|  57127 |
|  3 |
 Skoda      
|   9000 |
|  4 |
 Volvo      
|  29000 |
|  5 |
 Bentley    
| 350000 |
|  6 |
 Citroen    
|  21000 |
|  7 |
 Hummer     
|  41400 |
|  8 |
 Volkswagen 
|  21600 |
+----+------------+--------+
/<code>

We have the Cars table.

<code>
mysql
> 
SELECT
 
MIN
(Cost), 
MAX
(Cost), 
AVG
(Cost)
    
-
> 
FROM
 
Cars
;
+
-----------
+
-----------
+
------------
+
| 
MIN
(Cost) | 
MAX
(Cost) | 
AVG
(Cost)  |
+
-----------
+
-----------
+
------------
+
|      
9000
 |    
350000
 | 
72721
.1250
 |
+
-----------
+
-----------
+ 
------------
+
/<code>

We use the MIN(), MAX() and AVG() aggregate functions to compute the minimal price, maximal price and the average price of cars in the table.

我們使用MIN（），MAX（）和AVG（）聚合函數來計算表中汽車的最低價格，最高價格和平均價格。

<code>
mysql
> 
SELECT
 
SUM
(Cost), 
COUNT
(Id), 
STD
(Cost), 
    
-
> 
VARIANCE
(Cost) 
FROM
 
Cars
;
+
-----------
+
-----------
+
-------------
+
------------------
+
| 
SUM
(Cost) | 
COUNT
(Id) | 
STD
(Cost)   | 
VARIANCE
(Cost)   |
+
-----------
+
----------- 
+
-------------
+
------------------
+
|    
581769
 |         
8
 | 
105931
.1676
 | 
11221412265
.3594
 |
+
-----------
+
-----------
+
-------------
+
------------------
+
/<code>

We use the SUM() function to get the sum of all values in the Cost column. We count the number of cars in the table with the COUNT() function. Finally, we get the standard deviation and variance using the STD() and VARIANCE() functions.

我們使用SUM（）函數來獲取Cost列中所有值的總和。 我們使用COUNT（）函數計算表中的汽車數量。 最後，我們使用STD（）和VARIANCE（）函數獲得標準偏差和方差。

String functions

字符串函數

In this group we have various strings related functions.

在這一組中，我們有各種與字符串相關的函數.

<code>
mysql
> 
SELECT
 
LENGTH
(
'ZetCode'
), 
UPPER
(
'ZetCode'
), 
LOWER
(
'ZetCode'
);
+
-------------------
+
------------------
+
------------------
+
| 
LENGTH
(
'ZetCode'
) | 
UPPER
(
'ZetCode'
) | 
LOWER
(
'ZetCode'
) |
+
-------------------
+
------------------
+
------------------
+
|                 
7
 | 
ZETCODE
          | 
zetcode
          |
+
------------------- 
+
------------------
+
------------------
+
/<code>

The LENGTH() function returns the length of a string. The UPPER() function converts characters into upper-case letters. The LOWER() function converts characters into lower-case letters.

LENGTH（）函數返回字符串的長度。 UPPER（）函數將字符轉換為大寫字母。 LOWER（）函數將字符轉換為小寫字母.

<code>ysql> SELECT LPAD(RPAD(
"ZetCode"
, 
10
, 
"*"
), 
13
, 
"*"
);
+ 
| LPAD(RPAD(
"ZetCode"
, 
10
, 
"*"
), 
13
, 
"*"
) |
+ 
| ***ZetCode***                           |
+ 
/<code>

We use the LPAD() and RPAD() functions to append and prepend characters to a specified string. The "ZetCode" string has 7 characters. The RPAD() function appends 3 '*' characters to the string, which will be now 10 characters long.

我們使用LPAD（）和RPAD（）函數將字符追加和前置到指定的字符串。 “ ZetCode”字符串包含7個字符。 RPAD（）函數將3個'*'字符追加到字符串中，該字符現在將為10個字符長度.

<code>
mysql
> 
SELECT
 
REVERSE
(
'ZetCode'
), 
REPEAT
(
'*'
, 
6
);
+
--------------------
+
----------------
+
| 
REVERSE
(
'ZetCode'
) | 
REPEAT
(
'*'
, 
6
) |
+
--------------------
+
----------------
+
| 
edoCteZ
            | ******         |
+
--------------------
+
----------------
+
/<code>

The REVERSE() function reverses the characters in a string. The REPEAT() function repeats a string specified number of times.

REVERSE（）函數可取反字符串中的字符。 REPEAT（）函數重複指定次數的字符串。

<code> 
mysql
> 
SELECT
 
LEFT
(
'ZetCode'
, 
3
), 
RIGHT
(
'ZetCode'
, 
3
), 
    
-
> 
SUBSTRING
(
'ZetCode'
, 
3
, 
3
);
+
--------------------
+
---------------------
+
----------------------------
+
| 
LEFT
(
'ZetCode'
, 
3
) | 
RIGHT
(
'ZetCode'
, 
3
) | 
SUBSTRING
(
'ZetCode'
, 
3
,  
3
) |
+
--------------------
+
---------------------
+
----------------------------
+
| 
Zet
                | 
ode
                 | 
tCo
                        |
+
--------------------
+
---------------------
+
----------------------------
+
/<code>

The LEFT() function returns 3 leftmost characters, the RIGHT() function returns 3 characters from the right. The SUBSTRING() function returns three characters from the third position of the string.

LEFT（）函數返回最左邊的3個字符，RIGHT（）函數從右邊返回3個字符。 SUBSTRING（）函數從字符串的第三位置返回三個字符。

<code>
mysql
> 
SELECT
 
STRCMP
(
'byte'
, 
'byte'
), 
CONCAT
(
'three'
, 
' apples'
);
+
------------------------
+ 
----------------------------
+
| 
STRCMP
(
'byte'
, 
'byte'
) | 
CONCAT
(
'three'
, 
' apples'
) |
+
------------------------
+
----------------------------
+
|                      
0
 | 
three
 
apples
               |
+
------------------------
+
----------------------------
+
/<code>

The STRCMP() compares two strings and returns 0 if they are the same. The CONCAT() function concatenates two strings.

STRCMP（）比較兩個字符串，如果相同則返回0。 CONCAT（）函數連接兩個字符串。

<code>mysql> SELECT REPLACE(
'basketball'
, 
'basket'
, 
'foot'
);
+ 
| REPLACE(
'basketball'
, 
'basket'
, 
'foot' 
) |
+ 
| football                                |
+ 
/<code>

The REPLACE() function returns a string, in which we have replaced some text. The first parameter is the original string. The second parameter is a string, we want to replace. And the last parameter is the new replacing string.

REPLACE（）函數返回一個字符串，其中我們替換了一些文本。 第一個參數是原始字符串。 第二個參數是一個字符串，我們要替換。 最後一個參數是新的替換字符串。

Date & time functions

日期和時間函數

In this group we have various date and time functions.

在這個組中，我們具有各種日期和時間函數.

<code>
mysql
> 
SELECT
 
DAYNAME
(
'2011-01-23'
), 
YEAR
(
'2011/01/23'
),
    
-
> 
MONTHNAME
(
'110123'
);
+
-----------------------
+ 
--------------------
+
---------------------
+
| 
DAYNAME
(
'2011-01-23'
) | 
YEAR
(
'2011/01/23'
) | 
MONTHNAME
(
'110123'
) |
+
-----------------------
+
--------------------
+
---------------------
+
| 
Sunday
                |               
2011
 | 
January
             |
+
-----------------------
+
--------------------
+
---------------------
+
/<code>

In MySQL, date is written in the format YYYY-MM-DD. Year is followed by month and day. They can be separated by slash or by hyphen. MySQL also supports a shortened date format, without separators. Time is written in a standard form, HH:MM:SS. Hours followed by minutes and seconds.

在MySQL中，日期格式為YYYY-MM-DD。 年份之後是月份和日期。 它們可以用斜槓或連字符分隔。 MySQL還支持縮短的日期格式，沒有分隔符。 時間以標準格式HH：MM：SS編寫。 小時後是分鐘和秒。

<code>
mysql 
> 
SELECT
 
NOW
();
+
---------------------
+
| 
NOW
()               |
+
---------------------
+
| 
2011-01-22
 
00
:24
:49
 |
+
---------------------
+
/<code>

The NOW() function returns the current date and time.

NOW（）函數返回當前日期和時間。

<code>
mysql
> 
SELECT
 
CURTIME
(), 
CURDATE
();
+
-----------
+
------------
+
| 
CURTIME
() | 
CURDATE
()  |
+
-----------
+
------------
+
|  
00
:25
:03
  | 
2011-01-22
 |
+
-----------
+
------------
+
/<code>

The CURTIME() returns the current time and the CURDATE() returns the current date.

CURTIME（）返回當前時間，而CURDATE（）返回當前日期.

<code>mysql> SELECT DATEDIFF(
'2011-3-12'
, 
'2011-1-12'
);
+ 
| DATEDIFF(
'2011-3-12'
, 
'2011-1-12'
) |
+ 
|                                 
59
 |
+ 
/<code>

With the DATEDIFF() we get the number of days between two dates.

使用DATEDIFF（），我們可以得出兩個日期之間的天數。

<code>
mysql
> 
SELECT
 
DAYNAME
(
'1982-4-12'
), 
MONTHNAME 
(
'1982-4-12'
) ;
+
----------------------
+
------------------------
+
| 
DAYNAME
(
'1982-4-12'
) | 
MONTHNAME
(
'1982-4-12'
) |
+
----------------------
+
------------------------
+
| 
Monday
               | 
April
                  |
+
----------------------
+
------------------------
+
/<code>

The DAYNAME() function returns the day name of a date. The MONTHNAME() function returns a month name of a date.

DAYNAME（）函數返回日期的日期名稱。 MONTHNAME（）函數返回日期的月份名稱。

<code>
mysql
> 
SELECT
 
WEEKOFYEAR
(
'110123'
), 
WEEKDAY
(
'110123'
),
    
- 
> 
QUARTER
(
'110123'
);
+
----------------------
+
-------------------
+
-------------------
+
| 
WEEKOFYEAR
(
'110123'
) | 
WEEKDAY
(
'110123'
) | 
QUARTER
(
'110123'
) |
+
----------------------
+
-------------------
+
-------------------
+
|                    
3
 |                 
6
 |                 
1
 |
+
----------------------
+
-------------------
+
-------------------
+
/<code>

January 23, 2011 can be written in a shortened date format, 110123. We use the WEEKOFYEAR() to find out the week of the year. The WEEKDAY() returns 6, which is Sunday. And the QUARTER() function returns the quarter of the year.

2011年1月23日可以用縮短的日期格式110123編寫。我們使用WEEKOFYEAR（）找出一年中的星期。 WEEKDAY（）返回6，即星期日。 並且QUARTER（）函數返回一年的季度。

<code>mysql> SELECT DATE_FORMAT(
'110123'
, 
'%d-%m-%Y'
);
+ 
| DATE_FORMAT(
'110123'
, 
'%d-%m-%Y'
) |
+ 
| 
23
-01
-2011
                        |
+ 
/<code>

To display date in a different format, we use the DATE_FORMAT().

要以其他格式顯示日期，我們使用DATE_FORMAT（）.

<code>
mysql
> 
SELECT
 
DATE_ADD
(
'110123'
, INTERVAL 
45
 DAY), 
    
-
> 
SUBDATE
(
'110309'
, INTERVAL 
45
 DAY);
+
-------------------------------------
+
------------------------------------
+
| 
DATE_ADD
( 
'110123'
, INTERVAL 
45
 DAY) | 
SUBDATE
(
'110309'
, INTERVAL 
45
 DAY) |
+
-------------------------------------
+
------------------------------------
+
| 
2011-03-09
                          | 
2011-01-23
                         |
+
-------------------------------------
+
------------------------------------
+
/<code>

We can use DATE_ADD() to add time intervals to a date and SUBDATE() to subtract time intervals from a date.

我們可以使用DATE_ADD（）向日期添加時間間隔，並使用SUBDATE（）從日期中減去時間間隔.

System functions

系統函數

System functions provide some system information about MySQL database.

系統函數提供了有關MySQL數據庫的一些系統信息。

<code>
mysql
> 
SELECT 
 
VERSION
(), 
DATABASE
();
+
--------------------
+
------------
+
| 
VERSION
()          | 
DATABASE
() |
+
--------------------
+
------------
+
| 
5
.1
.41-3ubuntu12
.8
 | 
mydb
       |
+
--------------------
+
------------
+
/<code>

We get the version of the MySQL database and the current database name.

我們得到MySQL數據庫的版本和當前的數據庫名稱。

<code>mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
/<code>

The USER() function returns the user name and the host name provided by the client.

USER（）函數返回用戶名和客戶端提供的主機名。

<code>
mysql
> 
SELECT
 
CHARSET
(
'ZetCode'
), 
COLLATION
(
'ZetCode'
);
+
--------------------
+
----------------------
+
| 
CHARSET
(
'ZetCode'
) | 
COLLATION
(
'ZetCode'
) |
+
--------------------
+
----------------------
+
| 
utf8
               | 
utf8_general_ci
      |
+
--------------------
+
----------------------
+
/<code>

The CHARSET() function returns the character set of the argument. The COLLATION() returns the collation of the current string argument. They depend on the charset and collation of the client in use.

In this part of the MySQL tutorial, we worked with the built-in MySQL functions.

CHARSET（）函數返回參數的字符集。 COLLATION（）返回當前字符串參數的排序規則。 它們取決於使用中的客戶端的字符集和排序規則。

在MySQL教程的這一部分中，我們使用了內置的MySQL函數。

關鍵字: 函數 function functions