Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to query the database of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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

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

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

If you want to view all databases, you can simply say:

SHOW DATABASES

This lists all the databases that the current user can view, for example:

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

As you can see, there are six databases in the list above, all of which are automatically created by the system when MySQL is installed.

You can also use the SHOW DATABASES statement with a LIKE clause to list the database that contains the specified string.

SHOW DATABASES [LIKE 'string']

The LIKE clause is optional and matches the specified database name. LIKE clauses can be partially matched or completely matched.

String: specifies the string to be used to match. "string" can be a complete string or contain wildcards.

Wildcards with percent sign "%" and underscore "_" are supported.

"%" is the most commonly used wildcard character in MySQL. It can represent a string of any length, and the length of a string can be 0. For example, a% b represents a string of any length that begins with the letter an and ends with the letter b. This string can represent ab, acb, accb, accrb, and so on.

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

1) use the LIKE clause to view the database that exactly matches the 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 database whose name begins 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 database whose name ends in db:

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

Notes and techniques for using wildcards

Here are some considerations for using wildcards:

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

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

Note NULL. The "%" wildcard can match any character, but not NULL. That is, "%" does not match a record with a value of NULL 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 goal, you should use other operators. Because MySQL generally takes longer to process wildcards than other operators.

After determining the use of wildcards, do not use them at the beginning of a string unless absolutely necessary. Putting wildcards at the beginning of the search pattern is the slowest to search.

Pay close attention to the position of wildcards. If misplaced, the desired data may not be returned.

This is the end of "how to query the database of mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.