本篇我们就来介绍MySQL中表相关的操作目录表表的组成要素列定义Schema行数据表的元数据与物理属性存储引擎字符集与校对规则索引分区表空间表的类型创建表表达式核心参数解析修改表表达式核心参数解析查看表查看表列表SHOW TABLES查看表结构DESCRIBE / SHOW COLUMNS查看创建表语句SHOW CREATE TABLE查看表状态SHOW TABLE STATUS清空表表达式说明简单示例删除表表达式参数解析简单示例表在MySQL这样的关系型数据库管理系统RDBMS中表Table是数据存储的核心逻辑单元也是我们日常操作中最常打交道的对象MySQL 遵循关系模型理论表是“关系”的具体实现。从用户视角看它是一个二维结构行Row也称记录Record代表一个实体的具体实例。例如一张员工表中的每一行对应一位具体的员工。列Column也称字段Field代表实体的一种属性。例如员工表中的“姓名”、“工号”、“部门”等列。表的组成要素列定义Schema每一列都有明确的定义包括列名在同一表中唯一通常遵循小写加下划线的命名规范。数据类型规定该列能存储什么类型的值如INT整数、VARCHAR(n)可变长字符串、DATE日期、DECIMAL精确小数等。选择合适的数据类型对性能至关重要。约束Constraints对列的值施加规则保证数据完整性。NOT NULL禁止空值。DEFAULT指定默认值。PRIMARY KEY主键唯一标识一行。UNIQUE列值必须唯一。FOREIGN KEY外键引用另一张表的主键维护参照完整性。CHECK检查条件MySQL 8.0 支持。其他属性如AUTO_INCREMENT自动递增、COMMENT注释等。行数据行是实际存储的数据记录。MySQL 将行数据按照表的定义格式存储在磁盘上或内存中。每行都有一个隐式的行ID除非有主键用于内部管理。表的元数据与物理属性除了列和行表还拥有一系列元数据控制其行为和物理存储。存储引擎MySQL 采用插件式存储引擎架构表可以由不同的引擎管理。最常用的引擎是InnoDB默认它支持事务、行级锁、外键和崩溃恢复。其他引擎如MyISAM不支持事务表级锁适合只读或日志型应用、MEMORY数据存于内存速度快但易失等。创建表时可通过ENGINEInnoDB指定。字符集与校对规则表可以设置默认的字符集和校对规则该设置将作为表中字符串类型列如CHAR,VARCHAR,TEXT的默认值。例如CREATE TABLE t (name VARCHAR(50)) DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;索引索引是提高查询效率的数据结构与表紧密相关。MySQL 使用 B树InnoDB或哈希MEMORY等结构实现索引。索引可以建在一列或多列上有普通索引、唯一索引、全文索引、空间索引等类型。创建索引的语句CREATE INDEX idx_name ON t (name);索引在逻辑上属于表但在物理上与表数据分开存储但 InnoDB 的聚簇索引将数据和主键索引存储在一起。分区对于超大型表MySQL 支持分区表即将一张表的数据按规则如范围、列表、哈希分散存储在不同的物理分区中但逻辑上仍是一张表。这有助于管理、备份和查询性能。表空间InnoDB 引擎将表数据和索引存储在表空间tablespace中。默认情况下每张表对应一个独立的.ibd文件独立表空间也可以使用通用表空间。这是物理层面的概念但对用户透明。表的类型普通表永久存储数据的基础表。临时表使用CREATE TEMPORARY TABLE创建只在当前会话可见会话结束自动删除常用于复杂查询的中间结果存储。派生表查询中由子查询生成的虚拟表仅存在于查询执行期间。视图View虽然名字中有“表”但视图是虚拟表不存储数据只存储定义SELECT 语句对视图的查询会被重写为对基表的查询。创建表表达式CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition, ...) [table_options] [partition_options]create_definition列定义、索引、约束等。table_options表级选项如引擎、字符集、注释等。partition_options分区选项高级。核心参数解析列定义col_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [COMMENT string]data_type如INT,VARCHAR(50),DATE等。NOT NULL不允许空值。DEFAULT指定默认值。AUTO_INCREMENT自动增长通常用于主键。COMMENT列注释。约束PRIMARY KEY (col1, col2)主键。UNIQUE KEY (col)唯一约束。FOREIGN KEY (col) REFERENCES parent_tbl (parent_col)外键需 InnoDB。CHECK (expr)检查约束MySQL 8.0 支持。索引{INDEX | KEY} [index_name] (col_list)可加速查询。表选项ENGINE storage_engine指定存储引擎如InnoDB默认、MyISAM。AUTO_INCREMENT n设置自增起始值。DEFAULT [CHARACTER SET | CHARSET] charset_name默认字符集。COLLATE collation_name默认校对规则。COMMENT string表注释。ROW_FORMAT {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}行格式。分区选项PARTITION BY ...用于将大表分割为多个物理部分。修改表表达式ALTER TABLE tbl_name [alter_option [, alter_option] ...]其中alter_option可以是多种操作如添加列、修改列、删除列、重命名表、修改表选项等。核心参数解析添加列ADD [COLUMN] col_name definition [FIRST | AFTER col_name]删除列DROP [COLUMN] col_name修改列定义MODIFY [COLUMN] col_name new_definition [FIRST | AFTER col_name]保留列名CHANGE [COLUMN] old_col_name new_col_name definition可同时改名重命名表RENAME [TO | AS] new_tbl_name添加/删除约束ADD PRIMARY KEY (col_list)DROP PRIMARY KEYADD FOREIGN KEY ...DROP FOREIGN KEY fk_name添加/删除索引ADD INDEX index_name (col_list)DROP INDEX index_name修改表选项如ENGINEInnoDB、AUTO_INCREMENT100、COMMENT new comment等。其他ENABLE/DISABLE KEYS用于 MyISAM 批量插入优化RENAME INDEX old_name TO new_nameMySQL 8.0等。查看表查看表列表SHOW TABLES表达式SHOW [FULL] TABLES [FROM db_name] [LIKE pattern | WHERE expr]参数FULL显示表类型BASE TABLE 或 VIEW。FROM指定数据库默认为当前库。LIKE模式匹配表名。WHERE条件过滤MySQL 5.7.6。示例SHOW TABLES FROM mydb LIKE emp%;查看表结构DESCRIBE / SHOW COLUMNS表达式{DESCRIBE | DESC} tbl_name [col_name | LIKE pattern] SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE pattern | WHERE expr]参数SHOW COLUMNS支持FULL显示额外信息如权限、注释。示例DESC employees; SHOW FULL COLUMNS FROM employees;查看创建表语句SHOW CREATE TABLE表达式SHOW CREATE TABLE tbl_name示例SHOW CREATE TABLE employees\G \G 纵向显示美观查看表状态SHOW TABLE STATUS表达式SHOW TABLE STATUS [FROM db_name] [LIKE pattern | WHERE expr]参数显示表的详细信息如引擎、行数近似、数据长度、索引长度、创建时间等。示例SHOW TABLE STATUS FROM mydb WHERE Name employees;清空表表达式TRUNCATE [TABLE] tbl_name说明快速删除表中所有数据并重置自增值相当于 DROP CREATE但更快。属于 DDL 操作不可回滚。对于 InnoDB如果是基于全局共享表空间重置自增值如果是独立表空间效果类似。简单示例TRUNCATE TABLE employees; -- 清空数据表结构保留删除表表达式DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]参数解析TEMPORARY只删除临时表。IF EXISTS防止因表不存在而报错会生成警告。RESTRICT | CASCADE在 MySQL 中无实际效果仅为语法兼容可忽略。简单示例DROP TABLE IF EXISTS temp_employees;本期内容介绍到这里了。封面图自取
MySQL数据库：表及其表相关的操作
本篇我们就来介绍MySQL中表相关的操作目录表表的组成要素列定义Schema行数据表的元数据与物理属性存储引擎字符集与校对规则索引分区表空间表的类型创建表表达式核心参数解析修改表表达式核心参数解析查看表查看表列表SHOW TABLES查看表结构DESCRIBE / SHOW COLUMNS查看创建表语句SHOW CREATE TABLE查看表状态SHOW TABLE STATUS清空表表达式说明简单示例删除表表达式参数解析简单示例表在MySQL这样的关系型数据库管理系统RDBMS中表Table是数据存储的核心逻辑单元也是我们日常操作中最常打交道的对象MySQL 遵循关系模型理论表是“关系”的具体实现。从用户视角看它是一个二维结构行Row也称记录Record代表一个实体的具体实例。例如一张员工表中的每一行对应一位具体的员工。列Column也称字段Field代表实体的一种属性。例如员工表中的“姓名”、“工号”、“部门”等列。表的组成要素列定义Schema每一列都有明确的定义包括列名在同一表中唯一通常遵循小写加下划线的命名规范。数据类型规定该列能存储什么类型的值如INT整数、VARCHAR(n)可变长字符串、DATE日期、DECIMAL精确小数等。选择合适的数据类型对性能至关重要。约束Constraints对列的值施加规则保证数据完整性。NOT NULL禁止空值。DEFAULT指定默认值。PRIMARY KEY主键唯一标识一行。UNIQUE列值必须唯一。FOREIGN KEY外键引用另一张表的主键维护参照完整性。CHECK检查条件MySQL 8.0 支持。其他属性如AUTO_INCREMENT自动递增、COMMENT注释等。行数据行是实际存储的数据记录。MySQL 将行数据按照表的定义格式存储在磁盘上或内存中。每行都有一个隐式的行ID除非有主键用于内部管理。表的元数据与物理属性除了列和行表还拥有一系列元数据控制其行为和物理存储。存储引擎MySQL 采用插件式存储引擎架构表可以由不同的引擎管理。最常用的引擎是InnoDB默认它支持事务、行级锁、外键和崩溃恢复。其他引擎如MyISAM不支持事务表级锁适合只读或日志型应用、MEMORY数据存于内存速度快但易失等。创建表时可通过ENGINEInnoDB指定。字符集与校对规则表可以设置默认的字符集和校对规则该设置将作为表中字符串类型列如CHAR,VARCHAR,TEXT的默认值。例如CREATE TABLE t (name VARCHAR(50)) DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;索引索引是提高查询效率的数据结构与表紧密相关。MySQL 使用 B树InnoDB或哈希MEMORY等结构实现索引。索引可以建在一列或多列上有普通索引、唯一索引、全文索引、空间索引等类型。创建索引的语句CREATE INDEX idx_name ON t (name);索引在逻辑上属于表但在物理上与表数据分开存储但 InnoDB 的聚簇索引将数据和主键索引存储在一起。分区对于超大型表MySQL 支持分区表即将一张表的数据按规则如范围、列表、哈希分散存储在不同的物理分区中但逻辑上仍是一张表。这有助于管理、备份和查询性能。表空间InnoDB 引擎将表数据和索引存储在表空间tablespace中。默认情况下每张表对应一个独立的.ibd文件独立表空间也可以使用通用表空间。这是物理层面的概念但对用户透明。表的类型普通表永久存储数据的基础表。临时表使用CREATE TEMPORARY TABLE创建只在当前会话可见会话结束自动删除常用于复杂查询的中间结果存储。派生表查询中由子查询生成的虚拟表仅存在于查询执行期间。视图View虽然名字中有“表”但视图是虚拟表不存储数据只存储定义SELECT 语句对视图的查询会被重写为对基表的查询。创建表表达式CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition, ...) [table_options] [partition_options]create_definition列定义、索引、约束等。table_options表级选项如引擎、字符集、注释等。partition_options分区选项高级。核心参数解析列定义col_name data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [COMMENT string]data_type如INT,VARCHAR(50),DATE等。NOT NULL不允许空值。DEFAULT指定默认值。AUTO_INCREMENT自动增长通常用于主键。COMMENT列注释。约束PRIMARY KEY (col1, col2)主键。UNIQUE KEY (col)唯一约束。FOREIGN KEY (col) REFERENCES parent_tbl (parent_col)外键需 InnoDB。CHECK (expr)检查约束MySQL 8.0 支持。索引{INDEX | KEY} [index_name] (col_list)可加速查询。表选项ENGINE storage_engine指定存储引擎如InnoDB默认、MyISAM。AUTO_INCREMENT n设置自增起始值。DEFAULT [CHARACTER SET | CHARSET] charset_name默认字符集。COLLATE collation_name默认校对规则。COMMENT string表注释。ROW_FORMAT {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}行格式。分区选项PARTITION BY ...用于将大表分割为多个物理部分。修改表表达式ALTER TABLE tbl_name [alter_option [, alter_option] ...]其中alter_option可以是多种操作如添加列、修改列、删除列、重命名表、修改表选项等。核心参数解析添加列ADD [COLUMN] col_name definition [FIRST | AFTER col_name]删除列DROP [COLUMN] col_name修改列定义MODIFY [COLUMN] col_name new_definition [FIRST | AFTER col_name]保留列名CHANGE [COLUMN] old_col_name new_col_name definition可同时改名重命名表RENAME [TO | AS] new_tbl_name添加/删除约束ADD PRIMARY KEY (col_list)DROP PRIMARY KEYADD FOREIGN KEY ...DROP FOREIGN KEY fk_name添加/删除索引ADD INDEX index_name (col_list)DROP INDEX index_name修改表选项如ENGINEInnoDB、AUTO_INCREMENT100、COMMENT new comment等。其他ENABLE/DISABLE KEYS用于 MyISAM 批量插入优化RENAME INDEX old_name TO new_nameMySQL 8.0等。查看表查看表列表SHOW TABLES表达式SHOW [FULL] TABLES [FROM db_name] [LIKE pattern | WHERE expr]参数FULL显示表类型BASE TABLE 或 VIEW。FROM指定数据库默认为当前库。LIKE模式匹配表名。WHERE条件过滤MySQL 5.7.6。示例SHOW TABLES FROM mydb LIKE emp%;查看表结构DESCRIBE / SHOW COLUMNS表达式{DESCRIBE | DESC} tbl_name [col_name | LIKE pattern] SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE pattern | WHERE expr]参数SHOW COLUMNS支持FULL显示额外信息如权限、注释。示例DESC employees; SHOW FULL COLUMNS FROM employees;查看创建表语句SHOW CREATE TABLE表达式SHOW CREATE TABLE tbl_name示例SHOW CREATE TABLE employees\G \G 纵向显示美观查看表状态SHOW TABLE STATUS表达式SHOW TABLE STATUS [FROM db_name] [LIKE pattern | WHERE expr]参数显示表的详细信息如引擎、行数近似、数据长度、索引长度、创建时间等。示例SHOW TABLE STATUS FROM mydb WHERE Name employees;清空表表达式TRUNCATE [TABLE] tbl_name说明快速删除表中所有数据并重置自增值相当于 DROP CREATE但更快。属于 DDL 操作不可回滚。对于 InnoDB如果是基于全局共享表空间重置自增值如果是独立表空间效果类似。简单示例TRUNCATE TABLE employees; -- 清空数据表结构保留删除表表达式DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]参数解析TEMPORARY只删除临时表。IF EXISTS防止因表不存在而报错会生成警告。RESTRICT | CASCADE在 MySQL 中无实际效果仅为语法兼容可忽略。简单示例DROP TABLE IF EXISTS temp_employees;本期内容介绍到这里了。封面图自取
