Ultimate MySQL Commands Guide: Beginner to Pro SQL Explained

1. Introduction

MySQL is a widely used open-source relational database among database management systems (DBMS). It is particularly widely utilized in web applications and enterprise systems. This article provides a detailed explanation of MySQL’s basic commands, structured to allow smooth learning from beginners to intermediate users.

1.1 What is MySQL?

MySQL is software for managing relational databases (RDB). Data is stored in table format, and data is manipulated using SQL (Structured Query Language). MySQL has the following features.

• Open Source and can be used for free by anyone
• Characterized by fast and lightweight operation
• Compatible with many programming languages (PHP, Python, Java, etc.)
• Supports operation of large-scale databases

1.2 Benefits of Learning MySQL Commands

To use MySQL efficiently, it is important to understand the basic commands. There are the following benefits.

• Efficient Data Management: By mastering SQL commands, data retrieval, updates, and deletions can be performed quickly
• Business Automation: By utilizing scripts, database operations can be automated
• Smooth Error Handling: When problems occur, they can be resolved with appropriate SQL commands

1.3 What You Can Learn in This Article

This article provides detailed explanations on the following topics.

• Basic MySQL operations (startup, connection, database operations)
• Data addition, retrieval, update, and deletion (CRUD operations)
• User management and permission settings
• Useful commands and troubleshooting

From here on, we will proceed with detailed explanations of each section in order. Let’s start with the basic MySQL operations.

2. Basic MySQL Operations (For Beginners)

2.1 MySQL Startup and Connection

2.1.1 Starting MySQL

MySQL operates as a server program, so you need to start the server first. The startup method varies depending on the OS.

Starting on Linux / macOS

On Linux or macOS, you can start MySQL using the systemctl command.

sudo systemctl start mysql

Checking the MySQL Startup Status

sudo systemctl status mysql

If it displays “active (running)”, then MySQL is running normally.

Starting on Windows

On Windows, MySQL is often run as a service, so use the net start command.

net start mysql

Alternatively, if using XAMPP, start “MySQL” from the XAMPP control panel.

2.1.2 Connecting to MySQL

To connect to MySQL, use the mysql command. The basic connection command is as follows.

mysql -u username -p

Connecting as root User

By default, the root user is set as the administrator. You can connect with the following command.

mysql -u root -p

Press Enter to be prompted for the password. Enter the correct password to log in to MySQL.

Connecting to a Specific Host

To connect to a remote MySQL server, use the -h option.

mysql -h hostname -u username -p

For example, to connect to the MySQL server at 192.168.1.100, use the following.

mysql -h 192.168.1.100 -u root -p

Connecting by Specifying Port Number

The default port number for MySQL is 3306, but if it has been changed, you can specify it with the -P option.

mysql -h 192.168.1.100 -P 3307 -u root -p

2.2 Basic MySQL Commands

Once connected to MySQL, let’s try executing basic operation commands.

2.2.1 Checking the MySQL Version

To check the version of MySQL you are currently using, execute the following command.

SELECT VERSION();

2.2.2 Checking the Current Database

To check the currently selected database, execute the following command.

SELECT DATABASE();

If nothing is selected, NULL is returned.

2.2.3 Displaying a List of Available Databases

To list the databases existing in MySQL, use the following command.

SHOW DATABASES;

2.3 Common Errors and Solutions

2.3.1 “Access denied” Error

Error example:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Solution:

1. Check if the password is correct
2. Check the root permissions and reset them

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
FLUSH PRIVILEGES;

2.3.2 “Can’t connect to MySQL server on ‘localhost’” Error

Error example:

ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)

Solution:

1. Check if MySQL is running (systemctl status mysql)
2. Restart MySQL

sudo systemctl restart mysql

2.4 Summary

In this section, we explained how to start and connect to MySQL.

3. Database Operations

