MySQL之join关键词

MySQL的join关键词：

join，顾名思义，就是联合，加入的意思，如下图。

先建表

student表：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentno` int NOT NULL COMMENT '学号',
`loginpwd` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`studentname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
`sex` tinyint(1) NULL DEFAULT NULL COMMENT '性别，0或1',
`gradeid` int NULL DEFAULT NULL COMMENT '年级编号',
`phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '联系电话，允许为空',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地址，允许为空',
`borndate` datetime NULL DEFAULT NULL COMMENT '出生时间',
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`) USING BTREE,
UNIQUE INDEX `identitycard`(`identitycard`) USING BTREE,
INDEX `email`(`email`) USING BTREE,
FULLTEXT INDEX `studentname`(`studentname`),
FULLTEXT INDEX `studentname_index`(`studentname`)
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1000, '123456', '张伟', 0, 2, '13800001234', '', '1980-01-01 00:00:00', 'text123@qq.com', '123456198001011234');
INSERT INTO `student` VALUES (1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-01-01 00:00:00', 'text111@qq.com', '123456199001011233');
INSERT INTO `student` VALUES (1003, 'adsd ', '张学良', 1, 5, '242342425', '式风格士大夫', '2021-09-07 15:21:29', 'sfdfe', 'sfefsdfe');
INSERT INTO `student` VALUES (1002, 'dasfw', 'hick', 1, 4, 'asdasd', 'sadsas', '2021-09-01 17:38:34', 'asda', 'dasdas');
INSERT INTO `student` VALUES (1004, 'dsasd', 'erick', 1, 4, 'adaw', 'daw', '2021-09-13 17:44:34', 'asd', 'as');

SET FOREIGN_KEY_CHECKS = 1;

subject：科目表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`subjectno` int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名称',
`classhour` int NULL DEFAULT NULL COMMENT '学时',
`gradeid` int NULL DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`) USING BTREE,
INDEX `subjectno_index`(`subjectno`) USING BTREE COMMENT '课程id的索引'
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES (1, '高等数学-1', 110, 1);
INSERT INTO `subject` VALUES (2, '高等数学-2', 110, 2);
INSERT INTO `subject` VALUES (3, '高等数学-3', 100, 3);
INSERT INTO `subject` VALUES (4, '高等数学-4', 130, 4);
INSERT INTO `subject` VALUES (5, 'C语言-1', 110, 1);
INSERT INTO `subject` VALUES (6, 'C语言-2', 110, 2);
INSERT INTO `subject` VALUES (7, 'C语言-3', 100, 3);
INSERT INTO `subject` VALUES (8, 'C语言-4', 130, 4);
INSERT INTO `subject` VALUES (9, 'Java程序设计-1', 110, 1);
INSERT INTO `subject` VALUES (10, 'Java程序设计-2', 110, 2);
INSERT INTO `subject` VALUES (11, 'Java程序设计-3', 100, 3);
INSERT INTO `subject` VALUES (12, 'Java程序设计-4', 130, 4);
INSERT INTO `subject` VALUES (13, '数据库结构-1', 110, 1);
INSERT INTO `subject` VALUES (14, '数据库结构-2', 110, 2);
INSERT INTO `subject` VALUES (15, '数据库结构-3', 100, 3);
INSERT INTO `subject` VALUES (16, '数据库结构-4', 130, 4);
INSERT INTO `subject` VALUES (17, 'C#基础', 130, 1);

SET FOREIGN_KEY_CHECKS = 1;

result 成绩表：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`studentno` int NOT NULL COMMENT '学号',
`subjectno` int NOT NULL COMMENT '课程编号',
`examdate` datetime NOT NULL COMMENT '考试日期',
`studentresult` int NOT NULL COMMENT '考试成绩',
INDEX `subjectno`(`subjectno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES (1000, 1, '2013-11-11 16:00:00', 85);
INSERT INTO `result` VALUES (1000, 2, '2013-11-12 16:00:00', 70);
INSERT INTO `result` VALUES (1000, 3, '2013-11-11 09:00:00', 68);
INSERT INTO `result` VALUES (1000, 4, '2013-11-13 16:00:00', 98);
INSERT INTO `result` VALUES (1000, 5, '2013-11-14 16:00:00', 58);
INSERT INTO `result` VALUES (1001, 6, '2021-09-22 15:19:52', 79);
INSERT INTO `result` VALUES (1001, 13, '2021-09-22 16:48:09', 66);
INSERT INTO `result` VALUES (1000, 9, '2021-09-08 17:37:36', 80);
INSERT INTO `result` VALUES (1001, 9, '2021-09-16 17:37:50', 60);
INSERT INTO `result` VALUES (1002, 9, '2021-09-02 17:38:02', 99);
INSERT INTO `result` VALUES (1004, 9, '2021-09-15 17:44:09', 81);
INSERT INTO `result` VALUES (1001, 9, '2021-09-23 15:07:34', 85);

SET FOREIGN_KEY_CHECKS = 1;

开始，先试试inner join关键词：