项目二 数据定义（课件）-《MySQL数据库》同步教学（电子工业版）

项目二 数据定义 任务1 创建和管理数据库 任务2 创建和管理表 任务3 数据记录 任务1 创建和管理数据库 任务引入 通过上一个任务的学习，小白已经知道了数据库设计步骤，并设计好了数据库。那么怎么才能通过MySQL创建和删除数据库？默认的系统数据库有哪些？ 知识准备 一、查看系统数据库 MySQL数据库包含系统数据库和用户数据库。在登录MySQL后，对数据库进行管理时，先了解服务器中包含的数据库很有必要。 查看数据库的语法如下： SHOW DATABASES 案例——查看当前服务器的所有数据库 在“命令提示符”窗口中执行以下语句： mysql> SHOW DATABASES; 运行结果如图所示。 1. information_schema：information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。 2. mysql：mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。 3. performance_schema：主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。 4. sys：这个库是通过视图的形式把information_schema和performance_schema结合起来，查询出更加令人容易理解的数据。 快捷菜单 ““命令提示符”属性“对话框 二、创建数据库 MySQL安裝完成后,要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块存储数据的空间。 案例——创建数据库library 在上一个项目中对图书管理系统进行分析以及数据库设计，下面开始创建图书管理系统（library）的数据库，该数据库包括书籍类别表、读者信息表、书籍信息表、借阅信息表、还书信息表和罚款信息表。这里使用默认的字符集和默认排序规则创建library数据库。 运行结果如图所示。 案例——创建数据库library1 创建字符集为gbk的数据库library1 运行结果如图所示。 案例——判定library1数据库是否存在，如果不存在则建立此数据库。 运行结果如图所示。 三、管理数据库 1.查看数据库 为了验证数据库系统中是否创建了数据库，需要査看数据库。 案例——查看library和library1数据库的信息。 运行结果如图所示。 2.修改数据库 在建立数据库后，可以根据需要修改数据库的字符集或排序顺序。 案例——修改数据库 修改数据库library1的字符集为utf8mb4，并查看修改后的数据库信息。 运行结果如图所示。 3.删除数据库 如果建立的表不再使用，可以将其删除。 案例——删除数据库library1 运行结果如图所示。 四、数据库存储引擎 数据库存储引擎是数据库底层软件组成，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。 MySQL的核心就是存储引擎。 执行上述语法后查看数据库默认使用的引擎，如图所示。 下面介绍其中常用的引擎： 1. MEMORY存储引擎 MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。 2. InnoDB存储引擎 InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键。 3. MyISAM存储引擎 MyISAM基于ISAM存储引擎，并对其进行扩展。 MyISAM主要特性： 被大文件系统和操作系统支持。 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，若下一个块被删除，就扩展到下一块自动完成。 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16。 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上。 BLOB和TEXT列可以被索引。 NULL被允许在索引的列中，这个值占每个键的0~1个字节。 任务2 创建和管理表 任务引入 小白通过上一个任务的学习创建了一个关于图书管理的数据库，这个数据库中包含了借书证号、读者姓名、书名、图书出版日期、出版社、借书日期和还书日期等信息，他对数据库进行设计将这些信息拆分成了几个表。那么，怎么建立表呢？怎么修改和删除表？ 知识准备 一、数据元素 数据元素是数据的基本单位，在计算机程序中通常作为一个整体进行考虑和处理。有时，一个数据元素可由若干个数据项组成，例如，一本书的书目信息为一个数据元素，而书目信息的每一项（如书名 作者名等)为一个数据项。 （一）数据类型 MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。 1.数值类型 MySQL 支持所有标准 SQL 数值数据类型。 数值类型 2.日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。具体参见表所示。 3.字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。具体参见表所示。 （二）数据表的构成 表是数据存储的最常见和最简单的形式。它由一组数据记录组成，数据库中的数据是以表为单位进行组织的。一个表是一组相关的按行排列的数据；每个表中都含有相同类型的信息。 1.记录 表中的每一行称为一个记录，它由若干个字段组成。一个表中可以有若干的记录。 2.字段 也称域。表中的每一列称为一个字段。 3.表名 表名要确保其唯一性，表的名称要与用途相符，简略、直观、见名知意。 （三）完整性约束 完整性约束用于确定关系型数据库中数据的准确性和一致性。 1.主键约束 主键是表里一个或多个用于实现记录唯一性的字段。 2.唯一性约束 唯一性约束要求表里某个字段的值在每条记录里都是唯一的，这一点与主键类似。 3.外键约束 外键是子表里的一个字段，引用父表里的主键。 4.NOT NULL约束 在数据表的每一个字段都会使用关键字NULL和NOT NULL。 二、创建数据表 创建MySQL数据表需要以下信息： 表名 表字段名 定义每个表字段 创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。 案例——创建book_sort表 已经创建好library数据库，但是需要在数据库中添加各种表的结构，这里先创建书籍类型（book_sort）的结构表。根据表在MySQL中创建book_sort表。 运行结果如图所示。 案例——创建reader表 根据表在MySQL中创建读者信息（reader）表。 运行结果如图所示。 案例——创建book表 根据表在MySQL中创建书籍信息（book）表，其中book_id为主键，sort_id为外主键与book_sort表中的主键sort_id关联。 运行结果如图所示。 案例——创建borrow_record表 根据表在MySQL中创建借阅信息（borrow_record）表，其中book_id为主键与book表中book_id关联，reader_id为外键与reader表中的主键reader_id关联。 运行结果如图所示。 案例——创建return_record表 根据表在MySQL中创建还书信息（return_record）表，其中book_id为主键与book表中book_id关联，reader_id为外键与reader表中的主键reader_id关联。 运行结果如图所示。 案例——创建fee表 根据表在MySQL中创建罚款信息（fee）表，其中reader_id为主键与reader表中reader_id关联，book_id为外键与book表中的主键book_id关联。 运行结果如图所示。 三、管理数据表 1.查看数据表 案例——查看library数据库的所有表以及reader表的结构。 运行结果如图所示。 2.修改数据表 MySQL 使用 ALTER TABLE 语句修改表。 案例——增加remark列。 在已有的reader表中增加remark列，数据类型为varchar(30)。 运行结果如图所示。 案例——修改数据类型。 在book_sort表中修改sort_name的数据类型为varchar(8) 运行结果如图所示。 案例——修改列名。 在borrow_record表中将return_date列名更改为borrow_date 运行结果如图所示。 案例——删除表的外键。 删除book表的外键，需要删除外键标识，那就需要先查看book表的外键标识，再删除外键。 （1）查看book表的外键标示 运行结果如图所示。 （2）删除外键。 运行结果如图所示。 案例——修改外键的字段类型。 在fee表中修改book_id的字段类型为char(20)，因为此表中的book_id是外键，如果要对其修改必须先删除外键约束关系，修改后再重新修改外键的字段类型。 （1）查看fee表的外键标示 运行结果如图所示。 （2）删除book_id字段的外键。 运行结果如图所示。 （3）更改book_id字段类型 运行结果如图所示。 （4）添加book_id外键约束 运行结果如图所示。 案例——给book表添加外键。 给book表的sort_id添加外键 运行结果如图所示。 案例——给return_record表添加外键。 给return_record表的book_id和reader_id添加外键 运行结果如图所示。 案例——删除列。 删除reader表的remark列 运行结果如图所示。 3.删除数据表 可以删除一个或多个表。 案例——删除表fee1。 运行结果如图所示。 任务3 数据记录 任务引入 小白通过上一个任务的学习创建了图书管理系统中的书籍类型、读者信息、书籍信息、读者借书记录和读者还书记录等表结构，但是表中还没有相关的数据记录。那么，怎么给表添加数据记录呢？怎么修改和删除表中的记录呢？ 知识准备 一、添加数据 在MySQL中使用 INSERT INTO语句向数据库已有表中插入一行或者多行数据数据。 案例——给book_sort表添加数据 已经创建好library数据库和数据库中各个表的结构，根据表结构添加数据，使其数据库更加完整。程序中数据信息不是唯一，读者可以根据需要添加和删减数据信息，也可以根据需要输入自己已有的数据信息。 参照表，给book_sort表添加数据。 运行结果如图所示。 案例——给reader表添加数据 参照表，给reader表添加数据。 运行结果如图所示。 案例——给book表添加数据 参照表，给book表添加数据。 运行结果如图所示。 borrow_record表数据 二、管理数据 1.修改数据 可以使用 UPDATE 语句来修改、更新一个或多个表的数据。 案例——更新单个字段数据。 在 fee 表中，更新所有行的book_fee字段值为 1.0。 运行结果如图所示。 修改后的表 案例——更改多个字段数据。 在 book_sort 表中，更新sort_id值为06的记录，将sort_name字段值改为科技。 运行结果如图所示。 案例——清空某个字段中的所有数据。 在 fee 表中，清空book_fee字段中的所有数据。 运行结果如图所示。 2.删除数据记录 使用 DELETE 语句来删除表的一行或者多行数据。 案例——删除表中的某个字段数据。 为了保证数据的完整性，先复制一个表，再删除其中的数据。删除book_sort1表中sort_id为03的数据。 运行结果如图所示。 案例——删除表中所有数据。 为了保证数据的完整性，先复制一个表，再删除其中的数据。删除表book_sort1中的数据。 运行结果如图所示。 3.清空表记录 项目总结 习题 1. 常用的引擎有哪些？各有什么功能？ 2. 常见的数据类型有几种？ 3. 完整性约束有哪些？各有什么作用？ 项目实战 实战一 创建factory数据库 某工厂需要建立一个数据库管理系统，该工厂包括多个部门，一个部门有多个工人，每个工人每个月都有工资。通过该数据库按照一定的查询条件可以查询到工人的基本信息，能对工人工资进行管理等。 在“命令提示符”窗口中执行以下语句： mysql> CREATE DATABASE factory; 运行结果如图所示。 实战二 创建factory数据库中各个表 一个工厂包括多个部门，每个部门有部门号和部门名称，即部门信息（depart）表。一个部门有多个工人，每个工人有职工号，姓名，出生日期、性别、是否是党员、工作时间等，即工人信息（worker）表。每个工人每个月都有工资，即工资信息（salary）表。 （1）部门表（depart），其结构为：部门号（dno），类型为int；部门名（dname），类型为char(10)。其中，“部门号”为主键。 运行结果如图所示。 （2）职工表（worker），其结构为：职工号（wno），类型为int；姓名（wname），类型为char(8)；性别（wsex），类型为char(2)；出生日期（wbirthday），类型为datetime；是否为党员（CCP），类型为bit；参加工作时间（workday），类型为datetime；部门号（dno），类型为int。其中“职工号”为主键。 运行结果如图所示。 （3）职工工资表（salary），其结构为：职工号（wno），类型为int；姓名（wname），类型为char(8)；发工资日期（sday），类型为datetime；工资（salary），类型为decimal(6,1)。其中，“职工号”和“日期”为主键。 运行结果如图所示。 实战三 给factory数据库中的表添加数据 根据上个实战创建的表结构，分别往工人信息（worker）表、部门信息（depart）表和工资信息（salary）表中添加数据，使整个factory数据库完整。 （1）参照表，给depart表添加数据。 运行结果如图所示。 （2）修改worker表中的数据类型。 运行结果如图所示。 （3）参照表，给worker表添加数据。 运行结果如图所示。 （4）参照表，给salary表添加数据。 运行结果如图所示。 实战四 更改表中的数据 在 book_sort 表中，更新wno值为10的记录，将sday字段值改为2021-12-04。 在“命令提示符”窗口中执行以下语句： mysql>USE factory; mysql> UPDATE salary SET sday='2021-12-04' WHERE wno=10; 运行结果如图所示。 $$