第三章 - MySQL数据库的基本操作

第三章 - MySQL数据库的基本操作

3.1 MySQL数据库概述

MySQL数据库的管理主要包括数据库的创建、打开当前操作的数据库、显示数据库结构以及删除数据库等操作。MySQL也提供图形化管理工具，这使得对数据库的操作更加简单。

MySQL数据库主要有以下特点 ：

• 可移植性好 ；
• 具有扩展性和灵活性；
• 具有强大的数据保护功能；
• 能够支持大型数据库；
• 具有超强的稳定性；
• 具有强大的查询功能。

3.1.1 MySQL数据库文件

数据库管理的主要任务包括创建、操作和支持数据库。在MySQL中，每个数据库都对应存放在一个与数据库同名的文件夹中。

MySQL数据库文件有.frm、.myd和.myi等3种文件，其中.frm是描述表结构的文件，.myd是表的数据文件，.myi是表数据文件中的索引文件。

数据库的默认存放位置是 C:\Documents and Settings\All Users\MySQL\MySQL Server 5.7\Data 。可以通过配置向导或手工配置修改数据库的默认存放位置。

3.1.2 MySQL自动建立的数据库

MySQL安装完成之后，将会在其data目录下自动创建几个必须的数据库，可以使用show databases命令来查看当前所有存在的系统数据库，如表3-1所示。

数据库名称	数据库作用
mysql	描述用户访问权限
information_schema	保存关于MySQL服务器所维护的所有其他数据库的信息。如数据库名、数据库的表、表栏的数据类型与访问权限等
performance_schema	主要用于收集数据库服务器性能参数
sakila	MySQL官方测试用的数据库
sys	sys数据库里面包含了一系列的存储过程、自定义函数以及视图，存储了许多系统的元数据信息。
world	存储当前世界上的主要城市、国家和语言信息。

3.1.3 查看数据库

成功安装数据库后，可以使用show databases命令查看MySQL服务器中的所有数据库信息。

【例3.1】使用show databases语句查看MySQL服务器中的所有数据库。

mysql> show  databases;

注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。

3.2 MySQL数据库的设计过程

数据库设计是指对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。

数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效率的运行环境。高效率的运行环境包括：数据库数据的存取效率、数据库存储空间的利用率、数据库系统运行管理的效率等都是高的。

以高校的教务管理系统来说，就需要数据库来存储学生的学籍信息、考试信息、教师信息、课程信息等。数据库技术的可以实现更加有效地管理和存取大量的数据资源，以提高人力、物力和财力的利用率和工作效率。

3.2.1 数据库设计的基本过程

一般来说，按照数据库规范化设计的方法，数据库设计可分为需求分析、概念设计、逻辑设计和物理设计4个阶段，如图所示。之后是软件开发阶段中的数据库创建和数据库运行与维护。

在实际的项目开发中，如果系统的数据关系较复杂，数据存储量较大，设计的表较多，表和表之间的关系比较复杂，就需要首先考虑规范的数据库设计，然后再进行具体的创建库、创建表的工作。

1. 需求分析

需求分析的目标是通过调查研究，了解用户的数据要求和处理要求，并按一定的格式整理形成需求说明书。需求说明书是需求分析阶段的成果，也是以后设计的依据。

2. 概念设计

目标是对需求说明书提供的所有数据 和处理要求进行抽象与综合处理，按一定的方法构造反映用户环境的数据及其相互联系的概念模型。这种概念数据模型与DBMS无关，是面向现实世界的数据模型，极易为用户所理解。实体关系(E-R)的数据库设计方法是目前最常用的方法。

3. 逻辑设计

目标是把上一阶段得到的与DBMS无关的概念数据模型转换成等价的，并为某个特定的DBMS所接受的逻辑模型所表示的概念模式，同时将概念设计阶段得到的应用视图转换成特定DBMS下的应用视图。

4. 物理设计

