数据库的基本操作

数据库的基本操作

创建数据库

​ MySQL安装好以后，首先需要创建数据库，这是使用MySQL各种功能的前提。安装完成之后，将会在其data目录下自动创建几个必须的数据库，可以使用SHOW DATABASES;语句来查看当前所有存在的数据库。

​ 可以看到，数据库列表中包含了4个数据库，这四个数据库是必然存在的，一般建议是不需要删除。

​ 创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，则可以直接使用，否则，需要自己创建数据库。MySQL中创建数据哭的基本SQL语句为CREATE DATABASE database_name; （“database_name” 为要创建的数据库的名称，并且该名称不能与已经存在的数据库重名）。

[e.g.]

_创建测试数据库 test_db：_

CREATE DATABASE test_db;

​ 数据库创建好之后，可以使用SHOW CREATE DATABASE声明查看数据库的定义

_查看创建好的数据库 test_db 的定义：_

SHOW CREATE DATABASE test_db;

SHOW CREATE DATABASE test_db\G

再次使用 SHOW DATABASES;语句来查看当前所有存在的数据库

​ 可以看到，数据库列表中包含了刚刚创建的数据库 test_db 和之前存在的四个自带的数据库，一共5个数据库。

删除数据库

​ 删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。删除数据库的SQL基本语句为DROP DATABASE database_name;（同理 此处的database_name 为你想要删除的库名），如果你指定的数据库名不存在，则删除会出错。

[e.g]

_删除测试数据库 test_db_

DROP DATABASE test_db

_再次查看数据库 test_db的定义_

执行结果给出一条错误信息，即说明数据库 test_db 已不存在，删除成功了。

数据库存储引擎

​ 数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。MySQL的核心就是存储引擎。

MySQL 存储引擎简介

​ MySQL 提供了多个不同的存储引擎，不需要再整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL5.7 支持的存储引擎有：InnoDB，MyISAM，Memory，Merge，Archive，Federated，CSV，BLACKHOLE等。可以使用SHOW ENGINES;语句查看系统所支持的引擎类型。

SHOW ENGINES\g 等效于SHOW ENGINES;也就是 ‘ \g ’ 等于 ‘ ; ’

SHOW ENGINES\G 将查询到的横向表格纵向输出，方便阅读。（自己在terminal里试一下吧，太长了！）

​ Support 列的值表示某种引擎是否能使用：YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认存储引擎。

InnoDB 存储引擎

​ InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.7中，InnoDB 作为默认存储引擎。InnoDB 主要特性有：

1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。
2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。InnoDB表可以是任何尺寸，即使文件尺寸被限制为2GB的操作系统上。
4. InnoDB 支持外键完整性约束（FOREIGN KEY）。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此作为主键。
5. InnoDB 被用在众多需要高性能的大型数据库站点上。
6. InnoDB 不创建目录，使用InnoDB时，MySQL 将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0 和 ib_logfile1 的5MB大小的日志文件。

MyISAM 存储引擎

​ MyISAM 基于ISAM的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。主要特性有：

1. 大文件（63位文件长度）在支持大文件的文件系统和操作系统上被支持。
2. 当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少的碎片。
3. 每个MyISAM 表最大索引数为64，这可以通过重新编译来改变。每个索引最大的列数是16个。
4. 最大的键长度是1000B，这也可以通过编译来改变。对于键长度超过250B的情况，一个超过1024B的键将被用上。
5. BLOB和TEXT列可以被索引。
6. NULL值被允许在索引的列中。这个值占每个键的0~1个字节。
7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。
8. 每表一个AUTO_INCREMENT列的内部处理。为INSERT和UPADATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。
9. 可以把数据文件和索引文件放在不同的目录。
10. 每个字符列可以有不同的字符集。
11. 有VARCHAR的表可以固定或动态记录长度。
12. VARCHAR和CHAR列可以多达64KB。
13. 创建数据库时，会产生3个文件，文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD(MYData)，索引文件的扩展名是.MYI(MYIndex)。

MEMORY 存储引擎

​ MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。MEMORY主要特性有：

1. MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500B的最大键长度。
2. MEMORY存储引擎执行HASH和BTREE索引。
3. 可以在一个MEMORY表中有非唯一键。
4. MEMORY表使用一个固定的记录长度格式。
5. MEMORY不支持BLOB或TEXT列。
6. MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。
7. MEMORY表在所有客户端之间共享
8. MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。
9. 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。

存储引擎的选择

​ 不同的存储引擎都有各自的特点，以适应不同的需求，那么在做选择权，首先需要考虑每一个存储引擎提供了那些不同的功能。

​ 如果要提供提交、回滚和崩溃恢复能力的事务安全能力，并要求实现并发控制，InnoDB是个很好的选择。如果数据表主要用来插入和查询记录的，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。如果只有INSERT 和SELECT 操作，可以选择Archive引擎，它还支持高并发的插入操作，但是本身并不是事务安全的，非常适合存储归档数据，例如记录日志信息可以使用Archive引擎。

​ 一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，选择使用合适的存储引擎，将会提高整个数据库的性能。

综合案例——数据库的创建和删除

1. 登录数据库

   mysql -uroot -p（本机登录可以不用写 -h localhost）

   

2. 创建数据库zoo

   CREATE DATABASE zoo; 创建数据库 zoo

   SHOW DATABASES; 查看数据库里有哪些数据和，查看数据库zoo是否创建成功。

   

3. 选择当前数据库为zoo

   USE zoo;

4. 查看数据库zoo的信息

   SHOW CREATE DATABASE zoo;

   SHOW CREATE DATABASE zoo\G

   

5. 删除数据库zoo

   DROP DATABASE zoo; 删除zoo这个数据库

   SHOW DATABASES; 查看数据库是否删除

   

如何查看默认存储引擎

1. 查看系统中所有的存储引擎

   SHOW ENGINES;

   

2. 查看默认存储引擎

   SHOW VARIABLES LIKE 'storage_engine'; 查看默认存储引擎的SQL语句也同时验证了之前说默认存储引擎为InnoDB。