# [ MYSQL Advanced ] ( CheatSheet )

1. Advanced Database Operations

● Clone a database: CREATE DATABASE new_db AS (SELECT * FROM old_db);

● Drop all tables in a database: SET @tables = NULL; SELECT
GROUP_CONCAT(table_name) INTO @tables FROM information_schema.tables
WHERE table_schema = 'dbname'; SET @tables = CONCAT('DROP TABLE ',
@tables); PREPARE stmt FROM @tables; EXECUTE stmt;
● Rename a database: CREATE DATABASE new_db; RENAME TABLE old_db.table TO
new_db.table; DROP DATABASE old_db;

2. Advanced Table Operations

● Partition a table: ALTER TABLE tablename PARTITION BY RANGE

(year(column)) (PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES
LESS THAN (2001), PARTITION p2 VALUES LESS THAN MAXVALUE);
● Convert a table to use dynamic columns: ALTER TABLE tablename
ROW_FORMAT=DYNAMIC;
● Move a table to another schema: ALTER TABLE old_schema.table RENAME
new_schema.table;
● Change a column collation: ALTER TABLE tablename MODIFY column_name
VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
● Copy a table structure only: CREATE TABLE new_table LIKE old_table;

3. Advanced Data Manipulation

● Insert multiple rows: INSERT INTO tablename (column1, column2) VALUES

(value1, value2), (value3, value4), (value5, value6);
● Insert if not exists: INSERT IGNORE INTO tablename (column1, column2)
VALUES (value1, value2);
● Insert or update: INSERT INTO tablename (column1, column2) VALUES
(value1, value2) ON DUPLICATE KEY UPDATE column1 = VALUES(column1),
column2 = VALUES(column2);
● Batch update: UPDATE tablename SET column = CASE WHEN condition1 THEN
value1 WHEN condition2 THEN value2 ELSE column END WHERE id IN (id1, id2,
id3);
● Delete all rows and reset identity: TRUNCATE TABLE tablename;

4. Advanced Subqueries and CTEs

By: Waleed Mousa

 ● CTE for recursive queries: WITH RECURSIVE cte_name (column1, column2) AS
(SELECT column1, column2 FROM table WHERE condition UNION ALL SELECT
column1, column2 FROM table JOIN cte_name ON table.column =
cte_name.column) SELECT * FROM cte_name;
● Use of subquery in JOIN: SELECT * FROM table1 JOIN (SELECT column FROM
table2 WHERE condition) AS subquery ON table1.column = subquery.column;
● Correlated subquery: SELECT * FROM table1 WHERE column1 = (SELECT
MAX(column2) FROM table2 WHERE table2.foreign_key = table1.key);

5. Advanced User and Permissions Management

● Create a user with specific privileges: CREATE USER 'username'@'host'

IDENTIFIED BY 'password'; GRANT SELECT, INSERT ON dbname.* TO
'username'@'host';
● Clone a user with privileges: CREATE USER 'newuser'@'host' IDENTIFIED BY
'password'; GRANT ALL PRIVILEGES ON dbname.* TO 'newuser'@'host'
IDENTIFIED BY 'password' WITH GRANT OPTION;
● Show users with specific privileges: SELECT user, host FROM mysql.user
WHERE user LIKE '%';

6. Advanced Backup and Recovery

● Incremental backup: mysqldump --single-transaction --flush-logs

--master-data=2 --all-databases > backupfile.sql;
● Restore specific table from backup: mysql -u username -p dbname <
backupfile.sql --one-database dbname --tables tablename;
● Hot backup with Percona XtraBackup: xtrabackup --backup
--target-dir=/data/backups/

7. Advanced Performance and Maintenance

● Partition pruning: ALTER TABLE tablename DROP PARTITION partition_name;

● Query profiling: SET profiling = 1; SELECT * FROM tablename; SHOW
PROFILES;
● Defragment table: OPTIMIZE TABLE tablename;

8. Advanced String and Text Functions

● Extract part of string: SELECT SUBSTRING_INDEX(column, 'delimiter', part)

FROM tablename;

By: Waleed Mousa

 ● Find and replace using REGEXP: SELECT REGEXP_REPLACE(column, 'pattern',
'replacement') FROM tablename;
● Pattern matching: SELECT * FROM tablename WHERE column REGEXP 'pattern';