把逻辑设计阶段得到的逻辑数据库在物理上加以实现，其主要内容是根据DBMS提供的各种手段，设计数据的存储形式和存取路径，如文件结构、索引设计等。

3.2.2 教务管理数据库设计的规范化

数据库应用程序的性质和复杂性可以使得数据库的设计过程变化很大。而对于为成千上万的客户处理事务的数据库，数据库设计可能是长达数百页的正式文档，其中需要包含有关数据库的各种可能细节。要进行较复杂的数据库设计，必须遵守数据库设计规范化规则 (Normalization Rules)，并按照软件工程提供的规范才能进行数据库设计。

按照规范化规则设计数据库，可以将数据冗余降至最低，使得应用程序软件可以在此数据库中轻松实现强制完整性，且很少包括执行涉及4个以上表的查询。满足一定条件的关系模式称为范式(Normal Form，NF)，一个低级范式的关系模式，通过分解(投影)方法可转换成多个高一级范式的关系模式的集合，这个过程称为规范化。

数据依赖是一个关系内部属性与属性之间的一种约束关系。这种约束关系是通过属性间值的相等与否体现出来的数据间相关联系，它是现实世界属性间相互联系的抽象，是数据内在的性质，是语义的体现。

数据依赖中最重要的是函数依赖（FD）和多值依赖（MVD）。而函数依赖极为普遍地存在于现实世界中。比如描述一个学生的关系student，可以由学号、姓名、性别、电话等几个属性。由于一个学号只对应一个学生，所以一旦“学号”值确定后，学生的姓名、性别、电话等的值也就被唯一地确定了。

建立一个描述学校教务的数据库teaching，该数据库涉及的对象包括学生学号、学生姓名、学生性别、电话、课程号、课程名称和成绩等数据项。假设用一个单一的关系模式学生来表示，则该关系模式的属性集合为：

U={学生学号，学生姓名，学生性别，电话，课程号，课程名称，成绩}

考察这个关系模式发现存在以下问题：

1. 数据冗余度大：课程号和课程名称重复出现，重复次数与该班所有学生的所有课程成绩出现次数相同。

2. 更新异常：由于数据冗余，当更新数据库中的数据时，系统要付出很大的代价来维护数据库的完整性，否则会面临数据不一致的危险。

3. 插入异常：如果一门课程刚刚开设，尚无学生选课记录，则系统无法把该课程信息存入数据库。

4. 删除异常：如果某一级的学生全部毕业了，在删除该班学生信息的同时，把这个课程的信息也一起删除掉了。

第1范式（1NF）

第1范式的目标是确保每列的原子性。如果每列都是不可再分的最小数据单元，则满足第1范式（1NF）。

现以学生表为例，设计学生表结构如下：

学生（学生学号，学生姓名，学生性别，电话，课程号，课程名称，成绩）。

以上学生表中各项都符合1NF条件。

第2范式（2NF）

第2范式在第1范式的基础上，要求确保表中的每列都和码相关，即每一个非主属性都要完全函数依赖于码。

分析学生关系模式，码应该为（学生学号，课程号），很明显，在该关系模式中，学生姓名、电话名称、学生性别、电话等只完全函数依赖于学生学号，因此对码（学生学号，课程号）是部分函数依赖，因此该关系模式不满足第2范式。

如果把学生相关的属性单独拿出来，形成关系模式：

学生（学生学号，学生姓名，学生性别，电话，课程号，课程名称）

选修（学生学号，课程号，成绩）

则以上两个关系模式都符合第2范式。

第3范式（3NF）

第3范式是在第二范式的基础上，要求确保表中的每列都和码直接相关，而不是间接相关。如果一个关系满足2NF，并且除了码以外的其他列都不相互依赖，则满足第3范式（3NF）。

为了理解第3范式，需要根据Armstrong公理之一定义传递函数依赖，假设A、B和C是关系模式R的三个属性，如果A -> B且B -> C，则从这些函数依赖中，可以得出A -> C。

