学习大数据的第37天（mysql篇）——where 和 having、union、连表联查、视图

学习大数据的第37天（mysql篇）——where 和 having、union、连表联查、视图

MYSQL第四天

where 和 having

where和having都是用做筛选

where:处理元数据(from读取的数据)

having:对from读取数据的处理结果进行再次筛选

where->group by ->having

select *,age-18 as c from student where  c>2;  # 报错
select *,age-18 as c from student having  c>2; # 正确

select sex,count(*) as c from student group by sex where c>2; #报错
select sex,count(*) as c from student group by sex having c>2; #正确

select sex,count(*) as c from student group by sex where sex='1';  # 报错
select sex,count(*) as c from student group by sex having sex='1'; # 正确

select sex,count(*) as c from student where sex='1' group by sex ;  # 正确
select sex,count(*) as c from student having sex='1' group by sex ; # 错误

表连接

union

结果的纵向合并

默认去重

select * from student  union select * from student; #默认去去重
(select * from student where age>18) union( select * from student where sex='1'); #默认去去重
select * from student where age>18 or sex='1'; #针对于同一张表的结果合并可以选择使用or

union all

结果的纵向合并

默认不去重

select * from student  union all select * from student; #默认去去重
(select * from student where age>18) union all ( select * from student where sex='1'); #默认去去重
select * from student where age>18 or sex='1'; #针对于同一张表的结果合并可以选择使用or

select * from a,b

会生成笛卡尔积

不允许两个表相同

select * from student,score;#student=9条 score=30 结果=270;
select * from student,student;

连表联查

注意:多张表中字段可能相同 需要以表名.字段的方式区分

左连 left join

以左表为基准表 匹配右表中的数据,匹配不到以null补齐

select * from student left join score on student.id=score.studentid;

右连 right join

以右表为基准表 匹配左表中的数据,匹配不到以null补齐

select * from student right join score on student.id=score.studentid;

select * from score right join student on student.id=score.studentid; # 和左连一样 左右连接可以相互转换

内联 inner join

两张表的交际

select * from student inner join score on student.id=score.studentid;

SQL作为表使用

因为SQL的擦汗寻结果实际上是不存在的

想要使用需要给上一个别名

有可能假表和真表存在字段相同,如果假表没有名字就不能做区分了

select studentid,sum(score) as score from score group by studentid;
-- 再连接
select * from student 
left join 
(select studentid,sum(score) as score from score group by studentid) as s
on student.id=s.studentid;

从其他表中加载数据(把sql执行的结果进行存储为一张表)

create table test like student; # 是把student的表结构没有数据 赋值了过去
create table test as select * from student; #把student的表结构和数据都复制
insert into test as select * from student; #是把查询的结果插入到tets中

视图

是把由sql语句执行的结果保存到一张虚表(临时表,虚拟表)

是对于若干张基本表(mysqk存在的表)的引用

一对一不包含聚合函数:增上改查 (修改视图实际上就是修基本表)

一对一包含聚合函数:查

一对多不包含聚合函数:查

一对多包含聚合函数:查

show create view stu4;
desc stu4;
drop view stu4

测试 代码

-- 问题1 where为什么不能用
-- from -> where -> select 
-- 一下都是错误示范
-- select *,age-18 as c from student where c>3; # where找不到c这个字段
-- select *,avg(age) as c from student where c>18;
-- select sex,avg(age) as c from student GROUP BY sex where c>20.3;
-- select sex,avg(age) as c from student GROUP BY sex where sex='1';

-- 首先得知道谁先开始
-- from where group by 
select sex,avg(age) as c from student where sex='1' GROUP BY sex;

-- having 筛选 
-- having 走在select后面,可以用来处理后的结果及进行再次查询
select *,age-18 as c from student having c>18;
select sex,avg(age) as c from student GROUP BY sex HAVING sex='1';

-- having 不可以放在group by前面
-- from group by  where  select having 
where(处理元数据)
HAVING(针对处理后的结果在此查询)

select sex,avg(age) as c from student having sex='1' GROUP BY sex;

-- 问题2 应该怎么用
-- 问题3 两张表为什么进行连接
根据依赖关系进行连接

-- 表连接 UNION(表示结果的合并，经过select查询后的结果)
select *from student
UNION
select *from score;

-- 结果不是我们想要的

