第2章 MySQL的基本操作

图2-1 查看已有数据库列表
【例2.2】列出以my开头的所有数据库列表。如图2-2所示：
图2-2 列出以my开头的所有数据库列表
2.1.2 创建数据库CREATE DATABASE
句法：CREATE DATABASE db_name 功能：CREATE DATABASE用给定的名字创建一个数据库。 执行数据库创建操作的条件是：数据库名字必须是合法的，这个数据库不能是已经存在的，你 必须有足够的权限去创建它。如果数据库已经存在，则发生一个错误。 创建数据库时，MySQL服务器会在它的数据目录里创建一个与该数据库同名的子目录，这个新 目录称为数据库子目录。服务器还会在那个数据库目录里创建一个db.opt文件夹保存数据库的属 性。 在MySQL中的数据库实现成包含对应数据库中表的文件的目录。因为数据库在初始创建时没有 任何表，CREATE DATABASE语句只是在MySQL数据目录下面创建一个目录。 【例2.3】创建数据库myfirst。如图2-3所示：
图2-3 创建数据库myfirst 【例2.4】利用show databases观察数据库中的内容。如图2-4所示：
图2-4 查看当前数据库
2.1.3 删除数据库DROP DATABASE
句法：DROP DATABASE [IF EXISTS] db_name 功能：DROP DATABASE删除数据库中的所有表 和数据库。要小心地使用这个命令。如图2-5所示： 【例2.5】删除数据库myfirst。 如图2-5所示：
2.1.4 选择数据库USE DATABASE
句法：USE db_name 功能：USE 语句选择一个数据库，使其成为服务器的给定连接的缺省（当前）数据库。 必须对数据库具有某种访问权限，否则不能使用它。为了使用数据库中的表而选择该数 据库实际上不是必须的，因为可以利用db_name.tbl_name 形式来引用它的表。但是，不必指定数据库 限定词引用表要方便得多。 选择一个缺省数据库并不代表在连接的持续时间内它都必须是缺省的。可发布任意数目的USE 语句在数 据库之间进行任意地切换，只要具有使用它们的权限即可。选择一个数据库也不限制您只使用该数据库 中的表。您仍然可以通过用数据库名限定表名的方法，引用其他数据库中的表。 USE db_name语句告诉MySQL使用db_name数据库作为随后的查询的缺省数据库。数据库保持到会话结 束，或发出另外一个USE语句： mysql> USE db1; mysql> SELECT count(*) FROM mytable; # select from db1.mytable mysql> USE db2; mysql> SELECT count(*) FROM mytable; # select from db2.mytable 如果你不是用USE语句，那么上面的例子应该写成： mysql> SELECT count(*) FROM db1.mytable; mysql> SELECT count(*) FROM db2.mytable; 在服务器的连接终止时，服务器关于缺省数据库的所有记忆都消失了。即如果您再次连接到该服务器， 它不会记住以前您所选择的数据库。事实上，假定MySQL 是多线程的， 可通过一个用户处理多个连接，用户可以按任何顺序连接或断开，让服务器对缺省数据库进行记忆的想 法也是没有意义的。 【例2.6】选择数据库 myfirst。 在myfirst数据库中创建表，必须先选择该数据库，输入语句如下： mysql> USE myfirst; Database changed
2.2.3 InnoDB存储引擎
InnoDB已经开发了十余年，遵循CNU通用公开许可（GPL）发行。InnoDB已经被 一些重量级因特网公司所采用，如雅虎、Slashdot和Google，为用户操作非常大的 数据库提供了一个强大的解决方案。InnoDB给MySQL的表提供了事务、回滚、崩 溃修复能力和多版本并发控制的事务安全。InnoDB是MySQL上第一个提供外键约 束的表引擎。而且InnoDB对事务处理的能力，也是MySQL其他存储引擎所无法与 之比拟的。下面介绍InnoDB存储引擎的特点及其优缺点。 InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为 空，且值必须唯一。MySQL中规定自增列必须为主键。在插入值时，如果自动增 长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或空（NULL）， 则插入的值也为自动增长后的值；如果插入某个确定的值，且该值在前面没有出 现过，则可以直接插入。 InnoDB存储引擎中支持外键（FOREIGN KEY）。外键所在的表为子表，外键所 依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表 的某条信息时，子表也必须有相应的改变。InnoDB存储引擎中，创建的表的表结 构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和 innodb_data_file_path表空间中。 InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。 缺点是其读写效率稍差，占用的数据空间相对比较大。 InnoDB表是如下情况的理想引擎： q 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。 q 事务：InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎，这是管理敏 感数据（如金融信息和用户注册信息）的必需软件。 q 自动灾难恢复：与其他存储引擎不同，InnoDB表能够自动从灾难中恢复。虽 然MyISAM表是能在灾难后修复，但其过程要长得多。
第2 章
MySQL的基本操作
数据库是存储数据库对象的容器。MySQL数据库的管理 主要包括数据库的创建、选择当前操作的数据库、显示数据 库结构以及删除数据库等操作。表是数据库存储数据的基本 单位。一个表包含若干个字段或记录。表的操作包括创建新 表、修改表和删除表。这些操作都是数据库中最基本、也是 最重要的操作。 2.1 有关数据库的操作 2.1.1 查看数据库SHOW DATABASE 句法：SHOW DATABASES [LIKE wild] 功能：SHOW DATABASES列出在MySQL服务器主机上的 数据库。 如果使用LIKE wild部分，wild字符串可以是一个使用SQL 的“%”和“_”通配符的字符串。 【例2.1】查看已有数据库列表。如图2-1所示：
图2-5 删除数据库myfirst 请注意，不要乱用DROP DATABASE 语句，它将会删除数据库及其所有的 表。在删除了一个数据库后，该数据库就永远没有了。换句话说，不要仅为了看 看这条语句如何工作就试着执行该语句。如果管理员已经正常完成了数据库备份， 那么删除的数据库可能还可以恢复。数据库是由数据目录中的一个目录表示的。 如果在该目录中放置了一些非表的数据文件，它们是不会被DROP DATABASE 语句 删除的。因而也不删除那个数据库子目录。这就说明如果写了SHOW DATABASES语 句，尽管里面已经没有数据库表了，可那个数据库子目录却依然存在。这种情况 下，若要删除那个数据库，就必须手动删除该数据库子目录遗留的文件和子目录 本身，然后再发出DROP DATABASE语句。 DROP DATABASE返回从数据库目录被删除的文件的数目。通常，这3倍于 表的数量，因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个 “.frm”文件。
2.2.6 如何选择存储引擎
每种存储引擎都有各自的优势，不能笼统的说谁比谁更好，只有适合不适合。下 面根据其不同的特性，给出选择存储引擎的建议。 q InnoDB存储引擎：用于事务处理应用程序，具有众多特性，包括ACID事务支 持，支持外键。同时支持崩溃修复能力和并发控制。如果需要对事务的完整性要 求比较高，要求实现并发控制，那选择InnoDB存储引擎有其很大的优势。如果需 要频繁的进行更新、删除操作的数据库，也可以选择InnoDB存储引擎。因为，该 类存储引擎可以实现事务的提交（Commit）和回滚（Rollback）。 q MyISAM存储引擎：管理非事务表，它提供高速存储和检索，以及全文搜索能 力。MyISAM存储引擎插入数据快，空间和内存使用比较低。如果表主要是用于 插入新纪录和读出记录，那么选择MyISAM存储引擎能实现处理的高效率。如果 应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。 q MEMORY存储引擎：MEMORY存储引擎提供“内存中”表，MEMORY存储引 擎的所有数据都在内存中，数据的处理速度快，但安全性不高。如果需要很快的 读写速度，对数据的安全性要求较低，可以选择MEMORY存储引擎。MEMORY 存储引擎对表的大小有要求，不能建太大的表。所以，这类数据库只使用相对较 小的数据库表。 以上存储擎引的选择建议是根据不同存储引擎的特点提出的，并不是绝对的。实 际应用中还需要根据个自的实际情况进行分析。
2.2.4 MyISAM存储引擎
MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默 认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的， 它解决了ISAM的很多不足。MyISAM增加了很多有用的扩展。
2.2.5 MEMORY存储引擎
MEMORY存储引擎是MySQL中的一类特殊的存储引擎。其使用存 储在内存中的内容来创建表，而且所有数据也放在内存中。这些 特性都与InnoDB存储引擎、MyISAM存储引擎不同。下面将对 MEMORY存储引擎的文件存储形式、索引类型、存储周期和优缺 点等进行讲解。
2.2
最常用的引擎类型MySQL存引擎
存储引擎其实就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。 因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表 的类型）。在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而 MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据 自己的需要编写自己的存储引擎。
2.2.1 什么是MySQL存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存 储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额 外的速度或者功能，从而改善你的应用的整体功能。 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不 同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储 引擎，以便在选择如何存储信息、如何检索这些信息以及需要的数据结合什么性能和功能的时候为其提供最 大的灵活性。
2.2.2
查询MySQL中支持的存储引擎
使用SHOW ENGINES语句和SHOW语句可以查询MySQL中支持的存储引擎，下面分别使用这两个语句 来查询MySQL中支持的存储引擎。 （1）使用SHOW ENGINES语句查询MySQL中支持的存储引擎。其查询语句如下： SHOW ENGINES; SHOW ENGINES语句可以用“;”结束，也可以用“\g”或者“\G”结束。“\g”与“;”的作用是相同的， “\G”可以让结果显示得更加美观。使用SHOW ENGINES语句查询的查询结果如图2-6所示。
查询结果中的Engine参数指的是存储引擎的名称；Support参数指的是MySQL 是否支持该类引擎，YES表示支持；Comment参数指对该引擎的评论。 从查询结果中可以看出，MySQL支持多个存储引擎，其中InnoDB为默认存储 引擎。 （2）使用SHOW语句查询MySQL中支持的存储引擎。其查询语句如下： SHOW VARIABLES LIKE 'have%'; 使用SHOW ENGINES语句查询的结果如图2-7所示。 有些表根本不用来存储长期数据，实际上用户需要完全在服务器的RAM或特 殊的临时文件中创建和维护这些数据，以确保高性能，但这样以来，也存在很 高的不稳定风险。还有一些表只是为了简化对一组相同表的维护和访问，为同 时与所有这些表交互提供一个单一接口。另外还有其他一些特别用途的表，但 重点是：MySQL支持很多类型的表，每种类型都有自己特定的作用、优点和缺 点。MySQL还相应地提供了很多不同的存储引擎，可以以最适合于应用需求的 方式存储数据。MySQL有多个可用的存储引擎，下面主要介绍InnoDB、 MyISAM和MEMEORY三种存储引擎。