How to query mysql database

In mysql, you can use the "SHOW DATABASES;" statement to list all databases that the current user can view, or you can use the "SHOW DATABASES LIKE 'string';" statement to list the databases containing the specified string. .

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In MySQL, you can use the SHOW DATABASES statement to view or display databases within the scope of the current user's permissions.

• If you want toview all databases, you can directly state:

SHOW DATABASES;

This will list what the current user can view All databases, for example:

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

It can be found that there are 6 databases in the above list, which are automatically created by the system when MySQL is installed.

• You can also use the SHOW DATABASES statement with the LIKE clause, so that you canlist the databasesthat contain the specified string.

SHOW DATABASES [LIKE '字符串'];

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

  String: Specify the string to match. "String" can be a complete string, or it can contain wildcard characters.
  

  • supports percent sign "%" and underscore "_" wildcard characters.
    

"%" is the most commonly used wildcard character in MySQL. It can represent a string of any length. The length of the string can be 0. For example, a%b represents a string of any length starting with the letter a and ending with the letter b. This string can represent strings such as ab, acb, accb, accrb, etc.

"_" can only represent a single character, and the length of the character cannot be 0. For example, a_b can represent strings such as acb, adb, aub, etc.

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)

Notes and tips on using wildcards

The following are some notes on using wildcards:

• Pay attention to capitalization. MySQL is case-insensitive by default. If case-sensitive, data like "Tom" cannot be matched by "t%".

• Pay attention to the trailing spaces, which will interfere with wildcard matching. For example, "T%" will not match "Tom".

• Note NULL. The "%" wildcard can match any character, but cannot match NULL. In other words, "%" cannot match records with NULL values in the tb_students_info data table.

Here are some tips to remember when using wildcards.

• Don't overuse wildcards. If other operators can achieve the same purpose, you should use other operators. Because MySQL generally takes longer to process wildcards than other operators.

• After you decide to use wildcard characters, do not use them at the beginning of a string unless absolutely necessary. Placing the wildcard at the beginning of the search pattern will result in the slowest search.

• Pay careful attention to the placement of wildcard characters. If misplaced, the desired data may not be returned.

[Related recommendations:mysql video tutorial]

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