MYSQL Functions: All You Need to Know

Types of functions

  • Numeric functions — These are functions that operate on numeric data types
  • Strings functions — These are functions that operate on string data types
  • Aggregate functions — These are functions that operate on all different data types and produce summarized result sets.
  • Date functions — They are functions that operate on date data types.

Numeric functions

Strings Functions

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 mysql> SELECT CONCAT('My', 'S', 'QL');
2 +---------------------------------------------------------+
3 | CONCAT('My', 'S', 'QL') |
4 +---------------------------------------------------------+
5 | MySQL |
6 +---------------------------------------------------------+
7 1 row in set (0.00 sec)
1 mysql> SELECT CONCAT('My', 'S', 'QL'); 2 +---------------------------------------------------------+ 3 | CONCAT('My', 'S', 'QL') | 4 +---------------------------------------------------------+ 5 | MySQL | 6 +---------------------------------------------------------+ 7 1 row in set (0.00 sec)
1 mysql> SELECT CONCAT('My', 'S', 'QL');
2 +---------------------------------------------------------+
3 | CONCAT('My', 'S', 'QL') |
4 +---------------------------------------------------------+
5 | MySQL |
6 +---------------------------------------------------------+
7 1 row in set (0.00 sec)

 

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 mysql> SELECT CHAR_LENGTH("text");
2 +---------------------------------------------------------+
3 | CHAR_LENGTH("text") |
4 +---------------------------------------------------------+
5 | 4 |
6 +---------------------------------------------------------+
7 1 row in set (0.00 sec)
1 mysql> SELECT CHAR_LENGTH("text"); 2 +---------------------------------------------------------+ 3 | CHAR_LENGTH("text") | 4 +---------------------------------------------------------+ 5 | 4 | 6 +---------------------------------------------------------+ 7 1 row in set (0.00 sec)
1 mysql> SELECT CHAR_LENGTH("text");
2 +---------------------------------------------------------+
3 | CHAR_LENGTH("text") |
4 +---------------------------------------------------------+
5 | 4 |
6 +---------------------------------------------------------+
7 1 row in set (0.00 sec)

 

Aggregate functions

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 +-----------+----------------------------+--------------+-----+
2 | groc_code | groc_name | groc_price
3 +-----------+----------------------------+--------------+-----+
4 | 1 |Little Steps Growing up Milk| 7.75
5 | 2 | Sacla Basil Pesto 190g | 2.50
6 | 3 | Golden Caster Sugar 500g | 1.30
7 | 4 | Higgidy Spinach & Pine Nut | 4.00
8 | 5 | Heinz Salad Cream 605g | 2.50
1 +-----------+----------------------------+--------------+-----+ 2 | groc_code | groc_name | groc_price 3 +-----------+----------------------------+--------------+-----+ 4 | 1 |Little Steps Growing up Milk| 7.75 5 | 2 | Sacla Basil Pesto 190g | 2.50 6 | 3 | Golden Caster Sugar 500g | 1.30 7 | 4 | Higgidy Spinach & Pine Nut | 4.00 8 | 5 | Heinz Salad Cream 605g | 2.50
1 +-----------+----------------------------+--------------+-----+
2 | groc_code | groc_name | groc_price
3 +-----------+----------------------------+--------------+-----+
4 | 1 |Little Steps Growing up Milk| 7.75
5 | 2 | Sacla Basil Pesto 190g | 2.50
6 | 3 | Golden Caster Sugar 500g | 1.30
7 | 4 | Higgidy Spinach & Pine Nut | 4.00
8 | 5 | Heinz Salad Cream 605g | 2.50

 

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 mysql> SELECT AVG(groc_price) AS price_ag FROM groceries;
2 +----------+
3 | price_ag |
4 +----------+
5 | 3.61 |
6 +----------+
7 1 row in set (0.00 sec)
1 mysql> SELECT AVG(groc_price) AS price_ag FROM groceries; 2 +----------+ 3 | price_ag | 4 +----------+ 5 | 3.61 | 6 +----------+ 7 1 row in set (0.00 sec)
1 mysql> SELECT AVG(groc_price) AS price_ag FROM groceries;
2 +----------+
3 | price_ag |
4 +----------+
5 | 3.61 |
6 +----------+
7 1 row in set (0.00 sec)

 

Date functions

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 mysql> SELECT CURDATE();
2 -> '2022-04-21'
3 mysql> SELECT CURDATE() + 0;
4 -> 20220421
1 mysql> SELECT CURDATE(); 2 -> '2022-04-21' 3 mysql> SELECT CURDATE() + 0; 4 -> 20220421
1 mysql> SELECT CURDATE(); 
2        -> '2022-04-21' 
3 mysql> SELECT CURDATE() + 0; 
4        -> 20220421

 

DATE_ADD

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
1 mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
2 -> '2008-02-02'
3 mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
4 -> '2008-02-02'
1 mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); 2 -> '2008-02-02' 3 mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY); 4 -> '2008-02-02'
1 mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); 
2        -> '2008-02-02' 
3 mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY); 
4        -> '2008-02-02'