MySQL

1. 基本概念

1.1 数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库, 每个数据库都有一个或多个不同的表, 用来存储数据.

按照 关系型数据库 的层次结构, 数据模型分为:

• 库: 最大的存储结构
• 表: 每类数据存储在一张表中, 一个库中含有多个表
• 列: 每个表中该类数据的不同特性
• 行: 表中数据按行存储, 一行为一条记录

很类似 Excel 的感觉, 对吧.

关系型 & 非关系型?

非关系型数据库 主要通过键值对 / 散列存储的方式进行存储, 这种数据库效率较高, 但能进行的操作也相对应的较为简单.

我们这里讲的MySQL属于关系型数据库, 数据之间有严格的相互关系, 这代表这我们数据库一旦建立, 就可以很轻松的进行各种操作, 但相对应的, 效率较低.

1.2 DBMS: 数据库管理系统

有了仓库, 自然就要有仓管, 我们所说的DBMS(DataBase Management System) 就起这个作用.

常见的DBMS有:

• MySQL
• Oracle
• DB2
• SQLite
• …

我们本章节采用MySQL数据库管理系统进行数据库的各种操作.

MySQL目前的稳定版本分 5.7 和 8.0 两个版本, 我们这里采用8.0版本.

详细版本号: 8.2.0

至于MySQL的安装, 这里不再赘述, 读者可自行上网搜索, 很多的: )

需要明确的是, MySQL并不像传统的可视化程序, 它是一个后台服务, 安装全过程完成后, 它会跑在你电脑后台的一个端口上(我这边默认为3306)

这个服务默认开机自启动, 但如果读者发现哪次没有启动, 这里给出两种开启方式:

• 通过呼出任务管理器, 在 服务 栏搜索MySQL, 找到对应的服务启动即可.
• 通过cmd(管理员权限)利用 net start / stop (对应服务名) 进行开启 / 关闭.

别忘了在Path中加MySQL的bin目录, 这样就可以在cmd中直接使用MySQL命令了.

1.3 SQL: 结构化查询语言

SQL(Structured Query Language) 是一种用于管理关系型数据库的编程语言, 它定义了操作数据库的各种语法, 通过SQL, 我们可以方便的进行各种数据库操作.

SQL是标准化的语言, 不同的关系型数据库都支持SQL, 但同时不同的数据库管理系统也会进行相应的扩展. 这点根据不同的管理系统而定.

本章中主要针对MySQL进行阐述, 因此主要针对MySQL的SQL语法进行讲解.

我们的SQL主要从如下方面进行:

• 数据定义: DDL
• 数据操作: DML
• 数据查询: DQL
• 数据控制: DCL
• 事务控制: TCL

1.4 SQL可视化编写工具

我们在进行数据库的操作时, 一般不会直接在命令行中编写SQL, 而是使用一些可视化工具, 如:

• Vscode引入相关插件
• Navicat
• MySQL Workbench
• SQLyog
• …

这些工具可以方便我们进行各种数据库操作, 但本质上, 它们都是通过SQL语句进行数据库操作的.

详细版本号: 13.1.7

2. 数据库操作

2.1 数据库的基本操作

2.1.1 数据库的登录

通过在命令行中输入:

mysql -u<username> -p<password> -h<hostname> -P<port> <databasename>

• -u: 用户名(默认为root)
• -p: 密码(在安装过程中自己设置的密码)
• -h: 主机名(如果本地运行, 默认为localhost, 默认情况下可省略)
• -P: 端口号(默认为3306, 默认情况下可省略)
• <databasename>: 数据库名, 可省略

2.1.2 查看数据库版本

select version();

2.1.3 数据库注释

-- 单行注释
/*
多行注释
*/

2.1.4 退出数据库

exit;

2.1.5 SQLyog可视化界面

我们通过SQLyog看到的界面如上图所示.

• 左侧: 库 / 表总览
• 左上方: 执行选项
• 中上侧空白: 编写SQL语句
• 中下侧空白: 执行结果 / 表信息展示

2.2 DDL: 数据定义语言

MySQL中, 一个完整的数据创建过程分四步: 创建库 -> 定字段 -> 创建表 -> 插数据

DDL(Data Defination Language) 是数据定义语言, 主要用于定义数据库, 表, 索引, 视图等, 负责定义 / 管理数据库的结构, 本身并不涉及对数据的操作.

DDL中重要的关键字分三个:

