database table Relationship Related cases

I. One to one (one record)

Read a lot of online cases, now summarized as follows:

A record of a table can only correspond to one record of another table, whereas a record of another table can only match one record of this table;

To separate common and infrequently used fields, if each query is to query all records will affect the efficiency (table design), when the amount of data is very large, it will affect the query efficiency, so the high-volume segment of the less common fields to be separated, you can reduce the pressure of the database;

The simple design is as follows:

General table:

CREATE TABLE person (id INT PRIMARY KEY, NAME VARCHAR (Ten), Sex CHAR (1), wife int, husband int); INSERT into person VALUES (1,'Small Flower','0',0,3INSERT into person VALUES (2,'Yufen','0',0,4INSERT into person VALUES (3,'Zhang San','1',1,0INSERT into person VALUES (4,'John Doe','1',2,0INSERT into person VALUES (5,'Harry','0',0,0INSERT into person VALUES (6,'Zhang Five','2',0,0);

Can be decomposed into the following two tables:

CREATE TABLE Wife (  ID INT PRIMARY KEY,  NAME VARCHAR),   sex CHAR (1 )); CREATE TABLE Husband (  ID INT PRIMARY KEY,  NAME VARCHAR),  sex CHAR (1  ),  wid INT UNIQUE,  

There are two kinds of data table-to-one relationships: a foreign key Association (which is a foreign key association), and a primary key association , so that you can ensure a one-on relationship, as follows.

CREATE TABLE woman (id INT PRIMARY KEY auto_increment,name VARCHAR (50) not null,sex VARCHAR (6) is not NULL);--primary key auto-increment insert INTO woman VALUES (0, ' Little Red ', ' woman ') INSERT into woman VALUES (0, ' Little Violet ', ' woman ') INSERT into woman VALUES (0, ' Little orange ', ' woman ') INSERT into woman VALUES (0, ' Little blue ', ' woman '); CREATE TABLE man (id INT PRIMARY KEY auto_increment,name VARCHAR (50) not null,sex VARCHAR (6) not null,wife_id INT);--primary key auto-increment insert into man VALUES (0, ' Zhang San ', ' Male ', 1); INSERT into man VALUES (0, ' Zhang Fei ', ' Male ', 2); INSERT into man VALUES (0, ' Zhang Fish ', ' Male ', 3); INSERT into man VALUES (0, ' Zhang put ', ' Male ', 4); SELECT W.name as' Wife ', m.name as ' husband ' from woman W,man m WHERE w.id=m.wife_id

If you don't bother building a table, you can create two views (one window of the table) to handle

CREATE VIEW Women as SELECT * from person WHERE sex= ' 0 '; CREATE VIEW Men as SELECT * from person WHERE sex= ' 1 ';

Queries are used (As with Query Table command statements):

SELECT Women.name as wife,  Men.name as husband from  women,men WHERE women.husband = men.id;
Or
SELECT Women.name as wife,  Men.name as husband from  women INNER JOIN Man on  women.husband = men.id;

Two or one to many (one record corresponds to multiple records):

Departments and employees

 create TABLE dep (id INT PRIMARY KEY auto_increment,name VARCHAR ( 30 0, ' marketing '  0, ' technical department '  0, ' finance Department ' , ' development Department '  50 0, ' Zhang San ', 1, ' Zhang Fei ', 1 0, ' Zhang Niu ', 1, ' fish ', 1 -- multiple, one department corresponds to multiple employees select D.name as  ' Department ', E.name ' employees ' from DEP d,emp e WHERE E. ' did ' =d. ' ID '; 

Three, many-to-many relationships (multiple records correspond to multiple records):

Teachers and students, a teacher taught a lot of students, a student was taught by many teachers, many-to-many set up;

CREATE TABLE Teacher (t_id INT PRIMARY KEY auto_increment,name VARCHAR (50), Sex VARCHAR (6) ; INSERT into teacher VALUES (0, ' Li Ming ', ' Male ') INSERT into teacher VALUES (0, ' Lee Black ', ' male ') INSERT into teacher VALUES (0, ' Li Bai ', ' Male ');D ROP TABLE student; CREATE TABLE Student (s_id INT PRIMARY KEY auto_increment,name VARCHAR (50), Sex VARCHAR (6) ; INSERT into student VALUES (0, ' Jack ', ' man ') INSERT into student VALUES (0, ' Red gram ', ' Male ') INSERT into student VALUES (0, ' orange gram ', ' Male ') INSERT into student VALUES (0, ' Blueco ', ' Male ') INSERT into student VALUES (0, ' green gram ', ' Male ');D rop TABLE teacher_student; CREATE TABLE teacher_student (id INT PRIMARY key auto_increment,tid int,sid int,foreign key (tid) REFERENCES teacher (t_id), FOREIGN KEY (SID) REFERENCES student (s_id));
INSERT into Teacher_student VALUES (0,1,1) INSERT into Teacher_student VALUES (0,1,2) INSERT into Teacher_student VALUES (0,1,3) INSERT into Teacher_student VALUES (0,1,4);
--Query the name of the teacher (Li Ming) who has 1 Tid taught the names of the students select S. ' Name ' from student S JOIN (select Sid from Teacher_student WHERE tid=1) TS on s.s_id=Ts.sid;

--Query the teachers who have taught students (sid=1)
Select T.name from teacher T left JOIN (SELECT tid from teacher_student WHERE sid=1) ts on T. ' t_id ' =ts.tid

INSERT into teacher_student values (0,2,1), insert into teacher_student values (0,3,1);D elete from Teacher_student WHERE ID=5

database table Relationship Related cases