当前位置：首页 > news >正文

[SQL Statements] 基本的SQL知识之DDL针对表结构和表空间的基本操作

news 文章来源：https://blog.csdn.net/qq_41755616/article/details/129406550 2025/4/20 18:17:07

[SQL Statements] 基本的SQL知识之DDL针对表结构和表空间的基本操作

什么是数据库的表以及表空间

在MySQL中，一个数据库可以包含多个表，每个表是由若干个列（column）和行（row）组成的。表是存储数据的基本单位，用于组织、管理和存储数据。

MySQL中的表空间（Tablespace）是一个逻辑存储单元，它是由若干个数据文件（data files）组成的。表空间用于存储表和索引数据，每个表和索引都有一个关联的表空间。在MySQL中，每个表和索引都必须属于一个表空间，而每个表空间可以包含多个表和索引。

MySQL中的表空间有两种类型：系统表空间和用户表空间。系统表空间是MySQL内部使用的表空间，包括InnoDB的数据文件和redo log文件。用户表空间是由用户创建的，用于存储用户定义的表和索引数据。

在MySQL中，创建表时可以指定它所属的表空间。如果没有指定表空间，则会使用默认的表空间。

你可以这样理解数据库中的表，可以简单地理解这就是一个excel表格，而表空间就是一个或多个表组成一个表空间。

我们常说的一个表中有多个字段，这个字段其实就表示一列。

如图所示：

在这里插入图片描述

其中，表结构规定了这一列存放什么类型的数据，这就涉及到表的设计了。

如何进行表结构设计？

设计的主要步骤如下：

确定表的目的和需求：确定表的用途和需求，包括需要存储哪些数据、如何组织数据等方面的内容。
确定表的列和数据类型：确定表的列，包括列名、数据类型、长度、是否允许为空等属性。通常需要根据数据的类型和业务需求进行选择。
设计表的主键和索引：主键是用于标识表中每个记录的唯一标识符，通常使用自增长整数作为主键。索引可以提高查询性能，通常需要根据查询需求设计相应的索引。
设计表的约束：约束用于限制表中数据的取值范围，包括主键约束、唯一约束、非空约束、默认值约束等。
设计表之间的关系：如果有多个表需要进行关联，需要确定表之间的关系，包括一对一、一对多和多对多关系。
进行数据类型和范式的优化：对于大型和复杂的数据库，需要进行数据类型和范式的优化，以提高性能和减少数据冗余。
进行表的命名和注释：为表设置一个有意义的名称，并对表进行适当的注释，以方便后续的管理和维护。

在进行表结构设计时，需要根据具体的业务需求和数据库规模进行调整。同时，需要遵循一定的设计原则，如避免数据冗余、优化数据类型、保持表的一致性和正确性等。

举例

假设我们需要创建一个简单的学生信息管理系统，包括以下两个表：学生信息表和课程成绩表。

学生信息表包括以下列：

学生ID（student_id）：整数类型，作为主键
学生姓名（student_name）：字符串类型，最大长度为50
学生年龄（student_age）：整数类型
学生性别（student_gender）：字符串类型，最大长度为10
学生专业（student_major）：字符串类型，最大长度为50

课程成绩表包括以下列：

成绩ID（score_id）：整数类型，作为主键
学生ID（student_id）：整数类型，作为外键关联学生信息表中的学生ID列
课程名称（course_name）：字符串类型，最大长度为50
课程成绩（course_score）：浮点数类型

对于学生信息表和课程成绩表之间的关系，我们可以使用一对多关系，即一个学生可以对应多个成绩。在这种情况下，学生信息表中的学生ID列作为主键，课程成绩表中的学生ID列作为外键。同时，我们需要在学生ID列上创建索引，以提高查询性能。

为了遵循设计原则，我们可以将学生姓名、年龄、性别和专业信息拆分成一个独立的表，从而避免数据冗余。此外，我们还可以将学生信息表和课程成绩表拆分成不同的表空间，以便于管理和维护。最后，我们可以为表设置有意义的名称和注释，如学生信息表（students）和课程成绩表（scores），以方便后续的管理和维护。

