mysql数据库

最新推荐文章于 2024-10-09 12:58:26 发布

2301_80613136

最新推荐文章于 2024-10-09 12:58:26 发布

阅读量3.3k

点赞数 18

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_80613136/article/details/141792394

版权

文章目录

一，数据库的基本概念
二，数据库的发展
三，主流的数据库介绍
四，MySQL安装方法
五，MySQL 数据库管理

一，数据库的基本概念

1.基本概念

1.1数据(Data)

描述事物的符号记录
包括数字,文字、图形、图像、声音、档案记录等
以“记录”形式按统一的格式进行存储

1.2表

将不同的记录组织在一起
用来存储具体数据

1.3数据库

表的集合,是存储数据的仓库
以一定的组织方式存储的相互有关的数据集合

2.数据库管理系统

数据库管理系统是用于有效组织、管理和存取数据库资源的系统软件。它在操作系统的支持下，提供用户对数据库进行各种操作的能力。
主要功能：

数据库的建立和维护功能:

包括建立数据库的结构和数据的录入与转换、数据库的转储与恢复、数据库的重组与性能监视等功能。

数据定义功能:

包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容,以防止不合语义的错误数据被输入或输出。

数据操纵功能:

包括数据查询统计和数据更新两个方面。

数据库的运行管理功能:

这是数据库管理系统的核心部分,包括并发控制、存取控,制、数据库内部维护等功能。

通信功能:

DBMS与其他软件系统之间的通信,如·Access（第一代数据库）能与其他·Office,组件进行数据交换

3.数据库系统 (Database System, DBS)

数据库系统是一个人机系统，包含了硬件、操作系统、数据库、数据库管理系统（DBMS）、应用软件和数据库用户（包括数据库管理员）等组成部分。用户可以通过 DBMS 或应用程序来操作数据库，从而完成数据管理和处理任务。

3.1数据库系统的组成

硬件 (Hardware):
- 定义: 运行数据库系统所需的物理设备，包括服务器、存储设备、网络设备等。
- 作用: 提供数据库系统的基础计算资源和存储空间。
操作系统 (Operating System):
- 定义: 管理硬件资源，并为数据库管理系统和应用软件提供支持的平台。
- 作用: 确保数据库系统在硬件之上正常运行，并提供必要的系统服务，如内存管理、进程调度、文件系统等。
数据库 (Database):
- 定义: 按一定组织方式存储的相互有关的数据集合，是数据库系统的核心部分。
- 作用: 用于存储和管理大量的数据，支持数据的高效查询、更新和维护。
数据库管理系统 (DBMS):
- 定义: 用于组织、管理和存取数据库资源的系统软件。
- 作用: 提供用户和应用程序与数据库交互的接口，管理数据的存储和访问。
应用软件 (Application Software):
- 定义: 为解决具体管理或数据处理任务而编写的程序，通常以友好的界面与用户交互。
- 作用: 简化数据库的使用，使得普通用户无需具备专业的计算机知识即可操作数据库。典型的例子包括 Navicat 等数据库管理工具。
- 例子: 人事管理、财务管理、图书管理等信息管理软件，以及各种信息咨询系统。
数据库用户 (Database Users):
- 定义: 使用数据库系统的人员，包括普通用户和数据库管理员（DBA）。
- 作用: 普通用户通过应用软件或直接使用 DBMS 进行数据操作；数据库管理员负责数据库的更新和备份、系统维护、用户管理等工作，确保数据库系统的正常运行。

数据库管理员 (Database Administrator, DBA)
DBA 是负责数据库系统正常运行的重要角色，通常由业务水平较高、经验丰富的人员担任。他们的主要职责包括数据库的更新和备份、数据库系统的维护、用户管理等，确保数据库的安全、可靠性和性能。数据库管理员（DBA）在数据库系统的管理和维护中扮演着至关重要的角色。他们的工作直接影响数据库的运行效率、安全性和稳定性。** **

3.2 DBMS的工作模式（数据流向）*

接受应用程序的数据请求和处理请求：

DBMS接收到来自应用程序的请求。这些请求通常通过API、SQL查询、存储过程调用等方式传递到数据库。
DBMS会解析和验证请求的合法性，包括检查语法、用户权限等。

将用户的数据请求（高级指令）转换为复杂机器代码（底层指令）：

DBMS将用户提交的高级指令（如SQL查询）转换为底层机器代码或具体的操作指令。这可能包括生成查询计划、优化查询等步骤，以确保指令能够高效地在数据库内部执行。

实现对数据库的操作：

转换后的底层指令由DBMS执行。这可能涉及检索、插入、更新或删除数据库中的数据。
在这一阶段，DBMS负责管理事务、维护数据的一致性和完整性、执行索引查找等。

从数据库的操作中接受查询结果：

执行完操作后，DBMS将生成的结果集返回给请求的会话。这些结果可以是数据行、执行状态、统计信息等。
DBMS还负责处理可能发生的错误或异常情况，并将其信息返回给用户。

