MYSQL association relationship query method

MYSQL Association Relationship Query

Relationship query

First, let's review your original query:

SELECT u.*,  AS aid, , , , , ,  AS atel,  AS roleName
FROM tb_user u
INNER JOIN tb_address d ON  = d.user_id
LEFT JOIN tb_role_user ur ON ur.user_id = 
LEFT JOIN tb_role r ON  = ur.role_id;

This query does the following

1. fromtb_userSelect all columns in the table (u.*）。
2. Use inner connection (INNER JOIN)Willtb_userTable andtb_addressThe table is related, the condition istb_userTable ofidColumn equalstb_addressTable ofuser_idList.
3. fromtb_addressSelect specific columns in the table and specify an alias for certain columns (e.g. AS aid）。
4. Use the left outer join (LEFT JOIN) Convert the result set withtb_role_userThe table is related, the condition istb_role_userTable ofuser_idColumn equalstb_userTable ofidList.
5. Then use the left outer join to connect the result set of the previous step withtb_roleThe table is related, the condition istb_roleTable ofidColumn equalstb_role_userTable ofrole_idList.
6. fromtb_roleSelect from the tablenamecolumn and specify an alias for itroleName。

Now I will expand this query with some additional explanations and possible optimizations:

-- Select the user and its associated address、Role information
SELECT 
    -- All columns of the user table
     AS userId, , , , u.created_at, u.updated_at,
    -- Specific columns and alias of the address table
     AS addressId,  AS addressName,  AS pro, , ,  AS addressDetail,  AS addressTel,
    -- Alias ​​for character names
     AS roleName
FROM 
    tb_user u
    -- Inner connection：Only select users with addresses
    INNER JOIN tb_address d ON  = d.user_id
    -- Left outer connection：Select all users and their possible role associations
    LEFT JOIN tb_role_user ur ON  = ur.user_id
    -- 再Left outer connection：Select all role associations and their role names
    LEFT JOIN tb_role r ON ur.role_id = ;

MySQL self-association query

Define table areas, structure as follows

• id
• atitle
• pid

Because the province does not have a province to which it belongs, you can fill it in as null

The province pid of the city belongs to, fill in the number id corresponding to the province

This is self-association. A column in the table associates another column in the table, but their business logic meanings are different. The pid of the city information refers to the id of the information.

In this table, the structure remains unchanged, and information such as districts, counties, towns, streets, villages and communities can be added.

• The statements for creating an area table are as follows:

create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);

How many provinces are there in total

• Query all cities whose province's name is "Shanxi Province"

select city.* from areas as city
inner join areas as province on =
where ='Shanxi Province';

• Search all districts and counties whose city name is "Guangzhou City"

select dis.*,dis2.* from areas as dis
inner join areas as city on =
left join areas as dis2 on =
where ='Guangzhou City';

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.