Master MySQL: Essential Commands for Databases, Tables, and Operations

1. Database‑level commands (6)

SHOW DATABASES;

CREATE DATABASE db_name DEFAULT CHARSET utf8mb4;

DROP DATABASE db_name;

USE db_name;

SELECT DATABASE();

SHOW CREATE DATABASE db_name;

2. Table‑structure commands (8)

SHOW TABLES;

CREATE TABLE user (id BIGINT PRIMARY KEY, name VARCHAR(50));

DESC user;

SHOW CREATE TABLE user;

DROP TABLE user;

ALTER TABLE user ADD COLUMN age INT;

ALTER TABLE user MODIFY age BIGINT;

RENAME TABLE user TO user_new;

3. Data manipulation (CRUD, 10)

INSERT INTO user VALUES (1, 'Tom', 18);

INSERT INTO user VALUES (2, 'Jack', 20), (3, 'Lucy', 22);

SELECT * FROM user;

SELECT * FROM user WHERE age > 18;

SELECT * FROM user ORDER BY age DESC;

SELECT * FROM user LIMIT 10 OFFSET 0;

UPDATE user SET age = 20 WHERE id = 1;

DELETE FROM user WHERE id = 1;

SELECT COUNT(*) FROM user;

SELECT age, COUNT(*) FROM user GROUP BY age;

4. Indexes and execution plan (5)

CREATE INDEX idx_age ON user(age);

CREATE UNIQUE INDEX idx_name ON user(name);

SHOW INDEX FROM user;

DROP INDEX idx_age ON user;

EXPLAIN SELECT * FROM user WHERE age = 18;

5. Transactions and locks (4)

START TRANSACTION;

COMMIT;

ROLLBACK;

SELECT * FROM information_schema.innodb_trx;

6. Operations and troubleshooting (3)

SHOW PROCESSLIST;

SHOW STATUS;

SHOW VARIABLES;