How to Use Standard MySQL Library Functions on Alibaba Cloud ECS and ApsaraDB

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

MySQL Relational Database Management System (RDBMS) is widely used by developers because of its advanced features. These include dozens of built-in functions and transactions support. MySQL is also scalable, flexible and secure to ensure round-the-clock uptime.

The RDBMS standard library functions are specific set of routines that perform a specific task and return result very quickly. Hence, they eliminate the need to write lots of codes for handling database manipulation.

MySQL functions handle complex tasks including mathematical computations, string formats, date/time operations and I/O processing. The standard routines make SQL code easier to maintain, understand and debug while simplifying the coding of large database projects.

In this guide, we will walk you over the steps of implementing MySQL functions on Alibaba Cloud ApsaraDB for RDS or MySQL Databases hosted on the Elastic Compute Service (ECS) instances.

We have selected the most useful functions which are easier to adopt on your applications. To follow along with the guide, you will need to Sign up with Alibaba Cloud and provision a MySQL database either on ApsaraDB or on the ECS instance.

Prerequisites

A valid Alibaba Cloud account. Signup now and get up to $1200 free trial credit to test over 40 Alibaba Cloud products including ApsaraDB and MySQL on ECS instances.
A MySQL database.
Login details for your MySQL database. These include: hostname/IP address, username and password.

Step 1: Login to your Alibaba Cloud MySQL Database

There are a number of different ways to access your Alibaba Cloud MySQL database. In this guide, we will use a command line interface.

Run the command below to login to the MySQL server.

$ mysql -uroot -p -h198.18.0.6

Replace root and 198.18.0.6 with the correct username and IP address associated with your MySQL server.

Step 2: Creating the Test Database

To test MySQL functions, we will run the commands on a real database to help you understand better and apply the knowledge in a production environment.

So create a database named test_db on your server using the syntax below:

mysql>Create database test_db CHARACTER SET utf8 COLLATE utf8_general_ci;

Then, switch to the database:

mysql>use test_db;

Next, create a sample customers table:

mysql> create table customers ( 
customer_id BIGINT PRIMARY KEY,
registration_date DATE NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
balance DOUBLE NOT NULL
) Engine = InnoDB;

Use the describe statement to preview the table structure as shown below:

mysql> describe customers;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| customer_id       | bigint(20)  | NO   | PRI | NULL    |       |
| registration_date | date        | NO   |     | NULL    |       |
| first_name        | varchar(50) | NO   |     | NULL    |       |
| last_name         | varchar(50) | NO   |     | NULL    |       |
| balance           | double      | NO   |     | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

Populating the customers Table with Test Data

Run the commands below to populate the table with some test data:

mysql> INSERT INTO customers (customer_id, registration_date, first_name, last_name, balance) VALUES ('1', '2018-01-07','JOHN', 'DOE','263.89');
INSERT INTO customers (customer_id, registration_date, first_name, last_name, balance) VALUES ('2', '2018-04-30','RICHARD', 'ROE','1887.42');
INSERT INTO customers (customer_id, registration_date, first_name, last_name, balance) VALUES ('3', '2018-06-14','JANE', 'SMITH','89500.89');
INSERT INTO customers (customer_id, registration_date, first_name, last_name, balance) VALUES ('4', '2018-08-24','ELZA', 'MARY','654.79');

Confirm the presence of data by running a select query on the table:

mysql> select * from customers;
+-------------+-------------------+------------+-----------+----------+
| customer_id | registration_date | first_name | last_name | balance  |
+-------------+-------------------+------------+-----------+----------+
|           1 | 2018-01-07        | JOHN       | DOE       |   263.89 |
|           2 | 2018-04-30        | RICHARD    | ROE       |  1887.42 |
|           3 | 2018-06-14        | JANE       | SMITH     | 89500.89 |
|           4 | 2018-08-24        | ELZA       | MARY      |   654.79 |
+-------------+-------------------+------------+-----------+----------+
4 rows in set (0.00 sec)

