MySQL：where和having、表纵向连接、select * from a,b 表横向连接、连表联查、SQL作为表使用

一、where和having

where和having都是用做筛选

where:处理from读取的数据

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

where->group by ->having

select *,age-18 as c from student where  c>2;  # 报错，from-where-select c数据是select中获取的，当运行到where时找不到c数据
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';  # 报错  where和group by顺序反了
select sex,count(*) as c from student group by sex having sex='1'; # 正确  group by--having
​
select sex,count(*) as c from student where sex='1' group by sex ;  # 正确，where--group by
select sex,count(*) as c from student having sex='1' group by sex ; # 错误  group by和having顺序反了

二、表连接

1、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

2、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 表横向连接

会生成笛卡尔积，a的每一条数据和b的所有数据进行横向连接，count（a）*count（b）

不允许两个表相同

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

查找大于平均年龄的学生：

select * from student where age>(SELECT avg(age) from student);
select * from student,(SELECT avg(age) as age from student) as c WHERE student.age>c.age;

四、连表联查

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

1、左连 left join

格式 select * from 表1 left join 表2 on 匹配条件  以左边表1为基准

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

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

2、右连 right join

格式 select * from 表1 right join 表2 on 匹配条件  以右边表2为基准

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

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

3、内联 inner join

两张表的交集

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

五、SQL作为表使用

因为SQL的查询结果实际上是不存在的

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

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

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