数据库学习笔记（一）—— 初识MySQL

合集 - 数据库学习笔记(5)

1.数据库学习笔记（一）—— 初识MySQL2024-01-15

2.数据库学习笔记（三）—— MySQL 之 SELECT（查询）篇2024-01-223.数据库学习笔记（二）—— MySQL 之 存储引擎和索引篇2024-01-204.数据库学习笔记（四）—— MySQL 之 事务篇2024-01-225.数据库学习笔记（五）—— MySQL 之 瓶颈及优化篇2024-01-24

收起

初识MySQL

---

前言

　　关于 MySQL 的学习，具体内容非常庞大，但重点内容比较集中，因此关于 MySQL 的学习笔记可能和之前的不太一样。至于 GoWeb 的学习笔记，还远远不到结束的时候，学完数据库后，剩下的内容就在实战中学习。

介绍

什么是数据库？

 　　数据库是结构化信息或数据的有序集合，一般以电子形式存储在计算机系统中。通常由数据库管理系统 (DBMS) 来控制。在现实中，数据、DBMS 及关联应用一起被称为数据库系统，通常简称为数据库。

数据库与电子表格有何区别？

数据库和电子表格（例如 Microsoft Excel）都可以便捷地存储信息，两者的主要区别包括：

• 存储和操作数据的方式
• 谁可以访问数据
• 可以存储多少数据

　　从一开始，电子表格就是专门针对单一用户而设计的，其特性也反映了这一点。它非常适合不需要执行太多高度复杂的数据操作的单一用户或少数用户。相反，数据库是为了保存大量甚至海量有组织的信息而设计的，它允许多个用户同时使用高度复杂的逻辑和语言，快速、安全地访问和查询数据。

 

数据库分类

关系型数据库：SQL（Structured Query Language）

• MySQL、Oracle、Sql Server、DB2、SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储
• 通过外键关联来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

• Redis、MongoDB
• 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

 

数据库和数据库实例

在数据库的学习中，存在两个非常容易混淆的概念，即数据库和数据库实例。在MySQL中：

• 数据库：数据，即磁盘上的文件
• 数据库实例：数据+操作，在系统上的表现就是一个进程

　　在非集群数据库架构中，数据库与数据库实例存在一 一对应关系，在数据库集群中，可能存在多个数据库实例操作一个数据库情况，即多对一关系。（数据库集群是一种将多个数据库服务器连接在一起，以提高性能、可用性和可扩展性的技术）

 

MySQL架构

客户端/服务器架构

　　MySQL 是客户端/服务器架构，其服务器直接和数据交互，而客户端可以连接到这个服务器，发送增删改查的请求，然后服务器就响应这些请求，从而操作它维护的数据。

我们日常使用 MySQL 的情景一般是这样的：

1. 启动 MySQL 服务器程序（也叫 MySQL 数据库实例）。
2. 启动 MySQL 客户端程序并连接到服务器程序。
3. 在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

MySQL 的大部分安装包都包含了服务器程序和客户端程序，不过在Linux下使用RPM包时会有单独的服务器RPM包和客户端RPM包，需要分别安装。

 

客户端

　　在我们下载的 MySQL 的 bin 目录下，有个 mysql.exe，那就是客户端程序。我们平时使用 MySQL 输入的命令：

mysql -h主机名  -u用户名 -p密码
//-h 表示服务器的地址，不输入代表本机地址
//-u 表示用户名
//-p 表示密码，尽量不要跟在命令后输入，最好点击回车后再输入

　　其实就是启动这个客户端程序，并连接到服务器。

 

服务器端

　　在 Windows 系统上，服务器程序是以服务的方式运行：

 　　服务器端相比客户端更加复杂，大体可以分为两部分：Server 层和存储引擎层（二者的功能跟日常开发中的 Service 层和与 DAO 层的关系有点像，可以对比理解）：

Server 层

（1）MySQL向外提供的交互接口（Connectors）

　　Connectors组件，是 MySQL 向外提供的交互组件，如 java,.net,php 等语言可以通过该组件来操作SQL语句，实现与SQL的交互。

（2）管理服务组件和工具组件(Management Service & Utilities)

　　提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等

（3）连接池组件(Connection Pool)

　　负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

（4）SQL接口组件(SQL Interface)

　　接收用户 SQL 命令，如 DML,DDL 和存储过程等，并将最终结果返回给用户。

（5）查询分析器组件(Parser)

　　首先分析 SQL 命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。

（6）优化器组件（Optimizer）

　　对 SQL 命令按照标准流程进行优化分析。

（7）缓存主件（Caches & Buffers）

　　缓存和缓冲组件，MySQL 8.0版本后不再支持查询缓存。

 

存储引擎层

MySQL 服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。

　　MySQL 支持很多种存储引擎，例如MyISAM、InnoDB、BDB、Memory等，但最常用的是 InnoDB 和 MyISAM：

InnoDB存储引擎

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了，他的主要特点有：

1. 支持事务。支持4个事务隔离（ACID）级别，通过 MVCC（并发版本控制）来实现的；
2. 使用的锁粒度为行级锁，可以支持更高的并发，但更容易死锁，上锁开销更大；
3. 支持外键；
4. 在 InnoDB 中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
5. 对于 InnoDB 类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。

业务场景：

