电工与电子技术电子商务电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第6章ppt

1、电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第6章第6章MySQL存储引擎与数据库操作管理数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）CONTENTS存储引擎1字符集2创建数据库3修改数据库名称42知识点小结6删除数据库字符集16.1.1 概述存储引擎实际上就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据的机制。因为在关系数据库中数据的存储以表的形式存储的，所以存储引擎也可以称为表类型。MySQL数据库的存储引擎在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的，但是MySQL数据库提

2、供了很多种存储引擎。MySQL中的每一种存储引擎都有各自的特点。对于不同业务类型的表，为了提升性能，数据库开发人员应该选用更合适的存储引擎。6.1.1 概述查看当前MySQL数据库支持的存储引擎，有两种方式SHOW ENGINES;或SHOW ENGINES G说明：上述语句可以使用分号“；”结束，也可以使用“g”或者“G”结束，其中，“g”的作用于分号作用相同,而“G”可以让结果更加美观。【例6-1】登录MySQL控制台成功后执行SHOW ENGIENS G语句并查看结果。6.1.1 概述从上述结果我们可以知道，当前版本的MySQL数据库支持FEDERATED、MRG_MYISAM、MyIS

3、AM、 BLACKHOLE、CSV、MEMORY、ARCHIVE、InnoDB和PERFORMANCE_SCHEMA存储引擎。其输出的参数说明如下：Engine: 数据库存储引擎的名称。Support: 表示MySQL是否支持该类引擎。YES表示支持，NO表示不支持。Comment: 表示对该引擎的解释说明。Transactions: 表示是否支持事务处理。YES表示支持，NO表示不支持。(数据库事务，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。)XA: 表示是否分布式交易处理的XA规范，YES表示支持，NO表示不支持。Savepoints: 表示是否支持保存点

4、，以便事务回滚到保存点，YES表示支持，NO表示不支持。6.1.1 概述【例6-2】执行SHOW VARIABLES LIKE have%G语句并查看结果。执行结果如下结果中，value显示“DISABLED”和“YES”的标记表示支持该存储引擎，前者表示数据库启动的时候被禁用。“NO”表示不支持。6.1.1 概述【例6-3】如果读者不确定当前数据库默认存储引擎，可以通过以下语句经行查看：SHOW VARIABLES LIKE %storage_engine%;6.1.1 概述【例6-4】用USEstudentInfo；命令来选择studentInfo数据库，在用CREATE TABLE命令来

5、创建exampleTable,并用ENGINE指定数据库的存储引擎是MyISAM，用charset指定字符集是utf8，COLLATE指定校验规则utf8_bin。命令结果如下显示：6.1.2 InnoDB存储引擎InnoDB存储引擎是事务（Transaction）安全的，并且支持外键（foreign key）。如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（即insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。对于支持事务的InnoDB表，影响速度的主要原因是打开了自动提交( autocommit )选项，或者程序没有显示调

6、用“begin transaction;”（开始事务）和“commit; ”（提交事务），导致每条insert、delete或者update语句都自动开始事务和提交事务，严重影响了更新语句（insert、delete、update语句）的执行效率。让多条更新语句形成一个事务，可以大大提高更新操作的性能（有关事务的概念将在后续章节进行）。6.1.2 InnoDB存储引擎对于InnoDB存储引擎的数据库表而言，存在表空间的概念，InnoDB表空间分为共享表空间与独享表空间。其中，表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以

7、称作表空间。共享表空间：MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种元数据信息以及事务的回滚(UNDO)信息，全部存放在共享表空间文件中。默认情况下，该文件位于数据库的根目录下，文件名是ibdatal，且文件的初始大小为1OM。可以使用MySQL命令“show variables like innodb_data_file_path”查看该文件的属性（文件名、文件的初始大小、自动增长等属性信息）。 独享表空间：如果将全局系统变量innodb_file_per_table的值设置为ON（innodb_file_per_table的默认值为OFF），那么之后再创

8、建InnoDB存储引擎的新表时，这些表的数据信息、索引信息将保存到独享表空间文件中。6.1.3 MyISAM存储引擎MyISAM是默认存储引擎，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。 MyISAM表格可以被压缩，而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。如果事务回滚将造成不完全回滚，不具有原子性。如果执行大量的查询操作时，MyISAM是更好的选择。如果某个表主要提供OLAP支持，建议选

9、用MyISAM存储引擎。MyISAM具有检查和修复表的大多数工具。MyISAM表可以被压缩，而且最早支持全文索引，但MyISAM表不是事务安全的，也不支持外键（foreign key）。如果某张表需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。6.1.4 MEMORY存储引擎Memory存储引擎（之前称为HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。它默认使用哈希(HASH)索引，而不是我们熟悉的B+树索引。虽然Memory存储引擎速度非常快，但在使用上

