MySQL is a powerful relational database management system (RDBMS) used by developers worldwide. Whether you're a beginner or an experienced developer, knowing key MySQL commands can significantly improve your workflow.
In this guide, we’ll go over 100 essential MySQL commands, explaining each one line by line.
- Connecting to MySQL
Before executing any command, you need to connect to MySQL:
mysql -u root -p mysql → Starts the MySQL command-line tool. -u root → Specifies the username (root is the default admin user). -p → Prompts for a password before granting access. 2. Database Operations Create a Database CREATE DATABASE my_database; CREATE DATABASE → Creates a new database. my_database → The name of the database. Show Available Databases SHOW DATABASES; Lists all databases available on the MySQL server. Select a Database USE my_database; Switches to the specified database for running queries. Delete a Database DROP DATABASE my_database; DROP DATABASE → Permanently deletes the database. 3. Table Operations Create a Table CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE users → Creates a table named users. id INT AUTO_INCREMENT PRIMARY KEY → Defines an auto-incrementing primary key. name VARCHAR(100) → Defines a column for storing names. email VARCHAR(100) UNIQUE → Ensures unique email values. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP → Stores timestamp automatically. Show Tables in the Database SHOW TABLES; Lists all tables within the selected database. Describe Table Structure DESC users; Shows column details, including data types and constraints. Modify a Table
Add a new column:
ALTER TABLE users ADD COLUMN age INT; Adds a new age column to the users table.
Change column datatype:
ALTER TABLE users MODIFY COLUMN age SMALLINT; Changes the age column to SMALLINT.
Remove a column:
ALTER TABLE users DROP COLUMN age; Deletes the age column. Delete a Table DROP TABLE users; Permanently removes the users table. 4. Data Manipulation Commands (DML) Insert Data INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com'); Adds a new record into the users table. Retrieve Data
Get all records:
SELECT * FROM users; Selects all records from the users table.
Filter data:
SELECT * FROM users WHERE email = 'john@example.com'; Fetches records where the email is 'john@example.com'.
Sort data:
SELECT * FROM users ORDER BY created_at DESC; Orders results by created_at in descending order.
Limit results:
SELECT * FROM users LIMIT 5; Returns only the first 5 records. Update Data UPDATE users SET name = 'Jane Doe' WHERE email = 'john@example.com'; Updates the name field where the email matches 'john@example.com'. Delete Data DELETE FROM users WHERE email = 'john@example.com'; Removes a record where the email is 'john@example.com'. 5. Joins (Combining Data from Multiple Tables) INNER JOIN SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id; Fetches matching records from both users and orders tables. LEFT JOIN SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id; Fetches all users, even those without orders. RIGHT JOIN SELECT users.name, orders.amount FROM users RIGHT JOIN orders ON users.id = orders.user_id; Fetches all orders, even if they don’t belong to any user. 6. Indexing and Performance Optimization Create an Index CREATE INDEX idx_email ON users(email); Creates an index on the email column for faster searches. Show Indexes in a Table SHOW INDEXES FROM users; Lists all indexes in the users table. Remove an Index DROP INDEX idx_email ON users; Deletes the idx_email index. 7. Transactions (TCL Commands) Begin a Transaction START TRANSACTION; Starts a new transaction. Commit a Transaction COMMIT; Saves changes permanently. Rollback a Transaction ROLLBACK; Reverts changes made in the transaction. 8. User Management and Security Create a New User CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; Adds a new user to MySQL. Grant Permissions GRANT ALL PRIVILEGES ON my_database.* TO 'newuser'@'localhost'; Grants all privileges on my_database to newuser. Revoke Permissions REVOKE ALL PRIVILEGES ON my_database.* FROM 'newuser'@'localhost'; Removes privileges from newuser. Delete a User DROP USER 'newuser'@'localhost'; Removes the user from MySQL. 9. Backup and Restore Backup a Database mysqldump -u root -p my_database > backup.sql Creates a backup of my_database. Restore a Database mysql -u root -p my_database < backup.sql Restores my_database from backup.sql. 10. System and Metadata Commands Show Current Database SELECT DATABASE(); Show Running Processes SHOW PROCESSLIST; Check MySQL Version SELECT VERSION(); Show Current User SELECT USER(); Final Thoughts
Mastering these 100 MySQL commands will significantly improve your database management skills. Whether you're creating tables, optimizing queries, or managing users, these commands will make you more efficient as a developer.
Which MySQL command do you use the most? Let’s discuss in the comments! 🚀
Top comments (0)