9. Advanced Numeric and Date Functions

● Calculate age from date: SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE())

AS age FROM tablename;
● Generate a sequence of numbers: SELECT @row := @row + 1 AS row, column
FROM tablename, (SELECT @row := 0) AS r;
● Format number with commas: SELECT FORMAT(column, 0) FROM tablename;

10. Advanced Conditional Expressions

● Nested IF statements: SELECT IF(condition1, result1, IF(condition2,

result2, default_result)) FROM tablename;
● Conditional aggregation: SELECT SUM(IF(condition, column, 0)) FROM
tablename;
● CASE in ORDER BY: SELECT * FROM tablename ORDER BY CASE WHEN condition1
THEN column1 WHEN condition2 THEN column2 ELSE column3 END;

11. Advanced Join Techniques

● Join with USING clause: SELECT * FROM table1 JOIN table2 USING
(common_column);
● Anti Join (not in): SELECT * FROM table1 LEFT JOIN table2 ON
table1.column = table2.column WHERE table2.column IS NULL;
● Semi Join (exists): SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM
table2 WHERE table1.column = table2.column);

12. Advanced Set Operations

● Intersect using INNER JOIN: SELECT table1.column FROM table1 INNER JOIN
table2 ON table1.column = table2.column;
● Except using LEFT JOIN: SELECT table1.column FROM table1 LEFT JOIN table2
ON table1.column = table2.column WHERE table2.column IS NULL;

13. Advanced Security

● Generate UUID: SELECT UUID();

By: Waleed Mousa

 ● Encrypt a column: UPDATE tablename SET column = AES_ENCRYPT(column,
'key');
● Decrypt a column: SELECT AES_DECRYPT(column, 'key') FROM tablename;

14. Advanced Transaction Control

● Savepoint and rollback to savepoint: SAVEPOINT savepoint_name; ROLLBACK

TO SAVEPOINT savepoint_name;
● Set autocommit mode: SET autocommit = 0;
● Show transaction isolation level: SHOW VARIABLES LIKE
'transaction_isolation';

15. Advanced Administration

● Change user password: ALTER USER 'username'@'host' IDENTIFIED BY

'newpassword';
● Check MySQL server uptime: SHOW GLOBAL STATUS LIKE 'Uptime';
● Monitor slow queries: SET GLOBAL slow_query_log = 'ON';

16. Advanced Debugging and Profiling

● Log all queries for debugging: SET GLOBAL general_log = 'ON';

● Show current locks: SHOW ENGINE INNODB STATUS;
● Analyze query cost: SHOW STATUS LIKE 'Last_query_cost';

17. Advanced Index and Performance Optimization

● Add composite index: CREATE INDEX idx_name ON tablename (column1,

column2);
● Drop all indexes in a table: ALTER TABLE tablename DROP INDEX index_name;
● Check index usage: SHOW INDEX FROM tablename;

18. Advanced Numeric and Date Functions

● Add minutes to a time: SELECT DATE_ADD(time_column, INTERVAL 15 MINUTE)

FROM tablename;
● Round to nearest integer: SELECT ROUND(column) FROM tablename;
● Find the difference in months: SELECT TIMESTAMPDIFF(MONTH, date1, date2)
FROM tablename;

By: Waleed Mousa

19. Advanced Aggregate Functions Beyond Basics

● Median of a column: SELECT column FROM tablename ORDER BY column LIMIT 1

OFFSET (SELECT COUNT(*) FROM tablename) / 2;
● Percentile calculation: SELECT column FROM tablename ORDER BY column
LIMIT 1 OFFSET (SELECT COUNT(*) * 0.9) - 1;
● Mode of a column: SELECT column, COUNT(*) AS freq FROM tablename GROUP BY
column ORDER BY freq DESC LIMIT 1;

20. Advanced Database Maintenance and Inspection

● Check for corrupted tables: CHECK TABLE tablename EXTENDED;

● Repair corrupted table: REPAIR TABLE tablename;
● Inspect table space usage: SHOW TABLE STATUS LIKE 'tablename';

21. Advanced Information Schema Usage

● Get column statistics: SELECT * FROM information

