学习大数据的第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(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(select COUNT(*) from st2 as s2 where s1.sex=s2.sex and s1.scoresum
	(select COUNT(*) from (select *,sum(score) as scoresum from st1 GROUP BY st1.id) 
	as s2 where s1.sex=s2.sex and s1.scoresum