MySQL database multi-table operation

Reject the main table to delete or modify the foreign key associated column. (This is the default setting and the safest setting when the on delete and on update clauses are not defined)

3. Delete foreign key constraints: alter table table name drop foreign key foreign key name;

2. Operation of association table

1. Association relationship

(1) Many-to-one: In the many-to-one table relationship, The foreign key should be built on the most side, otherwise it will cause data redundancy.

From the above results you can It can be seen that the result of the cross connection is the combination of all the data in the two tables. It should be noted that in actual research and development, this requirement is rare and is generally not used. Instead, specific conditions are used to perform the data Purposeful inquiry.

2. Inner connection: also known as simple connection or natural connection. Use comparison operators to compare the data of the two tables, and list the data rows that match the join conditions and combine them into new records. The syntax format is as follows:

Select query field from table 1 [inner] join table 2 on table 1. relational field = table 2. relational field

You can also use where conditional statements to achieve this Same functionality.

Although the query results of these two methods are the same, inner join is an inner join statement, and where is a conditional judgment statement. You can add other conditions directly after where. The inner join statement cannot.

#If the two tables involved in a join query are the same table, this query is called a self-join query. Self-join is a special kind of join. It means that the tables connected to each other are physically the same table, but logically divided into two tables. For example, if you want to query which employees are in Wang Hong’s department, you can use self-join query. .

3. Outer connection: The table on the left of the keyword is called the coordinates, and the word on the right is called the right table

Select the field to be queried from table 1 left|right [outer] join table 2

On table 1. relationship field = table 2. relationship field where condition

(1) left join (left join): return includes the left table All records in and the records in the right table that meet the join conditions.

If a record in the left table does not exist in the right table, it will be displayed as empty in the right table.

(2) Right join: Returns all records in the right table and records in the left table that meet the join conditions.

4. Compound conditional connection query

4. Subquery: refers to a query statement nested in another A query within a query statement. It can be nested in a select, select...into statement, insert...into and other statements. When executing a query statement, the statements in the subquery will first be executed, and then the returned results will be used as filter conditions for the outer query. In the subquery, you can usually use the in, exists, any, and all operators

( 1) Subquery with in keyword: The inner query statement only returns one data column, and the value in this data column will be used for comparison operation by the outer query statement.

For example: Query the department with employees whose age is 20 years old.

(2) Subquery with exists keyword: The parameter after the exists keyword can be any subquery. The function of this subquery is equivalent to testing. It does not Any data will be generated and only True or False will be returned. When the return value is True, the outer query will be executed.

In the following example, the return result of the subquery is True, so the outer query statement will be executed, that is, all department information is queried. It should be noted that the exists keyword is more efficient than the in keyword, so in actual development, especially when the amount of data is large, it is recommended to use the exists keyword.

(3) Subquery with any keyword: any means that any one of the conditions is met. It allows the creation of an expression to compare the return value list of the subquery. As long as any comparison condition in the inner subquery is met, a result is returned as the outer query condition.

(4) Subquery with all keyword: It is similar to any, except that the results returned by the subquery with all keyword must satisfy all inner queries at the same time condition.

(5) Subquery with comparison operator

This article explains the multi-table operation of MySQL database, and more For more related content, please pay attention to php Chinese website.

Related recommendations:

$Selector--how to encapsulate DOM into jquery objects

Native js componentization Develop simple carousel chart example code

css3 animated navigation bar 3D