10、还是有一定的限制。MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集（intermediate result）。6.1.5 MERGE存储引擎MERGE存储引擎一组MyISAM表的组合，这些MyISAM表必须结构完全相同MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作插入操作：通过INSERT_METHOD字句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使的插入操作被相应地作用在第一或最后一个表上，不定义这个字句或者定义为NO，表示不能对这个MERGE表执行插入操作。DROP操作：只是删除MERGE的定义，对内部的表没有任何影响。

11、MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm文件存储表定义。另一个.MRG文件包含组合表的信息，包括MERGE表有哪些表组成、插入新的数据时的依据。可以通过修改.MRG文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。6.1.6 其他存储引擎(1)BLACKHOLE存储引擎BLACKHOLE 存储引擎是一个“黑洞”,不管我们写入任何信息，都是有去无回。 虽然不能存储数据，但是MySQL数据库还是会正常记录下Binlog（二进制日志），而这些Binlog还会被正常地同步到Slave上，可以Slave上对数据进行后续处理。BLACKHOLE存储引擎一般用

12、于以下3种场合:验证存储文件语法的正确性来自二进制日志记录的开销测量，通过比较，允许二进制日志功能的BLACKHOLE的性能与禁止二进制日志功能的BLACKHOLE的性能。被用来查找与存储和引擎自身不相关的性能瓶颈。在选择存储引擎时，应根据应用特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。6.1.6 其他存储引擎(2)CSV存储引擎CSV 存储引擎实际上操作的就是一个标准的CSV 文件，他不支持索引。主要用途：有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV 文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张

13、CVS 表，然后将生成的报表信息插入到该表，即可得到一份CSV 报表文件了。(3)ARCHIVE存储引擎ARCHIVE 存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。 ARCHIVE 表不支持索引，通过一个.frm 的结构定义文件，一个.ARZ 的数据压缩文件还有一 个.ARM 的meta. 信息文件。由于其所存放的数据的特殊性，ARCHIVE 表不支持删除，修改操作，仅支持插入和查询操作。锁定机制为行级锁定。6.1.7 存储引擎的选择（1）MyISAM适用场景是不需要事务支持、并发相对较低、数据修改相对较少、以读为主、数据一致性要求不是非常高。在选择存储引擎时，应根据应用

14、特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。（2）InnoDB 适用场景是需要事务支持、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成（3）MEMORY适用场景是需要很快的读写速度、对数据的安全性要求较低。MEMORY存储引擎对表的大小有要求，不能是太大的表。字符集26.2.1 概述字符集产生的原因:计算机只能识别二进制代码，为了使计算机不仅能做科学计算，也能处理文字信息，人们想出了给每个文字符号编码以便于计算机识别处理的办法.字符集简单地说就是一套文字符号及其编码、比较规则的集合第一个计算机字符集ASCII，后来进一步变成了国际标准

15、IS0-646采用7位编码，定义了包括大小写英文字母、阿拉伯数字、和标点符号，以及33个控制符号等。6.2.2 MySQL支持的字符集默认情况下，MySQL使用字符集为latin1（西欧ISO_8859_1字符集的别名）。由于latin1字符集是单字符编码，而汉字是双字节编码，由此可能导致MySQL数据库不支持中文字符查询或者中文字符乱码等问题。为了避免此类问题，需要对字符集及字符排序柜子进行设置。MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以使用相同的字符集，可以用SHOW CHARACTER SET 查看所有可以使用的字符集6.2.2 MySQ

16、L支持的字符集6.2.2 MySQL支持的字符集6.2.2 MySQL支持的字符集或者使用information_schema.character_sets可以显示所有的字符集和该字符集默认校对规则6.2.2 MySQL支持的字符集字符集校对规则定义MySQL存储字符串的方式定义比较字符串的方式字符集和校对规则是一对多的关系，两个不同的字符集不能有相同的校对规则，每个字符集有一个默认校对规则MySQL字符集例如gbk默认校对规则是gbk_chinese_ci，对于校验规则命名约定，它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（表示大小写不敏感）、_cs(大小写敏感)或_bin(

17、按照二进制编码值进行比较)结束。6.2.2 MySQL支持的字符集MySQL支持30多种字符集的70多种校对规则。每个字符集至少对应一个校对规则。可以用SHOW COLLATION LIKE *;或者通过系统表information_schema.COLLATIONS来查看相关字符集的校对规则。在上面的例子中GBK的校对规则，其中gbk_chinese_ci校对规则是默认的校对规则，其规定对大小写不敏感，即如果指定比较“N”和“n”，认为这两个字符是相同的。如果按照gbk_bin校对规则比较，由于它是对大小写敏感，所以认为这两个字符是不同的。6.2.3 MySQL字符集的选择对数据库来说，字符

18、集更加重要，因为数据库存储的数据大部分都是各种文字、字符集对数据库的存储、处理性能以及日后对系统的移植、推广都会有影响。MySQL目前支持的字符集种类繁多，选择时应该从以下几点考虑： 满足应用支持语言的要求（utf-8）考虑数据库字符集对已有数据的兼容性数据库只需要支持一般中文，数据量很大，性能要求也很高，选择双字GBK数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好如果所有客户端都支持相同的字符集，则应该优先选择字符集作为数据库字符集6.2.3 MySQL字符集的设置MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级字符集、校对规则(1)服务

