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