MySQL Commands Guide: Beginner to Intermediate Basics, CRUD, Users, Backup & Troubleshooting

1. Introduction

MySQL is a widely used open-source relational database among database management systems (DBMS). It is especially popular for web applications and enterprise systems. In this article, we will explain essential MySQL commands in detail and structure the content so that beginners to intermediate users can learn smoothly.

1.1 What is MySQL?

MySQL is software used to manage a relational database (RDB). Data is stored in tables, and you manipulate the data using SQL (Structured Query Language). MySQL has the following characteristics:

• Open-source and free for anyone to use
• Fast and lightweight performance
• Compatible with many programming languages (such as PHP, Python, and Java)
• Suitable for operating large-scale databases

1.2 Benefits of Learning MySQL Commands

To use MySQL efficiently, it is important to understand the basic commands. Key benefits include:

• More efficient data management: Mastering SQL commands lets you retrieve, update, and delete data quickly
• Workflow automation: Using scripts allows you to automate database operations
• Smoother error handling: When problems occur, you can resolve them with the appropriate SQL commands

1.3 What You Will Learn in This Article

This article explains the following topics in detail:

• Basic MySQL operations (starting, connecting, and database operations)
• Adding, retrieving, updating, and deleting data (CRUD operations)
• User management and privilege settings
• Useful commands and troubleshooting

Next, we will go through each section in order. Let’s start with the basic MySQL operations.

2. Basic MySQL Operations (Beginner-Friendly)

2.1 Starting and Connecting to MySQL

2.1.1 Starting MySQL

Because MySQL runs as a server program, you must start the server first. The startup method differs depending on the OS.

Starting on Linux / macOS

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

sudo systemctl start mysql

Check whether MySQL is running

sudo systemctl status mysql

If you see “active (running)”, MySQL is running normally.

Starting on Windows

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

net start mysql

Or, if you are using XAMPP, open the XAMPP Control Panel and click “Start” for “MySQL”.

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

Connect as the root user

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

mysql -u root -p

After you press Enter, you will be prompted to enter a password. If you enter the correct password, you can log in to MySQL.

Connect 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 a MySQL server at 192.168.1.100, write:

mysql -h 192.168.1.100 -u root -p

Connect by specifying a port number

The default MySQL port is 3306. 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 you are connected to MySQL, try running some basic commands.

2.2.1 Check the MySQL version

To check the MySQL version you are currently using, run the following command:

SELECT VERSION();

2.2.2 Check the current database

To check which database is currently selected, run:

SELECT DATABASE();

If no database is selected, NULL is returned.

2.2.3 List available databases

To list the databases that exist in MySQL, use:

SHOW DATABASES;

2.3 Common Errors and Fixes

2.3.1 “Access denied” error

Example error:

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

How to fix:

1. Verify the password is correct
2. Check and reset root privileges

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

Example error:

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

How to fix:

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

sudo systemctl restart mysql

2.4 Summary

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

3. Database Operations

In MySQL, you create a database to manage data, and then organize information by placing tables inside it. In this section, we will explain basic operations such as creating, selecting, listing, and deleting databases in detail.

3.1 Creating a Database

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

3.1.1 Create a basic database

If you run the following SQL command, you can create a new database named my_database.

CREATE DATABASE my_database;

Message when creation succeeds

Query OK, 1 row affected (0.01 sec)

3.1.2 When you try to create a database that already exists

If the database already exists, you will see an error like the following:

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

To prevent this error, it is useful to add the IF NOT EXISTS option.

CREATE DATABASE IF NOT EXISTS my_database;

When you run this command, the database is created only if it does not already exist.

3.2 Listing Databases

To check which databases exist on the current MySQL server, use the SHOW DATABASES command.

SHOW DATABASES;

Example output:

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

Note: System databases such as mysql and information_schema are required for MySQL administration.

3.3 Selecting a Database

In MySQL, you must select the database you want to use. To specify a database, run the USE command.

USE my_database;

Message when successful

Database changed

To confirm which database is currently selected, use the following command:

SELECT DATABASE();

Example output:

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

3.4 Deleting a Database

To delete a database you no longer need, use the DROP DATABASE command.

DROP DATABASE my_database;

3.4.1 Important notes before deletion

• Be careful: deleting a database removes all data!
• To avoid accidental errors, you can also add IF EXISTS.

DROP DATABASE IF EXISTS my_database;

This command finishes without an error even if my_database does not exist.

3.5 Common Errors and Fixes

3.5.1 “Access denied for user” error

Example error:

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

How to fix

• Grant privileges using the GRANT command

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

3.5.2 “Can’t drop database” error

Example error:

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

How to fix

• Restart MySQL

sudo systemctl restart mysql

• Delete all tables in my_database first, then run DROP DATABASE.

3.6 Summary

In this section, you learned how to create, select, list, and delete databases.

4. Table Operations

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

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

4.1 Creating a Table

To create a table, use the CREATE TABLE command. If you run the following SQL, it creates 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 an integer (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 (VARCHAR) up to 50 characters and is required due to NOT NULL.
• email VARCHAR(100) UNIQUE NOT NULL
• The email column is a string up to 100 characters, and the UNIQUE constraint prevents duplicate email addresses.
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• The created_at column automatically sets the current date/time as the default value.

Message when successful

Query OK, 0 rows affected (0.02 sec)

4.2 Checking Table Structure

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

DESC users;

Or

SHOW COLUMNS FROM users;

Example output:

+------------+--------------+------+-----+-------------------+----------------+
| 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 list the tables that exist in the current database, run the SHOW TABLES command.

SHOW TABLES;

Example output:

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

4.4 Deleting a Table

To delete a table you no longer need, use the DROP TABLE command.

DROP TABLE users;

4.4.1 Important notes when deleting a table

• Be careful: deleting a table permanently removes the data!
• To avoid errors when deleting, add IF EXISTS.

DROP TABLE IF EXISTS users;

Message when successful

Query OK, 0 rows affected (0.01 sec)

4.5 Resetting Table Data (Delete All Rows)

If you want to keep the table structure but delete only the data inside, use TRUNCATE TABLE.

TRUNCATE TABLE users;

Difference from DROP TABLE

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

4.6 Common Errors and Fixes

4.6.1 “Table already exists” error

Example error:

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

How to fix

• Check existing tables

SHOW TABLES;

• Create the table 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

Example error:

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

How to fix

• Check whether the table exists using SHOW TABLES;
• If it does not exist, add IF EXISTS

DROP TABLE IF EXISTS users;

4.7 Summary

In this section, we covered the basic MySQL table operations: creating, checking, and deleting tables.