编程学习资讯网

Mysql inner join on的用法实例--编程学习网

发布时间:2017-04-20 08:51:56   来源:本站编辑   浏览次数:

语法规则

?
1
2
3
4
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

先创建两个表,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;

插入一些数据到user表

INSERT INTO `user` VALUES (1,'小明',1),(2,'小红',1),(3,'涵涵',2);插入一些数据到 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 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就是取别名了。看上面例子就知道!

编程学习网 http://www.javalearns.cn

关注微信号:javalearns   随时随地学Java

或扫一扫

随时随地学Java