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

Prerequisites

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

--

--

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