MySQL Show/List Databases

The MySQL, SHOW DATABASES is used to list the databases that are within the MySQL environment. It prints all the databases including the default system databases. It depends on the user whether they want to print all the databases or some specific databases. We can use ‘LIKE‘, and ‘WHERE‘ clauses to specify the databases that we want to print. There are 4 default system databases in the MySQL environment. Let’s study them one by one in detail:

information_schema:

• The information schema database is responsible for the metadata of the MySQL server and its databases, tables, columns, and other objects.
• It gives rise to information resources that are tables and columns, names, data types, character sets and privileges, etc.
  ,
• It is the most popular among database administrators and developers stored in the MySQL database so that they may know the information related to the structure and configuration of their servers.
• It is not intended to store the information; it is made to maneuver data around the database schema which is the skeleton of all the procedures and programming in the database.

mysql:

• MySQL is most useful to keep system-related data (e.g. user account details, user privileges, and metadata information about the administration).
• Likewise, it may incorporate 5 tables to wit: user, db, tables_priv, columns_priv, and host all hold the information about users, databases, and their permissions.
• Administrators, in turn, employ this database to perform user account management, password landing, and configuration of authentication settings.
• The core functionality of the project is to have the database created with MYSQL Database. We’ll manage user authentication and authorization with this database.

performance_schema:

• The performance_ schema database is the source of information related to the performance of the MySQL server’s operation and resource uses.
• It grabs these data and aggregates them into one place, covering server events, mutexes, file I/O, table vs index access patterns, and so on.
• The Database administrators and developers will use the performance_schema to monitor and analyze the MySQL server performance and detect the point of slowdown to the maximum efficiency of the queries and the configuration.
• It provides the analysis of [the] system, at the resource usage level, the periods of query execution times, and other performance metrics (at both server and session level).

sys:

• sys schema is a group of stored procedures, functions, and views that are not only critical in providing insights into the MySQL server’s performance but also provide a source of information as far as the server’s configuration is concerned.
• It is based on the structures, performance_schema and information_schema through which the statisticist can have a simplified stand with improved views for performance analysis and monitoring.
• Programming the sys schema with tools like sys.statement_analysis, sys.schema_statistics, sys.innodb_*, and so on (much more).
• It enables the convenient retrieval of issues related to, the metrics and guidelines on the tuning and avoiding of over-utilization resources by MySQL server.

Syntax:

SHOW {DATABASES | SCHEMAS}

[LIKE ‘pattern’ | WHERE expr];

Explanation:

In the above syntax, we are writing a query to list the databases. It is not compulsory to add the ‘LIKE‘ or the ‘WHERE‘ clause to reference the database. LIKE or WHERE clause is used when we want to list particular databases. Otherwise, if we do not use the LIKE or WHERE clause, all the databases will get listed.

MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.

MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how to list all the databases in MySQL along with some examples.