In MySQL, you create a database (Database) to manage data, and organize information by placing tables (Table) within it. This section provides a detailed explanation of basic operations such as creating, selecting, listing, and deleting databases.

3.1 Creating a Database

To create a database in MySQL, use the CREATE DATABASE command.

3.1.1 Basic Database Creation

Executing the following SQL command creates a new database my_database.

CREATE DATABASE my_database;

Message on Successful Creation

Query OK, 1 row affected (0.01 sec)

3.1.2 Attempting to Create an Already Existing Database

If the database already exists, an error like the following is displayed.

ERROR 1007 (HY000): Can't create database 'my_database'; database exists

To prevent this error, it’s convenient to use the IF NOT EXISTS option.

CREATE DATABASE IF NOT EXISTS my_database;

Executing this command creates the database only if it does not exist.

3.2 Listing Databases

To check the databases in the current MySQL server, use the SHOW DATABASES command.

SHOW DATABASES;

Example output:

+--------------------+
| Database          |
+--------------------+
| information_schema |
| my_database       |
| mysql             |
| performance_schema |
| sys               |
+--------------------+

* System databases such as mysql and information_schema are necessary for MySQL management.

3.3 Selecting a Database

In MySQL, you need to select the created database to use it. To specify the database, execute the USE command.

USE my_database;

Message on Success

Database changed

To check the currently selected database, use the following command.

SELECT DATABASE();

Example output:

+------------+
| DATABASE() |
+------------+
| my_database |
+------------+

3.4 Deleting a Database

To delete an unnecessary database, use the DROP DATABASE command.

DROP DATABASE my_database;

3.4.1 Precautions Before Deletion

• Be careful! Deleting will erase all data.
• To avoid accidental deletion, you can also add IF EXISTS.

DROP DATABASE IF EXISTS my_database;

This command ends the process without error even if my_database does not exist.

3.5 Common Errors and Solutions

3.5.1 “Access denied for user” Error

Error example:

ERROR 1044 (42000): Access denied for user 'user_name'@'localhost' to database 'my_database'

Solution

• Grant permissions using the GRANT command

GRANT ALL PRIVILEGES ON my_database.* TO 'user_name'@'localhost';
FLUSH PRIVILEGES;

3.5.2 “Can’t drop database” Error

Error example:

ERROR 1010 (HY000): Error dropping database (can't rmdir './my_database', errno: 39)

Solution

• Restart MySQL

sudo systemctl restart mysql

• Delete all tables in my_database and then execute DROP DATABASE.

3.6 Summary

In this section, we learned about creating, selecting, listing, and deleting databases.

4. Table Operations

After creating the database, you need to create tables (Table) within it to organize the data. A table is like a spreadsheet for storing data, consisting of columns (fields) and rows (records).

In this section, we will explain in detail basic operations such as creating, checking, listing, and deleting tables.

4.1 Table Creation

To create a table, use the CREATE TABLE command. Executing the following SQL will create a table named users.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.1.1 Explanation of Each Element When Creating a Table

• id INT AUTO_INCREMENT PRIMARY KEY
• The id column is of integer type (INT), automatically increments (AUTO_INCREMENT), and is set as the primary key (PRIMARY KEY).
• name VARCHAR(50) NOT NULL
• The name column is a string of up to 50 characters (VARCHAR) and is required due to NOT NULL.
• email VARCHAR(100) UNIQUE NOT NULL
• The email column is a string of up to 100 characters, and due to the UNIQUE constraint, duplicate email addresses cannot be registered.
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• The created_at column automatically sets the current date and time as the default value.

Message on Success

Query OK, 0 rows affected (0.02 sec)

4.2 Checking the Table Structure

To check the structure of the created table, use DESC or SHOW COLUMNS.

DESC users;

or

SHOW COLUMNS FROM users;

Output example:

+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50)  | NO   |     | NULL              |                |
| email      | varchar(100) | NO   | UNI | NULL              |                |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

4.3 Listing Tables