对查询结果进行处理（格式转换）：

有时，DBMS会对结果进行进一步处理，例如格式化输出、将数据转换为特定的结构（如JSON或XML）等。
这种处理通常是根据应用程序的需求来进行的，以确保数据能够直接被应用程序所使用。

将处理结果返回给用户：

处理完毕后的数据通过API或SQL客户端返回给应用程序或最终用户。
同时，DBMS可能还会返回额外的元数据信息，如查询执行时间、受影响的行数等。

二，数据库的发展

1. 第一代数据库

1.1 出现时间:

20世纪60年代，第一代数据库系统诞生。

1.2 数据模型:

第一代数据库系统主要包括以下两种数据模型：
- 层次模型 (Hierarchical Model):
  - 数据以树状结构存储，父节点与子节点之间具有一对多的关系。
  - 适合表示层次关系明确的数据结构，但不适合处理复杂的多对多关系。
- 网状模型 (Network Model):
  - 数据以图结构存储，允许多对多关系。
  - 能够更加灵活地表示复杂的数据关系，但其复杂性也使得管理和查询更加困难。

1.3 作用:

第一代数据库系统为数据的统一管理和共享提供了支持，使得数据可以集中存储和访问，减少了数据冗余。
适合早期相对简单的数据管理需求，为后续数据库技术的发展奠定了基础。

1.4 当前地位:

虽然第一代数据库系统的模型在现代应用中不再主流，但它们为数据库技术的发展奠定了理论基础，尤其是在数据管理和结构化存储方面的贡献依然具有历史意义。

2. 第二代数据库

2.1 出现时间:

20世纪70年代初，第二代数据库系统开始出现。

2.2 数据模型:

第二代数据库系统主要采用以下数据模型：
- 关系模型 (Relational Model):
  - 由E.F. Codd在1970年提出，基于数学集合理论。
  - 数据以表格形式存储，表与表之间通过键值（如主键、外键）关联。
  - 关系模型提供了更直观且易于理解的结构，极大地简化了数据库的设计和管理。

2.3 作用:

第二代数据库系统简化了数据库设计、查询和管理，提高了开发和维护的效率。
关系模型逐渐取代了层次模型和网状模型，成为数据库行业的主流。

2.4 当前地位:

至今，关系数据库仍然占据着数据库应用的主导地位，广泛应用于各种业务系统中，如MySQL、Oracle、SQL Server等，继续在数据库领域中发挥重要作用。

3. 第三代数据库

3.1 出现时间:

20世纪80年代末到21世纪初，第三代数据库系统逐步问世。

3.2 数据模型:

第三代数据库系统包括多种不同类型的数据库模型：
- 面向对象数据库:
  - 采用面向对象的编程理念，数据以对象的形式存储，适合处理复杂的数据类型。
- 分布式数据库:
  - 通过网络将分布在多个物理位置的数据管理为一个整体，具有高可用性和容错性。
- NoSQL数据库:
  - 不依赖关系模型，支持多种数据模型（如键值对、文档、列族、图结构等），适合处理非结构化和半结构化数据。
- NewSQL数据库:
  - 结合了关系数据库的ACID一致性和NoSQL的高扩展性，提供对复杂事务和大规模并发处理的支持。

3.3 作用:

第三代数据库系统进一步增强了对复杂数据的处理能力，并且在大数据、高并发和分布式计算等方面提供了强有力的支持，适应了互联网时代的数据需求。

3.4 当前地位:

随着云计算和大数据技术的普及，第三代数据库系统在特定场景中得到了广泛应用，特别是在需要高扩展性、实时性和分布式处理的领域。

三，主流的数据库介绍

1.常见的数据库

