Basic knowledge about external keys and internal and external connections bitsCN.com
Basic knowledge about external keys and internal and external connections
As the existing company has never used the mysql foreign key (which affects the database performance and uses code logic control), I do not know much about the foreign key, today, I read some foreign key related information on the internet and found some internal and external connection knowledge.
Knowledge about 1mysql foreign keys
Mysql has three types of 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, and table A is called the primary table, table B is called a 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 are in cascade mode, set null mode, and no action/restrict mode.
A testing cascade method:
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, data cannot be inserted. user group 3 does not exist, which is inconsistent with the integrity constraints of the reference.
Delete 2 and 3 records in test222r in cascading mode
When updating, the groupid cascade of 1 record of test222 is changed to 2.
Method B: set null
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, and does not comply with the integrity constraints of reference
Deletion causes the groupid of the 2 and 3 records in test333 to be set to NULL,
When updating, the groupid of 1 record in t_user is set to NULL.
C. test the no action/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 during insertion, user group 3 does not exist, and does not comply with the integrity constraints of reference
When a table is deleted, it is referenced from the table. Therefore, the table cannot be deleted.
When updating a table, there are references in 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
The foreign key is the joined field of another table. it must be used as the primary key in another table to ensure data integrity. It generally includes the following types:
Entity A Integrity ensures that each entity is unique (implemented through primary keys)
B domain integrity, ensure that the attribute value is only selected from a specific set of optional sets
C. integrity of the association. make sure that each foreign key is NULL (if allowed) or contains a value that matches the relevant primary key value.
The following is a test example: 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. left-connected and right-connected external connections in the database
Internal join: check the data corresponding to the data in the two tables.
External join: check the corresponding data based on a table
Full join is based on multiple tables
First, insert the following test data:
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) inner connection
The inner join and join query operations list data rows that match the connection conditions. they use comparison operators to compare
Column value.
---- 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 connection ()
Definition: based on the internal join, it also contains all data rows that do not meet the conditions in the two tables, and fill in NULL in the left and right columns of the two tables.
Keyword: 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 queries.
(4) cross join
Definition: combines all rows in two tables. the number of joined rows is the product of the 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
BitsCN.com