• CREATE: 创建
• ALTER: 修改
• DROP: 删除

2.2.1 MySQL命名规范

关于标识符:

• 数据库名 / 表名不得超过30个字符, 变量名不得超过29个字符
• 必须只能包含 A-Z, a-z, 0-9, _ 共63个字符, 不能以数字开头
• 中间不能包含空格
• 同一个MySQL软件中, 数据库不可同名; 同一个库中, 表不能同名; 同一个表中, 字段不能同名
• 不能与常用方法冲突, 如果坚持使用, 需要用 ` 进行引用

除此之外, 有一些推荐使用的规范:

• 库 / 表 / 列名尽可能使用小写字母, 单词之间利用 _ 进行连接
• 数据库名与应用名称一致: 一个应用通常而言只需要一个数据库, 因此我们通常将应用名称作为数据库名
• 表命名最好遵循 业务名称_表 的形式, 如: trade_config, pay_task
• 列名最好遵循 表属性_实体 , 如: user_name, order_id

2.2.2 库的管理

2.2.2.1 创建数据库

库的创建有一种推荐方式以及多个可选项:

CREATE DATABASE [IF NOT EXISTS] <databasename> [CHARACTER SET <charset> COLLATE <collate>];

这里对可选参数进行解释:

• IF NOT EXISTS: 如果库不存在, 则创建( 推荐无论如何都加上这个参数 )
• CHARACTER SET: 指定字符集(默认utf8mb4)
• COLLATE: 指定排序规则(默认 utf8mb4_0900_ai_ci , 排序规则不区分大小写; 另一种方式是 utf8mb4_0900_as_cs , 这种方式会识别大小写的区别)

默认字符集与排序规则可以通过命令进行查看:

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

2.2.2.2 查看 / 使用库

查看库有以下方式:

SHOW DATABASES; -- 查看当前所有数据库
SELECT DATABASE(); -- 查看当前使用的数据库
SHOW TABLES FROM <databasename>; -- 查看指定数据库中的所有表
SHOW CREATE DATABASE <databasename>; -- 查看指定数据库的创建信息
USE <databasename>; -- 使用指定数据库

明确, 第五条指令是使用数据库的先决条件.

2.2.2.3 修改 / 删除库

我们可以通过语句对库的字符集与排序方式进行修改.

ALTER DATABASE <databasename> CHARACTER SET <charset> COLLATE <collate>;

注意, 原生SQL是不支持 修改库名 这种操作的, 所有的重命名库(不论是否是原生支持的), 本质上都是 创建新库->转移数据->删除旧库 的一个操作.

删除库的指令如下:

DROP DATABASE [IF EXISTS] <databasename>;

同样的, IF EXISTS 是推荐使用的参数.

删库之前, 务必谨慎谨慎再谨慎!

2.2.3 表的管理

2.2.3.1 表的创建

创建表比创建库复杂许多, 因为创建过程中不仅仅需要指定表名, 还需要进行列的定义:

CREATE TABLE [IF NOT EXISTS] <tablename>{
    <columnname> <datatype> [Optional constraints for column] [COMMENT <comment>],
    <columnname> <datatype> [Optional constraints for column] [COMMENT <comment>],
    ...
    [Optional constraints for column];
}[Optional constraints for table] [COMMENT <comment>]

我们进行一下说明:

• <tablename>: 表名
• <columnname>: 列名
• <datatype>: 列的数据类型
• Optional constraints for column: 列的约束条件
• Optional constraints for table: 表的约束条件
• COMMENT: 注释

其中利用中括弧 [] 括起来的是可选参数.

注释是很有必要的, 你也不希望第二天看不懂自己建了个啥玩意吧

2.2.3.2 列的数据类型

MySQL中, 列的数据类型颇多, 这里对常用类型进行枚举:

• 整型: 后面都可以加Unsigned修饰符变为无符号位整数(如INT UNSIGNED)
  • TINYINT: 1字节 二进制范围: -128127 / 0255
  • SMALLINT: 2字节 范围: -3276832767 / 065535
  • MEDIUMINT: 3字节 范围: -83886088388607 / 016777215
  • INT: 4字节 范围: -21474836482147483647 / 04294967295
  • BIGINT: 8字节 范围: -92233720368547758089223372036854775807 / 018446744073709551615

在标准SQL中, 仅支持INT以及SMALLINT, 其他类型都是MySQL特有的

• 浮点型:
  • FLOAT(M, D): 单精度浮点型, 固定占用4字节, M为总长度(最高24位), D为小数位数(最高8位)
  • DOUBLE(M, D): 双精度浮点型, 固定占用8字节, M为总长度(最高53位), D为小数位数(最高30位)
  • DECIMAL(M, D): 精确小数型, 大小动态计算, M为总长度(最高65位), D为小数位数(最高30位)

由于float和double的精度问题, 因此在需要精确计算的场景下, 一律推荐使用decimal

• 字符串类型:
  • CHAR(maxlength): 通常用于存储较短的字符串, 是固定长度字符串(不论存储多少字符, 都会占用4*maxlength个字节(在utf8mb4编码下计算) ), $ 0<maxlength<255 $
  • VARCHAR(maxlength): 通常用于存储较长的字符串, 是可变长度字符串(占据内存大小根据存储字符数变化), $ 0<maxlength<65535 $

CHAR在声明时, 可以不写maxlength, 系统默认设定为1, VARCHAR在声明时, 必须写maxlength

我们写的最大值 65535 是数据库中一行数据的最大占有空间, 同时在MySQL中, 默认利用一字节来表示改行是否为空, 因此实际上可以用于存储字符的存储大小为 65534 个字节, 如果使用utf8mb4编码, 则为 16383 个字符

那问题来了, 如果我们就是要存非常长的数据到一个格里面, 怎么办?

MySQL提供了TEXT类型, 用于存储较长的字符串, 这种类型在存储时不受到一行最大存储量的限制.

• 长字符串(TEXT)类型:
  • TINYTEXT: 最大长度为255字节
  • TEXT: 最大长度为65535字节
  • MEDIUMTEXT: 最大长度为16777215字节
  • LONGTEXT: 最大长度为4294967295字节(最大能存4g的文本)

说归说, TEXT这玩意性能实在是堪忧, 这个它的各类操作非常的慢, 因此在涉及到超长文本的处理中, 一般而言可以直接存到本地的文本文件中, 并保存本地文件的相对地址, 以供将来调用

• 时间类型
  • YEAR: 年份, 1字节, 范围: 1901~2155
  • TIME: 时间, 3字节, 范围: -838:59:59~838:59:59 (HH:MM:SS)
  • DATE: 日期, 3字节, 范围: 1000-01-01~9999-12-31 (YYYY-MM-DD)
  • DATETIME: 日期时间, 8字节, 范围: 1000-01-01 00:00:00~9999-12-31 23:59:59 (YYYY-MM-DD HH:MM:SS)
  • TIMESTAMP: 时间戳, 4字节, 范围: 1970-01-01 00:00:01~2038-01-19 03:14:07 (YYYY-MM-DD HH:MM:SS)

年份的时间格式其实是可以利用两位时间格式的, [0069] 代表 [20002069], [7099] 代表 [19701999] , 但这种方式不是十分明确, 不推荐使用.

关于时间格式的自动更新, 可以通过: TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.2.3.3 表的修改

修改表主要分两种操作:

--修改表名
ALTER TABLE <oldtablename> RENAME <newtablename>;
--修改表的结构

--在原表中添加一列:
ALTER TABLE <tablename> ADD <columnname> <datatype> [FIRST/AFTER <columnname>];
--这之中, FIRST表示将新列添加到表的最前面, AFTER表示将新列添加到指定列的后面, 如加上<columnname>, 则表示将新列添加到<columnname>的 前面 / 后面

--修改表中某一列的名称
ALTER TABLE <tablename> CHANGE <oldcolumnname> <newcolumnname> <datatype> [FIRST/AFTER <columnname>];

--修改表中某一列的数据类型
ALTER TABLE <tablename> MODIFY <columnname> <datatype> [FIRST/AFTER <columnname>];

--删除表中某一列
ALTER TABLE <tablename> DROP <columnname>;

2.2.3.4 表的删除

-- 删除表
DROP TABLE [IF EXISTS] <tablename1>[, <tablename2>, ...];
-- 可以同时删除多个表

-- 清空表的数据
TRUNCATE TABLE <tablename>;

上述操作是无法回滚的, 请务必慎重操作

至此, 数据库中 DDL 的部分基本上理清楚了.

2.3 DML: 数据操纵语言

DML主要针对数据库中数据的 增 / 删 / 改 , 这三种操作不会影响数据库结构, 但会真正改变数据库中的数据.

DML主要涉及的关键字有:

• INSERT: 插入
• UPDATE: 更新
• DELETE: 删除

由于数据库中的基本单位是 一行 , 因此我们进行上述操作时, 都要建立在锁定行的基础上, 请读者明确这个思路.

2.3.1 插入数据

-- 插入一行, 并为一行中的所有字段添加数据
INSERT INTO <tablename> VALUES(value1, value2, ...);
-- 需要为表中每一个字段(即每一列)指定值, 值的顺序必须与字段定义时相同

-- 插入一行, 并为指定字段添加数据
INSERT INTO <tablename> (column1, column2, ...) VALUES(value1, value2, ...);

-- 插入多行数据
INSERT INTO <tablename> VALUES(value 1.1, value 1.2, ...), ..., (value n.1, value n.2, ...);
INSERT INTO <tablename> (column1, column2, ...) VALUES(value 1.1, value 1.2, ...), ..., (value n.1, value n.2, ...);
-- 每个括弧中为一行数据, 括弧之间利用逗号隔开

2.3.2 修改 / 更新数据

-- 修改表中所有行的数据（全表修改）
UPDATE <tablename> SET column1 = value1, column2 = value2, ...;

-- 修改表中指定行的数据（条件修改）
UPDATE <tablename> SET column1 = value1, column2 = value2, ... WHERE condition;

2.3.3 删除数据

-- 删除表中所有行数据（全表删除）
DELETE FROM <tablename>;

-- 删除表中指定行数据（条件删除）
DELETE FROM <tablename> WHERE condition;

从严重程度上来看, DELETE 与 TRUNCATE 的区别在于: DELETE 是逐行删除, 而TRUNCATE 是全表删除, 相对而言, DELETE 由于可以回滚, 更加安全一些.

2.4 DQL: 数据查询语言

DQL(Data Query Language), 即数据查询语言, 其主要用于查询数据库中指定内容. 不会影响库表结构, 也不会更改原数据, DQL 会根据原表数据查询出一个虚拟表 .

DQL中最重要的关键字即:

• SELECT

此外, 查询分 单表查询 以及 多表查询 , 单表查询只需要根据特定的真实表通过指定查询语法生成虚拟表即可. 而多表查询需要先利用合并语法将多张真实表合并, 再利用查询语法生成虚拟表.

本节中主要对单表查询进行阐述.

2.4.1 基础 SELECT 语句

2.4.1.1 非表查询

SELECT 1;
SELECT 9/2;
SELECT VERSION();
-- SELECT 后方可以跟任意表达式, 会根据表达式输出结果

2.4.1.2 指定表查询

-- 查询某个表中的特定列
SELECT column1, column2, ... FROM <tablename>;

-- 查询多个表中的指定列
SELECT table1.column1, table2.column2, ..., table n.* FROM <tablename>;
-- * 表示查询该表中的所有列

2.4.1.3 查询指定列, 并重命名

-- 查询指定列, 并将查询结果在虚拟表内重命名
SELECT column1 as alias1, column2 as alias2, ... FROM <tablename>;
-- as 可以省略, 即以下形式也行得通
SELECT column1 alias1, column2 alias2, ... FROM <tablename>;

这种做法看起来挺无厘头的, 事实上, 在后期我们将数据库中的内容映射至某些指定位置时, 这种虚拟表的列重命名是非常重要的一环.

2.4.1.4 去除查询结果中的重复行

-- 查询指定列, 并去除得到结果(虚拟表)中的重复数据
SELECT DISTINCT column1, column2, ... FROM <tablename>;

这种语法会将结果中 完全相同 的整行进行合并, 仅仅显示成1行数据.

2.4.1.5 查询常数值

SELECT 'value' as column1, column2, ... FROM <tablename>;

这种语法会将查询结果中的每一行都添加一个名为 column1 的列, 其值为 ‘value’ . 其本质上与 2.4.1.3 中的内容是一个道理.

3. 数据库中的一些常用函数

本部分不会十分的系统, 因为在学习过程中会额外接触到很多函数, 但又不好将其加到上文中, 因此此处单开一章, 权作记录.

判空, 并赋予默认值

IFNULL(culumn, value);
-- 如果column为空, 则返回value, 否则返回column本身的值

-- 例如:
SELECT IFNULL(salary, 0) 月薪 FROM employees;

常用于在某些被选择的表内存在大量NULL类型时, 进行展示的默认值设定.