Mysql inner join on的用法实例(必看)

语法规则

select column_name(s)
from table_name1
inner join table_name2 
on table_name1.column_name=table_name2.column_name

先创建两个表，1.用户，2.用户类别

用户表

create table `user` (
 `id` int(32) not null auto_increment,
 `name` varchar(16) not null,
 `kindid` int(32) not null,
 primary key (`id`)
) engine=myisam default charset=utf8;

用户类别表

create table `userkind` (
 `id` int(32) not null auto_increment,
 `kindname` varchar(16) not null,
 primary key (`id`)
) engine=myisam default charset=utf8;

插入一些数据到user表

insert into `user` values (1,'小明',1),(2,'小红',1),(3,'涵涵',2);插入一些数据到 userkind表

insert into `userkind` values (1,'普通会员'),(2,'vip会员');

如图：

下面是控制台的查询例子：

enter password: ****
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 2
server version: 5.5.40 mysql community server (gpl)

copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.

oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.

type 'help;' or '\h' for help. type '\c' to clear the current input statement.

mysql> use join;
database changed
mysql> select * from `user`;
+----+------+--------+
| id | name | kindid |
+----+------+--------+
| 1 | 小明 |   1 |
| 2 | 小红 |   1 |
| 3 | 涵涵 |   2 |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> select * from `userkind`;
+----+----------+
| id | kindname |
+----+----------+
| 1 | 普通会员 |
| 2 | vip会员 |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from `user` inner join `userkind` on user.kindid=userkind.id;
+----+------+--------+----+----------+
| id | name | kindid | id | kindname |
+----+------+--------+----+----------+
| 1 | 小明 |   1 | 1 | 普通会员 |
| 2 | 小红 |   1 | 1 | 普通会员 |
| 3 | 涵涵 |   2 | 2 | vip会员 |
+----+------+--------+----+----------+
3 rows in set (0.02 sec)

mysql> select `id` as `用户id`,`name` as `用户名`,`kindname` as `用户类别` from
`user` inner join `userkind` where user.kindid=userkind.id;
error 1052 (23000): column 'id' in field list is ambiguous
mysql> select `user`.`id` as `用户id`,`name` as `用户名`,`kindname` as `用户类别
` from
  -> `user` inner join `userkind` where `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| 用户id | 用户名 | 用户类别 |
+--------+--------+----------+
|   1 | 小明  | 普通会员 |
|   2 | 小红  | 普通会员 |
|   3 | 涵涵  | vip会员 |
+--------+--------+----------+
3 rows in set (0.00 sec)

mysql> select `user`.`id` as `用户id`,`name` as `用户名`,`kindname` as `用户类别
` from `user` inner join `userkind` on `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| 用户id | 用户名 | 用户类别 |
+--------+--------+----------+
|   1 | 小明  | 普通会员 |
|   2 | 小红  | 普通会员 |
|   3 | 涵涵  | vip会员 |
+--------+--------+----------+
3 rows in set (0.00 sec)

mysql>

需要注意的是： 这里的on 基本等价于where(本人感觉)

当 column （字段） 两个表都有 却分不清时，需要用`表名`.`字段名` 进行分辨。

as就是取别名了。看上面例子就知道！

以上这篇mysql inner join on的用法实例(必看)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。