MySQL Commands Categorized

The Ultimate List of MySQL Commands Categorized by Usage

Mastering MySQL commands is essential for database administrators, developers, and anyone working with data-driven applications. MySQL, one of the most popular relational database management systems (RDBMS), provides a wide range of commands to manage databases efficiently.

This comprehensive guide categorizes MySQL commands based on their usage, ensuring quick reference and easy learning. Whether you're a beginner or an experienced user, this list will be an invaluable resource.

---

1. Database Management Commands

Creating and Managing Databases

• Create a new database:

  CREATE DATABASE database_name;

• List all databases:

  SHOW DATABASES;

• Select a database to use:

  USE database_name;

• Drop (delete) a database:

  DROP DATABASE database_name;

• Rename a database (not natively supported, use backup & restore method):

  RENAME TABLE old_database.table_name TO new_database.table_name;

• Check the current database:

  SELECT DATABASE();

---

2. Table Management Commands

Creating, Modifying, and Deleting Tables

• Create a new table:

  CREATE TABLE table_name (
      column1 datatype constraints,
      column2 datatype constraints,
      ...
  );

• Show all tables in the current database:

  SHOW TABLES;

• Describe table structure:

  DESC table_name;

• Alter table structure (add, modify, drop columns):

  • Add a column:

  ALTER TABLE table_name ADD column_name datatype constraints;
  

  • Modify a column:

  ALTER TABLE table_name MODIFY column_name new_datatype;
  

  • Rename a column:

  ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;
  

  • Drop a column:

  ALTER TABLE table_name DROP COLUMN column_name;
  

• Rename a table:
  
  

  RENAME TABLE old_table_name TO new_table_name;

• Drop (delete) a table:

  DROP TABLE table_name;

---

3. Data Manipulation Commands (CRUD Operations)

Inserting Data

• Insert a single row:

  INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

• Insert multiple rows:

  INSERT INTO table_name (column1, column2) VALUES 
  ('value1', 'value2'),
  ('value3', 'value4');

• Insert data while ignoring duplicates:

  INSERT IGNORE INTO table_name (column1, column2) VALUES ('value1', 'value2');

• Insert data with auto-increment reset:

  INSERT INTO table_name VALUES (NULL, 'value2');

Reading (Retrieving) Data

• Retrieve all records from a table:

  SELECT * FROM table_name;

• Retrieve specific columns:

  SELECT column1, column2 FROM table_name;

• Retrieve unique records:

  SELECT DISTINCT column_name FROM table_name;

• Filter results with WHERE clause:

  SELECT * FROM table_name WHERE column1 = 'value';

• Sort results using ORDER BY:

  SELECT * FROM table_name ORDER BY column_name ASC;  -- Ascending
  SELECT * FROM table_name ORDER BY column_name DESC; -- Descending

• Limit the number of results:

  SELECT * FROM table_name LIMIT 10;

• Search for patterns using LIKE:

  SELECT * FROM table_name WHERE column_name LIKE '%value%';

• Search for values within a range:

  SELECT * FROM table_name WHERE column_name BETWEEN 10 AND 50;

Updating Data

• Update specific records:

  UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'condition';

• Update multiple columns:

  UPDATE table_name SET column1 = 'new_value', column2 = 'new_value2' WHERE column3 = 'condition';

Deleting Data

• Delete specific records:

  DELETE FROM table_name WHERE column_name = 'value';

• Delete all records from a table (without deleting the structure):

  DELETE FROM table_name;

• Truncate a table (faster deletion, resets auto-increment):

  TRUNCATE TABLE table_name;

---

4. Indexing and Optimization

Creating and Managing Indexes

• Create an index:

  CREATE INDEX index_name ON table_name (column_name);

• Create a unique index:

  CREATE UNIQUE INDEX index_name ON table_name (column_name);

• Show indexes in a table:

  SHOW INDEX FROM table_name;

• Drop an index:

  DROP INDEX index_name ON table_name;

---

5. Joins and Relationships

Types of Joins

• Inner Join:

  SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

• Left Join:

  SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;

• Right Join:

  SELECT t1.*, t2.* FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;

• Full Outer Join (not natively supported in MySQL, use UNION):

  SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  UNION
  SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

---

6. User and Privilege Management

• Create a new user:

  CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• Grant privileges to a user:

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

• Show user privileges:

  SHOW GRANTS FOR 'username'@'host';

• Revoke user privileges:

  REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

• Delete a user:

  DROP USER 'username'@'host';

---

7. Backup and Restore

• Backup a database:

  mysqldump -u root -p database_name > backup.sql

• Restore a database:

  mysql -u root -p database_name < backup.sql

• Backup all databases:

  mysqldump -u root -p --all-databases > all_databases_backup.sql

---

8. Performance Monitoring

• Show running processes:

  SHOW PROCESSLIST;

• Show open connections:

  SHOW STATUS WHERE variable_name = 'Threads_connected';

• Optimize a table:

  OPTIMIZE TABLE table_name;

• Check table status:

  CHECK TABLE table_name;

---

This comprehensive MySQL guide covers everything from database management to advanced optimization. Whether you're setting up a new database, performing queries, or managing users, these commands will help you efficiently work with MySQL. 🚀