MySQL串連查詢,MySQL串連

來源:互聯網
上載者:User

MySQL串連查詢,MySQL串連
串連查詢1、內連結查詢

內串連INNER JOIN使用比較子進行表間某些列資料的比較操作,並列出這些表中與串連條件相匹配的資料行組合成新的記錄。

假設有如下兩張表student,teacher

mysql> select * from student;

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

| id | name | sex | class | math | english |

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

|  1 | 張三 | 男  | 班級1 |   90 |      91 |

|  2 | 李四 | 男  | 班級2 |   88 |      86 |

|  3 | 王五 | 女  | 班級3 |   92 |      88 |

|  4 | 趙六 | 女  | 班級1 |   79 |      80 |

|  5 | 孫七 | 女  | 班級2 |   91 |      96 |

|  6 | 李八 | 男  | 班級3 |   90 |      89 |

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

 

 

mysql> select * from teacher;

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

| id | name   | class |

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

|  1 | 張老師 | 班級1 |

|  2 | 李老師 | 班級2 |

|  3 | 王老師 | 班級3 |

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

 

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

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

| name | class | name   |

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

| 張三 | 班級1 | 張老師 |

| 李四 | 班級2 | 李老師 |

| 王五 | 班級3 | 王老師 |

| 趙六 | 班級1 | 張老師 |

| 孫七 | 班級2 | 李老師 |

| 李八 | 班級3 | 王老師 |

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

 

使用內連結查詢的方式為:

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

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

| name | class | name   |

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

| 張三 | 班級1 | 張老師 |

| 李四 | 班級2 | 李老師 |

| 王五 | 班級3 | 王老師 |

| 趙六 | 班級1 | 張老師 |

| 孫七 | 班級2 | 李老師 |

| 李八 | 班級3 | 王老師 |

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

 

在一個串連查詢中,如果涉及到的兩個表都是同一個表,這種查詢稱為字串連查詢。字串連查詢是一種特殊的內連結,他是指相互串連的表在物理上為同一張表,但可以在邏輯上分為兩張表

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

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

| id | name |

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

|  1 | 張三 |

|  4 | 趙六 |

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





相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.