To check the list of tables existing in the current database, execute the SHOW TABLES command.

SHOW TABLES;

Output example:

+------------------+
| Tables_in_mydb   |
+------------------+
| users            |
| products         |
| orders           |
+------------------+

4.4 Deleting Tables

To delete an unnecessary table, use the DROP TABLE command.

DROP TABLE users;

4.4.1 Precautions When Deleting Tables

• Be careful, as deletion will completely remove the data!
• To avoid accidental deletion, adding IF EXISTS can prevent errors.

DROP TABLE IF EXISTS users;

Message on Success

Query OK, 0 rows affected (0.01 sec)

4.5 Resetting Table Data (Delete All)

If you want to delete only the data inside the table while keeping the table structure intact, use TRUNCATE TABLE.

TRUNCATE TABLE users;

Difference from DROP TABLE

• DROP TABLE deletes the table itself.
• TRUNCATE TABLE deletes only the data and retains the table structure.

4.6 Common Errors and Solutions

4.6.1 “Table already exists” Error

Error example:

ERROR 1050 (42S01): Table 'users' already exists

Solution

• Check for existing tables

SHOW TABLES;

• Create with IF NOT EXISTS

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4.6.2 “Unknown table” Error

Error example:

ERROR 1051 (42S02): Unknown table 'users'

Solution

• Use SHOW TABLES; to check if the target table exists
• If it does not exist, add IF EXISTS

DROP TABLE IF EXISTS users;

4.7 Summary

In this section, we explained the basic operations for creating, checking, and deleting tables in MySQL.

5. Data Operations (CRUD)

After creating the database and tables, it is important to properly add, retrieve, update, and delete data. In MySQL, the basics of data operations are the four operations called CRUD (Create, Read, Update, Delete).

In this section, we will explain in detail the methods for adding data (INSERT), retrieving data (SELECT), updating data (UPDATE), and deleting data (DELETE).

5.1 Adding Data (INSERT)

To add new data to a table, use the INSERT INTO command.

5.1.1 Basic Data Addition

For example, to add a new user to the users table, execute the following SQL.

INSERT INTO users (name, email) VALUES ('Tanaka Taro', 'tanaka@example.com');

If successful, the following message will be displayed.

Query OK, 1 row affected (0.01 sec)

5.1.2 Adding Multiple Records at Once

It is also possible to add multiple records at once.

INSERT INTO users (name, email) VALUES
('Sato Hanako', 'sato@example.com'),
('Suzuki Ichiro', 'suzuki@example.com'),
('Takahashi Mayu', 'takahashi@example.com');

5.2 Retrieving Data (SELECT)

To retrieve data from a table, use the SELECT statement.

5.2.1 Retrieving All Data

To retrieve all data from the users table, execute the following SQL.

SELECT * FROM users;

Example output:

+----+------------+------------------+---------------------+
| id | name       | email            | created_at          |
+----+------------+------------------+---------------------+
|  1 | Tanaka Taro| tanaka@example.com | 2024-02-01 10:00:00 |
|  2 | Sato Hanako| sato@example.com  | 2024-02-01 10:01:00 |
|  3 | Suzuki Ichiro | suzuki@example.com | 2024-02-01 10:02:00 |
+----+------------+------------------+---------------------+

5.2.2 Retrieving Specific Columns Only

If you want to display only specific columns (fields), specify the column names.

SELECT name, email FROM users;

5.2.3 Retrieving Data with Conditions

Using the WHERE clause, you can retrieve only data that matches specific conditions.

SELECT * FROM users WHERE name = 'Sato Hanako';

5.2.4 Sorting (ORDER BY)

To sort data in ascending or descending order, use ORDER BY.Ascending Order (Smallest First)

SELECT * FROM users ORDER BY id ASC;

Descending Order (Largest First)

SELECT * FROM users ORDER BY id DESC;

