当前位置：首页 > news >正文

MySQL四大引擎，数据库管理，数据表管理，数据库账号管理

news 文章来源：https://blog.csdn.net/djssubddbj/article/details/135353029 2024/10/22 10:48:48

MySQL四大引擎

InnoDB

InnoDB引擎是MySQL默认的存储引擎。它支持事务和行级锁定，并具有高并发性和数据完整性保护的特性。InnoDB适用于具有复杂查询和高并发读写操作的应用程序。MyISAM

InnoDB引擎特点和优势

事务支持：InnoDB支持ACID（原子性、一致性、隔离性和持久性）事务，这意味着多个操作可以作为一个原子性操作执行，要么全部成功，要么全部回滚。这对于处理复杂的业务逻辑和保护数据完整性非常有用。
行级锁定：InnoDB引擎支持行级锁定，这意味着只有正在访问的行会被锁定，而不是整个表。这允许并发操作，提高了系统的并发性能。
外键约束：InnoDB支持外键约束，可以在表之间建立关系，实现数据的一致性和完整性。外键约束可以确保引用表中的数据与主表中的数据保持一致。
高并发性能：InnoDB引擎使用多版本并发控制（MVCC）来处理并发读写操作。它通过在事务中创建快照来实现并发性能，读操作不会阻塞写操作，写操作也不会阻塞读操作。
崩溃恢复：InnoDB具有崩溃恢复机制，可以在数据库崩溃后自动恢复数据的一致性。它将数据和日志文件分开存储，可以在崩溃后重新应用日志，确保数据的完整性。
支持可扩展性：InnoDB可以在多个CPU上并行处理事务，从而提高了系统的可扩展性和性能。

总体而言，InnoDB引擎适用于需要处理复杂的业务逻辑、需要高并发性和数据完整性保护的应用程序。它是MySQL默认的存储引擎，并在许多大型网站和应用程序中广泛使用，存储限制：64TG

Memory

Memory引擎将数据存储在内存中，提供了非常快速的数据读写速度。然而，由于数据存储在内存中，重启或崩溃时会丢失数据。因此，Memory引擎适用于临时数据或缓存。

Memory（也称为Heap）是MySQL的一种存储引擎，它将数据存储在内存中而非磁盘上。

Memory引擎的特点和优势：

快速访问：由于数据存储在内存中，Memory引擎的读写操作非常快速。这对于需要高性能和低延迟的应用程序非常有用。
适用于临时数据：Memory引擎适用于存储临时数据，如缓存数据、会话数据等。由于数据存储在内存中，读写速度快，适合处理频繁变动的数据。
不支持事务和持久性：Memory引擎不支持事务处理和持久性。当数据库重新启动时，所有存储在内存中的数据都会丢失。因此，Memory引擎不适合存储关键的持久性数据。
不支持外键约束和其他高级功能：Memory引擎不支持外键约束、触发器、子查询等一些高级功能。它主要专注于快速访问和处理数据。
简单的数据结构：Memory引擎使用哈希索引来加快数据的查找速度。由于没有复杂的磁盘I/O和事务管理，Memory引擎的数据结构比其他引擎更简单。

总体而言，Memory引擎适用于需要快速访问和处理临时数据的应用程序，特别适合用作缓存或会话存储。它有着极快的读写性能，但不支持事务处理和持久性。因此，在选择使用Memory引擎时，需要权衡数据的重要性和可靠性需求。

MyISAM

MyISAM引擎是MySQL最早的存储引擎之一。它不支持事务和行级锁定，但具有很高的插入速度和空间效率。MyISAM适用于读密集型应用程序，如日志记录或搜索引擎。

MyISAM引擎是MySQL的一种存储引擎，是默认的存储引擎之一。

MyISAM引擎特点和优势

