数据库1-2章

第一章数据库理论

什么是数据库

数据：

描述事物的符号记录，可以是数字、文字、图形、图像、声音、语言等，数据有多种形式，它们都可以经过数字化后存入计算机。

数据库：

==存储数据的仓库==，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：

数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高
数据由 DBMS 统一管理和控制（安全性、完整性、并发控制、故障恢复）

数据库管理系统(DBMS)

概念

数据库管理系统(DataBase ManagermentSystem，简称DBMS)是管理数据库的一个软件，它充当所有数据的知识库，并对它的存储、安全、一致性、并发操作、恢复和访问负责。是对数据库的一种完整和统一的管理和控制机制。
数据库管理系统不仅让我们能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。数据库管理系统的核心是一个用来存储大量数据的数据库
DBMS是所有数据的知识库，并对数据的存储、安全、一致性、并发操作、恢复和访问负责。
DBMS有一个数据字典（有时被称为系统表），用于贮存它拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据（metadata）

DBMS支持的数据模型

层次模型：若用图来表示，层次模型是一棵倒立的树。在数据库中，满足一下条件的数据模型称为层次模型：
- 有且仅有一个节点无父节点，这个节点称为根节点
- 其他节点有且仅有一个父节点。桌面型的关系模型数据库

网状模型：在现实世界中，事物之间的联系更多的是非层次关系的，用层次模型表示非树型结构是很不直接的，网状模型则可以克服这一弊病。网状模型是一个网络。在数据库中，满足以下两个条件的数据模型称为网状模型。A.允许一个以上的节点无父节点；B.一个节点可以有多于一个的父节点，从以上定义看出，网状模型构成了比层次结构复杂的网状结构，适宜表示多对多的联系。

关系模型：以二维表的形式表示实体和实体之间联系的数据模型称为关系数据模型。从模型的三要素角度看，关系模型的内容为：
- 数据结构：一张二维表格。
- 数据操作：数据表的定义、检索、维护、计算等。
- 数据约束条件：表中列的取值范围即域值的限制条件。

常见数据库

关系型数据库

关系数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。简单说，关系型数据库是由多张能互相联接的二维行列表格组成的数据库。
关系模型就是指二维表格模型，因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、MicrosoftAccess、MySQL、PostgreSQL、浪潮K-DB 、武汉达梦、南大通用、人大金仓、华为高斯（mysql核心的二次研发）、mariadb（mysql作者在mysql收购后研发的）等。
实体关系模型简称 E-R 模型，是一套数据库的设计工具，他运用真实世界中事物与关系的观念，来解释数据库中的抽象的数据架构。实体关系模型利用图形的方式（实体-关系图）来表示数据库的概念设计，有助于设计过程中的构思及沟通讨论。

非关系型数据库

非关系型数据库：又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL，是一种轻量、开源、不兼容 SQL 功能的数据库，对 NoSQL 最普遍的定义是“非关联型的”，强调 Key-Value 存储和文档数据库的优点，而不是单纯地反对 RDBMS（关系型数据库管理系统）
典型数据库：redis、mongodb

MySQL

简介

MySQL是一个小型关系数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被sun公司10亿美金收购。2009年，SUN又被Oracle以74亿美金收购。
目前MySQL被广泛地应用在Internet上的中小型网站中。由于体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

MySQL的特性

使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性。
支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、NetBSD、OpenBSD、OS/2 Wrap、Solaris、SunOS、Windows等多种操作系统。
为多种编程语言提供了API。这些编程语言包括C、C++、C#、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。
支持多线程，充分利用CPU资源，支持多用户,优化的SQL查询算法，有效地提高查询速度。
既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。
提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。
提供TCP/IP、ODBC和JDBC等多种数据库连接途径。
提供用于管理、检查、优化数据库操作的管理工具。
可以处理拥有上千万条记录的大型数据库。

MySQL架构

组成

共分为四层：网络连接层、数据库服务层、存储引擎层、系统文件层

网络连接层

综述：==MySQL是一个单进程多线程架构的数据库==，通过数据库连接池处理所有客户端接入的工作。
如何连接：
- 当输入命令mysql -h 127.0.0.1 -uroot -p123456 后MySQL服务端与客户端会基于TCP/IP协议栈连接建，这之间会检查用户名、密码、权限等
- 数据库连接建立成功后，MySQL服务端与客户端之间会采用半双工的通讯机制工作
- 同时MySQL会“安排”一条线程维护当前客户端的连接，这条线程也会时刻标识着当前连接在干什么工作，可以通过show processlist;命令查询所有正在运行的线程：

