数据库原理及MySQL应用 | 数据表操作

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 数据表操作是数据库操作中最基本和最重要的操作。

image.png

图5-1是图书销售数据库booksale中存放的图书表books。

image.png


■ 图5-1图书表books

01. 表的结构

表的结构也称为“型”(Type),用于描述存储于表中的数据的逻辑结构和属性。定义表就是指定义表的结构,使用数据定义语言来实现。在定义表之前首先需要注意以下几个概念。

(1) 表名:在同一个数据库中,每一个表都应该有一个唯一的名称。表名和数据库的名字一样,都应该满足标识符命名规则。

(2) 列名:从图5-1中可以看出,每个表由若干列组成,在同一个表中每个列的名字应该是唯一的,列的名字应该符合标识符命名规则。

(3) 列的数据类型:表中的每个列都要定义一个数据类型。定义数据类型时需要慎重考虑,如果定义的范围太小,可能会造成无法存放某些数据,如果定义的范围太大可能会造成存储空间的浪费。存储空间的增加将增加系统的I/O操作量,从而降低系统的使用效率。

(4) 列中是否允许有空值:表中的某些列可能严禁出现空值,例如,若要求每本图书都必须有图书编号,那么“图书编号”列就不允许有空值。某些列,例如“作者”列中可能会存在空值,也就是说某些图书没有明确作者或作者未知,这时这些列就应该定义成允许空值。

  1. 表中的数据

表中的数据也称为“值”(Value),是“型”的具体赋值。操纵表中的数据通过数据操纵语言实现。

(1) 数据行: 一个数据行也被称为一个元组或一条记录,是现实世界中一个物理或逻辑实体的数据描述形式。

(2) 数据列: 一个数据列也被称为一个属性或一个字段,是同一类型的所有实体在某个属性上的全部值的集合。列是表定义的基本对象,定义一个表的主要任务就是定义这个表中的各个列。

(3) 主键: 表的主键是表中的某个列或某几个列的组合,其值可以唯一标识表中的每个行。一个表只能定义一个主键,而且通常都应该定义一个主键。主键的值不能为空值,也不能重复。如果存在多个列或列组合同时满足作为主键的条件,则应该选择运算效率高的列或列组合作为表的主键。通常数值型的列比字符型的列运算效率高;如果同为字符型,则取值范围小的列的运算效率通常更高。

(4) 自增列: 又称标识列,可以将表中具有整数性质的某个列定义为自增列来唯一标识表中的每一行,定义的关键词为AUTO_INCREMENT。一个表中最多只能有一个列被定义为自增列。自增列不允许为空值,也不允许重复,自增列必须是主键或主键的一部分。默认情况下自增列中的第一个值是1,后续值自动加1。如果用户设置了一个非1的初始值,后续值将在该值基础上自动加1。

提示/

系统数据库information_schema中的数据表为系统数据表,如:SCHEMATA表(提供了当前MySQL实例中所有数据库的信息,SHOW DATABASES的结果取自此表)、TABLES表(提供了关于数据库中的表的信息,详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息,SHOW TABLES FROM schemaname的结果取自此表)、COLUMNS表(提供了表中的列信息,详细表述了某张表的所有列以及每个列的信息,SHOW COLUMNS FROM schemaname.table_name的结果取自此表)等。

02、表的创建

1. 创建表

创建表就是在数据库中建立新表。创建表的基本语法格式如下所示。

image.png


语法说明如下。

table_name是要定义的数据表的表名,可以是字母、数字和下画线组成的任意字符串。在同一数据库中数据表名是唯一的,不可与已经存在的数据表重名。

IF NOT EXISTS是可选选项。添加该选项,表示指定的数据表不存在时执行创建数据表操作,否则忽略此操作。

column是列的名字;DATATYPE是该列的数据类型;NOT NULL表示该列中不允许有空值,NULL表示该列中允许有空值,为默认选项。

PRIMARY KEY用于定义主键。如果是某个列作为主键,则可以直接在该列上定义主键约束;如果由多个列组成主键,则必须定义表级主键约束,其形式为"PRIMARY KEY (column1 [, column2] [, …] )"。

AUTO_INCREMENT表示将列定义为自增列。

【例5-2】在图书销售数据库booksale中创建图书表books用于存放图书的信息。

image.png


定义列时使用NOT NULL表示这个列在存储数据时不允许出现空值,否则使用默认的属性NULL,表示这个列在存储数据时允许出现空值。

如果数据表books已经存在,再运行上面的命令,系统会提示错误信息“Table 'books' already exists”,为了防止这种错误发生,在创建数据表时可以在“数据表名称”前添加IF NOT EXISTS,这样命令执行后,只是返回一条警告信息“Query OK, 0 rows affected, 1 warning (0.01 sec)”而已。

