25 MySQL Commands: The Ultimate MySQL Cheat Sheet

To get started with MySQL, you need to know a few basic commands. This article will go over the most important MySQL commands that you need to know — all in one giant MySQL cheat sheet. This will range from connecting to MySQL to creating and updating data. The more commands you know, the more you’ll be able to navigate the program when you need to.

What types of MySQL commands are there?

There are three main types of MySQL commands: DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language).

DDL commands

DDL commands are used to define the structure of a database. These commands include CREATE, ALTER, and DROP.

• CREATE. This command is used to create a new database or table.
• ALTER. This command is used to modify an existing database or table.
• DROP. This command is used to delete a database or table.

DML commands

DML commands are used to manipulate data in a database. These commands include SELECT, INSERT, UPDATE, and DELETE.

• SELECT. This command is used to select data from a database.
• INSERT. This command is used to insert data into a database.
• UPDATE. This command is used to update data in a database.
• DELETE. This command is used to delete data from a database.

DCL commands

DCL commands are used to control access to data in a database. These commands include GRANT and REVOKE.

• GRANT. This command is used to give users permissions to access data in a database.
• REVOKE. This command is used to remove permissions from users to access data in a database.

Together, all these commands form the basis of database administration and structure.

MySQL cheat sheet: the most important MySQL commands

Now that you know the different types of MySQL commands, let’s take a look at the most important ones based on how they’re used. We’re going to split them up into database management commands, table/data commands, administrative functions, and user functions. Although they are still coded as DDL, DML, and DCL commands, these categories more appropriately reflect what they do.

Database management commands

Database management commands are where you start learning MySQL. This controls how databases are created, modified, and dropped. It should be noted you can’t rename databases. You can just create new databases, import the information, and delete the old database.

1. SHOW DATABASES

Shows all databases on the server. You’ll need to use this if you’re looking for a specific database in the system or if you’re acclimating yourself to a new system.

2. CREATE DATABASE <database_name>

Creates a new database on the server. You can also do this through the MySQL Workbench system by going to File -> Create Schema. Workbench is an administrative GUI for MySQL.

3. DROP DATABASE <database_name>

Deletes a database from the server. You should be very careful when you do this because you can lose data if you haven’t been backing up your system.

4. USE <database_name>

Switches to a specific database on the server. You will usually use this command right after you’ve logged in or right after you’ve created a new database. When connecting to MySQL through PHP or another language, you will also need to select a database before you start to use it.

Table and data management

At the heart of MySQL is table and data management. This is where you’ll spend the bulk of your time. To view data, you will SELECT. To delete data, you will DELETE. And to modify data, you will UPDATE. To do any of these, you will also need the appropriate user permissions (which is discussed in the user section).

5. SHOW TABLES

Shows all tables in the current database. You can use this to quickly review what tables have been entered into the database.

6. SELECT * FROM <table_name>

Retrieves all rows from a table. The query results are given in a spreadsheet-like format that will show you the rows and columns that include all the information.

7. WHERE <column> = <value>

Returns rows where a column matches a given value. Often, you’ll use a primary key or even a foreign key, a unique identifier that is used to pull data more effectively.

8. ORDER BY <column> ASC/DESC

Sorts the results of a SELECT statement in a given order, either ascending or descending.

9. LIMIT <number_of_rows>

Returns only the first number of rows from the results of a SELECT statement. You can also start at a certain query result, so you could start at row 10 and then continue for another 20 rows. This is often used for pagination.

10. CREATE TABLE <table_name> (<column1> <data_type>, <column2> <data_type>)

Creates a new table with the given columns and data types. Learning the data types that correspond with SQL commands can take some time. Just know that there are a lot of different data types available, including INT, CHAR, VARCHAR, and BLOB.

11. ALTER TABLE <table_name> ADD <column> <data_type>

Adds a new column to an existing table. You can alter an existing table to add columns, drop columns, or just change columns. But be mindful of what this might do to your data.

12. ALTER TABLE <table_name> DROP <column>

Drops a column from an existing table. This cannot be rolled back if you didn’t previously save your data, so be cautious about using the ALTER TABLE function on a database table.