高性能：MyISAM引擎适用于读操作较多的应用场景，它可以提供很好的读取性能。它使用了表级锁定，因此在写入操作较少或没有并发写入的情况下，也可以具有良好的性能。
支持全文索引：MyISAM引擎支持全文索引，可以在文本列上进行高效的全文搜索。这对于需要进行全文搜索的应用程序非常有用。
不支持事务和行级锁定：MyISAM引擎不支持事务处理和行级锁定。这意味着它不适用于需要高并发写入和复杂的事务处理的应用程序。
不支持外键约束：MyISAM引擎不支持外键约束，这意味着它不具备维护数据完整性的能力。如果数据的完整性对应用程序很重要，可能需要考虑其他支持外键约束的引擎。
低存储和I/O开销：MyISAM引擎使用了较低的存储和I/O开销，这使得它适用于存储大量的只读数据。它还支持压缩表格，可以进一步减小存储空间。

总的来说，MyISAM引擎适用于读取密集型、对数据完整性要求不高的应用场景。它具有高性能和支持全文索引的优势，但不支持事务处理、行级锁定和外键约束。在选择使用MyISAM引擎时，需要考虑应用程序的读写比例、并发性和数据完整性等因素。

查看数据库引擎

SHOW ENGINES

数据库管理

元数据库

元数据库是一种特殊类型的数据库，用于存储和管理其他数据库的元数据。元数据是描述数据库结构、对象和关系的数据。它包括表、字段、索引、触发器等对象的定义、属性和关系，以及其他与数据库相关的信息，如用户权限和访问控制。元数据库可作为一个中央存储库，提供对其他数据库的元数据的集中管理和访问。

元数据库的主要功能

元数据库的主要功能包括元数据的创建、修改和删除，以及元数据的查询和检索。它可以用于管理和监控数据库的结构和对象的变化，支持数据模型的维护和演化，以及数据库的版本控制和迁移。元数据库还可以提供对数据库的数据字典和文档的自动生成，以及对数据库设计和开发过程的支持。

元数据库的好处包括提高数据库的可管理性和可维护性，减少人工操作和错误，提高数据库设计和开发的效率，以及支持数据库的自动化管理和部署。它还可以提供对数据库的全面监控和分析，帮助用户理解和优化数据库性能，并支持数据库的安全和合规性管理。

元数据库分类

information schema

Information Schema是MySQL数据库系统中的一种特殊数据库，用于存储关于数据库系统自身的元数据信息。它包含了数据库、表、列、索引、用户权限等各种元数据信息，可以通过查询Information Schema来获取数据库系统的元数据。

mysql

MySQL是一种开源的关系型数据库管理系统（RDBMS），它使用结构化查询语言（SQL）来管理和操作数据。MySQL数据库本身并不属于元数据库的范畴，它是一种用于存储和管理数据的数据库系统。

performance schema

Performance Schema是MySQL数据库系统中的一个特殊数据库，用于存储和管理与性能相关的元数据信息。它提供了一组系统视图和表，可以用于监控和分析MySQL数据库服务器的性能。面积

数据表管理

数据库的三大范式

数据库三大范式是指关系数据库设计中的三个规范化原则，用于规范化数据库的结构和消除数据冗余。

第一范式（1NF）：确保每个列都是原子的，即每个列不可再分。这意味着每个属性的值应该是不可分解的。例如，一个包含学生信息的表，每个学生只能有一个学号，不能有多个学号。

第二范式（2NF）：确保非主键列完全依赖于主键。这意味着每个非主键列必须完全依赖于主键，而不是依赖于主键的一部分。如果存在部分依赖，就需要将表分解成多个表来消除冗余。例如，一个包含订单信息的表，订单号是主键，每个订单可以包含多个产品，但是产品信息应该单独存储在另一个表中。

第三范式（3NF）：确保非主键列之间没有传递依赖。这意味着在一个表中，非主键列之间不应该存在传递依赖关系。如果存在传递依赖，就需要将表进一步分解以消除冗余。例如，一个包含员工信息的表，每个员工的部门信息应该存储在一个独立的表中，而不是直接存储在员工表中。

优化原则

越小越好
更小的磁盘空间、内存、CPU缓存
更少的CPU周期

