笔记:实现内连接

以操作数据库tb_train_cpp2和tb_train_cpp22为例:
DESC tb_train_cpp2;
DESC tb_train_cpp22;

数据表tb_train_cpp2中的字段country与tb_train_cpp22中的字段cname对应[可实现内连接]
SELECT tb_train_cpp2.userName,tb_train_cpp2.age,tb_train_cpp2.position,tb_train_cpp22.people_num
FROM tb_train_cpp2
JOIN tb_train_cpp22 ON tb_train_cpp2.country=tb_train_cpp22.cname;

以操作数据表tb_train_user和tb_train_province为例:
DESC tb_train_user;
DESC tb_train_province;

SELECT u.userid,u.username,u.score,p.proname
FROM tb_train_user AS u
JOIN tb_train_province AS p ON u.proid=p.id;

创建数据表tb_train_cpp31和tb_train_cpp32和tb_train_cpp33:
CREATE TABLE IF NOT EXISTS tb_train_cpp3(
userId TINYINT UNSIGNED KEY AUTO_INCREMENT,
userName VARCHAR(10) NOT NULL UNIQUE,
userCard CHAR(5) NOT NULL,
proName VARCHAR(10) DEFAULT '四川-成都'
)ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_cpp3(userName,userCard,proName) VALUES
('吕布','10058','河南-信阳'),
('张颌','17744','四川-绵阳'),
('曹操','11684','辽宁-鞍山'),
('赵子龙','10892','江苏-南京'),
('貂蝉','10906','湖北-武汉'),
('甘宁','12480',DEFAULT),
('张辽','18745','安徽-合肥'),
('夏侯惇','19003','河南-开封'),
('左慈','19869','广东-广州'),
('赵飞燕','16891','湖南-岳阳'),
('张飞','12565','陕西-西安'),
('袁绍','12007','新疆-乌鲁木齐');

CREATE TABLE IF NOT EXISTS tb_train_cpp33(
proName VARCHAR(10) NOT NULL,
proId TINYINT UNSIGNED
);
INSERT tb_train_cpp33 VALUES
('河南-信阳',12),
('四川-绵阳',25),
('辽宁-鞍山',38),
('江苏-南京',44),
('湖北-武汉',50),
('四川-成都',28),
('安徽-合肥',69),
('河南-开封',17),
('广东-广州',72),
('湖南-岳阳',86),
('陕西-西安',99),
('新疆-乌鲁木齐',105);

CREATE TABLE tb_train_cpp33(
userName VARCHAR(10) NOT NULL UNIQUE,
age INT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密'),
ablity FLOAT(4,2)
);
INSERT tb_train_cpp33 VALUES
('吕布',36,'男',78.24),
('张颌',32,'女',75.87),
('曹操',45,'男',94.66),
('赵子龙',DEFAULT,'保密',90.18),
('貂蝉',24,'女',82.51),
('甘宁',28,'男',85.90),
('张辽',40,'男',89.02),
('夏侯惇',37,'保密',76.34),
('左慈',56,'女',91.48),
('赵飞燕',20,'女',88.63),
('张飞',48,'男',64.99),
('袁绍',50,'保密',76.89);

SELECT t1.userName,t1.userCard,t2.proId
FROM tb_train_cpp31 AS t1
JOIN tb_train_cpp32 AS t2 ON t1.proName=t2.proName;

SELECT t1.userName,t1.userCard,t3.age,t3.sex,t3.ablity,t1.proName
FROM tb_train_cpp31 AS t1
JOIN tb_train_cpp33 AS t3 ON t1.userName=t3.userName;