Step 3: Working with MySQL String Functions on Alibaba Cloud

CONCAT

This function combines two strings into one single continuous string. The CONCAT function is very useful when working with names. For instance, if you would like to return the Id and full name of customers from our sample database, you would use the below SQL syntax:

mysql> select customer_id, CONCAT(first_name, ' ',  last_name) as full_name from customers;
+-------------+-------------+
| customer_id | full_name   |
+-------------+-------------+
|           1 | JOHN DOE    |
|           2 | RICHARD ROE |
|           3 | JANE SMITH  |
|           4 | ELZA MARY   |
+-------------+-------------+
4 rows in set (0.00 sec)

FORMAT

The FORMAT function returns a number formatted to a string and rounded to specified decimal places.

FORMAT (number or column, decimal places)

For instance, in our customers' database, we can retrieve the balances formatted with 2 decimal places and thousands separator for readability purposes as shown below:

mysql> Select first_name, last_name, FORMAT(balance , 2) as Balance from customers;
+------------+-----------+-----------+
| first_name | last_name | Balance   |
+------------+-----------+-----------+
| JOHN       | DOE       | 263.89    |
| RICHARD    | ROE       | 1,887.42  |
| JANE       | SMITH     | 89,500.89 |
| ELZA       | MARY      | 654.79    |
+------------+-----------+-----------+
4 rows in set (0.00 sec)

LCASE

To convert a string to lowercase, use the LCASE function. Let's run the command below to return the first names of customers in lower case;

mysql> select LCASE(first_name) as lowercase_names  from customers;
+-----------------+
| lowercase_names |
+-----------------+
| john            |
| richard         |
| jane            |
| elza            |
+-----------------+
4 rows in set (0.00 sec)

LEFT

This functions returns a specified number of characters from a string/column starting from the left side.

Syntax:

LEFT(string, chars)

We can use the LEFT function to return the initials of the customers first names as shown below:

mysql> select LEFT(first_name, 1) as first_name, last_name from customers;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| J          | DOE       |
| R          | ROE       |
| J          | SMITH     |
| E          | MARY      |
+------------+-----------+
4 rows in set (0.00 sec)

RIGHT

The RIGHT function extracts some specified number of characters in a string from the right side;

RIGHT (string, chars)

We can run this function on the registration_date column to extract only the date portion:

mysql> select RIGHT(registration_date,2 ) as date_of_the_month from customers;
+-------------------+
| date_of_the_month |
+-------------------+
| 07                |
| 30                |
| 14                |
| 24                |
+-------------------+
4 rows in set (0.00 sec)

LENGTH

Use the LENGTH function to determine the number of characters in a string. For instance, if we want to know the total count of characters in the first_name field, we can run the query below;

mysql> Select first_name, LENGTH(first_name) as first_name_length from customers ;
+------------+-------------------+
| first_name | first_name_length |
+------------+-------------------+
| JOHN       |                 4 |
| RICHARD    |                 7 |
| JANE       |                 4 |
| ELZA       |                 4 |
+------------+-------------------+
4 rows in set (0.00 sec)

SUBSTR

MySQL offers more flexibility when extracting text from a string. Apart from just specifying the location and length of the string, you can specify where the functions should start reading the string from using the SUBSTR function.

The syntax is shown below:

SUBSTR (string, start, length)

For instance, let's assume the payroll numbers of employees in an organisation start with the department code, then hyphen and then a unique id as shown below:

1468-12362252
1470-15555588

With a string like this, you can use the SUBSTRING function to retrieve the id part(rightmost part) of the string as shown below. We will start reading from character number 6 and we will display all the 8 digits from the id part as shown below:

mysql> SELECT SUBSTRING("1468-12362252", 6, 8) AS employee_id;
+-------------+
| employee_id |
+-------------+
| 12362252    |
+-------------+
1 row in set (0.00 sec)

TRIM

Use the TRIM function to remove spaces from the left and right side of a string.

