MySQL

• 1: MySQL字符集和比较规则
• 2: MySQL数据库操作
• 3: MySQL数据库查询
• 4: MySQL数据类型
• 5: MySQL条件查询
• 6: MySQL索引
• 7: MySQL表操作
• 8: MySQL连接
• 9: 事务
• 10: 数据库设计规范

1 - MySQL字符集和比较规则

字符集

计算机的世界里，它是只认识 0 和 1 的, 那汉字怎么办? 英文怎么办?

我们在屏幕上看到的英文、汉字等字符是二进制数转换之后的结果

通俗的说，按照何种规则将字符存储在计算机中，如’a’用什么表示，称为"编码"；反之，将存储在计算机中的二进制数解析显示出来，称为"解码",如果使用了错误的解码规则，则导致’a’解析成’b’或者乱码。

ASCII字符集&编码

ASCII(American Standard Code for Information Interchange，美国信息交换标准代码)

是基于拉丁字母的一套电脑编码系统。它主要用于显示现代英语

ASCII的最大缺点是只能显示26个基本拉丁字母、阿拉伯数目字和英式标点符号，因此只能用于显示现代美国英语

已经淘汰

GBXXXX字符集&编码

计算机发明之处及后面很长一段时间，只用应用于美国及西方一些发达国家，ASCII能够很好满足用户的需求。

但是当天朝也有了计算机之后，为了显示中文，必须设计一套编码规则用于将汉字转换为计算机可以接受的数字系统的字符集。

什么都有, 基本东亚文字全了

GBK是对GB2312-80的扩展, 收录了繁体

Unicode字符集&UTF编码

你说的对, 但是每个国家有自己的编码

那网络聊天怎么办? 编码不同根本无法识别

为了解决这个问题，一个伟大的创想产生了——Unicode。Unicode编码系统为表达任意语言的任意字符而设计。 被几种语言共用的字符通常使用相同的数字来编码, 不存在二义性。不再需要记录"模式"了

MySQL的UTF-8最多支持三个字节

UTF-8mb4支持四个字节

比较规则

2 - MySQL数据库操作

十分钟快速上手MySQL

创建数据库

create database 数据库名;

提示:

1. 上下箭头可以切换之前执行的指令
2. 记得加分号

切换数据库

use 数据库名;

删除数据库

提示: 这个指令一般不用😂

drop database 数据库名;

了解 表

Java交互MySQL

上述都是命令行操作

如何在Java程序对数据库进行操作?

驱动 和显卡驱动, 网卡驱动类似, Java程序想要对MySQL做操作时也需要这样的桥梁

MySQL Connector/J

classpath 也就是 src/main/resources 目录

将下载的 .jar 包放到项目 classpath 下

在IDE中写一个类连接到服务器

class DatabaseCreator {
    private static final String URL = "jdbc:mysql://localhost:3306/?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "Codingmore123";
    private static final String DATABASE_NAME = "pai_coding";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            if (!databaseExists(conn, DATABASE_NAME)) {
                stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME);
                System.out.println("数据库创建成功");
            } else {
                System.out.println("数据库已经存在");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static boolean databaseExists(Connection conn, String dbName) throws SQLException {
        ResultSet resultSet = conn.getMetaData().getCatalogs();

        while (resultSet.next()) {
            if (dbName.equals(resultSet.getString(1))) {
                return true;
            }
        }

        return false;
    }
}

DriverManager.getConnection(URL, USER, PASSWORD)：通过 JDBC 建立到 MySQL 服务器的连接。

初学者不需要完全明白这些代码

3 - MySQL数据库查询

在日常的开发工作中，查询语句也是最常用的，因为表在一开始设计的时候已经确定了，后期很少去修改表结构，也就意味着插入数据的方式也是确定的

但数据的展示方式却千奇百怪，用户端和 admin 管理端可能需要各种各样的数据，那 MySQL 就要提供最接近需求的数据，这样可以节省 Java 程序对数据的转换处理，也就相应提升了程序的性能。

SELECT 语句可以灵活组织不同表的数据, 提供最接近需求的组合

提示: 接下来的操作默认是单库操作 也就是说需要先选中某个数据库, 快去选择吧

查询单个字段

记录 我们把表中的每一行叫做一个“记录”，每一个记录包含这行中的所有信息，就像在通讯录数据库中某个人全部的信息，但记录在数据库中并没有专门的记录名，常常用它所在的行数表示这是第几个记录。

字段 字段是比记录更小的单位，字段集合组成记录，每个字段描述文献的某一特征，即数据项，并有唯一的供计算机识别的字段标识符。

SELECT 字段名 FROM 表名;

别名

查出来的字段名字太长, 先更换名字 可以使用 AS 关键字，格式如下所示

SELECT 字段名 AS 别名 FROM 表名;

这通常会在多表进行联合查询或者 Java 程序端和 MySQL 表字段不一致时使用。

比如说，Java 程序端的字段名是 articleTitle，那我们就可以使用别名来解决这个问题。

