2023年MySQL核心技术面试第一篇

目录

一 . 存储：一个完整的数据存储过程是怎样的？

1.1 数据存储过程

1.1.1 创建MySQl 数据库

1.1.1.1 为什么我们要先创建一个数据库，而不是直接创建数据表？

1.1.1.2基本操作部分

1.2 选择索引问题

二 . 字段：这么多的字段类型，应该如何定义

2.1 简介

2.1.1 例子：

2.1.1.1解释：

2.2 整数类型

2.2.1 如何选择合适的整数类型

2.3 浮点数类型和定点类型

2.3.1 为什么浮点数类型的无符号只有有符号的一半取值范围？

2.3.2 浮点数的精度问题

2.3.2.1 建表：

2.3.2.2 然后插入数据

2.3.2.3 查看表里面的数据

2.3.2.4 利用SQl语句进行价格相加：

2.3.2.4.1 sum：

一 . 存储：一个完整的数据存储过程是怎样的？

1.1 数据存储过程

MySQL是怎么进行数据存储的。

存储数据是处理数据的第一步，对各种繁杂的数据，进行有序和高效地存储起来。

在MySQL中，完整的数据存储过程共有4步，分别是创建数据库，确认字段，创建数据表，插入数据。

1.1.1 创建MySQl 数据库

数据存储的第一步就是创建数据库。

1.1.1.1 为什么我们要先创建一个数据库，而不是直接创建数据表？

数据库是MySQL里面最大的存储单元，系统架构层次上看，MySQL数据库系统，从小到大依次是数据库服务器，数据库，数据表，数据表的行与列。

没有数据库，数据表就没有载体，就无法存储数据。

1.1.1.2基本操作部分

创建数据库

create database demo；

删除数据库

drop database demo；

查看数据库

show databases；

创建数据表：

create table demo.test

(

barcode text,

goodsname text,

price int

)；

查看表结构

describe demo.test;

查看所有表

show tables；

添加主键

alter table demo.test

add column itemnumber int primary key auto_increment

向表添加数据

insert into demo.test

（barcode，goodsname，price）

values（'0001'，'本'，3）；

1.2 选择索引问题

select count(*) from t; t中有id(主键)，name，age,sex4个字段。假设数据10条，对sex添加索引。用explain 查看执行计划发现用了sex索引，为什么不是主键索引呢?主键索引应该更快的.

解答：

MySQL Innodb的主键索引是一个B+树，数据存储在叶子节点上，10条数据，就有10个叶子节点。
1. sex索引是辅助索引，也是一个B+树，不同之处在于，叶子节点存储的是主键值，由于sex只有2个
可能的值：男和女，因此，这个B+树只有2个叶子节点，比主键索引的B+树小的多
2. 这个表有主键，因此不存在所有字段都为空的记录，所以COUNT(*)只要统计所有主键的值就可以
了，不需要回表读取数据
3. SELECT COUNT(*) FROM t，使用sex索引，只需要访问辅助索引的小B+树，而使用主键索引，要
访问主键索引的那个大B+树，明细工作量大，这就是为什么，优化器使用辅助索引的原因

二 . 字段：这么多的字段类型，应该如何定义

2.1 简介

MySQl中有很多字段类型，比如整数，文本，浮点数。

2.1.1 例子：

在销售流水表中，需要定义商品销售的数量。由于有称重
商品，不能用整数，想当然地用了浮点数，为了确保精度，还用了 DOUBLE 类型。
结果却造成了在没有找零的情况下，客人无法结账的重大错误，DOUBLE 类型是不精准的，不能使用。

2.1.1.1解释：

浮点数在计算机中的内部表示是二进制的，而不是十进制的。对于某些常见的十进制小数（如0.1），其在二进制表示中是一个无限循环的小数。这样就存在一些十进制小数无法准确转换为浮点数的二进制表示。

