sql教程7函数

函数

mysql> SELECT movie_id,title, UCASE(title)  FROM movies;
+----------+---------------------------+---------------------------+
| movie_id | title                     | UCASE(title)              |
+----------+---------------------------+---------------------------+
|       16 | 67% Guilty                | 67% GUILTY                |
|        6 | Angels and Demons         | ANGELS AND DEMONS         |
|        4 | Code Name Black           | CODE NAME BLACK           |
|        5 | Daddy's Little Girls      | DADDY'S LITTLE GIRLS      |
|        7 | Davinci Code              | DAVINCI CODE              |
|        2 | Forgetting Sarah Marshal  | FORGETTING SARAH MARSHAL  |
|        9 | Honey mooners             | HONEY MOONERS             |
|       19 | movie 3                   | MOVIE 3                   |
|       22 | movie 3                   | MOVIE 3                   |
|       23 | movie 3                   | MOVIE 3                   |
|        1 | Pirates of the Caribean 4 | PIRATES OF THE CARIBEAN 4 |
|       17 | The Great Dictator        | THE GREAT DICTATOR        |
|        3 | X-Men                     | X-MEN                     |
+----------+---------------------------+---------------------------+
13 rows in set (0.03 sec)

mysql> SELECT 23 DIV 6 ;
+----------+
| 23 DIV 6 |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT 23 / 6 ;
+--------+
| 23 / 6 |
+--------+
| 3.8333 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 23 - 6 ;
+--------+
| 23 - 6 |
+--------+
|     17 |
+--------+
1 row in set (0.01 sec)

mysql> SELECT 23 + 6 ;
+--------+
| 23 + 6 |
+--------+
|     29 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 23 * 6 AS multiplication_result;
+-----------------------+
| multiplication_result |
+-----------------------+
|                   138 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT 23 % 6 ;
+--------+
| 23 % 6 |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 23 MOD 6 ;
+----------+
| 23 MOD 6 |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(23 / 6) AS floor_result;
+--------------+
| floor_result |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(23 / 6) AS round_result;
+--------------+
| round_result |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT RAND() AS random_result;
+---------------------+
| random_result       |
+---------------------+
| 0.22114622799786388 |
+---------------------+
1 row in set (0.00 sec)

存储函数

• 语法

CREATE FUNCTION sf_name ([parameter(s)])
   RETURNS data type
   DETERMINISTIC
   STATEMENTS

• 实例

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
  RETURNS VARCHAR(3)
   DETERMINISTIC
    BEGIN
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF  curdate() <= return_date
            THEN SET sf_value = 'No';
        END IF;
     RETURN sf_value;
    END|

汇聚函数

COUNT, SUM, AVG, MIN and MAX.

mysql> SELECT COUNT(movie_id)  FROM movierentals WHERE movie_id = 2;
+-----------------+
| COUNT(movie_id) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT movie_id FROM movierentals;
+----------+
| movie_id |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
3 rows in set (0.00 sec)

mysql> SELECT MIN(year_released) FROM movies;
+--------------------+
| MIN(year_released) |
+--------------------+
|               1920 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(year_released)  FROM movies;
+--------------------+
| MAX(year_released) |
+--------------------+
|               2012 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(amount_paid) FROM payments;
+------------------+
| SUM(amount_paid) |
+------------------+
|            10500 |
+------------------+
1 row in set (0.00 sec)

存储函数

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
  RETURNS VARCHAR(3)
   DETERMINISTIC
    BEGIN
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF  curdate() <= return_date
            THEN SET sf_value = 'No';
        END IF;
     RETURN sf_value;
    END|

mysql> SELECT movie_id,membership_number,return_date,CURDATE() ,sf_date(return_date)  FROM movierentals;
+----------+-------------------+-------------+------------+----------------------+
| movie_id | membership_number | return_date | CURDATE()  | sf_date(return_date) |
+----------+-------------------+-------------+------------+----------------------+
|        1 |                 1 | NULL        | 2020-03-23 | NULL                 |
|        2 |                 1 | 2012-06-25  | 2020-03-23 | Yes                  |
|        2 |                 3 | 2012-06-25  | 2020-03-23 | Yes                  |
|        2 |                 2 | 2012-06-24  | 2020-03-23 | Yes                  |
|        3 |                 3 | NULL        | 2020-03-23 | NULL                 |
+----------+-------------------+-------------+------------+----------------------+
5 rows in set (0.00 sec)