学习大数据的36天（mysql篇）——详解三大范式以及TopN问题

详解三大范式以及TopN问题

MSQL三大范式

第一范式:原子性 字段不可再分割

第二范式:唯一性 字段必须依赖与逐渐

问题：（1）数据冗余

​ （2）更新异常

​ （3）插入问题

​ （4）删除

第三范式:冗余性 不能进行依赖传递

TopN问题

类似于查找分组中最大或者前几个信息，也就是这类的问题不能group by 来解决

不能再使用group by 解决TopN的问题

因为group by所获的数据量是固定的(和组的数量保持一致)

TopN数量不固定

-- order by 
select *from worker order by age DESC;

select *from worker limit 0,3;
select *from worker limit 3;
  
-- from 获取数据  0
-- select 显示数据   3
-- order by 排序  2
-- limit 分页
-- 先排序后分页    3

-- from -> order by -> limit -> select

select *from worker order by age limit 3;

-- 求平均年龄
select FLOOR(avg(age)) as '平均年龄' from worker;
-- 求大于平均年龄的人
-- 求大于平均年龄的男生?
-- 错误写法：select *from worker age>avg(age);
select *from worker where age>(select FLOOR(avg(age)) as '平均年龄' from worker) and sex='1';

select *,avg(age) from worker; # 只能显示一个，如何将所有的信息后面都加上平均年龄呢？

-- 重新创建一个表：
-- id name 科目 学分 成绩 
-- 1   zs   数学 2   90
-- 2   ls   语文  3   80
-- 1   zs   语文  2    60
-- 会出现问题

create table student(
	id int(11) NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	age int(11) DEFAULT null,
	sex enum('0','1') DEFAULT NULL,
	PRIMARY KEY(id)
);

insert into student(name,age,sex)VALUES("李四",19,"1");
insert into student(name,age,sex)VALUES("王五",20,"1");
insert into student(name,age,sex)VALUES("赵六",21,"1");
insert into student(name,age,sex)VALUES("钱琪",22,"1");
insert into student(name,age,sex)VALUES("杨幂",18,"0");
insert into student(name,age,sex)VALUES("丽丽",19,"0");
insert into student(name,age,sex)VALUES("莎莎",20,"0");
insert into student(name,age,sex)VALUES("慧慧",22,"0");
insert into student(name,age,sex)VALUES("翠翠",23,"0");

create table score(
	id int(11) NOT NULL AUTO_INCREMENT,
	subjectName varchar(255) NOT NULL,
	score int(11) NOT NULL,
	studentId int(11) NOT NULL,
	PRIMARY KEY(id)
);

insert into score(subjectName,score,studentId)VALUES("语文",80,1001);
insert into score(subjectName,score,studentId)VALUES("数学",89,1001);
insert into score(subjectName,score,studentId)VALUES("英语",73,1001);
insert into score(subjectName,score,studentId)VALUES("语文",81,1002);
insert into score(subjectName,score,studentId)VALUES("数学",99,1002);
insert into score(subjectName,score,studentId)VALUES("英语",94,1002);
insert into score(subjectName,score,studentId)VALUES("语文",65,1003);
insert into score(subjectName,score,studentId)VALUES("数学",45,1003);
insert into score(subjectName,score,studentId)VALUES("英语",12,1003);
insert into score(subjectName,score,studentId)VALUES("语文",33,1004);
insert into score(subjectName,score,studentId)VALUES("数学",68,1004);
insert into score(subjectName,score,studentId)VALUES("英语",59,1004);
insert into score(subjectName,score,studentId)VALUES("语文",19,1005);
insert into score(subjectName,score,studentId)VALUES("数学",100,1005);
insert into score(subjectName,score,studentId)VALUES("英语",80,1005);
insert into score(subjectName,score,studentId)VALUES("语文",85,1006);
insert into score(subjectName,score,studentId)VALUES("数学",88,1006);
insert into score(subjectName,score,studentId)VALUES("英语",73,1006);
insert into score(subjectName,score,studentId)VALUES("语文",88,1007);
insert into score(subjectName,score,studentId)VALUES("数学",45,1007);
insert into score(subjectName,score,studentId)VALUES("英语",89,1007);
insert into score(subjectName,score,studentId)VALUES("语文",51,1008);
insert into score(subjectName,score,studentId)VALUES("数学",42,1008);
insert into score(subjectName,score,studentId)VALUES("英语",43,1008);
insert into score(subjectName,score,studentId)VALUES("语文",90,1009);
insert into score(subjectName,score,studentId)VALUES("数学",56,1009);
insert into score(subjectName,score,studentId)VALUES("英语",69,1009);
insert into score(subjectName,score,studentId)VALUES("语文",99,1010);
insert into score(subjectName,score,studentId)VALUES("数学",63,1010);
insert into score(subjectName,score,studentId)VALUES("英语",88,1010);

-- 求学生年龄最大值 
select max(age) from student;

-- 求男女当中的最大值
select *from student;
select *,max(age)'最大值',sum(age) '总数' from student GROUP BY sex;
-- 1005	杨幂	18	0	23
-- 1001	李四	19	1	22
-- 结果为两条:
--   分组： 求的是组的数，而不是组内容的数
-- 且为性别分类的第一个：
-- 	 分组之后默认顺序的第一条
select sex,max(age)'最大值',sum(age) '总数' from student GROUP BY sex;

-- 将sex和age组合在一起分组
select sex,max(age)'最大值',sum(age) '总数' from student GROUP BY sex,age;

-- 求所有的最大值
select max(age) from student;

-- 求age最大的学生
select * from student where age=(select max(age) from student);

-- 求每组最大的值
select sex,max(age) from student GROUP BY sex;

-- 求每组age最大的学生
-- 错误写法：
select max(age) from student where age=(select sex,max(age) from student GROUP BY sex);

-- 错误写法2：TopN问题（查询的结果不对） 
select * from student where age in (select max(age) from student GROUP BY sex);

-- group BY 不适合每组最大的问题

-- Top1 每组最大的学生
select * from student s1 where 0=(select COUNT(*) from student s2 where s1.sex = s2.sex and s1.age<s2.age)

-- Top 每组前三个大的学生
select * from student s1 where 3>(select COUNT(*) from student s2 where s1.sex = s2.sex and s1.age<s2.age)


-- 解决group  by  被限制的问题
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 分页
select *from worker LIMIT 0,5;

-- group by 
select *from worker;


select * from worker group by sex;