table of Contents
1 Multi-table joint check join
At the beginning of using multi-table joint search, we record the order in which various keywords should be placed
from --> where --> group by --> having --> select --> order by
The essence of multi-table query is to generate a Cartesian product based on the query table, and then filter the data. Regardless of whether it is using relational query or join query, their essence is the same.
Now we have two experiment tables.
When we need all of Zhang San's information (including class details), we build a query like this.
select id,s.name,age,money,birth,s.class_no,c.name,english_name from student as s, class as c where s.class_no=c.class_no and s.name="张三";
But when there are a lot of data tables, the logic will be very confusing, so sql uses join to standardize this multi-table joint check.
select id,s.name,age,money,birth,s.class_no,c.name,english_name from student as sinner join class as con c.class_no=s.class_nowhere s.name="张三"；
Join is roughly divided into three types
inner join: Only the records on both sides of the JOIN are displayed, and the others are removed.
Left join: All data in the table on the left of the JOIN appears, and no data on the right is filled with NULL.
right join: All data in the table on the right of the JOIN appears, and no data on the left is filled with NULL.
In order to more intuitively reflect the difference between the three methods, we insert a few abnormal data.
insert into student(name,age,money,birth,class_no) values("张三",12,12.11,"2003-12-12",3); //在student插入一个不存在的class编号insert into class(class_no,name,english_name) values(4,"中文四班","Chinese class four"); //在class插入一条不存在学生的class。
select id,s.name,age,money,birth,s.class_no,c.name,english_name from student as sinner join class as con c.class_no=s.class_no;
We searched out the detailed information of all the students in the class.
Sometimes we want to know the information of all students, regardless of whether it has a legal class.
select id,s.name,age,money,birth,s.class_no,c.name,english_name from student as sleft join class as con c.class_no=s.class_no;
Sometimes we want to know the details of the students in all classes, regardless of whether there are students in the class, we need to know that there is this class.
select id,s.name,age,money,birth,s.class_no,c.name,english_name from student as sright join class as con c.class_no=s.class_no;
Through such an example, the difference between the three joins has been clearly shown.
Inner join is displayed when there is corresponding data on both sides
The left join must display the data on the left in addition to the corresponding data on both sides.
In right join, in addition to the corresponding data on both sides, the data on the right must be displayed.
Use pictures to deepen your impression.
2 Nested query
Nested query is the current main query can also nest one or more sub-queries. Use the result set of the subquery as the query condition of the main query.
We want to know Zhang San’s class information. We used to do this
select class_no,name,english_namefrom classinner join studenton class.class_no=student.class_nowhere student.name="张三"
We use nested queries to achieve this function.
select class_no,name,english_name from classwhere class_no=( select class_no from student where name="张三")
To find things in multiple tables using nested query logic I think is more complicated than other methods.
3 Self-join query
Sometimes, we will encounter some data that puts the hierarchical relationship in a table. We need to manually write query statements to get the data based on their hierarchical relationship. At this time, self-join query is a suitable way.
We have a data table test like this
In this table, we judge the hierarchical relationship between them based on their own cid and parent id.
There are two levels of this relationship. The first level is data with pid of 1. The second layer is a subclass of their cid as pid.
We can split them into two tables to compare the hierarchical relationship.
We now use the self-join query statement to display all the secondary information.
SELECT t1.cid,t1.name,t1.pid,t2.name AS pnameFROM test AS t1, test AS t2WHERE t1.pid=t2.cid
In this way, we will query the data table.
This is a simple self-join query.