mysql中inner join on_MYSQL数据库Mysql inner join on的用法实例(必看)

《MYSQL数据库Mysql inner join on的用法实例(必看)》要点：

本文介绍了MYSQL数据库Mysql inner join on的用法实例(必看)，希望对您有用。如果有疑问，可以联系我们。

MYSQL入门语法规则

MYSQL入门

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

MYSQL入门先创建两个表,1.用户,2.用户类别

MYSQL入门用户表

MYSQL入门

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;

MYSQL入门用户类别表

MYSQL入门

CREATE TABLE `userkind` (

`id` int(32) NOT NULL AUTO_INCREMENT,

`kindname` varchar(16) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MYSQL入门插入一些数据到user表

MYSQL入门INSERT INTO `user` VALUES (1,'小明',1),(2,'小红',1),(3,'涵涵',2);插入一些数据到 userkind表

MYSQL入门INSERT INTO `userkind` VALUES (1,'普通会员'),(2,'VIP会员');

MYSQL入门如图：

MYSQL入门

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

MYSQL入门

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>

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

MYSQL入门当 column (字段) 两个表都有 却分不清时,需要用`表名`.`字段名` 进行分辨.

MYSQL入门as就是取别名了.看上面例子就知道!

MYSQL入门以上这篇Mysql inner join on的用法实例(必看)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持维易PHP.