SELECT title AS articleTitle FROM article;

查询多个字段

有时候我们需要同时拿到多个字段

SELECT 字段1, 字段2, 字段3 FROM 表名;

查询所有字段

了解 通配符

SELECT * FROM 表名;

查询结果去重

有可能多个记录会有相同的字段值, 这很正常 懒得一个个分辨可以去重 使用 DISTINCT 关键字进行查询

SELECT DISTINCT 字段名 FROM 表名;

查询结果排序

当结果集中需要排序时，可以使用 ORDER BY 关键字进行查询，格式如下所示：

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC|DESC];

查询结果限制条数

我们只想要其中的1条或多条, 从哪里开始找, 查找几行结束

SELECT 字段名 FROM 表名 LIMIT 开始行,行数;

开始行也叫偏移量（OFFSET），默认是 0，可以缺省。

了解: MySQL条件查询

统计

返回记录数

SELECT COUNT(ProductID) FROM Products;

注意 此方法会忽略NULL记录!

COUNT(1)

COUNT(*)

count(1) 是计算所有记录数，1 被视作一个常数值，在每一行都存在，因此与 count(*) 基本相同。

mysql的数据引擎有哪些?

区别是什么?

4 - MySQL数据类型

表是由不同数据类型的列组成的，然后填充了一行一行的数据。当我们要创建表的时候，就要根据业务需求，选择合适的数据类型。

整数类型

其实还有个 2 字节的 smallint 但是不常用

设计表时

• 对于状态\类型字段使用 tinyint
• 记录id, 主键id等使用 bigint
• 其他用int

符号?

这里注意, MySQL是存在有/无符号整型之分的, Java默认有符号

当涉及到负数表示时, 为了兼容, Java中必须使用Long 型

因为Java的int范围和MySQL不同分别是-2147483648 到 2147483647, 和 0 到 4294967295(无符号)

所以在设计表时, 整数的符号也要考虑

像自增 ID，肯定是无符号的，所以我们会在定义的时候将其设置为 unsigned

显示宽度?

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `int10` int(10) NOT NULL,
  `int11` int(11) NOT NULL,
  `int` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

经常设计表的同学知道, int(10) 和 int(11) 指的是显示宽度的不同, 这里不涉及精度的问题, 多余的位用0填充

就是视觉规定而已

了解 ALEX 的博客写的很好

5 - MySQL条件查询

SELECT 查询没有查询条件。比如只返回姓名为xx的人, 年龄为xx的人等条件

要知道，真实的业务场景中，数量会非常大，有些甚至会有几百万、几千万条数据，如果不带上查询条件，一次性把全部数据查出来是不太现实的。

所以，我们通常要求在执行 SELECT 查询时，都要带上查询条件。那这一节，我们就来学习一些简单的 WHERE 条件查询。

查询操作符

示例: 查找id大于1000的文章

SELECT title, user_id, create_time FROM article WHERE user_id > 1000;

区间查询

利用一个 >= 和一个 <=，配合 AND 关键字，就可以实现区间查询

SELECT 字段名 FROM 表 WHERE user_id >= 1000 AND user_id <= 2000;

查询上下界id为1000 到 2000 的文章

其实就是规定上下界 所以可以用 BETWEEN 代替

SELECT title, user_id, create_time FROM article WHERE user_id BETWEEN 1000 AND 2000;

取反也可以, 加上 NOT 就行

SELECT title, user_id, create_time FROM article WHERE user_id NOT BETWEEN 1000 AND 2000;

感觉比Python简单

枚举查询

当区间为1, 其实就是枚举, 这就不需要指定上下界了 我们可以直接指定数值 用 IN

查询 user_id 是 1、2、3 的文章

SELECT title, user_id, create_time FROM article WHERE user_id IN (1, 2, 3); 

当然可以取反. 此略

逻辑操作符

除了数据操作符外还可以进行逻辑判断

其实上文的 AND 就是一个

AND 操作符用于组合多个查询条件，只有当所有的条件都满足时，才会返回结果

SELECT title, user_id, create_time FROM article WHERE user_id != 1 AND title = 'something';

OR与其类似

可以使用() 指定优先级

SELECT title, user_id, create_time FROM article WHERE user_id != 1 AND (title = '聊聊分库分表' OR short_title != '');

in

in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select * from user where user_id in (1, 2, 3);

exists

select * from user where exists (select * from user where user_id = 0);

这两个都是条件嵌套

6 - MySQL索引

索引介绍

我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

**索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

开始之前, 我们直观的体会下索引

如果要查询 mysql 单词, 我们就需要字符匹配, 首先是 m, y 之后是 s,q l 在接近上万条的记录中我们需要把整个表过一遍, 天哪 因此索引的目的就是为了缩小搜索范围来获取想要的数据结果 很容易想到将表分段, 通过确定某个分段的方式来获取数据

了解: 数据结构-树

索引的类型

主键索引:

数据表的主键列使用的就是主键索引。

一张数据表只能有一个主键, 并且不能重复, 不能为NULL

