JAVA新手入门05～MYSQL提高篇


JAVA基础
作者: 双king
浏览量:2次

这一篇我们会讲解下mysql的一些稍微有点难度的内容，目前的互联网公司，面试的时候问mysql的时候也还是挺多的，不仅仅开发岗位，有些测试岗位在面试的时候，也需要会mysql，所以本篇比上一篇的基础篇要稍微难点。

主要会讲解以下内容：

  • 多表联合查询
  • 索引命中规则
  • 表结构设计三范式
  • 查询优化技巧

1、联合查询

多个表联合查询，是说查询的时候，同时查询2个或者2个以上的表，为什么要这么做呢？是因为数据都是按分类存储的，往往学生信息单独存储在一张表，学生成绩会存储在另外一张表，所以要同时查询出学生的姓名和成绩的时候，就需要联合2个表，同时查询。也可以先看看第3节的表结构设计三范式，加深一下理解。

继续上一节的内容，假设我们除了有学生表，还有一张学生成绩表(student_score)，现在我们就有2张表，表的结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
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 简单关联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#查询学生语文成绩
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的结果

image-20201205130701583

我们给student_score表中插入记录，可以看到已经可以查询出记录来了

image-20201205135110607

这里一定要学会join/left join/right join 并且知道区别

这里有以下几点需要注意：

  • 多个表查询，需要使用别名，就是表的名字后面跟的a、b，可以用来区分每个表

  • 注意 a.num = b.num 和 b.type = 1的不同位置，可能有的地方会教大家不使用on的版本，如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    #查询学生语文成绩 不使用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个临时表，例如上面的语句可以修改为:

1
2
3
4
5
6
7
8
9
10
11
12
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 手机

就需要使用子查询了：

1
2
3
4
5
6
7
8
9
10
11
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** -列都是不可再分)

image-20201205124834334

  • 第二范式**(2nd NF**-每个表只描述一件事情)

image-20201205124922738

  • 第三范式**(3rd NF**- 不存在对非主键列的传递依赖**)**

    image-20201205124945554

数据库设计三范式，是一个设计数据库结构的参考，遵循了这个设计规则，不一定就是好的设计。在实际的项目设计中，还是要以实际情况为准，可能会把1个表的列，弄的特别多，例如20个列，30个列，甚至100个列都有可能，也有的团队为了以后容易扩展，提前设计了冗余的列，也有的时候，一个列例如商品的简称，在多个表都存在，这些都是违反了设计规则的，但是确实是存在的。数据字段的冗余，是一种空间换时间的概念，也用的挺好。

对于新手的朋友们来说，首先要参考的就是三范式，然后要根据实际的情况，做出调整，该违反就要违反，规则可以创新，只要有利于你的开发就行，数据库的列，一旦定好了，很少会删除，但是可以新增，这一点要清楚。

再说一个关于设计列的注意事项，就是要仔细考虑是否可以是NULL，NULL是编程届绕不开的一个话题，关于这点，我的建议是不要为NULL，可以设置默认值，在JAVA代码开发中，也应该尽量给个初始值，避免用NULL表示某个特殊的含义，如果你的代码中出现了以下这种情况:

1
2
3
4
5
6
7
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列

统计订单的金额:

1
2
3
4
5
6
7
select
	order_id,
	sum(amount) as total
from order
where
create_date = '2020-01-01'
group by order_id

现在需要加1个过滤条件，哪些订单的金额大于100元

1
2
3
4
5
6
7
8
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

1
select * from order where exists (select 1)

由于select 1总是会返回值，所以这个SQL，相当于:

1
select * from order where 1=1

所以我们 一般不这么用，而是把前1个表的字段和子查询的字段关联起来，例如要查询sku_id在1到100之间的，有订单的SKU的信息

1
2
3
4
5
6
7
8
select
a.*
from sku a
where
a.sku_id in
(
select sku_id from order where sku_id between 1 and 100
)

也可以使用EXISTS

1
2
3
4
5
6
7
8
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 ，最后关联学生表，查询学生的成绩，用子查询就是:

1
2
3
4
5
6
7
8
9
10
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

1
2
3
4
5
6
7
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是什么意思？

1
2
3
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门课程的学生）

1
2
3
4
5
6
7
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)
)

希望对你有帮助。

全文完。