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

  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.

Run the command below to login to the MySQL server.

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:

Then, switch to the database:

Next, create a sample table:

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

Populating the Table with Test Data

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

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

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:

FORMAT

The FORMAT function returns a number formatted to a string and rounded to specified 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:

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;

LEFT

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

Syntax:

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

RIGHT

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

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

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;

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:

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:

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:

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:

UCASE

This function converts characters to uppercase.

Example

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:

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;

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:

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:

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:

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:

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:

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.

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:

Then, let’s select the customer to confirm:

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:

DATEDIFF

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

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

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.

Here are some general DATE_FORMAT values:

  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

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

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:

DAYNAME

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

MONTH

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

Example:

NOW

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

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:

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:

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

Output:

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:

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

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;

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

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

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:

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.

Reference:https://www.alibabacloud.com/blog/how-to-use-standard-mysql-library-functions-on-alibaba-cloud-ecs-and-apsaradb_594267?spm=a2c41.12420324.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

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