MySQL之基础篇

什么是数据库, 数据库管理系统, 数据库系统, 数据库管理员?

数据库（DataBase，DB）是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。
数据库管理系统(database management system)是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库，简称dbms。
数据库系统（database systems），是由数据库及其管理软件组成的系统。
数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统。

什么是元组, 码, 候选码, 主码, 外码, 主属性, 非主属性？

• 元组：一个元组即数据库中表的一行记录，是关系数据库中的基本概念。
• 码：表中的列，是唯一能标识实体的属性。
• 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何真子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
• 主码：主码即主键，主码是从候选码中选出的，一个实体集只能由一个主码，但可以有多个候选码。
• 外码：外码即外键，如果一个关系中的一个属性是另一个关系中的主键则这个属性为外码。
• 主属性：候选码中的所有属性列都是主属性，例如关系 员工（工号，姓名，身份证号，年龄），工号和身份证号都能够唯一标识员工实体，都是候选码，而这两个就是主属性。
• 非主属性：与主属性定义相反，没有在候选码中出现过的属性叫非主属性

什么是 ER 图？

ER图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。
由以下三个要素组成：

1. 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如用户、订单、商品、供应商等等。在 ER 图中，实体使用矩形框表示
2. 属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段，举个例子：学生实体拥有属性（学号，姓名，性别，年龄，身份证号）。在 ER 图中，属性使用椭圆形表示。
3. 联系：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。

什么是内连接、外连接、交叉连接、笛卡尔积呢？

1. 内连接（inner join）：内连接查询操作列出与连接条件相匹配的记录。
2. 外连接（outer join）：取两张表中满足连接条件的记录，以及某张表（两张表）中不满足连接条件的记录。
   2.1 左联接（left join）：是以左表为基准，然后将左表没有的对应项显示，右表的列为NULL
   2.2 右连接（right join）：是以右表为基准，然以将右表没有的对应项显示，左表的列为NULL
   2.3 全连接（full join）：完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
3. 交叉连接（cross join）:交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
4. 笛卡尔积：数学中的概念，假设集合X={a, b}，集合Y={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

说一下数据库的三大范式？

• 1NF：所有属性不可分，具有原子性。
• 2NF：基于1NF，非主键列必须完全依赖于主键，如果表是复合主键，则非主键列必须完全依赖于主键，不能仅依赖主键的一部分（即消除部分函数依赖）。
• 3NF：基于2NF，表中的非主键只依赖于主键，而不依赖于其他非主键（即消除传递依赖）。

varchar 与 char 的区别？

char：

• char的长度是不可变的
• 因为其长度固定，存取快，方便程序的存储与查找
• char最多存储255个字符，与编码无关
• char的存储方式是英文字符（ASCII）占用1个字节，汉字占用两个字节

varchar：

• varchar的长度是可变的
• 与char相反，存取慢，但不占用多余空间
• varchar最多可存65532个字符
• varchar的存储方式是英文和汉字都占用2个字节

DATETIME 和 TIMESTAMP 的异同？

1. 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
2. 存储空间：在5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节；但是在5.6.4之后，由于这两个类型允许有小数部分，所以占用的存储空间和以前不同；MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。
3. 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
4. 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

MySQL 中 in 和 exists 的区别？

1. in是把外表和内表做hash连接，先查询内表；exists是对外表做loop查询，每次loop循环再对内标查询。
2. 在外表大的时用in效率更快，内表大用exists更快
3. not in 和 not exists：如果查询语句使用了 not in，那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

drop、delete 与 truncate 的区别？

	DROP	DELETE	TRUNCATE
语言	DDL	DDL	DML
删除速度	删除速度最快	最慢	较慢
回滚	不能回滚	可以回滚	不能回滚
删除内容	表结构和数据都删除	表结构还在，删除表的全部或者一部分数据行;可以针对数据中的table、view两种类型	表结构还在，删除表的全部或者一部分数据行;只能针对table

UNION 与 UNION ALL 的区别？

• 如果使用UNIOIN，会在表连接后筛选掉重复行
• 如果使用UNIOIN ALL，不会筛选掉重复行
• UNION ALL效率比UNION快，如果合并没有需要删除重复行，建议使用UNION ALL

count(1)、count(*) 与 count(列名) 的区别？

执行效率：

• count(*)包括了所有列，会统计表中的所有的记录数，包含字段为null 的记录。
• count(1)包括了忽略所有列，会统计表中的所有的记录数，包含字段为null 的记录。
• count(列名)只包括列名那一列，会统计表中的所有的记录数，但不包括字段为null的记录。

执行速度：

• 列名为主键，count(列名)比count(1)块；反之，则count(列名)比count(1)慢
• 如果表有多个列并且没有主键，则count(1)的执行效率优于count(*)
• 如果有主键，select count(主键)最优
• 如果表中只有一个字段，则 select count（*）最优

什么是存储过程

存储过程可以看作是一些SQL语句的集合，中间可以加点逻辑控制语句。例如我们可能需要一大串SQL语句完成一个操作，这时可以使用存储过程，也方便下次调用，并且存储过程的执行效率比单纯SQL语句要快，因为存储过程是预编译过的。不过在实际业务应用比较少，因为存储过程不利于调试和扩展，且没有移植性，还会消耗数据库资源。

数据库事务

什么是事务

事务是逻辑上的一些操作，要么都执行，要么都不执行。
例如经典案例：小明要给小红转账1000元，这个转账会涉及两个关键步骤：

1. 小明的余额减少1000元
2. 小红的余额增加1000元
   那么事务就会把这两个步骤看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都失败。

事务的状态

3. 活动的（active）事务在执行时的状态叫活动状态
4. 部分提交的（partially commited）事务最后一条语句被执行后的状态叫部分提交状态
5. 失败的（failed）事务不能正常执行完的状态叫失败状态
6. 中止的（aborted）事务回滚并且数据库恢复到执行事务前的状态叫中止状态
7. 提交的（committed）事务在部分提交后，将修改后的数据写到磁盘上的状态叫提交状态

事务的特点

1. 原子性（Atomicity）：事务是最小执行单位，不允许分割。事务的原子性保证动作要么全部执行完成，要么不执行。
2. 隔离性（Isolation）：多个事务之间是隔离的，互不影响的。

   四种隔离状态：
   1. 读未提交（Read uncommitted）
   2. 读提交（Read committed）
   3. 可重复读（Repeatable read）
   4. 串行化（Serializable）
   

3. 一致性（Consistency）：执行事务前，数据保持一致
4. 持久性（Durability）：当事务操作完成后，数据将会被刷新到磁盘永久保存，即使系统故障也不会丢失。
   只有保证了事务的原子性、持久性、隔离性之后，一致性才能得到保障。

参考文献

数据库基础知识总结[https://javaguide.cn/database/basis.html]
从根上理解 MySQL 的事务[https://learnku.com/articles/39938]
面渣逆袭[https://tobebetterjavaer.com/sidebar/sanfene/mysql.html]