考察上述分解后的关系模式：

学生（学生学号，学生姓名，学生性别，电话，课程号，课程名称）

可以得出课程名称 -> 课程号，而课程号 -> 学生学号（假设学生选修该课程），因此存在课程名称 -> 学生学号的传递函数依赖，因此该关系模式不符合第3范式。

如果把学生关系模式中的课程相关的属性单独拿出来，形成关系模式：

学生（学生学号，学生姓名，学生性别，电话）
课程（课程编号，课程名称）

分析学生实体和课程实体之间的联系，并添加一些必要的属性，可以得出学生实体和课程实体之间是多对多（m:n）的联系。

绘制学生实体和课程实体的“选修”关系局部E-R图，如图所示。

如果再加上教师实体，并针对本系统的特点修改，则教务管理系统的E-R图如图所示。

3.3 用户数据库的创建和管理

3.3.1 创建数据库

1、数据库命名规则

在创建数据库时，数据库命名有以下几项规则：

- 不能与其他数据库重名，否则将发生错误。

- 名称可以由任意字母、阿拉伯数字、下划线（_）和“$”组成，可以使用上述的任意字符开头，但不能使用单独的数字，否则会造成它与数值相混淆。

- 名称最长可为64个字符，而别名最多可长达256个字符

- 不能使用MySQL关键字作为数据库名、表名。

在默认情况下，Windows下数据库名、表名的大小写是不敏感的，而在Linux下数据库名、表名的大小写是敏感的。如果为了便于数据库在平台间进行移植，可以采用小写来定义数据库名和表名。

2、创建数据库语法结构

使用 create database 或 create schema 命令可以创建数据库。其语法结构如下。

create {database|schema}[if not exists]databasename
[default]character set charset_name
|[default]collate collation_name;

创建数据库

创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据。这是进行表操作的基础，也是进行数据库管理的基础。MySQL中，创建数据库是通过SQL语句create database实现的。

【例3.2】通过create database语句创建一个名称为mysqltest的数据库。

mysql> create  database if not exists mysqltest;

【例3.3】创建教务管理数据库teaching，并指定字符集为gb2312，校对原则为gb2312_chinese_ci。

mysql> create database teaching
-> default character set gb2312
-> default collate gb2312_chinese_ci;

3、查看新创建的数据库

成功创建数据库后，可以使用show databases命令查看数据库，也可以在指定路径（或数据库的默认存放位置）下查看数据库。

mysql> show databases;

3.3.2 管理数据库

1、打开数据库

数据库创建后，若要操作一个数据库，还需要使其成为当前的数据库，即打开数据库。可以使用USE语句打开一个数据库，使其成为当前默认数据库。

例如， 选择名称为mysqltest的数据库，设置其为当前默认的数据库。

命令和运行结果如下：

  mysql> use mysqltest;
             Database changed

2、修改数据库

数据库创建后，如果需要，可以修改数据库的参数。

修改数据库的语法格式如下：

alter {database | schema} [db_name]
[default] character set charset_name
|[default] collate collation_name；

【例3.4】将mysqltest库修改字符集为gb2312，校对原则为gb2312_chinese_ci

命令和运行结果如下：

mysql> alter database mysqltest
         -> default character set gb2312
          -> collate gb2312_chinese_ci;
Query OK, 1 row affected (0.00 sec)

3、显示数据库结构

如果查看已数据库的相关信息，例如MySQL版本id号、默认字符集等信息，使用MySQL命令实现。

【例3.5】显示数据库teaching的结构信息。

命令和运行结果如下：

mysql> show create database teaching;

4、删除数据库

删除数据库是指在数据库系统中删除已经存在的数据库。删除数据库之后，原来分配的空间将被收回。

删除数据库语法格式如下：

drop database  [if exists]  db_name 

例如，删除mysqltest库命令如下：

