Detailed explanation of mysql's multi-table query

First, in order to facilitate the explanation of the problem, create two tables emp (employee information) and dept (employee department information). The data is as follows:

In the previous article, we shared the single Table query method, but in actual applications, the data we want to query is likely not in the same table, but comes from different tables. If a multi-table query does not add any conditions, the result obtained is called a Cartesian product. For example, find the employee name, employee salary, and department name.

It can be found that the result is like this, select the first record from the first table, combine it with all the records in the second table, and then Then take the second record from the first table and combine it with all the records in the second table. This result is meaningless. What we need is the record with emp.deptno = dept.deptno field.

Self-join

Self-join refers to joining the query in the same table

Display the superior of employee FORD Leader’s name

Subquery

Subquery refers to a select statement embedded in other sql statements, also called nesting Inquire.

Single row subquery: The query result of the subquery has only one row

Displays employees in the same department as SMITH

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

Query the names, jobs, salaries, and departments of officials who have the same job as the 10 best departments number, but does not include No. 10’s own information

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

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

Multi-column subquery: The query returns a subquery statement of multiple columns of data

Query employees who have the same department and position as SMITH, excluding SMITH himself

Use a subquery in the from clause

Display employee information that is higher than the average salary of your department

Find The ename, job, sal of the person with the highest salary in each department

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

There are two methods that can be used here, but it has been verified that when there is a lot of data, the efficiency of the from subquery is higher than that of the 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 the empty table is the same as The structure of table tt is the same; 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 multiple selects To execute the results, you can use union and union all set operators

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

2. union all is similar to union, but will not automatically remove duplicates
For example: similar to or

Foreign key

Foreign key defines the relationship between the master table and the slave table. Foreign key constraints are mainly defined on the slave table. The master table must have a primary key or a unique key. When a foreign key is defined, the foreign key column data must exist in the main table column or be NULL.
For example: create a main table class, slave table stu

From the above figure, we can know that the primary key cannot be null. However, the foreign key can be null, and the data in the foreign key cannot exist but does not exist in the main table.

For more related questions, please visit the PHP Chinese website: mysql video tutorial

The above is the detailed content of Detailed explanation of mysql's multi-table query. For more information, please follow other related articles on the PHP Chinese website!