Knowledge Points of this Blog
1. Three simple ways of association: left association, right association and inlining
2. Demonstrating multi-table queries and associated queries with examples
3.1-to-1 database table design, 1-to-multi database table design and multi-to-multi database table design

Three ways of association: left association, right association and inlining

Left join: Left table is dominant (left table remains intact, adding records that depend on the right and left tables to form new tables)
Right join: The right table is dominant (the right table remains intact, adding records that depend on the left table and the right table to form new tables)
Internal join: Extract the records associated with the two tables separately to form a new table
Sketch Map
Left correlation:

Right correlation

inline

Take the example of multiple cars owned by different owners with different actual tables.

The virtual tables generated by the left association are as follows

The virtual tables generated by the right association are as follows

The virtual tables generated inline are as follows

Example demonstrates multi-table query and association query

Now there are two tables. Vehicle owner information table and vehicle information table. pid in car is the foreign key.
person table

car table

Requirement 1: Check the number of car owners whose number of vehicles is greater than or equal to two

SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;

Query who owns two or more cars
Mode 1 General Query

SELECT person.pname,car.cname,car.cid,car.price FROM person,car WHERE person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2) AND person.pid=car.pid;

Mode 2 - Relevant Query

SELECT person.pname,car.cname,car.cid,car.price FROM person INNER JOIN car ON person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2) AND person.pid=car.pid;
SELECT person.pname,car.cname, car.cid,car.price FROM person INNER JOIN car ON person.pid= car.pid WHERE person.pid IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2);

Find out which car owners don't have a car
Mode I

SELECT person.pname,person.pid FROM person WHERE person.pid NOT IN(SELECT pid FROM car);

Mode 2: Relevance

SELECT person.pname,person.pid FROM person LEFT JOIN car ON person.pid= car.pid WHERE car.pid IS NULL;

Mode 3: Right Association

SELECT person.pname,person.pid FROM car RIGHT JOIN person ON person.pid = car.pid WHERE car.pid IS NULL;

Find out which owners own cars
Mode I

SELECT person.pname,car.cname, car.price FROM person,car WHERE person.pid= car.pid;

Mode 2

SELECT person.pname,car.cname, car.price FROM person INNER JOIN car ON person.pid= car.pid;

1-to-1 database table design, 1-to-multi database table design and multi-to-multi database table design

Design of 1-to-1 database tables
Needs: A one-to-one relationship chart

☆Law 1:Separate two separate tables
create table wife(
  id int primary key,
  name varchar(10),
  sex char(1)
);

create table husband(
  id int primary key,
  name varchar(10),
  sex char(1),
  wid int unique,
  constraint husband_fk foreign key(wid) references wife(id)
); //Reflect one-to-one through constraint constraints

☆Law 2:Merge in a separate table
create table person(
  id int primary key,
  name varchar(10),
  sex char(1),
  wife int,
  husband int
);
insert into person values(1,'Floret','0', 0,3);
insert into person values(2,'Yu Fen','0', 0,4);
insert into person values(3,'Zhang San','1', 1,0);
insert into person values(4,'Li Si','1', 2,0);
insert into person values(5,'Wang Wu','0', 0,0);

//Reflect one-to-one relationship by creating views separately
create view women as select * from person where sex='0';
create view men as select * from person where sex='1';

Design of 1 pair of multi-database tables
Demand: One person owns more than one car
Database Design

Scheme 1 (poor design)
Number, Name, Sex, Age, Vehicle Number, Vehicle Volume Price
 P001 Jack Male 25C001 BMW 12L 80W
 P001 Jack Male 25 C002 Benz 12L 100W
 P001 Jack Male 25 C003 Benz 12L 100W
 P002 Tom Male 26 C004 BMW 12L 80W
 P002 Tom Male 26 C005 Benz 10L 60W
 P003 Rose Female 24 C006 Adio 10L 70W

Plan 2 (Good Design)
1) Formulating "one party" separately
 Number, Name, Sex and Age.
P001 Jack Male 25..
P002 Tom Male 26..
P003 Rose Woman 24...

2) Make a table for "multi-party" (depending on "one party": by using foreign key (filling a field)
Foreign key: The table on which the dependency is located, which is the primary key of the dependent table.

Car Number    Vehicle model displacement price owner
C001      BMW    12L    80w    P001
C002      Benz   12L    100w   P001
C003      Benz   12L    100w   P001
C004      BMW    12L    80w    P002
C005      Benz   10L    60w    P002
C006      Adio   10L    70w    P003

3) Implementation of SQL Code

create table person2(
  id varchar(32) primary key,
  name varchar(30),
  sex char(1),
  age int
);
//insert record
insert into person2 values('P1001','Floret','0',25);
insert into person2 values('P1002','Yu Fen','0',24);
insert into person2 values('P1003','Tom','1',25);
insert into person2 values('P1004','Rose','0',23);

create table car(
  id varchar(32) primary key,
  name varchar(30),
  price numeric(10,2),
  pid varchar(32),
  constraint car_fk foreign key(pid) references person2(id)
);
insert into car values('C001','BMW',82.5, 'P1001');
insert into car values('C002','BMW',111.5, 'P1001');
insert into car values('C003','Benz',78.5, 'P1001');
insert into car values('C004','BMW',55.5, 'P1002');
insert into car values('C005','Audio',82.5, 'P1002');
insert into car values('C006','QQ',6.5, 'P1003');
insert into car(id,name,price) values('C007','ABC',6.6);

Design of multi-pair and multi-database tables
Demand citation: student-curriculum relationship

1. Database design (poor design)
1) Student table

Number, Name, Sex, Age, Telephone Address.
P001 Jack male 23 135 *** ** *...
P002 Tom Male 24 139 *** ** *...

2) Course (elective) schedule

Course Name Student Publishing House Price.
S001 Java P001 Electronics Industry 40..
S001 Java P002 Electronics Industry 40..
...
S002 database P001 Tsinghua University 35..
S002 database P002 Tsinghua University 35..

2. Database Design (Good Design Scheme)
1) Student table (entity table is built independently, numbering field is the main key)

Number, Name, Sex, Age, Telephone Address.
P001 Jack male 23 135 *** ** *...
P002 Tom Male 24 139 *** ** *...

2) Course schedule (entity table is built independently, numbering field is the main key)

Course Name Publishing House Price
 S001 Java Electronics Industry 40...
S002 database Tsinghua University 35.

3) Course Selection Schedule (set up a relationship table for the relationship between two entities and set up a joint primary key)
Course Number Student Number

S001      P001
S001      P002
S002      P001
S002      P002
 Foreign key foreign key
 ┗━━-------━━━┛
      ┃
    composite keys

3) Code Implementation

create table stud2(
  id varchar(32) primary key,
  name varchar(30),
  age int
);
create table ject(
  id varchar(32) primary key,
  name varchar(30)
);
create table sj(
  studid varchar(32),
  jectid varchar(32)
);

//Establish joint primary key: first add foreign key and then joint primary key, no way!!!
alter table sj add constraint fk_stud foreign key(studid) 
      references stud2(id);
alter table sj add constraint fk_ject foreign key(jectid) 
      references ject(id);
alter table sj add constraint pk_sj primary key(studid,jectid);

//Delete foreign keys
alter table sj drop foreign key fk_stud;
alter table sj drop foreign key fk_ject;

//Establish the correct sequence of joint primary keys: first add joint primary keys, then add foreign keys