mysql> drop database  mysqltest; 

需要注意的是，删除数据库会删除该数据库中所有的表和所有数据。因此，删除数据库前最好存有备份。

3.4 利用MySQL workbench管理数据库

3.4.1 使用MySQL Workbench创建数据库

（1）从“开始” -> “所有程序”中找到MySQL文件夹，执行MySQL Workbench 6.2 CE命令，进入MySQL Workbench主界面，单击MySQL57连接。

进入MySQL Workbench数据库操作的主界面，如图3-6所示。

（2）在数据库操作主界面中，单击如图所示的提示为Create a new schema in the connected server的创建数据库按钮，Schema在这里就是数据库的意思。

（3）进入如图所示设置数据库参数界面。在输入数据库名mysqltest，选择字符集和排序规则。然后，单击Apply按钮。

（4）进入如图所示的创建数据库脚本显示对话框中，再单击Apply按钮，即可完成数据库的创建mysqltest。

（5）查看数据库。在数据库操作界面的schemas区域，就看到刚才创建的数据库mysqltest，还有前面创建的数据库teaching以及MySQL中的系统数据库了。如图所示。

3.4.2 利用MySQL Workbench管理数据库

1、修改数据库参数

（1）在数据库操作界面的schemas区域，右击要修改的数据库mysqltest，如图所示。执行弹出菜单中的Alter schema命令。

（2）进入如图3-12所示的修改数据库对话框，可以对数据库mysqltest进行修改。最后单击Apply按钮即可完成数据库的修改。

2、删除数据库

（1）在数据库操作界面的schemas区域，右击要修改的数据库mysqltest，如图所示。

执行弹出菜单中的drop schema命令。进入删除数据库对话框，如图所示。

（2）选择Review SQL 选项，进入如图所示的复审对话框。单击Cancel按钮，即可取消数据库删除的过程，单击Execute按钮，即可删除该数据库。

3.5 MySQL存储引擎

MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎，MySQL的核心就是存储引擎。

数据库的存储引擎决定了表在计算机中的存储方式。存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎简而言之就是指表的类型。

MySQL 5.7支持的存储引擎有：InnoDB、MRG_MyISAM、Memory、BLACKHOLE、MyISAM、CSV、Archive、Federated和PERFORMANCE schema等9种。不同存储引擎都有各自的特点，以适应不同的需求。MySQL常用存储引擎如表3-2所示。

3.5.1 查看数据库存储引擎

MySQL的存储引擎是一种插入式的存储引擎概念。这决定了MySQL数据库中的表可以用不同的方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

MySQL的默认存储引擎是InnoDB，如果想设置其他存储引擎，可以使用如下MySQL命令：

set  default_storage_engine=MyISAM;

例如，执行“show engines;”，即可查看当前MySQL服务实例支持的存储引擎。命令和结果如图3-16所示。

3.5.2 常用存储引擎介绍

1、存储引擎InnoDB

MySQL 5.7选择InnoDB作为默认存储引擎。InnoDB是事务型数据库的首选引擎，是具有提交、回滚和崩溃恢复能力的事务安全存储引擎，支持行锁定和外键约束。

InnoDB 是 MySQL上第一个提供外键约束的表引擎，而且InnoDB对事务处理的能力，也是MySQL其他存储引擎所无法与之比拟的。相对MEMORY来说，写处理能力差些，且会占用较多磁盘空间以保留数据和索引。

InnoDB存储引擎中支持自动增长列auto_increment。自动增长列的值不能为空，且值必须惟一。MySQL中规定自增列必须为主键。在插入值时，如果自动增长列不输入值，则插入的值为自动增长后的值。如果输入的值为0或者空（null），则插入的值也为自动增长后的值。如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。

InnoDB存储引擎的除了支持外键（Foreign Key）和事务（Transaction）外还始支持全文检索。如果表需要执行大量的添加、删除、修改数据的操作，出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。

