語(yǔ)法規(guī)則
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
先創(chuàng)建兩個(gè)表，1.用戶，2.用戶類(lèi)別
用戶表
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;
用戶類(lèi)別表
CREATE TABLE `userkind` (
`id` int(32) NOT NULL AUTO_INCREMENT,
`kindname` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
插入一些數(shù)據(jù)到user表
INSERT INTO `user` VALUES (1,'小明',1),(2,'小紅',1),(3,'涵涵',2);插入一些數(shù)據(jù)到 userkind表
INSERT INTO `userkind` VALUES (1,'普通會(huì)員'),(2,'VIP會(huì)員');
如圖：
下面是控制臺(tái)的查詢例子：
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 | 普通會(huì)員 |
| 2 | VIP會(huì)員 |
+----+----------+
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 | 普通會(huì)員 |
| 2 | 小紅 | 1 | 1 | 普通會(huì)員 |
| 3 | 涵涵 | 2 | 2 | VIP會(huì)員 |
+----+------+--------+----+----------+
3 rows in set (0.02 sec)
mysql> select `id` as `用戶ID`,`name` as `用戶名`,`kindname` as `用戶類(lèi)別` 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 `用戶類(lèi)別
` from
-> `user` inner join `userkind` where `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| 用戶ID | 用戶名 | 用戶類(lèi)別 |
+--------+--------+----------+
| 1 | 小明 | 普通會(huì)員 |
| 2 | 小紅 | 普通會(huì)員 |
| 3 | 涵涵 | VIP會(huì)員 |
+--------+--------+----------+
3 rows in set (0.00 sec)
mysql> select `user`.`id` as `用戶ID`,`name` as `用戶名`,`kindname` as `用戶類(lèi)別
` from `user` inner join `userkind` on `user`.`kindid`=`userkind`.`id`;
+--------+--------+----------+
| 用戶ID | 用戶名 | 用戶類(lèi)別 |
+--------+--------+----------+
| 1 | 小明 | 普通會(huì)員 |
| 2 | 小紅 | 普通會(huì)員 |
| 3 | 涵涵 | VIP會(huì)員 |
+--------+--------+----------+
3 rows in set (0.00 sec)
mysql>
需要注意的是： 這里的on 基本等價(jià)于where(本人感覺(jué))
當(dāng) column （字段） 兩個(gè)表都有 卻分不清時(shí)，需要用`表名`.`字段名` 進(jìn)行分辨。
as就是取別名了。看上面例子就知道！
以上這篇Mysql inner join on的用法實(shí)例(必看)就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
您可能感興趣的文章:- Mysql連接join查詢?cè)碇R(shí)點(diǎn)
- MySQL的join buffer原理
- 超詳細(xì)mysql left join,right join,inner join用法分析
- MySQL JOIN之完全用法
- MySQL優(yōu)化之使用連接(join)代替子查詢
- mysql多個(gè)left join連接查詢用法分析
- MYSQL數(shù)據(jù)庫(kù)基礎(chǔ)之Join操作原理
