这里有新鲜出炉的 Mysql 教程,程序狗速度看过来!
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
下面小编就为大家带来一篇 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 的用法实例 (必看) 就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持 PHPERZ。
来源: http://www.phperz.com/article/17/0609/332626.html