Complete MySQL commands reference for web developers. Learn database operations, user management, optimization with practical examples and common pitfalls.
You’ve installed MySQL, created a database through phpMyAdmin’s GUI, and now you’re stuck when the GUI doesn’t offer the option you need. Or your hosting only provides command-line access and you’re not sure where to start.
Most web developers learn MySQL backwards – they click through phpMyAdmin for years without understanding what’s actually happening under the hood. When they need to optimize a slow query, configure user permissions properly, or automate database backups, they realize they never learned MySQL commands.
This guide covers essential MySQL commands for database management organized by real-world use cases. Each command includes the syntax, when you’d use it, practical examples, and the common mistakes that cause problems.
TL;DR
phpMyAdmin, MySQL Workbench, and Adminer provide graphical interfaces for database management. They’re excellent tools. But they don’t replace understanding MySQL commands for several reasons:
GUIs have limitations. Some operations simply aren’t available through graphical interfaces. Advanced query optimization, replication setup, and certain privilege configurations require command-line access.
Server access might be command-line only. Production servers often don’t have GUIs installed. SSH access with MySQL command-line is the only option. Knowing MySQL commands means you can manage databases anywhere.
Automation requires commands. Backup scripts, deployment procedures, and database migration tools use MySQL commands. You can’t automate mouse clicks in phpMyAdmin.
Debugging needs command visibility. When a query fails, seeing the exact MySQL command and error message is more useful than a GUI error popup. Command-line shows you precisely what’s happening.
Speed for repetitive tasks. Creating 10 databases with specific character sets, adding users with particular privileges, or running a series of optimization commands – these tasks are faster with command-line than clicking through GUI forms.
This guide focuses on MySQL commands while acknowledging that GUIs have their place. Use phpMyAdmin for browsing data and building complex queries visually. Use command-line for administration, automation, and tasks where you need full control.
Before running MySQL commands, you need to connect to the MySQL server.
Purpose: Connect to MySQL server with username and password prompt.
When to use: Every time you need command-line database access.
Example:
mysql -u root -p
After running this command, MySQL prompts for the password. Type the password (it won’t display as you type) and press Enter. You’ll see the MySQL prompt: mysql>
Common pitfall: Trying to include the password in the command: mysql -u root -pMyPassword works but is insecure – the password appears in your command history and process list. Always use -p alone and enter the password at the prompt.
Purpose: Connect to remote MySQL server and select a specific database.
When to use: Accessing MySQL on a different server or immediately working with a specific database.
Example:
mysql -h db.example.com -u webapp_user -p production_db
Common pitfall: Firewall blocking port 3306 (MySQL’s default port). If the connection times out, verify the MySQL server allows remote connections and no firewall blocks the port.
These MySQL commands create, view, modify, and delete databases.
Purpose: List all databases on the MySQL server.
When to use: Checking what databases exist before creating a new one or selecting which database to work with.
Example:
SHOW DATABASES;
Output shows database names:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| my_website_db |
+--------------------+
Common pitfall: None – this is a read-only command safe to run anytime. Note the semicolon at the end (required for all MySQL commands).
Purpose: Create a new empty database.
When to use: Setting up a new website, application, or project that needs its own database.
Example:
CREATE DATABASE ecommerce_site CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
The character set and collation specify how text is stored and sorted. utf8mb4 supports all Unicode characters including emojis. utf8mb4_unicode_ci provides case-insensitive sorting.
Common pitfall: Forgetting character set specification. MySQL defaults to latin1 on older installations, which doesn’t support international characters properly. Always explicitly set CHARACTER SET utf8mb4.
Purpose: Select a database to work with (all subsequent commands operate on this database).
When to use: After connecting to MySQL or when switching between databases.
Example:
USE ecommerce_site;
After this command, table operations, queries, and other commands affect ecommerce_site database.
Common pitfall: Forgetting to run USE and then wondering why table commands fail with “No database selected” error.
Purpose: Permanently delete a database and all its tables.
When to use: Removing old test databases or decommissioning projects.
Example:
DROP DATABASE old_test_site;
Common pitfall: THIS IS PERMANENT AND IRREVERSIBLE. There’s no “Are you sure?” prompt. Double-check the database name before running this command. Always backup before dropping databases.
These MySQL commands manage table structure.
Purpose: List all tables in the currently selected database.
When to use: Checking what tables exist before creating new ones or selecting a table to query.
Example:
USE ecommerce_site;
SHOW TABLES;
Common pitfall: Running this without first selecting a database with USE returns “No database selected” error.
Purpose: Show table structure (columns, data types, keys, defaults).
When to use: Understanding table schema before writing queries or modifying structure.
Example:
DESCRIBE users;
Output shows column details:
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | varchar(255) | NO | UNI | NULL | |
| password | varchar(255) | NO | | NULL | |
| created | datetime | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
Common pitfall: None – this is read-only and extremely useful. Shorthand: DESC users;
Purpose: Create a new table with specified columns and structure.
When to use: Setting up database schema for an application.
Example:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Common pitfall: Forgetting PRIMARY KEY on ID column or using wrong data types. DECIMAL for money (not FLOAT – precision matters), VARCHAR for variable-length text, INT for whole numbers.
Purpose: Add a new column to existing table.
When to use: Extending table structure without recreating it.
Example:
ALTER TABLE products ADD COLUMN description TEXT AFTER price;
Common pitfall: Adding NOT NULL columns to tables with existing data requires a DEFAULT value or MySQL rejects the operation.
Purpose: Permanently delete a table and all its data.
When to use: Removing obsolete tables.
Example:
DROP TABLE old_logs;
Common pitfall: PERMANENT DELETION. Always backup before dropping tables. Use DROP TABLE IF EXISTS table_name; to avoid errors if table doesn’t exist.
These MySQL commands create, read, update, and delete table data.
Purpose: Retrieve all rows and columns from a table.
When to use: Viewing table contents, debugging, or when you need all data.
Example:
SELECT * FROM users;
SELECT * FROM products WHERE stock > 0;
SELECT name, price FROM products ORDER BY price DESC LIMIT 10;
Common pitfall: Running SELECT * FROM large_table; on tables with millions of rows crashes phpMyAdmin and locks your browser. Always use LIMIT on large tables: SELECT * FROM users LIMIT 100;
Purpose: Add new rows to a table.
When to use: Creating new records through scripts or manual data entry.
Example:
INSERT INTO products (name, price, stock)
VALUES ('Widget', 29.99, 100);
INSERT INTO products (name, price, stock) VALUES
('Gadget', 49.99, 50),
('Doohickey', 19.99, 200);
Common pitfall: Forgetting to escape single quotes in values. INSERT INTO products (name) VALUES ('O'Reilly') fails. Escape with double single quote: VALUES ('O''Reilly') or use prepared statements in application code.
Purpose: Modify existing rows.
When to use: Correcting data, updating status fields, or changing values.
Example:
UPDATE products SET stock = stock - 1 WHERE id = 42;
UPDATE users SET status = 'active' WHERE last_login > '2026-01-01';
Common pitfall: FORGETTING THE WHERE CLAUSE updates EVERY row. UPDATE products SET price = 0; sets all product prices to zero. Always use WHERE to limit which rows change.
Purpose: Remove rows from a table.
When to use: Deleting old records, removing test data, or cleaning up.
Example:
DELETE FROM sessions WHERE expires < NOW();
DELETE FROM logs WHERE created < DATE_SUB(NOW(), INTERVAL 30 DAY);
Common pitfall: FORGETTING WHERE CLAUSE deletes EVERYTHING. DELETE FROM users; empties the table. Always include WHERE. For complete table clearing, TRUNCATE TABLE table_name; is faster.
These MySQL commands control database access and user privileges.
Purpose: Create a new MySQL user account.
When to use: Setting up application database users or granting access to team members.
Example:
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'SecurePassword123';
CREATE USER 'remote_admin'@'%' IDENTIFIED BY 'AnotherPassword456';
The host portion specifies where the user can connect from. 'localhost' means only local connections. '%' means any host.
Common pitfall: Using '%' for production database users is a security risk. Limit to specific IP addresses when possible: 'webapp'@'192.168.1.100'
Purpose: Give a user specific permissions on databases or tables.
When to use: Setting up application users with limited access or configuring team member permissions.
Example:
GRANT ALL PRIVILEGES ON ecommerce_site.* TO 'webapp'@'localhost';
GRANT SELECT, INSERT, UPDATE ON ecommerce_site.products TO 'readonly'@'%';
FLUSH PRIVILEGES;
FLUSH PRIVILEGES; applies the changes immediately.
Common pitfall: Granting ALL PRIVILEGES when users only need specific permissions. Applications should get SELECT, INSERT, UPDATE, DELETE but not DROP, CREATE, ALTER unless absolutely necessary.
Purpose: Remove previously granted permissions.
When to use: Reducing user permissions or removing access.
Example:
REVOKE DELETE ON ecommerce_site.* FROM 'webapp'@'localhost';
FLUSH PRIVILEGES;
Common pitfall: Forgetting FLUSH PRIVILEGES; after revoking – permissions might not update immediately.
Purpose: Delete a MySQL user account.
When to use: Removing access for former team members or cleaning up unused accounts.
Example:
DROP USER 'old_employee'@'%';
Common pitfall: Dropping user doesn’t automatically close active connections. User can still execute queries until their session ends.
Purpose: View what permissions a user has.
When to use: Auditing user access or troubleshooting permission errors.
Example:
SHOW GRANTS FOR 'webapp'@'localhost';
Common pitfall: None – this is read-only and useful for security audits.
These MySQL commands improve performance and maintain database health.
Purpose: Show how MySQL executes a query (which indexes it uses, how many rows it scans).
When to use: Debugging slow queries or optimizing database performance.
Example:
EXPLAIN SELECT * FROM products WHERE category = 'electronics';
Output shows query execution plan, revealing whether indexes are being used.
Common pitfall: Ignoring type column in EXPLAIN output. type: ALL means full table scan (slow). type: ref or type: index means index is used (fast).
Purpose: Create an index to speed up queries on specific columns.
When to use: Queries on a column are slow and EXPLAIN shows full table scans.
Example:
CREATE INDEX idx_email ON users (email);
CREATE INDEX idx_category_price ON products (category, price);
Common pitfall: Over-indexing slows down INSERT/UPDATE operations. Only index columns used in WHERE, JOIN, and ORDER BY clauses frequently.
Purpose: Defragment table data and reclaim unused space.
When to use: After many DELETE or UPDATE operations or when table performance degrades.
Example:
OPTIMIZE TABLE products;
Common pitfall: OPTIMIZE locks the table during execution. Run during low-traffic periods on production databases.
Purpose: Update table statistics MySQL uses for query optimization.
When to use: After significant data changes or when query performance unexpectedly degrades.
Example:
ANALYZE TABLE products;
Common pitfall: None – this is quick and doesn’t lock tables in InnoDB.
These scenarios demonstrate how MySQL commands solve actual database management problems.
Situation: Website shows “Access denied for user ‘webapp’@’localhost'”
Solution:
-- Check if user exists
SELECT User, Host FROM mysql.user WHERE User = 'webapp';
-- If user doesn't exist, create it
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password';
-- Grant necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON website_db.* TO 'webapp'@'localhost';
FLUSH PRIVILEGES;
Situation: Page loading takes 10+ seconds, suspect database query.
Solution:
-- Find slow queries in application logs, then analyze
EXPLAIN SELECT * FROM orders
JOIN order_items ON orders.id = order_items.order_id
WHERE orders.user_id = 123;
-- If EXPLAIN shows full table scan on user_id
CREATE INDEX idx_user_id ON orders (user_id);
-- Verify improvement
EXPLAIN SELECT * FROM orders ...;
Situation: All queries to a specific table are hanging.
Solution:
-- Find queries locking the table
SHOW PROCESSLIST;
-- Kill the blocking query (replace X with Process ID)
KILL X;
Situation: About to run ALTER TABLE on production, want a safety backup.
Solution (command line, not MySQL prompt):
mysqldump -u root -p database_name > backup_2026-02-20.sql
To restore:
mysql -u root -p database_name < backup_2026-02-20.sql
Situation: Ran DELETE without WHERE clause or with wrong condition.
Solution: If you have backups:
# Restore entire database from backup
mysql -u root -p database_name < latest_backup.sql
If no backup exists, data is lost. This is why automated backups matter.
Situation: VARCHAR(50) column needs to be VARCHAR(255) but table has data.
Solution:
ALTER TABLE users MODIFY COLUMN address VARCHAR(255);
For type conversions that might lose data:
-- Check data first
SELECT MAX(LENGTH(phone)) FROM users;
-- If safe, convert
ALTER TABLE users MODIFY COLUMN phone CHAR(10);
The best approach: use GUIs for convenience, learn command-line for power. When your GUI doesn’t offer what you need or you hit a limitation, command-line knowledge saves you.
WebHostMost provides MySQL database access through multiple interfaces:
Web Control Panel includes database management tools for creating databases, managing users, and basic operations.
phpMyAdmin access for visual database administration and query building.
Command-line access through Web Terminal for MySQL management and automation.
Our MySQL infrastructure includes:
View hosting plans with included MySQL databases.
More developer resources: WebHostMost Blog
MySQL database management becomes significantly easier once you understand the commands behind the GUI clicks. phpMyAdmin is a useful tool, but knowing MySQL commands gives you the flexibility to work anywhere, automate tasks, and solve problems that GUIs can’t handle.
The commands in this guide cover the essential 80% of database management tasks web developers encounter. Print this reference. Keep it accessible while working. Over time, common commands become second nature while less frequent ones remain available when needed.
Next steps: explore stored procedures, triggers, and replication for advanced database management. But master these fundamentals first.
Database problems don’t wait for you to find the right GUI button. Learn MySQL commands and solve issues immediately.