MySQL数据库

1 数据库简介

1.1 数据库管理系统

  数据库管理系统（DBMS）是一种用于管理数据库的软件系统，提供创建、读取、更新和删除（CRUD）数据库中数据的方法。DBMS的主要任务是对数据进行有效和安全的管理。

  DBMS允许用户定义和创建数据库，定义表和它们的关系，定义表中数据的约束和规则，以及查询和更新数据等。它还提供了一种访问控制机制，以确保只有授权用户才能访问数据库。DBMS还提供了各种性能优化机制，如索引、缓存、查询优化等，以提高数据库的性能。

  常见的DBMS包括：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、MongoDB等。

1.2 什么是SQL

  SQL是Structured Query Language（结构化查询语言）的缩写，是用于操作关系型数据库的标准化语言。SQL是一种声明式语言，它的主要任务是定义和操作数据库中的数据。

  SQL被广泛应用于访问和操作关系型数据库中的数据。通过SQL，用户可以进行各种操作，例如查询、插入、更新和删除数据，定义和修改数据库表结构和约束，以及授权和管理用户等。

  SQL标准分为几个不同的部分，包括**数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和数据查询语言（DQL）**等。其中，DDL用于创建、修改和删除数据库对象，例如表、视图、索引等；DML用于插入、更新和删除数据；DCL用于授权和管理用户访问数据库的权限；DQL用于查询数据。

  不同的关系型数据库管理系统（RDBMS）实现SQL标准的程度不同，有些数据库可能会支持特定的扩展和功能。但是，大多数RDBMS都支持SQL标准，使得用户可以使用相同的语言来操作不同的数据库。

1.3 数据库模型

  数据库模型是描述数据之间关系的概念性框架。主要有以下几种：

  1.层次模型：数据按照树形结构进行组织，即每个数据记录只有一个父节点，但可以有多个子节点。优点是检索速度快，缺点是不灵活。

  2.网状模型：数据以网络结构组织，每个数据记录可以有多个父节点和子节点，优点是能够表达复杂的数据关系，缺点是不易维护。

  3.关系模型：数据按照表格形式组织，每个数据记录是一行，每个数据属性是一列。表格之间通过键值关联。关系模型是目前应用最广泛的数据库模型，因为它的简单性、灵活性和易用性。

  4.面向对象模型：数据被组织为对象，每个对象有其属性和方法。优点是能够表达现实世界中的对象，缺点是查询语言相对复杂。

  5.XML模型：数据被组织为XML（可扩展标记语言）文档，优点是具有很好的可读性和可扩展性，缺点是性能较低。

  6.NoSQL模型：NoSQL是一类非关系型数据库模型，采用不同于传统关系型数据库的数据存储和查询方式。NoSQL模型通常用于大数据和分布式系统中，优点是处理海量数据和高并发访问的能力强，缺点是数据一致性较

  MySQL属于关系型数据库管理系统（RDBMS），采用的是关系模型。关系模型是目前应用最广泛的数据库模型，数据按照表格形式组织，每个数据记录是一行，每个数据属性是一列。表格之间通过键值关联。MySQL是一种开源的RDBMS，由瑞典MySQL AB公司开发，现在由Oracle公司维护和支持。MySQL支持SQL标准，因此能够与大多数SQL兼容的应用程序集成，是一个非常流行的数据库系统。

1.4 设计数据库

  设计一个数据库需要考虑多个方面，包括以下几个步骤：

  1.确定数据库需求：首先要明确数据库要解决的问题，确定需要存储的数据类型和数据量，以及数据库应用程序的功能和性能需求。

  2.设计数据库结构：设计数据库的结构包括确定需要存储的数据实体、数据属性、关系以及数据约束等。这一步需要使用数据库建模工具进行数据建模，例如使用E-R图或UML建模工具，将实体、属性、关系和约束等以图形化的方式表示出来。

  3.选择合适的数据库管理系统：根据应用程序的需求，选择合适的数据库管理系统，例如关系型数据库（如MySQL、Oracle、SQL Server等）、面向对象数据库（如MongoDB、CouchDB等）或键值数据库（如Redis、Memcached等）等。

  4.创建数据库表和字段：根据设计好的数据库结构，创建数据库表和字段。这一步需要确定表名、字段名、数据类型、长度、默认值、约束和索引等。

  5.设计数据库安全和备份策略：设计数据库安全和备份策略包括设计用户和角色、定义权限、实施数据备份和恢复策略等。这一步需要考虑数据保护、数据完整性、数据安全等方面。

  6.编写应用程序：设计好数据库结构之后，需要编写应用程序来与数据库进行交互。在应用程序中，需要实现对数据库的查询、插入、更新、删除等操作，并处理异常情况。

  7.测试和维护：设计好数据库之后，需要对数据库进行测试，包括测试数据的完整性、性能和安全性等。同时还需要进行定期的数据库维护工作，包括备份、优化、数据清理等。

1.5 数据库性能优化

  数据库性能优化是指通过调整数据库的结构和参数配置，提高数据库的响应速度、吞吐量和并发能力，以满足应用程序的需求。以下是一些常用的数据库性能优化方法：

  1.索引优化：索引是提高数据库查询性能的关键，通过对频繁查询的字段创建索引，可以大大缩短查询时间。需要注意的是，过多或者不必要的索引会降低插入、更新、删除等操作的性能，需要权衡优化。

  2.优化查询语句：对于复杂的查询语句，可以考虑分解为多个简单的查询，减少查询时间。同时需要避免在WHERE子句中使用函数或者运算符等操作，会导致索引失效。

  3.数据库参数调优：针对不同的数据库系统，有不同的参数可以调整以优化性能，例如MySQL的缓存、并发和连接池等参数。

  4.表结构优化：优化表的结构，避免使用过多的大字段，不必要的冗余字段，以及过多的关联查询等操作。

  5.分区和分库：对于数据量较大的数据库，可以考虑将数据按照一定规则分成多个分区或者分库，减轻单个节点的负载压力。

  6.缓存优化：使用缓存可以大大减少数据库的访问，提高响应速度和吞吐量。常用的缓存方案包括内存缓存、分布式缓存和CDN等。

  7.定期维护：定期对数据库进行维护，包括备份、优化、数据清理等操作，可以减少数据库的负担，提高性能和可靠性。

总之，数据库性能优化需要结合具体的应用场景和数据特点，综合考虑多种优化方案，以达到最优的性能和稳定性。

1.6 数据库的备份和恢复

  数据库备份与恢复是数据库管理中非常重要的一环，可以保障数据的安全和完整性。以下是一些常用的数据库备份与恢复方法：

  1.完全备份：完全备份是指将整个数据库备份下来，包括数据和日志等信息。完全备份是最基本的备份方式，可以保证数据的完整性，但备份文件通常比较大，且恢复时间比较长。

  2.增量备份：增量备份是指只备份自上次备份以来的更改部分。增量备份可以减少备份文件的大小和备份时间，但恢复过程比较复杂，需要先进行完全备份，然后逐个应用增量备份。

  3.差异备份：差异备份是指备份自上次完全备份以来的所有更改部分，与增量备份相比，差异备份的备份文件比较小，但恢复时间仍然较长。

  4.定期备份：定期备份是指根据业务需求，定期进行数据库备份。备份频率根据数据的变化情况和恢复时间的要求等因素来确定，例如每天、每周或者每月备份等。

  5.恢复操作：恢复操作是指在出现数据库故障时，将备份的数据恢复到数据库中。恢复过程中需要先选择合适的备份文件，然后根据备份类型和恢复策略等因素来选择恢复方式，例如通过备份文件直接恢复，或者通过增量备份和差异备份逐个应用。

总之，数据库备份与恢复是数据库管理中非常重要的一环，可以保障数据的安全和完整性。备份需要根据具体业务需求来制定备份策略，同时需要定期进行备份测试，确保备份数据的可靠性和完整性。在出现故障时，需要及时选择合适的恢复方式，以保障业务的连续性和可靠性。

2 数据库基本概念

2.1 数据库

  数据库是一种用于存储、组织和管理数据的电子系统，可以让用户方便地存储和检索数据。数据库通常由一个或多个数据表组成，每个表都有一些列，每列代表了一个特定的数据类型。在表中，每一行都代表了一条记录，其中每个列都包含了相应的数据值。

  数据库可用于各种类型的应用程序，包括商业、科学、医疗、社交媒体等等。它们提供了一种安全、可靠的数据存储方式，可以让多个用户同时访问和处理同一个数据集。用户可以在数据库中进行各种操作，例如添加、修改、删除和查询数据。

  现代数据库通常采用关系型数据库管理系统（RDBMS），这种系统使用SQL（Structured Query Language）作为管理和查询数据的标准语言。此外，还有一些非关系型数据库，例如NoSQL数据库，它们不使用SQL语言，但通常具有更好的扩展性和性能。

2.2 数据库表

  在关系型数据库中，数据通常被组织成表的形式，表由行和列组成，类似于电子表格。每个表都有一个唯一的名称，每个列都有一个名称和一个数据类型，每行包含一组相关的数据。

  表是数据库中最基本的组成部分之一，它可以存储一定类型的数据，并提供对这些数据的快速访问和操作。每个表都具有一个结构，包括表的名称、列的名称、数据类型以及约束条件等。

  在表中，每行都代表一个记录或实例，每个记录由一个或多个列组成，它们包含了相关的数据。例如，在一个名为“用户”的表中，每一行可能代表一个用户，每个列可能包含一个用户的姓名、电子邮件地址、电话号码和地址等信息。

  表中的列通常具有一定的数据类型，例如整数、浮点数、日期、文本等等。列还可以包含约束条件，例如唯一性约束、主键约束、外键约束等，这些约束条件用于确保数据的完整性和一致性。

  表的设计和管理是数据库设计和管理的重要组成部分，包括创建、删除、修改和查询表中的数据等。

2.3 数据库记录（行）

  在关系型数据库中，表是由行和列组成的数据结构，每一行表示一条记录或实例，其中每一列包含一个数据元素。每个记录都包含了一组相关的数据，表示某个实体或概念的特定属性。例如，在一个名为“学生”的表中，每一行可能代表一个学生，每个列可能包含学生的姓名、学号、出生日期、性别、专业等信息。每个记录中的数据是根据列的数据类型来组织的，以确保数据的一致性和可比性。

  在表中，每个记录都必须包含一个唯一的标识符，通常称为主键。主键可以是一列或多列的组合，用于标识记录并确保记录的唯一性。

  记录的创建、修改和删除是数据库管理的基本操作之一。用户可以通过SQL语句或图形用户界面来实现这些操作。在记录的创建和修改过程中，用户需要确保输入的数据符合列的数据类型、长度和约束条件，以确保数据的完整性和一致性。

  在数据库查询中，记录也起着重要的作用，用户可以使用SQL查询语句来检索特定条件下的记录，并对记录进行排序、分组、过滤等操作。查询的结果通常是一个记录集，其中每个记录代表了满足查询条件的一条记录。

2.4 数据库列

  在关系型数据库中，表是由行和列组成的数据结构，每个列都代表了表中某一类数据的属性。每个列都有一个名称和一个数据类型，以及一些其他的属性，如默认值、约束条件等。

  列是表中最基本的组成部分之一，它们描述了表中的数据结构和属性。在表中，每一列代表一种数据类型，例如整数、浮点数、日期、字符串等等。在列中，每个数据元素都具有相同的数据类型，这有助于确保数据的一致性和可比性。

  列还可以包含一些约束条件，例如唯一性约束、主键约束、外键约束等，这些约束条件用于限制数据的输入，以确保数据的完整性和一致性。例如，在一个“用户”表中，用户ID可能是一个整数类型的主键列，用于唯一标识每个用户记录。

  在数据库设计和管理中，列的设计是非常重要的，需要考虑到数据类型的选择、数据长度、约束条件以及索引等因素，以确保表的性能和数据质量。

2.5 数据库主键

  在关系型数据库中，主键是用于唯一标识表中每个记录的一列或一组列。每个表只能有一个主键，并且主键列的值必须是唯一的、不为空的，并且不能被修改或删除。主键的作用是保证数据的一致性、完整性和可靠性，同时也是表之间建立关系的重要基础。

  在实际应用中，主键通常是一个自增长的整数列，用于自动生成唯一的标识符。例如，在一个“学生”表中，可以将学生的ID设置为主键，并使用自增长功能来自动生成每个学生的唯一ID。主键还可以是一个复合键，由多个列的组合构成，用于标识复合唯一性。例如，在一个“订单”表中，可以使用订单编号和订单日期作为复合主键，以确保每个订单的唯一性。

  主键的选择和设计对于数据库的性能和数据质量非常重要。主键的类型、长度和约束条件需要根据具体的数据结构和需求进行选择，以确保主键的稳定性和可靠性。在创建和修改表的结构时，必须明确指定主键列，并确保主键的唯一性和完整性。

2.6 数据库外键

  在关系型数据库中，外键是用于建立表之间关系的一种机制，它定义了一个表中的列与另一个表中的主键或唯一键之间的关联。通过外键，可以实现表之间的数据完整性和一致性，确保关联的数据在不同表之间的正确性和有效性。

  外键通常由两个部分组成：一个是定义在当前表中的列，称为“外键列”或“关联列”，另一个是定义在引用表中的列，称为“主键列”或“引用列”。在建立外键关系时，需要确保外键列的数据类型、长度和约束条件与主键列的定义相匹配，以确保数据的正确性和一致性。

  外键关系可以在表的创建时或之后建立，可以使用SQL语句或图形用户界面来实现。在建立外键关系时，需要指定引用表的名称和主键列的名称，以确保外键列与主键列之间的正确关联。如果外键列中的数据与引用表中的主键列中的数据不匹配，或者引用表中的主键列中的数据发生更改或删除，那么相应的外键列中的数据也将被更新或删除。

  外键的作用是确保数据的一致性和完整性，可以有效避免数据冗余和重复，并提高数据的查询效率和可维护性。在数据库设计和开发中，外键的选择和建立应该根据具体需求和数据模型进行，并考虑到数据库的性能和安全性。

