MySQL multi-table association query example analysis

Database design paradigm

There are currently five paradigms in database design. Generally, our database only needs to meet the first three paradigms

The first paradigm: ensure that each column remains atomic

What is atomicity? It means that it cannot be subdivided. For example, the contact information below is

. The contact information includes QQ, WeChat, phone, etc. Obviously, this column does not satisfy atomicity. , if it is a separate QQ or phone number, there is only one that satisfies the first normal form

Second normal form: To have a primary key, other fields are required to depend on the primary key

Why is the primary key so important? We can understand it this way, if the table is regarded as a team, then the primary key is the flag of the team

• Without the primary key, there is no uniqueness. Without uniqueness, this row of records cannot be located in the collection. So we need the primary key.

Why do other fields need to rely on the primary key? Because there is no way to determine their location without relying on the primary key. More importantly, the row of records composed of other fields represents the same thing as the primary key, and the primary key is unique. They only need to depend on the primary key, and they become unique.

Third Normal Form: The third normal form is to eliminate transitive dependencies and facilitate understanding. It can be regarded as "eliminating redundancy"

How to understand this? See the following example

If we design a table like the above, it looks normal, but we split the table up

If we do this, it will be much clearer. We directly associate the two tables through the product number. In every aspect, it is much better than squeezing them all into one table.

Key

We know that there is a primary key, which is equivalent to the identifier of the table, so what about the foreign key?

● Foreign key: refers to a record in another data table.

● The foreign key column type is consistent with the primary key column type. The association/reference relationship between data tables is established by relying on the specific primary key (primary key) and foreign key (foreign key)

Syntax:

Add foreign key constraints
ALTER TABLE table name ADD [CONSTRAINT constraint name] FOREIGN KEY (foreign key column)
REFERENCES associated table (primary key);
Delete the foreign check key ALTER TABLE table name DROP FOREIGN KEY foreign key constraint name

We mentioned in the above example of the third normal form that to eliminate redundancy and associate two tables through a certain column, then We usually set this column that connects two tables as a foreign key

However, if we need to query related information between two tables, we may not necessarily use foreign key constraints

• If two tables are associated with a query, we do not add a foreign key constraint. We call this a weak reference.

• If a foreign key constraint is added, then it It is a strong reference

So what is the difference between these two references?

We know that when we use foreign keys, the foreign keys are located in the secondary table. The key points to the primary key of the main table, so a constraint is established between the two tables. At this time, we cannot modify the values ​​associated with the main table or the slave table at will. This is a strong reference

1. When there are no corresponding records in the master table, records cannot be added to the slave table

2. Values ​​in the master table cannot be changed, resulting in orphaned records in the slave table

3 , The slave table has records corresponding to the master table, and the row cannot be deleted from the master table

4. Before deleting the master table, delete the slave table first

Weak references allow us to modify the association at will Values ​​between

-- 创建学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    num INT,
    NAME VARCHAR(20),
    sex CHAR(1),
    gradeId INT   -- 从表外键列
)
-- 创建年级表
CREATE TABLE grade(
    -- 主表主键列
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)     
-- 添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_grade 
      FOREIGN KEY(gradeId) REFERENCES grade(id)

Create and add data to the main table:

Create and add data to the slave table:

As you can see, a foreign key constraint has been added to the gradeId field

At this time we try to delete a column of the main table:

Yes See, you cannot change the main table at will. Once changed, the data in the slave table will be isolated

Inner join

##●● Query the intersection data in the two tables that meet the conditions

Syntax:

Select results from Table 1, Table 2 where Table 1.column1 = Table 2.column2

Inner joins include equal joins, non-equivalent joins, and self-joins. Here we mainly discuss self-joins

Cartesian product phenomenon: Table 1 has m rows, Table 2 has n rows, the result =m*n

What is self-connection? It means that you associate yourself with yourself and do a Cartesian product with yourself. This may not be easy to understand. Here is an example:

我们平时在淘宝网购填地址的时候, 都是采用选择的方式, 先选择省,然后是省下面的市, 接着是市下面的区(县) ,它们都是在数据库中存着, 如何去实现这个功能呢 ?

有人可能会说, 建三张表相互关联即可 , 但实际是 , 我们采用自连接的方式 , 一张表即可实现

CREATE TABLE demo(     -- 建立demo表
    id INT PRIMARY KEY,
    NAME VARCHAR(50),
    pid INT    
 )

往表中填入数据, pid为关联上一级的id

-- 自连接 
-- 在多表关系中我们需要定义别名来区分
SELECT d1.name,d2.name,d3.name FROM demo d1 
            INNER JOIN demo d2 ON d1.id=d2.pid  --自连接条件
            INNER JOIN demo d3 ON d2.id=d3.pid  --自连接条件
  WHERE d3.id=6101011   -- 查询条件

结果 :

外连接

外连接又分为左外连接与右外连接

先看左外连接 :

语法

select 结果 from 表1 left join 表 2 on 表1.column1 = 表 2.column2

左连接和内连接有什么不同呢? 通过两幅图我们就可以看出 ,内连接是取了两张表的共同部分 , 而左连接是取了左边表的全部(包括两张表的共同部分)

也就是说, 不仅查询两张表的共同部分, 并且左边表会被全部查询出来

我们通过上面外键所建的表来演示 , 为了演示方便,我们为student表再添加一列数据

可以看到, 此时第五列并没有去关联grade表

-- 左外连接查询
SELECT * FROM student s 
     LEFT JOIN grade g ON s.gradeId= g.id

查询结果如下 :

那么说到这, 右外连接也就不难理解了 , 每次都会完整的查询右边的表

同样我们再为grade添加一条无关联的数据

语法 :

select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2

-- 右外连接查询
SELECT * FROM student s 
     RIGHT JOIN grade g ON s.gradeId= g.id

查询结果 : 

可以看到, 右表被完全查询

The above is the detailed content of MySQL multi-table association query example analysis. For more information, please follow other related articles on the PHP Chinese website!