5.2.5 Limiting the Number of Records (LIMIT)

To retrieve only the first 2 records:

SELECT * FROM users LIMIT 2;

5.3 Updating Data (UPDATE)

To update data in a table, use the UPDATE statement.

5.3.1 Updating Specified Data

For example, to change the email address of the user with id = 2, execute the following SQL.

UPDATE users SET email = 'sato_hanako@example.com' WHERE id = 2;

5.3.2 Updating Multiple Columns Simultaneously

UPDATE users SET name = 'Sato Misaki', email = 'misaki@example.com' WHERE id = 2;

5.3.3 All Records Will Be Updated If No Condition Is Specified

Caution!If you do not specify the WHERE clause, all data will be updated.

UPDATE users SET email = 'test@example.com';  -- ⚠️ All users' emails will be changed

5.4 Deleting Data (DELETE)

To delete unnecessary data, use the DELETE FROM statement.

5.4.1 Deleting Specified Data

To delete the user with id = 3, execute the following SQL.

DELETE FROM users WHERE id = 3;

5.4.2 Deleting All Data

Caution!If you omit the WHERE clause, all data in the table will be deleted.

DELETE FROM users;  -- ⚠️ Deletes all data (dangerous!)

If deleting everything, TRUNCATE TABLE is recommended

TRUNCATE TABLE users;

DELETE performs deletion processing row by row, but TRUNCATE TABLE initializes the table, so it is faster.

5.5 Summary

In this section, we learned the basic operations for adding data (INSERT), retrieving data (SELECT), updating data (UPDATE), and deleting data (DELETE).

6. User Management and Permission Settings

In MySQL, to ensure database security, it is important to set appropriate permissions for each user. Especially in projects involving multiple developers or production environment databases, user management is essential.

In this section, we will explain MySQL user management and permission settings in detail.

6.1 MySQL User Management

User information in MySQL is stored in the user table of the mysql database. As the basics of user management, let’s learn the methods for creating, listing, and deleting users.

6.1.1 Creating Users

To create a new user, use the CREATE USER command.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

The meaning of this command:

• Creates a user named new_user
• localhost (allows only local connections)
• Sets password123 as the password

Creating a User for Remote Access

To access MySQL from outside the server, specify '%'.

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'securepassword';

% means allows access from all IP addresses.

6.2 Listing Users

To check the list of existing users, refer to the mysql.user table.

SELECT user, host FROM mysql.user;

Output example:

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | localhost |
| new_user  | localhost |
| remote_user | %       |
+-----------+-----------+

In this table, you can check each user’s user (username) and host (connectable host).

6.3 Deleting Users

To delete unnecessary users, use the DROP USER command.

DROP USER 'new_user'@'localhost';

Notes After Deletion

• Deleting a user also deletes that user’s permissions.
• If deleted by mistake, you need to create it again with CREATE USER.

6.4 Permission Management

In MySQL, you can set permissions for each user to limit the scope of operations.

6.4.1 Granting Permissions (GRANT)

To grant specific permissions to a user, use the GRANT command.

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';

This command grants all permissions on my_database to new_user.

Granting Only Specific Permissions

GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'new_user'@'localhost';

This command allows new_user only data retrieval (SELECT), insertion (INSERT), and update (UPDATE).

6.5 Checking Permissions

To check a user’s permissions, use the SHOW GRANTS command.

SHOW GRANTS FOR 'new_user'@'localhost';

Output example:

+------------------------------------------------------------+
| Grants for new_user@localhost                              |
+------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'new_user'@'localhost' |
+------------------------------------------------------------+

6.6 Revoking Permissions (REVOKE)

To revoke specific permissions from a user, use the REVOKE command.

REVOKE INSERT, UPDATE ON my_database.* FROM 'new_user'@'localhost';

Executing this command, new_user loses the INSERT (add) and UPDATE (update) permissions.

6.7 Changing Passwords

6.7.1 Changing a User’s Password

