This guide explains how to list databases and tables using MySQL or MariaDB using simple commands.
Managing your databases from the Linux shell is quick and efficient compared to some of the bloated database management tools available.
Here’s a quick primer on seeing what databases and tables you’ve got set up on your MySQL or MariaDB server.
Listing Databases
Once you’ve logged in to your database, simply enter the following to list your databases:
SHOW DATABASES;
You’ll get a list of all of the databases set up on the system. Easy!
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | mydatabase | +--------------------+
Note that:
- Some databases: mysql, information_schema, performance_schema, are present in the default MySQL/MariaDB installation and are used by the database software itself – don’t mess with them!
- Make sure you end all MYSQL commands with “;” (not including speech marks). Otherwise, they won’t be executed
Filtering the list of databases
If you want to only show databases matching a certain name, use the LIKE statement. This example shows only databases starting with my:
SHOW DATABASES LIKE 'my%'; +--------------------+ | Database | +--------------------+ | mydatabase | +--------------------+
Selecting a Database
Once you’ve identified the database you want to use from the list, use it:
USE mydatabase;
Listing Tables
Unsurprisingly, listing the tables in a database is just as simple. Once you’ve selected a database for use:
SHOW TABLES;
And you’ll receive a list of tables ready to query.
+-------------------------+ | Tables_in_classicmodels | +-------------------------+ | users | | posts | | comments | +-------------------------+
