Shulou(Shulou.com)06/01 Report--

The following is followed by the author to understand the detailed steps of querying mysql multi-table. I believe you will benefit a lot after reading it. The text is not in the essence. I hope to query the detailed steps of mysql multi-table. This short article is what you want.

First, to make it easier to illustrate, create two tables, emp (employee information) and dept (employee department information), with the following data:

In the previous article, we shared the method of single-table query, but in practice, the data we are looking for is probably not in the same table, but from different tables. If the multi-table query does not add any conditions, the result is called Cartesian product. For example, find the name of the employee, the salary of the employee, and the name of the department.

It can be found that the result is that the first record is selected from the first table, combined with all the records in the second table, and then the second record is taken from the first table and combined with all the records in the second table. such a result is meaningless. What we need is a record in the emp.deptno = dept.deptno field.

Self-connection

Self-join refers to joining queries in the same table

Displays the name of the superior leader of the employee FORD

Subquery

Subqueries are select statements embedded in other sql statements, also known as nested queries.

Single-row subquery: the query result of a subquery has only one row

Show employees in the same department as SMITH

Multi-row subquery (in, all, any): a subquery that returns multiple records

Inquire about the name, job, salary and department number of the same officials as the jobs of 10 good departments, but do not include their own information on the 10th.

Displays the name, salary and department number of the employee whose salary is higher than that of all employees with department number 30

Displays the name, salary and department number of the employee whose salary is higher than that of any employee with department number 30

Multi-column subquery: a subquery statement in which a query returns multiple column data

Inquire about employees who have exactly the same department and position as SMITH, excluding SMITH himself

Using subqueries in the from clause

Display employee information that is higher than the average salary of one's own department

Find the ename, job, sal of the highest paid people in each department

Display information (department name, number, address) and number of personnel for each department

There are two methods that can be used, but it has been proved that when there is a lot of data, the efficiency of from subquery is higher than that of multi-table query.

Delete duplicate records in the table

It is known that there is duplicate data in a table tt

Create an empty table tmp_tt, the structure of which is the same as that of table tt; create table tmp_tt like tt

Distinct the tt table and import the data into the empty table; insert into tmp_tt select distinct * from tt

Delete the original table ttdrop table tt

Rename tmp_tt to ttalter table tmp_tt rename tt

Merge query

In practical applications, in order to merge the execution results of multiple select, you can use the union,union all collection operator

1. The union operator is used to obtain the union of two results, and then automatically remove duplicate lines to find people whose salary is greater than 2500 and position Manger

2. Union all is similar to union, but it does not remove weight automatically.

For example: similar to or

Foreign key

The foreign key defines the relationship between the master table and the slave table, the foreign key constraint is mainly defined on the slave table, and the master table must have a primary key or a unique key. When a foreign key is defined, it is required that the foreign key column data must exist in the main table column or be NULL.

For example: create a master table class, slave table stu

From the figure above, we can see that the primary key cannot be null, but the foreign key can be null, and there can be no foreign key data that does not exist in the primary table.

After reading this article about the detailed steps of querying mysql multiple tables, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.