How To List MySQL Databases (Step-by-Step Code Tutorial)

Present Databases Contained in the MySQL Server

Now that you simply’re logged in, you possibly can record MySQL databases current within the server by executing the SHOW DATABASES command:

SHOW DATABASES;

In return, you get all of the databases current within the storage:

Out of the six databases returned, information_schema and performance_schema are the default databases which are robotically generated whenever you set up MySQL.

The information_schema database is a non-modifiable database that shops all the data associated to databases and different objects (views, person privileges, tables, constraints, and many others.) saved within the MySQL server.

Filtering Outcomes of the Database Output

Beforehand, you returned all the databases on the MySQL server with SHOW DATABASES, however you usually need to filter the database output, primarily when there are a lot of databases on the server.

The LIKE clause filters the results of SHOW DATABASE primarily based on a specified sample. Right here’s the final syntax:

SHOW DATABASES LIKE '';

It should be a string representing the sample you need to match. The string should finish with the proportion image, %, which denotes a number of characters.

For instance, if you wish to show simply the databases whose names begin with the letter w, you accomplish that by working the next:

SHOW DATABASES LIKE 'w%';

Right here’s the filtered end result:

Utilizing Info Schema to Question Desk Metadata

Earlier, you noticed how the information_schema database shops all the data associated to databases, tables, and different objects within the MySQL server surroundings.

The information_schema database makes use of the schemata desk to retailer details about all databases. For database filtering, you possibly can carry out a posh search to question the schema desk for particular databases.

For instance, if you need databases whose names begin with both “samp” or “phrase,” you possibly can mix a number of different clauses to make a posh question:

SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'samp%' OR schema_name LIKE 'phrase%';

Right here’s the end result:

As well as, you’ve gotten the tables desk from the information_schema database, which comprises details about all tables. Equally, you possibly can carry out a question to retrieve solely the tables that match a specified sample.

For instance, the next question returns the schema data of solely the WordPress tables — solely the tables whose names begin with “wp_”:

SELECT * FROM information_schema.tables WHERE table_name LIKE 'wp_%';

Right here’s the end result:

Different tables present in information_schema embrace columns, constraints, table_constraints, check_constraints, and referential_constraints.

Widespread Points and Finest Practices

One of the crucial widespread causes of errors when executing SQL is the failure to make use of a semicolon on the finish of statements.

One other is utilizing an invalid SQL syntax or an incorrectly spelled desk/column title. To keep away from this, cross-check the desk or column title to make sure it’s spelled appropriately. Make sure to cross-check your syntax as properly.

Listed below are another greatest practices to remember.

Use Uppercase for SQL Key phrases

When writing SQL code, all the time use uppercase for SQL key phrases and lowercase for desk names and column names. This makes your code extra readable and fewer vulnerable to errors.

So, as a substitute of this:

choose * from information_schema.tables the place table_name like 'wp_%';

Do that:

SELECT * FROM information_schema.tables WHERE table_name LIKE 'wp_%';

Keep away from Utilizing SELECT *

Keep away from utilizing SELECT * in your SQL queries. Your request is unclear as a result of you possibly can’t all the time know what it’s going to return. As an alternative, specify the columns you need to choose from the desk.

So as a substitute of this:

SELECT * EXCEPT(cellphone) FROM customers.profile

Do that:

SELECT title,
    dob,
    deal with,
    nation,
    deal with,
FROM person.profile

Indent Your Code

Lastly, yet one more tip to make discovering errors simpler is to indent your code. It makes it extra readable!

Database Managers

Alternatively, you possibly can select to handle your databases with out writing SQL by means of using a database supervisor. This permits customers entry to database administration capabilities while not having to put in writing SQL queries. This software program connects to a MySQL server and offers a person interface to show the database capabilities. As soon as related, the UI will present all databases on the server. The appear and feel differ throughout administration instruments, however the course of is analogous.

A number of instruments can be found to select from, together with phpMyAdmin and Adminer, each of that are accessible by means of DevKinsta. The default administration device for DevKinsta is Adminer, as it’s light-weight, simple, and quick, but phpMyAdmin can be accessed with ease.

Abstract

As a server administrator, you want to have the ability to effectively and precisely retrieve particulars in regards to the databases in your MySQL server. The talents to see which databases are on the server, view particular tables and the data from inside them, and entry details about person roles and privileges are all essential duties. Happily, utilizing SQL out of your command line could make this all a breeze.

When your database administration must stretch past querying tables, Kinsta may also help. Study extra about our scalable database hosting options right now!