Database design (one-to-one, one-to-many, Many-to-many) associated query __ Database

※ The relationship between table and table 1, a one-to-one need of two tables. Of course, in order to save space for the project, usually only a table, if you want to achieve one-to-one query, you can build two views.
The example is as follows: 1 The physical table, initialization data CREATE table person (id INT, NAME VARCHAR (ten), Sex CHAR (1), wife int, husband int);
INSERT into the person VALUES (1, ' floret ', ' 0 ', 0, 3);
INSERT into the person VALUES (2, ' Yufen ', ' 0 ', 0,4);
INSERT into the person VALUES (3, ' John ', ' 1 ', 1,0);
INSERT into the person VALUES (4, ' Dick ', ' 1 ', 2,0);
INSERT into the person VALUES (5, ' Harry ', ' 1 ', 0,0);
2 Establish two Views create view women as SELECT * from person where sex= ' 0 ';

Create VIEW men as select * from person where sex= ' 1 ';
3) Check the husband/wife information/old version SELECT Women.name as wives, men.name as husband from women, men WHERE Women.husband = men.id;

New way of using inner join--98--more efficient SELECT women.name as wife, Men.name as husband from women INNER JOIN men on women.husband = men.id;
<pre name= "code" class= "SQL" >3, multi-database design Analysis ※ Case: A person can choose multiple courses, a course can be a lot of people choose.
Scheme one (poor design): 1 student form number name Sex age telephone ...  P001 Jack P002 Tom P003 Rose--------------------------2) curriculum number name textbook credits ... Students S001 Java..   ......   P001 S001 Java .....
P002 S001 Java ......   S002 database .....   P001 S002 database .....

P002 ...
Programme II (Good design: two entity table + a relational table): 1 Student Table (independent)---entity number name sex Age telephone ... 
P001 Jack P002 Tom P003 Rose Female 25 2) Course form (independent)---entity number name teaching material credits ...
S001 Java .....
S002 database .....
S003 XML .....
3 The timetable (newly added table for the relationship between many and many)--relationship Course number student number S001 P001 S001 P002 ...
S002 P002 S002 P003 ...
 S003 P001 ...        FOREIGN Key Foreign Key |
   |  
       |━━━━|
     |
Union primary key 4) code implementation CREATE TABLE Stud (ID varchar () primary key, NAME varchar (), age INT);
CREATE TABLE ject (ID varchar () primary key, NAME varchar (30));
CREATE TABLE SJ (studid varchar), Subjectid varchar (32));
Add constraints individually (note order: to first add a union primary key, then add a foreign key)//Create a federated primary key ALTER TABLE SJ add CONSTRAINT pk_sj PRIMARY key (Studid,subjectid); Create two foreign keys ALTER TABLE SJ ADD constraint Fk_stud foreign key (Studid) references StuD (ID);

ALTER TABLE SJ ADD constraint FK_SUBJ foreign key (Subjectid) references ject (ID); Delete FOREIGN Key---when you add a foreign key and then add a federated primary key.
Delete the foreign key, again ALTER TABLE SJ DROP FOREIGN key Fk_stud;



ALTER TABLE SJ DROP FOREIGN KEY fk_subj;
INSERT into stud VALUES (' P001 ', ' Floret ', 25);
INSERT into stud VALUES (' P002 ', ' Jack ', 23);
INSERT into stud VALUES (' P003 ', ' Tom ', 24);
INSERT into stud VALUES (' P004 ', ' John ', 24);

INSERT into stud VALUES (' P005 ', ' Zhaozilong ', 26);
INSERT into ject VALUES (' S001 ', ' Java ');
INSERT into ject VALUES (' S002 ', ' Java ee ');
INSERT into ject VALUES (' S003 ', ' XML ');
INSERT into ject VALUES (' S004 ', ' database ');

INSERT into ject VALUES (' S005 ', ' JQuery ');
INSERT into SJ VALUES (' P001 ', ' S001 ');
INSERT into SJ VALUES (' P001 ', ' S003 ');
INSERT into SJ VALUES (' P002 ', ' S001 ');
INSERT into SJ VALUES (' P002 ', ' S002 ');
INSERT into SJ VALUES (' P002 ', ' S003 ');
INSERT into SJ VALUES (' P003 ', ' S001 ');
INSERT into SJ VALUES (' P004 ', ' S002 '); INSERT into SJ VALUES (' P004 ', ' S003 ');

Association query:

SQL Enhanced Association (JOIN): Left association (right join) (inner join) in-link (a.) MySQL does not support: fully associative (full join) External Association (outter join)/association, can be understood as a few Synthesize a new table, and then query in the new table//1 which courses//92 standard select Stud.name,ject.name from STUD,JECT,SJ where Stud.id=sj.studid and ject.i 
D = Sj.subjectid;  96 Standard Select Stud.name,ject.name from stud INNER join SJ on Stud.id=sj.studid INNER join ject on Ject.id

= Sj.subjectid;  
2 query who did not select the class//92 standard select Stud.name from stud WHERE stud.id not in (SELECT Studid from SJ); 96 standard--left association SELECT stud.name from Stud ieft join SJ on Stud.id=sj.studid to ject on ject.id = Sj.s


Ubjectid WHERE Ject.name is NULL;   3 query which courses are not candidates//96 standard--left association SELECT Ject.name from Ject to the "SJ on Ject.id=sj.subjectid", Stud on
Stud.id = Sj.studid WHERE stud.name is NULL; 96 Standard--Right association SELECT ject.name,stud.name from Stud right-wing join SJ on Stud.id=sj.studid right-hand join ject on JE Ct.id = Sj.subjectid WHERE stud.nameis NULL;
※ Demo automatic growth column and field value uniqueness constraint CREATE TABLE AA (ID INT auto_increment PRIMARY KEY, NM VARCHAR () unique); INSERT into AA (nm) VALUES (' UUU ');