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
|
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
