語法規(guī)則
|
1
2
3
4
|
SELECT column_name(s)FROM table_name1INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
先創(chuàng)建兩個(gè)表，1.用戶，2.用戶類別
用戶表
|
1
2
3
4
5
6
|
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; |
用戶類別表
|
1
2
3
4
5
|
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,'普通會員'),(2,'VIP會員');
如圖：
下面是控制臺的查詢例子：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
Enter password: ****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use join;Database changedmysql> 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 ambiguousmysql> 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 基本等價(jià)于where(本人感覺)
當(dāng) column （字段） 兩個(gè)表都有 卻分不清時(shí)，需要用`表名`.`字段名` 進(jìn)行分辨。
as就是取別名了?？瓷厦胬泳椭?！
以上這篇Mysql inner join on的用法實(shí)例(必看)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。