【例5-3】在图书销售数据库booksale中创建顾客表customers用于存放顾客的信息。

image.png


定义列cstid时使用PRIMARY KEY表示将该列定义为表的主键。定义主键时系统自动将该列定义为NOT NULL,即不允许空。

【例5-4】在图书销售数据库booksale中创建订单表orders用于存放订单的信息。

image.png


定义列orderid时使用AUTO_INCREMENT表示将该列定义为自增列,系统会自动在该列中生成不重复的整数序列值。定义列的AUTO_INCREMENT属性时必须将该列定义为主键或主键的一部分。

定义列orderdate时使用数据类型TIMESTAMP,并且将默认值设置为current_timestamp,表示插入记录时系统会自动将系统当前日期时间存入该列中。默认值约束的设置见6.3.4节。

【例5-5】在图书销售数据库booksale中创建订单项目表orderitems用于存放订单项目的信息。

image.png


该表的主键由两列组成,所以这里需要使用表级主键。因为主键所在列都不允许出现空值,所以即使定义主键所在列时没有使用NOT NULL,系统也会自动为该列增加非空属性。

添加IF NOT EXISTS参数,表示要创建的orderitems表只有在不存在时,才执行该创建表命令。

2. 创建带JSON类型的表

新的数据类型JSON的引用可以将复杂数据存储在一个数据列中,易于存储。

【例5-6】在图书销售数据库booksale中创建带有JSON类型的表t_json用于存放售货员信息,然后查看数据库中已经存在的数据表。

image.png


该表的主键由一列组成,可以采用列级主键,也可以采用表级主键,这里使用的表级主键。因为主键所在列都不允许出现空值,所以无论该列是否定义NOT NULL,系统都会自动为该列增加非空属性。

3. 表的复制

使用上述的CREATE TABLE命令可以根据实际需要创建表,是实际开发中较常用的方式。而CREATE TABLE LIKE命令则可以对源表的模式进行复制,从现有的数据表中精确地复制表的定义(不复制其数据),其创建的表除了表名和源表不一样外,其余所有的细节都是一样的。复制表的基本语法格式如下所示。

image.png


语法说明如下。

LIKE old_table_name是基于表old_table_name的定义创建空表table_name,包括原始表中定义的任何列属性和索引。该子句可加括号也可不加括号。

table_name是生成的新表名。

TEMPORARY是可选选项,用于创建临时表。临时表仅在当前会话中可见,并在会话关闭时自动丢弃。

IF NOT EXISTS是可选选项。添加该选项,表示指定的数据表不存在时执行数据表复制操作,否则忽略此操作。

【例5-7】在图书销售数据库booksale中创建和图书表books一样结构的临时表图书备份表booksbak。

image.png


booksbak表和books表的结构一模一样。当退出MySQL再次登录后,该临时表将不再存在。SHOW TABLES命令,不能看到临时表。

4. 查看表结构

查看表结构是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE语句和SHOW CREATE TABLE语句,通过这两个语句,可以查看表的数据列名、数据列的数据类型和完整性约束条件等。

1) DESCRIBE语句查看表定义

可以使用DESCRIBE(可以缩写为DESC)命令查看表的基本定义,包括数据列的列名、数据类型、是否为空、是否为主键、默认值、自增列等,其基本语法格式如下所示。

image.png


【例5-8】查看orders表的结构。

image.png


执行结果如图5-2所示。

image.png


■ 图5-2查看Orders表的结构

2) SHOW CREATE TABLE语句查看表详细定义

可以使用SHOW CREATE命令查看定义表的SQL语句,从而得到表的详细结构,包括列的名称、数据类型、是否为空、默认值、表的存储引擎、字符编码等,比使用DESC命令显示的信息要全面。SHOW CREATE TABLE命令的基本语法格式如下所示。

image.png


【例5-9】查看books表的结构。

image.png


执行结果如图5-3所示。

image.png


■ 图5-3查看books表的结构

提示/

图5-3是在命令提示符下显示的结果,在显示内容较长的情况下,使用“\G”选项可以更好地显示结果。如果在客户端工具Navicat中,由于显示列宽度有限,可以将其复制出来查看。

03、表的修改

修改表是指修改数据库中已存在的表的定义。表创建好以后,可以根据需要使用ALTER TABLE语句修改表的结构,包括在表中增加新列、修改列的属性以及删除列等。

1. 增加列

增加新列的基本语法格式如下所示。

image.png


语法说明如下。

FIRST表示新增加的列作为表的第一列;也可以使用AFTER columnname的形式将新增加的列指定到columnname所表示的列之后;默认情况下,新增加的列是表的最后一列

table_name是要修改的数据表的表名,该表必须是数据库中已经存在的表。

ADD COLUMN是增加新列的命令关键字,其中COLUMN关键字可以省略。

columndefinition是对新增加列的完整定义。

