MySQL 学习笔记（一）

9

数据库说简单很简单：用来持久化数据的东西、通过 SQL 语言来查询、表主键外键这些概念。但是面试时候，数据库相关的问题如果不专门进行学习是根本不太清楚的：索引、SQL 的执行过程等。我对于这一块的知识一直是模糊的，在工作中的简单实用让我根本没有机会来深入使用到数据库的关键机制。

这个专题，用于记录自己系统性学习 MySQL 的学习笔记，补足作为后端工程师非常关键的这一个短板。

数据库的基本了解

在深入具体的数据库知识之前，先对 MySQL（所代表的数据库）有一些概念上的认识：

  • 概念上，数据库是存储数据的那个仓库，MySQL 实际上是数据库管理系统（DBMS），它是管理数据库的软件，而我们用 SQL 语言才操作 MySQL 这个管理系统，进而操作数据库。无论面对的是什么数据库，我们都可以用 SQL 这个统一的标准语言。

  • 如果想要在 Mac 上安装 MySQL的话，可以参考这个安装教程视频。

    • 安装了 MySQL 之后，我们的电脑就可以被看作是一个 MySQL 数据库服务器了（DBMS + 数据库 -> 内部的数据表）

    • 而我们可以使用一些客户端来和这个服务器进行交互：比如命令行客户端，或者如 DataGrip 这样的客户端工具

  • MySQL 是关系型数据库，关系型数据库是由多张相互连接的二维表组成的，数据是通过表存储在关系型数据库里的

    • MySQL 可以创建多个数据库（也叫 Schema），每个数据库可以创建多个表

SQL 语句基本概念及分类

了解了这些，接下来就是学习 SQL 的使用了，毕竟这是我们和数据库管理系统交互的语言。

先记录一下 SQL 语法的一些基本特点：

  • 一条 SQL 语句可以写成单行，也可以写成多行，通常我们用缩进来提升可读性。

  • SQL 语句以分号结尾，这一点在命令行中运行的时候能够体现出来，不加分号语句没有结束。

  • SQL 语句对大小写不敏感，关键字建议大写，但是实际上为了方便输入，

  • 我们可以用小写字母来书写。SQL 中的单行注释使用 -- ，MySQL 特有的语法是 # ，多行注释使用 /* */ 来书写。

SQL 语句也是由分类的，通常可以分为 4 大类：

  • DDL 数据定义语言：用来定义数据库、表、字段、索引等

  • DML 数据操作语言：对表中的数据进行增删改

  • DQL 数据查询语言：它对应增删改查中最重要的查询

  • DCL 数据控制语言：用来创建数据库用户，以及控制访问数据库的权限

接下来分别学习每种语言

SQL 之 DDL 语句

查询/使用数据库

先从定义语言开始，在查询之间我们先要学会如何创建出数据库、表。

  • 查询所有数据库 show databases;

  • 使用某一个数据库 use <database>;

  • 查询当前使用的数据库是什么 select database(); 这里使用了一个函数

创建/删除数据库

上面是三个简单的命令，下面这个命令——如何创建数据库——有很多需要注意的点，先看语法

s

  • 语法中 [] 的部分表示可选，因此最简单的创建数据库的语句是create database <db name>

    • 也可以使用 create schema <db name>，schema 就是数据库的概念

  • 但是如果重复创建已经存在的数据库会报错，因此加上 if not exits 可以避免报错，已经存在就不创建

  • 关于字符集，MySQL 默认的字符集是 utf8m4 它是使用 4 个字节存储每个字符的一种字符集，能够更好地支持更多的字符

    • 我们可以通过 show variables like 'character_set_server'; 来查询字符集设置

  • collate 是和排序规则有关，也有默认值，这里先忽略

而删除数据库可以使用 drop database [if exists] <db name> 的语法，同样的，加上 if exists 可以避免报错。

创建表

创建好了数据库接下来就是创建表，语法如下：

create table tb_user(
  id int comment '编号',
  name varchar(50) comment '姓名',
  age int comment '年龄',
  gender varchar(1) comment '性别'
) comment '用户表';

  • 表名后面是 ( ) 括号包围住的定义

  • 每一行定义是 字段名 类型 [可选的注释]

  • 表后面也可以接可选的注释

  • 每一行都要有逗号，除了最后一行，这里容易犯错

查询表

创建好数据表之后，我们可以通过 show tables 来查询当前数据库中所有的表。如果想要查看表是怎么定义的，可以使用 desc <table name> 或者直接查看建表语句 show create table <table name> ，后者信息更全一些。

MySQL 中的数据类型

建表中最关键的一部分就是每个字段的数据类型，MySQL 中有三类数据类型，以及一些相关要点：

  • 数值类型

    • 对于大部分类型，根据字节大小，都有在 Java 中的类似对应

    • 我们在类型后面可以指定 UNSIGNED 指定使用无符号类型，即只有正数，比如表示年龄的时候我们就可以使用 age TINYINT UNSIGNED

    • FLOAT 和 DOUBLE 都有精度误差，而 DECIMAL 用于存储精确的顶点数

      • 三个小数类型都可以使用 M（精度）来指定总位数，以及 D（标度）来指定小数点后的位数。例如要存储 100.1 就可以指定 DOUBLE(4, 1)

  • 字符串类型

    • 带 blob 的都是用来存储二进制数据的（虽然开发中可能会用文件服务器来存储）

    • char 是定长字符串，比如 char(10) 就总是会占用 10 个字符的长度，无论实际内容多长

    • varchar 是变长字符串，会根据实际内容来决定存储长度，如果超出长度会报错

      • 由于需要根据内容计算，性能稍差

    • 例子

      • 存储用户名（最长不超过 50 个字符varchar(50)

      • 性别，如果只有男/女的话，使用 char(1) 就够了

  • 日期时间类型

    • 需要注意的是 TIMESTAMP 这种类型，最大值只到 2038 年（由于只使用了 4 个字节，32 位整数）

    • 另外 DATETIME 是不包含时区信息的，需要自己手动转换

    • 如果我们表示生日的话，就可以使用 DATE 类型

操作表的相关操作

  • 添加字段 alter table <name> add <field> <type> [comment] [constraint]

  • 只修改数据类型 alter table <name> modify column <column name> <new type>

  • 修改字段名和类型 alter table <name> change <old> <new> <type> [comment] [constraint]

  • 删除字段名 alter table <name> drop <field name>

    • 删除用 drop

  • 修改表名 alter table <name> rename to <new name>

  • 删除表

    • 可以使用 drop table [if exists] <name> 会把表和数据都删除

    • 如果使用 truncate table <table> 就会删除表之后重建表，相当于只清空数据

总结来说，修改操作都是以 alter table <name> 起头，然后使用不同的操作关键字（add modify change drop rename to 等）。