For instance, if a staff member erroneously entered a customer_name as ' JOHN ' you can use the trim function to remove the leading and trailing spaces:

mysql> Select TRIM(' JOHN   ') as first_name;
+------------+
| first_name |
+------------+
| JOHN       |
+------------+
1 row in set (0.00 sec)

UCASE

This function converts characters to uppercase.

Example

mysql> select UPPER('4 gb memory card with key holder') as product_name;
+---------------------------------+
| product_name                    |
+---------------------------------+
| 4 GB MEMORY CARD WITH KEY HOLDER |
+---------------------------------+
1 row in set (0.00 sec)

Step 4: Implementing MySQL Numeric Functions on Alibaba Cloud

ABS

This function returns the absolute value of a number. This means the distance of the number from zero without considering whether it is a positive or negative number.

For instance ABS('-200') and ABS('200') will just display the same result:

mysql> select ABS('-200') as absolute_value;
+----------------+
| absolute_value |
+----------------+
|            200 |
+----------------+
1 row in set (0.01 sec)

AVG

This is an aggregate function that allows you to calculate the average of a given expression or column.For instance, to calculate the average balance from the customers table, run the command below;

mysql> select AVG(balance) as average_balance from customers;
+--------------------+
| average_balance    |
+--------------------+
| 23076.747499999998 |
+--------------------+
1 row in set (0.00 sec)

CEIL

This function is used to return the next smallest integer value that is equal or greater to a number. For instance, if the currency we use doesn't support decimals when making payments, we can retrieve the customers balance using the CEIL function to determine how much they should pay us as shown below:

mysql> select first_name, last_name, CEIL(balance) as real_balance from customers;
+------------+-----------+--------------+
| first_name | last_name | real_balance |
+------------+-----------+--------------+
| JOHN       | DOE       |          264 |
| RICHARD    | ROE       |         1888 |
| JANE       | SMITH     |        89501 |
| ELZA       | MARY      |          655 |
+------------+-----------+--------------+
4 rows in set (0.01 sec)

MAX

Use the MAX function to determine the highest value in a column. For instance, to know the customers with the greatest debt, we can run the command below:

mysql> select MAX(balance) as highest_balance from customers;
+-----------------+
| highest_balance |
+-----------------+
|        89500.89 |
+-----------------+
1 row in set (0.00 sec)

So, Jane Smith is the debtor with the highest amount to pay;

MIN

The MIN function displays the smallest value in an expression. We can run this function against our customers table to find the debtor who owes us the least amount:

mysql> select MIN(balance) as lowest_balance from customers;
+----------------+
| lowest_balance |
+----------------+
|         263.89 |
+----------------+
1 row in set (0.00 sec)

SUM

Use the SUM function to determine the summed up value of an expression. For instance to find the total amount that all customers owe to our business, use the command below:

mysql> select SUM(balance) as total_debt from customers;
+-------------------+
| total_debt        |
+-------------------+
| 92306.98999999999 |
+-------------------+
1 row in set (0.00 sec)

COUNT

If you wish to calculate the number of rows returned by a MySQL statement, use the COUNT function. This function can be very useful if you have a large data set (e.g. for customers) and you want to know the total count to make a business decision.

To count the total number in our customers' database, we can use the command below:

mysql> select count(*) as total_customers from customers;
+-----------------+
| total_customers |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

Step 5: Working with MySQL DATE Functions on Alibaba Cloud

MySQL supports a wide range of DATE functions. We will discuss some of them on this section and show you how you can apply them on your database.

DATE

Use the DATE command to extract a date value from a string or a column. For instance given the string '2018-05-11 12:20:58', the DATE function can retrieve the date.

mysql> Select DATE('2018-05-11 12:20:58') as date_part;
+------------+
| date_part  |
+------------+
| 2018-05-11 |
+------------+
1 row in set (0.00 sec)

CURRENT_DATE

Sometimes, you might want an SQL script to insert the current date from the server instead of inputting the value manually. The CURRENT_DATE function can be used for this purpose because it will be more accurate.

