This is a working cheat sheet: the MySQL commands you will type most often, grouped by what they act on, followed by one short session that uses them on a real table so you can see what each one prints. Everything was run in the mysql command-line client on MySQL 8.0.
The commands at a glance
| Task | Command |
|---|---|
| List databases | SHOW DATABASES; |
| Create and switch | CREATE DATABASE college; then USE college; |
| Which database am I in? | SELECT DATABASE(); |
| List tables | SHOW TABLES; |
| See columns | DESCRIBE students; |
| See the full definition | SHOW CREATE TABLE students\G |
| Read rows | SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT n; |
| Summarise | SELECT col, COUNT(*) FROM t GROUP BY col; |
| Add rows | INSERT INTO t (cols) VALUES (...); |
| Change rows | UPDATE t SET col = v WHERE ...; |
| Remove rows | DELETE FROM t WHERE ...; |
| Change structure | ALTER TABLE t ADD COLUMN ...; |
| Remove a table | DROP TABLE t; |
| Users and access | CREATE USER, GRANT, REVOKE, SHOW GRANTS; |
| Diagnose | EXPLAIN SELECT ...;, SHOW PROCESSLIST;, SELECT VERSION(); |
In the command-line client, ending a statement with \G instead of ; prints each row vertically, which is easier to read for wide output.
One session, start to finish
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
course VARCHAR(20),
city VARCHAR(20),
fee_paid DECIMAL(8,2) DEFAULT 0
);
INSERT INTO students (name, course, city, fee_paid) VALUES
('Aman','BCA','Noida',15000), ('Riya','B.Tech','Delhi',22000),
('Karan','BCA','Delhi',0), ('Neha','MCA','Noida',18000),
('Farhan','B.Tech','Lucknow',22000);
DESCRIBE students;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(40) | NO | NULL | ||
| course | varchar(20) | YES | NULL | ||
| city | varchar(20) | YES | NULL | ||
| fee_paid | decimal(8,2) | YES | 0.00 |
Filter and sort
SELECT name, course FROM students WHERE city = 'Delhi' ORDER BY name;
| name | course |
|---|---|
| Karan | BCA |
| Riya | B.Tech |
Group and total
SELECT course, COUNT(*) AS students, SUM(fee_paid) AS collected
FROM students GROUP BY course ORDER BY collected DESC;
| course | students | collected |
|---|---|---|
| B.Tech | 2 | 44000.00 |
| MCA | 1 | 18000.00 |
| BCA | 2 | 15000.00 |
Change data and structure
UPDATE students SET fee_paid = 5000 WHERE name = 'Karan';
ALTER TABLE students ADD COLUMN email VARCHAR(80) AFTER name;
DELETE FROM students WHERE id = 5;
SELECT id, name, email, fee_paid FROM students;
| id | name | fee_paid | |
|---|---|---|---|
| 1 | Aman | NULL | 15000.00 |
| 2 | Riya | NULL | 22000.00 |
| 3 | Karan | NULL | 5000.00 |
| 4 | Neha | NULL | 18000.00 |
The new email column is NULL for existing rows because no default was given. AFTER name only controls where it appears in SELECT *.
Check what you built
SHOW CREATE TABLE students\G
Create Table: CREATE TABLE `students` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
`email` varchar(80) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL,
`fee_paid` decimal(8,2) DEFAULT '0.00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Note AUTO_INCREMENT=6: deleting row 5 does not free its ID. The next insert gets 6.
Pitfalls worth remembering
- UPDATE or DELETE without WHERE changes every row. Start the client with
mysql --safe-updateswhile learning. It refuses such statements:ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. - Forgetting
USE. "No database selected" (error 1046) means you never switched database. - DROP is permanent. There is no undo. Take a backup first (see backup and restore with mysqldump).
- Quotes. Use single quotes for strings and backticks for names that clash with keywords, such as
`order`.
For the categories behind these commands, see DDL, DML, DCL and TCL in MySQL. To go deeper into editing rows, read INSERT, UPDATE and DELETE with examples.