当进行浮点数计算时，舍入误差会逐渐累积。即使看似简单的计算，例如0.1 + 0.1 + 0.1，也可能产生一个微小的舍入误差。这意味着在处理货币或计量单位时，通过浮点数计算得到的结果可能与预期的结果有细微差异。

在结账场景中，如果使用浮点数（DOUBLE）存储商品销售的数量和金额，并进行计算，那么可能会出现舍入误差。例如，如果商品价格是0.1元，数量是3个，正确的总金额应该是0.3元。但由于浮点数的舍入误差，实际计算时可能得到一个接近0.30000000000000004的结果。这样就导致无法准确匹配预期的金额，客人无法正确结账。

因此，在处理与货币或计量单位相关的数据时，浮点数（DOUBLE）类型不是一个理想的选择，因为它可能引发舍入误差和精度问题。更好的选择是使用固定点数类型（如DECIMAL），它可以提供更高的精确度和准确性来处理这些情况，避免结账错误的发生。

2.2 整数类型

整数类型一共5种：tinyint ，smallint，mediumint，int（integer），bigint。

2.2.1 如何选择合适的整数类型

需要考虑存储空间和可靠性的平衡问题：

1.占用字节数少的整数类型可以节省出存储空间，如果太小了，可能会出现超出取值范围的情况，引发系统问题。

例子：

在我们的项目中，商品编号采用的数据类型是 INT。
我们之所以没有采用占用字节更少的 SMALLINT 类型整数，原因就在于，客户门店中流通的
商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。如果使
用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范
围 65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可
靠性的问题。

注意：实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本，我们应该首先确保数据不会超过取值范围，在这个前提下考虑如何节省存储空间。

2.3 浮点数类型和定点类型

浮点数和定点数的特点是可以处理小数，将整数看成小数的特例。

浮点数类型：float，double，real

float 表示单精度浮点数；4字节

double 表示双精度浮点数 8字节

real 默认 double，

如果要float：set sql_mode = " real_as_float";

2.3.1 为什么浮点数类型的无符号只有有符号的一半取值范围？

原因是，MySQL 是按照这个格式存储浮点数的：符号（S）、尾数（M）和阶
码（E）。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此，所谓
的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

2.3.2 浮点数的精度问题

2.3.2.1 建表：

CREATE TABLE demo.goodsmaster
(
barcode TEXT,
goodsname TEXT,
price DOUBLE,
itemnumber INT PRIMARY KEY AUTO_INCREMENT
);

2.3.2.2 然后插入数据

-- 第一条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0001',
'书',
0.47
);
-- 第二条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0002',
'笔',
0.44
);
-- 第三条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0002',
'胶水',
0.19
);

2.3.2.3 查看表里面的数据

SELECT * from demo.goodsmaster;

结果：

mysql> SELECT *
-> FROM demo.goodsmaster;
+---------+-----------+-------+------------+
| barcode | goodsname | price | itemnumber |
+---------+-----------+-------+------------+
| 0001 | 书 | 0.47 | 1 |
| 0002 | 笔 | 0.44 | 2 |
| 0002 | 胶水 | 0.19 | 3 |
+---------+-----------+-------+------------+
3 rows in set (0.00 sec)

2.3.2.4 利用SQl语句进行价格相加：

SELECT SUM(price)
FROM demo.goodsmaster;

2.3.2.4.1 sum：

关键字sum，MySQL的求和函数，MySQL聚合函数的一种，知道这个函数表述计算字段值的和就可以了。

我们应该理想的值，0.47+0.44+0.19 =1.1

结果：

mysql> SELECT SUM(price)
-> FROM demo.goodsmaster;
+--------------------+
| SUM(price) |查询结果是 1.0999999999999999

将类型改成float，输出的值为1.0999999940395355，误差更大。

当我们需要进行值对比作为条件进行查询的时候，就会发生误差。

比如：

SELECT *
FROM demo.goodsmaster
WHERE SUM(price)=1.1

原因;