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?
他没有向视图中添加数据?
视图是对表的引用,如果你删除了对应的表,视图也会消失不见
视图如果是对一张表的引用,可以进行增删改查的操作,如果是表的聚合,多张表的操作
增删改查都不行