Schema.COLUMN_STATISTICS WHERE TABLE_NAME = 'tablename';
● List foreign keys in a schema: SELECT * FROM
information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA =
'dbname';
● Get table size: SELECT table_name AS 'Table', ROUND((data_length +
index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM
information_schema.TABLES WHERE table_schema = 'dbname';

22. Advanced Performance Schema for Diagnostics

● Track wait events: SELECT * FROM

performance_schema.events_waits_summary_global_by_event_name;
● Check IO statistics: SELECT * FROM
performance_schema.file_summary_by_event_name;
● Monitor query execution time: SELECT * FROM
performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME
= 'dbname' ORDER BY SUM_TIMER_WAIT DESC;

23. Advanced Query Optimization Techniques

● Use index hints: SELECT * FROM tablename USE INDEX (index_name) WHERE
column = 'value';

By: Waleed Mousa

 ● Force index usage: SELECT * FROM tablename FORCE INDEX (index_name) WHERE
column = 'value';
● Optimize query plan: SELECT SQL_NO_CACHE * FROM tablename WHERE column =
'value';

24. Advanced Data Export and Import

● Export data to CSV: SELECT * INTO OUTFILE '/path/to/file.csv' FIELDS

TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM
tablename;
● Import data from CSV: LOAD DATA INFILE '/path/to/file.csv' INTO TABLE
tablename FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY
'\n';
● Export specific columns: SELECT column1, column2 INTO OUTFILE
'/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
TERMINATED BY '\n' FROM tablename;

25. Advanced JSON Functions

● Extract JSON value: SELECT JSON_EXTRACT(json_column, '$.key') FROM

tablename;
● Update JSON value: UPDATE tablename SET json_column =
JSON_SET(json_column, '$.key', 'new_value') WHERE condition;
● Merge JSON values: SELECT JSON_MERGE(json_column1, json_column2) FROM
tablename;

26. Advanced Spatial Data Operations

● Create spatial index: CREATE SPATIAL INDEX idx_name ON tablename

(spatial_column);
● Select within radius: SELECT * FROM tablename WHERE
ST_Distance_Sphere(point_column, POINT(lng, lat)) <= radius;
● Find nearest point: SELECT * FROM tablename ORDER BY
ST_Distance_Sphere(point_column, POINT(lng, lat)) LIMIT 1;

27. Advanced Full-Text Search

● Full-text search with relevance: SELECT *, MATCH (column) AGAINST

('search_term') AS relevance FROM tablename WHERE MATCH (column) AGAINST
('search_term');

By: Waleed Mousa

 ● Boolean mode search: SELECT * FROM tablename WHERE MATCH (column) AGAINST
('+term1 -term2' IN BOOLEAN MODE);
● Natural language search: SELECT * FROM tablename WHERE MATCH (column)
AGAINST ('search_term' IN NATURAL LANGUAGE MODE);

28. Advanced View Management

● Create a view with joins: CREATE VIEW viewname AS SELECT a.column1,

b.column2 FROM table1 a JOIN table2 b ON a.common_column =
b.common_column;
● Update a view: CREATE OR REPLACE VIEW viewname AS SELECT column1, column2
FROM tablename;
● Drop a view: DROP VIEW viewname;

29. Advanced Stored Procedures and Functions

● Create a stored procedure: CREATE PROCEDURE proc_name (IN param1 INT, OUT
param2 VARCHAR(100)) BEGIN SELECT column INTO param2 FROM tablename WHERE
id = param1; END;
● Call a stored procedure: CALL proc_name(1, @output); SELECT @output;
● Create a stored function: CREATE FUNCTION func_name (param1 INT) RETURNS
VARCHAR(100) BEGIN RETURN (SELECT column FROM tablename WHERE id =
param1); END;

30. Advanced Triggers

● Create a trigger for update: CREATE TRIGGER trigger_name BEFORE UPDATE ON

tablename FOR EACH ROW BEGIN SET NEW.column = OLD.column + 1; END;
● Create a trigger for insert: CREATE TRIGGER trigger_name BEFORE INSERT ON
tablename FOR EACH ROW BEGIN SET NEW.column = 'default_value'; END;
● Drop a trigger: DROP TRIGGER trigger_name;

31. Advanced Replication Management

● Setup replication user: CREATE USER 'repl'@'%' IDENTIFIED WITH

mysql_native_password BY 'password'; GRANT REPLICATION SLAVE ON *.* TO
'repl'@'%';
● Show replication status: SHOW SLAVE STATUS\G;
● Start replication: START SLAVE;
● Stop replication: STOP SLAVE;

By: Waleed Mousa

32. Advanced Event Scheduler

● Create an event: CREATE EVENT event_name ON SCHEDULE EVERY 1 DAY STARTS

'2024-01-01 00:00:00' DO UPDATE tablename SET column = value WHERE
condition;
● Show events: SHOW EVENTS;
● Drop an event: DROP EVENT event_name;

33. Advanced Schema Management

● Copy schema: mysqldump -d -u username -p old_db | mysql -u username -p

new_db;
● Rename schema: RENAME TABLE old_schema.table1 TO new_schema.table1,
old_schema.table2 TO new_schema.table2;
● Export schema structure: mysqldump -d -u username -p dbname > schema.sql;

34. Advanced Audit and Logging

● Enable general query log: SET GLOBAL general_log = 'ON';

● Show slow queries: SELECT * FROM mysql.slow_log ORDER BY start_time DESC;
● Enable binary logging: SET GLOBAL log_bin = 'ON';

35. Advanced Partition Management

● Reorganize partition: ALTER TABLE tablename REORGANIZE PARTITION

partition_name INTO (PARTITION new_name VALUES LESS THAN (value));
● Merge partitions: ALTER TABLE tablename MERGE PARTITIONS partition1,
partition2 INTO new_partition;
● Drop partition: ALTER TABLE tablename DROP PARTITION partition_name;

36. Advanced Data Masking and Anonymization

● Mask sensitive data: UPDATE tablename SET column =

CONCAT(SUBSTRING(column, 1, 3), '***', SUBSTRING(column, -3)) WHERE
condition;
● Anonymize data with random values: UPDATE tablename SET column =
CONCAT(CHAR(FLOOR(65 + RAND() * 26)), CHAR(FLOOR(65 + RAND() * 26)),
CHAR(FLOOR(65 + RAND() * 26))) WHERE condition;

37. Advanced Data Migration

By: Waleed Mousa

 ● Migrate data between tables: INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM old_table;
● Copy data between servers: mysqldump -u username -p --host=source_host
dbname | mysql -u username -p --host=destination_host dbname;
● Transform data during migration: INSERT INTO new_table (column1, column2)
SELECT column1, UPPER(column2) FROM old_table;

38. Advanced Security Management

● Change user host: UPDATE mysql.user SET host = 'new_host' WHERE user =
'username' AND host = 'old_host'; FLUSH PRIVILEGES;
● Disable a user account: ALTER USER 'username'@'host' ACCOUNT LOCK;
● Enable a user account: ALTER USER 'username'@'host' ACCOUNT UNLOCK;

39. Advanced Performance Analysis

● Show buffer pool status: SHOW ENGINE INNODB STATUS\G;

● Analyze query cache: SHOW STATUS LIKE 'Qcache%';
● Analyze index usage: SHOW STATUS LIKE 'Handler_read%';

40. Advanced Storage Engine Management

● Convert table storage engine: ALTER TABLE tablename ENGINE = 'InnoDB';

● Check storage engine status: SHOW ENGINE INNODB STATUS;
● Enable/disable storage engine: SET GLOBAL storage_engine = 'InnoDB';

41. Advanced Geographic Data Management

● Insert spatial data: INSERT INTO tablename (spatial_column) VALUES

(ST_GeomFromText('POINT(lng lat)'));
● Query spatial data: SELECT ST_AsText(spatial_column) FROM tablename WHERE
condition;
● Spatial distance calculation: SELECT ST_Distance_Sphere(Point(lng1,
lat1), Point(lng2, lat2)) FROM tablename;

42. Advanced JSON Operations

● Merge JSON arrays: SELECT JSON_ARRAY_APPEND(column, '$', value) FROM

tablename;
● Remove JSON key: UPDATE tablename SET json_column =
JSON_REMOVE(json_column, '$.key') WHERE condition;

By: Waleed Mousa

● Extract multiple JSON values: SELECT
JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.key1')),
JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.key2')) FROM tablename;

By: Waleed Mousa