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

SELECT column_name(s)FROM table_name1INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

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;

Enter password: ****Welcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> use join;Database changedmysql> 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 ambiguousmysql> 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>