2.7 数据库索引

  在关系型数据库中，索引是一种数据结构，用于加速数据的检索和查询操作。它是一个数据库对象，存储在磁盘上，包含了数据表中一个或多个列的排序信息。通过对索引列进行排序，可以快速定位表中符合条件的记录，从而提高查询效率和性能。

  索引通常由一个或多个列组成，可以是唯一索引、非唯一索引、聚集索引或非聚集索引等类型。唯一索引要求索引列的值必须是唯一的，非唯一索引则允许索引列中包含重复的值。聚集索引和非聚集索引的区别在于索引存储的位置和方式不同，前者将数据和索引存储在一起，后者则将数据和索引分开存储。

  索引的作用是提高查询效率和性能，减少数据的扫描和过滤操作，从而加快查询的速度。通过合理选择和设计索引，可以有效优化数据库的性能和响应时间。但是，过多的索引也会增加数据库的存储空间和维护成本，因此需要根据具体的业务需求和数据模型进行选择和设计。

  在数据库中，可以使用SQL语句或图形界面来创建、修改和删除索引。为了保证索引的效果和稳定性，需要考虑索引列的选择、排序、长度和约束条件等因素，避免索引冗余和重复。同时，需要定期维护和优化索引，以确保索引的有效性和性能。

2.8 数据库视图

  在关系型数据库中，视图（View）是一种虚拟的表，它是基于一个或多个基本表的查询结果，以一定的方式呈现出来的结果集。与实际的基本表不同，视图并不实际存储数据，而是从一个或多个表中检索数据的逻辑结果集。

  视图的作用是提供一种简化和抽象的数据视图，使用户能够更轻松地查询和操作数据。通过使用视图，可以隐藏实际数据表的结构和细节，简化查询操作，减少数据冗余和重复。同时，视图还可以增强数据的安全性和保密性，通过授权和权限设置，限制用户对数据的访问和操作。

  在数据库中，可以使用SQL语句或图形界面来创建、修改和删除视图。为了确保视图的有效性和正确性，需要根据具体的业务需求和数据模型进行选择和设计。视图的设计应该考虑到数据的一致性、完整性和性能等因素，避免视图的冗余和复杂性。同时，需要定期维护和更新视图，以确保视图的有效性和性能。

3 MySQL简介

  MySQL是一种开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前由Oracle公司进行开发和维护。MySQL使用标准的SQL数据语言进行数据的管理和操作，支持多种操作系统平台，包括Linux、Windows和Mac OS等。

  MySQL的特点包括：

  1.开源、免费：MySQL是一个完全开源的软件，可以免费下载和使用。

  2.稳定、可靠：MySQL是一种经过广泛测试和验证的数据库管理系统，具有良好的稳定性和可靠性。

  3.高性能：MySQL使用多种技术和优化策略，可以提供高效的数据存储和检索功能。

  4.可扩展性：MySQL支持分布式和集群部署，可以根据业务需求进行水平或垂直扩展。

  5.兼容性：MySQL支持多种操作系统和编程语言，可以与其他应用程序无缝集成。

  6.安全性：MySQL支持多种安全功能和加密算法，可以保护数据的安全性和完整性。

  MySQL广泛应用于Web应用程序、企业应用、嵌入式系统和云计算等领域。目前，MySQL已成为全球使用最广泛的关系型数据库之一，拥有庞大的社区和生态系统，提供丰富的插件和扩展功能，能够满足各种不同的数据管理和处理需求。

3.1 MySQL的历史

  MySQL的历史可以追溯到1994年，当时瑞典的Michael Widenius和David Axmark开发了一个名为MySQL的轻量级关系型数据库管理系统，它基于C语言开发，采用了BSD许可证，可以免费使用和修改。

  1995年，Michael Widenius和David Axmark与Allan Larsson共同成立了MySQL AB公司，开始商业化推广MySQL，并提供了商业版本的服务和支持。

  在接下来的几年中，MySQL逐渐成为最流行的开源数据库之一，受到了广泛的关注和应用。2000年，MySQL AB发布了第一个完整的MySQL版本，增加了许多新的特性和功能，包括事务处理、多版本并发控制、存储过程、触发器等。

  2008年，MySQL AB被Sun Microsystems收购，成为Sun的子公司。在Sun的支持下，MySQL继续发展和壮大，增加了更多的特性和功能，并逐渐成为企业级应用程序的标准数据库。

  2010年，Sun Microsystems被Oracle Corporation收购，MySQL成为Oracle的一部分。在Oracle的领导下，MySQL继续推出了新的版本，增加了更多的功能和改进，包括InnoDB存储引擎的改进、分区表、复制、性能优化等。

  目前，MySQL已成为全球使用最广泛的关系型数据库之一，拥有庞大的用户群体和生态系统，包括各种工具、插件和扩展功能，能够满足各种不同的数据管理和处理需求。

3.2 MariaDB

  MariaDB是一个由MySQL的创始人Michael Widenius领导的团队开发的开源关系型数据库管理系统。MariaDB最初于2009年发布，旨在创建一个完全兼容MySQL的数据库系统，同时提供更好的性能、更多的功能和更好的稳定性。

  MariaDB基于MySQL的代码库，并且完全兼容MySQL，因此可以轻松地将MySQL应用程序迁移到MariaDB。不过，MariaDB也提供了许多MySQL不具备的功能，例如更好的性能优化、更好的事务支持、更好的安全性、更好的扩展性、更好的存储引擎支持等。

  MariaDB是开源软件，可以免费下载和使用。它也有商业版本，提供额外的功能和支持。MariaDB在开源社区和企业中都很受欢迎，许多知名的公司和组织使用MariaDB作为其关键业务系统的数据库。

3.3 数据库引擎

  数据库引擎是数据库管理系统中的一个核心组件，用于处理数据的存储、检索和修改。不同的数据库引擎具有不同的存储和查询机制，可以影响数据库的性能、可靠性和功能。

  MySQL提供了多种存储引擎，不同的存储引擎具有不同的特点和适用场景，如下所示：

  1.MyISAM：这是MySQL最古老的存储引擎之一，提供了高速的读取和写入速度，适合用于读多写少的应用程序。不过，它不支持事务处理和行级锁定，也不支持外键约束。

  2.InnoDB：这是MySQL目前最流行的存储引擎之一，提供了高性能和高可靠性，支持事务处理和行级锁定，还支持外键约束和其他高级特性。它适合用于处理大型的、高并发的应用程序。

  3.Memory：这个存储引擎将数据存储在内存中，提供了极快的读取和写入速度，但是数据只能存储在内存中，一旦服务器关闭或者重启，数据将会丢失。

  4.CSV：这个存储引擎将数据存储为CSV（逗号分隔值）格式，适合用于存储大量的数据文件，但是它不支持事务处理和行级锁定，也不支持索引。

  5.Archive：这个存储引擎适合用于存储归档数据，它使用压缩算法来减小数据文件的大小，但是它不支持更新和删除操作，只支持插入操作。

  6.Blackhole：这个存储引擎将所有的写入操作都忽略掉，但是它仍然可以执行读取操作，适合用于数据的复制和同步。

  除了上述存储引擎，MySQL还提供了其他一些存储引擎，如NDB Cluster、Federated、Merge等。每个存储引擎都有自己的特点和适用场景，开发人员可以根据自己的需求选择最适合的存储引擎。

3.4 MySQL的架构

  MySQL的架构可以分为以下三层：

  1.连接层（Connection Layer）：连接层负责接收客户端的连接请求，并验证客户端的身份。如果客户端验证通过，则连接层将会将请求转发给下一层的处理器。连接层还负责处理连接请求的一些参数，如字符集、认证方式、加密方式等。

  2.处理器层（Processing Layer）：处理器层负责处理所有的SQL语句和事务请求。当处理器层接收到SQL语句时，它会对语句进行解析、优化和执行。处理器层还负责管理所有的数据库对象，如表、索引、视图等。

  3.存储引擎层（Storage Engine Layer）：存储引擎层负责管理数据的存储和检索。存储引擎层将数据存储在磁盘或内存中，并且负责数据的读取、写入和索引。MySQL提供了多种存储引擎，每种存储引擎都有自己的特点和适用场景。

  MySQL的三层架构使得它具有很高的可扩展性和灵活性，可以根据不同的应用场景选择不同的存储引擎，从而达到最佳的性能和可靠性。

3.5 MySQL客户端和服务器

  MySQL客户端和服务器是MySQL架构中的两个重要组件，它们之间通过网络连接进行通信，完成数据库操作。

  MySQL服务器是一个运行在后台的进程，用于接收客户端的连接请求，处理SQL语句和事务请求，管理数据的存储和检索。MySQL服务器的主要功能是提供安全可靠的数据库服务，为多个客户端提供并发访问的支持。

  MySQL客户端是连接到MySQL服务器的一个应用程序，用于向MySQL服务器发送SQL语句、事务请求等操作，并接收返回的结果。MySQL客户端可以通过命令行工具、GUI工具、API等方式进行访问。

  MySQL客户端和服务器之间的通信基于TCP/IP协议，MySQL服务器默认监听端口为3306。客户端通过TCP/IP协议连接到MySQL服务器后，需要进行身份验证和授权，然后才能对数据库进行操作。MySQL支持多种身份验证和授权方式，如基于用户名/密码的身份验证、基于IP地址的访问控制等。

  MySQL客户端和服务器的分离架构使得MySQL具有很高的可扩展性和灵活性，可以根据不同的应用场景灵活配置和调整服务器的参数和选项，从而达到最佳的性能和可靠性。

4 windows下安装MySQL

4.1 MySQL的两个版本

  MySQL是一个开源的关系型数据库管理系统，有许多版本发布，其中比较常见的两个版本是MySQL Community Edition和MySQL Enterprise Edition。

  MySQL Community Edition是免费的开源版本，提供了一些基本的数据库管理功能，包括数据存储、查询、备份和恢复等。该版本拥有较强的社区支持，有大量的开源开发者和爱好者为其开发各种插件和扩展，可以根据需要灵活地定制功能。

  MySQL Enterprise Edition则是一款商业版本，拥有更多的高级功能和服务，包括高级安全性、性能优化、数据分析、监控和自动化等。同时，它还提供了更加稳定和可靠的支持，包括24小时技术支持、紧急修复、升级和专业咨询等服务。

  总体而言，MySQL Community Edition适合于一般的中小型应用开发，而MySQL Enterprise Edition则更适合于需要高可用性和大规模部署的企业级应用。

4.2 安装MySQL

  以下是在Windows操作系统上安装MySQL Community Edition的基本步骤：

  1.下载MySQL Community Edition安装程序：前往MySQL官方网站（https://dev.mysql.com/downloads/mysql/）下载对应操作系统版本的MySQL安装程序。选择Windows (x86, 32-bit), Windows (x86, 64-bit), Windows (x86, 32-bit), MSI Installer或者Windows (x86, 64-bit), MSI Installer 适用于您的操作系统版本。同时也需要选择适用于您操作系统版本的MySQL Community Edition。

  2.运行安装程序：打开下载好的MySQL安装程序，并按照提示进行安装。

  3.安装过程中的配置：在安装过程中，您需要选择要安装的MySQL版本和组件。如果您需要安装MySQL Workbench等附加组件，则需要勾选相应的选项。接着您需要选择MySQL安装的位置和配置文件，建议采用默认选项。

  4.设置root密码：在安装过程中，您需要设置root用户的密码。请务必设置强密码并妥善保存。

  5.完成安装：在安装过程完成后，您可以打开MySQL Command Line Client，测试MySQL是否安装成功。在命令行中输入以下命令，如果能成功登录，则表示MySQL已经安装成功：

mysql -u root -p

  然后输入root用户的密码，就可以开始使用MySQL了。

注意：在安装MySQL之前，需要确保您的计算机上没有安装其他的MySQL版本，否则可能会导致安装失败。另外，如果您的计算机上已经安装了防火墙，请确保MySQL可以通过防火墙进行访问。

4.3 安装目录分析

  在Windows下安装MySQL时，通常会选择一个目录进行安装。该目录中包含MySQL数据库管理系统的所有文件和子目录。下面是MySQL Windows安装目录的一些常见文件和子目录：

  1.bin 目录：包含MySQL的二进制可执行文件。这些文件是MySQL服务器和客户端程序的核心部分，例如 mysql.exe，mysqld.exe 等。

  2.data 目录：MySQL服务器存储所有数据的默认位置。在这个目录中，可以找到所有的数据库和表数据文件，以及日志文件。

  3.docs 目录：MySQL的文档文件，包括用户手册，安装指南和开发文档等。

  4.include 目录：包含用于编译MySQL客户端和服务器的头文件。

  5.lib 目录：包含MySQL的库文件，例如动态链接库和静态链接库。

  6.share 目录：包含共享文件，例如错误消息、字符集和语言文件等。

  7.support-files 目录：包含MySQL的配置文件示例，例如 my-default.ini， my-huge.ini 等。

  8.my.ini 文件：MySQL的配置文件，包含了MySQL服务器的配置信息，例如端口号、字符集和数据存储路径等。

需要注意的是，这些文件和子目录的名称和结构可能会因不同版本的MySQL而有所不同。

5 Linux下安装MySQL

以下是在CentOS操作系统上安装MySQL Community Edition的基本步骤：

1. 更新软件包列表 在终端中输入以下命令，更新软件包列表：

   yum update

2. 安装MySQL 在终端中输入以下命令，安装MySQL：

   yum install mysql-server

   安装过程中，您需要设置root用户的密码。请务必设置强密码并妥善保存。