To change a MySQL user’s password, use ALTER USER.

ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'newpassword123';

6.7.2 Changing the root User’s Password

To change the administrator (root) password, execute the following command.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

After changing the password, execute FLUSH PRIVILEGES to apply the permissions.

FLUSH PRIVILEGES;

6.8 Common Errors and Solutions

6.8.1 “Access denied” Error

Error example:

ERROR 1045 (28000): Access denied for user 'new_user'@'localhost' (using password: YES)

Solution

• Check permissions with SHOW GRANTS

SHOW GRANTS FOR 'new_user'@'localhost';

• Re-grant permissions

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;

6.8.2 “User already exists” Error

Error example:

ERROR 1396 (HY000): Operation CREATE USER failed for 'new_user'@'localhost'

Solution

• If it already exists, delete it and recreate

DROP USER 'new_user'@'localhost';
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

6.9 Summary

In this section, we explained MySQL’s user management and permission settings in detail.
Especially important points:

• Create users with CREATE USER
• Grant permissions with GRANT
• Check permissions with SHOW GRANTS
• Change passwords with ALTER USER

7. Useful MySQL Commands (For Intermediate Users)

MySQL provides many useful commands in addition to basic data operations (CRUD) to support database management and optimization. In this section, we introduce MySQL commands for intermediate users, such as database backup and restoration, performance optimization, and checking system information.

7.1 Database Backup

To prevent data loss, MySQL allows backup (export) and restoration (import).

7.1.1 Backup Using mysqldump

Using the mysqldump command, you can back up a specific database.

mysqldump -u root -p my_database > my_database_backup.sql

Option Descriptions

• -u root → Execute as root user
• -p → Enter password
• my_database → Name of the database to back up
• > → Output file (e.g., my_database_backup.sql)

7.1.2 Backing Up All Databases

To back up all databases at once, use the --all-databases option.

mysqldump -u root -p --all-databases > all_databases_backup.sql

7.2 Database Restoration

To restore backup data to MySQL, use the mysql command.

7.2.1 Restoring a Single Database

mysql -u root -p my_database < my_database_backup.sql

7.2.2 Restoring All Databases

mysql -u root -p < all_databases_backup.sql

7.3 Checking Running Processes

To check the MySQL server’s load status or which queries are running, use SHOW PROCESSLIST.

SHOW PROCESSLIST;

Output example:

+----+------+-----------+----+---------+------+----------+------------------+
| Id | User | Host      | db | Command | Time | State    | Info             |
+----+------+-----------+----+---------+------+----------+------------------+
|  1 | root | localhost | my_database | Query  |    10 | Sending data | SELECT * FROM users |
+----+------+-----------+----+---------+------+----------+------------------+

7.3.1 Displaying All Currently Running Processes in Detail

SHOW FULL PROCESSLIST;

7.4 Query Optimization

7.4.1 Checking Execution Plan (EXPLAIN)

Using the EXPLAIN command, you can check the query’s execution plan and identify performance issues.

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Output example:

+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | email_index   | email| 62      | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

• possible_keys: Available indexes
• key: Actually used index
• rows: Number of rows searched (fewer is faster)

7.5 Checking Indexes

As data volume increases, searches can slow down. Therefore, it is important to set indexes appropriately.

7.5.1 Checking Current Indexes

To get a list of table indexes, use SHOW INDEX.

SHOW INDEX FROM users;

Output example:

+-------+------------+----------+--------------+-----------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name     | Index_type|
+-------+------------+----------+--------------+-----------------+-----------+
| users |          0 | PRIMARY  |            1 | id              | BTREE     |
| users |          1 | email    |            1 | email           | BTREE     |
+-------+------------+----------+--------------+-----------------+-----------+

7.6 Checking Database Status

7.6.1 Checking MySQL Version

To check the current MySQL version, execute the following command.

SELECT VERSION();

Output example:

+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+

