Mysql多表查询

多表查询

inner join

//准备工作先创建两张表
//创建表testa
mysql> create table testa(id int(10) not null,name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> desc testa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

//创建表testb
mysql> create table testb(id int(20) not null,age tinyint(4) not null,pay int(20) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> desc testb;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(20)    | NO   |     | NULL    |       |
| age   | tinyint(4) | NO   |     | NULL    |       |
| pay   | int(20)    | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//插入数据到testa和testb
mysql> insert testa(id,name) values(1,'tom'),(2,'jerry'),(3,'lisi');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert testb(id,age,pay) values(1,20,1000),(2,21,2000),(5,10,3000);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testa ;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
|  3 | lisi  |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from testb ;
+----+-----+------+
| id | age | pay  |
+----+-----+------+
|  1 |  20 | 1 |
|  2 |  21 | 2 |
|  5 |  10 | 5 |
+----+-----+------+
3 rows in set (0.00 sec)

//使用inner join
mysql> select testa.*,testb.* from testa inner join testb on testa.id = testb.pay;
+----+-------+----+-----+-----+
| id | name  | id | age | pay |
+----+-------+----+-----+-----+
|  1 | tom   |  1 |  20 |   1 |
|  2 | jerry |  2 |  21 |   2 |
+----+-------+----+-----+-----+
2 rows in set (0.00 sec)

left join

//表testa和testb内容和上述使用inner join时内容一致
//使用left join
mysql> select testa.*,testb.* from testa left join testb on testa.id = testb.pay;
+----+-------+------+------+------+
| id | name  | id   | age  | pay  |
+----+-------+------+------+------+
|  1 | tom   |    1 |   20 |    1 |
|  2 | jerry |    2 |   21 |    2 |
|  3 | lisi  | NULL | NULL | NULL |
+----+-------+------+------+------+
3 rows in set (0.00 sec)
//left join以左边表为主，右边的表和以左边为主的表所匹配不到的显示为空

right join

//表testa和testb内容和上述使用inner join时内容一致
//使用right join
mysql> select testa.*,testb.* from testa right join testb on testa.id = testb.pay;
+------+-------+----+-----+-----+
| id   | name  | id | age | pay |
+------+-------+----+-----+-----+
|    1 | tom   |  1 |  20 |   1 |
|    2 | jerry |  2 |  21 |   2 |
| NULL | NULL  |  3 |  10 |   5 |
+------+-------+----+-----+-----+
3 rows in set (0.00 sec)
//right join以右边表为主，左边的表和以右边为主的表所匹配不到的显示为空

group by

• group by一般和函数count、sum、avg一起使用
• 出现在select后面的字段一般是函数中的或者group by中的
• 如果筛选可以使用where