In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)05/31 Report--
This article introduces the relevant knowledge of "MySQL multi-table associated query instance analysis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Database design paradigm
At present, there are five paradigms for database design. Generally speaking, our database only needs to meet the first three items.
First Paradigm: ensure that each column remains atomic
What is atomicity? It means that it can no longer be divided, for example,
Contact methods include QQ, Wechat, phone, etc. Obviously, this column does not satisfy atomicity. If it is a separate QQ or phone, there is only one, which satisfies the first paradigm.
The second paradigm: to have a primary key, other fields are required to rely on the primary key
Why is the primary key so important? We can understand that if the watch is regarded as a team, then the main key is the team flag of the team.
Without a primary key, there is no uniqueness, and without uniqueness, this row of records cannot be located in the set, so you want the primary key.
Why should other fields depend on the primary key? Because they are not dependent on the primary key, they cannot be found. More importantly, the row of records made up of other fields and the primary key represent the same thing, while the primary key is unique, and they only need to rely on the primary key to become unique.
The third paradigm: the third paradigm is to eliminate transitive dependence, easy to understand, and can be regarded as "eliminating redundancy".
How do you understand this? Look at the following example
If we design a table like this, it looks normal in general, but we break it up.
If we do this, is it much clearer? we associate these two tables directly through the product number, which is much better than squeezing them all into one table in any way.
Foreign key
We know that there is a primary key, which is equivalent to the identity of the table, what about the foreign key?
● foreign key: refers to a record of another data table.
The ● foreign key column type is consistent with the primary key column type, and the association / reference relationship between the data tables is established by the specific primary key (primary key) and foreign key (foreign key).
Syntax:
Add a foreign key constraint
ALTER TABLE table name ADD [CONSTRAINT constraint name] FOREIGN KEY (foreign key column)
REFERENCES associated table (primary key)
Delete external inspection key ALTER TABLE table name DROP FOREIGN KEY foreign key constraint name
As we said in the example of the third paradigm above, if we eliminate redundancy and associate two tables with a column, then the column that joins the two tables will generally be set to a foreign key.
However, if we need two tables to associate queries, we do not necessarily use foreign key constraints.
If two tables are associated with a query, we do not add foreign key constraints, which we call weak references
If a foreign key constraint is added, it is a strong reference
So what's the difference between these two citations?
We know that when we use a foreign key, the foreign key is located in the slave table, and the foreign key points to the primary key of the master table, so a constraint is established between the two tables. At this time, we cannot modify the values associated with the master table or from the table at will. This is a strong reference.
1. When there is no corresponding record in the master table, the record cannot be added to the slave table
2. Values in the master table cannot be changed, resulting in isolation from records in the table
3. There is a record corresponding to the master table in the slave table, and the row cannot be deleted from the master table.
4. Delete the slave table before deleting the master table
For weak references, we can modify the values between associations at will.
-- create student table CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT, num INT, NAME VARCHAR (20), sex CHAR (1), gradeId INT-- Foreign key column of slave table)-- create grade table CREATE TABLE grade (--Primary key column id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (20))-- add foreign key constraint ALTER TABLE student ADD CONSTRAINT fk_grade FOREIGN KEY (gradeId) REFERENCES grade (id)
Create and add data for the main table:
Create and add data from the table:
As you can see, the foreign key constraint is added to the gradeId field
At this point we try to delete a column of the main table:
As you can see, the master table cannot be changed at will. If it is changed, the data in the slave table will be isolated.
Internal connection
● queries out the intersecting data in two tables that meet the conditions.
Syntax:
Select results from Table 1, Table 2 where Table 1.column1 = Table 2.column2
Internal connections include equivalent connections, non-equivalent connections, and self-connections. Here we mainly discuss self-connections.
Cartesian product phenomenon: table 1 has m rows, Table 2 has n rows, the result = m
What is self-connection, that is, you associate yourself with yourself, and you do Cartesian product with yourself? this may not be easy to understand. Examples are as follows:
When we usually purchase and fill in the address on Taobao, we always use the method of choice, first choose the province, then the city under the province, and then the district (county) under the city, they are all stored in the database, how to achieve this function?
Some people may say that it is OK to build three tables that are related to each other, but in fact, we can do it with one table in a self-join way.
CREATE TABLE demo (--create demo table id INT PRIMARY KEY, NAME VARCHAR (50), pid INT)
Fill in the table with data. Pid is the id at the next level associated with it.
-- self-join-- in multi-table relationships, we need to define aliases to distinguish between SELECT d1.namejournal d2.namememery d3.name FROM demo D1 INNER JOIN demo d2 ON d1.id=d2.pid-- self-join condition INNER JOIN demo D3 ON d2.id=d3.pid-- self-join condition WHERE d3.id=6101011-- query condition.
Results:
External connection
External connection is divided into left external connection and right external connection.
First take a look at the left outer link:
Grammar
Select results from Table 1 left join Table 2 on Table 1.column1 = Table 2.column2
What's the difference between a left connection and an inner connection? From the two pictures, we can see that the inner join takes the common part of the two tables, while the left join takes the whole of the left table (including the common part of the two tables).
In other words, not only the common parts of the two tables are queried, but all the tables on the left are queried.
We demonstrate through the table created by the foreign key above. For the convenience of the demonstration, we add another column of data to the student table.
As you can see, the fifth column is not associated with the grade table at this time
-- left outer join query SELECT * FROM student s LEFT JOIN grade g ON s.gradeId = g.id
The query results are as follows:
So when it comes to this, the right outer join is not difficult to understand. Every time, the table on the right will be fully queried.
Again, let's add an unrelated piece of data to grade.
Syntax:
Select results from Table 1 right join Table 2 on Table 1.column1 = Table 2.column2
-- query SELECT * FROM student s RIGHT JOIN grade g ON s.gradeId = g.id with right outer join
Query results:
As you can see, the table on the right is fully queried
This is the end of the content of "MySQL Multi-table Relational query instance Analysis". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.