这一篇我们会讲解下mysql的一些稍微有点难度的内容,目前的互联网公司,面试的时候问mysql的时候也还是挺多的,不仅仅开发岗位,有些测试岗位在面试的时候,也需要会mysql,所以本篇比上一篇的基础篇要稍微难点。
主要会讲解以下内容:- 多表联合查询
- 索引命中规则
- 表结构设计三范式
- 查询优化技巧
1、联合查询
多个表联合查询,是说查询的时候,同时查询2个或者2个以上的表,为什么要这么做呢?是因为数据都是按分类存储的,往往学生信息单独存储在一张表,学生成绩会存储在另外一张表,所以要同时查询出学生的姓名和成绩的时候,就需要联合2个表,同时查询。也可以先看看第3节的表结构设计三范式,加深一下理解。
继续上一节的内容,假设我们除了有学生表,还有一张学生成绩表(student_score),现在我们就有2张表,表的结构如下:
CREATE TABLE `student` (
`num` int(11) NOT NULL COMMENT '学号',
`name` varchar(10) DEFAULT '' COMMENT '姓名',
`age` smallint(6) DEFAULT 0 COMMENT '年龄',
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息';
CREATE TABLE `student_score` (
`num` int(11) NOT NULL COMMENT '学号',
`type` varchar(10) DEFAULT '' COMMENT '0数学 1语文 2英语',
`score` smallint(6) DEFAULT 0 COMMENT '考试分数',
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生考试分数表';
1.1 简单关联
#查询学生语文成绩
select
a.num,
a.name,
a.age,
b.type,
b.score
from
student a join student_score b
on
a.num = b.num
where
b.type = 1
#使用left join 或者right join
# a left join b 是说如果a表中有num=1的学生,b表中没有num=1的学生的成绩,也有返回结果,只是有学生信息,没有成绩
# a right join b 是正好反过来
# a join b 如果是上述情况,则不会返回数据
select
a.num,
a.name,
a.age,
b.type,
b.score
from
student a left join student_score b
on
a.num = b.num
#查询男生和女生的平均成绩,这里要用到avg这个函数,但是这里主要是想讲解
#group by 的用法,
select
(case when a.sex = 0 then '女生' else '男生' end) as 性别,
avg(b.score) as '平均成绩'
from
student a join student_score b
on
a.num = b.num
group by a.sex
# 如果查询语句是: select a,b,c,sum(d),avg(f) from table
# 那么必须要使用group by ,group by的列,用逗号分隔,且所有非聚合函数的列,都必须在group by的后面,
# 这个例子就是a,b,c这3个列
#正确SQL:
select a,b,c,sum(d),avg(f) from table where a =10 group by a,b,c
来看下left join的结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IDMXLdsU-1609395437886)(/img/mysql06.png)]
我们给student_score表中插入记录,可以看到已经可以查询出记录来了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2HF7fDiB-1609395437889)(/img/mysql07.png)]
这里一定要学会join/left join/right join 并且知道区别
这里有以下几点需要注意:
-
多个表查询,需要使用别名,就是表的名字后面跟的a、b,可以用来区分每个表
-
注意 a.num = b.num 和 b.type = 1的不同位置,可能有的地方会教大家不使用on的版本,如下:
#查询学生语文成绩 不使用on的版本,不推荐这种写法 select a.num, a.name, a.age, b.type, b.score from student a,student_score b where a.num = b.num and b.type = 1
这种写法是不推荐的,模糊了表的关联条件和过滤条件,关联条件是2个表的纽带,on后面跟的是关联条件,过滤条件是数据查询出来之后,where之后的内容,尽量分开写。
-
尽量查询需要的字段。
-
关联条件一定要写正确,如果有2个关联条件,但是只写了1个,就会导致笛卡尔积(这里不多讲了),导致数据错误,一般会返回更多的数据行。
1.2 子查询
mysql支持子查询,可以做1个临时表,例如上面的语句可以修改为:
select
a.num,
a.name,
a.age,
b.type,
b.score
from
(select num,name,age from student) a
left join
(select type,score from student_score) b
on
a.num = b.num
尽管这么修改毫无意义,但是在某些情况下,不得不使用子查询,例如有1个表category,里面存储了商品的分类信息,表有以下4个字段:id name level p_id,分别表示分类ID,名称,级别(1级,2级,3级),父ID
这个时候如果要查询这样的结果:
1级ID 一级分类名称 2级ID 二级分类名称
100 数码 100100 手机
就需要使用子查询了:
select
a.id,
a.name,
b.id,
b.name
from
(select id,name from category where level = 1) a
join
(select id,name,p_id from category where level = 2) b
on
a.id = b.p_id
2、索引命中规则
单个索引 ,例如student表中的列,num是有索引的,那么下面几种将命中索引:
- num = 10
- num in (10,20) in里面最多有多少个,取决于MYSQL允许SQL的最大有多长。
- num between 10 and 20
- num > 10
下面这几种,将不会命中索引
- num + 1 = 10 做运算不会生效
- abs(num) = 10 函数运算之后不会生效
- name like ‘%明%’ 假设name列建了索引,用like不会生效
如果是联合索引,例如建立了一个num,name,sex的联合索引,索引的顺序是num,name,sex,那么命中索引的情况如下:
-
num = 10 and name = ‘小明’ and sex = 1
-
num in (10,20) and name = ‘小明’
-
num = 10 and name like ‘%小明%’ 这里索引只会命中num列,name的不会命中
-
sex = 1 and name = ‘小明’ and num = 10 和顺序无关,只要有num、name、sex就可以
如下情况则不会命中
- name = ‘小明’ 遵循左原则,单独查询name不会生效
- sex = 1 遵循左原则,单独查询sex不会生效
- name = ‘小明’ and sex = 1遵循左原则,单独查询name、sex不会生效
记住,联合索引要生效,左边的条件必须有,否则不生效。
3、表结构设计三范式
自己体会一下吧,相信对于大家来说都不难理解。
- 第一范式**(1st NF** -列都是不可再分)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9oYFstXu-1609395437892)(/img/mysql03.png)]
- 第二范式**(2nd NF**-每个表只描述一件事情)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wD6r7SX8-1609395437895)(/img/mysql04.png)]
-
第三范式**(3rd NF**- 不存在对非主键列的传递依赖**)**
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SyAXAi0C-1609395437899)(/img/mysql05.png)]
数据库设计三范式,是一个设计数据库结构的参考,遵循了这个设计规则,不一定就是好的设计。在实际的项目设计中,还是要以实际情况为准,可能会把1个表的列,弄的特别多,例如20个列,30个列,甚至100个列都有可能,也有的团队为了以后容易扩展,提前设计了冗余的列,也有的时候,一个列例如商品的简称,在多个表都存在,这些都是违反了设计规则的,但是确实是存在的。数据字段的冗余,是一种空间换时间的概念,也用的挺好。
对于新手的朋友们来说,首先要参考的就是三范式,然后要根据实际的情况,做出调整,该违反就要违反,规则可以创新,只要有利于你的开发就行,数据库的列,一旦定好了,很少会删除,但是可以新增,这一点要清楚。
再说一个关于设计列的注意事项,就是要仔细考虑是否可以是NULL,NULL是编程届绕不开的一个话题,关于这点,我的建议是不要为NULL,可以设置默认值,在JAVA代码开发中,也应该尽量给个初始值,避免用NULL表示某个特殊的含义,如果你的代码中出现了以下这种情况:
if(data == null) {
do something
}
if(data != null) {
do something
}
请尽量优化。
4、查询优化技巧
- 尽量能命中索引。
- 如果有联合查询,小表放前面,大表在后面。
- 尽量查询需要的字段,不要使用select *。
- 如果有子查询做多表联合查询,尽量先使用where条件降低数据量,再做关联查询。
- 注意索引失效的几种方式,对列做运算或者使用函数会使索引失效。
其它 提高内容
mysql还有2个用的比较少的关键字HAVING EXISTS,下面简单说一下这2个关键字
HAVING
一般是和group by 搭配使用的,用来分组,例如,你有一个订单表,order_id,sku_id,amount,create_date这4列
统计订单的金额:
select
order_id,
sum(amount) as total
from order
where
create_date = '2020-01-01'
group by order_id
现在需要加1个过滤条件,哪些订单的金额大于100元
select
order_id,
sum(amount) as total
from order
where
create_date = '2020-01-01'
group by order_id
having total > 100
只需要记住,当使用sum()这类聚合的函数的时候,如果需要对聚合的值进行过滤,可以使用
EXISTS和NOT EXISTS
先看exist吧,exist子查询会返回1个true或者false,如果返回了true,就显示对应的数据,可以先来1个简单的SQL看看
假设我们有1张表sku,存储了sku的基本信息,另外一个表就是刚才的订单表order
select * from order where exists (select 1)
由于select 1总是会返回值,所以这个SQL,相当于:
select * from order where 1=1
所以我们 一般不这么用,而是把前1个表的字段和子查询的字段关联起来,例如要查询sku_id在1到100之间的,有订单的SKU的信息
select
a.*
from sku a
where
a.sku_id in
(
select sku_id from order where sku_id between 1 and 100
)
也可以使用EXISTS
select
a.*
from sku a
where
exists
(
select b.sku_id from order b where a.sku_id = b.sku_id and a.sku_id between 1 and 100
)
NOT EXISTS就是不满足的意思,恰好相反。
下面来看一个更复杂的场景,假设我们有3张表,
- student 学生表 ,有id name2个字段 分别表示学生的学号和姓名
- course 课程表 有 c_id c_name 2个字段 分别表示课程的ID和名称
- score 成绩表 id,c_id,score 3个字段 分别表示学号、课程ID、分数
刚开始的数据是,小明选了所有的3门课程,ABC,小丽选择了AB两门课程,没有选择C
假设学生只要选学了课程,就一定有分数,那么怎么查出来,哪些学生选学了所有的课程呢?我们的一般思维是,把课程数量算出来,select count(c_id) from course ,然后查询成绩表,看看学生的课程数量
select id,count(c_id) from score ,最后关联学生表,查询学生的成绩,用子查询就是:
select d.name
from
(select a.id
from
(select id,count(c_id) as _count from score group by id) a
join
(select count(c_id) as _count from course) b
where
a._count = b._count ) c join student d
on c.id = d.id
那么用exists来怎么写呢?s
select name from student a
where not exists
(
select c_id from course b
where not exists
(select id from score c where a.id = c.id and b.c_id = c.c_id)
)
这个SQL有点复杂,似乎很难理解,先分析最里面的not exists ,可以降低难度,先分析最里面的exists是什么意思?
select c_id from course b
where exists
(select id from score c where a.id = c.id and b.c_id = c.c_id)
查询所有的课程,看看是不是有分数,对于小明来说,小明所有课程有分数,返回TRUE,所以如果变更为not exists,就变成FALSE了,外侧的: select name from student a where not exists 正好是需要FALSE,所以小明命中,他选择了所有课程。
对于小丽来说,内侧的这个语句,很明显,小丽的C课程没有分数,所以NOT EXISTS返回TRUE,但是外侧也是NOT EXISTS,需要返回FALSE才能命中,所以小丽不符合条件,得出,小丽没有选择所有的课程。
大家可以思考下,下面这个写法,为什么不行?(归根到底的原因是EXISTS只有有记录返回,就算TRUE,所以内侧的EXISTS,只要选了课程,一定会返回TRUE,外侧的EXISTS,也会是TRUE,这个SQL表示查询至少选择了1门课程的学生)
select name from student a
where exists
(
select c_id from course b
where exists
(select id from score c where a.id = c.id and b.c_id = c.c_id)
)
希望对你有帮助。
全文完。
关注我的博客,获取更多Java编程知识: 双King的技术博客