3. 配置MySQL 在安装过程中，MySQL服务器会自动启动。您可以在终端中输入以下命令来检查MySQL是否正在运行：

   systemctl status mysqld

   如果MySQL没有启动，您可以使用以下命令手动启动MySQL：

   systemctl start mysqld

4. 安全配置MySQL 您可以使用以下命令启动MySQL安全配置向导，该向导将帮助您设置MySQL的安全性：

   mysql_secure_installation

   在向导中，您可以选择禁用匿名用户、禁用root用户的远程登录、删除测试数据库和刷新权限表等选项。

5. 完成安装 在安装过程完成后，您可以在终端中输入以下命令，使用MySQL：

   mysql -u root -p

   然后输入root用户的密码，就可以开始使用MySQL了。

注意：如果您的CentOS版本不同，安装MySQL的步骤可能会有所不同。另外，如果您的计算机上已经安装了防火墙，请确保MySQL可以通过防火墙进行访问。

6 MySQL配置文件分析

6.1 MySQL配置文件

  1.Centos文件路径

  在 CentOS 系统中，MySQL 的默认配置文件是 /etc/my.cnf 或者 /etc/mysql/my.cnf，具体的文件路径取决于 MySQL 的安装方式和操作系统的版本。

  如果存在多个 MySQL 实例或者使用了第三方 MySQL 安装包，则可能会存在多个配置文件。可以通过以下命令查找 MySQL 配置文件的位置：

sudo find / -name "my.cnf"

  该命令将在系统中查找所有名称为 my.cnf 的文件，并显示它们的路径。如果 MySQL 的默认配置文件存在，那么可以在输出中找到它的路径。

  需要注意的是，如果没有找到 MySQL 的默认配置文件，则可以通过创建一个新的 my.cnf 文件来手动配置 MySQL 的参数和选项。在 CentOS 中，该文件通常放置在 /etc/ 目录下。在创建和修改 my.cnf 文件时，需要注意文件权限和所有权，以确保 MySQL 可以读取和修改该文件。

  2.windows文件路径

  在 Windows 操作系统中，MySQL 的默认配置文件名为 my.ini 或者 my.cnf，其存放位置取决于 MySQL 的安装方式和版本。在通常情况下，MySQL 安装程序会将默认配置文件存储在安装目录下的 bin 目录中。

  具体来说，如果您使用的是 MySQL 官方的 MSI 安装程序，则默认的配置文件路径通常为：

C:\\Program Files\\MySQL\\MySQL Server X.Y\\my.ini

  其中，X.Y 表示 MySQL 版本号。如果您使用的是 ZIP 或者二进制包进行安装，则默认的配置文件存放在解压缩目录的 support-files 子目录下。

  注意，如果在安装 MySQL 时选择了不同的安装目录，那么默认配置文件的位置也会相应地发生变化。

  另外，在 Windows 中，MySQL 还可以通过 --defaults-file 命令行选项来指定使用的配置文件。例如：

C:\\Program Files\\MySQL\\MySQL Server X.Y\\bin\\mysqld --defaults-file=C:\\mycustom\\my.ini

  以上命令将启动 MySQL，并使用 C:\\mycustom\\my.ini 文件作为配置文件。

6.2 重要选项分析

  MySQL 的配置文件包含了 MySQL 服务器的所有配置信息，MySQL 在启动的时候会读取该配置文件来初始化服务器的各种参数和选项。下面是 MySQL 配置文件的一些常见选项和参数：

  1.[mysqld]：包含了 MySQL 服务器的配置信息，其中包括常见的配置项，例如 port，datadir 等。

  2.port：指定了 MySQL 服务器监听的端口号，默认值是 3306。

  3.datadir：指定了 MySQL 服务器的数据文件的存储路径，默认值是 /var/lib/mysql。

  4.bind-address：指定了 MySQL 服务器监听的 IP 地址，默认值是 0.0.0.0，表示监听所有的 IP 地址。

  5.socket：指定了 MySQL 服务器使用的 Unix 套接字文件的路径，默认值是 /var/run/mysqld/mysqld.sock。

  6.log-error：指定了 MySQL 服务器的错误日志文件的路径和文件名，默认值是 /var/log/mysql/error.log。

  7.pid-file：指定了 MySQL 服务器的 PID 文件的路径和文件名，默认值是 /var/run/mysqld/mysqld.pid。

  8.[client]：包含了 MySQL 客户端的配置信息，其中包括常见的配置项，例如 user，password 等。

  9.user：指定了 MySQL 客户端连接数据库时使用的用户名，默认值是当前登录用户的用户名。

  10.password：指定了 MySQL 客户端连接数据库时使用的密码，默认值是空。

  MySQL 的配置文件可以在安装时指定，也可以在运行时通过命令行选项来指定。并且，MySQL 配置文件的路径和名称可能因不同的操作系统和 MySQL 版本而有所不同。

7 MySQL数据库基本操作

  以下是常用的 SQL 语句来创建、删除、修改和打开数据库：

  1.创建数据库：创建一个名为 mydatabase 的数据库：

CREATE DATABASE mydatabase;

  2.删除数据库：删除名为 mydatabase 的数据库：

DROP DATABASE mydatabase;

  3.修改数据库：修改名为 mydatabase 的数据库的字符集为 utf8mb4：

ALTER DATABASE mydatabase CHARACTER SET utf8mb4;

  4.打开数据库：

  在 MySQL 中，使用 USE 语句来打开数据库。例如，要使用名为 mydatabase 的数据库，可以使用以下命令：

USE mydatabase;

  在执行该命令后，所有后续的 SQL 语句都将在 mydatabase 数据库中执行。

创建、删除、修改或打开数据库，需要有足够的权限才能执行。

在 MySQL 中，通常使用授权命令（例如 GRANT）来授予或撤销用户对数据库的权限。另外，还需要使用适当的 SQL 客户端（例如 mysql 命令行工具或者 MySQL Workbench）来执行这些 SQL 语句。

7.1 MySQL创建表

  MySQL 创建表的 SQL 语句：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
    PRIMARY KEY (one or more columns)
);

  上面的 SQL 语句中，table_name 是要创建的表的名称，column1, column2, column3 等是表中的列名，datatype 是该列的数据类型，constraints 是该列的约束条件（例如：NOT NULL、UNIQUE、DEFAULT 等）。PRIMARY KEY 是必需的，并且至少应指定一个列作为主键。主键用于唯一地标识表中的每个记录，因此它必须是唯一的并且不能为 NULL。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

上面的示例创建了一个名为 users 的表，该表包含五个列：

id int not null auto_increment

  id：字段名，用户唯一编号，整数类型的主键列，具有自动递增属性。

  int：整数类型。 not null：不能为空

  auto_increment：自增。新增用户时，MySQL自动分配 1,2,3,4……

name varchar(50) not null

  name：用户名

  varchar(50)：长度为50个字符的字符串类型的列，可变长度字符串，最多存储50个字符

  not null：不允许为空

email varchar(255) not null unique

  email：邮箱

  varchar(255)：长度为255个字符的字符串类型的列，可变长度字符串，最多存储255个字符

  not null：不允许为空

  unique：唯一约束，整张表里邮箱不能重复，防止同一个邮箱注册多个账号

password varchar(255) not null

  password：用户密码

  varchar(255)：长度为255个字符的字符串类型的列，可变长度字符串，最多存储255个字符

  not null：不允许为空

注明：开发规范：不要直接存明文密码，业务代码里先加密（bcrypt等）再存入数据库。

created_at timestamp default current_timestamp

  created_at：创建时间，时间戳类型的列。

  timestamp：时间戳类型

  default current_timestamp：默认值为当前时间

注明：新增用户时，如果不手动传入时间，数据库自动填入本条数据插入的时间。

primary key(id)

  设置 id 为主键。主键特性：唯一、非空，用来唯一标识每一行用户数据，加速查询。

# 优化后的sql：
create table users (
    id int not null auto_increment comment '用户ID',
    name varchar(50) not null comment '用户名',
    email varchar(255) not null comment '邮箱',
    password varchar(255) not null comment '加密密码',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_email(email)
) comment = '用户表';

# 增加 comment 注释，方便后期维护查看表结构。

删除表：

drop table users;

数据类型和约束条件取决于具体的需求，可以根据需要进行修改。

7.2 MySQL插入数据

  向 MySQL 数据库中插入数据可以使用 INSERT INTO 语句：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

  注明：

  table_name 是要插入数据的表名

  column1, column2, column3 等是表中的列名

  value1, value2, value3 等是要插入的值。

  列名和对应的值必须匹配。

  1.插入单条

insert into users(name,email,password) values ('Tom','Tom@qq.com','Password@123');

  上面的示例向 users 表中插入了一条记录，包含 name、email 和 password 三个字段的值。如果某个字段允许为空，则可以将其值设置为 NULL。如果某个字段是自动递增的，则不需要在插入数据时指定该字段的值，MySQL 会自动分配。

  2.插入多条记录

  如果要同时插入多条记录，可以在 VALUES 后面添加多个值组，用逗号分隔。

insert into users (name,email,password)
values ('Kite','Kite@qq.com','Password@456'),
       ('Rose','Rose@qq.com','Password@789');

  以上 SQL 语句将向 users 表中插入两条记录。

MySQL 数据库中插入数据需要有足够的权限才能执行该操作。同时，也需要注意数据的合法性和完整性，以避免数据不一致或丢失的问题。

7.3 MySQL删除数据

  MySQL 使用 DELETE 语句来删除数据。DELETE 语句用于从表中删除数据，可以指定删除的行和条件。

DELETE FROM table_name
WHERE condition;

  table_name 是要删除数据的表名，condition 是删除数据的条件。如果没有指定条件，将会删除表中所有的数据。

DELETE FROM users
WHERE id = 1;

  上面的示例中，从 users 表删除了 id 列的值为 1 的行。

删除操作是不可恢复的，所以在执行 DELETE 语句之前一定要三思而后行。同时，也需要注意数据库中的关联关系和完整性约束，以避免数据不一致或丢失的问题。如果需要删除整个表，可使用 DROP TABLE 语句，但同样需要谨慎操作。

7.4 MySQL修改数据

alter table users add column status varchar(16) not null default 'active' comment '用户状态: active激活,inactive禁用';

  MySQL 中使用 UPDATE 语句来修改数据。UPDATE 语句用于更新表中的数据，可以指定要更新的列和更新的条件。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

  table_name 是要更新数据的表名，column1, column2, … 是要更新的列名，value1, value2, … 是要更新的值，condition 是更新数据的条件。

update users set email = 'new-email@example',status = 'inactive' where id = 2;

  上面的示例，将 users 表中 id 列的值为 2 的行的 email 列更新为 new-email@example.com，status 列更新为 inactive。

修改操作可能会影响到其他的数据和约束，需要谨慎操作。同时，也需要注意数据类型的匹配和长度限制，以避免数据被截断或溢出。

7.5 MySQL基本查询

  MySQL 中使用 SELECT 语句来查询数据。SELECT 语句用于从表中检索数据，可以指定要检索的列、过滤条件、排序方式等。

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

  column1, column2, … 是要检索的列名，可以使用通配符 * 检索所有列。table_name 是要从中检索数据的表名。condition 是查询条件，用于过滤数据，可以使用 AND、OR、IN、BETWEEN 等操作符进行组合。ORDER BY 子句用于指定检索结果的排序方式，可以按一个或多个列进行升序或降序排序。

select name,email from users where email like '%example' order by name asc;

  上面的示例，从 users 表中检索了 name 和 email 两个列的数据，并按照 name 列的升序方式排序。同时，指定了一个过滤条件，用于筛选出 email 列以 example 结尾的记录。

查询结果可能会包含多条记录，需要使用适当的方式来处理这些数据，例如循环遍历或使用聚合函数。同时，也需要注意 SQL 注入等安全问题，确保输入的查询条件是安全的。

8 可视化客户端

  MySQL 可视化客户端是一个图形化界面的工具，帮助用户更方便地管理和操作 MySQL 数据库。以下是常见的 MySQL 可视化客户端：

  1.MySQL Workbench：官方提供的 MySQL 数据库管理工具，支持数据建模、SQL 编辑和调试、数据备份和恢复等。MySQL Workbench 是跨平台的，可以在 Windows、Linux 和 macOS 等操作系统上使用。

  2.Navicat for MySQL：商业的 MySQL 客户端，支持多种操作系统，并提供了丰富的功能，包括数据导入和导出、可视化查询构建器、数据同步、备份和恢复等。

  3.HeidiSQL：免费的开源 MySQL 客户端，支持 Windows 平台。提供了多种功能，包括数据导入和导出、SQL 编辑和执行、数据库对象管理等。

  4.DBeaver：免费的开源数据库工具，支持多种数据库，包括 MySQL、PostgreSQL、SQLite 等。DBeaver 提供了丰富的功能，包括数据导入和导出、SQL 编辑和执行、数据可视化等。

  5.phpMyAdmin：基于 Web 的 MySQL 客户端，可以通过浏览器访问。提供了丰富的功能，包括数据导入和导出、SQL 编辑和执行、数据库对象管理等。phpMyAdmin 是免费的，可以在大多数 Web 服务器上使用。

  6.SQLyog：商业的 MySQL 可视化客户端，提供了丰富的功能，包括数据管理、SQL 编辑和执行、数据同步、备份和恢复等。

  7.DataGrid：（数据网格）是一种用于显示表格数据的控件，可以在网页中显示数据，并提供了许多有用的功能，如排序、筛选、分页等。

