When we work with a MySQL server, it is an everyday activity to view or list the databases, display the database table. The display of the user account information and privileges residing on the server.
Displaying MySQL Databases:
Open the MySQL Command Line Client that appears with a prompt for mysql>. First, use the password you created during the installation of MySQL to log into the MySQL database server. You are now linked to the host of the MySQL server, where all SQL statements can be executed. Finally, to list/show databases, run the SHOW Databases command.
The most popular way to get a list of MySQL databases is to connect to a MySQL server using the MySQL client and execute the Display DATABASES order.
Open the MySQL server using the following command and, when prompted, enter your MySQL user password:
mysql -u user -p
MySQL also allows one to list databases with another instruction, which is a Display SCHEMAS statement. This command is a Display DATABASES synonym and gives the same result.
- Complete MySQL dashboard with Grafana & Prometheus
- How To Setup OpenLDAP Server on Debian 10
- How To Create a Database on InfluxDB 1.7 & 2.0
Displaying all MySQL databases:
You would need to log in as a user who can access all the databases to list all the databases on the MySQL server by default. The MySQL root user, or set a global privilege for Display DATABASES.
Log in to the root MySQL user:
mysql -u user -p
Run the DATABASES Display command:
On the MySQL server, you can see a list of all the databases.
Database list using pattern matching:
The Display Databases command in MySQL also offers an alternative that allows us to filter the returned database by matching the LIKE and WHERE clauses with different patterns. The LIKE clause lists the name of the database that fits the pattern specified. More versatility is provided by the WHERE clause to list the SQL statement database that fits the specified condition.
The syntax for using pattern matching with the Display Databases command is as follows:
LIKE pattern Display DATABASES;
DISPLAY DATABASES WHERE the phrase is;
You can understand this with the following example where the percentage sign assumes zero, one, or several characters:
LIKE " percent schema" Display DATABASES;
The LIKE clause is often not sufficient; we can then conduct a more detailed search to query the information schema’s database information from the schema table. In MySQL, the information schema is a database of information to get the results using the command Display DATABASES.
FROM information schema.schema; SELECT schema name
With the Display DATABASES instruction, you can check how the WHERE clause is used. This statement returns the database whose name for the schema begins with ‘s’::
SELECT schema name FROM schema. Schema details WHERE schema name LIKE percentage;
Database list via command line:
You can use either the MySQL command with the -e option that stands for executing or the mysqlshow that shows database and table information. It is used to get a list of databases without logging in to the MySQL shell.
This is particularly useful when you want to work with shell scripts for your MySQL databases.
To present a list of all databases, run the following command on your terminal:
mysql -u user -p -e 'database display;'
Here, you have learned how to display or list tables in the MySQL database and use pattern matching to filter performance.