MySQL multi-Table Query example

Note: Multi-table queries and connection queries (left JOIN, etc.) function Similarly, without much difference.

Create DATABASE Test charset UTF8 collate utf8_bin;
Use test;
CREATE TABLE Tb_chengji (
Xuesheng varchar () NOT NULL default ' primary key,
Yuwen Int (10)
) Charset=utf8;

INSERT into Tb_chengji values (' Henderson ', 90), (' Lallana ', 100), (' mane ', 115), (' Coutinho ', 99);

CREATE TABLE Tb_info (
ID Int (TEN) NOT null primary key auto_increment,
Xuesheng varchar (TEN) NOT null default ' ',
Xingbie char (1) NOT null default ' ',
Dizhi varchar (TEN) Select Tb_info.id,tb_chengji.xuesheng,xingbie,dizhi,yuwen from Tb_chengji,tb_info where tb_ Chengji.xuesheng=tb_info.xuesheng and tb_chengji.yuwen>=100; Not null default ' '
) Charset=utf8;

INSERT into Tb_info values (1, ' mane ', ' male ', ' winger '), (2, ' Coutinho ', ' Male ', ' left midfield '), (3, ' Lallana ', ' female ', ' midfield '), (4, ' Henderson ', ' female ', ' lower midfield ');

Multi-Table Query example:
Example 1

Select Tb_info.id,tb_chengji.xuesheng,xingbie,dizhi,yuwen from Tb_chengji,tb_info where tb_chengji.xuesheng=tb_ Info.xuesheng and tb_chengji.yuwen>=100;
+----+-----------+---------+--------+-------+
| ID | Xuesheng | Xingbie | Dizhi | Yuwen |
+----+-----------+---------+--------+-------+
| 1 | Mane | Male | Winger | 115 |
| 3 | Lallana | Women | Midfield | 100 |
+----+-----------+---------+--------+-------+
2 rows in Set (0.08 sec)

Example 2

Select Tb_info.id,tb_chengji.xuesheng,xingbie,dizhi,yuwen from Tb_chengji,tb_info where tb_chengji.yuwen>= ;
+----+-----------+---------+-----------+-------+
| id | xuesheng | xingbie | dizhi | yuwen |
+----+-----------+---------+-----------+-------+
| 1 | lallana | man | winger |
| 1 | mane | men | winger |
| 2 | lallana | male | left midfield |
| 2 | mane | male | left midfield |
| 3 | lallana | female | midfield |
| 3 | mane | female | midfield |
| 4 | lallana | female | lower midfield |
| 4 | mane | female | midfield |
+----+-----------+---------+-----------+-------+
8 rows in Set (0.00 sec)

Example 1 Tb_chengji.xuesheng=tb_info.xuesheng the table Tb_chengji and Tb_info together, called the equivalent connection.

Example 2 If you do not use Tb_chengji.xuesheng=tb_info.xuesheng, the result will be a Cartesian product of two tables, called the full connection.

MySQL multi-Table Query example