Java面试题030：一文深入了解MySQL（2）

欢迎大家关注我的专栏，该专栏会持续更新，从原理角度覆盖Java知识体系的方方面面。

一文吃透JAVA知识体系（面试题）https://blog.csdn.net/wuxinyan123/category_7521898.html?fromshare=blogcolumn&sharetype=blogcolumn&sharerId=7521898&sharerefer=PC&sharesource=wuxinyan123&sharefrom=from_link

1、MySQL数据类型

（1）主键int和bigint类型的区别

        有符号int最大可以支持到约22亿，远远大于我们的需求和MySQL单表所能支持的性能上限。对于OLTP应用来说，单表的规模一般要保持在千万级别，不会达到22亿上限。如果要加大预留量，可以把主键改为改为无符号int，上限为42亿，这个预留量已经是非常的充足了。
        使用bigint，会占用更大的磁盘和内存空间，内存空间毕竟有限，无效的占用会导致更多的数据换入换出，额外增加了IO的压力，对性能是不利的。

        因此推荐自增主键使用int unsigned类型，但不建议使用bigint。

（2）FLOAT、DOUBLE 和 DECIMAL

        float类型表示单精度浮点数值，double类型表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；

        MySQL官方文档有这样一句话，数据准确度取决于分配给数据类型存储的长度。其中float分配了4字节，而double分配了8字节。由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。

        float类型的数据，只分配了32位的存储空间，double类型值分配了64位，但并不是所有的实数都能转成32位或者64位的二进制形式，如果超了，就会出现截断，这就是误差的来源。 

        比如将上面例子中的131072.32转成二进制后的数据为： 
100000000000000000.0101000111101011100001010001111010111000010100011111… 
        这是一个无穷数，对于float类型，只能截取前32位进行存储，对于double只能截取前64位进行存储。所以 
        131072.32保存为float类型是存储形式为：01001000000000000000000000010100; 
        131072.32保存为double类型的格式为：0100000100000000000000000000001010001111010111000010100011110101

FLOAT

        float(M, D)两个参数的意义：表示一共能存M位，其中小数点后占D位。比如float(3,1)表示一共3位，其中小数点后1位数字。

两个误区：

1. 数据的精度总是能精确到D位，也就是数据的不精确一定出现在小数点后

2. 数据存储的时候只能存储到D位小数

第一个误区，如果对于float4字节的存储空间连整数的存储不下的时候，连整数都有误差的，更何况小数，所以存储空间大小决定存储精度，和D值无关。

上面的例子连整数都不准了，小数被完全抹去了。

第二个误区，对于存储而言，是和D无关的一个参数。因为浮点型数据最终都要被转成二进制进行存储。并且对于float，这个二进制只能有32位0和1的组合。

mysql> insert into f2 values (3456789.39);
Query OK, 1 row affected (0.01 sec)mysql> alter table f2 modify f1 float(15,4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1mysql> insert into f2 values (131072.31);
Query OK, 1 row affected (0.01 sec)

可以看到，修改一下表字段的显示宽度D，这个时候可以看到MySQL真正存储的数字是131072.3125

DECIMAL 

        decimal类型是MySQL官方唯一指定能精确存储的类型，也是DBA强烈推荐和金钱相关的类型都要存储为decimal类型。MySQL指定decimal类型最大长度为65。

如何选择

• 要精确保存6位数字左右的浮点型数据：可以使用float。

• 要精确到保存13位数字左右的浮点型数据 ：可以使用double。

• 还要精益求精，则使用decimal定点型存储 。

（3）char和varchar

        CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

        VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

如何选择CHAR与VARCHAR：

• 如果数据确定长度都⼀样，就使用定长 CHAR 类型，比如：⾝份证，md5，学号，邮编。

• 如果数据长度有变化,就使⽤变长 VARCHAR ,比如：名字，地址，但要规划好长度，保证最长的字符串能存的进去。

• 定长 CHAR 类型比较浪费磁盘空间，但是效率高。

• 变长VARCHAR 类型比较节省磁盘空间，但是效率低。

• 定长CHAR 类型会直接开辟好对应的存储空间。

• 变长VARCHAR 类型在不超过定义长度范围的情况下⽤多少开辟多少存储空间。

（4）BLOB 和 TEXT

        BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件。

BLOB

        其中最常用的就是 blob 字段类型了，最多可存储 65KB 大小的数据，一般可用于存储图标或 logo 图片。不过数据库并不适合直接存储图片，如果有大量存储图片的需求，请使用对象存储或文件存储，数据库中可以存储图片路径来调用。

TEXT

        一般情况下，遇到存储长文本字符串的需求时可以考虑使用 text 类型。

        在日常场景中，存储字符串还是尽量用 varchar ，只有要存储长文本数据时，可以使用 text 类型。对比 varchar ，text 类型有以下特点：

• text 类型无须指定长度。

• 若数据库未启用严格的 sqlmode ，当插入的值超过 text 列的最大长度时，则该值会被截断插入并生成警告。

• text 类型字段不能有默认值。

• varchar 可直接创建索引，text 字段创建索引要指定前多少个字符。

• text 类型检索效率比 varchar 要低。        

2、数据库的三大范式

• 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。

        不符合第一范式的表：属性还可以再分割

修改后的表：每个字段原子性，不可再分割

• 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

        这张表中的stu_id和kc_id构成了联合主键。score没问题，它完全依赖于联合主键，stu_id和kc_id两个值才能决定score的值。但是kc_name只依赖于kc_id，这是部分依赖关系，不符合第二范式。

修改后的表：分为两张表，每张表都符合第二范式。

• 第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

        sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。(id -> sex_code -> sex_desc)

修改后的表：分为两张表，每张表都符合第三范式。

3、数据表操作

（1）创建表

        MySQL实现了三种创建表的方法，支持自定义表结构或者通过复制已有的表结构来创建新表。

CREATE TABLE

        CREATE TABLE 语句的基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name
(column1 data_type column_constraint,column2 data_type,...,table_constraint
);

使用该语句时，我们需要手动定义表的结构。以上包含的内容如下：

• IF NOT EXISTS 表示当该表不存在时创建表，当表已经存在时不执行该语句。

• table_name 指定了表的名称。

• 字段的定义:columnN 是字段的名称，data_type 是它的数据类型；column_constraint 是可选的字段约束；多个字段使用逗号进行分隔。

• table_constraint 是可选的表级约束。

其中，常见的约束包括主键、外键、唯一、非空、检查约束以及默认值。

CREATE TABLE … LIKE

        复制已有表结构的方法，只复制表结构，包括字段的属性和索引，但是不复制数据。

CREATE TABLE [IF NOT EXISTS] table_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }

        目标表会保留原始表中的主键、唯一约束、非空约束、表达式默认值、检查约束（自动生成约束名），同时还会保留原始表中的计算列定义。CREATE TABLE … LIKE 命令不会保留外键约束（但是会保留外键索引）。

        只能基于表进行复制，而不能复制视图。

