Basic MySQL Commands: A Practical Guide

Basic MySQL commands are the essential SQL statements you use to connect to a database, create and modify tables, and insert, retrieve, update, and delete data. This guide covers the most common commands with syntax examples, explains how they work, and points out frequent pitfalls so you can start working with MySQL confidently.

Connecting to MySQL

Before running any commands, you need to connect to the MySQL server. From a terminal, use the mysql client:

mysql -u username -p

This prompts for a password. To connect to a specific database directly:

mysql -u username -p database_name

If the server is on a different host or port, add -h host and -P port (note the capital P for port). Once connected, you see a mysql> prompt. Type EXIT; or \q to quit.

Database and Table Basics

Databases are containers for tables. Common commands:

• SHOW DATABASES; – lists all databases you have access to.
• USE database_name; – selects a database for subsequent commands.
• CREATE DATABASE database_name; – creates a new database.
• DROP DATABASE database_name; – deletes a database and all its tables (use with caution).
• SHOW TABLES; – lists tables in the selected database.
• DESCRIBE table_name; or DESC table_name; – shows column details (name, type, nullability, key, default).

To create a table, you specify columns and their data types:

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

This creates a users table with an auto-incrementing integer primary key, a required username, a unique email, and a timestamp that defaults to the current time.

Inserting and Retrieving Data

To add a record, use INSERT:

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

To retrieve data, use SELECT. The basic form:

SELECT * FROM users;

Select specific columns:

SELECT username, email FROM users;

Filter rows with WHERE:

SELECT * FROM users WHERE username = 'alice';

Sort results with ORDER BY (ascending by default, use DESC for descending):

SELECT * FROM users ORDER BY created_at DESC;

Limit the number of rows:

SELECT * FROM users LIMIT 10;

Combine these clauses: SELECT username FROM users WHERE email LIKE '%example.com' ORDER BY username LIMIT 5;

Updating and Deleting Data

To change existing rows, use UPDATE with a WHERE clause to target specific records:

UPDATE users SET email = 'alice@newdomain.com' WHERE username = 'alice';

Without WHERE, the update applies to all rows – a common mistake. To delete rows:

DELETE FROM users WHERE username = 'bob';

To remove all rows but keep the table structure, use TRUNCATE TABLE users; (faster than DELETE without WHERE, but cannot be rolled back).

Altering Tables

After a table exists, you often need to change its structure. Common ALTER TABLE commands:

• Add a column: ALTER TABLE users ADD COLUMN age INT;
• Drop a column: ALTER TABLE users DROP COLUMN age;
• Modify a column type: ALTER TABLE users MODIFY COLUMN email VARCHAR(200);
• Rename a column: ALTER TABLE users RENAME COLUMN email TO email_address;
• Rename the table: ALTER TABLE users RENAME TO people;

Common Mistakes and Best Practices

New users often trip on these:

• Forgetting the semicolon – MySQL waits for it before executing. If you press Enter without it, the prompt changes to ->; type ; and press Enter.
• Using reserved words as names – SELECT, TABLE, WHERE are reserved. If you must use one, wrap it in backticks: `select`.
• Mixing up string quotes – Use single quotes for strings; double quotes are often accepted but can cause issues in some modes.
• Updating without WHERE – Always double-check that your UPDATE or DELETE has a WHERE clause unless you intend to affect all rows.
• Case sensitivity – SQL keywords are case-insensitive, but table and column names may be case-sensitive depending on the operating system (Linux is case-sensitive for table names).

For safety, consider running START TRANSACTION; before a destructive operation, then ROLLBACK; if something goes wrong or COMMIT; to save. This works with InnoDB tables (the default in modern MySQL).

Frequently Asked Questions

How do I see all databases and tables?

Use SHOW DATABASES; to list databases. After selecting one with USE database_name;, run SHOW TABLES; to see its tables. To view a table’s structure, use DESCRIBE table_name; or SHOW COLUMNS FROM table_name;.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one, can have a WHERE clause, and can be rolled back if inside a transaction. TRUNCATE removes all rows in one operation, cannot be filtered, and generally cannot be rolled back. TRUNCATE is faster but more destructive.

How do I fix “ERROR 1045 (28000): Access denied”?

This means your username or password is wrong, or the user lacks privileges for the database. Check your credentials and that the user has been granted access to the specific database. You may need to contact your administrator or use the root account.

Can I undo a DELETE without a backup?

If you are using InnoDB and the delete was not committed, you can roll back with ROLLBACK;. If it was committed, you cannot undo it. Regular backups are essential – use mysqldump to create a logical backup.

What does “ERROR 1064 (42000): You have an error in your SQL syntax” mean?

This is a generic syntax error. Check for missing semicolons, misspelled keywords, incorrect quotes, or commas in the wrong place. The error message often points to the exact location near the problem.

How do I import an SQL file?

From the terminal, use: mysql -u username -p database_name < file.sql. This runs all commands in the file. Alternatively, inside the MySQL client, use SOURCE file.sql;.

Related

• Bootstrap Accordion Plus/Minus Icon Toggle
• Prompt Compression: Measure Coding Agent Accuracy & Cost
• Prompt Injection Defense: Protect AI Agents