Table association
concept
Table represents an entity in life, such as department table dept and employee table emp. Table association represents the relationship between tables, such as department and employee, commodity and commodity classification, teacher and student, classroom and student.

At the same time, we should also know that tables are not all related. They form their own small circle. For example, it may not matter if the goods and the details of the goods are circled, and the departments and employees are circled. For example, the goods have nothing to do with the employees, and the goods have nothing to do with the students.

Below, we discuss the relationship between tables, which can be divided into four types:

one to one QQ and QQ email, employee and employee number
one to many is the most common, departments and employees, users and orders
many to one many to many in turn, employees and departments, orders and users
many to many teachers and students, teachers and courses
 

USE cgb2108;
#Table design features:

#All tables end with s, identifying the plural
#Most fields start with the first letter of the table. It is convenient to identify which table field is in the multi table associated query

CREATE TABLE courses(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno));

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno));

CREATE TABLE students(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5),
PRIMARY KEY (sno));


CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10), 
PRIMARY KEY (tno)
courses);

#Insert test data

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'Zeng Hua' ,'male' ,'1977-09-01',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'Kuang Ming' ,'male' ,'1975-10-02',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'Wang Li' ,'female' ,'1976-01-23',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'Li Jun' ,'male' ,'1976-02-20',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'Wang Fang' ,'female' ,'1975-02-10',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'Lu Jun' ,'male' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'Yi Tian','male','1958-12-02','associate professor','Computer Department');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'Wang Xu','male','1969-03-12','lecturer','Department of Electronic Engineering');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'Li Ping','female','1972-05-05','assistant','Computer Department');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'Bing Chen','female','1977-08-14','assistant','Department of Electronic Engineering');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'Introduction to computer',825);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'operating system' ,804);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'Analog circuit' ,856);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'probability theory' ,831);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'Advanced mathematics' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);

Multi table join t query ---- Cartesian product, three kinds of connections, sub query

Cartesian product
Multi table query refers to a query based on two or more tables. In practical applications, querying a single table may not meet your needs. For example, the employee table emp displays not only deptno, but also the Department name, and the Department name dname is in the dept table.

It is worth noting that in the actual development, multi table joint query is used as little as possible. This is the fundamental reason. In the query process, a large result set is built in memory, and then the data is filtered. The construction process and the memory resources used, including the judgment during filtering, are both resource-consuming and time-consuming.

This is why the Alibaba specification prohibits associated queries on more than three tables:

Three kinds of join
inner join
Left (outer) join
right join
Only those with records on both sides of the INNER JOIN are displayed, and the others are removed
The data in the table on the left of the LEFT JOIN appears, and there is no data on the right, which is filled with NULL
The data in the right table of RIGHT JOIN appears, and no data on the left is filled with NULL

Subquery subquery   
concept
Subquery refers to the select statement embedded in other select statements, also known as nested query. Sub query execution efficiency is low. Use with caution. When there are few records, it has little impact on the efficiency, and the diagram is convenient for direct use. When there are many records, it is best to use other methods instead.

Single line subquery=
The returned result is a
Multiline subquery in
in subquery
 

##Query Li Jun's total score
 #Cartesian product
 SELECT SUM(scores.degree) FROM students,scores 
 WHERE scores.sno=students.sno AND sname='Li Jun' 
 #join query
 SELECT SUM(scores.degree) FROM students JOIN scores 
 ON scores.sno=students.sno WHERE sname='Li Jun'
 #Subquery
 SELECT SUM(degree) FROM scores WHERE sno=(SELECT sno FROM students WHERE sname='Li Jun');
 
 ##Practice querying the course names that Chen Bing can explain
 #Cartesian product
SELECT courses.cname FROM teachers,courses 
WHERE teachers.tno=courses.tno AND tname='Bing Chen';
#join query
SELECT courses.cname FROM teachers JOIN courses ON teachers.tno=courses.tno WHERE tname='Bing Chen';
#Subquery
SELECT courses.cname FROM courses WHERE tno=(SELECT tno FROM teachers  WHERE  tname='Bing Chen')