MySQL存储引擎

InnoDB存储引擎

InnoDB存储引擎是MySQLCommon storage engine,

1. 它给MySQL的表提供了事务处理、回滚、Features like crash fixes and multi-version concurrency control;
2. Support column value auto-increment（Column values ​​cannot be null and must be unique）;
3. 支持外键.
4. 缺点： The space occupied is relatively large

MyISAM储存引擎

MyISAMStorage engine support3种不同的存储格式：静态型、动态型和压缩型.

• 静态型：静态型是MyISAM的默认存储格式,Its fields are fixed length;
• 动态型：包含变长字段,记录的长度不是固定的.

MyISAMThe storage engine takes up little space,处理速度快;However, transaction integrity and concurrency are not supported.

Memory存储引擎

MemoryThe storage engine uses what is stored in memory to create tables,And the data is all stored in memory.

每个基于Memory存储引擎的表实际对应一个磁盘文件,The file name is the same as the table name,And only store the structure of the table.如果重启或关机,所有数据都会消失.

Memory存储引擎默认使用哈希索引,It can also be specified as when creating the indexB型树索引,Using a hash index is faster than using Btype tree index is fast.

Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作,非常适合存储归档数据,such as log information.

Archivestorage engine by usingzlib算法将数据行压缩后存储,But it doesn't know about transaction-safe storage engines,It was designed to provide high-speed insertion and compression.

数据库的相关操作

创建数据库

create database [if not exists] <数据库名>
[[default] charset set <字符集名>]
[[default] collate <校对规则名>]

• [if not exists]：Indicates whether the database exists before creating the database,The create operation is performed only if the data does not exist
• [[default] charset set <字符集名>]：Represents the character set of the specified database.
• [[default] collate <校对规则名>]：Indicates the default collation for the specified character set.

修改数据库

alter database <数据库名> {
[default] charset set <字符集名> |
[default] collate <校对规则名> }

使用alter databaseChange the global properties of the database,You need to obtain permission to modify the database in advance.

删除数据库

drop database [if exists] <数据库名>

查看数据库列表

show databases [like '数据库名'];

用户查看MySQLA list of databases that currently exist in .

[like ‘数据库名’]：User matches the specified database name,It can be a partial match or an exact match.

打开数据库

use <数据库名>