For instance, to insert a new customer with the current date as the registration date, we can run the command below:

mysql> INSERT INTO customers (customer_id, registration_date, first_name, last_name, balance) VALUES ('5', CURRENT_DATE(),'BABY', 'ROE','2758.79');
Query OK, 1 row affected (0.01 sec)

Then, let's select the customer to confirm:

mysql> select * from customers where customer_id='5';
+-------------+-------------------+------------+-----------+---------+
| customer_id | registration_date | first_name | last_name | balance |
+-------------+-------------------+------------+-----------+---------+
|           5 | 2018-08-25        | BABY       | ROE       | 2758.79 |
+-------------+-------------------+------------+-----------+---------+
1 row in set (0.01 sec)

CURRENT_TIME

You can determine the current time from the MySQL server by using the CURRENT_TIME function. You can use function to save the current time in a database if your table requires a time column.

To return the current time from the server, use the SQL command below:

mysql> select CURRENT_TIME()as time_now ;
+----------+
| time_now |
+----------+
| 12:36:19 |
+----------+
1 row in set (0.00 sec)

DATEDIFF

This function returns an interval expressed in number of days between two dates. The general syntax is shown below:

DATEDIFF (date1, date2)

Let us run this command to determine the number of days a customer has been active in our company by calculating the number of days between the registration_date and today's date..

mysql> Select first_name, last_name, DATEDIFF(CURRENT_DATE() ,registration_date) as active_days from customers;
+------------+-----------+-------------+
| first_name | last_name | active_days |
+------------+-----------+-------------+
| JOHN       | DOE       |         230 |
| RICHARD    | ROE       |         117 |
| JANE       | SMITH     |          72 |
| ELZA       | MARY      |           1 |
| BABY       | ROE       |           0 |
+------------+-----------+-------------+
5 rows in set (0.00 sec)

DATE_FORMAT

You can display date in a more readable format by using the DATE_FORMAT function which takes a date string and a mask as the input.

DATE_FORMAT (date_string, mask)

Here are some general DATE_FORMAT values:

%a : Abbreviated day of the week e.g. SUN.
%d : Day of the month represented in a numeric value from 01 to 31
%M :Returns the month name in full eg. AUGUST
%b : Represents the month name in abbreviated format e.g. DEC
%m : Month represented as numeric value from 01 to 12
%Y : Year in a 4 digit numeric value
%y : Year in a 2 digit numeric value
%H: Hour in 24 hrs format from 00 to 24
%h: Hour in 12 hrs format from 00 to 12
%i: Minutes from 00 to 59
%s: Seconds from 01 to 59
%p: When used in a 12 hours time format, it shows whether the input time is AM or PM

For instance, let's retrieve the customers registration dates in a more readable format using the DATE_FORMAT function:

mysql> Select DATE_FORMAT(registration_date, "%d/%b/%Y %H:%i:%s") as registration_date from customers;
+----------------------+
| registration_date    |
+----------------------+
| 07/Jan/2018 00:00:00 |
| 30/Apr/2018 00:00:00 |
| 14/Jun/2018 00:00:00 |
| 24/Aug/2018 00:00:00 |
| 25/Aug/2018 00:00:00 |
+----------------------+
5 rows in set (0.00 sec)

DAY

The DAY function returns the day of the month from the given date. For instance, let's run the command below on the customers table:

mysql> select first_name, last_name, registration_date, DAY(registration_date) as day_of_the_month from customers;
+------------+-----------+-------------------+------------------+
| first_name | last_name | registration_date | day_of_the_month |
+------------+-----------+-------------------+------------------+
| JOHN       | DOE       | 2018-01-07        |                7 |
| RICHARD    | ROE       | 2018-04-30        |               30 |
| JANE       | SMITH     | 2018-06-14        |               14 |
| ELZA       | MARY      | 2018-08-24        |               24 |
| BABY       | ROE       | 2018-08-25        |               25 |
+------------+-----------+-------------------+------------------+
5 rows in set (0.00 sec)

