Complete 2026 Guide to MySQL Commands: Syntax, Usage, and Best Practices

Connect to MySQL

Log in locally: mysql -u root -p Enter the password when prompted.

Connect to a remote MySQL server

Command syntax: mysql -h 192.168.10.100 -P 3306 -u root -p Parameter meanings:

-h – host IP address

-P – port number

-u – username

-p – password (prompted)

SHOW DATABASES

List all databases, including system schemas: SHOW DATABASES; Typical output includes information_schema, performance_schema, sys, and user‑defined databases such as order_db.

CREATE DATABASE

Create a new database, optionally specifying the character set to avoid garbled characters:

CREATE DATABASE order_db CHARACTER SET utf8mb4;

USE

Select the current database:

USE order_db;

SHOW TABLES

List all tables in the current database. Example output: SHOW TABLES; Result may include user, order, product.

DESCRIBE TABLE

View the structure of a table: DESC user; Or retrieve the CREATE statement:

SHOW CREATE TABLE user;

CREATE TABLE

Define a table with columns and constraints:

CREATE TABLE user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    age INT,
    create_time DATETIME
);

INSERT

Single‑row insert:

INSERT INTO user (username, age) VALUES ('Tom', 20);

Batch insert (far more efficient than looping inserts):

INSERT INTO user (username, age) VALUES 
    ('Tom', 20),
    ('Jack', 22),
    ('Lucy', 18);

SELECT

Basic query and common variations:

SELECT * FROM user;
SELECT * FROM user WHERE age > 20;
SELECT * FROM user ORDER BY age DESC;
SELECT * FROM user LIMIT 0, 10;
SELECT COUNT(*) FROM user;

UPDATE

Update specific rows (omit WHERE to modify the entire table, which is risky):

UPDATE user SET age = 25 WHERE id = 1;
UPDATE user SET age = age + 1 WHERE age < 18;

DELETE

Delete specific rows or all rows (dangerous without WHERE):

DELETE FROM user WHERE id = 1;
DELETE FROM user;          -- delete all rows
DROP TABLE user;          -- drop the table
DROP DATABASE order_db;   -- drop the database

ALTER TABLE

Modify table structure:

Add column: ALTER TABLE user ADD email VARCHAR(100); Modify column: ALTER TABLE user MODIFY email VARCHAR(200); Drop column: ALTER TABLE user DROP COLUMN email; Add index:

ALTER TABLE user ADD INDEX idx_username (username);

EXPLAIN

Show the execution plan of a query. Important fields for performance tuning:

type – access type (e.g., ALL means full table scan, the worst performance)

key – index used by the optimizer

rows – estimated number of rows examined; larger values indicate slower queries

Extra – additional information such as Using filesort (extra sorting) or Using temporary (temporary table creation); both suggest optimization opportunities

Example:

EXPLAIN SELECT * FROM user WHERE username='Tom';

Typical result columns illustrate the fields above.