2、存储引擎MyISAM

MyISAM存储引擎曾是MySQL的默认存储引擎。MyISAM存储引擎不支持事务、外键约束，但访问速度快，对事务完整性不要求，适合于以SELECT/INSERT为主的表。

现在的MyISAM增加了很多有用的扩展。MyISAM存储引擎的表存储成三个文件。文件的名字与表名相同。扩展名包括frm、myd 和myi。其中，frm为扩展名的文件存储表的结构；myd为扩展名的文件存储数据，其是mydata的缩写；myi为扩展名的文件存储索引，其是myindex的缩写。

MyISAM存储引擎的特点：

• 具有检查和修复表的大多数工具。
• MyISAM表可以被压缩。
• MyISAM表最早支持全文索引。
• MyISAM表不支持事务。
• MyISAM表不支持外键（Foreign Key）。

如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

3、存储引擎MEMORY

MEMORY存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。

每个MEMORY表可以放置数据量的大小受max_heap_table_size系统变量的约束，初始值为16MB，可按需求增大。此外，在定义MEMORY表时可通过max_rows子句定义表的最大行数。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中存储表的结构。而其数据文件是存储在内存中。这样有利于对数据的快速的处理，提高整个表的处理效率。

该存储引擎主要用于那些内容稳定的表，或者作为统计操作的中间表。对于该类表需要注意的是，因为数据并没有实际写入磁盘，一旦重启，则会丢失。

注意：服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了，可以释放这些内存，甚至可以删除不需要的表。

3.5.3 如何选择存储引擎

对于存储引擎的选择，在实际工作中，选择一个合适的存储引擎是一个很复杂的问题。每种存储引擎都有各自的优势，可以根据各种存储引擎的特点进行对比，给出不同情况下选择存储引擎的建议。

MySQL中提到了存储引擎的概念，它是MySQL的一个特性，可简单理解为后面要介绍的表类型。每一个表都有一个存储引擎，可在创建时指定，也可以使用alter table语句修改，都是通过engine关键字设置的。

3.6 图形化管理工具navicat

1.连接MySQL服务器

Navicat for MySQL是一个桌面版MySQL数据库管理和开发工具，和微软SQLServer的管理器很像，易学易用，很受大家的欢迎。

在上图窗口输入连接名（任取），在“密码(p)：”输入密码，单击“确定”按钮，登录到数据库服务器中。

2.创建数据库

如果要创建数据库，选中已建立连接的连接名，按鼠标右键，在弹出的快捷菜单中选中“新建数据库”菜单项，出现创建新数据库对话框，在“输入数据库名”文本框中输入新建数据库的名称，如果新建数据库采用服务器默认的字符集和校对规则，则直接按“确定”按钮。如果在创建数据库要使用特定的字符集和校对规则，则分别点击字符集和校对下拉框指定需要的字符集和校对规则后按“确定”按钮。点击“确定”按钮后，新的数据库就已创建。

3.访问数据库

如果要对数据库进行维护，在“连接”列表框中，双击要维护的数据库名称，此时窗口右边列表框中出现所选数据库已经建立的数据表文件，若按鼠标右键，弹出数据库操作快捷菜单，可以实现数据维护的相关操作。

4.创建数据库表

双击要创建表的数据库名称，右边为数据库表管理窗口，点击工具栏中的“新建表”按钮，出现新建表窗口，在“栏位”框中依次输入表的字段定义，“字段名”、“类型名”、“长度”、“小数点”后位数、“是否为空”等定义，在对话框下半框中还可输入对应字段的“默认值”、“注释”等信息。数据库表定义完成后，按工具栏中“保存”按钮，在“输入表名”文本框中输入新建表的名称后按“确定”按钮，新的数据库表就已创建

5.修改表结构

如果要对表的结构进行修改，在选择要修改的表，点击工具栏中的“设计表”按钮，可以修改表结构的各项定义