CREATE TABLE … SELECT

        利用查询语句的结果定义字段和复制的数据。

CREATE TABLE table_name[AS] SELECT ...;

例如：创建一个新表：emp_devp，表中包含了研发部的所有员工。

CREATE TABLE emp_devpAS
SELECT e.*FROM employee eJOIN department dON (d.dept_id = e.dept_id AND d.dept_name = '研发部');

        如果只想复制结构，不需要复制数据，可以在查询语句中增加 LIMIT 0 或者 WHERE 1=0 条件。

        该方式不会自动创建任何索引，这样可以使得该语句尽量灵活。如果想要创建索引，可以在 SELECT 语句之前指定。例如：

CREATE TABLE t3(id INTEGER PRIMARY KEY)
AS SELECT col1 as id, col2 FROM t1;

        CREATE TABLE … SELECT 命令不会保留计算列的定义，也不会保留默认值定义。同时某些数据类型可能产生转换。例如，AUTO_INCREMENT 属性不会被保留，VARCHAR 类型被转换为 CHAR 类型。

        查询语句中的函数和表达式需要指定一个别名，否则该命令可能失败或者创建意料之外的字段名。

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

（2）删除表

        清空表数据命令：truncate

truncate table 表名

• 不能与where一起使用。

• truncate删除数据后是不可以rollback的。

• truncate删除数据后会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。

• truncate删除数据后不写服务器log，整体删除速度快。

• truncate删除数据后不激活trigger(触发器)。

• 当仍要保留该表，但要删除所有数据表记录时， 用 truncate

        删除表命令：drop

drop table if exists 表名;

        truncate只会清除表数据，drop不光清除表数据还要删除表结构。

        当不再需要该表时， 用 drop。

        删除数据：delete

delete from 表名 where id='1';

• delete可以删除一行，也可以删除多行；

• 如果不加where条件，则是删除表所有的数据，这是很危险的！不建议这样做！

（3）数据表的约束

        为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。约束的最终目标：保证数据的完整性和可预期性。

        主键约束（PRIMARY KEY）

        用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。

create table student(id int primary key,name varchar(20)
);

或

create table student01(id intname varchar(20),primary key(id)
);

        非空约束（NOT NULL）

        字段的值不能为空。

create table student02(id intname varchar(20) not null
);

        外键约束（FOREIGN KEY）

        外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。从表里的外键通常为主表的主键，从表里外键的数据类型必须与主表中主键的数据类型一致。

        外键主要有以下几个作用：

• 维护数据的一致性：防止独立记录的存在，比如在一个订单表中，如果引用一个不存在的客户ID，那么这条记录是无效的。

• 确保引用完整性：通过外键约束，可以确保父表中的记录在子表中的存在。

• 级联操作：外键支持级联删除和更新操作，即在父表中删除或更新记录时，子表中的相关记录也会同步删除或更新。

CREATE TABLE child_table (child_id INT PRIMARY KEY,parent_id INT,CONSTRAINT fk_parentFOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
);

或

ALTER TABLE child_table
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id);

        MySQL 会自动为外键列创建索引，但显式创建索引通常会提高查询性能。

        通常不能直接修改外键约束，需要先删除旧的约束，然后添加新的约束。

ALTER TABLE Orders DROP FOREIGN KEY fk_customer;
ALTER TABLE Orders ADD CONSTRAINT fk_customer_new
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE SET NULL;

        唯一性约束（UNIQUE）

        唯一键的主要目的是保证表中的某些列的数据唯一性，不一定用作行标识。唯一键更多的是保证在业务上，不要和别的信息出现重复。

create table student(id int,name varchar(20) unique
);

id 是主键，保证每个用户有唯一的标识。name 是唯一键，保证用户名不能重复。

mysql> ALTER TABLE student ADD UNIQUE(email);

        默认值约束（DEFAULT）

        用于给数据表中的字段指定默认值。

create table if not exists student(name varchar(20) not null,gender char(2) default '男',age tinyint unsigned default 18
);

数据表的查询操作和索引放到下一节。

Java面试题029：一文深入了解MySQL（1）