Master MySQL: Essential Commands, Queries, and Optimization Techniques

MySQL is one of the most popular open‑source relational database management systems, known for high performance, reliability and ease of use.

This article provides a comprehensive guide to common MySQL operations and optimization techniques, helping readers quickly master core usage.

1. MySQL Basic Operations

1. Connect to MySQL

mysql -u username -p

After entering the password, you enter the MySQL command‑line interface.

2. Database Operations

Create Database

CREATE DATABASE database_name;

Show Databases

SHOW DATABASES;

Select Database

USE database_name;

Drop Database

DROP DATABASE database_name;

3. Table Operations

Create Table

CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
    [table_constraints]
);

Show Table Structure

DESCRIBE table_name;

Alter Table

ALTER TABLE table_name ADD column_name data_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

Drop Table

DROP TABLE table_name;

2. Data Operations

1. Insert Data

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

2. Query Data

Basic query:

SELECT column1, column2 FROM table_name WHERE condition;

Conditional query: SELECT * FROM users WHERE age > 20; Order by: SELECT * FROM users ORDER BY age DESC; Pagination:

SELECT * FROM users LIMIT 10 OFFSET 20;

3. Update Data

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

4. Delete Data

DELETE FROM table_name WHERE condition;

3. Advanced Query Techniques

1. Join Queries

Inner join:

SELECT a.*, b.* FROM tableA a INNER JOIN tableB b ON a.key = b.key;

Left join:

SELECT a.*, b.* FROM tableA a LEFT JOIN tableB b ON a.key = b.key;

2. Subqueries

SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

3. Aggregate Functions

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT SUM(age) FROM users;

4. Group By

SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 5;

4. Index Operations

1. Create Index

CREATE INDEX index_name ON table_name(column_name);

2. Show Index

SHOW INDEX FROM table_name;

3. Drop Index

DROP INDEX index_name ON table_name;

5. User Privilege Management

1. User Management

Create User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Drop User

DROP USER 'username'@'host';

2. Privilege Management

Grant Privileges

GRANT privileges ON database.table TO 'username'@'host';

Revoke Privileges

REVOKE privileges ON database.table FROM 'username'@'host';

Flush Privileges

FLUSH PRIVILEGES;

6. Backup and Restore

1. Backup Database

mysqldump -u username -p database_name > backup_file.sql

2. Restore Database

mysql -u username -p database_name < backup_file.sql