DAYNAME

The DAYNAME function returns the weekday name from a date. We can run this on the customers table:

mysql> select first_name, last_name, registration_date, DAYNAME (registration_date) as day_of_the_month from customers;
+------------+-----------+-------------------+------------------+
| first_name | last_name | registration_date | day_of_the_month |
+------------+-----------+-------------------+------------------+
| JOHN       | DOE       | 2018-01-07        | Sunday           |
| RICHARD    | ROE       | 2018-04-30        | Monday           |
| JANE       | SMITH     | 2018-06-14        | Thursday         |
| ELZA       | MARY      | 2018-08-24        | Friday           |
| BABY       | ROE       | 2018-08-25        | Saturday         |
+------------+-----------+-------------------+------------------+
5 rows in set (0.00 sec)

MONTH

To determine an integer value representing the month from 01 to 12, use the MONTH function.

Example:

mysql> select first_name, last_name, registration_date, MONTH(registration_date) as month_of_the_year from customers;
+------------+-----------+-------------------+-------------------+
| first_name | last_name | registration_date | month_of_the_year |
+------------+-----------+-------------------+-------------------+
| JOHN       | DOE       | 2018-01-07        |                 1 |
| RICHARD    | ROE       | 2018-04-30        |                 4 |
| JANE       | SMITH     | 2018-06-14        |                 6 |
| ELZA       | MARY      | 2018-08-24        |                 8 |
| BABY       | ROE       | 2018-08-25        |                 8 |
+------------+-----------+-------------------+-------------------+
5 rows in set (0.00 sec)

NOW

The NOW() function display the current date and time from the MySQL server:

Select NOW();
mysql> Select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-08-25 12:59:59 |
+---------------------+
1 row in set (0.00 sec)

YEAR

To return a year for a given date, use the YEAR () function. For instance, to return the year when the customers registered in our database, we can run the command below:

mysql> select first_name, last_name, registration_date, YEAR(registration_date) as year_of_registration from customers;
+------------+-----------+-------------------+----------------------+
| first_name | last_name | registration_date | year_of_registration |
+------------+-----------+-------------------+----------------------+
| JOHN       | DOE       | 2018-01-07        |                 2018 |
| RICHARD    | ROE       | 2018-04-30        |                 2018 |
| JANE       | SMITH     | 2018-06-14        |                 2018 |
| ELZA       | MARY      | 2018-08-24        |                 2018 |
| BABY       | ROE       | 2018-08-25        |                 2018 |
+------------+-----------+-------------------+----------------------+
5 rows in set (0.01 sec)

Step 6: Using MySQL Advanced Functions on Alibaba Cloud

Apart from string, numeric and date functions there are some other useful advanced MySQL command that you can apply on your database. These include:

CASE

This function is very useful when you want to evaluate whether a condition is met from your SQL command. For instance, we can use the CASE statement to classify our customers depending on the dates they registered.

The general syntax of the CASE function looks like this:

mysql>CASE
WHEN condition1 THEN expression1
WHEN condition2 THEN expression2
ELSE expression3
END

The below CASE statement evaluates the membership of our customers depending on the number of days they have been active in our system:

mysql>Select first_name, last_name,
DATEDIFF(CURRENT_DATE() ,registration_date) as active_days,
(
CASE
WHEN (DATEDIFF(CURRENT_DATE() ,registration_date))=0 THEN 'GUEST'
WHEN (DATEDIFF(CURRENT_DATE() ,registration_date))<=50 THEN 'INTERMEDIATE CUSTOMER'
ELSE 'PREMIUM CUSTOMER'
END
) as membership
from customers;

Output:

