MySql runs from entry to database deletion (3) | advanced table query

table of Contents

1 Multi-table joint check join

2 Nested query

3 Self-join query


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

Cartesian Product:

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.

student

class

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。

inner join

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.

left join

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;

right join

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

cidnamepid
2information Technology1
3Software development1
4database3
5art design1
6web development3
7ps technology5
8Office information2

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.

First level

cidnamepid
2information Technology1
3Software development1
5art design1

Level 2

cidnamepid
4database3
6web development3
7ps technology5
8Office information2

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.