19、器字符集和校对规则首先用”SHOW VARIABLES LIKE character_set_server ”;查询当前服务器的字符集用”SHOW VARIABLES LIKE collation_server;”命令查看校对规则。6.2.3 MySQL字符集的设置服务器字符集和校对规则，可以在MySQL服务启动的时候确定。可以在f配置文件中设置 如果要设置成gbk，那么可以修改内容如下:MySQLdcharacter-set-server=gbk;还有一种方式，就是在启动时指定字符集为gbk，命令如下:MySQLd character-set-server=gbk以上两种方式只是指定了字符集

20、，校验规则使用的是与其对应的默认的校验规则。6.2.3 MySQL字符集的设置（2）数据库字符集和校验规则数据库的字符集和校验规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改。需要注意的是，如果数据库中已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。设置数据库字符集的规则如下：如果指定了字符集和校对规则，则使用指定的字符集和校对规则：如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；如果

21、没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符和校对规则。6.2.3 MySQL字符集的设置要显示当前数据库字符集和校验规则可用以下两条命名分别查看:SHOW VARIABLES LIKE character_set_database SHOW VARIABLES LIKE collation_database执行结果如下：6.2.3 MySQL字符集的设置(3)表字符集和校验规则表的字符集和校对规则在创建表的时候指定，可以通过ALTER TABLE命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。设置表的字符集的规则和

22、上面基本类似：如果指定了字符集和校对规则，使用指定的字符集和校对规则；如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。6.2.3 MySQL字符集的设置要显示当前表字符集和校验规则可用以下如下命名查看:SHOW CREATE TABLE 表名;下面查询studentInfo数据库下面的exampleTable的字符集和校验规则。执行结果如下：6.2.3 MySQL字符集的设置(4)列字符集和校验规则MySQL可以定义列级别的字

23、符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况，应该说一般遇到这种情况的几率比较小，这只是MySQL提供给我们一个灵活设置的手段。列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。(5)连接字符集和校验规则本节（1）（4）描述的4种设置方式连接字符集和校对规则，确定的是数据保存的字符集和校对规则，对于实际的应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。6.2.3 MySQL字符集的设置MySQL提供了3个不同的参数：通常情况下，这3个字符集应该是相同的，才可

24、以确保用户写入的数据可以正确地读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出。通常情况下，不会单个地设置这3个参数，可以通过以下命令：SET NAMES *;来设置连接的字符集和校验规则，这个命令可以同时修改这3个参数的值。character_set_clientcharacter_set_connectioncharacter_set_results客户端的字符集连接的字符集返回结果的字符集6.2.3 MySQL字符集的设置使用这个方法修改连接的字符集和校验规则，需要应用每次连接数据库后都执行这个命令。另一个简单的办法，就是在f中设置一下语句:MySQ

25、Ldefault-character-set=gbk;这样服务启动后，所有连接默认是使用gbk字符集进行连接，而不需要在程序中再执行SET NAMES命令。创建数据库36.3 创建数据库在MySQL中，创建数据库是通过SQL语句CREATE DATABASE或CREATE SCHEMA命令来实现的。每一个数据库都有一个数据库字符集和一个数据校验规则，不能够为空。语法格式：CREATE DATABASE | SCHEMA IF NOT EXISTS db_name DEFAULT CHARACTER SET charset_nameDEFALUT COLLATE collation_name说明

26、：数据库名：在文件系统中，MySQL的数据库存储区将以目录方式表示MySQL数据库。因此，命令中的数据库名字必须符合操作系统文件夹命名规则。值得一说的是，在MySQL中不区分大小写，在一定程度上方便使用。若指定了CHARACTER SET charset_name 和COLLATE collation_name,则采用指定的字符集charset_name和校验规则collation_name，若没有指定，则会采用默认的值6.3 创建数据库【例】创建一个名为StudentInfo的数据库，一般情况下在创建之间要用IF NOT EXISTS命令先判断数据库是否不存在。 CREATE DATABAS

27、E IF NOT EXISTS studentInfo ; 执行结果如下结果显示数据库创建成功。6.3 创建数据库为了检验数据库中是否已经存在名为studentInfo的数据库，我们使用SHOW DATABASES;命令查看所有的数据库；如下所示:查询结果显示，已经存在studentInfo数据库。数据库创建成功。说明：在执行CREATE DATABASE studentInfo;后，处理结果信息显示“Query OK，1 rows affected(0.01 sec)”表示创建成功，1行受到影响，处理时间是0.00秒。6.3 创建数据库可用命令查看数据库的详细信息，语法格式如下:SHOW CREATE DATABASE 数据库名称;【例】查看studentInfo数据库的详细信息。执行结果如下：6.3 创建数据库【例】建立一个使用big5字符集，并带校验规则exampleDB的数据