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

Prerequisites

  1. 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.
  2. A MySQL database.
  3. 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.

$ mysql -uroot -p -h198.18.0.6

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.

mysql>Create database test_db CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql>use test_db;
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;
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)
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');
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)
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.

LEFT(string, chars)
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)
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.

SUBSTR (string, start, length)
1468-12362252
1470-15555588
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.

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

UCASE

This function converts characters to uppercase.

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.

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)

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.

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.

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)
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.

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

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.

mysql>CASE
WHEN condition1 THEN expression1
WHEN condition2 THEN expression2
ELSE expression3
END
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;
+------------+-----------+-------------+-----------------------+
| 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.

IF(condition, value_if_the_condition_is_true, value_if_the_condition_is_false)
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.

IFNULL(expression, alternative value)
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
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)
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)

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com