日常问题总结

1.EXPLAIN SELECT
st.sno
,st.sname
FROM
student st
left JOIN score sc ON st.sno = sc.sno

st表索引失效这是为啥？？

EXPLAIN SELECT
st.sno
,st.sname
FROM
score sc
left JOIN student st ON st.sno = sc.sno

表顺序反过来ok
建表语句

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sno` int(3) DEFAULT NULL,
  `cno` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `degree` decimal(4, 1) DEFAULT NULL,
  INDEX `sno`(`sno`) USING BTREE,
  INDEX `cno`(`cno`) USING BTREE,
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (103, '3-245', 86.0);
INSERT INTO `score` VALUES (105, '3-245', 75.0);
INSERT INTO `score` VALUES (109, '3-245', 68.0);
INSERT INTO `score` VALUES (103, '3-105', 92.0);
INSERT INTO `score` VALUES (105, '3-105', 88.0);
INSERT INTO `score` VALUES (109, '3-105', 76.0);
INSERT INTO `score` VALUES (101, '3-105', 64.0);
INSERT INTO `score` VALUES (107, '3-105', 91.0);
INSERT INTO `score` VALUES (108, '3-105', 78.0);
INSERT INTO `score` VALUES (101, '6-166', 85.0);
INSERT INTO `score` VALUES (107, '6-166', 79.0);
INSERT INTO `score` VALUES (108, '6-166', 81.0);
 
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sno` int(3) NOT NULL,
  `sname` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ssex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sbirthday` datetime DEFAULT NULL,
  `class` int(5) DEFAULT NULL,
  PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '李军', '男', '1976-02-20 00:00:00', 95033);
INSERT INTO `student` VALUES (103, '陆君', '男', '1974-06-03 00:00:00', 95031);
INSERT INTO `student` VALUES (105, '匡明', '男', '1975-10-02 00:00:00', 95031);
INSERT INTO `student` VALUES (107, '王丽', '女', '1976-01-23 00:00:00', 95033);
INSERT INTO `student` VALUES (108, '曾华', '男', '1977-09-01 00:00:00', 95033);
INSERT INTO `student` VALUES (109, '王芳', '女', '1975-02-10 00:00:00', 95031);