Basic knowledge about external keys and internal and external connections

The basic knowledge of external keys and internal and external connections because the existing company has never used mysql Foreign keys (affects the database performance and uses code logic control), so I am not very familiar with external keys, today, I read some foreign key related information on the Internet and found some internal and external connection knowledge. 1mysql foreign key knowledge there are three types of mysql Foreign keys. If the primary keyword of Table A is A field in Table B, this field is called the foreign key of Table B, table A is called the master table, and table B is called the slave table. Foreign keys are used to achieve the integrity of the reference. Different foreign key constraints can closely combine the two tables, in particular, modifying or deleting cascade operations makes daily maintenance easier. Common Foreign keys include cascade, set null, and no action/restrict test cascade:

01create table test111 (02    id int not null,03    name varchar(30),04    primary key (id)05);06create table test222 (07    id int not null,08    name varchar(30),09    groupid int,10    primary key (id),11    foreign key (groupid) references test111(id) on delete cascade on update cascade12);1314insert into test111 values (1, 'Group1');15insert into test111 values (2, 'Group2');1617insert into test222 values (1, 'qianxin', 1);18insert into test222 values (2, 'yiyu', 2);19insert into test222 values (3, 'dai', 2);20delete from test111 where id=2;             21update test111 set id=2 where id=1;

 

In this case, it cannot be inserted. User Group 3 does not exist, the groupid cascade of 1 record of test222 is changed to 2 B testing to set null when two or three records in test222r are cascade deleted when the table is not deleted according to the integrity constraints.

01create table test111 (02    id int not null,03    name varchar(30),04    primary key (id)05);06create table test333 (07    id int not null,08    name varchar(30),09    groupid int,10    primary key (id),11    foreign key (groupid) references test111(id) on delete set null on update set null12);1314insert into test111 values (1, 'Group1');15insert into test111 values (2, 'Group2');1617insert into test333 values (1, 'qianxin', 1);18insert into test333 values (2, 'yiyu', 2);19insert into test333 values (3, 'dai', 3);20delete from test111 where id=2;             21update test111 set id=2 where id=1;

 

Unable to insert, user group 3 does not exist, the deletion does not conform to the integrity constraints. This causes the groupid of the 2 and 3 Records in test333 to be set to NULL. When an update is made, the groupid of the 1 record in t_user is set to NULL. /restrict) method

01create table test111 (02    id int not null,03    name varchar(30),04    primary key (id)05);06create table test444 (07    id int not null,08    name varchar(30),09    groupid int,10    primary key (id),11    foreign key (groupid) references test111(id) on delete no action on update no action12);1314insert into test111 values (1, 'Group1');15insert into test111 values (2, 'Group2');1617insert into test444 values (1, 'qianxin', 1);18insert into test444 values (2, 'yiyu', 2);19insert into test444 values (3, 'dai', 3);20delete from test111 where id=2;             21update test111 set id=2 where id=1;

 

Cannot be inserted at the time of insertion, user group 3 does not exist, and does not comply with the integrity constraints of the reference when deleting the table, there is a reference from the table, so the master table cannot delete the update when there is a reference from the table, therefore, the primary table cannot be modified in MySQL. The restrict method works the same as the no action method. 2 oracle foreign key is the joined field between the table and another table. It must be used as the primary key in another table to ensure data integrity. It usually includes the following types: entity A integrity, ensuring that each entity is unique (implemented through the primary key) Domain B integrity, make sure that the attribute value only selects C Association integrity from a specific set of optional sets, and make sure that each foreign key or NULL (if allowed) the following is a test example of how to create a foreign key for two tables.

01CREATE TABLE ZZ_STUDENT (02    ID CHAR (10),03    NAME VARCHAR (8),04    SEX CHAR (1)05);0607ALTER TABLE ZZ_STUDENT ADD CONSTRAINT PK_STUDENT PRIMARY KEY (ID);0809CREATE TABLE ZZ_SCORE (ID CHAR(10), MATH NUMBER(5, 2));1011ALTER TABLE ZZ_SCORE ADD CONSTRAINT FK_SCROE FOREIGN KEY (ID) REFERENCES ZZ_STUDENT (ID);

 

3. Database internal connection external connection left connection right connection internal connection: Check the data corresponding to the data in the two tables for external connection: A table is used as the basis to check the corresponding data. Full join is based on multiple tables. The following test data is inserted first:

1 insert into student (sno, sname) values ('123456', 'shit'); 2 insert into student (sno, sname) values ('123456', 'wang '); 3 insert into student (sno, sname) values ('000000', 'zhang'); 4 insert into student (sno, sname) values ('000000', 'xiaoja '); 5 insert into stu_score (sno, scrore) values ('000000', 2005001); 6 insert into stu_score (sno, scrore) values ('000000', 90.00 ); 7 insert into stu_score (sno, scrore) values ('000000', 2005008); 8 insert into stu_score (sno, scrore) values ('000000', 80.50 );

 

(1) The inner join, inner join, and join query operations list data rows that match the connection conditions. They use comparison operators to compare the column values of the connected columns.

---- Assume that table a has M records, table B has N records, and table a and table B have K sno records. ---- inner join (data volume = K) 1 select. *, B. * from student a join stu_score B on. sno = B. sno; 2 select. *, B. * from student a inner join stu_score B on. sno = B. the query result is as follows: 2005001 Xiao Shi 2005001 902005002 Xiao Wang 2005002 95

 

(2) external connections (left and right connections)

Left join or left join, left outer join returns the right join or right join of all records in the left table that are equal to the join fields in the right table, right outer join returns all records in the right table that are equal to the join fields in the table ---- left join/left outer join (data volume = M) select. *, B. * from student a left join stu_score B on. sno = B. sno; select. *, B. * from student a left outer join stu_score B on. sno = B. sno; query results: 2005001 Xiao Shi 2005001 902005002 Xiao Wang 2005002 Xiao Zhang 952005003 Xiao Jia ---- right join/right Outer Join (data volume = N) select. *, B. * from student a right join stu_score B on. sno = B. sno; select. *, B. * from student a right outer join stu_score B on. sno = B. the query result is as follows: 2005001 Xiao Shi 2005001 902005002 Xiao Wang 2005002 952005008 80.52005009 88.5

 

(3) Full join () Definition: Based on the inner join, it also contains all non-conforming data rows in two tables, and fill in the NULL keyword in the left table and right table column: FULL JOIN

---- Full join (data volume = M + N-K) select a. *, B. * from student a full join stu_score B on a. sno = B. sno;

 

Mysql does not support the query. (4) cross join definition: combines all rows in two tables. The number of connected rows is the product number of two tables. (Cartesian Product) KEYWORDS: cross join format: FROM table name 1 cross join table name 2 ---- cross join (data volume = M * N)

select a.*,b.* from student a cross join stu_score b ;

 

The query result is as follows:

2005001 Xiao Shi 2005001 902005002 Xiao Wang 2005001 902005003 Xiao Zhang 2005001 902005004 Xiao Jia 2005001 Xiao Shi 902005001 2005002 Xiao Wang 952005002 2005002 Xiao Zhang 952005003 2005002 Xiao Jia 952005004 2005002 Xiao Shi 952005001 2005008 Xiao Wang 80.52005002 2005008 Xiao Zhang 80.52005003 2005008 Xiao Jia 80.52005004 Xiao Shi 2005008 80.52005001 xiao Wang 2005009 88.52005003 Xiao Zhang 2005009 88.52005004 Xiao Jia 2005009 88.5