MySQL database multi-table operation

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.

# The gid in the Student table is the class ID of the student, which is the primary key ID in the grade table. Then gid can be used as a foreign key to the table student. The referenced table, that is, table grade, is the master table; the table that refers to the foreign key, that is, table student, is the slave table, and the two tables are in a master-slave relationship. Table student can use gid to connect the information in table grade, thereby establishing a connection between the data in the two tables.

After the outer key is attracted, the outer key column can only insert the values ​​of the reference column. The value of the reference column cannot be deleted.

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:

(1) The table to establish a foreign key must be of InnoDB type, not Temporary tables. Because only InnoDB type tables in MySQL support foreign keys;

(2) When defining foreign key names, quotation marks cannot be added, such as constraint ‘FK_ID’ or constraint “FK_ID”, which are both wrong.

When the data in the main table is deleted, the data in the slave table should also be deleted, otherwise a lot of meaningless junk data will be generated. Mysql can add on delete or on update clauses when creating foreign keys to tell the database how to avoid the generation of garbage data. The specific syntax format is as follows:

Alter table table name addconstraint FK_ID foreign key (foreign key field name) references external table name (primary key field name);

[on delete{cascade | set null | no action | restrict}]

[on update{cascade | set null | no action | restrict}]

The specific description of each parameter in the statement is as shown in the following table:

Parameter nameCascadeSet nullNo action##Restrict

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
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. (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 After the above statement is successfully executed, the data between the two tables will be related. sex. If you want to query the students in Software Class 1, you must first query the ID of Software Class 1, and then query the students in the student table based on this ID. #3. Deleting data: Due to the relationship between the grade table and the student table, the referenced value of the reference column cannot be deleted. Therefore, when deleting a software class, be sure to delete all students in the class first, and then delete the class. (1) Delete all students in Software Class 1 (2) In the grade table, delete Software Class 1                                                                                                                                                                                                                                forwarding to deleting Software Class 2, will result in an error: It should be noted that in actual situations, if you want to delete 'Software Class 1' There is no need to delete 'Students of Software Class 1'. You can change gid=1 in the student table to gid=null. As long as the column in the main table is not referenced by the slave table, it can be deleted. However, when creating the table, the gid field has a non-null constraint, so in this example only students can be deleted. 3. Connection query: When there are fields with the same meaning in two or more tables, you can use these fields to perform connection queries on different tables. 1. Cross connection: The returned result is the Cartesian product of all data rows in the two tables being connected, that is, the number of data rows in the first table that meet the query conditions is returned multiplied by the second The number of data rows in the table that meet the query conditions. The syntax format is as follows: Select * from table 1 cross join table 2; 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

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!