+------------+-----------+-------------+-----------------------+
| first_name | last_name | active_days | membership            |
+------------+-----------+-------------+-----------------------+
| JOHN       | DOE       |         230 | PREMIUM CUSTOMER      |
| RICHARD    | ROE       |         117 | PREMIUM CUSTOMER      |
| JANE       | SMITH     |          72 | PREMIUM CUSTOMER      |
| ELZA       | MARY      |           1 | INTERMEDIATE CUSTOMER |
| BABY       | ROE       |           0 | GUEST                 |
+------------+-----------+-------------+-----------------------+
5 rows in set (0.00 sec)

IF

Just like the CASE statement, IF is a function that executes a block of code to determine if a particular condition is TRUE or FALSE.

The basic syntax is shown below:

IF(condition, value_if_the_condition_is_true, value_if_the_condition_is_false)

For instance, we can run the command below to determine if a customer's balance is greater than $700.

mysql> Select first_name, last_name, balance,If(balance>700,'GREATER THAN $700','LESS OR EQUAL TO $700') as balance_status from customers;
+------------+-----------+----------+-----------------------+
| first_name | last_name | balance  | balance_status        |
+------------+-----------+----------+-----------------------+
| JOHN       | DOE       |   263.89 | LESS OR EQUAL TO $700 |
| RICHARD    | ROE       |  1887.42 | GREATER THAN $700     |
| JANE       | SMITH     | 89500.89 | GREATER THAN $700     |
| ELZA       | MARY      |   654.79 | LESS OR EQUAL TO $700 |
| BABY       | ROE       |  2758.79 | GREATER THAN $700     |
+------------+-----------+----------+-----------------------+
5 rows in set (0.00 sec)

IFNULL

The IFNULL function is very useful. It helps you return an alternative value in case a given expression evaluates to NULL. For instance, if you want to find the customers' balances from the table and one of the balances happens to be null, this may harm the computation.

To ensure that the operation is safe, you can use the IFNULL as shown below;

IFNULL(expression, alternative value)

First, let's update the balance of the first customer to NULL using the below command:

mysql> UPDATE customers set balance=NULL where customer_id='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now let's run the select statement to see the customers' balances:

mysql> Select * from customers;
+-------------+-------------------+------------+-----------+----------+
| customer_id | registration_date | first_name | last_name | balance  |
+-------------+-------------------+------------+-----------+----------+
|           1 | 2018-01-07        | JOHN       | DOE       |     NULL |
|           2 | 2018-04-30        | RICHARD    | ROE       |  1887.42 |
|           3 | 2018-06-14        | JANE       | SMITH     | 89500.89 |
|           4 | 2018-08-24        | ELZA       | MARY      |   654.79 |
|           5 | 2018-08-25        | BABY       | ROE       |  2758.79 |
+-------------+-------------------+------------+-----------+----------+
5 rows in set (0.00 sec)

As you can see above, the first customer's balance is now NULL. If your programming script can not effective handle NULL values, you will get some computation problems.

So if you anticipate that a column can have some null values but you want to return a clean set of data, use the IFNULL function as shown below:

mysql> Select first_name, last_name, IFNULL(balance,0) as balance from customers;
+------------+-----------+----------+
| first_name | last_name | balance  |
+------------+-----------+----------+
| JOHN       | DOE       |        0 |
| RICHARD    | ROE       |  1887.42 |
| JANE       | SMITH     | 89500.89 |
| ELZA       | MARY      |   654.79 |
| BABY       | ROE       |  2758.79 |
+------------+-----------+----------+
5 rows in set (0.00 sec)

The IFNULL function have effectively provided zero as the alternate value for NULL values to ensure the script consuming the output data does not land into computational problems.

Conclusion

In this article, we have taken you through all the basic MySQL functions that you can apply on your database hosted on Alibaba Cloud. We believe that you will be able to implement the above standard library functions to make your code easier to read and maintain.

Remember, when used properly, MySQL functions can speed up queries when compared with home-grown procedures that might slow down your MySQL server. If you are new to Alibaba Cloud, Sign Up now and enjoy up to $1200 free credit to test MySQL on ApsaraDB or ECS instances and over 40 more cloud computing products.