mysql基本命令4

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;查看表结构

-- 问题1:where为什么不能用

-- from (id name age sex )-> where (只能处理from获取的结果) -> select
-- select *,age-18 as c from student where c>3;#ERRO
-- 
-- select *,avg(age) as c from student  where c >18;#ERRO

select sex,avg(age) as c FROM student group by sex where sex='1'; 
 -- 报错(先走from然后group by 分两组，每组都有数据（每组不一定都是一个数据）在走where中sex='1'只有一个数据对不上导致报错)
-- from where group by
select sex,avg(age) as c FROM student where sex='1' group by sex ; -- 正确
执行顺序：from  where group by
筛选:having 
 from select having(处理后的结果进行再次查询)
select *,age-18 as c from student having c>3; 
select *,avg(age) as c from student  having c >18;

select sex,avg(age) as c FROM student group by sex having sex='1';

select sex,avg(age) as c FROM student having sex='1' group by sex ; #报错

select * from student where age>'18';
select * from student having age>'18';

-- 执行顺序where ->gruop by-> having
where(处理元数据)
having(针对于处理后的结果再次查询)


-- 问题2:因该用什么
-- 问题3:两张表为什么进行连接

union :结果的合并(纵向合并 默认去重)
select * from 表1 union select * from 表2；
union all:结果的合并(纵向合并 默认不去重)
注意：字段匹配才能合并；
-- select * from student
-- union all
-- select * from score;


-- select * from student,student;

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

select id,name,if(age>'18','成年','未成年') as c from student having c='未成年';

select * from (select id,name,if(age>'18','成年','未成年') as c from student) as t;

select avg(age) from student;
select * from student;
select * from student,(select avg(age) as age from student) as cccc where student.age>cccc.age; -- 笛卡尔积
select * from a,b;



create table s2(
	id int,
	name VARCHAR(255),
	play varchar(255)
)

INSERT into s1(id,name,play) values('1001','zs','lol'),('1002','ls','steam'),('1003','ww','dota2');
INSERT into s2(id,name,play) values('1101','zl','lol'),('1102','xm','steam'),('1103','xh','dota');

select * from s2;

左连接（left join）
左表为准，到右表中找匹配的数据，如果找到就拿出来，找不到用null
补齐。

格式 select * from 表1 left join 表2 on 匹配条件

select * from s1 left join s2 on s1.play=s2.play;



右连接（right join）
右表为准，到左表中找匹配的数据，如果找到就拿出来，找不到用null
补齐。左右连接可以相互转化。

select * from s2 right join s1 on s1.play=s2.play;


内连接（inner join）
	内连接是左右连接的交集。

select * from s1 INNER JOIN s2 on s1.play=s2.play;


-- 查询每个学生考了多少分
-- 学生信息要有 科目 分数

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


-- 求学生的总分
-- 学生信息 总分
-- 方式1
-- 先求总分
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;


-- 方式2
-- 先连接
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 max(scoresum) as maxscore 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) ss;


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) test
where scoresum=(select max(scoresum) as maxscore 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) ss);
-- 年纪前三
-- 男女前三

drop view stu4 输出视图