第3章数据库基本操作.

第3章数据库基本操作本章重点掌握如何通过语句查看数据库存储引擎了解一些常见的数据库存储引擎熟悉MyISAM、MEMORY和InnoDB存储引擎的适用情况掌握MySQL中创建数据库的两种方式掌握查看数据库时的SHOW语句熟悉如何修改数据库的字符集和校对规则掌握删除数据库时执行的语句掌握选择数据库时执行的语句，并且查看当前数据库表主要内容MySQL存储引擎3.1创建数据库3.2查看数据库3.3修改数据库3.4删除数据库3.5数据库的其他操作3.6实战训练3.73.1MySQL存储引擎MySQL数据库是开发源代码的关系型数据库，它可以提供对SQL语言、子查询、存储过程、触发器、视图、索引、事务、锁、以及影像复制等功能的支持。同Oracle和SQLServer等大型数据库一样，MySQL也是C/S系统并且是单进程多线程架构的数据库。3.1.1查看数据库存储引擎简单来说，存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。可以查看当前MySQL数据库的存储引擎。查询方法非常简单，语法如下：上述语句可以使用分号“;”结束，也可以使用“\g”或者“\G”结束。其中，“\g”的作用与分号“;”作用相同，而“\G”可以让结果显示得更加美观。SHOWENGINES;3.1.1查看数据库存储引擎【示例1】登录MySQL控制台成功后执行SHOWENGIENS\G语句并查看结果。输出结果如下：mysqlSHOWENGINES\G***************************1.row***************************Engine:FEDERATEDSupport:NOComment:FederatedMySQLstorageengineTransactions:NULLXA:NULLSavepoints:NULL***************************2.row***************************Engine:MRG_MYISAMSupport:YESComment:CollectionofidenticalMyISAMtablesTransactions:NOXA:NOSavepoints:NO***************************3.row***************************Engine:MyISAMSupport:YESComment:MyISAMstorageengineTransactions:NOXA:NOSavepoints:NO3.1.1查看数据库存储引擎**************************4.row***************************Engine:BLACKHOLESupport:YESComment:/dev/nullstorageengine(anythingyouwritetoitdisappears)Transactions:NOXA:NOSavepoints:NO***************************5.row***************************Engine:CSVSupport:YESComment:CSVstorageengineTransactions:NOXA:NOSavepoints:NO***************************6.row***************************Engine:MEMORYSupport:YESComment:Hashbased,storedinmemory,usefulfortemporarytablesTransactions:NOXA:NOSavepoints:NO***************************7.row***************************Engine:ARCHIVESupport:YESComment:ArchivestorageengineTransactions:NOXA:NOSavepoints:NO***************************8.row***************************3.1.1查看数据库存储引擎Engine:InnoDBSupport:DEFAULTComment:Supportstransactions,row-levellocking,andforeignkeysTransactions:YESXA:YESSavepoints:YES***************************9.row***************************Engine:PERFORMANCE_SCHEMASupport:YESComment:PerformanceSchemaTransactions:NOXA:NOSavepoints:NO9rowsinset(0.03sec)3.1.1查看数据库存储引擎3.1.1查看数据库存储引擎InnoDB是默认的数据库存储引擎。除此之外，当前版本的MySQL数据库还支持MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY（HEAP）、ARCHIVE和PERFORMANCE_SCHEMA存储引擎。另外，当前查询出9条记录，每条记录主要内容相同，如下对这些查询结果进行了说明。Engine数据库存储引擎名称。Support表示MySQL是否支持该类引擎，YES表示支持，NO表示不支持。Comment表示对该引擎的评论信息。Transactions表示是否支持事务处理，YES表示支持，NO表示不支持。XA表示是否分布式交易处理的XA规范，YES表示支持，NO表示不支持。Savepoints表示是否支持保存点，以便事务回滚到保存点，YES表示支持，NO表示不支持。3.1.1查看数据库存储引擎备注：分布式事务处理（DistributedTransactionProcessing，DTP）是指一个事务可能涉及多个数据库操作，分布式事务处理的关键是必须有一种方法可以知道事务在任何地方所做的所有动作，提交或回滚事务的决定必须产生统一的结果（全部提交或全部回滚）。事务（Transaction）：又称之为交易，指一个程序或程序段，在一个或多个资源如数据库或文件上为完成某些功能的执行过程的集合，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。XA就是X/Open组织针对DTP定义的交易中间件与数据库之间的接口规范（即接口函数），交易中间件用它来通知数据库事务的开始、结束以及提交、回滚等。XA接口函数由数据库厂商提供。3.1.1查看数据库存储引擎通常把一个数据库内部的事务处理，如对多个表的操作，作为本地事务看待。数据库的事务处理对象是本地事务，而分布式事务处理的对象是全局事务。所谓全局事务，是指分布式事务处理环境中，多个数据库可能需要共同完成一个工作，这个工作即是一个全局事务，例如，一个事务中可能更新几个不同的数据库。对数据库的操作发生在系统的各处但必须全部被提交或回滚。此时一个数据库对自己内部所做操作的提交不仅依赖本身操作是否成功，还要依赖与全局事务相关的其它数据库的操作是否成功，如果任一数据库的任一操作失败，则参与此事务的所有数据库所做的所有操作都必须回滚。3.1.1查看数据库存储引擎【示例2】5.6.13版本的MySQL数据库默认安装时使用InnoDB存储引擎，如果使用者不确定当前数据库的默认存储引擎，可以通过输入语句进行查看。语句如下：SHOWVARIABLESLIKE'storage_engine';控制台执行上述语句输出的结果如下：mysqlSHOWVARIABLESLIKE'storage_engine';+------------------------+-------------+|Variable_name|Value|+------------------------+-------------+|storage_engine|InnoDB|+------------------------+-------------+1rowinset(0.78sec)3.1.4存储引擎InnoDBInnoDB是当前MySQL数据库版本的默认存储引擎，它与传统的ISAM和MyISAM相比，其最大特色就是支持数据库事务正确执行的ACID原则，即：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。目前，InnoDB采用双轨制授权，一个是GPL授权，另一个则是专有软件授权。3.1.4存储引擎InnoDB原子性整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。一致性在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。3.1.4存储引擎InnoDB隔离性隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。持久性在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。InnoDB的主要特点：（1）InnoDB是为处理巨大数据量时的最大性能设计；（2）InnoDB给MySQL提供了具有事务、回滚和崩溃修复能力、多版本并发控制的事务安全型表；（3）InnoDB提供了行级锁，提供与Oracle类似的不加锁读取；（4）在技术上，InnoDB是一套放在MySQL后台的完整数据库系统，InnoDB在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。3.1.4存储引擎InnoDB3.1.4存储引擎InnoDBInnoDB体系结构如下：后台线程：1、后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据；2、另外，将以修改的数据文件刷新到磁盘文件；3、同时，保证在数据库发生异常的情况下，InnoDB能恢复到正常运行状态。3.1.4存储引擎InnoDB内存池：InnoDB有多个内存块，这些内存块组成了一个大的内存池。这些内存块包括有：缓冲池（innodb_buffer_pool）和日志缓冲(log_buffer)以及额外内存池（innodb_addtional_mem_pool_size）。3.1.6如何选择存储引擎下面分别介绍了MyISAM、MEMORY和InnoDB这3种存储引擎的特性对比，根据其不同的特性，给出了一些选择的建议。MyISAM存储引擎这种存储引擎提供了高速的存储与检索和全文搜索能力。该存储引擎插入数据快，但是空间和内存的使用效率较低。如果表主要适用于插入新纪录和读出记录，那么选择MyISAM存储引擎可以实现处理的高效率。3.1.6如何选择存储引擎MEMORY存储引擎这种存储引擎提供“内存中”表，该存储引擎的所有数据都存储在内存中，数据的处理速度很快但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择这种存储引擎。MEMORY存储引擎对表的大小有要求，不能建议太大的表，因此，这类数据库只使用与相对较小的数据库表。3.1.6如何选择存储引擎InnoDB存储引擎它主要用于事务处理应用程序，支持外键，同时还支持崩溃修复能力和并发控制。如果对事务的完整性要求比较高，要求实现并发通知，那么选择InnoDB存储引擎比较有优势。如果需要频繁的进行更新和删除操作，也可以选择该存储引擎，因为该存储引擎可以实现事务的提交和回滚。3.2创