MYSQL之数据库设计范式和高级查询

1 数据库设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，
必须满足一定的范式。

范式一

确保每列保持原子性；数据库表中的所有字段都是不可分解的原子值；
例如：某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为
多个字段，省份、城市、详细地址等；

范式二

确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）；
这一范式的前提是主键是一个由多个列组成的主键。也是组合索引

范式三

确保每列都和主键直接相关，而不是间接相关；减少数据冗余；

反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；因此处于性能考虑，可能需要进行反范式设计；

原先我理解的只有范式标准只有以上的标准，但是再网上查了一下发现还是有其他的范式来约束设计数据库。巴德斯科范式（BCNF）、第四范式（4NF）和第五范式（5NF），但是一般设计数据库时，只需要做到以上三个范式的约束即可。

高级查询

基础查询

-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `sname`, `class_id` FROM student;
-- 别名 列明 不要用关键字
SELECT `sname` AS '姓名' , `class_id` AS '班级ID' FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `class_id` FROM student;

node：
1 select * 不建议使用，因为数据库会因为这种语句全表扫描，影响了数据库的执行效率。应该只查询自己需要的部分字段
2 查询时显示的表时。列名尽量不要用数据库的列名，通过as设置别名。
3 distinct 关键字可以把查询结果的重复记录给去掉

条件查询

-- 查询姓名为 邓洋洋 的学生信息
SELECT * FROM `student` WHERE `name` = '邓洋洋';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=2;

node
1 条件查询 where 或者 where（条件1） and (条件2)

范围查询

-- 查询班级id 1 到 3 的学生的信息
SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;

node
1 关键字：between … and …

判空查询

# is null 判断造成索引失效
# 索引 B+ 树
SELECT * FROM `student` WHERE `class_id` IS NOT NULL; #判断不为空
SELECT * FROM `student` WHERE `class_id` IS NULL; #判断为空
SELECT * FROM `student` WHERE `gender` <> ''; #判断不为空字符串
SELECT * FROM `student` WHERE `gender` = ''; #判断为空字符串

node
1 再判空查询的时候不要用 is null 或者 is not null
因为 is null 或者 is not null 会导致数据库查找记录的时候走的不是索引而是全表扫描，这样会导致数据库的效率降低了。
应该用 <> ’ ’ 判断不为空 = ’ ’ 判断为空字符串

模糊查询

-- 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符
-- 查询名字为 谢 开头的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '谢%';
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

node
1 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符

分页查询

SELECT * FROM `student` LIMIT 1,2;

node
1 - 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用
– 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第
一条记录的参数为0。

查询后排序

• 关键字：order by field, asc:升序, desc:降序
  SELECT * FROM score ORDER BY num ASC;
  – 按照多个字段排序
  SELECT * FROM score ORDER BY course_id DESC, num DESC;

聚合查询

聚合函数	描述
sum ()	计算某列的总和
avg()	计算某列的平均值
max()	计算某列的最大值
min()	计算某列的最小值
count()	计算某列的行数

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

分组查询

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY
`gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num 

node
1 一般来说 分组查询和聚合函数一起使用。
2 group_concat() 函数的作用时将查询组成的字段拼接再一起。
3 group by 关键字 (列) 根据某一条件进行分组查询
4 group by 关键字 一般都是与having 配合使用。group by + 列名 having + 条件 使用。