7.6.2 Checking Database Size

To check the size of each database, execute the following query.

SELECT table_schema AS "Database", 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;

Output example:

+---------------+-----------+
| Database      | Size (MB) |
+---------------+-----------+
| my_database   |     45.23 |
| mysql         |      2.10 |
+---------------+-----------+

7.7 Summary

In this section, we introduced useful MySQL commands for intermediate users.
Particularly important ones are:

• Database backup and restoration (mysqldump, mysql)
• Checking running processes (SHOW PROCESSLIST)
• Query optimization (EXPLAIN)
• Index management (SHOW INDEX)
• Database status check (information schema queries)

8. MySQL Troubleshooting

When using MySQL, you may encounter various issues such as connection errors or performance problems. In this section, we introduce common MySQL errors and their solutions.

8.1 If You Cannot Connect to MySQL

8.1.1 “Access denied for user” Error

Error example:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Causes

• Incorrect password
• The user does not have appropriate permissions
• Issue with MySQL authentication method

Solutions

1. Verify if the password is correct

mysql -u root -p

2. Check current user permissions

SELECT user, host FROM mysql.user;

3. Reset the password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

8.1.2 “Can’t connect to MySQL server on ‘localhost’” Error

Error example:

ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)

Causes

• MySQL server is stopped
• Port has been changed
• Influence of firewall

Solutions

1. Check if MySQL is running

sudo systemctl status mysql

2. Restart MySQL

sudo systemctl restart mysql

3. Specify the connection port

mysql -u root -p --port=3306

4. Check the firewall

sudo ufw allow 3306/tcp

8.2 Database or Table Issues

8.2.1 “Unknown database” Error

Error example:

ERROR 1049 (42000): Unknown database 'my_database'

Causes

• Database does not exist
• Typo in database name

Solutions

1. Check the list of databases

SHOW DATABASES;

2. Create the database

CREATE DATABASE my_database;

8.2.2 “Table doesn’t exist” Error

Error example:

ERROR 1146 (42S02): Table 'my_database.users' doesn't exist

Causes

• Table does not exist
• Typo in table name

Solutions

1. Check the list of tables

SHOW TABLES;

2. Create the table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

8.2.3 “Table is marked as crashed” Error

Error example:

Table 'users' is marked as crashed and should be repaired

Causes

• Server crashed abnormally
• Data is corrupted

Solutions

1. Repair the table

REPAIR TABLE users;

2. Check the entire database

CHECK TABLE users;

8.3 Performance Issues

8.3.1 “Too many connections” Error

Error example:

ERROR 1040 (HY000): Too many connections

Causes

• Exceeded maximum connections

Solutions

1. Check current number of connections

SHOW STATUS LIKE 'Threads_connected';

2. Increase maximum connections

SET GLOBAL max_connections = 200;

8.3.2 Slow Queries

Causes

• No indexes set
• Inefficient queries

Solutions

1. Check execution plan

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

2. Create an index

CREATE INDEX email_index ON users(email);

8.4 Summary

In this section, we introduced common issues that occur in MySQL and their solutions.

• Connection errors → Check password and user permissions
• Database and table issues → Check with SHOW DATABASES / SHOW TABLES
• Performance issues → Utilize EXPLAIN and INDEX

9. [With Tables] MySQL Command List (Reference Version)

In this section, we summarize the key MySQL commands introduced so far in a table format. Organized by category, you can quickly locate the commands you need.

9.1 Basic Operation Commands

9.2 Database Operations

9.3 Table Operations

9.4 Data Operations (CRUD)

9.5 User Management and Permissions

9.6 Backup and Restore

9.7 Performance and Optimization

9.8 Troubleshooting

9.9 Summary

In this section, we have compiled the important MySQL commands into a table format. In particular, we have organized commands commonly used in practice—such as data operations (CRUD), backups, performance management, and troubleshooting—so that you can reference them immediately.