Introduction
MySQL is a widely-used open-source relational database management system (RDBMS). It is known for its reliability, ease of use, and performance. Mastering MySQL commands is essential for database administration, querying, and management. This cheat sheet provides a quick reference to some of the most commonly used MySQL commands.
MySQL Database Commands Cheat Sheet
Here’s a handy cheat sheet of the most commonly used MySQL commands:
| Command | Description |
|---|---|
SHOW DATABASES; |
Lists all databases on the server. |
CREATE DATABASE db_name; |
Creates a new database. |
USE db_name; |
Selects a database to use. |
DROP DATABASE db_name; |
Deletes a database and all of its tables. |
SHOW TABLES; |
Lists all tables in the selected database. |
CREATE TABLE tbl_name (column_definitions); |
Creates a new table. |
DROP TABLE tbl_name; |
Deletes a table from the database. |
DESCRIBE tbl_name; |
Displays the structure of a table. |
ALTER TABLE tbl_name ADD column_name column_definition; |
Adds a new column to a table. |
ALTER TABLE tbl_name DROP COLUMN column_name; |
Deletes a column from a table. |
SELECT * FROM tbl_name; |
Selects all records from a table. |
SELECT column1, column2 FROM tbl_name WHERE condition; |
Selects specific columns and records that meet a condition. |
INSERT INTO tbl_name (column1, column2) VALUES (value1, value2); |
Inserts new data into a table. |
UPDATE tbl_name SET column1 = value1, column2 = value2 WHERE condition; |
Updates existing data in a table. |
DELETE FROM tbl_name WHERE condition; |
Deletes records from a table that meet a condition. |
CREATE INDEX index_name ON tbl_name (column_name); |
Creates an index on a table. |
DROP INDEX index_name ON tbl_name; |
Deletes an index from a table. |
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; |
Creates a new user. |
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host'; |
Grants all privileges to a user on a database. |
REVOKE ALL PRIVILEGES ON db_name.* FROM 'username'@'host'; |
Revokes all privileges from a user on a database. |
SHOW GRANTS FOR 'username'@'host'; |
Displays the granted privileges for a user. |
DROP USER 'username'@'host'; |
Deletes a user. |
SHOW PROCESSLIST; |
Shows the currently running threads in the server. |
KILL process_id; |
Terminates a connection thread. |
SHOW STATUS; |
Displays server status information. |
SHOW VARIABLES; |
Displays system variables. |
SHOW INDEX FROM tbl_name; |
Shows indexes for a table. |
FLUSH PRIVILEGES; |
Reloads the privileges from the grant tables in the mysql database. |
EXPLAIN SELECT * FROM tbl_name; |
Shows how MySQL executes a SELECT statement. |
BACKUP DATABASE db_name TO DISK = 'file_path'; |
Creates a backup of the database. (Note: This is a syntax example; actual MySQL does not support this directly.) |
RESTORE DATABASE db_name FROM DISK = 'file_path'; |
Restores a database from a backup. (Note: This is a syntax example; actual MySQL does not support this directly.) |
Explanation of Key Commands with Examples
SHOW DATABASES;
Description: Lists all databases on the server.
SHOW DATABASES;
Output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| myDatabase |
| mysql |
| performance_schema |
| sys |
+--------------------+
Explanation: This command lists all the databases present on the MySQL server.
CREATE DATABASE db_name;
Description: Creates a new database.
CREATE DATABASE myDatabase;
Output:
Query OK, 1 row affected (0.01 sec)
Explanation: This command creates a new database named myDatabase.
USE db_name;
Description: Selects a database to use.
USE myDatabase;
Output:
Database changed
Explanation: This command switches the current database context to myDatabase.
DROP DATABASE db_name;
Description: Deletes a database and all of its tables.
DROP DATABASE myDatabase;
Output:
Query OK, 0 rows affected (0.02 sec)
Explanation: This command deletes the database myDatabase along with all its tables and data.
SHOW TABLES;
Description: Lists all tables in the selected database.
SHOW TABLES;
Output:
+-------------------+
| Tables_in_myDatabase |
+-------------------+
| employees |
+-------------------+
Explanation: This command lists all the tables present in the currently selected database.
CREATE TABLE tbl_name (column_definitions);
Description: Creates a new table.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10, 2)
);
Output:
Query OK, 0 rows affected (0.05 sec)
Explanation: This command creates a new table named employees with columns id, name, position, and salary.
DROP TABLE tbl_name;
Description: Deletes a table from the database.
DROP TABLE employees;
Output:
Query OK, 0 rows affected (0.02 sec)
Explanation: This command deletes the employees table from the database.
DESCRIBE tbl_name;
Description: Displays the structure of a table.
DESCRIBE employees;
Output:
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(100)| YES | | NULL | |
| position | varchar(100)| YES | | NULL | |
| salary | decimal(10,2)| YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
Explanation: This command shows the structure of the employees table, including column names, types, and constraints.
ALTER TABLE tbl_name ADD column_name column_definition;
Description: Adds a new column to a table.
ALTER TABLE employees ADD birthdate DATE;
Output:
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Explanation: This command adds a new column birthdate of type DATE to the employees table.
ALTER TABLE tbl_name DROP COLUMN column_name;
Description: Deletes a column from a table.
ALTER TABLE employees DROP COLUMN birthdate;
Output:
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Explanation: This command removes the birthdate column from the employees table.
SELECT * FROM tbl_name;
Description: Selects all records from a table.
SELECT * FROM employees;
Output:
+----+----------+----------+--------+
| id | name | position | salary |
+----+----------+----------+--------+
| 1 | John Doe | Manager | 75000.00|
+----+----------+----------+--------+
Explanation: This command retrieves all records from the employees table.
SELECT column1, column2 FROM tbl_name WHERE condition;
Description: Selects specific columns and records that meet a condition.
SELECT name, position FROM employees WHERE salary > 50000;
Output:
+----------+----------+
| name | position |
+----------+----------+
| John Doe | Manager |
+----------+----------+
Explanation: This command retrieves the name and position of employees whose salary is greater than 50,000.
INSERT INTO tbl_name (column1, column2) VALUES (value1, value2);
Description: Inserts new data into a table.
INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Manager', 75000);
Output:
Query OK, 1 row affected (0.01 sec)
Explanation: This command inserts a new record into the employees table with the specified values.
UPDATE tbl_name SET column1 = value1, column2 = value2 WHERE condition;
Description: Updates existing data in a table.
UPDATE employees SET salary = 80000 WHERE name = 'John Doe';
Output:
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Explanation: This command updates the salary of John Doe to 80,000 in the employees table.
DELETE FROM tbl_name WHERE condition;
Description: Deletes records from a table that meet a condition.
DELETE FROM employees WHERE name = 'John Doe';
Output:
Query OK, 1 row affected (0.01 sec)
Explanation: This command deletes the record of John Doe from the employees table.
CREATE INDEX index_name ON tbl_name (column_name);
Description: Creates an index on a table.
CREATE INDEX idx_salary ON employees (salary);
Output:
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
Explanation: This command creates an index named idx_salary on the salary column of the employees table.
DROP INDEX index_name ON tbl_name;
Description: Deletes an index from a table.
DROP INDEX idx_salary ON employees;
Output:
Query OK, 0 rows affected (0.02 sec)
Explanation: This command deletes the index idx_salary from the employees table.
CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
Description: Creates a new user.
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Output:
Query OK, 0 rows affected (0.01 sec)
Explanation: This command creates a new user newuser with the specified password.
GRANT ALL PRIVILEGES ON db_name.* TO ‘username’@’host’;
Description: Grants all privileges to a user on a database.
GRANT ALL PRIVILEGES ON myDatabase.* TO 'newuser'@'localhost';
Output:
Query OK, 0 rows affected (0.01 sec)
Explanation: This command grants all privileges on myDatabase to the user newuser.
REVOKE ALL PRIVILEGES ON db_name.* FROM ‘username’@’host’;
Description: Revokes all privileges from a user on a database.
REVOKE ALL PRIVILEGES ON myDatabase.* FROM 'newuser'@'localhost';
Output:
Query OK, 0 rows affected (0.01 sec)
Explanation: This command revokes all privileges on myDatabase from the user newuser.
SHOW GRANTS FOR ‘username’@’host’;
Description: Displays the granted privileges for a user.
SHOW GRANTS FOR 'newuser'@'localhost';
Output:
+-------------------------------------------------------------------------------------------------+
| Grants for newuser@localhost |
+-------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'localhost' IDENTIFIED BY PASSWORD '*PASSWORD_HASH' |
| GRANT ALL PRIVILEGES ON `myDatabase`.* TO 'newuser'@'localhost' |
+-------------------------------------------------------------------------------------------------+
Explanation: This command displays all the privileges granted to the user newuser.
DROP USER ‘username’@’host’;
Description: Deletes a user.
DROP USER 'newuser'@'localhost';
Output:
Query OK, 0 rows affected (0.01 sec)
Explanation: This command deletes the user newuser.
SHOW PROCESSLIST;
Description: Shows the currently running threads in the server.
SHOW PROCESSLIST;
Output:
+----+------+-----------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-----------+---------+------+-------+------------------+
| 1 | root | localhost | myDatabase| Query | 0 | init | SHOW PROCESSLIST |
+----+------+-----------+-----------+---------+------+-------+------------------+
Explanation: This command shows the list of currently running processes on the MySQL server.
KILL process_id;
Description: Terminates a connection thread.
KILL 1;
Output:
Query OK, 0 rows affected (0.00 sec)
Explanation: This command terminates the connection thread with the specified process_id.
SHOW STATUS;
Description: Displays server status information.
SHOW STATUS;
Output:
+--------------------------+----------------+
| Variable_name | Value |
+--------------------------+----------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 100 |
| Bytes_sent | 200 |
| ... | ... |
+--------------------------+----------------+
Explanation: This command displays various status variables that provide information about the server’s performance.
SHOW VARIABLES;
Description: Displays system variables.
SHOW VARIABLES;
Output:
+--------------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| ... | ... |
+--------------------------------+-----------------------------+
Explanation: This command shows the current values of system variables.
SHOW INDEX FROM tbl_name;
Description: Shows indexes for a table.
SHOW INDEX FROM employees;
Output:
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| employees | 1 | idx_salary | 1 | salary | A | 1 | NULL | NULL | YES | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Explanation: This command shows all indexes for the employees table.
FLUSH PRIVILEGES;
Description: Reloads the privileges from the grant tables in the mysql database.
FLUSH PRIVILEGES;
Output:
Query OK, 0 rows affected (0.01 sec)
Explanation: This command reloads the privilege tables, making any changes to user privileges take effect.
EXPLAIN SELECT * FROM tbl_name;
Description: Shows how MySQL executes a SELECT statement.
EXPLAIN SELECT * FROM employees;
Output:
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
Explanation: This command provides information on how MySQL executes the specified SELECT statement.
BACKUP DATABASE db_name TO DISK = ‘file_path’;
Description: Creates a backup of the database. (Note: This is a syntax example; actual MySQL does not support this directly.)
BACKUP DATABASE myDatabase TO DISK = '/path/to/backup.sql';
Output:
(Note: This is a conceptual example. MySQL does not natively support this command directly.)
Explanation: In MySQL, to create a backup, you would typically use the mysqldump command-line utility rather than an SQL command within the database.
RESTORE DATABASE db_name FROM DISK = ‘file_path’;
Description: Restores a database from a backup. (Note: This is a syntax example; actual MySQL does not support this directly.)
RESTORE DATABASE myDatabase FROM DISK = '/path/to/backup.sql';
Output:
(Note: This is a conceptual example. MySQL does not natively support this command directly.)
Explanation: In MySQL, to restore a database, you would typically use the mysql command-line utility to import the data from a dump file.
Conclusion
Mastering MySQL commands is essential for efficiently managing databases and performing administrative tasks. This cheat sheet provides a quick reference to some of the most commonly used commands, helping you navigate and operate your MySQL databases more effectively. Keep this guide handy to make the most of MySQL. Happy coding!
By understanding and using these commands, you can simplify your database management processes, enhance your efficiency, and ensure your databases are well-maintained.