第 5 章 数据库的创建与管理 如果要使用数据库管理系统对数据进行管理,首先需要创建数据库。创建 数据库主要包括确定数据库的名称、为数据库设置相关属性信息等。然后以数 据库为起点,根据应用需求创建数据表的结构并录入数据,之后才能对表中数 据进行查询、更新、统计等操作。在数据库系统中,数据表是数据存储、管理和 查询过程中最重要而又最基本的操作对象,是查询操作的数据基础,是视图、索 引、存储过程的处理对象。 数据库的创建 5.1 数据库的创建 5.1.1 创建数据库 创建数据库就是确定数据库名称、使用的字符集、校验规则以及相关属性, 操作系统为该数据库分配一定的存储空间,用于存储数据库的对象,包括数据 表、索引、视图及存储过程等。在MySQL 中,创建数据库是通过SQL 语句 CREATEDATABASE(或CREATESCHEMA 语句)来实现。语法格式如下: CREATE DATABASE [IF NOT EXISTS]数据库名称 [CHARSET 字符集] [COLLATE 校验规则]; 说明如下。 (1)数据库名称:在MySQL系统中,数据库及其包含的对象是以目录或文 件的方式进行管理的,因此,数据库名称必须符合操作系统的文件命名规则。 (2)IFNOTEXISTS:可选项,如果使用该选项,则会在创建数据库之前, 首先判断该数据库是否存在,若存在则不再创建,也不会报错;如果不存在则会 创建该数据库。如果没有使用该选项,则创建数据库时若数据库已经存在,系 统就会报错。 (3)CHARSET:可选项,用来设置数据库采用的字符集。字符集是一套 字符内容与编码方式的集合。MySQL 使用字符集和校验规则来处理字符数 据。省略表示采用默认字符集utf8mb4。 72 数据库原理及应用(MySQL版·微课版) (4)COLLATE:可选项,用来设置数据库使用的校验规则。校验规则(COLLATION) 是指在特定字符集中用于比较字符大小的一套规则,是字符串的排序规则。省略表示采 用默认值utf8mb4_0900_ai_ci。 MySQL支持多种字符集,每种字符集对应多种校验规则,且都有一种默认的校验规 则。每种校验规则只对应一种字符集,与其他的字符集无关。例如,字符集utf8mb4的 默认校验规则是utf8mb4_0900_ai_ci,字符集gbk的默认校验规则是gbk_chinese_ci。因 此,在设置字符集和校验规则时要注意匹配使用,不能交叉使用。 说明:本书中创建数据库和数据表时,字符集和校验规则都采用系统默认值。 【例5.1】 创建网络购物系统数据库,数据库名称为online_sales_system,字符集和 校验规则采用默认值。 SQL语句如下: CREATE DATABASE IF NOT EXISTS online_sales_system; 运行结果如图5.1所示。 图5.1 创建数据库 运行结果中“QueryOK,1rowaffected(0.00sec)”表示语句成功运行,有一行受到 影响,用时0.00s。SQL语句运行速度很快,由于显示精度的原因,不到0.01s,显示为 0.00s。读者的运行结果中的运行时间可能和上图有所不同,那是因为运行时间的长短与 使用的计算机的配置有关。图5.1显示结果表明数据库online_sales_system 已成功被 创建。 5.1.2 管理数据库 1.显示数据库 使用SHOW DATABASES语句可以显示当前可用的所有数据库,包括MySQL系 统提供的数据库和用户自己创建的数据库。语法格式如下: SHOW DATABASES; 【例5.2】 使用SHOW DATABASES语句显示当前可用的数据库。 SQL语句如下: SHOW DATABASES; 运行结果如图5.2所示。 由运行结果可以看出,SHOW DATABASES语句正常运行,显示当前目录中共有 第5章 数据库的创建与管理 73 图5.2 显示当前可 用的数据库 5个不同的数据库,分别是information_schema、mysql、online_ sales_system、performance_schema和sys,其中只有online_ sales_system 是用户定义的数据库,其他4个是系统自带的数 据库。 (1)information_schema数据库:存储了数据库的元数据 (DatabaseMetadata),元数据是关于MySQL服务器的信息,例 如数据库或表的名称、列的数据类型或访问权限等,有时也称为 数据字典(DataDictionary)或系统目录(SystemCatalog)。 (2)mysql数据库:提供了MySQL服务器运行时所需信息 的表,包括用于存储数据库对象元数据(DatabaseObjectMetadata)的数据字典表,以及 诸如日志系统表、时区系统表等一系列系统表。 (3)performance_schema数据库:用于监控服务器的运行性能,并提供执行时的有 用信息的访问。 (4)sys数据库:此数据库主要是利用performance_schema数据库提供的信息,生 成一系列的视图、存储过程和存储函数,帮助数据库管理人员和开发者了解系统的 性能。 注意:读者的数据库列表可能和这里的不一样,因为显示的数据库与读者自己定义 的数据库有关。 2.打开数据库 连接到MySQL时,使用SHOW DATABASES语句查看到的都是不同数据库的名 称,即不同的数据库是并列存在的。此时没有指定特定数据库。用户使用CREATE DATABASE语句创建数据库时,不能将其设置为当前数据库。如果要指定某个数据库 为当前数据库,需要使用USE命令打开该数据库,使之成为当前数据库。打开数据库的 语法格式如下: USE 数据库名称; 【例5.3】 打开网络购物数据库online_sales_system。 SQL语句如下: USE online_sales_system; 运行结果如图5.3所示。 执行结果“Databasechanged”说明USE 语句执行成功,当前数据库已经切换为 图5.3 打开数据库 online_sales_system 数据库。之后的命令如果不做特别 说明(使用数据库名来限定表名),都是针对该数据库中的 对象进行操作。如果需要在不同数据库之间切换,也要使 用USE语句打开对应的数据库。因此,打开数据库也称为切换数据库。 74 数据库原理及应用(MySQL版·微课版) 3.查看数据库信息 数据库信息主要包括数据库中包含的数据表、视图、存储函数、存储过程等对象,以及 数据库使用的字符集和校验规则等,在MySQL中提供了相应的语句查看这些信息。 方法一:分别查看数据库中包含的表、使用的字符集和校验规则。 使用SHOW TABLES语句查看数据库中包含的表、视图等对象,语法格式如下: SHOW TABLES; 使用SHOW VARIABLESLIKE 语句查看当前数据库使用的字符集,语法格式 如下: SHOW VARIABLES LIKE'character_set_database'; 使用SHOW VARIABLESLIKE语句查看当前数据库使用的校验规则,语法格式 如下: SHOW VARIABLES LIKE'collation_database'; 其中,VARIABLES是指系统变量,在MySQL中提供了很多系统变量,用于存储定义数 据库时用到的元数据以及系统环境信息。character_set_database和collation_database 都属于系统变量。而LIKE是一个比较运算符,使用规则为LIKE <表达式>,用于检索 和<表达式>相匹配的变量,其中<表达式>中可以使用通配符(%可以匹配多个字符, “_”可以匹配一个字符)。 【例5.4】 查看网络购物数据库online_sales_system 中包含的数据表等对象。 SQL语句如下: SHOW TABLES; 图5.4 数据库online_sales_system 中的数据表 运行结果如图5.4所示。 运行结果Emptyset(0.09sec)的含义是SHOW TABLES语句运行成功,但在当前数据库中没有数 据库对象,因此显示为Emptyset,(0.09sec)表示运 行该语句用时0.09s。 【例5.5】 查看网络购物数据库online_sales_system 使用的字符集。 SQL语句如下 SHOW VARIABLES LIKE 'character_set_database'; 运行结果如图5.5所示。 由运行结果可以看出,当前数据库online_sales_system 使用的字符集为utf8mb4,与 第5章 数据库的创建与管理 75 图5.5 显示数据库online_sales_system 使用的字符集 创建该数据库时使用的默认字符集一致。 【例5.6】 查看网络购物数据库online_sales_system 使用的校验规则。 SQL语句如下: SHOW VARIABLES LIKE'collation_database'; 运行结果如图5.6所示。 图5.6 显示数据库online_sales_system 使用的校验规则 由运行结果可以看出,当前数据库online_sales_system 使用的校验规则为utf8mb4_ 0900_ai_ci,与创建该数据库时使用的默认校验规则一致。 方法二:使用SHOW CREATEDATABASE语句查看数据库定义的详细信息,包 括创建该数据库的SQL语句、数据库使用的字符集和校验规则等。语法格式如下: SHOW CREATE DATABASE <数据库名称>; 【例5.7】 使用SHOW CREATEDATABASE语句查看online_sales_system 数据 库定义的详细信息。 SQL语句如下: SHOW CREATE DATABASE online_sales_system; 运行结果如图5.7所示。 图5.7 使用SHOW CREATEDATABASE显示online_sales_system 数据库的详细信息 此运行结果中除了显示创建数据库的语句、数据库的字符集和校验规则外,还显示了 数据库是否加密的信息,“DEFAULTENCRYPTION='N'”显示加密的值为'N',表示没 加密。如果加密的值是'Y',表示已加密。 76 数据库原理及应用(MySQL版·微课版) 4.删除数据库 删除数据库是指删除数据库以及数据库中所有的表、索引、视图等对象,并清除操作 系统分配给该数据库的存储空间。删除数据库后,所有数据库存储的数据都将被删除,而 且删除操作不可恢复。因此,删除数据库时需要特别谨慎,应遵循非必要不删除的原则。 即使遇到必须删除的情况,也建议先将数据库进行备份,之后再进行删除。删除数据库的 语句的语法规则如下: DROP DATABASE [IF EXISTS]数据库名; 【例5.8】 使用DROPDATABASE语句删除刚创建的网络购物数据库。 SQL语句如下: DROP DATABASE online_sales_system; 运行结果如图5.8所示。 图5.8 删除数据库online_sales_system 【例5.9】 使用DROP DATABASE 语句删除数据库时,直接删除和使用IF EXISTS可选项再删除一次数据库online_sales_system。运行结果如图5.9所示。 图5.9 删除数据库 由运行结果可以看出可选项IFEXISTS的作用:在执行删除操作之前首先判断数 据库是否存在,只有数据库存在才执行删除操作,否则不执行删除操作,也不给出错误信 息。而不使用可选项IFEXISTS,则不做判断直接删除,若被删的数据库不存在,系统就 会给出错误提示信息“databasedoesn..texist”。在实际应用中,建议大家使用IFEXISTS 选项。 认识数据表 5.2 认识数据表 在MySQL中,不同的数据库是以目录的形式进行管理的,使用USE语 句可以设置当前数据库,也相当于设置一个当前目录。在该目录中包含该 第5章 数据库的创建与管理 77 数据库的所有对象,包括数据表、视图、索引、存储过程或存储函数等对象。其中,数据表 是存储原始数据的主要对象。它由行和列组成,每列为一个字段,描述实体集的一个属 性;每行为一条记录,描述一个具体的实体。在一个数据库中可以包含多个数据表,每个 数据表保存一个实体集或一个联系的相关数据。 在MySQL中为数据库、数据表、视图、索引、存储过程、存储函数等对象命名的有效 字符序列统称为标识符。标识符实际上就是一个名称,但标识符必须符合一定规则或约 定,在MySQL中标识符的命名规则如下。 (1)标识符可以由当前字符集中的任何字母、中文、数字字符组成,另外,还可以包括 下画线(_)和美元符号(＄)。但一般不建议使用中文作为标识符,因为中文字符在计算机 中存储时涉及字符集的选择,不同的字符集对中文字符的编码不同,如果字符集不匹配会 出现乱码。如果使用中文字符作为标识符,那么代码的可移植性就会很差。 (2)标识符最长为64个字符。但标识符的长度受限于所用操作系统限定的长度。 (3)标识符应尽可能做到“见名知意”。换句话说,通过数据表的名称就可以知道数 据表表示的实体集,通过字段名称就可以知道字段表示的属性值的含义,从而增强可读 性。通常可以选择能表示数据含义的英文单词或缩写、汉语拼音等作为标识符。如用 customers作为客户表的名称、用price作为销售价格的字段名称。 (4)用户自己定义的标识符可以直接使用,如果标识符名与系统保留字(关键字)重 名,应该用反引号括起来(注意,不是单引号)。但建议在给对象命名时,避免和系统的保 留字重名。系统提供的语句命令和语句中的关键字都属于保留字,例如CREATE、 DROP、USE、SELECT、FROM 等。 (5)操作系统中的文件系统是否大小写敏感,会影响如何命名和引用数据库对象。 如果文件系统对大小写敏感(如UNIX),名字tbl_items、TBL_items和tbl_ITEMS是3 个不同的标识符。如果文件系统对大小写不敏感(如Windows),那么这3个名字指的是 一个标识符,也就是对应一个对象。 注意:各个DBMS 的约定不完全相同,使用前需要查看相关的系统资料。在 Windows环境中的MySQL系统默认对标识符不区分大小写,在Linux环境中默认区分 大小写。 1.表的名称 完整的数据表名称由数据库名和表名两部分构成,其格式如下: 数据库名.表名 在当前数据库中操作数据表等对象时,数据表名之前的数据库名可以省略,只有在处 理属于两个不同数据库中的数据表时,才需要用完整的数据表名。 表名和数据库名的命名需要遵循MySQL的标识符命名规则。例如,网络购物数据 库的名称为online_sales_system,包含的4个表的名称分别客户表customers、商品表 items、订单表orders、订单明细表order_details,它们均符合标识符的命名规则。 78 数据库原理及应用(MySQL版·微课版) 2.数据表 在MySQL中,数据表是一张满足关系模型的二维表,二维表的第一行是各列标题, 每列称为一个字段,所有的字段构成一张数据表的表结构,其余每行代表一条记录,记录 中每列的值代表该记录对应字段的值。所有的记录构成表的内容。数据表实质上就是行 列的集合。 【例5.10】 显示客户表customers的数据,了解表的构成。 SQL语句如下: SELECT * FROM customers; 运行结果如图5.10所示。 图5.10 显示客户表customers的数据 由运行结果可以看出,客户表customers包含5个字段,分别是customer_id、name、 gender、registration_date和phone;customers包含7 条记录。第一条记录的含义是 customer_id的值为字符串'101',name的值为'薛为民',gender的值为'男',registration_ date的值为2' 012-01-09',phone的值为1' 6800001111';第二条记录表示的数据和第一条记 录不同,尤其是customer_id一定不同,从这里也可以发现,对于不同的记录,字段值是不 完全相同的。 3.表结构 表结构描述的是表的框架,表结构决定数据表拥有哪些字段以及这些字段的特性。 字段特性主要包括字段的名称、数据类型、长度、精度、小数位数、是否允许空值(NULL)、 是否需要设置默认值、是否为主键、是否为外键、是否有索引等。其中,每个字段的数据类 型是必须定义的,数据类型决定该字段的取值范围和可以参与的运算。例如,客户表中的 联系电话phone字段虽然都是由数字组成,但联系电话不会参与数学运算,有可能会参与 字符串连接运算,因此定义为字符类型,又根据目前电话号码最长是11位,因此确定 phone字段最多只能取11位字符;注册日期registration_date字段只能取合法的日期值, 即必须符合现实生活中的日期,月份不能小于1或大于12,日期不能小于1或大于31等。 【例5.11】 使用DESC语句查看客户表customers的表结构。 SQL语句如下: 第5章 数据库的创建与管理 79 DESC customers; 运行结果如图5.11所示。 图5.11 使用DESC查看customers的表结构 由运行结果可以看出,客户表customers的表结构由customer_id、name、gender、 registration_date和phone一共5个字段构成,其中字段customer_id的数据类型为 char(3),且为customers表的主键(Key列的值为PRI表示该字段为主键);字段name的 数据类型为varchar(20);字段gender为枚举类型,即只能从“男”和“女”两个值中选择一 个,且默认值为“男”;字段registration_date的数据类型为date;字段phone的数据类型 为char(11)。 4.字段名 每个数据表可以拥有多个字段,每个字段分别用来存储不同类型、不同性质的数据。 字段名除了必须符合MySQL的标识符命名规则之外,还要满足以下要求。 (1)字段名可由中文、英文字母、数字、下画线(_)、#符号及$符号组合而成。在实 际应用中,不建议使用中文作为字段名。 (2)同一个数据表中,不能出现重名字段。但不同数据表中的字段名可以重名。 (3)字段名应尽可能做到“见名知意”。通过字段名称就可以知道字段表示的属性值 的含义,从而增强可读性。例如name表示姓名、gender表示性别。 (4)字段名不能使用MySQL 语言中的关键字。如DROP、ALTER、INSERT、 CREATE、ONLINE、FROM 等。 5.表间关系 在关系数据库中,一个数据库系统一般包含多个数据表,数据表之间也会存在关系。 表间关系是通过外键(外码)来实现的,也体现了关系之间的参照完整性。如果表A 外键 字段的数据取值要么是NULL,要么是来自于表B主键字段的值,那么将表A 称为表B 的从表(子表),表B称为表A 的主表(父表)。也就是说,对于两个具有关联关系的表而 言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。网络购 物数据库中4个表之间的关系如图5.12所示。 从图5.12可以看出,客户表customers的主键为客户编号customer_id,商品表items 的主键为商品编号item_id,订单表orders的主键为订单编号order_id。订单表的外键为 客户编号customer_id,与客户表构成从-主表关系,订单明细表order_details的主键为订 80数据库原理及应用(MySQL版·微课版) 图5.12网络购物数据库中的表间关系 单编号order_id和商品编号item_id,一个外键为订单编号order_id,与订单表构成从-主 关系;另一个外键为商品编号item_id,与商品表items 构成从-主表关系。 5.3数据类型 在我们认知的世界中,存在着五花八门的数据,如果要将数据存储到计算机中,就需 要按数据类型将这些数据进行分类,不同类型的数据在计算机中占用的字节数、编码方 式、取值范围以及能参与的运算各不相同。在创建数据表的结构时,必须首先确定每个字 段的数据类型。字段的数据类型是数据完整性的一部分,它限制了字段的取值范围、存储 方式和使用方法。MySQL 提供了多种数据类型。在创建表时根据实际需求(字段值的 范围、大小、精度、参与的运算等)为每个字段选择合适的数据类型,不但能节省整个数据 库占用的存储空间,而且能提高数据库的运行效率。 3.数值类型 5.1 MySQL 支持所有的ANSI/ISOSQL92 数值类型(ANSI,AmericanNational StandardsInstitute,美国国家标准局), 数值分为整数和小数。其中整数用整数类型表 示,小数用浮点数类型或定点数类型表示。 1. 整数类型 整数就是由正负符号和0~9构成的不带小数点的数据,正号可以省略,例如12 、56 、 -128 等都属于整型数据。在MySQL 中整数类型包括TINYINT 、SMALLINT 、 MEDIUMINT 、INT 和BIGINT 等。不同类型的整数占用的存储空间不同,取值范围也 不同,如表5. 从表5.1所示。 占用字节数越多的类型存储的数值范围越大。在MySQL8. 1中可以看出, 0. 17 之前的版本中可以指定整型数据的显示宽度,使用INT( M )进行设置, M 表示最大显 示宽度。显示宽度是可选项,如果没有指定显示宽度,则按系统默认的宽度显示。例如 INT(5) 0. 表示最大有效显示宽度为5。在MySQL8.17 之后的版本不推荐设置显示宽 度,并将在未来的版本中移除此用法。