• 需要支持事务的场景（银行转账之类）
• 适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的
• 数据修改较频繁的业务

InnoDB引擎调优：

• 主键尽可能小，否则会给Secondary index带来负担
• 避免全表扫描，这会造成锁表
• 尽可能缓存所有的索引和数据，减少IO操作
• 避免主键更新，这会造成大量的数据移动

MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点为：

1. 不支持事务
2. 表级锁定（更新时锁定整个表）
3. 读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
4. 只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
5. 不支持外键
6. 读取速度快

业务场景

• 不需要支持事务的场景（像银行转账之类的不可行）
• 一般读数据的较多的业务
• 数据修改相对较少的业务
• 数据一致性要求不是很高的业务

MyISAM引擎调优

• 设置合适索引
• 启用延迟写入，尽量一次大批量写入，而非频繁写入
• 尽量顺序insert数据，让数据写入到尾部，减少阻塞
• 降低并发数，高并发使用排队机制
• MyISAM的count只有全表扫描比较高效，带有其它条件都需要进行实际数据访问

 

服务器处理客户端请求

MySQL基础操作

　　上面对数据库和 MySQL 的总体进行大概了解，现在开始接触 MySQL 的基础操作：

数据库操作

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [库选项];　　--[ ] 代表可选项，实际使用不带括号

• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

库选项：

• CHARACTER SET 字符集名：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
• COLLATE 校对规则名：指定字符集的默认校对规则

• 查看数据库

SHOW DATABASES;　　#查看所有数据库

SHOW CREATE DATABASE 数据库名称；　　/*查看指定数据库的创建信息*/

• 使用数据库

USE 数据库名称；

• 删除数据库

DROP DATABASE [IF EXISTS] 数据库名;　　--IF EXISTS 选项可以判断删除的数据库是否存在

字段类型

数据类型	描述	大小/格式
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数，一般用于金融计算
char	字符串固定大小	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8-1
text	文本串	2^16-1
date	日期格式	年-月-日
time	时间格式	时：分：秒
datetime	最常用的时间格式	年-月-日 时：分：秒
timestamp	时间戳，1970.1.1到现在的毫秒数
year	年份表示
null	没有值，未知

字段属性

字段属性	描述
UnSigned	无符号的 声明了该列不能为负数
ZEROFILL	0填充的 不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement	通常理解为自增，自动在上一条记录的基础上默认+1 通常用来设计唯一的主键，必须是整数类型 可定义起始值和步长 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表 SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL	默认为NULL , 即没有插入该列的数值 如果设置为NOT NULL , 则该列必须有值
DEFAULT	用于设置默认值

 

数据表操作

• 创建数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(
    字段名 字段类型 [字段属性] [索引] [注释],
     …
) [表选项]

• TEMPORARY： 表示临时表，仅在当前会话中可见，并且在会话关闭时自动删除。
• 表选项：用于设置表的相关特性，如存储引擎（ENGINE）、字符集（CHARSET）和校对集(COLLATE)。
  • 不设置字符集编码的话，会使用 MySQL默认的字符集编码Latin1 MySQL 8.0的默认字符集改为了 UTF8（MySQL里叫做 utf8mb4），可以在my.ini里修改

例子：

create table `goods` (　　#建议表名和字段名用``扩起来
    `id` int NOT NULL AUTO_INCREMENT comment '编号',　　#comment 用于在创建表时添加注释，并保存到表结构中
    `name` varchar(32) NOT NULL DEFAULT '匿名' comment '商品名',　　#varchar 表示可变长度的字符串，可变的字符数为32
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

• 查看数据表

查看数据库中的表：

SHOW TABLES [FROM 数据库名] [LIKE 匹配模式];

• 如果不使用 from，默认查看当前使用数据库的表。（如果当前没有使用数据库，会报错）
• 如果不使用 like，会显示数据库的所有表。

查看数据表的字段信息：

DESC 表名;

以下命令同样可以查看数据表的字段信息，使用 full 会更详细：

SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];    #格式一

SHOW [FULL] COLUMNS FROM 数据库名.数据表名;    #格式二

效果：

• 修改数据表

修改表名：

ALTER TABLE 旧表名 RENAME TO 新表名;

RENAME TABLE 旧表名1 TO 新表名1 [旧表名2 TO 新表名2] ...;

添加字段：

ALTER TABLE 表名 ADD 字段名 字段类型 [字段属性]

修改字段名：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型 [字段属性]

修改字段约束：

ALTER TABLE 表名 MODIFY 字段名 字段类型 [字段属性]

删除字段：

ALTER TABLE 表名 DROP 字段名

• 删除数据表

DROP [TEMPORARY] TABLE [IF EXISTS] 表名1 [,表名2] ...;

• TEMPORARY： 表示临时表，仅在当前会话中可见，并且在会话关闭时自动删除。但有时，在持久性连接中，可能需要我们手动删除临时表。

数据操作

• 添加数据 insert

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

例子：

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');

-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');

-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 

 

• 查询数据 select

简单查询：

SELECT (字段名1, 字段名2, 字段名3, ...) FROM 数据表名 WHERE 字段名 = 值;

 

• 修改数据 update

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

例子：

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;

-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';

-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

 

• 删除数据 delete

DELETE FROM 表名 [WHERE 条件]

例子：

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;

-- 删除指定数据
DELETE FROM `student` WHERE id=1;