JIYIK CN >

As a database administrator, there are certain situations and circumstances where you have to search the entire database for the occurrence of a pattern or a string.

For example, how many employees use Gmail accounts in your office, or you want to know a certain age from the employee table to know their retirement date, etc.

We will explore a GUI-based approach to searching your entire database, including searching in MySQL Workbench and phpMyAdmin. However, you can also create SQL commands and procedures.

For this article, we'll focus on the GUI options for searching.

Method 1: Search the entire phpMyAdmin数据库

Here, we will see how to search all the tables in the database using phpMyAdmin. You can easily find the occurrences of the required string by following the given steps.

Create database and table

We created a persondatabase named and two tables named studentand .teacher

Fill the table and check the data

You can use the following code to insert data into each table.

#insert into student table
INSERT INTO student(ID, FIRST_NAME, LAST_NAME,GENDER, EMAIL)
VALUES
(1,'Shaajeel', 'Daniel', 'Male','shajeeld@gmail.com'),
(2,'Nayya', 'Preston', 'Female','npreston@yahoo.com'),
(3,'James', 'Robert', 'Male','james@yahoo.com'),
(4,'Jennifer', 'John', 'Female','jennifer09@hotmail.com'),
(5,'Sarah', 'Paul', 'Female','sarahpaul011@yahoo.com'),
(6,'Karen', 'Donald','Female','dkaren@gmail.com');

#insert into teacher table
INSERT INTO teacher(ID, FIRST_NAME, LAST_NAME,GENDER, EMAIL)
VALUES
(1,'Thomas', 'Christopher','Male','thomasC098@hotmail.com'),
(2,'Lisa', 'Mark', 'Female','lisamark@gmail.com'),
(3,'Anthony', 'Richard', 'Male','anthony044@yahoo.com'),
(4,'Matthew', 'Charles', 'Male','matthewcharles@gmail.com')
(5,'Kiren', 'Donald','Female','dkiren@gmail.com');

You can SELECTview the inserted data using the following query.

SELECT * FROM `teacher`;

SELECT * FROM `student`;

Search string

To search for a string,

• First, select the database (see Box 1).
• Click 搜索the tab and enter the string, pattern, or expression to search for (see box number 2).
• You can choose your search criteria whether you are looking for an exact match or something else; see red box number 3.

We are looking for an exact match as a substring for this tutorial. Select the table to search for red box number 4 and click GOthe button at the bottom right.

The following screenshot will show the count of matches found for each table. You can click 浏览the button to view the corresponding full record (row).

Method 2: Search the entireDatabase

If you use MySQL Workbench, you can use the graphical user interface to search the entire database for a specific string or pattern.

First, select all the tables you want to search (see red box 1). Remember, if you don't select the tables, the search will not be performed.

Click Database Menuand select Search Table Data( Database Menu -> Search Table Data). Since we are searching for an exact match of a substring, we selected CONTAINS(see red box number 3), but you can select as needed.

Now you can write the string or expression you want to search for (see red box number 4).

You can know the maximum number of matches for each table and the complete database. You can also specify whether you want to search all types of columns with a single click 开始搜索.

It will display the output including schema, table name, primary key, column name (where the match was found) and its value (see red box number 6).

in conclusion

After learning about two methods of searching the entire database, including searching through MySQL Workbench and phpMyAdmin, we concluded that the GUI option makes life easy for database administrators, database programmers, and architects. Although there are SQL queries behind the scenes, we can use the GUI option to operate more efficiently and faster.