MySQL multi table query

This is the back-end small class of the monastery. Each article is shared from

[background introduction] [knowledge analysis] [common problems] [solutions] [coding practice] [extended thinking] [more discussion] [References]

Eight aspects of in-depth analysis of back-end knowledge / skills. This article shares:

[MySQL multi table query]

Title: [Java (vocational) small class of Xiuzhen academy] MySQL multi table query

Hello, I'm a 15 student of Wuhan Branch of it Academy. I'm an honest, pure and kind java programmer. This time, we will bring you a question about in-depth thinking. Why do we use MySQL multi table query?

(1) Background:

In task 1, we have a certain understanding of the basic SQL of MySQL database, and can also use the SQL operation based on a single table to complete the Java task of the Academy. However, in actual projects, the data we need often exist in multiple tables, Then the basic single table query statement is inadequate (of course, you can also choose to query a single table multiple times, but the development efficiency is greatly reduced)

What is a multi table query

Multi table query is to find the required data from multiple tables, and integrate the data found in the two tables according to the constraints set by yourself

How is the relationship between tables established?

Table association is only a logical concept. MySQL is not powerful enough to help us perfectly realize the physical "hard binding". In fact, the logic of this association is only that some data in the table have certain connections (such as setting up public fields, building association tables, etc.), and this data connection is the logic we set when designing tables

How to find out the data in several different tables with only one SQL statement?

Cross connect

Cross join is to join any two tables. Suppose that there are two tables, the Cartesian product is the arrangement and combination of all records of the two tables, for example: select * from table 1 and table 2 - this is the Cartesian product of Table 1 and table 2. However, in practice, most of the real use is its subset (that is, table 2 has association conditions), and the Cartesian product will be used as the final result only in very special application scenarios

(2) Knowledge analysis:

What are the classifications of multi table connections? What are the connection methods for these classifications?

A) Inner join

B) External connection: left outer join, right outer join, union

C) Cross join: cross join

Inner connection

Syntax: select column from table 1 alias inner join table 2 alias on table 1 Column = Table 2 Columns

From, the Cartesian product of Table 1 and table 2 will be performed. For each row of data matched, it will be judged whether it is true through the on condition, and the matching information of the row will be stored in the temporary table, otherwise it will not be stored

External connection

Left outer join: all records in the left table will be put into the result set, regardless of whether there are matching records in the right table

Syntax: select column from table 1 left outer join table 2 on table 1 Column = Table 2 column

Right outer connection: all records in the right table will be returned regardless of whether the connection conditions are successfully matched

Syntax: select column from table 1 right outer join table 2 on table 1 Column = Table 2 column

Subquery

Take the inner query result as the comparison condition of the outer query, a select From... Where query statement blocks can be nested in another select From... In the where clause of the where query block, it is also called nested query. The outer query is called the parent query and the main query. An inner query is called a sub query, which is a sub query. Sub queries can be nested at multiple levels, and the results of sub queries become the conditions of parent queries. Process the sub queries first and then the parent queries.

(3) Frequently asked questions:

constraint

Integrity constraints in database are used to prevent accidental destruction of data and ensure data security and consistency. Usually, we need to add appropriate constraints on some columns of the database to prevent data inconsistency caused by program bugs, because it is difficult to troubleshoot such data inconsistency problems

(4) Solution:

Constraints in tables

Primary key constraint, unique constraint, non empty constraint, etc

Constraints between tables

Foreign key constraints (also known as referential integrity constraints)

Function: maintain data consistency and integrity

(5) Coding practice

(6) Expand thinking:

The respective advantages of multiple queries on a single table and multi table joint queries

When the business logic is not very complex, most joins can be replaced by multiple queries. In my opinion, the difference between them is that using join prefers to use SQL statements to deal with logical problems between data, and multiple queries prefer to use program code to solve logical problems between data, forcing people to use program code instead of SQL statements to deal with problems

Advantages of multiple queries in a single table

1. Low resource consumption in high concurrency scenarios

2. Reduces the coupling between services

3. Strong scalability

Advantages of multi table joint query

1. High development efficiency in low concurrency scenarios

2. The consistency and integrity of frequent complex data reading and writing are high

3. The ability requirements for developers are relatively low

(7) References:

1. Know: https://zhuanlan.zhihu.com/p/24659011

2. CSDN blog: https://blog.csdn.net/jintao_ma/article/details/51260458

(8) More discussion:

Question 1 Three paradigms of relational database?

One paradigm is the indivisibility and atomicity of attributes.

The second paradigm is to have a primary key, which requires that other fields depend on the primary key.

The three paradigms are to eliminate transitive dependency and facilitate understanding, which can be regarded as "eliminating redundancy"

Question 2 How to use all external links in MySQL?

Full connection is not supported in MySQL. The way to realize full connection in MySQL is to use the union keyword to combine left and right connections

Question 3 How to duplicate multiple tables if there are too many queries?

Pay attention to add more restrictive statements and standardize the database design

(9) Thanks:

Thanks to elder martial brother Lou Wenbin. This tutorial is based on their previous technology sharing.

(10) Conclusion:

That's all for today's sharing. You are welcome to like, forward, leave messages and make bricks~

Ppt link video link