各位用户为了找寻关于Mysql inner join on的用法实例(必看)的资料费劲了很多周折。这里教程网为您整理了关于Mysql inner join on的用法实例(必看)的相关资料，仅供查阅，以下为您介绍关于Mysql inner join on的用法实例(必看)的详细内容

语法规则

? 1 2 3 4 SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

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

用户表

? 1 2 3 4 5 6 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;

用户类别表

? 1 2 3 4 5 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会员');

如图：

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

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 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的用法实例(必看)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。