查看某个数据库中有哪些表

使用show tables;

例如，我在test2数据库下查看我有哪些表

在这里插入图片描述

数据类型

在去创建表之前，我们需要知道mysql中有哪些数据类型，以方便创建指定的字段时使用正确的数据类型进行存储

数字类型

数字类型数据主要包括两种，和其他编程语言的数据类型一样，分为精确数和浮点数。

这每种数据类型均可以指定参数，用来表示这个数有多长

精确数

又分为两种：整数（integer）和定点数（Fixed-Point）

Integer类型 - INTEGER, INT, SMALLINT, TINYINT,MEDIUMINT, BIGINT

类型	字节长度	最小值	最大值	无符号情况下最大值
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT 最常用	4	-2147483648	2147483647	4294967295
BIGINT	8	-2^63	2^63 -1	2^64 -1

定点数据 - DECIMAL, NUMERIC

存在这种固定的数据是很有必要的，在需要保持精确度很高的情况下就得用到这种类型，例如用户的金钱余额、交易金额等数据，就必须要保持极高的数据准确的。对于这种数据采用浮点显然不合适，因此需要用到定点数据

在MySQL里面，NUMERIC类型的实现就是DECIMAL.

DECIMAL：可以指定精度和小数位

例如：对于薪资(salary)这一列，设置精度为6，小数位为2的数据类型

salary DECIMAL(6,2) 这意味着该字段的数据存储范围是 -9999.99 到 9999.99

标准SQL中的 DECIMAL(M) 等价于 DECIMAL(M,0) ，MySQL支持这两种写法

浮点类型 - FLOAT, DOUBLE

和其他编程语言的一样，表示的数据是一个近似值，没有特别特别要求精确的小数就用这种类型

FLOAT：单精度 4byte

DOUBLE：双精度 8byte

位值类型 - BIT

用来存储1位的值范围1~64

对于上述数据类型，最容易出现的问题便是，超出范围和溢出

当MySQL将一个值存储在列数据类型的允许范围之外的数字列中时，该结果取决于当时有效的SQL模式：

如果启用了严格的SQL模式，则MySQL将拒绝带有错误的超出范围的值，并且按照SQL标准，插入将失败。
如果没有启用限制性模式，MySQL会将该值剪辑到列数据类型范围的适当端点，并存储结果值。

当将超出范围的值分配给一个整数列时，MySQL将存储表示列数据类型范围的相应端点的值。

其实说实话，一般的解决方案就是，修改该列的数据类型，存得更大更多一点。

日期和时间类型

表示日期和时间的数据类型如下：

DATE, TIME, DATETIME,TIMESTAMP, YEAR

在使用日期和时间类型时，请记住以下一般注意事项：

MySQL以标准输出格式检索给定日期或时间类型的值，但它试图解释您提供的输入值的各种格式（例如，当您指定要分配给日期或时间类型或与日期或时间类型进行比较的值时）。我们期望您提供有效的值。如果您使用其他格式的值，则可能会出现不可预测的结果。
尽管MySQL尝试以几种格式解释值，但必须始终给出日期部分 year-month-day 顺序，而不是其他地方的 month-day-year 或 day-month-year 顺序，将其他顺序的字符串转换为年-月-日的顺序，可以使用 STR_TO_DATE() 方法
年份不要只用后两位代替，因为无法知道你到底是1999年还是2099年。虽然这种存储是可以的，但极度不建议。
默认情况下，当MySQL遇到一个日期或时间类型的值超出范围或对该类型无效时，它会将该值转换为该类型的“零”值。
通过将SQL模式设置为适当的值，您可以更准确地指定您希望MySQL支持的日期类型。
MySQL允许在DATE 或DATETIME列中存储日期或月和日期为零的日期。比如在你不需要确切知道出生日期的时候，只需要知道年月时，可以存储’1998-02-00’，但这样的数据就不要拿来进行运算了。
MySQL允许将“0000-00-00”的“0”值存储为“虚拟日期”。在某些情况下，这比使用NULL值更方便，并且使用更少的数据和索引空间。如要不允许存“0000-00-00”，请启用NO_ZERO_DATE模式。
‘ZERO’在走 Connector/ODBC 的时候会自动转化为NULL

