1. Foreign key
1. What is a foreign key: A foreign key refers to one or more columns in another table. The referenced columns should have primary key constraints or Uniqueness constraints. Foreign keys are used to establish and strengthen the connection between data in two tables.
2. Add foreign key constraints to the table
If you want to truly connect the data of two tables, you need to add foreign key constraints to the table. The syntax format for adding foreign key constraints to a table is as follows: alter table table name add constraint FK_ID foreign key (foreign key field name) references foreign key table name (primary key field name)
When adding foreign key constraints to a table, there are some things that need to be noted, as follows:
| Function description | |
| Delete all records that contain references to the deleted key value | |
| Modify all records that contain references to the deleted key values and replace them with null values (cannot be used for fields that have been marked as not null) | |
| No action | ##Restrict |
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 themany-to-one table relationship, The foreign key should be built on themostside, otherwise it will cause data redundancy. (2) Many-to-many: such as student table and course schedule. Usually, in order to realize this relationship, an intermediate table (called a join table) needs to be defined. This table will have two foreign keys, referencing the course schedule and the student table respectively. In a many-to-many relationship, it should be noted that the two foreign keys connecting the table are repeatable, but the relationship between the two foreign keys cannot be repeated, so the two foreign keys are the same as those of the table. Union primary key.(3) One-to-one: First of all, the master-slave relationship must be distinguished. The slave table requires the existence of the master table to be meaningful. For example, the person is the master table, the ID card is the slave table, and the foreign key is established in the slave table. It should be noted that this relationship is not common in databases, because information stored in this way is usually placed in a table. In actual development, the one-to-one association relationship can be applied in the following aspects.Ø Split a table with many columns;Ø Isolate part of a table for security reasons;Ø Save temporary data and can be deleted effortlessly This table deletes the data.2. Adding data
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 |
The above is the detailed content of MySQL database multi-table operation. For more information, please follow other related articles on the PHP Chinese website!
