FOREIGN KEY constraints and table joins
"Establishing a table relationship"1. Create a table that describes the relationships between the two tables and stores the primary key correspondence of the two tables that need to be connected. 2. Using FOREIGN KEY constraintsforeign key: A field in one table refers to the primary key of another table, one field in a table with multiple data, and a primary key for another table.
creation of foreign keys:
CONSTRAINT < foreign key name prefix > FOREIGN key < table key Name > REFERENCES < referenced table name > (< referenced field >);
Cascade:A part is deleted, and another part of the corresponding relationship is deleted.
"Table Connection"if there are t_student and t_class two tables, wherein t_student in the name of the students and their classes, T_class in the class name A, B, C, through the foreign key established the two contacts, if you want to find out all Class A people, need to first in the T_ class to find the primary key of the class, and then go to T_student to find all the foreign key values that match this primary key. The simplest approach is to use nested queries.
SELECT * from t_student where class = (SELECT ID from t_class where name = ' A ');
2. Query two tables at the same time, the Cartesian product of the table, resulting in two table length of the product query, there should be enough constraints. because field names in multiple tables may be duplicated, use the table name. field syntax to access, you can easily query the name of the table after the alias (the space after the alias). Querying two tables at the same time can query the fields of two tables in a comprehensive form. For example, the following statement also implements the above features:
SELECT ts.name sname,tc.name cname from t_student ts, t_class tc WHERE ts.class = tc.id and tc.name = ' A ';
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
(ii) connection of tables and foreign KEY constraints