ZERO数据：

日期类型	零值（你也可以通过这个零值知道存储的格式）
DATE	0000-00-00
TIME	00:00:00
DATETIME	0000-00-00 00:00:00
TIMESTAMP	0000-00-00 00:00:00
YEAR	0000

datetime和timestamp区别：

datetime范围是1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999

timestamp范围是 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999

字符和字符串类型数据

CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, 以及SET

char 和 varchar

两者的差别在于存储和检索方式不同，在最大长度和是否保留尾随空间的方面也不同

要存储的值	char(4)	存储空间	varchar(4)	存储空间
‘’	’ ’	4byte	‘’	1byte
‘ab’	'ab ’	4byte	‘ab’	3byte
‘abcd’	‘abcd’	4byte	‘abcd’	5byte
‘abcdefghijkklm’	‘abcd’	4byte	‘abcd’	5byte

char类型：长度 0~255

varchar类型：长度 0~65535

BINARY 和 VARBINARY

这两者和char varchar很像，这个存储的是字节字符串而不是字符串。也就是说它们有二进制字符集、排序比较等方式

而且最大长度也对应相同

blob 和 text

BLOB是一个二进制的大对象，它可以保存可变量的数据。

blob有四个类型： tinyblob、blob、mebiumblob、longblob

text是MySQL对Blob类型的具体实现，四个类型不同就在于对最大的数据值支持情况不同

也就是说：

text对应的也有四个类型：tinytext、text、mebiumtext、longtext

Enum

ENUM是一个字符串对象，其值从在表创建时在列规范中明确枚举的允许值列表中选择。

比如

 ENUM('Mercury', 'Venus', 'Earth')

每个值对应的索引如下：

值	索引
NULL	NULL
‘’	0
‘Mercury’	1
‘Venus’	2
‘Earth’	3

SET

SET是一个字符串对象，它可以有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。

一个SET列最多可以有64个不同的成员

创建表

-- 直接创建表
create [Temporary] table [if not exists] 表名
(建表定义, ...) [表选项] [分区选项]-- 通过一个查询表达式创建表
create [Temporary] table [if not exists] 表名
[(建表定义, ...)] [表选项] [分区选项] [as] 查询表达式-- 通过一个旧表创建
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名
{ like 旧表名| (like 旧表名) }

将创建一个具有给定名称的表。您必须具有对该表的创建权限。默认情况下，将使用InnoDB存储引擎在默认数据库中创建表。如果该表存在、没有默认数据库或该数据库不存在，则会发生错误。

在创建表时，您可以使用 TEMPORARY关键字。临时表仅在当前会话中可见，并且在会话关闭时会自动删除。

使用CREATE TABLE ... LIKE，根据另一个表的定义创建一个空表，包括在原始表中定义的任何列属性和索引

建表定义：

主要是指创建表中的每个字段通过 字段名字段定义 进行创建
字段定义：

字段定义中又主要是指定义字段的类型，除此之外，还可以定义字段是否为空，默认值，是否自增，是否是外键，是否是主键，注释信息等

数据类型 [not null | null] [default (值)] [auto_increment] [unique [key]] [primary [key]] [comment '注释信息']
表选项：

用来设置表的属性，比如存储引擎，字符集，压缩方式等
分区选项：

举例

直接创建表的示例：

CREATE TABLE IF NOT EXISTS my_table (id INT PRIMARY KEY,name VARCHAR(50),age INT,address VARCHAR(100)
) CHARSET=utf8mb4;

该语句创建一个名为 my_table 的表，有四个列，分别为 id、name、age 和 address，其中 id 是主键。存储引擎为 InnoDB，字符集为 utf8mb4。

