2024-01-02Mysql 连接查询实例，理解左连接、右连接
实例使用数据集：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 16, '北京市通州区');
INSERT INTO `student` VALUES (2, '李四', 17, '河北石家庄');
INSERT INTO `student` VALUES (3, '李师师', 17, '河南洛阳');
INSERT INTO `student` VALUES (4, '杜甫', 18, '陕西西安');
INSERT INTO `student` VALUES (5, '高适', 16, '安徽福州');
INSERT INTO `student` VALUES (11, '李白', 11, '河北张家口');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for core
-- ----------------------------
DROP TABLE IF EXISTS `core`;
CREATE TABLE `core` (
`id` int(11) NOT NULL,
`sid` int(11) NULL DEFAULT NULL,
`cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`core` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of core
-- ----------------------------
INSERT INTO `core` VALUES (1, 1, '数学', '98');
INSERT INTO `core` VALUES (2, 1, '语文', '87');
INSERT INTO `core` VALUES (3, 1, '英语', '76');
INSERT INTO `core` VALUES (4, 2, '数学', '96');
INSERT INTO `core` VALUES (5, 2, '语文', '92');
INSERT INTO `core` VALUES (6, 2, '英语', '81');
INSERT INTO `core` VALUES (7, 3, '数学', '45');
INSERT INTO `core` VALUES (8, 3, '语文', '76');
INSERT INTO `core` VALUES (9, 7, '英语', '45');
INSERT INTO `core` VALUES (19, 8, '数学', '87');
SET FOREIGN_KEY_CHECKS = 1;
student表，单表数据情况：select * from student s
core表，单标数据查询情况：SELECT * FROM `core` c
多表，内连接：select s.*,c.* from student s inner join `core` c on s.id = c.sid
左连接效果：
select s.*,c.* from student s left join `core` c on s.id = c.sid
有链接：select s.*,c.* from student s right join `core` c on s.id = c.sid
/*左外连接、右外连接跟左连接、右连接的结果一样*/
select s.*,c.* from student s left OUTER JOIN `core` c on s.id = c.sid
select s.*,c.* from student s right outer join `core` c on s.id = c.sid
