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 to view 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 can list the databases that 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!