MySQLDay04

一、where和having的区别

where(处理元数据)
having(针对于处理后的结果再次查询)

二、表连接union

union：表示结果的合并（纵向合并 默认去重）
union all：结果的合并（纵向合并 默认不去重）

三、连表联查

1、左连接（left join）

左表为准，到右表中找匹配的数据，如果找到就拿出来，找不到用null补齐

2、右连接（right join）

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

3、内连接（inner join）

内连接是左右连接的交集

1、查询每个学生考了多少分

学生信息要有 科目 学分


方式1

方式2

2、求年级第一的学生

先求最大的分数

然后再求年级第一的学生

3、求年级前三的学生

求年级前三
SELECT students.*,score.score from students left join score on students.id=score.studentid; 
SELECT *,sum(score) as scoresum from (SELECT students.*,score.score from students left join score on students.id=score.studentid) as stu group by id;
SELECT *,sum(score) as scoresum from (SELECT students.*,score.score from students left join score on students.id=score.studentid) as stu group by id ORDER BY scoresum
desc limit 3;

4、求男女前三的学生

求男女前三 TopN
select students.*,score.score from students LEFT JOIN score on students.id=score.studentid;
SELECT *,sum(score) as scoresum from (select students.*,score.score from students LEFT JOIN score on students.id=score.studentid) as stu GROUP BY id;

SELECT * from (SELECT *,sum(score) as scoresum from (select students.*,score.score from students LEFT JOIN score on students.id=score.studentid)
 as stu GROUP BY id) as stu1 where 3>(SELECT count(*) FROM (SELECT *,sum(score) as scoresum from (select students.*,score.score from students LEFT JOIN 
score on students.id=score.studentid) as stu GROUP BY id) as stu2 WHERE stu1.sex=stu2.sex and stu1.scoresum<stu2.scoresum);

4、SQL作为表使用

因为SQL的查询结果实际上是不存在的
想要使用需要给上一个别名
有可能假表和真表存在字段相同，如果假表没有名字就不能做区分了

select studentid,sum(score) as score from score group by studentid;
-- 再连接
select * from students
left join 
(select studentid,sum(score) as score from score group by studentid) as s on students.id=s.studentid;

四、视图

问题1：当前这个sql语句会经常多次的书写
问题2：再次使用 写sql不便于观看
解决方案：对这个sql语句的结果进行保存（视图）

1、视图的定义

1、视图是由查询结果形成的一张虚拟的表
2、试图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本数据发生了改变，试图也跟着改变）
3、可以跟基本表一样，进行增删改查操作（ps：增删改操作有条件限制）
4、注意：区别于使用like方式创建表----

create table a like b;(直接引用表结构)
insert into b select * from a;(需要插入数据)

5、创建过程中直接引用其他的表结构

create table test like students;

6、创建过程中直接引用其他的表

CREATE table t1 as SELECT * from students;

2、创建视图

create view tmp as select * from student_info where age=25;

1、存储为视图，存一个假的，因为相同的个数据没必要存多次，底层原理共用一个表，要变都变

create view stu1 as SELECT * from students;

3、查看视图结构

desc tmp;
show create table tmp;

4、删除视图

drop view tmp;

5、视图（虚拟表，虚表）与基本表的关系

1、一对一，增删改查

1、增加

INSERT into stu1(id,name,age,sex) values('1010','show','22','1');

SELECT * from students;

2、删除

delete from stu1 where id='1010';

select * from students;

3、改

UPDATE stu1 set name='show' where id='1007';

select * from students;

6、视图和基本表（来自于基本表的聚合） 一对一

create view stu2 as SELECT sex,avg(age) as age from students group by sex;

不能进行增删改，只能进行查询

7、视图和基本表 一对多

create view stu3 as SELECT students.*,score.subjectname,score.score,score.studentid from students left JOIN score on students.id=score.studentid;

普通的一对多只能查询
聚合的表一对多只能查询

8、用视图查询男女前三

-- 连接
SELECT students.*,score from students left join score on students.id=score.studentid;
-- 求总分
SELECT students.*,sum(score) as score from students left join score on students.id=score.studentid group by id;
-- 男女前三
select * from (SELECT students.*,sum(score) as score from students left join score on students.id=score.studentid group by id)
 as student1 where 3>(SELECT count(*) from (SELECT students.*,sum(score) as score from students left join score on students.id=score.studentid group by id)
 as student2 where student1.sex=student2.sex and student1.score<student2.score);
-- 通过试图实现男女前三
create view ss as SELECT students.*,sum(score) as score from students left join score on students.id=score.studentid group by id
SELECT * from ss as student1 where 3>(select count(*) from ss as student2 where student1.sex=student2.sex and student1.score<student2.score);

结构

SELECT * from xxx as student1 where 3>(select count(*) from xxx as student2 where student1.sex=student2.sex and student1.score<student2.score);

求班级前三

select * from ss order by score desc limit 3;