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

$ mysql -uroot -p -h198.18.0.6

Step 2: Creating the Test Database

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

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

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

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

LEFT

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

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

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

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

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

UCASE

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

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

AVG

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

CEIL

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

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

MIN

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

SUM

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

COUNT

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

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

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

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

DATEDIFF

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

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

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

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

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

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

YEAR

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

CASE

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

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

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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Tech Insights — Two-phase Commit Protocol for Distributed Transactions

The Evolution and Future of Hybrid Cloud

How to start using Godot Engine in 2021 ?

Optimal Solution to 100% CPU Usage of Databases

Running 32bit software and the Linux Journal Archive on 64bit Debian 10 Buster

Bulk Update Multiple WebLogic WLSDM Settings via WL-OPC

WebHR Topped The Human Resources (HR) Category Leaders Q4 2017

“We’ll do that later”: how to improve your HTTP response cycle

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

More from Medium

Spinning Apache Kafka® Microservices With Cadence Workflows

How we implemented Pod Logging at NetBook

Standardising Workflows & Crontabs with Airflow

Using Search Template — ElasticSearch