函数

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)

原著是一个有趣的人,若有侵权,请通知删除