-- 合并同一张表
select *from student
UNION all
select *from student;
-- 使用union合并默认是纵向合并 且 默认去重
-- 不想去重可以加上all

-- 如果两张报表的字段一样，可以合并完全，但是如果字段不一样，则根据谁的字段来进行
-- 选择呢
-- 合并的新表，字段是以第一个表为主

-- 字段不一样会报错
-- select *from student
-- UNION all
-- select id,subjectName,score from score;

-- 笛卡尔积
select * from student,score;

-- 求平均年龄 求大于平均年龄的人
-- 第一种方式
select avg(age)from student;
select *from student where age>(select avg(age)from student);

-- 第二种方式连接两个表，用笛卡尔积横向连接的方法
select avg(age) from student;
select *from student,(select avg(age) as c from student) as s where age>c;

-- 第三种方式使用having进行查找
select *,if(age>(select avg(age) from student),'大于','小于') as c from student HAVING c='大于';

-- 左连接: 以左表为准，到右表中 找匹配的数据，如果找到就拿出来,如果没有填null
-- 格式：select *from 表1 LEFT JOIN 表2 on 匹配条件
-- 左连接(以左表为基准，右表如果没有用null代替)
select student.*,subjectName,score from student LEFT JOIN score on student.id = score.studentId;
-- 右连接(以右表为基准，左表如果没有用null代替)
select student.*,subjectName,score from student RIGHT JOIN score on student.id = score.studentId;
-- 内连接(两个表相同的拿出来，没有的就算了)
select student.*,subjectName,score from student INNER JOIN score on student.id = score.studentId;

-- 查询每个学生考了多少分
-- 学生信息要有科目和分数
select student.*,subjectName,score from student LEFT JOIN score on student.id = score.studentId;
-- 每个学生的总分

-- 方式一：先求总分，再连接
select studentId,sum(score) as scoresum from score GROUP BY studentId; 
-- 学生信息 也要
--  将 每个学生的总分表于学生表进行连表联查
select student.*,scoresum from student LEFT JOIN 
	(select studentId,sum(score) as scoresum from score GROUP BY studentId) 
		as c on student.id = c.studentId;

-- 方式二：先连接，再分组求和
select *from student LEFT JOIN score on student.id = score.studentId;
select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id;

-- 求年级第一
-- 先求最大的分数
select *,max(sum)from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) as ss; # 这么求有问题,最大值不是李四的值

-- 方法一：连表联查

-- 查询表中最大总分
select max(sum)from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) as ss;

-- 根据总分去表中查找相同的学生信息
select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id HAVING sum=(select max(sum)from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) as ss);



-- 方法二：可以先排序，如何取第一个
select * from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) as ss ORDER BY sum DESC limit 1;

-- 年纪前三

-- 查询想要查找的表
select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id order by sum DESC limit 3;
-- order by  运行是在select之后

-- 表信息排序

-- 男女前三

-- 查询出想要查找的表
select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id;

-- 根据性别分组找到男女前三的学生信息
select * from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) s1 where 3>(select COUNT(*) from (select *,sum(score) as sum from (select student.*,score from student LEFT JOIN score on student.id = score.studentId) as s 
	GROUP BY s.id) s2 where s1.sex = s2.sex and s1.sum<s2.sum); 

-- 视图的引用
-- 进场 会用到某一个sql的结果
-- 是把某个sql结果当作一个表来用
-- 虚表 虚拟的表 
select student.*,score from student LEFT JOIN score on student.id = score.studentId;
-- 问题一：当前这个sql语句会多次书写
-- 问题二：再次使用缩写sql不便于观看
-- 解决方案：对这个sql语句的结果进行保存
-- 取个名字
-- stuscoreview

-- 视图是由查询结果形成的一张虚拟的表。
-- 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
-- 可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；
-- 注意：区别于使用like方式创建表----
-- 	     create table a like b;（直接引用表结构）
-- 	     insert into b select * from a;（需要插入数据）
-- 
-- 创建视图
-- 	create view tmp as select * from student_info where age=25;
-- 
-- 查看视图结构：
-- 	desc tmp;
-- 	show create table tmp;
-- 
-- 删除视图：
-- 	drop view tmp;

-- 这三种都是真的存储，创建了新的表
-- 创建过程中直接引用其他的表结构
create table test01 like student;
-- 创建过程中直接引用其他的表(复制完成的表(表结构以及数据)，在数据据库中，不是视图)
create table shows as select *from student;
-- 将student表中的数据，插入test01中
insert into test01 select *from student;

