程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql inner join on的用法實例(必看)

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

編輯:MySQL綜合教程

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的用法實例(必看)就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved