数据准备
1、建2张表
# 姓名表
create table table_name(
id int(11) primary key auto_increment,
user_id int(11) default 0,
name varchar(5) default ''
);
# 年龄表
create table table_age(
id int(11) primary key auto_increment,
user_id int(11) default 0,
age int(11) default 0
);
2、原始数据
# user_id, name, age
(1, "小赵", 21),
(2, "小钱", 22),
(3, "小孙", 23),
将6条数据分为两部分插入到数据库中
# 名字表少一条 user_id = 3
insert into table_name(user_id, name)
values(1, "小赵"), (2, "小钱");
# 年龄表少一条 user_id = 2
insert into table_age(user_id, age)
values(1, 21), (3, 23);
3、查看数据
mysql> select * from table_name;
+----+---------+--------+
| id | user_id | name |
+----+---------+--------+
| 1 | 1 | 小赵 |
| 2 | 2 | 小钱 |
+----+---------+--------+
mysql> select * from table_age;
+----+---------+------+
| id | user_id | age |
+----+---------+------+
| 1 | 1 | 21 |
| 3 | 3 | 23 |
+----+---------+------+
1、INNER JOIN（内连接）
mysql> select a.user_id, name, age
-> from table_name as a inner join table_age as b
-> on a.user_id=b.user_id;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 1 | 小赵 | 21 |
+---------+--------+------+