13. ALTER TABLE <table_name> RENAME <old_column> TO <new_column>

Renames a column in an existing table. This is one of the easiest ways to change a table that already exists.

14. TRUNCATE TABLE <table_name>

Deletes all the data from a table but retains the table structure. Users will often truncate tables when they’re trying to remove the result set so they can continue building their database from scratch.

15. CREATE INDEX <index_name> ON <table_name>(<column1>)

Creates an index on a given column in a table. If you didn’t create an index when you first built the database table, you might need to do this after the fact. Or you might want to select a different index entirely.

16. DROP INDEX <index_name> ON <table_name>

Drops an index from a table. You can always drop an index but it’s not necessarily a good idea unless you know what effect that will have on the database. Don’t drop a primary key from your MySQL database unless you have a very good reason for doing so.

17. UPDATE <table_name> SET <column_name> = <value>

Updates rows and columns in a given table. Sets the value of a column. Often used with WHERE, which would be used to select which rows are affected.

18. DELETE FROM <table_name>

Deletes records from a table. Used with the WHERE selection. Deletes entire rows (to delete a column entry, you would set the value to null).

Administrative functions

Apart from the traditional Structured Query Language commands, MySQL also has some important administrative commands. These commands are run on the MySQL server and have to do with connecting to and administering the server.

19. CONNECT <host> <username>

Connects to a MySQL server on the given hostname and username. This is how you connect directly to a MySQL server if you need to, especially if it is not on your machine.

20. SHOW PROCESSLIST

Shows all active queries on the server. This will show you what you need to know if the system is running sluggishly.

21. KILL <query_id>

Kills a specific active query identified by its unique ID. You would do this if a query was hanging for some reason, such as getting stuck in a loop.

User management

MySQL user management is more important than it might seem. You need to manage your users appropriately with at least one user per database to keep the system secure. It’s bad practice to have one user that can access all databases, besides the root user.

22. FLUSH PRIVILEGES

Flushes the privileges cache, reloading all privilege tables. It’s a good idea to do this every time you’ve granted new privileges or revoked old privileges.

23. GRANT <privilege> ON <database_name> TO <username>

Grants a given privilege to a user on a specific database. You can give the user the right to SELECT, UPDATE, and DELETE on a database. These are roughly like read, write, and execute permissions for files.

24. REVOKE <privilege> ON <database_name> FROM <username>

Revokes a given privilege from a user on a specific database. As an example, you might revoke the SELECT function so that a user cannot read a database, or you might revoke the DELETE function so that a user can read the database but not delete it.

25. SET PASSWORD FOR <username>=<password>

Sets the password for a user account. You should always use the user SET PASSWORD function so that the password isn’t stored in plain text. Otherwise, you can use the “UPDATE mysql.user” function, but you will need to specify “PASSWORD(‘password’)” rather than simply setting the password to plain text.

Tips for using MySQL commands

Those are some of the most basic and the most important MySQL commands. How can you go further? By knowing where to look when you’re stuck:

• Use the help system to learn more about a particular command.
• Try running SELECT * FROM <table_name> to see the structure of the table.
• Use EXPLAIN <query_id> to see how MySQL is planning to run your query.
• Use SHOW VARIABLES to see a list of all MySQL variables and their current values.
• Use mysqldump to export your data into a text file for backup purposes.
• Don’t forget that MySQL itself isn’t case sensitive, but the values that you’re using and searching for are. So, ‘CREATE TABLE‘ and ‘create table‘ are both the same command, but CREATE TABLE ‘users’ and CREATE TABLE ‘USERS’ are not.

You can usually find more information through MySQL documentation or through the extremely active MySQL community.

Going beyond the MySQL cheat sheet

How do you learn MySQL commands? It’s all about practice. No one starts out by memorizing these commands. They start out by developing applications and finding a use for each of these commands. The only way you’ll remember it is through hands-on experience, so start developing your next MySQL application today and develop deeper insights with MySQL Courses. And if you just need to brush up, check out some of the most common MySQL interview questions.