数据库设计的三范式 && MYSQL行转列的两种做法

数据库设计的三范式 && MYSQL行转列的两种做法

1.数据库三范式

范式1：遵循原子性：表中字段的数据，不可以再拆分。例如：

范式2：遵循唯一性，消除部分依赖：任何非主键字段完全依赖主键字段不能依赖其他非主键字段。
这里面的学分至于学科有关与主键无关，所以他不应该出现在这里

范式3：消除传递依赖：任何两个非主键字段的数值之间不存在函数依赖关系。
这里的计算关系是不合理的

什么时候可以违反三范式？

降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定违反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和 其他方法经常能够解决性能问题，而不必采用反规范这种方法

2. MYSQL行转列的两种做法

DROP TABLE IF EXISTS `sys_student`;
CREATE TABLE `sys_student`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_student
-- ----------------------------
INSERT INTO `sys_student` VALUES (1, '小明', '语文', 96);
INSERT INTO `sys_student` VALUES (2, '小明', '数学', 67);
INSERT INTO `sys_student` VALUES (3, '小明', '英语', 89);
INSERT INTO `sys_student` VALUES (4, '大花', '语文', 99);
INSERT INTO `sys_student` VALUES (5, '大花', '语文', 94);

1、sql法

SELECT NAME AS
	'姓名',
	MAX( CASE WHEN SUBJECT = '语文' THEN score ELSE 0 END ) AS '语文',
	MAX( CASE WHEN SUBJECT = '数学' THEN score ELSE 0 END ) AS '数学',
	MAX( CASE WHEN SUBJECT = '英语' THEN score ELSE 0 END ) AS '英语' 
FROM
	sys_student 
GROUP BY
NAME

如果是oracle或者pgsql，则可以

SELECT *
FROM student
PIVOT (
 SUM(score) FOR subject IN (语文, 数学, 英语)
)

2、反射法

/**
 * 通过反射获取字段的值
 * @param fieldName 字段名
 * @param t 对象
 * @param <T> 对象类型
 * @return
 */
public <T> Object getValueByReflect(String fieldName, T t) {
    String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    try {
        Method method = t.getClass().getMethod(methodName);
        Object fieldValue = method.invoke(t);
        return fieldValue;
    } catch (Exception e) {
        logger.error("通过反射获取字段值失败:{}", e);
        return null;
    }
}

通过这两种方法皆可把行数据转为对象返回

3.SQL查询优化

面试官：项目中你是如何进行分页查询优化的？比如一个表里有20000000数据，如何查询第1000000页的10条记录

⾸先定位偏移位置的id SELECT id FROM user_record LIMIT 1000000,1;
根据获取到的id值向后查询 SELECT * FROM user_record WHERE id >=(SELECT id FROM user_record LIMIT 1000000,1) LIMIT 10;