How to List MySQL Databases, and 30+ other useful MySQL commands

Explore essential MySQL commands for effective database management, including creating, querying, and updating tables, managing users, and working with stored procedures, views, and triggers.

In this article

MySQL is a widely used open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. SQL is a powerful and flexible language designed specifically for managing relational databases. MySQL is popular for its ease of use, high performance, and scalability, which makes it an ideal choice for various applications, including web applications, data warehousing, and more.

To interact with a MySQL database, you can use various client applications, such as the command-line tool mysql, a graphical user interface (GUI) like HeidiSQL, TablePlus, Querious, or even libraries in programming languages such as PHP, Python, and Java. Once connected to a MySQL server, you can execute SQL commands to create, retrieve, update, or delete data, as well as manage database structures like tables and indexes.

In this guide, we will cover some useful MySQL commands that can help you manage your databases effectively. We will provide a brief explanation of each command, its usage, and any required privileges. The commands will be presented in code highlighter boxes for clarity.

List MySQL Databases

Display all databases present on the MySQL server by using this command. You must possess the SHOW DATABASES privilege to execute it.

SHOW DATABASES;

List MySQL Users

MySQL does not provide a direct SQL command to list all users. However, you can list all MySQL users by querying the mysql.user table. This table stores information about all MySQL users and their privileges.

To list all MySQL users, execute the following command:

SELECT User, Host FROM mysql.user;

This will return a list of users and the hosts they are associated with. Note that you need the SELECT privilege on the mysql.user table to execute this query.

Create a New Database

Create a new database with this command. To execute it, you need the CREATE DATABASE privilege.

CREATE DATABASE database_name;

Select a Database

Before you can execute commands towards a certain database, you first need to "connect" to it. This is done with the following command:

USE database_name;

Show Database Tables

List all the tables in the active database by running this command. The SHOW TABLES privilege is necessary for execution.

SHOW TABLES;

Create a New Table

Construct a new table with specified columns and data types with this command. The CREATE TABLE privilege is required for execution.

CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...);

Show (describe) Table Structure

Show the structure of a given table using this command. It requires the DESCRIBE privilege to execute.

DESCRIBE table_name;

Delete (drop) a database table

Remove a specified table and all its contents with this command. The DROP TABLE privilege is needed to execute it.

DROP TABLE table_name;

Insert Data into a Table

Add a new row of data to a specified table using this command. The INSERT privilege is required for execution.

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

Get Data from a Database Table

Fetch data from a specified table using this command. You can apply clauses like WHERE, ORDER BY, and LIMIT to filter, sort, and limit results. The SELECT privilege is needed to execute it.

SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name LIMIT number;

Update Data in a MySQL Database Table

Modify existing data in a specified table using this command. Execution requires the UPDATE privilege.

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Removing Data from a Table

Erase rows of data from a specified table using this command. The DELETE privilege is necessary for execution.

DELETE FROM table_name WHERE condition;

Creating an Index

Generate an index on one or more columns of a specified table with this command. Indexes can boost query performance because MySQL only needs to search through the data of that indexed column (instead of all the content of a table). Primary key columns (typically the id column) are indexed by default.

You need the CREATE INDEX privilege to execute it.

CREATE INDEX index_name ON table_name (column1, column2, ...);

Deleting an Index

Remove the specified index from a table using this command. The DROP INDEX privilege is required for execution.

DROP INDEX index_name ON table_name;

Create a MySQL User

Create a new MySQL user with a specific password using this command. The CREATE USER privilege is needed to execute it.

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

Granting Privileges to a MySQL User

Assign specific privileges to a MySQL user for a database, table, or specific columns using this command. The GRANT privilege is necessary for execution.

GRANT privilege1, privilege2, ... ON database_name.table_name TO 'username'@'host';

Displaying a MySQL User Privileges

Show the privileges assigned to a MySQL user by running this command. The SHOW GRANTS privilege is required to execute it.

SHOW GRANTS FOR 'username'@'host';

Revoking Privileges from a MySQL User

Remove specific privileges from a MySQL user using this command. Execution requires the REVOKE privilege.

REVOKE privilege1, privilege2, ... ON database_name.table_name FROM 'username'@'host';

Deleting a MySQL User

Erase a specified MySQL user using this command. The DROP USER privilege is necessary for execution.

DROP USER 'username'@'host';

Create a MySQL View

Create a view based on a SELECT statement's result using this command. Views are virtual tables that can simplify complex queries or provide limited access to data. The CREATE VIEW privilege is required to execute it.

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

List MySQL Views

List all views in the active database using this command. Execution requires the SHOW TABLES privilege.

SHOW TABLES WHERE table_type = 'VIEW';

Removing a View

Delete a specified view using this command. The DROP VIEW privilege is needed to execute it.

DROP VIEW view_name;

Make a MySQL Stored Procedure

Create a stored procedure that can be called multiple times to perform a specific action or calculation. The CREATE ROUTINE privilege is required to execute this command.

CREATE PROCEDURE procedure_name(IN parameter1 datatype1, OUT parameter2 datatype2, ...)
BEGIN
    -- SQL statements
END;

Calling a Stored Procedure

Invoke the specified stored procedure with provided input parameters using this command.

CALL procedure_name(parameter1, parameter2, ...);

Listing Stored Procedures

Show all stored procedures in the active database using this command. The SHOW ROUTINES privilege is necessary for execution.

SHOW ROUTINES WHERE routine_type = 'PROCEDURE';

Deleting a Stored Procedure

Remove a specified stored procedure with this command. You need the DROP ROUTINE privilege to execute it.

DROP PROCEDURE procedure_name;

Create a MySQL Trigger

Create a trigger that automatically executes when an event like INSERT, UPDATE, or DELETE occurs on a specified table. Triggers help maintain referential integrity or perform automatic actions based on data changes.

The CREATE TRIGGER privilege is required for execution.

CREATE TRIGGER trigger_name
    BEFORE/AFTER INSERT/UPDATE/DELETE
    ON table_name
    FOR EACH ROW
BEGIN
    -- SQL statements
END;

List MySQL Triggers

Display all triggers in the active database using this command. The SHOW TRIGGERS privilege is necessary for execution.

SHOW TRIGGERS;

Removing a MySQL Trigger

Delete a specified trigger with this command. The DROP TRIGGER privilege is needed to execute it.

DROP TRIGGER trigger_name;

Generating a MySQL Function

Create a user-defined function that can be used in SQL expressions to perform specific calculations or actions. The CREATE ROUTINE privilege is required for execution.

CREATE FUNCTION function_name(parameter1 datatype1, parameter2 datatype2, ...)
RETURNS datatype
BEGIN
    -- SQL statements
    RETURN value;
END;

List MySQL Functions

Show all user-defined functions in the active database using this command. The SHOW ROUTINES privilege is necessary for execution.

SHOW ROUTINES WHERE routine_type = 'FUNCTION';

Delete a MySQL Function

Remove a specified user-defined function with this command. You need the DROP ROUTINE privilege to execute it.

DROP FUNCTION function_name;

 

Updated