通过一个查询表达式创建表的示例：

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table AS
SELECT name, age, address FROM my_table WHERE age >= 18;

该语句创建一个名为 my_temp_table 的临时表，列名为 name、age 和 address，与 my_table 中对应列的数据类型相同，数据来自于 my_table 中 age 大于等于 18 的记录。

通过一个旧表创建的示例：

CREATE TABLE IF NOT EXISTS my_new_table LIKE my_table;

该语句创建一个名为 my_new_table 的表，与 my_table 的结构完全一致，但不包括索引和数据。

修改表

alter table 表名
[修改操作,...]

alter table 将会更改表的结构，例如，可以添加或删除列、创建或销毁索引、更改现有列的类型，或重命名列或表本身。

举例说明

添加列：

ALTER TABLE my_table ADD COLUMN email VARCHAR(50) AFTER address;

该语句在 my_table 表中添加一个名为 email 的列，数据类型为 VARCHAR(50)，在 address 列之后。

修改列：

ALTER TABLE my_table MODIFY COLUMN age INT UNSIGNED NOT NULL DEFAULT 0;

该语句将 my_table 表中的 age 列修改为无符号整数类型，非空且默认值为 0。

删除列：

ALTER TABLE my_table DROP COLUMN address;

该语句删除 my_table 表中的 address 列。

添加主键：

ALTER TABLE my_table ADD PRIMARY KEY (id);

该语句在 my_table 表中添加一个名为 id 的主键。

添加外键：

ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);

该语句在 orders 表中添加一个名为 fk_customer_id 的外键，引用 customers 表中的 id 列。

添加索引：

ALTER TABLE my_table ADD INDEX idx_name (name);

该语句在 my_table 表中为 name 列添加一个名为 idx_name 的普通索引。

删除索引：

ALTER TABLE my_table DROP INDEX idx_name;

该语句删除 my_table 表中名为 idx_name 的索引。

更改表名：

ALTER TABLE my_table RENAME TO new_table;

该语句将 my_table 表的名称更改为 new_table。

这些操作只是 ALTER TABLE 语句的一部分，实际使用时可以根据需要选择相应的操作。

删除表

drop [temporary] table [if exists] 表名[,表名...] [restrict|cascade]

这个操作将会删除一个或者多个表

举例

删除表：

DROP TABLE my_table;

该语句将 my_table 表从数据库中删除。

判断表是否存在：

DROP TABLE IF EXISTS my_table;

该语句先判断 my_table 表是否存在，如果存在则删除，否则不执行任何操作。

删除表的同时删除关联的索引：

DROP TABLE my_table CASCADE;

该语句将 my_table 表从数据库中删除，并删除与其关联的所有索引。

删除表的同时删除关联的触发器：

DROP TABLE my_table CASCADE CONSTRAINTS;

该语句将 my_table 表从数据库中删除，并删除与其关联的所有索引和触发器。

一个安全的删表方式

-- 创建备份表并复制数据
CREATE TABLE my_table_backup LIKE my_table;
INSERT INTO my_table_backup SELECT * FROM my_table;-- 删除原表
DROP TABLE my_table;-- 如果删除了错误的表，可以使用备份表恢复数据
-- INSERT INTO my_table SELECT * FROM my_table_backup;-- 如果不再需要备份表，可以删除
-- DROP TABLE my_table_backup;

联的所有索引。

删除表的同时删除关联的触发器：

DROP TABLE my_table CASCADE CONSTRAINTS;

该语句将 my_table 表从数据库中删除，并删除与其关联的所有索引和触发器。

一个安全的删表方式

-- 创建备份表并复制数据
CREATE TABLE my_table_backup LIKE my_table;
INSERT INTO my_table_backup SELECT * FROM my_table;-- 删除原表
DROP TABLE my_table;-- 如果删除了错误的表，可以使用备份表恢复数据
-- INSERT INTO my_table SELECT * FROM my_table_backup;-- 如果不再需要备份表，可以删除
-- DROP TABLE my_table_backup;