1. Preface
In SQL development, multi table associated query is a skill that cannot be bypassed. The same query results are written in different ways, and their operation efficiency is also very different.
In the actual development, I have seen (it seems to have written ~) many long and smelly query SQL. It is common to check the data for more than ten minutes.
Therefore, if you have a deep understanding of SQL's multi table query mechanism and write less slow queries, you should be able to get less scolding.
2. Equivalent connection and non equivalent connection
2.1 equivalent connection
Equivalent join is the most basic and simplest kind of multi table query. Its value is the Cartesian product of all conditions.
After from, the value of which table appears first in the previous results, as follows:
select *
from student,
family
where student.family_id = family.id;
In the latest Java development manual released by Alibaba, it is mandatory that as long as multiple tables are involved, the alias (or table name) of the table must be added before the column name for qualification
2.2 non equivalent connection
Non equivalent connection is carried out by the value in table a in a certain range in table b, which can well meet the preset segmented statistical requirements.
There are two ways to write non equivalent connections. Use between and... Or greater than or less than
-- The first way to write: use between...and...
select a.discipline_name, a.score, b.grade_tag
from achievement a,
achievement_grade b
where a.score between b.lowest_score and b.highest_score;
-- The second way is to use>=or<=
select a.discipline_name, a.score, b.grade_tag
from achievement a,
achievement_grade b
where a.score >= b.lowest_score
and a.score <= b.highest_score;
3. Self connection and non self connection
3.1 self connection
Self connection, as the name suggests, is to connect the same table with itself. In order to distinguish, you need to give different aliases to the table. For example, a score sheet needs to query all data with scores higher than "language":
If you do not use self connection, you need to query the score of Chinese first, and then query the data greater than this score.
For details, you can follow the following steps:
-- First query the score of Chinese select score from achievement where discipline_name = 'language'; -- Then query the data with higher scores than Chinese select * from achievement where score > 76;
Using self connection, you can complete the query in one sq statement:
select a.*
from achievement a,
achievement b
where b.discipline_name = 'language'
and a.score > b.score;
3.2 non self connection
Except self connection, others are called non self connection~~~
4. Internal and external connections
The distinction between inner connection and outer connection is essentially another classification method. For example, inner connection is equivalent connection.
- Inner join: merge rows of two or more tables with the same column. The result set does not contain rows that do not match one table with another
External join: in addition to the rows that meet the join conditions, the two tables also return the rows that do not meet the conditions in the left (or right) table
Row, this connection is called left (or right) outer connection. When there is no matching row, the corresponding column in the result table is empty (NULL).
- Left outer connection: in connection conditions, the left table is also called the master table, and the right table is called the slave table.
- Right outer connection: in connection conditions, the table on the right is also called the master table, and the table on the left is called the slave table.
- Total external connection
4.1 test data
The data of student table and family table are as follows:
4.2 left outer connection
-- Find out student For all the data in the, the unsatisfied data is displayed as null
-- here student in front
select a.*
from student a
left join family b on a.family_id = b.id
4.3 right outer connection
-- Find out student For all the data in the, the unsatisfied data is displayed as null
-- here student rearwards
select a.*
from family b
right join student a on b.id = a.family_id;
4.4 all external connection
Unfortunately, MySQL does not support all external connections.
Appendix: test data SQL script
-- auto-generated definition
create table student
(
id int auto_increment
primary key,
student_id int null comment 'Student number',
student_name varchar(40) null comment 'full name',
family_id int null comment 'family ID',
create_time datetime default CURRENT_TIMESTAMP null comment 'Creation time'
)
comment 'Student list';
create table family
(
id int auto_increment
primary key,
family_name varchar(40) null comment 'Family name',
family_address varchar(40) null comment 'Home address',
create_time datetime default CURRENT_TIMESTAMP null comment 'Creation time'
)
comment 'Family table';
create table achievement
(
id int auto_increment
primary key,
score int null comment 'fraction',
discipline_name varchar(40) null comment 'Discipline name',
student_id int null comment 'Student number'
)
comment 'Transcript';
create table achievement_grade
(
id int auto_increment
primary key,
grade_tag varchar(10) null comment 'grade',
lowest_score int null comment 'Lowest score',
highest_score int null comment 'Highest score',
create_time datetime default CURRENT_TIMESTAMP null comment 'Creation time'
)
comment 'Score grade table';
INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (1, 'fail,', 0, 60, '2022-03-02 11:44:01');
INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (2, 'good', 60, 80, '2022-03-02 11:44:01');
INSERT INTO achievement_grade (id, grade_tag, lowest_score, highest_score, create_time) VALUES (3, 'excellent', 80, 100, '2022-03-02 11:44:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (1, 1, 'Zhang San', 1, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (2, 2, 'Li Si', 2, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (3, 3, 'Wang Wu', 3, '2022-03-02 09:55:01');
INSERT INTO student (id, student_id, student_name, family_id, create_time) VALUES (4, 4, 'Goofy', null, '2022-03-02 19:45:14');
INSERT INTO family (id, family_name, family_address, create_time) VALUES (1, 'Zhang Sanjia', 'Beijing', '2022-03-02 09:54:13');
INSERT INTO family (id, family_name, family_address, create_time) VALUES (2, 'Li Sijia', 'Shanghai', '2022-03-02 09:54:13');
INSERT INTO family (id, family_name, family_address, create_time) VALUES (3, 'Wang Wujia', 'Siberia', '2022-03-02 09:54:13');
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (1, 76, 'language', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (2, 80, 'mathematics', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (3, 65, 'English', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (4, 98, 'Geography', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (5, 77, 'history', 1);
INSERT INTO achievement (id, score, discipline_name, student_id) VALUES (6, 69, 'biology', 1);Wechat search: "deep sea Yunfan" pays attention to my number
Or add my wechat: hqzmss, pull you into the technical exchange group