表连接及视图

mysql三大范式

1. 原子性 字段不可在分割(也就是字段尽量分割到不能再分了)

2. 唯一性 字段必须依赖于主键  （你的其余字段需要和主键有一些关联关系 ）

3. 冗余性（多余的字段不要出现，否则我们的数据量会过大）

表连接union

（其实只是将2张表合在一起，不是特别常用）

这是一张存储学生信息的表                                   这是存储学生成绩的表（一共60行截了部分）       

 union格式：查询语句1 union （all） 查询语句2          --all表示不去重

select * from student 
UNION
select * from score;

结果是将上面的两张表连接到了一块 ，以第一张表的字段为基准，作为新的字段

 

我们的表student和score的字段都是三个，我如果是拿两个字段数量不同的的拼接就会报错

              

select * from table1
UNION
select * from table2;
 

 

 我们一行数据如果完全相同union会帮我们自动去重，后面加上all就会保留重复的行

select * from  student 
UNION all
select * from  student;

结果确实是全部显示了： 

表连接join

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

格式：查询语句 left join 表 on 连接条件

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

 

（socore中没有关于1011的信息，用null填充了） 

 

上面的表看起来有冗余信息，我们需要筛选出有用的信息

-- 这里的studnet.id也可以换成score.studentid，
select student.id,name,age,sex,subjectname,score from student 
left join
score
on student.id=score.studentid;

看起来清爽多了，符合 mysql三大范式的需求

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

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

那么左右表的转换应该怎么做？说白了就是字段的顺序，我们还是和上面的一样手写所有字段

select student.id,name,age,sex,subjectname,score from  score
left join
student
on student.id=score.studentid;

结果确实看起来像是转换过了

（student表中没有关于1010的信息）

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

我们写个案例理解一下，还是上面的数据，

select student.id,name,age,sex,subjectname,score from  score
inner join
student
on student.id=score.studentid;

我们score中的数据是比student中多出一个1010的

 使用inner join后，是按照两张表的公共部分显示 

表连接进阶

1. 链表联查,并加上总分

我们还是以上面的表为基准，我现在希望查询总分成绩，加上id和name.

select id,name,sum(score) as score from(select student.id,name,sex,age,score from student
inner join score 
on student.id=score.studentid) as stu1 
group by id;

 2.查询前3名

select id,name,sum(score) as score from(select student.id,name,sex,age,score from student
inner JOIN
score ON
student.id=score.studentid) as stu1 
group by id 
order by score desc limit 3;

就是再上面的基础上加了降序排序和显示前3行数据 

 3.TOPN

查寻男女组的前三名

我们先写好模板再将语句往里面嵌套

select * from test as a where 3>(select * from test as b where a.sex=b.sex and a.score>b.score)

将下面的语句替换test 记得加括号！！！

select id,name,sex,sum(score) as score from(select student.id,name,sex,age,score from student
inner JOIN
score ON
student.id=score.studentid) as stu1 
group by id 

select * from (select id,name,sex,sum(score) as score from(select student.id,name,sex,age,score from student
inner JOIN
score ON
student.id=score.studentid) as stu1 group by id)
as a where 3>(select count(*) from (select id,name,sex,sum(score) as score from(select student.id,name,sex,age,score from student
inner JOIN
score ON
student.id=score.studentid) as stu1 group by id )
as b where a.sex=b.sex and a.score<b.score);

现在的语句一行太长了不方便观看，可以使用美化功能将他们转换为多行显示

这里同样的查询语句我写了2遍，可不可以有像变量的用法，我定义一次后可以多次调用呢？

create table x as select student.id,name,age,sex,sum(score) as score from student 
inner JOIN
score ON
student.id=score.studentid
group by id 
select * from x as a where 3>(select count(*) from x as b where a.sex=b.sex and a.score<b.score);

查询的虚拟表 加载到表x中，方便多次使用

判断逻辑

if的语法: if( 判断条件, 内容1,  内容2)

判断条件是是true,返回内容1，false返回内容2

例子：这是一个a和b的字段，我们现在查询他们相加的结果，但是有问题，与null相加后都会变成null

if的作用就来了，我先判断b的内容是不是null,是null就用0代替，不是就无事发生

select a+if(b is null,0,b) from table1;

 

case:CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

select  case when b=1 then '这是1' when b=2 then '这是2' else  '未知的数字' end from table1;

 视图

引入：从其他表加载数据

方式1：创建表的同时插入了数据

命令：create table xxx  as  select * from ... insert into table select xxx

create table studenttest as select * from student;
insert into table select studenttest;

确实完全复制了student表中的内容 

 方式2 ：自己先建一个表，然后将student中的数据导入到自己建立的表中

create table studentcopy(id int(11),name varchar(255),age int(11),sex enum('0','1'))ENGINE=INNODB;

insert into studentcopy SELECT * from student;

 

创建视图：

命令：create view 视图名 as select 字段名称... from 表名;

create view stuview as select * from student ;

 查看视图的内容：

 这里的控制台信息有些不太一样，我们创建表加载数据的时候是受影响行数12，这里怎么会是0？

他没有向视图中添加数据？

视图是对表的引用，如果你删除了对应的表，视图也会消失不见

 视图如果是对一张表的引用，可以进行增删改查的操作，如果是表的聚合，多张表的操作

增删改查都不行