8.1 DBeaver客户端

  DBeaver /diː'beɪvə(r)/ 是免费的开源数据库管理工具，支持多种数据库（MySQL、PostgreSQL、Oracle、SQL Server 等）的连接和管理，可以在 Windows、macOS、Linux 等平台上使用。以下是 DBeaver 的基本使用方法：

  1.下载并安装 DBeaver 客户端：可以从 DBeaver 的官方网站（https://dbeaver.io/download/）下载对应平台的安装包，并按照提示进行安装。

  2.新建数据库连接：启动 DBeaver 后，点击“新建连接”按钮，选择要连接的数据库类型（如 MySQL），输入连接信息（如主机名、端口、用户名、密码等），并测试连接是否成功。

  3.执行 SQL 查询：在连接成功后，可以在 DBeaver 中打开 SQL 编辑器，输入 SQL 查询语句（如 SELECT、INSERT、UPDATE、DELETE 等），并执行查询，查看结果。

  4.管理数据库对象：在 DBeaver 中，可以管理数据库中的各种对象，如表、视图、存储过程等。可以查看对象的结构、编辑对象的属性、复制对象等。

  5.导入导出数据：在 DBeaver 中，可以将数据导出为 CSV、Excel、SQL 等格式，也可以将数据从文件或数据库中导入。

  6.高级功能：DBeaver 还提供了一些高级功能，如数据比较、数据同步、查询优化器、安全管理等，可以根据需要使用。

  DBeaver 是一款功能强大的数据库管理工具，可以帮助开发者和管理员轻松地连接和管理多种数据库，提高工作效率和生产力。

8.2 SQLyog客户端

  SQLyog是流行的MySQL管理工具，支持Windows操作系统，以下是SQLyog在Windows下的下载和安装步骤：

  1.访问SQLyog官网（https://www.webyog.com/product/sqlyog)。

  2.单击“下载”按钮以下载安装程序。

  3.运行下载的安装程序。

  4.点击“下一步”按钮，阅读许可协议，然后勾选同意协议。

  5.选择要安装的组件，可以选择“SQLyog Community Edition”或“SQLyog Ultimate”。

  6.指定安装位置并点击“下一步”按钮。

  7.选择开始菜单文件夹并单击“下一步”。

  8.选择桌面图标选项并单击“下一步”。

  9.点击“安装”按钮，等待安装完成。

  10.点击“完成”按钮，启动SQLyog。

9 MySQL的数据类型

  MySQL 支持多种数据类型，包括数值型、字符串型、日期时间型、二进制型等。以下是 MySQL 常见的数据类型：

数据类型	常见子类型
数值型	TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL
字符串型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET
日期时间型	DATE、TIME、YEAR、DATETIME、TIMESTAMP
二进制型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

  数据类型的选择应该根据存储的数据类型和范围来确定。对于存储性别的字段，可以使用 ENUM 类型，因为该字段的取值范围较小，只有男、女两个取值。对于存储长文本的字段，应该使用 LONGTEXT 类型，而不是 TEXT 类型，以支持更大的文本内容。

create table `employees` (
                          `id` int(11) not null auto_increment comment '员工ID',
                          `name` varchar(50) not null comment '员工姓名',
                          `age` tinyint(4) not null comment '员工年龄',
                          `email` varchar(100) not null comment '邮箱',
                          `salary` decimal(10,2) not null comment '薪水',
                          `hire_date` date not null comment '入职日期',
                          `photo` blob comment '员工照片',
                          `created_at` timestamp default current_timestamp comment '创建时间',
                          primary key (`id`),
                          unique uk_email(email)) 
                          comment = '员工表',
                          engine=InnoDB default charset=utf8mb4;                                            

表中定义了以下字段：

  1.id: 整数型，主键，自动增长

  2.name: 字符串型，最大长度为 50

  3.age: 整数型，最大长度为 4

  4.email: 字符串型，最大长度为 100

  5.salary: 十进制型，总长度为 10，小数部分长度为 2

  6.hire_date: 日期型

  7.photo: 二进制型，用于存储员工照片

根据实际需求，数据类型和字段定义可以有所不同。在创建表时，需要注意表的命名、字段名的命名、数据类型的选择等，以便确保表结构的规范性和易于维护性。

10 SQL语句

10.1 SQL简介

  SQL（结构化查询语言）是一种用于关系数据库管理系统的标准化查询语言，主要用于数据库中数据的增删改查、表的创建与删除、表之间的关联和约束等方面。

10.2 SQL分类

  根据 SQL 语句的作用和用途，可将 SQL 分为以下几类：

  1.数据定义语言（DDL）：用于创建、修改和删除数据库、表、视图、索引、触发器等数据库对象。常用的 DDL 语句包括：CREATE、ALTER 和 DROP 等。

  2.数据操作语言（DML）：用于向数据库中的表中添加、修改或删除数据。常用的 DML 语句包括：SELECT、INSERT、UPDATE 和 DELETE 等。

  3.数据查询语言（DQL）：用于查询数据库中的数据，一般用于从一个或多个表中获取特定的数据集。DQL 语句中最常用的就是 SELECT 语句，可以通过 WHERE 子句和 JOIN 操作等方式查询出符合条件的数据。

  4.数据控制语言（DCL）：用于管理数据库用户和权限，可以对数据库用户进行授权或限制其访问权限。常用的 DCL 语句包括：GRANT 和 REVOKE 等。

  5.事务控制语言（TCL）：用于管理数据库中的事务，包括事务的开始、提交和回滚等操作。常用的 TCL 语句包括：COMMIT、ROLLBACK 和 SAVEPOINT 等。

  6.数据库管理语言（DAL）：用于管理数据库的结构和组件，包括备份和恢复数据库、数据压缩和加密、数据库复制等。常用的 DAL 语句包括：BACKUP、RESTORE 和 DBCC 等。

10.3 MySQL的DDL

  MySQL 的 DDL 是用于定义数据库、表、列、索引等数据库对象的语言。DDL 语句可以实现数据库的创建、修改和删除等，具体包括以下语句：

  1.创建数据库

CREATE DATABASE database_name;

  2.删除数据库

DROP DATABASE database_name;

  3.创建表

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY (one or more columns)
);

  4.修改表

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY column_name datatype;

  5.删除表

DROP TABLE table_name;

  6.创建索引

CREATE INDEX index_name ON table_name (column_name);

  7.删除索引

DROP INDEX index_name ON table_name;

DDL 语句可以通过 MySQL 命令行客户端或图形化客户端（如 MySQL Workbench）等工具执行。在执行 DDL 语句时，需要注意语法的正确性，避免错误操作导致数据库结构的损坏。同时，DDL 操作具有较高的权限，应该谨慎使用。

10.4 MySQL的DML

  DML 是 MySQL 中的一种语言，用于操作表中的数据，包括 SELECT、INSERT、UPDATE 和 DELETE 四种操作。

  1.SELECT：SELECT 语句用于从表中检索数据。可以使用 WHERE 子句指定条件，也可以使用 ORDER BY 子句排序结果。

SELECT * FROM employees WHERE age > 30 ORDER BY salary DESC;

  2.INSERT：INSERT 语句用于向表中插入新数据。需要指定表名和要插入的数据。如果要插入多行数据，可以使用 VALUES 关键字。

INSERT INTO employees (name, age, email, salary, hire_date) VALUES ('Tom', 25, 'tom@example.com', 5000.00, '2022-02-18');

  3.UPDATE：UPDATE 语句用于更新表中的数据。需要指定表名、要更新的字段和新的值，以及 WHERE 子句指定更新的条件。

UPDATE employees SET salary = 6000.00 WHERE id = 1;

  4.DELETE：DELETE 语句用于从表中删除数据。需要指定表名和 WHERE 子句指定要删除的数据。

DELETE FROM employees WHERE age < 25;

使用 DML 语句时，需小心使用 WHERE 子句和其他限制条件，以免意外删除或修改数据。

10.5 MySQL的DQL

  DQL 是 MySQL 中的一种语言，用于从表中检索数据。其中最常见的是 SELECT 语句：