数据库连接池(Connection Pool)
- 产生原因：所有的客户端连接都需要一条线程去维护，而线程资源无论在哪里都属于宝贵资源，因此不可能无限量创建，所以这里的连接池就相当于Tomcat中的线程池，主要是为了复用线程、管理线程以及限制最大连接数的。
- 连接池的最大线程数可以通过参数max-connections来控制，如果到来的客户端连接超出该值时，新到来的连接都会被拒绝，关于最大连接数的一些命令主要有两条：
  - show variables like '%max_connections%';：查询目前DB的最大连接数。
  - set GLOBAL max_connections = 200;：修改数据库的最大连接数为指定值。
- 对于不同的机器配置，可以适当的调整连接池的最大连接数大小，以此可以在一定程度上提升数据库的性能。除了可以查询最大连接数外，MySQL本身还会对客户端的连接数进行统计，对于这点可以通过命令show status like "Threads%";查询

数据库服务层

MySQL大多数核心功能都位于这一层，包括客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数（例如：日期、时间、统计、加密函数...），所有跨引擎的功能都在这一层实现，譬如存储过程、触发器和视图等一系列服务。

SQL接口组件
- 作用：接收客户端的SQL命令，比如DML、DDL语句以及存储过程、触发器等，当收到SQL语句时，SQL接口会将其分发给其他组件，然后等待接收执行结果的返回，最后会将其返回给客户端
- 简单来说，也就是SQL接口会作为客户端连接传递SQL语句时的入口，并且作为数据库返回数据时的出口。
- SQL语句分为五大类：
  - DML：数据库操作语句，比如update、delete、insert等都属于这个分类。
  - DDL：数据库定义语句，比如create、alter、drop等都属于这个分类。
  - DQL：数据库查询语句，比如最常见的select就属于这个分类。
  - DCL：数据库控制语句，比如grant、revoke控制权限的语句都属于这个分类。
  - TCL：事务控制语句，例如commit、rollback、setpoint等语句属于这个分类。
解析器：主要作用是词法分析、语义分析、语法树生成等等，即验证SQL语句是否正确，以及将SQL语句解析成MySQL能看懂的机器码指令
优化器：作用是生成执行计划，比如选择最合适的索引，选择最合适的join方式等，最终会选择出一套最优的执行计划，维护当前连接的线程会负责根据计划去执行SQL
缓存和缓冲区
- 读取缓存
  - 一般指select语句的数据缓存，当然也会包含一些权限缓存、引擎缓存等信息，但主要还是select语句的数据缓存
  - 作用：MySQL会对于一些经常执行的查询SQL语句，将其结果保存在Cache中，因为这些SQL经常执行，因此如果下次再出现相同的SQL时，能从内存缓存中直接命中数据，自然会比走磁盘效率更高
  - 注意：高版本的MySQL中，移除了查询缓存区，毕竟命中率不高，而且查询缓存这一步还要带来额外开销，同时一般程序都会使用Redis做一次缓存，因此结合多方面的原因就移除了查询缓存的设计。
- 写入缓冲区
  - 缓冲区的设计主要是：为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。
  - 数据库进行写操作时，都会先从缓冲区中查询是否有要操作的页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后MySQL会在后台利用一种名为Checkpoint的机制，将内存中更新的数据刷写到磁盘
  - MySQL在设计时，通过缓冲区能减少大量的磁盘IO，从而进一步提高数据库整体性能

存储引擎层

存储引擎也可以理解成MySQL最重要的一层，在前面的服务层中，聚集了MySQL所有的核心逻辑操作，而引擎层则负责具体的数据操作以及执行工作
Oracle、SQLServer等商用数据库只有一个存储引擎，因为它们是闭源的，所以仅有官方自己提供的一种引擎。而MySQL则因为其开源特性，所以存在很多很多款不同的存储引擎实现，MySQL为了能够正常搭载不同的存储引擎运行，因此引擎层是被设计成可拔插式的，也就是可以根据业务特性，为自己的数据库选择不同的存储引擎。
MySQL目前有非常多的存储引擎可选择，其中最为常用的则是InnoDB与MyISAM引擎，可以通过show variables like '%storage_engine%';命令来查看当前所使用的引擎。其他引擎如下：

存储引擎是MySQL数据库中与磁盘文件打交道的子系统，不同的引擎底层访问文件的机制也存在些许细微差异，引擎也不仅仅只负责数据的管理，也会负责库表管理、索引管理等，MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成

文件系统层

这一层主要分为两个板块：日志板块和数据板块。该层是MySQL数据库的基础，本质上就是基于机器物理磁盘的一个文件系统，其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL运行时所需的文件，这一层的功能比较简单，也就是与上层的存储引擎做交互，负责数据的最终存储与持久化工作。
日志板块：使用七种常用的日志类型
- ①binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
- ②redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
- ③undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
- ④error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
- ⑤general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
- ⑥slow-log：慢查询日志，主要记录执行时间较长的SQL。
- ⑦relay-log：中继日志，主要用于主从复制做数据拷贝。
数据板块：MySQL的所有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同，常见的数据文件类型如下：
- db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。
- .frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。
- .MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。
- .MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。
- .ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。
- .ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。
- .ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。
- .ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。
- .cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