INNODB中, 引擎在没有显示指定主键的情况下, 会优先判断检查是否有唯一索引且没有NULL的字段, 如果有就选择该字段为默认的主键, 否则引擎自动创建一个自增主键

7 - MySQL表操作

有了数据库以后，我们就可以在数据库中对表进行增删改查了

MySQL是关系型数据库, 这个名词通俗来说就是把世界上的一切物体都实体化, 通过属性之间的关系管理数据

实体化的结果叫做表, 数据库中以表为组织单位存储数据

查表

首先指定数据库

查看当前数据库中的所有表，可以使用 

show tables

不首先指定数据库的话

show tables from 数据库名;

提示: 这是查看表名的操作, 跟表结构没关系

建表

create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);

了解: MySQL数据类型

这里可能会遇到问题

指示表已经存在了, 这时只能删掉以前的表 为了避免这种情况，我们可以在建表的时候，先判断表是否存在，如果不存在，再创建表，语法如下：

create table if not exists 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);

注释

是的, 表也有注释, 在上百张表的情况下, 注释就很重要了

create table 表名(
    列名1 数据类型1 comment '注释1',
    列名2 数据类型2 comment '注释2',
    ...
    列名n 数据类型n comment '注释n'
) comment '表注释';

查看表结构

desc 表名;
describe 表名;
explain 表名;
show columns from 表名;
show fields from 表名;

上述指令效果完全一样

改表

这需要一些数据库底层原理基础知识才能操作, 此处略过

了解 MySQL数据库简单查询 MySQL字符集和比较规则

8 - MySQL连接

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

连接分为三种 内, 左, 右

内连接

INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

• table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

左连接

LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

返回左表的所有行, 如果右表没有匹配就为NULL

右连接

不常使用, 因为可以用左连接交换表顺序达到相同效果

9 - 事务

引用: go语言大佬, go语言设计与原理作者 [面向信仰编程 - draveness](https://draveness.me/mysql-innodb/

在关系型数据库中，事务的重要性不言而喻，只要对数据库稍有了解的人都知道事务具有 ACID 四个基本属性，

而我们不知道的可能就是数据库是如何实现这四个属性的；在这篇文章中，我们将对事务的实现进行分析，尝试理解数据库是如何实现事务的，当然我们也会在文章中简单对 MySQL 中对 ACID 的实现进行简单的介绍。

原子性, 一致性, 隔离性, 持久性 ACID

原子性

事务就是一系列的操作，要么全部都执行，要都不执行，这其实就是对事务原子性的刻画

如果事务不具备原子性，那么就没办法保证同一个事务中的所有操作都被执行或者未被执行了，整个数据库系统就既不可用也不可信。

数据库的事务提交也是多线程并发的, 适用并发编程

持久性

数据被写入到数据库中，那么数据一定能够被安全存储在磁盘上, 这就是持久性

可能会奇怪, 除非硬盘挂掉, 为什么会存在持久性问题呢

其实持久性并非问题, 而是一种原则

一旦事务被提交，那么数据一定会被写入到数据库中并持久存储起来。 也就是当数据被写到硬盘后, 不允许撤销!

当事务已经被提交之后，就无法再次回滚了，唯一能够撤回已经提交的事务的方式就是创建一个相反的事务对原操作进行『补偿』，这也是事务持久性的体现之一。

隔离性

数据库的事务之间没有隔离性, 就会发生级联回滚的问题, 造成性能上的巨大损失

如果所有的事务的执行顺序都是线性的，那么对于事务的管理容易得多，但是允许事务的并行执行却能能够提升吞吐量和资源利用率，并且可以减少每个事务的等待时间。

一致性

分为两种一致性

ACID 和 CAP

其中ACID定义为, 如果事务原子性的一个一致的数据库中独立运行, 那么执行之后数据库的状态是一定的, 在事务的执行的前后以及过程中不会违背对数据完整性的约束, 所有对数据库写入的操作都应该是合法的，并不能产生不合法的数据状态。

CAP则是通常的一致性, 其实是说分布式系统中的各个节点中对于同一数据的拷贝有着相同的值, 也就是存储可见性

10 - 数据库设计规范

数据库的三大范式，它是数据库设计中最基本的三个规范，那么，三大范式是什么？在实际开发中，我们一定要严格遵守三大范式吗？

第一范式

确保每列原子性 也就是字段的值是原子的, 不可再分割

第二范式

确保表中的每列和主键相关

第二范式在第一范式的基础上, 消除表的部分依赖

即非主键字段必须完全依赖于主键，而不是仅依赖于主键的一部分。

这里和订单id(主键) 有关的只有数量和商品ID

拆成两张表

第三范式

确保每列都与主键列直接相关, 而不是间接相关

在第二范式的基础上, 消除表的传递依赖

所有非主键字段必须直接依赖于主键，而不是通过其他非主键字段间接依赖。

在这个表中，部门名称依赖于部门ID，而部门ID依赖于主键员工ID，形成了传递依赖，违反了3NF。