Shulou(Shulou.com)05/31 Report--

MySQL multi-table relationship is what, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The relationship between multiple tables

The relationship between table and table refers to the relationship between table and data.

One-to-one relationship

In practice, one-to-one is rarely used in development, because one-to-one can be created into a table.

Case: a husband can only have one wife

One-to-many relationship

Case: one category corresponds to multiple goods

Conclusion: the one who has the foreign key is the more one.

1. Note:

An one-to-many relationship is similar to the creation of an one-to-one relationship, except that the foreign key is not unique.

two。 One-to-many relationship creation:

Add a foreign key column

Add a foreign key constraint

3. Note:

It needs an intermediate table to complete the creation of many-to-many relationships.

A many-to-many relationship is actually a combination of two one-to-many relationships.

Many-to-many relationship creation:

Create an intermediate table and create foreign key columns for two tables in a many-to-many relationship

Add a foreign key constraint to the intermediate table

Add a federated primary key constraint to the intermediate table

Multi-table association query

6.1

Key word for cross-connect: CROSSJOIN

1. Implicit cross connection

SELECT*FROMA,B

two。 Explicit cross connection

SELECT*FROMACROSSJOINB

Internal connection

Keyword for internal connection: INNERJOIN

Inner joins, also known as equivalent joins, use the comparison operator to match rows in two tables based on the values of the columns common to each table.

1. Implicit internal connection

SELECT*FROMA,BWHEREA.id=B.id

two。 Explicit internal connection

SELECT*FROMAINNERJOINBONA.id=B.id

External connection

The outer join can be a left outer join, a right outer join, or a full outer join. In other words, the external connection can be divided into: left outer connection, right outer connection and full external connection.

External joins require the concept of a master table or a reserved table.

When an outer join is specified in the FROM clause, it can be specified by one of the following sets of keywords:

1. Left outer connection: LEFTJOIN or LEFTOUTERJOIN

SELECT*FROMALEFTJOINBONA.id=B.id

two。 Right outer connection:: RIGHTJOIN or RIGHTOUTERJOIN

SELECT*FROMARIGHTJOINBONA.id=B.id

3. Full external connection (not supported by MySQL): FULLJOIN or FULLOUTERJOIN

SELECT*FROMAFULLJOINBONA.id=B.id

Summary of external connections:

L analyze the master-slave table through business requirements

If you use LEFTJOIN, the main table is on its left

If you use RIGHTJOIN, the main table is on its right

If the result of the query is mainly based on the master table, and if the records of the slave table cannot be matched, the null will be added.

Paging query

The paging keyword of MySQL is: LIMIT

Format:

SELECT*FROMtableLIMIT [offset,] rows

Subquery

Definition:

Subqueries allow you to nest one query within another.

Sub-query, also known as internal query, relative to the internal query, including the internal query is called the external query.

A subquery can contain any clause that a normal select can include, such as distinct, groupby, orderby, limit, join, union, etc.

But the corresponding external query must be one of the following statements: select, insert, update, delete.

Location:

Select, after from, where.

There is no practical significance in groupby and orderby.

MySQL transaction processing

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity.

In MySQL, only databases or tables that use the Innodb database engine support transactions.

Z. transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed.

Z. transactions are used to manage DML operations, such as insert,update,delete statements

In general, transactions must meet four conditions (ACID):

Atomicity (atomicity)

All operations that make up a transaction must be a logical unit that either executes all or none at all.

Consistency (stability)

The database must be stable before and after the transaction is executed.

Isolation (isolation)

Transactions do not interact with each other.

Durability (Reliability)

All transactions must be written to disk after successful execution.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.