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

<code>mysql -u username -p</code>

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

2. Database Operations

Create Database

<code>CREATE DATABASE database_name;</code>

Show Databases

<code>SHOW DATABASES;</code>

Select Database

<code>USE database_name;</code>

Drop Database

<code>DROP DATABASE database_name;</code>

3. Table Operations

Create Table

<code>CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
    [table_constraints]
);</code>

Show Table Structure

<code>DESCRIBE table_name;</code>

Alter Table

<code>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;</code>

Drop Table

<code>DROP TABLE table_name;</code>

2. Data Operations

1. Insert Data

<code>INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);</code>

2. Query Data

Basic query:

<code>SELECT column1, column2 FROM table_name WHERE condition;</code>

Conditional query:

<code>SELECT * FROM users WHERE age > 20;</code>

Order by:

<code>SELECT * FROM users ORDER BY age DESC;</code>

Pagination:

<code>SELECT * FROM users LIMIT 10 OFFSET 20;</code>

3. Update Data

<code>UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;</code>

4. Delete Data

<code>DELETE FROM table_name WHERE condition;</code>

3. Advanced Query Techniques

1. Join Queries

Inner join:

<code>SELECT a.*, b.* FROM tableA a INNER JOIN tableB b ON a.key = b.key;</code>

Left join:

<code>SELECT a.*, b.* FROM tableA a LEFT JOIN tableB b ON a.key = b.key;</code>

2. Subqueries

<code>SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);</code>

3. Aggregate Functions

<code>SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT SUM(age) FROM users;</code>

4. Group By

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

4. Index Operations

1. Create Index

<code>CREATE INDEX index_name ON table_name(column_name);</code>

2. Show Index

<code>SHOW INDEX FROM table_name;</code>

3. Drop Index

<code>DROP INDEX index_name ON table_name;</code>

5. User Privilege Management

1. User Management

Create User

<code>CREATE USER 'username'@'host' IDENTIFIED BY 'password';</code>

Drop User

<code>DROP USER 'username'@'host';</code>

2. Privilege Management

Grant Privileges

<code>GRANT privileges ON database.table TO 'username'@'host';</code>

Revoke Privileges

<code>REVOKE privileges ON database.table FROM 'username'@'host';</code>

Flush Privileges

<code>FLUSH PRIVILEGES;</code>

6. Backup and Restore

1. Backup Database

<code>mysqldump -u username -p database_name > backup_file.sql</code>

2. Restore Database

<code>mysql -u username -p database_name < backup_file.sql</code>