MySQL connection query and MySQL connection

MySQL connection query and MySQL connection
Connection query 1. Internal link query

Inner join uses a comparison operator to compare data in certain columns between tables, and lists the data rows matching the connection conditions in these tables to form a new record.

Suppose there are two tables, student and teacher.

Mysql> select * from student;

+ ---- + ------ + ----- + ------- + ------ + --------- +

| Id | name | sex | class | math | english |

+ ---- + ------ + ----- + ------- + ------ + --------- +

| 1 | Zhang San | male | Class 1 | 90 | 91 |

| 2 | Li Si | male | Class 2 | 88 | 86 |

| 3 | Wang Wu | female | Class 3 | 92 | 88 |

| 4 | Zhao Liu | female | Class 1 | 79 | 80 |

| 5 | Sun Qi | female | Class 2 | 91 | 96 |

| 6 | LI Ba | male | Class 3 | 90 | 89 |

+ ---- + ------ + ----- + ------- + ------ + --------- +

 

 

Mysql> select * from teacher;

+ ---- + -------- + ------- +

| Id | name | class |

+ ---- + -------- + ------- +

| 1 | instructor Zhang | Class 1 |

| 2 | instructor Li | Class 2 |

| 3 | instructor Wang | Class 3 |

+ ---- + -------- + ------- +

 

Mysql> select student. name, student. class, teacher. name from student, teacher where student. class = teacher. class;

+ ------ + ------- + -------- +

| Name | class | name |

+ ------ + ------- + -------- +

| Michael Zhang | Class 1 | Mr. Zhang |

| Li Si | Class 2 | Miss Li |

| Wang Wu | Class 3 | Mr. Wang |

| Zhao 6 | Class 1 | instructor Zhang |

| Sun Qi | Class 2 | instructor Li |

| LI Ba | Class 3 | instructor Wang |

+ ------ + ------- + -------- +

 

The internal link query method is as follows:

Mysql> select student. name, student. class, teacher. name from student inner join teacher on student. class = teacher. class;

+ ------ + ------- + -------- +

| Name | class | name |

+ ------ + ------- + -------- +

| Michael Zhang | Class 1 | Mr. Zhang |

| Li Si | Class 2 | Miss Li |

| Wang Wu | Class 3 | Mr. Wang |

| Zhao 6 | Class 1 | instructor Zhang |

| Sun Qi | Class 2 | instructor Li |

| LI Ba | Class 3 | instructor Wang |

+ ------ + ------- + -------- +

 

In a join query, if the two tables involved are both the same table, this query is called a join query. A word join query is a special internal link. It refers to a physically the same table for a joined table, but it can be logically divided into two tables.

Mysql> select distinct s1.id, s1.name from student as s1, student as s2 where s1.class = s2.class and s2.class = 'class 1 ';

+ ---- + ------ +

| Id | name |

+ ---- + ------ +

| 1 | James |

| 4 | Zhao Liu |

+ ---- + ------ +