SQL Server(微软的，面向Windows操作系统，简单、易用）
Oracle(甲骨文的，面向所有主流平台，安全、完善,操作复杂)
DB2(IBM的，面向所有主流平台，大型、安全、完善）
MySQL(被甲骨文公司收购了，免费、开源、体积小)

2.云端数据库

Amazon RDS（Relational Database Service）
阿里云 RDS
PostgreSQL
My MySQL 5.7 和 8.0
Oracle
Google BigQuery

3.时序数据库

InfluxDB
TimescaleDB
Prometheus
OpenTSDB
Druid
QuestDB
Graphite
VictoriaMetrics
ClickHouse (虽然主要是分析数据库，但常用于时序数据)
RRDTool
MariaDB

4.关系数据库

关系数据库系统是基于关系模型的数据库系统。
关系模型的数据结构使用简单易懂的二维数据表（即表格形式）。
关系模型可以用简单的“实体-关系”（E-R）图来表示。
E-R 图中包含了三要素：实体（即数据对象）、关系和属性。
E-R 图是一种常用的工具，用于在关系数据库设计中表示数据模型，通过图形化的方式帮助理解数据之间的关系。

例图：银行客户和银行账户之间e-r图

4.1关系数据库概念：实体、属性和联系，以及这些概念如何构成关系数据库。

1.实体：

实体是指可以在现实世界中识别的对象或事件。它们是数据库中存储的数据的主要对象。
例：银行客户、银行账户等。

2.属性：

属性是实体所具有的特征，每个实体可以有多个属性。
例：“银行客户”实体中的每个实例具有姓名、地址、电话等属性。

3.联系：

联系是实体集之间的关系，描述了实体之间的关联。
例：银行客户和银行账户之间存在“储蓄”的关系。

所有实体及实体之间联系的集合构成了一个关系数据库。

4.2关系数据库的存储结构是二维表格

在每个二维表中

每一行称为一条记录,用来描述一个对象的信息
每一列称为一个字段,用来描述对象的一个属性

4.3关系型数据库应用

关系型数据库	应用
Oracle，MySQL	12306用户信息系统
SQL Server，Sybase	淘宝账号系统
Informix，Access	联通手机号信息系统
DB2，FoxPRO	银行用户账户系统
PostgreSQL	网站用户信息系统

关系型数据库:

适用于对关系明确的数据建立模型，定义和存储数据。
关系型数据库擅长处理结构化数据，通常用于需要高度一致性和事务支持的场景，如金融、银行等领域。

5.非关系数据库

非关系型数据库也被称作 NoSQL（Not Only SQL）。
存储数据不以关系模型为依据，不需要固定的表格式。

5.1非关系型数据库的优点

数据库可高并发读写。
对海量数据高效存储与访问。
数据库具有高扩展性与高可用性。

5.2常用的非关系型数据库

1.常见的非关系型数据库

键值存储数据库 (Key-Value Stores)
- **Redis ** 支持持久化的内存数据库，适合用作缓存以及持久化存储。
- DynamoDB
- Riak
- **Memcached 主要用于缓存数据，提供快速的读写操作，但不支持持久化。 **

应用场景: 会话管理、缓存、配置管理等。

文档数据库 (Document Stores)
- **MongoDB 面向文档的数据库，适合存储半结构化和非结构化数据。 **
- CouchDB
- RethinkDB
- MarkLogic

应用场景: 内容管理系统、博客平台、实时分析等。

列族存储数据库 (Column-Family Stores)
- Cassandra
- **HBase ** 基于Hadoop的分布式数据库，擅长处理大规模的数据存储和检索。
- ScyllaDB

应用场景: 分布式大数据存储、数据分析、时间序列数据等。

图数据库 (Graph Databases)
- Neo4j
- Amazon Neptune
- OrientDB
- ArangoDB

应用场景: 社交网络分析、推荐系统、权限管理、知识图谱等。
2.非关系型数据库（NoSQL）:

能够存储海量数据，并为大数据分析提供支持，帮助筛选出有价值的信息。
NoSQL数据库不强制要求数据具有固定的结构，适合处理各种类型的非结构化数据。

3.Not Only SQL (NoSQL):

不仅仅是一个数据库，还可以用来缓存数据。
键值对存储是NoSQL的一种常见方式，其中“键”是变量名，“值”是对应的变量值。

4.Redis的持久化方式:

Redis 作为键值对存储数据库，数据保存在内存中，但会定期将数据写入磁盘以实现持久化。
持久化方式确保了即使在服务器重启或故障时，数据也不会丢失。

5.高德实时分析平台:

使用非关系型数据库存储数据，以应对海量数据的需求。
如果需要持久化数据，可以选择使用Redis来实现数据持久化。

6.MySQL数据库

6.1基本信息

产品性质：MySQL 是一款开源的关系型数据库管理系统。
隶属：MySQL 目前由 Oracle 公司维护和开发。
许可协议：MySQL 遵循 GPL（GNU General Public License）协议，这意味着它可以免费使用和修改。

6.2主要特点

高性能和稳定性：
- MySQL 以其卓越的性能和服务稳定性著称，适用于各种规模的应用，从小型网站到大型企业级系统。
开源和低成本：
- 作为开源软件，MySQL 没有版权限制，可以自由下载、使用和修改，这大大降低了使用成本。
多线程、多用户支持：
- MySQL 支持多线程和多用户环境，这使得它可以处理并发的数据库操作，满足复杂应用的需求。
C/S 架构：
- MySQL 基于 C/S（客户端/服务器）架构，客户端应用程序可以通过网络与 MySQL 服务器通信，执行数据库操作。
安全可靠：
- MySQL 提供了多种安全机制，如用户权限管理、加密连接等，确保数据的安全性和可靠性。
广泛应用：
- 由于其易用性、性能和低成本，MySQL 被广泛应用于 Web 开发、数据分析、电子商务、内容管理系统（CMS）等领域。

6.3MySQL 商业版和MySQL 社区版

1.MySQL 社区版

开源免费：MySQL 社区版是完全开源和免费的，遵循 GPL 许可证。
功能丰富：提供了大多数企业所需的核心功能，包括事务处理、存储引擎（如 InnoDB）、全文搜索和复制功能。
社区支持：社区版主要依赖 MySQL 用户社区的支持，提供免费资源和论坛，但没有正式的技术支持。

2.MySQL 商业版

商业许可：MySQL 商业版需要购买许可，通常适用于需要附加功能和服务的企业。
附加功能：商业版提供了社区版没有的一些企业级功能，例如企业管理工具、数据加密、备份和恢复等增强功能。
技术支持：购买 MySQL 商业版的用户可以获得 Oracle 的专业技术支持和服务，包括24/7的支持和紧急修复等。

6.4MySQL 商业版与社区版的区别

1. 许可与成本：

社区版：完全开源且免费，遵循 GPL 许可证，适合个人开发者、中小企业及预算有限的项目。
商业版：需要购买商业许可证，适合需要企业级功能和技术支持的大型企业或关键任务应用。

2. 功能：

社区版：提供核心的 MySQL 功能，包括事务处理、InnoDB 存储引擎、复制、全文搜索等，但不包括某些高级企业功能。
商业版：除了社区版的所有功能外，还提供额外的企业级功能，如数据加密、在线备份和恢复、企业管理工具、安全增强功能等。

3. 技术支持：

社区版：主要依靠社区支持，通过在线论坛、文档和社区资源获得帮助，没有官方的技术支持。
商业版：由 Oracle 提供专业的技术支持，包括24/7全天候支持、紧急补丁、咨询服务等，适合对服务和支持有较高要求的企业。

4. 更新与补丁：

社区版：更新和补丁主要由社区贡献和维护，可能在某些情况下响应速度较慢。
商业版：由 Oracle 提供更快速和定期的更新与补丁，确保企业客户能够及时获得安全和性能改进。

5. 用途与定位：

社区版：适用于个人项目、小型到中型应用程序、学习和开发环境。
商业版：专为企业级应用程序设计，适用于高可用性、高安全性和高性能要求的生产环境。

6.5MySQL产品

第一阵营： 版本 5.0-5.1，是早期产品的延续。
第二阵营： 版本 5.4-5.7，整合了MySQL AB公司、社区和第三方公司开发的存储引擎，提高性能。
第三阵营： 版本 6.0-7.1，是MySQL Cluster版本，为适应新时代对数据库的集群需求而开发。

产品下载网址：

http://www.dev.mysql.com/downloads

四，MySQL安装方法

方法一，yum下载

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-server
systemctl start mysqld
systemctl status mysqld
grep 'temporary password' /var/log/mysqld.log
2024-08-27T17:20:56.306707Z 1 [Note] A temporary password is generated for root@localhost: 8:fnffTss;&A
mysql -u root -p
输入密码
8:fnffTss;&A

遇下面这个情况

手动导入 MySQL GPG 公钥，再次运行安装命令

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install -y mysql-server

方法二，编译安装MySQL

步骤 1: 安装必要的依赖项

在 CentOS 7 上，首先确保拥有所有必要的构建工具和依赖项：

sudo yum groupinstall 'Development Tools' -y
sudo yum install cmake ncurses-devel bison openssl-devel -y

步骤 2: 下载 MySQL 5.7.20 源代码

从 MySQL 官方网站下载 MySQL 5.7.20 的源代码压缩包，或者使用 wget 命令直接下载：

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz

下载完成后，解压缩文件：

tar -zxvf mysql-5.7.20.tar.gz
cd mysql-5.7.20

步骤 3: 配置编译选项

使用 cmake 配置 MySQL 的编译选项。

vim /etc/
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_TCP_PORT=3306

步骤 4: 编译和安装 MySQL

配置完成后，使用 make 命令开始编译安装 MySQL：

make -j 2 && make install

步骤 5: 创建 MySQL 用户和组

为了安全性，MySQL 通常以专门的 mysql 用户和组身份运行：

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

步骤 6: 设置目录权限

将 MySQL 的安装目录和数据目录的所有权设置为 mysql 用户和组：

chown -R mysql:mysql /usr/local/mysql

步骤 7: 初始化数据库

初始化 MySQL 数据目录，并创建系统表：

bin/mysqld --initialize --user=mysql

初始化完成后，MySQL 会生成一个临时的 root 密码，请记下该密码，稍后将需要它。

步骤 8: 设置 MySQL 配置文件

创建 MySQL 配置文件 /etc/my.cnf，配置文件的基本内容如下：

vim /etc/my.cnf
[client]
port=3306
socket=/usr/local/mysql/mysql.sock

[mysqld]
port=3306
socket=/usr/local/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/mysql.pid
user=mysql
symbolic-links=0
log-error=/usr/local/mysql/mysql-error.log

保存并退出。

步骤 9: 设置 MySQL 服务

将 MySQL 服务脚本复制到 /etc/init.d/，并设置 MySQL 为系统服务：

cp support-files/mysql.server /etc/init.d/mysql
chkconfig --add mysql

步骤 10: 启动 MySQL 服务

启动 MySQL 服务，并设置开机自启动：

baservice mysql start
chkconfig mysql on

步骤 11: 配置环境变量

将 MySQL 的 bin 目录添加到系统的 PATH 变量中，以便在命令行直接使用 mysql 命令：

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.bash_profile
source ~/.bash_profile

步骤 12: 安全配置

运行 mysql_secure_installation 脚本，来完成初步的安全设置：

/usr/local/mysql/bin/mysql_secure_installation

被要求输入之前生成的临时 root 密码，并可以设置新密码，删除匿名用户，禁用远程 root 登录等。
###步骤 13: 登录 MySQL
使用你设置的 root 密码登录 MySQL：

mysql -u root -p123456
或
mysql -u root -p
123456

方法三，二进制安装

1. 下载 MySQL 8.0.35 二进制包

首先，下载 MySQL 8.0.35 的二进制包。可以从 MySQL 官方下载页面下载 MySQL 8.0.35 的 tar.gz 文件。
使用 wget 命令下载：

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz

2. 解压二进制包

下载完成后，将文件解压到适当的目录，比如 /usr/local/mysql：

mkdir -p /usr/local/mysql
tar -xvf mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysql --strip-components=1

3. 创建 MySQL 用户和组

为了安全起见，建议创建一个专门的 MySQL 用户和组：

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

4. 设置目录权限

为 MySQL 数据目录设置适当的权限：

mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql

5. 初始化 MySQL 数据目录

使用 mysqld 命令初始化数据目录：

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

这个命令将初始化数据目录并生成一个临时的 root 密码，可以在输出的日志中找到。

6. 配置 MySQL

创建 MySQL 配置文件 /etc/my.cnf 并添加以下内容：

vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
user = mysql
log-error = /usr/local/mysql/mysql-error.log
pid-file = /usr/local/mysql/mysql.pid

7. 设置环境变量

为了便于使用 MySQL 命令，将 MySQL 二进制目录添加到系统的 PATH 中。可以编辑 /etc/profile 文件，添加以下内容：

export PATH=/usr/local/mysql/bin:$PATH

然后执行以下命令使更改生效：

source /etc/profile

8. 启动 MySQL 服务

使用以下命令启动 MySQL 服务器：

/usr/local/mysql/bin/mysqld_safe --user=mysql &

9. 设置 MySQL 开机启动

可以将 MySQL 设置为系统服务，确保其在系统启动时自动启动。
首先，将 MySQL 的启动脚本复制到 /etc/init.d/：

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

然后，使用以下命令来添加 MySQL 到系统启动项：

chkconfig --add mysql
chkconfig --level 345 mysql on

10. 登录并配置 MySQL

使用初始化时生成的临时 root 密码登录 MySQL：

mysql -u root -p

登录后，你可以根据提示修改 root 密码，并进行其他安全设置。

11. 运行 MySQL 安全配置脚本

建议运行 MySQL 提供的安全配置脚本以增强安全性：

/usr/local/mysql/bin/mysql_secure_installation

五，MySQL 数据库管理

1.ESCRIBE命令的输出

1.1解释：

Field: 显示每个字段（列）的名称。
Type: 显示字段的数据类型和可能的其他属性，如长度或精度。
Null: 指示该字段是否允许为空值 (YES) 或不允许 (NO)。
Key: 指示该字段是否是键。可能的值包括：
Default: 显示字段的默认值。如果在插入新记录时没有为该字段提供值，则会使用该默认值。
Extra: 显示额外的字段属性，如自动递增 (auto_increment)。

1.2常用数据类型及其用途

INT（整型）
- 用途：用于定义整数类型的数据。
- 存储大小：4 字节。
- 范围：
  - 有符号：-2,147,483,648 到 2,147,483,647
  - 无符号：0 到 4,294,967,295
FLOAT（单精度浮点数）
- 用途：用于存储小数点后的数据，通常表示到小数点后六位。
- 存储大小：4 字节（32 位）。
- 精度：大约 7 位十进制数字。
DOUBLE（双精度浮点数）
- 用途：用于存储需要更高精度的小数数据。
- 存储大小：8 字节（64 位）。
- 精度：大约 15 位十进制数字。
CHAR（固定长度的字符类型）
- 用途：用于定义固定长度的字符数据。
- 存储大小：由定义的字符长度决定，最多 255 个字符。
- 特点：如果存入数据的实际长度比指定长度要小，会在右侧补空格至指定长度；如果实际长度大于指定长度，低版本数据库可能会截断数据，高版本可能会报错。
VARCHAR（可变长度的字符类型）
- 用途：用于定义可变长度的字符数据。
- 存储大小：根据实际存储的字符长度加上 1 或 2 字节的前缀（用于存储字符串的长度）。
- 最大长度：最多 65,535 个字符（取决于其他列定义的总长度）。
TEXT（文本类型）
- 用途：用于存储大量的文本数据。
- 存储大小：存储字符串的长度（由 MySQL 版本和文本类型决定，如 TEXT、MEDIUMTEXT、LONGTEXT）。
- 最大长度：最多 65,535 个字符（TEXT 类型）；MEDIUMTEXT 和 LONGTEXT 类型可以存储更多字符。
IMAGE（图片类型）
- 用途：用于存储二进制数据，如图片、视频、音频等。
- 注：在 MySQL 中通常使用 BLOB（Binary Large Object）类型来存储图片等二进制数据。
DECIMAL(5,2)（定点数类型）
- 用途：用于存储定点数，特别适用于需要精确表示的小数数据，如货币金额。
- 定义：DECIMAL(5,2) 表示最多 5 位数字，其中 2 位在小数点后。示例：999.99。
- 存储大小：根据定义的总位数来分配存储空间。

1.3主键（Primary Key）

唯一性：主键是一种唯一标识符，用于确保表中的每一行记录是唯一的。
复合主键：一个主键可以由多个字段（列）组成，这种情况下称为复合主键。复合主键的组合值必须唯一。

1.4说明：

CHAR** vs **VARCHAR：CHAR 适合存储定长的数据，VARCHAR 适合存储可变长度的数据。CHAR 会在不足指定长度时补空格，而 VARCHAR 则只存储实际的字符长度。
精度问题：FLOAT 和 DOUBLE 是近似值类型，不适合需要精确表示的小数运算场景，如财务计算。此时应优先使用 DECIMAL。

这些数据类型的选择和正确使用，对于数据库设计、性能优化、数据完整性和准确性至关重要。

2. MySQL 数据库中不同存储引擎的数据文件存储方式

2.1 MySQL 数据文件和存储引擎

1. MyISAM 存储引擎

.frm** 文件**：
- 存储表的结构定义（表的元数据）。
.MYD** 文件**（MyISAM Data）：
- 存储 MyISAM 表的数据。
.MYI** 文件**（MyISAM Index）：
- 存储 MyISAM 表的索引。
- 无论表有多少索引，这些索引都会存储在同一个 .MYI 文件中。
每个 MyISAM 表在数据库的文件系统中都有三个文件：.frm、.MYD、.MYI，它们都存放在数据库目录中。

2. InnoDB 存储引擎

InnoDB 是 MySQL 中常用的存储引擎，与 MyISAM 不同，它有自己的数据存储和索引管理方式。

.frm** 文件**：
- 与 MyISAM 一样，存储表的结构定义。
.ibd** 文件**：
- 当启用独享表空间（innodb_file_per_table）时，每个表会有一个 .ibd 文件。
- .ibd 文件包含表的数据和索引。
- 这个文件与 .frm 文件存储在同一目录下（通常是数据库目录）。
ibdata** 文件**：
- 当使用共享表空间时，InnoDB 数据和索引存储在 ibdata 文件中。
- 默认情况下，所有表的数据和索引都会存储在一个或多个共享的 ibdata 文件中，文件位置和数量可以通过 MySQL 配置来管理。

2.2 存储引擎选择

MyISAM：
- 适合读操作多的场景，不支持事务。
- 文件存储较为简单，每个表对应 .frm、.MYD 和 .MYI 三个文件。
InnoDB：
- 支持事务、行级锁定和外键，更适合需要高并发写操作和事务管理的场景。
- 可以选择使用共享表空间（ibdata 文件）或独享表空间（每个表一个 .ibd 文件）。

2.3 总结

MySQL 的 MyISAM 存储引擎使用 .frm、.MYD 和 .MYI 文件分别存储表结构、数据和索引。
InnoDB 存储引擎可以使用 .ibd 文件来存储表的数据和索引（在独享表空间模式下），或者使用 ibdata 文件来存储所有表的数据和索引（在共享表空间模式下）。
选择适当的存储引擎和配置方式可以优化数据库的性能和管理。

3. SQL 语句

用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

3.1分类

1.DDL（数据定义语言 - Data Definition Language）

用途：用于定义和管理数据库中的各种对象，如数据库、表、视图、索引等。
常见语句：
- CREATE：创建数据库、表、视图、索引等。
- ALTER：修改数据库或表的结构，如增加或删除列。
- DROP：删除数据库或表。
- TRUNCATE：清空表中的所有数据（但不删除表结构）。

2.DML（数据操纵语言 - Data Manipulation Language）

用途：用于对表中的数据进行插入、更新、删除等操作。
常见语句：
- INSERT：向表中插入新数据。
- UPDATE：更新表中的现有数据。
- DELETE：删除表中的数据。
- 注意：DML 操作通常需要配合事务管理（COMMIT 和 ROLLBACK）以确保数据的一致性。

3.DQL（数据查询语言 - Data Query Language）

用途：用于查询数据库中的数据。
常见语句：
- SELECT：从一个或多个表中查询数据。可以使用 WHERE 子句、ORDER BY 子句、GROUP BY 子句、JOIN 子句等来过滤和排序查询结果。
注意：有时 DQL 会被归类为 DML 的一部分，因为它主要涉及对数据的读取操作。

4.DCL（数据控制语言 - Data Control Language）

用途：
- 用于控制数据库用户或角色的权限，管理安全性和访问控制。
- 用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE
常见语句：
- GRANT：授予用户或角色特定的权限，如访问特定表或执行特定操作的权限。
- REVOKE：撤销用户或角色的特定权限。
注意：DCL 操作通常涉及到数据库的安全性设置，并且影响用户和角色的访问权限。

5.TCL（事务控制语言 - Transaction Control Language）

用途：用于管理事务，控制事务的提交和回滚，确保数据的一致性和完整性。TCL 有时也被包含在 DCL 中。
常见语句：
- COMMIT：提交事务，将事务中的所有操作保存到数据库中。
- ROLLBACK：回滚事务，撤销事务中的所有操作。
- SAVEPOINT：设置事务的保存点，允许对事务进行部分回滚。
- SET TRANSACTION：设置事务的特性，如隔离级别。

4,操作

4.1DDL 数据定义语言

用于创建数据库对象，如库、表、索引等
create
drop
alter
1.创建数据库

CREATE DATABASE 数据库名;

2.创建表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

3.在表创建后设置主键
PRIMARY KEY :主键选择没有重复并且不为空值的字段

alter table 表名 add primary key (字段如id);

4.2DML管理表中的数据记录 ,数据操纵语言

用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
**insert **
update
delete

1.在表里插入数据

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

或

遇到以下问题使用alter table yyy modify passwd char(60);增加加密后的密码长度

2.修改，更新表内数据

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

或改一个值

3.删除数据
a.删除表内数据

DELETE FROM 表名 [WHERE 条件表达式];

b.删除整个表的数据

DELETE FROM 表名

4.3DQL查询数据记录

select
查询数据

SELECT 字段名1,字段名2[,...] FROM 表名[WHERE 条件表达式];
SELECT * FROM 表名;                      #查询整个表数据
SELECT name,score FROM 表名 WHERE id=2;  #查询表id2的 name,score
select name from 表名\G  		             #以列表方式竖向显示
select * from 表名 limit 3;   	         #只显示头3行
select * from 表名 limit 2,3;            #显示第2行后的前3行

4.4数据表高级操作

1.delete

DELETE清空表后，返回的结果内有删除的记录条目。
DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。（假设表中有1,2,3条数据使用delete删除后在增加的数据id号从4开始记录）

DELETE FROM 表名

2.truncate

RUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，
因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，ID会从1开始重新记录

truncate table 表名;

3.删除类型比较：

DROP TABLE:
- 类型： DDL（数据定义语言）
- 可回滚： 不可回滚（操作后无法恢复）
- WHERE 子句： 不支持 WHERE 子句
- 影响： 删除整个表的结构和内容
- 速度： 删除速度非常快
** TRUNCATE TABLE:**
- 类型： DDL（数据定义语言）
- 可回滚： 不可回滚
- WHERE 子句： 不支持 WHERE 子句
- 影响： 只删除表中的所有数据，保留表结构
- 速度： 删除速度很快
** DELETE FROM TABLE:**
- 类型： DML（数据操作语言）
- 可回滚： 可以回滚（如果在事务中执行）
- WHERE 子句： 支持 WHERE 子句，用于选择要删除的特定行
- 影响： 根据 WHERE 子句删除表中的部分或全部数据，保留表结构
- 速度： 删除速度较慢，因为逐行删除数据

4.选择使用的场景：

DROP: 当不再需要整张表时使用。
DELETE: 当需要删除部分数据行时，且可以使用 WHERE 子句指定条件。
TRUNCATE: 当需要保留表结构但删除所有数据时使用。

5.速度和安全性比较：

速度:DROP > TRUNCATE > DELETE
安全性:DELETE 最安全，因为它可以回滚并且操作更加精细。

删除需要谨慎，多次与上级核实，确保是需要删除的

6.创建临时表（Temporary Table）的特点

使用 CREATE TEMPORARY TABLE 语句来创建临时表，用SHOW TABLES命令无法查看到创建的临时表，一旦会话结束，临时表会自动销毁。
会话结束前，可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。
无法无法创建外键的原因：MySQL 不支持在临时表中创建外键约束，因为临时表的生命周期有限，并且不适合建立与其他表的持久性关系。
临时表的适用场景：临时表适用于处理临时数据，而不适合需要复杂关系的场景。

创建临时表

create temporary table xxx (
    id int(4) zerofill primary key auto_increment,
    name varchar(10) not null,
    cardid int(18) not null unique key,
    hobby varchar(50));

insert into xxx values (1,'zhangsan',123456,'running');
select * from xxx;
show tables;
quit
退出后再次登录mysql查看
select * from xxx;


int(4) zerofill：如果数值不满4位数，前面用"0"填充，例0001

auto_increment：此字段为自增长字段，即每条记录自动递增1，默认从1开始递增，自增长字段数据不可以重复，自增长字段必须是主键，如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键

not null：表示此字段不允许为NULL

退出后查看

7. 数据库管理系统（DBMS）处理 `INSERT` 操作时的流程（SQL数据流向）

执行插入命令：执行 INSERT INTO info 命令，开始向 info 表中写入数据。
数据暂存到内存：数据库将数据暂时存储在内存中，而不是直接写入磁盘。
表数据复制到内存：info 表的数据会被复制到内存中，以便进行修改操作。
在内存中修改数据：在内存中的副本上进行数据的插入或修改。
等待提交确认：修改完成后，等待用户提交确认（COMMIT）。
提交后写入磁盘：敲下回车并提交（COMMIT）后，数据才会从内存写入磁盘中的表。
数据持久化：确认提交后，数据被永久保存到磁盘中，确保数据持久保存。

对于 CREATE TABLE xxx：

临时表的操作：创建的临时表只会保存在内存中，所有操作都是在内存中进行。
连接结束时释放：退出数据库连接后，临时表和其数据会被自动释放，不会保存在磁盘上。

8.克隆

8.1克隆复制表

create table x1 like y1;    #复制格式，通过LIKE方法，复制x1表结构生成y1表

8.2克隆复制内容

insert into x1 select * from y1;      #备份内容

8.3克隆备份

CREATE TABLE test02 (SELECT * from test);   #复制test 表数据到test02中
show create table test02\G

9.修改表名和表结构

ddl

9.1修改表名

ALTER TABLE 旧表名 RENAME 新表名;

9.2 扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值为地址不详，可与NOT NULL配合使用
ALTER TABLE yyy ADD address varchar(50) NOT NULL default '地址不详';

9.3修改字段(列)名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
unique key:唯一键（特性:唯一，但可以为空，空值只允许出现一次)   
Primary key:唯一且非空(主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。)


ALTER TABLE yyy CHANGE name user_name varchar(10) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。

ALTER TABLE 表名 modify column 字段名 类型。
数据库中表 字段是varchar(30)，修改类型可以用（谨慎修改类型，可能会导致原有数据出错）

10，删除字段

ALTER TABLE 表名 DROP 字段名;

ALTER TABLE yyy DROP passwd;

11.另类创建表和主键的的方式

例
use school;
create table if not exists info (
id int(4) zerofill primary key auto_increment,     #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
not null：表示此字段不允许为NULL

4.5数据库用户管理

1.创建新用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
密码：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
如果需要使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；

例
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2.查看用户信息

USE mysql;
SELECT User,authentication_string,Host from user;

3.重命名

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

4.删除用户

DROP USER 'lisi'@'localhost' ;

5.设置修改当前和其他账户密码，忘记root的解决办法

SET PASSWORD = PASSWORD('abc123');  #修改当前的账 户密码，是登录mysql的用的账号

修改别的账号密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T');

忘记mysql的root密码
第一步，修改/etc/my.cnf 配置文件为免密登陆mysql,重启mysql

vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加这个，使登录mysql不使用授权表

systemctl restart mysqld

第二步，免密登录mysql，修改root的密码

mysql -u root -p
upadate mysql.user set authenication_string = password('123456') where user='root';

第三部，注释/etc/my.cnf配置文件中的skip-grant-tables。重启mysql

vim /etc/my.cnf
[mysqld]
#skip-grant-tables    #注释这个

systemctl restart mysqld

网址cmd5.com可以将md5加密的简单密码还原

4.6数据库用户授权（重点）

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, 
update”。使用"all"表示所有权限，可授权执行任何操作。

数据库名.表名：指定授权操作的数据库和表的名称，其中可以使用通配符"*"。
例如，使用“kgc.*"表示授权操作的对象为school数据库中的所有表。

'用户名@来源地址'：指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192.168.80.%”等。

IDENTIFIED BY：设置用户连接数据库时所使用的密码字符串。
在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。

没有授权的情况下无法查看数据库和表

允许用户在本地查询某某某数据库中所有表的数据记录

例
允许用户zhangsan在本地查询school数据库中所有表的数据记录，
但禁止查询其他数据库中的表的记录。
GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

只能查看xxx库和其下的表，其他操作不允许（比如删除等等）

给账号赋予权利

使用Navicat Premium测试

只给了xxx数据库下的yy2表，只能看到yy2

all privilege权限：

insert(插入数据)
select (查询数据)
update (更新表的数据)
delete(删除表中数据)
create (创建库，表)
drop(删除库，表)
refernces 允许用户执行已定义的存储过程和函数。
index(建立索引)
alter(更改表属性)
create temp orary tableslock tables (锁表)
execute
create view (创建视图)
show view(显示视图)
create routine(创建存储过程)
alter routine(修改存储过程)
event(事件)
trigger on(创建触发器)

查看账户的的权限

SHOW GRANTS FOR 用户名@来源地址;
show grant for 'dbe1'@'192.168.88.%';

收回权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;
例
REVOKE ALL ON *.* FROM 'lisi'@'%';

USAGE权限只能用于数据库登陆，不能执行任何操作;
USAGE权限不能被回收，即REVOKE不能删除用户。
flush privileges;