第二章 MySQL 安装部署

Windows安装MySql8.0 mis格式

步骤1：双击下载的mysql-installer-community-8.0.26.0.msi文件，打开安装向导。

步骤2：打开“Choosing a Setup Type”（选择安装类型）窗口，在其中列出了5种安装类型，分别是 Developer Default（默认安装类型）、Server only（仅作为服务器）、Client only（仅作为客户端）、 Full（完全安装）、Custom（自定义安装）。这里选择“Custom（自定义安装）”类型按钮，单击“Next(下一步)”按钮。

步骤3：打开“Select Products” （选择产品）窗口，可以定制需要安装的产品清单。例如，选择“MySQL Server 8.0.26-X64”后，单击“→”添加按钮，即可选择安装MySQL服务器，如图所示。采用通用的方法，可以添加其他你需要安装的产品。

此时如果直接“Next”（下一步），则产品的安装路径是默认的。如果想要自定义安装目录，则可以选中对应的产品，然后在下面会出现“Advanced Options”（高级选项）的超链接。

ProgramData目录（这是一个隐藏目录），如果没显示，需要到此电脑中的查看中勾选隐藏项目项。如果自定义安装目录，请避免“中文”目录。另外，建议服务目录和数据目录分开存放。

步骤4：在上一步选择好要安装的产品之后，单击“Next”（下一步）进入确认窗口，如图所示。单击 “Execute”（执行）按钮开始安装。

步骤5：安装完成后在“Status”（状态）列表下将显示“Complete”（安装完成），如图所示。

4.配置MySQL8.0

MySQL安装之后，需要对服务器进行配置。具体的配置步骤如下。

步骤1：在上一个小节的最后一步，单击“Next”（下一步）按钮，就可以进入产品配置窗口。

骤3：单击“Next”（下一步）按钮，打开设置授权方式窗口。其中，上面的选项是MySQL8.0提供的新的授权方式，采用SHA256基础的密码加密方法；下面的选项是传统授权方法（保留5.x版本兼容性）。

步骤4：单击“Next”（下一步）按钮，打开设置服务器root超级管理员的密码窗口，如图所示，需要输入两次同样的登录密码。也可以通过“Add User”添加其他用户，添加其他用户时，需要指定用户名、允许该用户名在哪台/哪些主机上登录，还可以指定用户角色等。此处暂不添加用户。

步骤5：单击“Next”（下一步）按钮，打开设置服务器名称窗口，如图所示。该服务名会出现在Windows服务列表中，也可以在命令行窗口中使用该服务名进行启动和停止服务。本书将服务名设置为 “MySQL80”。如果希望开机自启动服务，也可以勾选“Start the MySQL Server at System Startup”选项（推荐）。下面是选择以什么方式运行服务？可以选择“Standard System Account”(标准系统用户)或者“Custom User” (自定义用户)中的一个。这里推荐前者。

mis格式安装

mysql服务启动与停止
- 启动：net start mysql80
- 停止：net stop mysql80

Windows安装mysql8.0 zip格式

卸载mis格式

注1：不能直接删除msyql安装目录，需要卸载完毕后检查是否有安装目录
注2：删除文件夹C:\ProgramData\MySQL（若存在）和C:\ProgramData\MySQL（若存在）
注3：若重新安装有报错，可以按照下面的方式检查是否有未删除的内容

下载zip格式

地址：https://dev.mysql.com/downloads/mysql/ ，选择标准包

安装过程

解压缩：注意，路径中不能有中文，如：D:\mysql-8.0.37-winx64

配置环境变量：注意，之前的环境变量删除，或者直接修改

创建配置文件：D:\mysql-8.0.37-winx64\my.ini

 [client]
 # 设置mysql客户端连接服务端时默认使用的端口
 port=3306
 default-character-set=utf8mb4
 
 [mysql]
 # 设置mysql客户端默认字符集
 default-character-set=utf8mb4
 
 [mysqld]  # 服务端设置
 # 设置3306端口
 port=3306
 # 重要，设置mysql的安装目录
 basedir=D:\mysql-8.0.37-winx64
 # 重要，设置mysql数据库的数据的存放目录
 datadir=D:\mysql-8.0.37-winx64\data
 # 允许最大连接数
 max_connections=200
 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
 max_connect_errors=10
 # 服务端使用的字符集默认为UTF8
 character-set-server=utf8mb4
 # 创建新表时将使用的默认存储引擎
 default-storage-engine=INNODB