【例5-10】在图书表books中新增一个新列press,用于存放出版社名称。该列数据类型为VARCHAR(50),允许空值。

image.png


关键词NULL表示该列允许空值,由于NULL是默认设置,所以该关键词可以省略。也可以通过以下两条语句完成增加列操作。

image.png


多加了一个关键字FIRST,表示press列在表中第一的位置。

image.png


多加了一个关键字AFTER,表示press列在author列的后面。

这三条命令添加的列名相同,实操操作完一个命令后,应先删除该列,再继续下一个命令。

提示/

如果表中已经有数据,那么在表中增加一个新列时,新列中是没有数据的,所以如果将增加的新列设置成不允许有空值,必然产生错误。可以有两种方法解决这个问题,一种是首先将新列定义成允许有空值,然后向新列中输入数据后再将这个列修改为不允许有空值;另一种是在添加新列时为该列定义一个默认值。

2. 修改列

修改列的基本语法格式如下所示。

image.png


语法说明如下。

FIRST表示将修改的列调整为表的第一列;也可以使用AFTER columnname的形式将修改的列指定到columnname所表示的列之后。

table_name是要修改的数据表的表名,该表必须是数据库中已经存在的表。

MODIFY COLUMN是修改列的命令关键字,其中COLUMN关键字可以省略。

columndefinition是对修改列的完整定义。

image.png


语法说明如下。

columndefinition是对修改列的完整定义,该定义中列名可以重新命名。

oldcolumnname是要修改列的列名。

提示/

通过该语句不仅可以修改列的属性,也可以修改列的名称。

【例5-11】修改图书表books中的出版社列press,将数据类型修改为VARCHAR(20),不允许空值,并将位置修改为位于作者列author之后。

image.png


【例5-12】修改图书表books,将图书编号列bookid修改为自增、主键列。

image.png


【例5-13】修改订单表orders,删除订单编号列orderid的自增属性。

image.png


订单编号列orderid的为空性属性和主键属性不变。

image.png

3. 删除列

删除列的基本语法格式如下所示。

image.png


将图书表books中的出版社列press的名称改为publisher,其他属性不变。

语法说明如下。

columnname是要删除列的列名。

table_name是要修改的数据表的表名,该表必须是数据库中已经存在的表。

DROP COLUMN是删除列的命令关键字,其中COLUMN关键字可以省略。

【例5-15】删除图书表books中的出版社列publisher。

image.png

4. 重命名表

数据库系统通过表名来区分不同的表,表名在同一个数据库中唯一标识一张表。重命名表的基本语法格式如下所示。

image.png


语法说明如下。

new_table_name是数据表修改后的新表名,该表名在数据库中不能存在。

table_name是要修改的数据表的表名,该表必须是数据库中已经存在的表。

RENAME[TO]是重命名表的命令关键字,其中TO关键字可以省略。

【例5-16】将顾客表customers的名称重命名为users。

image.png


数据库booksale中customers表已经不存在了,取而代之的是users表。

04、表的删除

删除表是指删除数据库中已存在的表。删除表将同时删除表中的数据。因此,删除表操作要想好了再做。创建表时可能存在外键约束,被关联的父表删除比较复杂。这里只讲没有关联的普通表的删除,关联表的删除在讲解外键约束时再讲解。

删除表的基本语法格式如下所示。

image.png


语法说明如下。

RESTRICT | CASCADE是可选选项。RESTRICT是确保只有不存在相关视图和完整性约束的表才能删除。CASCADE是任何相关视图和完整性约束一并被删除。

table_name是要删除的数据表的表名,可以一次性删除多个数据表。

IF EXISTS是可选选项。添加该选项,表示指定的数据表存在时执行删除数据表操作,否则忽略此操作。

【例5-17】删除顾客表users。

image.png


数据库booksale中users表必须存在,否则命令执行将提示错误信息“ERROR 1051 (42S02): Unknown table 'booksale. *'”。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
TiAmoZhang
+关注
目录
打赏
5
4
1
0
1026
分享
相关文章
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
44 16
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
31 7
MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合
2024年小结:感谢阿里云开发者社区每月的分享交流活动,支持持续学习和进步。过去五个月投稿29篇,其中17篇获高分认可。本文详细介绍了MySQL InnoDB存储引擎的MVCC机制,包括数据版本链、readView视图及解决脏读、不可重复读、幻读问题的demo演示。
【AI应用启航workshop】构建高可用数据库、拥抱AI智能问数
12月25日(周三)14:00-16:30参与线上闭门会,阿里云诚邀您一同开启AI应用实践之旅!
基于PolarDB的图分析:通过DTS将其它数据库的数据表同步到PolarDB的图
本文介绍了使用DTS任务将数据从MySQL等数据源实时同步到PolarDB-PG的图数据库中的步骤.
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
55 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
61 3
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
81 2
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等