-- 假的存储，存储为视图

-- 创建视图格式
create view stu1 as select *from student;
create view stu2 as select student.*,score from student LEFT JOIN score on student.id = score.studentId;


-- 对比test01 和 stu1之间的搞关系
-- （1）如果原表进行增删改查时，视图中的数据也会发生改变，所用的数据是同一个，但是创建的test01不会发生改变
-- （2）视图是对查询的结果进行封装，而复制表是直接重新创建一张表
-- （3）视图与基本表的关系是一一对应的关系
-- （4）视图如果是直接将基本表进行创建，相当于直接对基本表一对一增删改查都可以
-- （5）视图如果是基本表的一对一，来自与基本表的聚合，比如group by等等时

-- 视图与基本表的增删改查
insert into stu1(id,name,age,sex) VALUES('1010','test','18','1');
delete from stu1 where id='1010';
update stu1 set name='show' where id='1010';
select *from (select *from student) as stu1;

-- 探究视图如果是基本表的一对一，来自与基本表的聚合，比如group by等等时
create view stu2 as select sex,avg(age) as 'age' from student GROUP BY sex;
-- 增删改查

insert into stu2(sex,age) VALUES('1','20.00'); # 不能进行添加操作
delete from stu2 where sex='1'; # 不能进行删除操作
update stu2 set sex='80' where sex='1'; # 不能进行更新操作
# 查询是可以的

-- 视图与基本表的聚合，所以得出结论：只能查询，不能增删改。


-- 数据不经过处理的（普通的）一对多

select student.*,subjectName,score,studentId from student LEFT JOIN score on student.id = score.studentId;

-- 不允许出现相同的字段
create view stu3 as select student.*,subjectName,score,studentId from student LEFT JOIN score on student.id = score.studentId;

-- 新增一条数据(不允许，会缺失字段)
-- insert into stu3.....

-- 删除一条数据(跟添加同样的问题)

-- 改一条数据(跟添加同样的问题)

-- 查询没问题

-- 聚合函数的一对多
-- 求学生的总分
select student.*,score from student LEFT JOIN score on student.id = score.studentId;
select *,sum(score) from (select student.*,score from student 
	LEFT JOIN score on student.id = score.studentId) as s GROUP BY s.id;

-- 创建视图视图4
create view stu4 as select *,sum(score) from (select student.*,score from student 
	LEFT JOIN score on student.id = score.studentId) as s GROUP BY s.id;

-- 探究聚合函数的一对多的视图，增删改查的问题

-- 增删改不行，查询可以

-- 查看视图的创建语句
show create view stu4;

-- 查看视图的表结果
desc stu4; 

-- 删除视图
drop view st3;

-- 通过视图实现男女前三（视图小练习）

select student.*,score from student LEFT JOIN score on student.id = score.studentId;

select *,sum(score) as scoresum from 
	(select student.*,score from student LEFT JOIN score on student.id = score.studentId)
	 as s GROUP BY s.id;

select *from (select *,sum(score) as scoresum from 
	(select student.*,score from student LEFT JOIN score on student.id = score.studentId)
	 as s GROUP BY s.id) as s1 where 3>(select COUNT(*) from (select *,sum(score) as scoresum from 
	(select student.*,score from student LEFT JOIN score on student.id = score.studentId)
	 as s GROUP BY s.id) as s2 where s1.sex=s2.sex and s1.scoresum<s2.scoresum);


-- 根据上述的sql语句创建视图

create view st1 as select student.*,score from student LEFT JOIN score on student.id = score.studentId;

create view st2 as select *,sum(score) as scoresum from st1 GROUP BY st1.id;

create view st3 as select * from st2 as s1 where 3>(select COUNT(*) from st2 as s2 where s1.sex=s2.sex and s1.scoresum<s2.scoresum);


？？？？？？？？使用st2来创建第三个视图产生问题，而使用st2原本的创建视图语句可以实现？？？？？？？？？？

解决：添加视图的时候不需要加括号

create view st3 as select *from 
	(select *,sum(score) as scoresum from st1 GROUP BY st1.id) as s1 where 3>
	(select COUNT(*) from (select *,sum(score) as scoresum from st1 GROUP BY st1.id) 
	as s2 where s1.sex=s2.sex and s1.scoresum<s2.scoresum);