Mysql inner join on的用法實例(必看)。本站提示廣大學習愛好者:(Mysql inner join on的用法實例(必看))文章只能為提供參考,不一定能成為您想要的結果。以下是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的用法實例(必看)就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持。