insert into employees (name,age,email,salary,hire_date)
values 
('John Smith',34,'john.smith@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
 ('Jane Doe',34, 'jane.doe@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Bob Johnson',32, 'bob.johnson@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Mary Williams',45,'mary.williams@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('David Brown',23,'david.brown@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Sarah Davis',56,'sarah.davis@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Paul Wilson',34,'paul.wilson@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Lisa Jackson',67,'lisa.jackson@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Michael Lee',32,'michael.lee@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Jennifer Perez',51,'jennifer.perez@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('James Garcia',23,'james.garcia@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Elizabeth Rodriguez',28,'elizabeth.rodriguez@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Charles Hernandez',25,'charles.hernandez@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Jessica King',32,'jessica.king@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Anthony Wright',29,'anthony.wright@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Melissa Scott',25,'melissa.scott@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Kevin Green',21,'kevin.green@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Amanda Baker',54,'amanda.baker@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Richard Adams',21,'richard.adams@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Christina Carter',45,'christina.carter@example.com',FLOOR(RAND() * 10000),'2026-07-28'),
  ('Daniel Lewis',20,'daniel.lewis@example.com',FLOOR(RAND() * 10000),'2026-07-28'); 

  1.SELECT 所有列：SELECT * 语句用于检索表中的所有列。

SELECT * FROM employees;

  2.SELECT 指定列：可以使用 SELECT 语句选择表中的指定列。

SELECT name, age, email FROM employees;

  3.使用 WHERE 子句：WHERE 子句用于过滤检索结果。可以根据多个条件过滤数据。

SELECT name, age, email FROM employees WHERE age > 30 AND salary > 5000.00;

  4.使用 ORDER BY 子句：ORDER BY 子句用于按指定列对结果进行排序，可以按升序(ASC)或降序(DESC)排序。

SELECT name, age, email, salary FROM employees ORDER BY salary DESC;

  5.使用 LIMIT 子句：LIMIT 子句用于限制检索结果的数量。

SELECT name, age, email, salary FROM employees ORDER BY salary DESC LIMIT 10;

  DQL 还有许多其他功能，例如使用 GROUP BY 和 HAVING 子句进行分组、使用 JOIN 子句连接多个表等等。在使用 DQL 语句时，需要注意语句的性能和效率，避免对数据库造成不必要的负担。

11 MySQL的查询

11.1 条件查询where子句

  在 MySQL 中，WHERE 子句用于从表中选择满足条件的数据。以下是一些示例：

  1.基本用法：WHERE 子句可以与 SELECT 语句一起使用，以便从表中检索指定条件的数据。

# 以下示例返回所有薪水高于 5000 的员工的信息。
SELECT * FROM employees WHERE salary > 5000;

  2.AND 和 OR 运算符：WHERE 子句可以使用 AND 和 OR 运算符来检索满足多个条件的数据。

# 以下示例返回所有年龄大于 30 岁且薪水高于 5000 的员工的信息，或者返回所有年龄大于 30 岁或薪水高于 5000 的员工的信息。
SELECT * FROM employees WHERE age > 30 AND salary > 5000;
SELECT * FROM employees WHERE age > 30 OR salary > 5000;

  3.IN 运算符：IN 运算符用于从表中检索符合指定值列表的数据。

# 以下示例返回所有年龄为 25、30 或 35 的员工的信息。
SELECT * FROM employees WHERE age IN (25, 30, 35);

  4.BETWEEN 运算符：BETWEEN 运算符用于从表中检索在指定范围内的数据。

# 以下示例返回所有薪水在 5000 到 8000 之间的员工的信息。
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 8000;

  5.LIKE 运算符：LIKE 运算符用于从表中检索与指定模式匹配的数据。

# 以下示例返回所有名字以 "John" 开头的员工的信息。
SELECT * FROM employees WHERE name LIKE ‘John%';

WHERE 子句还有许多其他功能，例如使用 IS NULL 和 IS NOT NULL 来检索空值和非空值，使用通配符进行模糊匹配等等。

11.2 MySQL查询like

  在 MySQL 中，LIKE 运算符用于模糊查询满足指定模式的数据，通常用于字符串类型的字段。

  LIKE 运算符可以与通配符一起使用，通配符用于指定要匹配的模式。MySQL 中常用的通配符有两种：

  1.百分号（%）：表示任意字符，包括 0 个字符。

  2.下划线（_）：表示任意单个字符。

  1.匹配以指定字符开头的字符串

# 以下示例返回所有名字以 "Tom" 开头的员工的信息。
SELECT * FROM employees WHERE name LIKE 'Tom%';

  2.匹配以指定字符结尾的字符串

# 以下示例返回所有名字以 "son" 结尾的员工的信息。
SELECT * FROM employees WHERE name LIKE '%son';

  3.匹配包含指定字符的字符串

# 以下示例返回所有名字中包含 "Tom" 的员工的信息。
SELECT * FROM employees WHERE name LIKE '%Tom%';

  4.匹配指定长度的字符串

# 以下示例返回所有名字长度为 3 的员工的信息。
SELECT * FROM employees WHERE name LIKE '___';

使用 LIKE 运算符进行模糊查询可能会影响查询性能，特别是在处理大量数据时。因此，在使用 LIKE 运算符时，应尽量减少使用通配符，以提高查询效率。

11.3 MySQL聚合函数

在 MySQL 中，聚合函数用于在查询中对一组数据进行计算并返回单个结果。下面是 MySQL 支持的一些常见聚合函数：

聚合函数	解释
COUNT()	计算行数或非空值的数量	MAX()	返回数值列中的最大值
SUM()	计算数值列的总和	MIN()	返回数值列中的最小值
AVG()	计算数值列的平均值

-- 计算 employees 表中员工的总数
SELECT COUNT(*) FROM employees;

-- 计算 employees 表中薪水的总和
SELECT SUM(salary) FROM employees;

-- 计算 employees 表中薪水的平均值
SELECT AVG(salary) FROM employees;

-- 找到 employees 表中薪水最高的员工
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

-- 找到 employees 表中薪水最低的员工
SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

聚合函数通常是与 GROUP BY 子句一起使用的，以对每个分组计算结果。例如，以下查询将返回每个部门的员工数量：

# 新建字段department在hire_date后面
alter table employees add column department varchar(20) not null after hire_date;

# 修改id=1员工的部门为研发部
update employees set department = '研发部' where id = 1;

# 批量修改
update employees set department = '产品部' where id in(2,3,4,5);


SELECT department, COUNT(*) FROM employees GROUP BY department;

11.4 MySQL分组查询

  在 MySQL 中，GROUP BY 子句用于将结果集按照一个或多个列进行分组，并对每个分组应用一个聚合函数，从而计算每个分组的汇总信息。

  以下是使用 GROUP BY 的示例：

# 返回每个部门的平均工资。GROUP BY 子句按照 department 列进行分组，然后对每个分组使用 `AVG()` 聚合函数计算平均工资。
SELECT department, AVG(salary) FROM employees GROUP BY department;

GROUP BY 子句必须出现在 SELECT 语句中的聚合函数之后，而且查询中不能使用未在 GROUP BY 子句或聚合函数中的列。如果需要对分组进行过滤，可以使用 HAVING 子句。

# 返回平均工资大于 5000 的部门，`HAVING` 子句使用了 `AVG()` 聚合函数。
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

11.5 MySQL子查询

  MySQL 子查询是指一个 SQL 语句中嵌入另一个 SQL 语句，这个嵌入的 SQL 语句称为子查询，它用来为主查询提供数据。

  子查询可以嵌套到 SELECT、FROM、WHERE 等子句中，以便从嵌套的子查询中获取数据，然后将其传递给主查询。

  使用子查询查找工资高于平均工资的员工：

1.子查询 `(SELECT AVG(salary) FROM employees)` 返回了员工表中所有员工的平均工资
2.主查询则选择了工资高于平均工资的员工，并返回了这些员工的姓名和工资。

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

  子查询可以使用各种运算符和函数，例如 EXISTS、IN、ANY、ALL、MAX、MIN 等等，以及联合查询、交叉查询等高级查询技术，可以极大地扩展 SQL 查询的功能和灵活性。

create table students(
     id int not null auto_increment comment '学生ID',
     name varchar(50) not null comment '学生姓名',
     age int not null comment '学生年龄',
     grade int not null comment '高考成绩',
     school varchar(60) not null comment '录取院校',
     created_at timestamp default current_timestamp comment '创建时间',
     primary key(id),
     unique uk_name(name))
     engine=InnoDB default charset=utf8mb4 comment = '学生信息表';

insert into students (name,age,grade,school)
values ('刘志强',23,678,'北京大学'),
       ('王小宇',21,578,'西南交通大学'),
       ('樊晓',25,567,'西北大学'),
       ('罗晓勇',24,612,'西安电子科技大学'),
       ('刘程',22,695,'北京大学'),
       ('王大志',20,456,'西昌学院'),
       ('张红兵',22,432,'攀枝花学院'),
       ('张朴正',21,695,'哈尔滨工业大学'),
       ('刘刚',23,705,'哈尔滨工业大学');

  这个表包含了学生的 ID、姓名、年龄、分数、录取院校。可以使用子查询来查找年龄最大的学生：

select * from students where age = (select MAX(age) from students);

  子查询 (SELECT MAX(age) FROM students) 返回了 students 表中所有学生的最大年龄，主查询则选择了年龄等于这个最大值的学生，并返回了这些学生的所有信息。

11.6 MySQL联合查询

  MySQL 中的联合查询是指将多个 SELECT 查询语句的结果组合在一起，形成一个新的结果集。联合查询通常用于需要合并多个数据源的情况，或者需要从不同的表中检索相关数据的情况。

  联合查询使用 UNION 关键字连接多个 SELECT 查询语句。联合查询要求每个 SELECT 查询语句返回相同数量和类型的列，列的顺序也必须相同。例如，假设有两个表 table1 和 table2，它们包含相同数量和类型的列：

SELECT col1, col2, col3 FROM table1
UNION
SELECT col1, col2, col3 FROM table2;

  这个联合查询会将 table1 和 table2 的查询结果合并在一起，生成一个新的结果集。如果 table1 和 table2 中有相同的行，联合查询会自动去除重复的行。如果需要包括所有行（包括重复的行），可以使用 UNION ALL 关键字。

  联合查询的每个 SELECT 查询语句可以包含任意的 SELECT 子句、WHERE 子句、GROUP BY 子句、HAVING 子句和 ORDER BY 子句。

# 先从table1和table2中选择所有col3大于 10 的行，然后将它们合并在一起，并按照col1的升序排列结果集。

SELECT col1, col2 FROM table1 WHERE col3 > 10
UNION
SELECT col1, col2 FROM table2 WHERE col3 > 10
ORDER BY col1 ASC;

假设有两个表 employee_info 和 customers，包含以下数据：

employee_info 表：

create table employee_info(
     id int not null auto_increment comment '自增主键ID',
     emp_id int not null comment '员工编号',
     first_name varchar(50) not null comment '名',
     last_name varchar(50) not null comment '姓',
     department varchar(60) not null comment '部门',
     salary int not null comment '薪水',
     created_at timestamp default current_timestamp comment '创建时间',
     primary key(id),
     unique uk_emp_id(emp_id))
     engine=InnoDB default charset=utf8mb4 comment = '员工信息补充表';
     
INSERT INTO employee_info (emp_id, first_name, last_name, department, salary) 
VALUES (1001, 'John', 'Doe', 'Sales', 5000),
       (1002, 'Jane', 'Smith', 'HR', 6000),
       (1003, 'David', 'Lee', 'IT', 5500),
       (1004, 'Mary', 'Johnson', 'Finance', 7000);

customers 表：

CREATE TABLE customers (
    id int not null auto_increment comment '自增主键ID',
    cust_id int not null comment '客户编号',
    first_name varchar(50) not null comment '名',
    last_name varchar(50) not null comment '姓',
    email VARCHAR(100) not null comment '客户邮箱',
    phone VARCHAR(20) not null comment '客户联系方式',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_cust_id(cust_id))
    engine=InnoDB default charset=utf8mb4 comment = '客户信息表';
    
INSERT INTO customers (cust_id, first_name, last_name, email, phone) 
VALUES (1009, 'Alice', 'Cooper', 'alice.cooper@example.com', '555-123-4567'),
       (1010, 'Bob', 'Dylan', 'bob.dylan@example.com', '555-234-5678'),
       (1021, 'Charlie', 'Parker', 'charlie.parker@example.com', '555-345-6789'),
       (1075, 'Dave', 'Matthews', 'dave.matthews@example.com', '555-456-7890');

  使用联合查询将这两个表中的数据合并在一起。可以使用以下 SQL 语句：

select emp_id,first_name,last_name,department,salary 
from employee_info
union
select cust_id,first_name,last_name,null as department,null as salary
from customers;

  这个联合查询将 employee_info 和 customers 表的查询结果合并在一起，生成一个新的结果集。如果 employees 和 customers 中有相同的行，联合查询会自动去除重复的行。如果需要包括所有行（包括重复的行），可以使用 UNION ALL 关键字。

  在上述查询中，customers 表没有部门和薪水信息，因此使用 NULL 值填充。

11.7 MySQL连接查询

  在MySQL中，连接查询（join）是一种通过将两个或多个表中的行组合在一起的查询方式。连接查询可以从多个表中获取数据，能够更有效地组织和分析数据。

  MySQL支持不同类型的连接查询，包括内连接（inner join）、左连接（left join）、右连接（right join）和全外连接（full outer join）。

11.7.1 左连接查询

  左连接是查询两个或多个表的方式，返回左边表中的所有行，并且返回右边表中与左边表中的行匹配的行。如果右表中没有与左表中的行匹配的行，则返回NULL值。

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id;

  上述这个查询中，表1是左表，表2是右表。它会返回表1中的所有行，即使没有与表2中的行匹配的行。如果表2中没有与表1中的行匹配的行，则返回NULL值。

  左连接可以用来查找一个表中的所有行，即使在另一个表中没有匹配的行。这对于查找不完整的数据非常有用，或者需要获取所有的数据而不是仅仅匹配的。

CREATE TABLE customers (
    id int not null auto_increment comment '自增主键ID',
    cust_id int not null comment '客户编号',
    first_name varchar(50) not null comment '名',
    last_name varchar(50) not null comment '姓',
    email VARCHAR(100) not null comment '客户邮箱',
    phone VARCHAR(20) not null comment '客户联系方式',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_cust_id(cust_id))
    engine=InnoDB default charset=utf8mb4 comment = '客户信息表';
    
INSERT INTO customers (cust_id, first_name, last_name, email, phone) 
VALUES (1009, 'Alice', 'Cooper', 'alice.cooper@example.com', '555-123-4567'),
       (1010, 'Bob', 'Dylan', 'bob.dylan@example.com', '555-234-5678'),
       (1021, 'Charlie', 'Parker', 'charlie.parker@example.com', '555-345-6789'),
       (1075, 'Dave', 'Matthews', 'dave.matthews@example.com', '555-456-7890');


CREATE TABLE orders (
    id int not null auto_increment comment '自增主键ID',
    order_id int not null comment '订单编号',
    cust_id int not null comment '客户编号',
    product_name varchar(50) not null comment '产品名称',
    quantity int not null comment '产品数量',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_order_id(order_id))
    engine=InnoDB default charset=utf8mb4 comment = '订单信息表';


INSERT INTO orders (order_id, cust_id, product_name, quantity)
VALUES (1201, 1009, '小米汽车SU7', 1990),
       (1202, 1010, '吉利星瑞', 2300),
       (1203, 1021, '帝豪GS', 6789),
       (1204, 1075, '问界M9', 12378);

  使用左连接查询来查找每个客户及其订单，即使客户没有订单也可以显示出来。以下是使用左连接的查询：

select customers.first_name,orders.product_name,orders.quantity
from customers
left join orders
on customers.cust_id = orders.cust_id;

  使用LEFT JOIN来将两个表连接起来。左表是customers表，右表是orders表。ON子句中使用了cust_id列作为连接条件。这个查询会返回所有客户的信息，即使他们没有订单，也会显示出来。如果客户没有订单，orders表中的字段将会是NULL值。

11.7.2 右连接查询

  右连接查询返回右边表中的所有行，并且返回左边表中与右边表中的行匹配的行。如果左表中没有与右表中的行匹配的行，则返回NULL值。

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.id = table2.id;

  上述这个查询中，表1是左表，表2是右表。它会返回表2中的所有行，即使没有与表1中的行匹配的行。如果表1中没有与表2中的行匹配的行，则返回NULL值。

  虽然使用右连接和左连接可以返回不同的结果集，但它们是等价的。也就是说，使用右连接可以得到与左连接相同的结果集，只要交换左表和右表的位置，并把连接条件从ON子句中移到WHERE子句中。

SELECT *
FROM table2
RIGHT JOIN table1
ON table2.id = table1.id
WHERE table1.id IS NULL;

  上述这个查询中，首先使用右连接将表2和表1连接起来，然后将连接条件从ON子句中移到WHERE子句中，并将条件反转。最后，使用IS NULL操作符筛选出在左表中没有匹配的行，即返回右表中所有没有匹配行的结果。这个查询返回与左连接相同的结果集。

  使用右连接查询来查找每个客户及其订单，即使没有订单也可以显示出来。以下是使用右连接的查询：

select
    orders.product_name,
    orders.quantity,
	customers.first_name
from
	customers
right join orders
on
	orders.cust_id = customers.cust_id;

  上述这个查询中，使用了RIGHT JOIN来将两个表连接起来。右表是customers表，左表是orders表。在ON子句中使用cust_id列作为连接条件。这个查询会返回所有客户的信息，即使他们没有订单，也会显示出来。如果客户没有订单，orders表中的字段将会是NULL值。

11.7.3 全连接查询

  在MySQL中，全连接（full join）是一种连接查询，返回左右两个表中所有的行，如果两个表中的行没有匹配，则返回NULL值。

  MySQL中并没有提供FULL JOIN关键字，可以使用LEFT JOIN和UNION操作符来实现FULL JOIN。使用LEFT JOIN将左表和右表连接起来，并使用UNION操作符将右表中不在左表中的行连接起来，得到所有行。

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id
UNION
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.id = table2.id
WHERE table1.id IS NULL;

  这个查询中，首先使用LEFT JOIN将表1和表2连接起来，并得到匹配的行。然后，使用UNION操作符将表2中不在表1中的行连接起来。最后，使用RIGHT JOIN将表2和表1连接起来，并得到表1中不在表2中的行，并使用WHERE子句将这些行筛选出来。这个查询会返回所有行，包括两个表中的所有行。如果两个表中的行没有匹配，则会返回NULL值。

  由于FULL JOIN并不是MySQL原生支持的，这种实现FULL JOIN的方法可能不是最高效的，因此在处理大数据量的查询时，需要采用其他的方法来实现FULL JOIN。

  使用全连接查询查找每个客户及其订单，即使没有订单也可以显示出来。

select customers.first_name, orders.product_name, orders.quantity
from orders
left join customers
on orders.cust_id = customers.cust_id
union 
select customers.first_name, orders.product_name, orders.quantity
from orders
right join customers
on orders.cust_id = customers.cust_id 
where orders.cust_id is null;

  在这个查询中，使用了LEFT JOIN和RIGHT JOIN，以实现全连接。在ON子句中使用cust_id列作为连接条件。这个查询会返回所有客户的信息，即使他们没有订单，也会显示出来。如果客户没有订单，orders表中的字段将会是NULL值。

11.7.4 内连接查询

  在MySQL中，内连接（inner join）是一种连接查询，它只返回左右两个表中匹配的行。内连接可以使用JOIN关键字或INNER JOIN关键字实现。

SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id;

  在这个查询中，使用JOIN关键字将表1和表2连接起来，并使用ON子句指定连接条件。这个查询会返回两个表中匹配的行，如果两个表中的行没有匹配，则不会返回。

  还可以使用INNER JOIN关键字来实现内连接。以下是使用INNER JOIN关键字实现内连接的示例查询：

SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;

  这个查询和使用JOIN关键字实现内连接的查询效果是一样的，只不过使用了INNER JOIN关键字而已。

  内连接只返回匹配的行，而不返回没有匹配的行。如果你需要返回所有行，包括没有匹配的行，可以考虑使用外连接（outer join），包括左外连接（left join）、右外连接（right join）和全连接（full join）等。

select customers.first_name, orders.product_name, orders.quantity
from customers
join orders
on customers.cust_id = orders.cust_id;

  在这个查询中，使用JOIN关键字和ON子句指定了连接条件。查询会返回所有匹配的行，也就是每个客户和他们的订单信息。如果一个客户没有订单，他不会被包含在结果集中。

12 MySQL函数

  MySQL函数可以分为以下几类：

  1.聚合函数：聚合函数对一组值进行计算，并返回单个值作为结果。这些函数包括COUNT、SUM、AVG、MAX和MIN等。

  2.数学函数：数学函数对数字值进行计算，例如ABS、CEIL、FLOOR、ROUND和TRUNCATE等。

  3.字符串函数：字符串函数对字符串进行操作，例如CONCAT、SUBSTR、LENGTH、LOWER和UPPER等。

  4.日期和时间函数：日期和时间函数对日期和时间值进行计算和操作，例如NOW、DATE、YEAR、MONTH、DAY、HOUR、MINUTE和SECOND等。

  5.逻辑函数：逻辑函数用于执行逻辑运算，例如AND、OR和NOT等。

  6.条件函数：条件函数用于根据指定的条件返回不同的结果，例如IF、CASE和COALESCE等。

  7.加密函数：加密函数用于对数据进行加密和解密，例如MD5、SHA1和AES_ENCRYPT等。

  8.其他函数：其他函数包括流程控制函数、系统函数和用户自定义函数等。

  这些函数可以在MySQL中方便地进行调用和使用，可以使数据库操作更加高效和便捷。

12.1 聚合函数

  为了验证MySQL的聚合函数，需要准备一些数据，用于存储订单信息：

# 新建字段price在quantity后面
alter table orders add column price decimal(10,2) not null after quantity;

# 批量修改数据
update orders 
set price = case id
  when 5 then 10.99 
  when 6 then 5.99
  when 7 then 12.99
  when 8 then 7.50
end 
where id in(5,6,7,8);

  使用聚合函数来计算订单信息的总数、平均价格、最高价格和最低。

select count(*) as total_orders,
       avg(price) as avg_price,
       max(price) as max_price,
       min(price) as min_price
from orders;

  在这个查询中，使用了聚合函数COUNT、AVG、MAX和MIN计算总订单数、平均价格、最高价格和最低价格。使用AS关键字给计算结果取了别名，使查询结果更加易读和直观。

12.2 字符串函数

  为了验证MySQL的字符串函数，需要准备一些数据，并创建一个表。以下是一个示例表student_str，用于存储学生信息：

create table student_str(
     student_id int not null auto_increment comment '学生ID',
     first_name varchar(50) not null comment '名',
     last_name varchar(50) not null comment '姓',
     email varchar(50) not null comment '电子邮件',
     phone varchar(20) not null comment '联系方式',
     created_at timestamp default current_timestamp comment '创建时间',
     primary key(student_id),
     unique uk_name(email))
     engine=InnoDB default charset=utf8mb4 comment = '学生信息扩展表';


INSERT INTO student_str (student_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Doe', 'johndoe@example.com', '555-1234'),
       (2, 'Jane', 'Smith', 'janesmith@example.com', '555-5678'),
       (3, 'Bob', 'Johnson', 'bjohnson@example.com', '555-9012');

  使用字符串函数来操作学生信息的姓名、电子邮件和电话号码。以下是使用字符串函数的查询：

select CONCAT(first_name, '', last_name) as full_name,
       UPPER(email) as upper_email,
       SUBSTR(phone, 1, 3) as phone_prefix
from student_str;

  这个查询中，使用了字符串函数CONCAT、UPPER和SUBSTR来操作学生信息的姓名、电子邮件和电话号码。使用AS关键字给计算结果取了别名，可以使查询结果更加易读和直观。

12.3 日期时间函数

  使用日期时间函数来操作员工信息的入职日期和工资。

select date_format(hire_date, '%Y-%m-%d') as formatted_date,
       year(hire_date) as hire_date,
       month(hire_date) as hire_month,
       day(hire_date) as hire_day,
       salary * 12 as annual_salary
from employees;

  这个查询中，使用了日期时间函数DATE_FORMAT、YEAR、MONTH和DAY来操作员工信息的入职日期和工资。使用AS关键字给计算结果取了别名，可以使查询结果更加易读和直观。

12.3 逻辑函数

  为了验证MySQL的逻辑函数，需要准备一些数据，并创建一个表。以下是一个示例表products，用于存储产品信息：

CREATE TABLE products (
    id int not null auto_increment comment '自增主键ID',
    product_id int not null comment '产品ID',
    product_name VARCHAR(50) not null comment '产品名称',
    unit_price DECIMAL(10,2) not null comment '产品单价',
    in_stock BOOLEAN comment '产品是否有货',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_name(product_name))
    engine=InnoDB default charset=utf8mb4 comment = '存储产品信息表';   
    
);


insert into products (product_id, product_name, unit_price, in_stock)
values (1001,'遥控汽车',980.00,true),
       (1002,'遥控飞机',767.00,true),
       (1003,'变形金刚',435.00,true),
       (1004,'坦克模型',1880.00,false),
       (1005,'直升机模型',1780.00,false);

  使用逻辑函数来操作产品信息的库存状态。

SELECT product_name,
       IF(in_stock, 'In stock', 'Out of stock') AS stock_status
FROM products;

  在这个查询中，使用了逻辑函数IF来操作产品信息的库存状态。使用AS关键字给计算结果取了别名，可以使查询结果更加易读和直观。

12.4 条件函数

  为了验证MySQL的条件函数，需要准备一些数据，并创建一个表。以下是一个示例表stu_info，用于存储学生信息：

create table stu_info (
    id int not null auto_increment comment '自增主键ID',
    student_id int not null comment '学生编码',
    first_name varchar(50) not null comment '名',
    last_name varchar(50) not null comment '姓',
    gender varchar(50) not null comment '性别',
    age int not null comment '年龄',
    score decimal(10,2) not null comment '学生成绩',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_student_id(student_id))
    engine=InnoDB default charset=utf8mb4 comment = '学生信息扩展表';

INSERT INTO stu_info (student_id, first_name, last_name, gender, age, score)
VALUES (6745356, 'John', 'Doe', 'Male', 18, 80.00),
       (9087421, 'Jane', 'Smith', 'Female', 19, 85.00),
       (5347890, 'Bob', 'Johnson', 'Male', 20, 90.00);

  使用条件函数来操作学生信息的成绩情况。

select first_name,
       last_name,
       case
       	   when score >= 90 then 'A'
       	   when score >= 80 then 'B'
       	   when score >= 70 then 'C'
       	   else 'F'     	        	       	   
       end as grade
from stu_info;

  在这个查询中，使用了条件函数CASE来操作学生信息的成绩情况。使用AS关键字给计算结果取了别名，可以使查询结果更加易读和直观。

13 用户、角色、权限管理

13.1 MySQL DCL

  在MySQL中，DCL代表“数据控制语言”，用于控制和管理用户访问数据库的权限。DCL命令包括以下几个关键字：

  1.GRANT：用于向用户或用户组授予访问数据库的特定权限。

  2.REVOKE：用于从用户或用户组中撤销访问数据库的特定权限。

  3.DENY：用于拒绝用户或用户组访问数据库的特定权限。

  上述关键字可以用于控制用户访问数据库的权限，以确保数据库的安全性和完整性。以下是一些常用的DCL命令：

  1.GRANT SELECT ON database.* TO user@localhost; // 授予用户在database数据库中查询数据的权限

  2.REVOKE INSERT ON database.* FROM user@localhost; // 从用户中撤销在database数据库中插入数据的权限

  3.DENY DROP ON database.* TO user@localhost; // 拒绝用户在database数据库中删除数据表的权限

  上述命令需要具有适当的特权和权限才能使用。只有具有管理员权限的用户才能授予、撤销和拒绝其他用户的访问权限。

  为了验证MySQL的DCL命令，需要准备一些数据并创建一个具有特定权限的用户。以下是一个示例数据库students，用于存储学生信息：

CREATE DATABASE students;

USE students;

create table stu_mess (
    id int not null auto_increment comment '自增主键ID',
    student_id int not null comment '学生编码',
    first_name varchar(50) not null comment '名',
    last_name varchar(50) not null comment '姓',
    gender varchar(50) not null comment '性别',
    age int not null comment '年龄',
    score decimal(10,2) not null comment '学生成绩',
    created_at timestamp default current_timestamp comment '创建时间',
    primary key(id),
    unique uk_student_id(student_id))
    engine=InnoDB default charset=utf8mb4 comment = '学生信息表';

INSERT INTO stu_mess (student_id, first_name, last_name, gender, age, score)
VALUES (9001, 'John', 'Doe', 'Male', 18, 80.00),
       (9002, 'Jane', 'Smith', 'Female', 19, 85.00),
       (9003, 'Bob', 'Johnson', 'Male', 20, 90.00);

  以上SQL语句创建了一个名为students的数据库和一个名为stu_mess的表，并插入了一些示例数据。

  创建一个新用户，并授予该用户对students数据库的查询权限。

create user 'testuser'@'localhost' identified by 'testpass';

grant select on students.* to 'testuser'@'localhost';

  使用CREATE USER命令创建了一个名为testuser的新用户，并使用GRANT命令授予该用户对students数据库的查询权限。

  使用新创建的用户testuser连接到MySQL，并查询students数据库中的数据。以下是使用testuser用户进行查询的示例：

  在提示符中输入密码testpass，然后使用以下命令查询student表中的数据：

  如果一切正常，testuser应该可以成功连接到MySQL，并查询students数据库中的数据。演示了使用DCL命令控制用户访问数据库的权限，以确保数据库的安全性和完整性。

13.2 MySQL用户管理

  MySQL允许创建多个用户，每个用户都有自己的用户名和密码。

  1.创建用户：要创建新用户，可以使用CREATE USER语句：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

  上述语句将创建一个名为username的用户，该用户只能从本地主机（localhost）登录，并设置了密码为password。可以使用CREATE USER创建任意数量的用户。

  2.删除用户：要删除现有用户，可以使用DROP USER语句：

DROP USER 'username'@'localhost';

  上述语句将从MySQL服务器中删除名为username的用户。

  3.授予权限：要授予用户对特定数据库或表的访问权限，可以使用GRANT语句：

GRANT permission ON database.table TO 'username'@'localhost';

GRANT privilege ON object TO 'username'@'localhost';

  上述语句将授予名为username的用户在数据库database中的table表上执行permission操作的权限。可以使用GRANT授予不同的权限，如SELECT、INSERT、UPDATE和DELETE等。

  4.撤销权限：要从用户中撤销访问权限，可以使用REVOKE语句：

REVOKE permission ON database.table FROM 'username'@'localhost';

REVOKE privilege ON object FROM 'username'@'localhost';

  上述语句将从名为username的用户中撤销在database数据库的table表上执行permission操作的权限。

  5.修改用户密码：要更改现有用户的密码，可以使用SET PASSWORD语句：

SET PASSWORD FOR 'username'@'localhost' = 'newpassword';

  上述语句将更改名为username的用户的密码为newpassword。

  6.查看现有用户：要查看MySQL服务器上的现有用户，可以使用SELECT语句从mysql.user表中检索用户信息：

SELECT user, host FROM mysql.user;

  上述语句将返回MySQL服务器上所有用户的用户名和主机名。

  使用上述命令可以管理和控制MySQL用户的访问权限，并确保数据库的安全性和完整性。

13.3 角色管理

  MySQL权限管理是控制MySQL用户对数据库、表、列等对象的访问权限的过程。MySQL提供了多种权限管理方法来保护数据库中的数据。

  MySQL 8.0引入了角色管理功能，通过角色来管理用户的权限。角色是一组权限的集合，可以将多个用户赋予同一个角色，简化了权限管理的复杂度。

-- 创建角色
CREATE ROLE role_name;

-- 删除角色
DROP ROLE role_name;

-- 给角色授权
GRANT privilege ON object TO role_name;

-- 收回角色授权
REVOKE privilege ON object FROM role_name;

13.4 数据库和表权限管理

  在MySQL中，可以将权限授予到数据库或表级别。

-- 授予用户对数据库的访问权限
GRANT privilege ON database_name.* TO 'username'@'localhost';

-- 授予用户对表的访问权限
GRANT privilege ON database_name.table_name TO 'username'@'localhost';

-- 收回用户对数据库的访问权限
REVOKE privilege ON database_name.* FROM 'username'@'localhost';

-- 收回用户对表的访问权限
REVOKE privilege ON database_name.table_name FROM 'username'@'localhost';

  MySQL提供了多种数据访问控制机制，如视图、存储过程、触发器、事件等，可以通过这些机制来控制对数据的访问。例如，可以使用视图来隐藏敏感数据，使用存储过程来限制对数据的修改。MySQL提供了多种权限管理和数据访问控制机制，以保护数据库中的数据。管理员可以根据实际需求来选择适合自己的权限管理方法和数据访问控制机制。

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
  id int not null AUTO_INCREMENT PRIMARY KEY comment '自增主键ID',
  name VARCHAR(50) not null comment '用户名',
  email VARCHAR(50) not null comment '用户邮箱',
  created_at timestamp default current_timestamp comment '创建时间',
  unique uk_name(name))
  engine=InnoDB default charset=utf8mb4 comment = '用户信息表';


INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');
  
  
CREATE TABLE orders (
  id int not null AUTO_INCREMENT PRIMARY KEY comment '自增主键ID',
  user_id int not null comment '用户ID',
  product VARCHAR(50) not null comment '产品名称',
  price decimal(10,2) not null comment '产品价格',
  created_at timestamp default current_timestamp comment '创建时间',
  unique uk_user_id(user_id))
  engine=InnoDB default charset=utf8mb4 comment = '订单信息表';


INSERT INTO orders (user_id, product, price) VALUES
  (1, 'iPhone', 999.99),
  (7, 'iPad', 499.99),
  (2, 'MacBook', 1499.99),
  (3, 'Apple Watch', 199.99);

  创建一些用户和角色，并给它们授予不同的权限。

-- 创建用户
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'charlie'@'localhost' IDENTIFIED BY 'password';

-- 创建角色
CREATE ROLE sales;
CREATE ROLE finance;

-- 给角色授权
GRANT SELECT ON testdb.users TO sales;
GRANT SELECT, INSERT ON testdb.orders TO sales;
GRANT SELECT, UPDATE, DELETE ON testdb.orders TO finance;

-- 给用户授权
GRANT sales TO 'alice'@'localhost';
GRANT sales TO 'bob'@'localhost';
GRANT finance TO 'charlie'@'localhost';

  上面的代码创建了三个用户和两个角色。其中，alice和bob都属于sales角色，而charlie属于finance角色。此外，sales角色被授予了对users表的SELECT权限，以及对orders表的SELECT和INSERT权限；finance角色被授予了对orders表的SELECT、UPDATE和DELETE权限。最后，我们将角色分别授予给了对应的用户。

  测试不同用户和角色对数据的访问权限。例如，可以用alice的账户查询orders表中的数据，只能看到自己的订单，而不能修改或删除数据：

USE testdb;

SELECT * FROM orders WHERE user_id = 1; -- 可以查询自己的订单

INSERT INTO orders (user_id, product, price) VALUES (15, 'iMac', 1999.99); -- 可以插入自己的订单

UPDATE orders SET price = 899.99 WHERE id = 1; -- 无法修改其他用户的订单

DELETE FROM orders WHERE id = 1; -- 无法删除其他用户的订单

  使用finance角色来修改和删除订单表中的数据：

USE testdb;

SELECT * FROM orders; -- 可以查询所有订单

UPDATE orders SET price = price * 0.9 WHERE product = 'iPhone'; -- 可以修改订单价格

DELETE FROM orders WHERE price > 1000; -- 可以删除价格高于1000的订单

14 MySQL约束

  MySQL中的约束是用来限制表中数据的有效性和完整性的规则。确保了数据的正确性和一致性，并帮助保持数据库中的数据质量。MySQL支持以下类型的约束：

  1.主键约束（PRIMARY KEY）：用于确保表中的每行都具有唯一的标识符。

  2.外键约束（FOREIGN KEY）：用于确保表之间的关系的完整性。

  3.唯一约束（UNIQUE）：用于确保表中的某一列或一组列的值是唯一的。

  4.检查约束（CHECK）：用于确保表中的数据符合指定的条件。

14.1 主键约束

# 创建带有主键约束的表
CREATE TABLE students (
    id INT not null auto_increment PRIMARY KEY comment '自增主键ID',
    name VARCHAR(50) not null comment '学生姓名',
    age INT not null comment '学生年龄',
    created_at timestamp default current_timestamp comment '创建时间')
    engine=InnoDB default charset=utf8mb4 comment = '学生信息表';
    
# 验证主键约束
INSERT INTO students (id, name, age) 
VALUES (1, 'John', 20),
       (2, 'Jane', 22),
       (3, 'Bob', 19),
       (3, 'Mary', 21); -- 这行会导致主键冲突错误

14.2 外键约束

-- 1. 创建客户主表 customers
CREATE TABLE customers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '客户主键ID',
    name VARCHAR(50) NOT NULL COMMENT '客户姓名',
    phone VARCHAR(20) COMMENT '联系电话',
    address VARCHAR(255) COMMENT '地址',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户信息表';

-- 2. 创建订单表 orders，关联客户表（外键约束）
CREATE TABLE `orders` (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键ID',
    customer_id INT NOT NULL COMMENT '客户编号',
    order_date DATE NOT NULL COMMENT '订单日期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    -- 外键：customer_id 关联 customers 的id
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '订单信息表';


# 验证外键约束
INSERT INTO customers (id, name) 
VALUES (1, 'John'),
       (2, 'Jane');
INSERT INTO orders (id, customer_id, order_date) 
VALUES (1, 1, '2022-01-01'),
       (2, 3, '2022-02-01');   -- 这行会导致外键约束错误

外键约束生效：customer_id=3，但是 customers 表里只有 id=1 和 id=2 的客户，不存在id=3。
外键规则：订单的客户编号，必须在客户主表预先存在，防止脏数据。

14.3 唯一约束

# 创建带有唯一约束的表
CREATE TABLE employees (
    id INT not null auto_increment PRIMARY KEY comment '员工主键ID',
    name VARCHAR(50) not null comment '员工电话',
    email VARCHAR(50) not null comment '员工邮箱',
    phone VARCHAR(15) not null comment '员工联系方式',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    unique uk_email(email))
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '员工信息表';
  
  
# 验证唯一约束
INSERT INTO employees (id, name, email, phone) 
VALUES (1, 'John', 'john@example.com', '123-456-7890'),
       (2, 'Jane', 'jane@example.com', '234-567-8901');
       (3, 'Bob', 'john@example.com', '345-678-9012'); -- 这行会导致唯一约束错误

14.4 检查约束

# 创建带有检查约束的表
CREATE TABLE products (
    id INT not null auto_increment PRIMARY KEY comment '产品主键ID',
    name VARCHAR(50) not null comment '产品名称',
    price DECIMAL(10, 2) not null comment '产品价格',
    quantity INT not null comment '产品数量',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    CHECK (price > 0),
    CHECK (quantity >= 0))
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '员工信息表';
    
    
# 验证检查约束
INSERT INTO products (id, name, price, quantity) 
VALUES (1, 'Product 1', 10.99, 100),
       (2, 'Product 2', -5.99, 50), -- 这行会导致检查约束错误
       (3, 'Product 3', 19.99, -10);

  约束可以在表创建时定义，也可以在表创建后使用ALTER TABLE语句进行修改。

14.5 NULL 和 NOT NULL 约束

  在 MySQL 中，可以使用 NULL 和 NOT NULL 约束来定义列中是否允许插入 NULL 值。NULL 值指的是缺少值或不适用的值，它与空字符串或空格不同。

  当在表中定义一个列时，可以使用以下语法定义该列是否允许为 NULL：

column_name data_type NULL | NOT NULL;

  如果没有指定 NULL 或 NOT NULL 约束，则该列默认允许为 NULL。

  创建一个包含允许为 NULL 和不允许为 NULL 的列的表：

CREATE TABLE test_table (
    id INT NOT NULL auto_increment primary key comment '主键ID',
    name VARCHAR(50) NULL comment '名字',
    age INT NOT NULL comment '年龄',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间')
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '测试表';    

  id 和 age 列被定义为 NOT NULL，意味着不能为 NULL。而 name 列被定义为允许为 NULL。

  在插入数据时，可以指定 NULL 值来填充允许为 NULL 的列：

insert into test_table (id, name, age) values (1,null,20);

  当尝试将 NULL 值插入不允许为 NULL 的列时，将会收到错误消息。

15 Default关键字

  在 MySQL 中，DEFAULT 关键字用于指定当插入一条记录时，如果没有为某个列指定值，该列应使用的默认值。如果未指定默认值，则该列将默认为 NULL。

  创建包含默认值的列的表：

CREATE TABLE test_table (
    id INT NOT NULL auto_increment primary key comment '主键ID',
    name VARCHAR(50) not null default 'Anonymous' comment '名字',
    age INT NOT NULL comment '年龄',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间')
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '测试表'; 

  name 列被定义为具有默认值 ‘Anonymous’。当在插入数据时未为该列指定值时，将使用默认值 ‘Anonymous’。

INSERT INTO test_table (id, age) VALUES (1, 20);

  如果需要更改默认值，可以使用 ALTER TABLE 语句来更改列的默认值：

alter table test_table alter column name set default 'Unknown';

  删除默认值，可以使用以下语法：

alter table test_table alter column name drop default;

16 MySQL数据库设计

16.1 数据库设计

  数据库设计是指在实现数据库应用程序之前，对数据库的结构和逻辑进行规划和设计的过程。数据库设计涉及到从业务需求中识别出数据实体、属性、关系和约束，以及设计适当的表结构、视图、索引、触发器、存储过程和其他数据库对象。好的数据库设计能够提高数据访问的效率和数据的安全性，并能更好地支持业务需求。

  数据库设计的最佳实践：

  1.识别和建模实体：从业务需求中识别出需要存储的实体，定义实体的属性，建立实体之间的关系。

  2.规范化表结构：将数据分解为多个表，避免数据冗余和数据更新异常，提高数据的一致性和完整性。

  3.设计适当的数据类型：选择适当的数据类型和长度，避免浪费存储空间和查询性能。

  4.设计主键和外键：为每个表定义主键，确保每条记录的唯一性，定义外键建立表之间的关系。

  5.设计索引：为经常被查询的列创建索引，加快查询性能。

  6.设计视图：为常用的查询创建视图，提高查询的重用性和可读性。

  7.设计存储过程和触发器：将业务逻辑封装在存储过程和触发器中，提高数据的安全性和完整性。

  8.设计备份和恢复策略：定期备份数据库，并测试备份和恢复策略，确保数据的安全性和可靠性。

16.2 设计工具

  数据库设计工具是一种帮助数据库开发人员进行数据库设计和管理的软件。这些工具提供了一个图形化界面来建模、维护和管理数据库。以下是几种常用的数据库设计工具：

  1.MySQL Workbench：MySQL官方提供的免费数据库设计工具，可用于设计和管理MySQL数据库。

  2.Oracle SQL Developer：Oracle公司提供的免费数据库设计工具，支持多种数据库，包括Oracle、MySQL、Microsoft SQL Server等。

  3.ER/Studio：ER/Studio是一款商业数据库设计工具，提供全面的数据建模和管理功能，支持多种数据库。

  4.Navicat：Navicat是一款商业数据库管理工具，支持多种数据库，包括MySQL、Oracle、Microsoft SQL Server、PostgreSQL等。

  5.Toad for Oracle：Toad for Oracle是一款商业数据库管理工具，主要用于管理和维护Oracle数据库。

16.3 ER图

  ER实体关系图，是一种用于表示实体之间关系的图形化工具。ER图通常用于数据库设计过程中，可以帮助开发人员建立清晰的数据模型，并描述实体之间的关系。

  在ER图中，实体通常用矩形表示，属性用椭圆形表示，关系用菱形表示。实体和属性之间用直线连接，表示实体和属性之间的关系；实体和关系之间用直线连接，表示实体和关系之间的关系；关系和属性之间也用直线连接，表示关系和属性之间的关系。

  ER图常用于设计关系型数据库，用于描述数据库的结构和约束。通过ER图，开发人员可以更好地理解数据库中的实体和关系，提高数据库的可维护性和扩展性。 

  ER图可以手动绘制，也可以使用数据库设计工具来绘制。在设计ER图时，需要遵循一些基本原则，例如实体之间的关系应该是可靠的，具有明确的定义和规则。

17 MySQL数据库三范式

  数据库设计三范式（Normalization）是指对关系数据库的设计进行规范化，以减少数据冗余、提高数据一致性、避免数据异常等问题，保证数据的有效性和完整性。三范式是一个逐步拆分数据表的过程，其目的是消除重复数据，并使得数据表内的每个属性都只与主键直接相关。

  三范式的具体定义如下：

  1.第一范式（1NF）：数据表中的每个属性都是原子性的，即不可再分割。如果数据表中某个属性可以分为更小的子属性，就需要拆分成一个新的数据表。

  2.第二范式（2NF）：数据表中的非主键属性都必须完全依赖于主键，而不是依赖于主键的一部分。如果一个数据表中有多个主键，非主键属性必须与所有主键相关，而不能只与其中一部分主键相关。

  3.第三范式（3NF）：数据表中的非主键属性不依赖于其他非主键属性。如果有两个非主键属性之间存在依赖关系，需要将其拆分成两个数据表。

  设计一个符合三范式的数据库模型可以避免冗余和不一致性数据，并能提高数据操作的效率和可靠性。但是，过度的规范化可能会导致数据库结构复杂，难以维护和查询。在设计数据库时，应该根据具体的应用场景和数据需求，权衡规范化和性能的关系，以获得最佳的设计方案。

  为了初始化数据并验证数据库的三范式，可以考虑以下示例数据：

  假设要创建一个数据库来管理公司的员工信息。可以创建一个包含以下表的关系型数据库：

员工表（Employee）与部门表（Department）：

员工编号	姓名	部门编号	职位		部门编号	部门名称	部门经理编号
1	张三	101	经理		101	销售部	1
2	李四	102	经理		102	研发部	2
3	王五	102	员工

验证第一范式（1NF）

  在第一范式中，每个表中的每个字段都应该是原子的，不可再分的。上述表格中，每个字段都是原子的，因此符合第一范式。

验证第二范式（2NF）

  在第二范式中，每个表中的非主键字段都应该完全依赖于主键。上述表格中，员工表中的部门编号和部门表中的部门编号都是主键，而职位字段只依赖于员工表中的部门编号，因此符合第二范式。

验证第三范式（3NF）

  在第三范式中，每个表中的非主键字段都不应该依赖于其他非主键字段。上述表格中，员工表中的部门编号和部门表中的部门编号都是主键，而部门名称和部门经理编号只依赖于部门编号，因此符合第三范式。

18 MySQL的事务

18.1 MySQL事务简介

  MySQL事务是一系列数据库操作的集合，这些操作被视为一个单独的工作单元，要么全部成功完成，要么全部失败回滚。

  在MySQL中，可以使用以下语句来开始和结束一个事务：

  1.开始事务：START TRANSACTION 或 BEGIN

  2.提交事务：COMMIT

  3.回滚事务：ROLLBACK

  当一个事务开始时，所有的SQL语句都将被保存在缓冲区中，而不是立即执行。只有当COMMIT语句被执行时，MySQL才会将缓冲区中的SQL语句提交到数据库中。如果在事务执行期间出现错误或者ROLLBACK语句被执行，所有在缓冲区中的SQL语句都会被撤销，数据库状态会回滚到事务开始前的状态。

  使用事务可以确保数据库的一致性和完整性。如果一个事务中的任何一条SQL语句执行失败或者事务被回滚，那么数据库的状态就不会被破坏。这使得MySQL事务在高并发和复杂业务场景下非常有用，可以有效地保证数据的正确性和可靠性。

18.2 提交事务

# 创建student表的SQL语句
CREATE TABLE student (
  id INT not null auto_increment PRIMARY KEY comment '自增主键ID',
  name VARCHAR(50) not null comment '学生姓名',
  age INT not null comment '学生年龄',
  created_at timestamp default current_timestamp comment '创建时间',
  unique uk_name(name))
  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '学生信息表';
  
  
# 提交事务
START TRANSACTION;
INSERT INTO student (id, name, age) VALUES (1, 'John', 20);
INSERT INTO student (id, name, age) VALUES (2, 'Mary', 22);
INSERT INTO student (id, name, age) VALUES (3, 'Tom', 21);
COMMIT;

  使用START TRANSACTION语句来开启一个事务，然后执行三个INSERT语句往student表中插入三个学生的信息。最后，使用COMMIT语句来提交这个事务。

  如果在执行这个事务的过程中，任何一个INSERT语句执行失败（比如插入了一个重复的学生ID），那么整个事务将会被回滚，student表的数据将会回到事务开始之前的状态。这样，使用事务来管理数据库操作，可以确保数据的一致性和完整性，避免了数据操作过程中的错误和异常情况。

18.3 回滚事务

  尝试向student表中插入一个重复的学生ID，导致事务执行失败并回滚。

  注明：Oracle：一条SQ报错，整个事务直接失效，后续不能执行

  MySQL InnoDB：SQL报错不等于事务终止！事务继续运行。

# 通过以下语句检查student表的内容，会返回我们之前插入的三个学生记录。
SELECT * FROM student;

# 执行以下SQL语句往student表中插入一个重复的学生ID
START TRANSACTION;
INSERT INTO student (id, name, age) VALUES (1, 'David', 19);
INSERT INTO student (id, name, age) VALUES (4, 'Lisa', 20);
COMMIT;

  将第一个学生的ID设置为1，这是我们之前插入的学生的ID。因此，第一个INSERT语句会失败，整个事务不会因为第一个操作失败而回滚。

  执行完上述SQL语句后，我们可以再次执行以下SELECT语句来检查student表的内容：

SELECT * FROM student;

  返回插入的4个学生记录，新插入的id为4的学生的记录会被添加到表中。

  这个例子说明了MySQL事务的回滚功能：当事务中的任何一条SQL语句执行失败时，整个事务不会被回滚。

18.4 数据库事务的特性

  数据库事务具有以下四个特性，通常被称为ACID特性：

  1.原子性（Atomicity）：一个事务中的所有操作要么全部完成，要么全部不完成，事务的执行是一个不可分割的原子操作。

  2.一致性（Consistency）：事务执行之前和之后，数据库的完整性约束没有被破坏。也就是说，一个事务执行之前和之后，数据库中的数据必须保持一致，如果事务执行失败，则数据必须回滚到执行事务前的状态。

  3.隔离性（Isolation）：事务的执行不会被其他事务干扰，事务的执行结果与其他事务是隔离的。每个事务都应该认为它是唯一在数据库上运行的事务，并且在其他事务对其影响之前完成。

  4.持久性（Durability）：一旦事务被提交，它的结果就应该是永久的。即使在系统故障的情况下，数据也不应该被丢失。

  这些特性确保了事务的可靠性和数据的完整性，是保证数据库操作正确性和可靠性的重要保障。

  事务可以保证一组操作全部执行或全部回滚，避免了数据操作中的异常和错误，确保了数据的正确性和一致性。

通过一个银行转账项目演示事务的特性：

# 创建 accounts 表
create table accounts (
   name varchar(50) not null primary key comment '账号名',
   balance decimal(10,2) not null comment '流水',
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   unique uk_name(name));
     
INSERT INTO accounts (name, balance) 
VALUES ('A', 1000.00),
       ('B', 500.00);

假设有两个银行账户，账户A和账户B。希望从账户A向账户B转移100元。这个过程需要进行以下三个操作：

  1.检查账户A的余额是否足够。

  2.从账户A中扣除100元。

  3.将100元存入账户B中。

可以使用MySQL事务来确保这三个操作的原子性，以便在出现故障时能够回滚并确保数据的完整性。

START TRANSACTION;

SELECT balance FROM accounts WHERE name = 'A' FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE name = 'A';

UPDATE accounts SET balance = balance + 100 WHERE name = 'B';

COMMIT;

  在这个示例中，首先使用SELECT语句锁定账户A的余额，以确保其他事务不能同时读取或修改这个值。然后，执行两个UPDATE语句来减少账户A的余额并增加账户B的余额。最后，使用COMMIT语句提交事务并释放锁。

  如果在事务执行过程中发生任何错误，如余额不足或数据库错误，事务会自动回滚并撤销对数据库的任何更改。这确保了银行转账的操作是原子的，即要么成功转移100元，要么不进行转移。

  通过使用MySQL事务，可以确保在复杂的数据库操作中保持数据的完整性和一致性。

18.5 MySQL事务的隔离级别

  MySQL 支持四种事务隔离级别，每种隔离级别的事务并发控制方式不同，如下所述：

  1.READ UNCOMMITTED（未提交读）：最低的隔离级别，允许事务读取未提交的数据，可能会导致脏读、不可重复读和幻象读的问题。

  2.READ COMMITTED（提交读）：允许事务读取已提交的数据，可以避免脏读的问题，但仍可能会出现不可重复读和幻象读的问题。

  3.REPEATABLE READ（可重复读）：保证在同一事务中多次读取同一数据时返回相同的结果，可以避免不可重复读的问题，但仍可能会出现幻象读的问题。

  4.SERIALIZABLE（串行化）：最高的隔离级别，通过强制事务串行执行来避免并发问题，可以避免脏读、不可重复读和幻象读的问题，但会降低并发性能。

  设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL <isolation level>;
# `<isolation level>` 可以是 `READ UNCOMMITTED`、`READ COMMITTED`、`REPEATABLE READ` 或 `SERIALIZABLE` 中的任意一个。

设置事务隔离级别会影响数据库的性能和并发控制效果，应根据具体的应用场景选择适当的隔离级别。

  假设有一个银行转账的场景，银行账户信息保存在 account 表中，包括账户编号（id）和账户余额（balance）两个字段。现在有两个客户端 A 和 B，分别要从账户 1 向账户 2 转账 100 元和 200 元。

# 创建 account 表并插入一些数据
create table account (
   id int not null auto_increment primary key comment '账号ID',
   balance decimal(10,2) not null comment '账户余额',
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间');

INSERT INTO account (id, balance) VALUES (1, 1000), (2, 1000);

  在两个客户端中分别开启一个事务，并设置不同的隔离级别。在客户端 A 中，设置隔离级别为 READ COMMITTED，在客户端 B 中，设置隔离级别为 REPEATABLE READ。

  在客户端 A 中执行以下 SQL 代码：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT;

  在客户端 B 中执行以下 SQL 代码：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;

COMMIT;

  由于隔离级别不同，客户端 A 和客户端 B 的事务执行结果也不同。在 READ COMMITTED 隔离级别下，客户端 A 可以读取其他已提交事务的数据，因此在转账时可以正确地读取账户 2 的余额。而在 REPEATABLE READ 隔离级别下，客户端 B 无法读取其他已提交事务的数据，因此在转账时无法正确地读取账户 2 的余额，可能会导致余额不足的错误。

18.6 MySQL事务并发

  在 MySQL 中，事务并发执行是允许的，意味着多个事务可以同时读取和写入数据库，但同时也会引入一些并发控制的问题。下面是一些关于 MySQL 事务并发的概念和相关问题：

  1.事务隔离级别：MySQL 支持四种事务隔离级别，包括 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，不同隔离级别的事务对并发控制的方式也不同。

  2.锁定：在并发环境下，为了避免数据的不一致性，MySQL 会使用锁定机制来控制对数据的访问。MySQL 支持两种锁定方式：行级锁和表级锁，行级锁可以避免锁定整个表，提高并发性能。

  3.死锁：如果两个或多个事务试图锁定对方持有的资源，就会发生死锁。MySQL 的 InnoDB 存储引擎提供了死锁检测和回滚机制，可以自动检测并回滚死锁。

  4.并发问题：并发执行的事务可能会引起一些问题，例如丢失更新、脏读、不可重复读和幻象读。这些问题可以通过使用事务隔离级别和锁定机制来避免或减少发生。

  在 MySQL 中，事务并发执行是一种常见的情况。为了确保数据的一致性和可靠性，我们需要了解并发控制的相关概念和技术，并根据具体的应用场景选择适当的事务隔离级别和锁定机制。

  假设有一个银行转账的场景，银行账户信息保存在 account 表中，包括账户编号（id）和账户余额（balance）两个字段。现在有两个客户端 A 和 B，分别要从账户 1 向账户 2 转账 100 元和 200 元。

# 创建 account 表并插入一些数据
create table account (
   id int not null auto_increment primary key comment '账号ID',
   balance decimal(10,2) not null comment '账户余额',
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间');

INSERT INTO account (id, balance) VALUES (1, 1000), (2, 1000);

  在两个客户端中分别开启一个事务，并进行转账操作。在客户端 A 中，执行以下 SQL 代码：

START TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT;

  在客户端 B 中，执行以下 SQL 代码：

START TRANSACTION;

UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;

COMMIT;

由于两个事务并发执行，可能会出现一些并发问题。以下是一些常见的并发问题：

  1.脏读（Dirty read）：一个事务读取到了另一个未提交事务的数据，如果另一个事务回滚了，则读取到的数据就是无效的。

  2.不可重复读（Non-repeatable read）：在同一个事务中，一个事务多次读取同一数据时，返回的结果不一致，因为另一个事务在这期间对数据进行了修改或删除。

  3.幻象读（Phantom read)：在同一个事务中，一个事务多次查询同一范围内的数据时，返回的结果不一致，因为另一个事务在这期间插入或删除了数据。

  针对这些并发问题，可以通过设置事务隔离级别和加锁等方式来解决。例如，可以将事务隔离级别设置为 SERIALIZABLE，通过强制事务串行执行来避免并发问题，或者在转账操作中加锁，确保在修改数据时只有一个事务能够访问。

19 MySQL的视图

MySQL 中的视图（View）是一种虚拟的表，它并不真实存在于数据库中，但可以像表一样被查询和操作。视图是基于查询语句创建的，它包含了查询语句中的 SELECT 语句的结果集，可以对这个结果集进行各种操作。

MySQL 中的视图有以下特点：

1. 视图是虚拟的，不占用数据库的物理存储空间。
2. 视图是基于查询语句创建的，对查询语句进行了封装，简化了复杂的查询操作。
3. 视图可以用来保护数据的安全性，可以限制用户只能看到特定的数据。
4. 视图可以被当做普通表使用，可以进行查询、插入、更新和删除等操作，但是它们的数据是由基础表生成的，无法直接修改。

以下是一个创建视图的示例 SQL 语句：

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

其中，view_name 是视图的名称，column1 和 column2 是要查询的列，table_name 是要查询的基础表，condition 是查询条件。

例如，以下是一个创建视图的示例 SQL 语句：

CREATE VIEW customer_info AS SELECT name, address, phone FROM customer WHERE status = 'active';

这个视图名为 customer_info，包含了 customer 表中 status 字段为 'active' 的客户的姓名、地址和电话号码。

使用视图可以简化查询语句，例如，可以使用以下 SQL 查询语句查询 customer_info 视图：

SELECT * FROM customer_info;

这个查询语句会返回 customer 表中 status 字段为 'active' 的客户的姓名、地址和电话号码。可以看到，使用视图可以避免重复编写复杂的查询语句，提高了查询效率和代码的可维护性。

视图实例

假设有一个 employees 表，其中包含了员工的姓名、部门、薪水等信息。我们可以创建一个视图，只显示部门为 Sales 的员工的姓名和薪水信息。下面是创建视图的 SQL 语句：

CREATE VIEW sales_employees AS
SELECT name, salary FROM employees
WHERE department = 'Sales';

上面的语句创建了一个名为 sales_employees 的视图，它只包含了部门为 Sales 的员工的姓名和薪水信息。我们可以像查询表一样查询这个视图：

SELECT * FROM sales_employees;

这个查询语句会返回部门为 Sales 的员工的姓名和薪水信息。注意，视图并不存储数据，它只是一个基于查询语句的虚拟表，所以我们不能向视图中插入、修改或删除数据。

当 employees 表中的数据发生改变时，sales_employees 视图也会相应地发生改变。例如，如果我们修改了某个部门为 Sales 的员工的薪水信息，那么查询 sales_employees 视图会反映这个改变。因此，视图提供了一种方便的方式来查看经常需要查询的数据，同时也保证了数据的一致性。