Linux（九）MySQL中的where和having，表连接，连表联查，试图

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