数值类型

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

整数型

一般常见的整数的数据类型有int、smallint、mediumint、bigint。

数据类型	字节大小	范围（有符号）	范围（无符号- unsigned）	用途
TINYINT	1 字节	(-128，127)	(0，255)	微小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	小整数值
MEDIUMINT	3字节	(-8 388 608，8 388 607)	(0，16 777 215)	中等整数值
INT或INTEGER	4字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	整数值
BIGINT	8字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	大整数值

小数型

数据类型	字节大小	范围（有符号）	围（无符号- unsigned）	用途
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点小数值
OUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308）	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点小数

日期时间型

类型	字节大小	范围	格式
DATE	4字节	000-01-01/9999-12-31	YYYY-MM-DD
TIME	3字节	-838:59:59/838:59:59	HH:MM:SS
YEAR	1字节	1901/2155	YYYY
DATETIME	8字节	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4字节	1970/1/1 0:00	YYYYMMDD HHMMSS

这里只说datetime与timestamp区别：
datetime与timestamp都是日期和时间的混合类型，区别在于：
（1）表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围；
（2）将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间；
（3）同一个timestamp类型的日期或时间，不同的时区，显示结果不同；
（4）insert、update、delete语句中，日期时间型数据，需要用引号（单引号或者双引号）引起来。

字符型

字符串类型	字节大小	描述及存储需求
CHAR	0-255字节	定长字符串，如CHAR(10)，定长10占位，不足补空格
VARCHAR	0-255字节	变长字符串，如VARCHAR(10)，最长10个字节，存储长度按照实际输入长度为准
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
VARBINARY(M)	M	允许长度0-M个字节的变长字节符串，值的长度+1个字节
BINARY(M)	M	允许长度0-M个字节的定长字节符串

1、字符串型数据，在sql语句中，需要用单引号或者双引号引起来，如果"张三"，"中国人"；
2、char、varchar、text比较：
（1）经常变化的字段用varchar
（2）知道固定长度的用char
（3）尽量用varchar
（4）超过255字符的只能用varchar或者text
（5）能用varchar的地方不用text
（6）按照查询速度： char最快， varchar次之，text最慢。
3、binary和varbinary区别：
（1）binary类型的长度是固定的，在创建表时就指定了，实际数据长度不足最大长度的空间由‘\0’补全。举个例子，binary(50)就是指定binary类型的长度为50；
（2）varbinary类型的长度是可变的，在创建表时指定了最大长度；指定好了varbinary类型的最大值以后，其长度可以在0到最大长度之间。举个例子，varbinary(50)的最大字节长度是50，但是，不是每条记录的字节长度都是50。在这个最大值范围内，使用多少分配多少。varbinary类型实际占用的空间为实际长度加一。这样，可以有效的节约系统的空间；

数据库账号管理

数据库账号管理是指对数据库中的账号进行管理和控制的操作。数据库账号是用来控制用户对数据库的访问权限的身份标识。数据库账号管理包括创建账号、修改账号、删除账号、授权账号等操作。

创建账号：创建一个新的数据库账号，指定账号名称和密码，并设置该账号的权限。

修改账号：修改已有的数据库账号的密码，或修改账号的权限。

删除账号：删除数据库中的一个账号，同时删除该账号所拥有的权限。

授权账号：给账号分配权限，指定账号对数据库中的某些表、视图、存储过程等的操作权限。

数据库账号管理是非常重要的安全措施，可以保障数据库中的数据不被未经授权的人访问和修改。只有经过授权的账号才能访问数据库，并且根据账号的权限只能进行特定的操作。

用户的管理操作

查询用户常见信息

select user,host from user;

查询用户

select * from user;

设置权限

创建用户设置密码

create user liao identified by '123456'

select user,host from user

收回权部分权限

revoke all on mysql.* from liao@'%';

增加授权
grant ALL on mysql.* to liao@'%';

授予查看一张表格权限

grant select on mysql.tables_priv to liao@'%';