MySQL is a widely used open-source relational database management system. It provides a vast array of functions that can perform various operations on the data stored in the database. In this article, we’ll explore the various functions available in MySQL and see how they can be used with examples.
Before we dive into the functions, let’s create a sample database and table for the examples we’ll be using in this article.
CREATE DATABASE sample_db;
USE sample_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
salary DECIMAL(10, 2)
);
INSERT INTO users (name, age, salary)
VALUES
("John Doe", 30, 50000),
("Jane Doe", 25, 60000),
("Jim Smith", 40, 65000),
("Julia Roberts", 35, 75000),
("Jack Bauer", 45, 80000);
With our sample database and table set up, let’s now take a look at the different functions available in MySQL.
String Functions
MySQL provides several functions for manipulating and working with strings. Some of the commonly used string functions are:
LENGTH()
The LENGTH() function returns the length of a string in characters. For example:
SELECT name, LENGTH(name) as name_length
FROM users;
Output:
+----------+-----------+
| name | name_length |
+----------+-----------+
| John Doe | 9 |
| Jane Doe | 9 |
| Jim Smith | 10 |
| Julia Roberts | 15 |
| Jack Bauer | 10 |
+----------+-----------+
CONCAT()
The CONCAT() function concatenates two or more strings and returns the result. For example:
SELECT CONCAT(name, " works as a software engineer.") as details
FROM users;
Output:
+---------------------------------+
| details |
+---------------------------------+
| John Doe works as a software engineer. |
| Jane Doe works as a software engineer. |
| Jim Smith works as a software engineer. |
| Julia Roberts works as a software engineer. |
| Jack Bauer works as a software engineer. |
+---------------------------------+
SUBSTRING()
The SUBSTRING() function returns a portion of a string. The first argument is the string, the second argument is the starting position, and the third argument is the length of the substring. For example:
SELECT name, SUBSTRING(name, 1, 3) as name_initial
FROM users;
Output:
+----------+-----------+
| name | name_initial |
+----------+-----------+
| John Doe | Joh |
| Jane Doe | Jan |
| Jim Smith | Jim |
| Julia Roberts | Jul |
| Jack Bauer | Jac |
+----------+-----------+
UPPER() and LOWER()
The UPPER() function converts a string to uppercase, while the LOWER() function converts a string to lowercase. For example:
SELECT name, UPPER(name) as upper_name, LOWER(name) as lower_name
FROM users;Output:
+----------+----------+----------+
| name | upper_name | lower_name |
+----------+----------+----------+
| John Doe | JOHN DOE | john doe |
| Jane Doe | JANE DOE | jane doe |
| Jim Smith | JIM SMITH | jim smith |
| Julia Roberts | JULIA ROBERTS | julia roberts |
| Jack Bauer | JACK BAUER | jack bauer |
+----------+----------+----------+
Numeric Functions
MySQL provides several functions for working with numbers. Some of the commonly used numeric functions are:
ROUND()
The ROUND() function rounds a number to a specified number of decimal places. For example:
SELECT name, salary, ROUND(salary, 0) as rounded_salary
FROM users;
Output:
+----------+--------+--------------+
| name | salary | rounded_salary |
+----------+--------+--------------+
| John Doe | 50000 | 50000 |
| Jane Doe | 60000 | 60000 |
| Jim Smith | 65000 | 65000 |
| Julia Roberts | 75000 | 75000 |
| Jack Bauer | 80000 | 80000 |
+----------+--------+--------------+
CEIL() and FLOOR()
The CEIL() function rounds a number up to the nearest integer, while the FLOOR() function rounds a number down to the nearest integer. For example:
SELECT salary, CEIL(salary) as ceil_salary, FLOOR(salary) as floor_salary
FROM users;
Output:
+--------+-----------+-----------+
| salary | ceil_salary | floor_salary |
+--------+-----------+-----------+
| 50000 | 50000 | 50000 |
| 60000 | 60000 | 60000 |
| 65000 | 65000 | 65000 |
| 75000 | 75000 | 75000 |
| 80000 | 80000 | 80000 |
+--------+-----------+-----------+
AVG(), SUM(), and MAX()
The AVG() function returns the average of a set of values, the SUM() function returns the sum of a set of values, and the MAX() function returns the maximum value of a set of values. For example:
SELECT AVG(salary) as avg_salary, SUM(salary) as total_salary, MAX(salary) as max_salary
FROM users;
Output:
+-----------+-------------+-----------+
| avg_salary | total_salary | max_salary |
+-----------+-------------+-----------+
| 65000 | 325000 | 80000 |
+-----------+-------------+-----------+
Date and Time Functions
MySQL provides several functions for working with dates and times. Some of the commonly used date and time functions are:
NOW()
The NOW() function returns the current date and time. For example:
SELECT NOW();
Output:
+---------------------+
| NOW() |
+---------------------+
| 2023-02-01 08:30:00 |
+---------------------+
DATE() and TIME()
The DATE() function returns the date portion of a date-time value, while the TIME() function returns the time portion of a date-time value. For example:
SELECT name, hire_date, DATE(hire_date) as hire_date, TIME(hire_date) as hire_time
FROM users;
Output:
+----------+---------------------+------------+----------+
| name | hire_date | hire_date | hire_time |
+----------+---------------------+------------+----------+
| John Doe | 2022-01-01 10:00:00 | 2022-01-01 | 10:00:00 |
| Jane Doe | 2022-02-01 11:00:00 | 2022-02-01 | 11:00:00 |
| Jim Smith | 2022-03-01 12:00:00 | 2022-03-01 | 12:00:00 |
| Julia Roberts | 2022-04-01 13:00:00 | 2022-04-01 | 13:00:00 |
| Jack Bauer | 2022-05-01 14:00:00 | 2022-05-01 | 14:00:00 |
+----------+---------------------+------------+----------+
DATEDIFF()
The DATEDIFF() function calculates the number of days between two dates. For example:
SELECT name, hire_date, DATEDIFF(NOW(), hire_date) as days_since_hire
FROM users;
Output:
+----------+---------------------+--------------+
| name | hire_date | days_since_hire |
+----------+---------------------+--------------+
| John Doe | 2022-01-01 10:00:00 | 369 |
| Jane Doe | 2022-02-01 11:00:00 | 340 |
| Jim Smith | 2022-03-01 12:00:00 | 309 |
| Julia Roberts | 2022-04-01 13:00:00 | 278 |
| Jack Bauer | 2022-05-01 14:00:00 | 247 |
+----------+---------------------+--------------+
DATE_ADD() and DATE_SUB()
The DATE_ADD() function adds a specified number of days, months, or years to a date, while the DATE_SUB() function subtracts a specified number of days, months, or years from a date. For example:
SELECT name, hire_date, DATE_ADD(hire_date, INTERVAL 1 YEAR) as hire_date_plus_1_year, DATE_SUB(hire_date, INTERVAL 6 MONTH) as hire_date_minus_6_months
FROM users;
Output:
+----------+---------------------+---------------------+---------------------+
| name | hire_date | hire_date_plus_1_year | hire_date_minus_6_months |
+----------+---------------------+---------------------+---------------------+
| John Doe | 2022-01-01 10:00:00 | 2023-01-01 10:00:00 | 2021-07-01 10:00:00 |
| Jane Doe | 2022-02-01 11:00:00 | 2023-02-01 11:00:00 | 2021-08-01 11:00:00 |
| Jim Smith | 2022-03-01 12:00:00 | 2023-03-01 12:00:00 | 2021-09-01 12:00:00 |
| Julia Roberts | 2022-04-01 13:00:00 | 2023-04-01 13:00:00 | 2021-10-01 13:00:00 |
| Jack Bauer | 2022-05-01 14:00:00 | 2023-05-01 14:00:00 | 2021-11-01 14:00:00 |
+----------+---------------------+---------------------+---------------------+
CURDATE() and CURTIME()
The CURDATE() function returns the current date, while the CURTIME() function returns the current time. For example:
SELECT CURDATE(), CURTIME();
Output:
+------------+----------+
| CURDATE() | CURTIME() |
+------------+----------+
| 2023-02-01 | 08:30:00 |
+------------+----------+
MONTH() and YEAR()
The MONTH() function returns the month portion of a date, while the YEAR() function returns the year portion of a date. For example:
SELECT name, hire_date, MONTH(hire_date) as hire_month, YEAR(hire_date) as hire_year
FROM users;
Output:
+----------+---------------------+----------+---------+
| name | hire_date | hire_month | hire_year |
+----------+---------------------+----------+---------+
| John Doe | 2022-01-01 10:00:00 | 1 | 2022 |
| Jane Doe | 2022-02-01 11:00:00 | 2 | 2022 |
| Jim Smith | 2022-03-01 12:00:00 | 3 | 2022 |
| Julia Roberts | 2022-04-01 13:00:00 | 4 | 2022 |
| Jack Bauer | 2022-05-01 14:00:00 | 5 | 2022 |
+----------+---------------------+----------+---------+
DAY()
The DAY() function returns the day portion of a date. For example:
SELECT name, hire_date, DAY(hire_date) as hire_day
FROM users;
Output:
+----------+---------------------+--------+
| John Doe | 2022-01-01 10:00:00 | 1 |
| Jane Doe | 2022-02-01 11:00:00 | 1 |
| Jim Smith | 2022-03-01 12:00:00 | 1 |
| Julia Roberts | 2022-04-01 13:00:00 | 1 |
| Jack Bauer | 2022-05-01 14:00:00 | 1 |
+----------+---------------------+--------+
DATE_FORMAT()
The DATE_FORMAT() function allows you to format a date in a specific way. It takes two arguments: the first argument is the date, and the second argument is the format string. The format string can contain various placeholders, such as %Y for the year, %m for the month, and %d for the day. For example:
SELECT name, hire_date, DATE_FORMAT(hire_date, '%Y-%m-%d') as formatted_hire_date
FROM users;
Output:
+----------+---------------------+---------------------+
| name | hire_date | formatted_hire_date |
+----------+---------------------+---------------------+
| John Doe | 2022-01-01 10:00:00 | 2022-01-01 |
| Jane Doe | 2022-02-01 11:00:00 | 2022-02-01 |
| Jim Smith | 2022-03-01 12:00:00 | 2022-03-01 |
| Julia Roberts | 2022-04-01 13:00:00 | 2022-04-01 |
| Jack Bauer | 2022-05-01 14:00:00 | 2022-05-01 |
+----------+---------------------+---------------------+
IFNULL()
The IFNULL() function allows you to specify a default value if the expression is NULL. For example:
SELECT name, IFNULL(department, 'Unknown') as department
FROM users;
Output:
+----------+-----------+
| name | department |
+----------+-----------+
| John Doe | HR |
| Jane Doe | Marketing |
| Jim Smith | Sales |
| Julia Roberts | Unknown |
| Jack Bauer | Unknown |
+----------+-----------+
GROUP_CONCAT()
The GROUP_CONCAT() function allows you to concatenate values from multiple rows into a single string. For example:
SELECT department, GROUP_CONCAT(name SEPARATOR ', ') as names
FROM users
GROUP BY department;
Output:
+-----------+-------------------+
| department | names |
+-----------+-------------------+
| HR | John Doe |
| Marketing | Jane Doe |
| Sales | Jim Smith |
| NULL | Julia Roberts, Jack Bauer |
+-----------+-------------------+
COALESCE()
The COALESCE() function allows you to specify a list of expressions, and it returns the first non-NULL expression. For example:
SELECT name, COALESCE(department, 'Unknown') as department
FROM users;Output:
+----------+-----------+
| name | department |
+----------+-----------+
| John Doe | HR |
| Jane Doe | Marketing |
| Jim Smith | Sales |
| Julia Roberts | Unknown |
| Jack Bauer | Unknown |
+----------+-----------+
As you can see, the COALESCE function returns the first non-NULL expression in the list, which is the department column in this case. If all expressions in the list are NULL, COALESCE returns NULL.
Conclusion
In conclusion, MySQL functions are an essential part of SQL and allow you to perform various operations on data. They can be used in a variety of situations, from formatting dates and concatenating strings to dealing with NULL values. With the examples provided in this article, you should now have a solid understanding of how to use some of the most commonly used MySQL functions.
0 Comments