欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-14 15:14:22
语法规则 select column_name(s) from table_name1 inner join table_name2 on table_...

语法规则

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会员');

如图:

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

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

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的用法实例(必看)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。