How to view the database in mysql?

In mysql, you can use the "SHOW DATABASES" statement to view the database. This statement can view or display the databases within the scope of the current user's permissions; the syntax format is "SHOW DATABASES [LIKE 'database name'];".

(Recommended tutorial: mysql video tutorial)

The database can be regarded as a container that specifically stores data objects. , each database has a unique name, and the names of the databases have practical meanings, so that you can clearly see what data each database is used to store. There are system database and custom database in the MySQL database. The system database is the database that comes with the system after installing MySQL, and the custom database is the database created by user definition.

In MySQL, you can use the SHOW DATABASES statement to view or display databases within the scope of the current user's permissions. The syntax format for viewing the database is:

SHOW DATABASES [LIKE '数据库名'];

The syntax description is as follows:

• The LIKE clause is optional and is used to match the specified database name. The LIKE clause can match partially or completely.

• The database name is surrounded by single quotes ' '.

Example 1: View all databases

List all databases that the current user can view:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 row in set (0.22 sec)

can be found, There are 6 databases in the above list, which are automatically created by the system when MySQL is installed. Their respective functions are as follows:

• information_schema: mainly stores some database object information in the system , such as user table information, column information, permission information, character set information, partition information, etc.

• mysql: The core database of MySQL, similar to the master table in SQL Server, is mainly responsible for storing database users, user access permissions and other control and management information that MySQL needs to use. Commonly used ones include modifying the root user password in the user table of the mysql database.

• performance_schema: Mainly used to collect database server performance parameters.

• sakila: A sample database provided by MySQL. The database has a total of 16 tables. These data tables are relatively common. When designing the database, you can refer to these sample data tables. Quickly complete required datasheets.

• sys: After MySQL 5.7 is installed, there will be an additional sys database. The sys database mainly provides some views, and the data comes from performance_schema, mainly to make it easier for developers and users to view performance issues.

• world: The world database is a database automatically created by MySQL. The database only includes 3 data tables, which respectively store the city, country, and language used by the country.

Example 2: Create and view the database

First create a database named test_db:

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.12 sec)

Then use SHOW The DATABASES statement displays all database names within the permission scope, as shown below:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| world              |
+--------------------+
7 row in set (0.22 sec)

You see, the database just created has been displayed.

Example 3: Using LIKE clause

First create three databases, named test_db, db_test, db_test_db.

1) Use the LIKE clause to view the database that exactly matches test_db:

mysql> SHOW DATABASES LIKE 'test_db';
+--------------------+
| Database (test_db) |
+--------------------+
| test_db            |
+--------------------+
1 row in set (0.03 sec)

2) Use the LIKE clause to view the database whose name contains test:

mysql> SHOW DATABASES LIKE '%test%';
+--------------------+
| Database (%test%)  |
+--------------------+
| db_test            |
+--------------------+
| db_test_db         |
+--------------------+
| test_db            |
+--------------------+
3 row in set (0.03 sec)

3) Use the LIKE clause to view the databases whose names begin with db:

mysql> SHOW DATABASES LIKE 'db%';
+----------------+
| Database (db%) |
+----------------+
| db_test        |
+----------------+
| db_test_db     |
+----------------+
2 row in set (0.03 sec)

4) Use the LIKE clause to view the databases whose names end with db:

mysql> SHOW DATABASES LIKE '%db';
+----------------+
| Database (%db) |
+----------------+
| db_test_db     |
+----------------+
| test_db        |
+----------------+
2 row in set (0.03 sec)

The above is the detailed content of How to view the database in mysql?. For more information, please follow other related articles on the PHP Chinese website!