Get the MySQL Uptime, version and port details at one query

 mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.19, for Win64 (x86_64)

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.19-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3307
Uptime:                 38 min 53 sec

Threads: 5  Questions: 300  Slow queries: 0  Opens: 1323  Flush tables: 1  Open
tables: 402  Queries per second avg: 0.128
--------------

Mysql 8 (REGEXP_LIKE) Examples

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b'); //SELECT the name start with b

 SELECT * FROM pet WHERE REGEXP_LIKE(name, 'boy'); //SELECT the name which contains boy

 SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');// select only 5 character length name

 SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');// select only 5 character length name

 SELECT * FROM pet WHERE REGEXP_LIKE(name, '^[m-q]');//select records with name start with m to q

 SELECT * FROM pet WHERE REGEXP_LIKE(name, '^[m-q]','c');//select records with name start with m to q (case sensitive)

 SELECT * FROM pet WHERE REGEXP_LIKE(name, '^[m-q]','i');//select records with name start with m to q (insensitive)

 SELECT * FROM pet WHERE REGEXP_LIKE(name, 'ri|om');//select records with name contains ri or om

 SELECT * FROM pet WHERE REGEXP_LIKE('fo\no', '^fo\no$');//start & end with fo\no

Some more Exercise 

 SELECT * FROM pet WHERE REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Baaan', '^Ba*n');                  -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Bn', '^Ba*n');                       -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Bn', '^Ba+n');                       -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('Bn', '^Ba?n');                       -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('Baan', '^Ba?n');                    -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('pi', 'pi|apa');                          -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('axe', 'pi|apa');                        -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('apa', 'pi|apa');                        -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('apa', '^(pi|apa)$');                  -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('pi', '^(pi|apa)$');                     -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('pix', '^(pi|apa)$');                   -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('abcde', 'a[bcd]{2}e');             -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('abcde', 'a[bcd]{3}e');             -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');        -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('aXbc', '[a-dXYZ]');                -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('aXbc', '^[a-dXYZ]$');            -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');          -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');        -> 0
 SELECT * FROM pet WHERE REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');        -> 1
 SELECT * FROM pet WHERE REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');       -> 0

Use powerful regular expression function REGEXP instead of LIKE in MySQL

1) Result include start with in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[abcd]';

2) Result include start with range in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[a-c]';

3) Result excluded start with in column values

SELECT * FROM database.table_name WHERE column_name REGEXP '^[^abcd]'

4) Result Conatins ban in column values

SELECT * FROM database.table_name WHERE column_name REGEXP 'ban+';

5) Result contains all character in column values

SELECT * FROM database.table_name  WHERE column_name REGEXP 'shr?';

6) Result contains 1and next any number/character in column values

SELECT * FROM database.table_name WHERE column_name  REGEXP '1.';

7) Result end with word in column values

SELECT column_name FROM database.table_name WHERE column_name  REGEXP 'ban$';

8) Result contains word in column values

 SELECT column_name FROM database.table_name WHERE  column_name REGEXP 'ban';

9) Result Strings that contain a particular substring at a specific position

 SELECT column_name FROM database.table_name WHERE column_name  REGEXP '^....ban';//

Partitioning in MySQL

Add partition in a table

ALTER TABLE database.table_name PARTITION BY Partition_Type(Column_Name) (
PARTITION Partition_Name VALUES IN (qualify_value),
PARTITION Partition_Name VALUES IN (qualify_value)
 );

Get all detail of partitions in a database 


SELECT * FROM information_schema.partitions WHERE TABLE_SCHEMA='database'  AND PARTITION_NAME IS NOT NULL;


Select the existing record from partitioned table in MySQL 


a) SELECT * FROM database.table_name PARTITION (Partition_Name1 );
b) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 );
c) SELECT * FROM database.table_name PARTITION (Partition_Name1 ,Partition_Name2 ) WHERE qualify_value='value';

Get dump of MySQL table data

MySQL dump of only single table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name > file_name.sql

MySQL dump of only multiple table record  data 


mysqldump -u root -PPORT_NUMBER -ppassword  --complete-insert database_name table_name1 table_name2 table_name3...  > file_name.sql

MySQL dump of only selected record 

mysqldump -u root -PPORT_NUMBER -ppassword database_name table_name --where="condition"

Example.

1)  mysqldump -t -u root -P3306 -ppassword school student --where="studentId=123"

                                                                   OR

   mysqldump -t -u root -ppassword school student --where="studentId=123" (without port)

2)  mysqldump -t -u root -ppassword school student --where="studentId=123 AND Name Like %sant%"

Get table size (MB) in MySQL

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sma_blink"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Compare two table structure in MySQL

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('table1','table2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;