Data Query in Oracle Database tips Series

Data Query is one of the most important functions in database operations. Sometimes the performance of database queries is directly related to the database operation efficiency and database selection. I will not talk about the principle below, but I will summarize some of the tips that are easy to ignore. Maybe everyone is worried about it?

　First Tip: Use a connector to connect multiple fields.

　　

For example, in the employee basic information table, there are employee names, employee positions, birth dates, and so on. If the three fields in the current view are displayed in the same field, there is a delimiter in the middle. For example, I want to display the result as "Manager Victor was born in May 3, 1976 ". What should we do? In fact, this is relatively simple. We can use the connector to connect these fields in the Select query statement.

You can write a query statement as follows:

SELECT employee position | ''| employee name | 'derived FROM '| date of birth as employee information FROM employee basic information table;

You can use this statement to meet the preceding requirements. That is to say, in normal queries, we can use the | connector to connect some related fields. This is useful in the report view. For example, when I used to design a library management system, there were publishing houses and serial numbers in the basic information of books. However, when printing a report, you need to combine these fields into one field for printing. Therefore, you need to use this connector to connect these fields. Additionally, you can add descriptive text in the middle of the field to facilitate reading. For example, I added a space between the employee's position and employee's name, and added several annotated texts between the employee's name and date of birth. These functions seem relatively small, but can greatly improve the readability of the content. This is what we need to pay attention to in the database design process.

In short, the later connector can improve the readability and flexibility of our reports.

　Tip 2: Cancel duplicate rows.

For example, there is a basic employee information table in the personnel management system. This table may contain fields such as department, position, employee name, and ID card number. If the content is queried, duplicate rows may not exist. However, if I want to know which departments and positions have been set up in the company, and relevant personnel have been configured for these departments and positions. In this case, how can I query it?

If I directly query the Department table, I can know which departments and positions are set in the system. However, it is very likely that these departments or positions have no one due to personnel changes. Therefore, the information of all departments and positions found here cannot be ensured that there must be employees in this department or position. That is to say, this does not meet our above requirements.

If I want to query employee information directly from the employee information table, although the Department and position information can be queried, there must be employee information. However, at this time, the department and position information are displayed in duplicate rows. If the procurement department has a division of labor and cooperation, there may be a procurement team lead. At this time, there are three duplicate records in the queried department and position information.

Therefore, neither of the above two processing methods can fully meet the needs of enterprise users. In this case, we can actually use a DISTINCT function to eliminate the repeated rows queried.

For example, we can use select distinct department information, job information FROM employee basic information table. This query statement with the DISTINCT constraint can not only query information about all positions and departments with employees, but also filter out duplicate records to improve readability.

Therefore, this function is particularly useful in the database design process, especially in the use of query statements.

　Third tip: Use the WHERE statement frequently.

We all know that high database query efficiency is an important criterion for us to evaluate the database design. Undoubtedly, using the Where clause in database queries is one of the most important ways to improve the performance of database queries. Especially when designing a large table to query records that meet the conditions, the WHERE condition statement can be used to limit the query response speed.

For example, in the library management system, when someone wants to query the "CPA" counseling book, although the "CPA" is not in the category or name of the book ", it is also feasible to query all records first, and then check whether there is any relevant book information one by one. However, in this case, the system response speed will be very slow, because there are many records in it. On the other hand, the query results also seem to be a headache.

In fact, we only need to add some query parameters to the query and use the Where Condition Statement to limit them, which can speed up the database response and find the data that best meets user requirements.

In addition, I have also been familiar with some platform-based management software designed on Oracle databases. They can customize related reports. In report design, if you select "Big Table query" on the front-end design platform, the platform automatically applies the Where condition statement when generating the report, to improve the efficiency of the front-end system to query data from the database.

Therefore, I believe that in the design of the Oracle database system, the Where statement should be used frequently. Use Where statements to improve database query efficiency.

• Four pages in total:
• Previous Page
• 1
• 2
• 3
• 4
• Next Page