本文介绍了如何在MySQL中创建、修改表结构，包括创建表、重命名、添加/删除列、修改数据类型，以及数据完整性约束的设置。还探讨了约束的类型和使用，以及表的删除、重命名和截断操作，是数据库初学者的重要参考资料。
点击上方“罗晓胜”，马上关注，您的支持对我帮助很大
上期文章
/ 前言 /
前面我们都是学习怎么操作数据，下面我们学习怎么去操作表，对表结构进行增删改查。
/ 正文 /
本章要点
• 创建表
• 修改表
• 删除表
• 重命名
• 截断表
• 数据完整性约束
数据库对象命名原则
• 数据库对象命名原则 – 必须由字母开始，长度在 1–30个字符之间。– 名字中只能包含 A–Z, a–z, 0–9, _ (下划线)，$ 和 #。– 同一个数据库服务器用户所拥有的对象名字不能重复。– 名字不能为数据库的保留字。– 名字是大小写不敏感
注：详情可参考本项目-mysql开发规范
建表语句语法
• 建表语法 CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);
用子查询语法创建表
• 使用子查询创建表的语法
CREATE TABLE table[(column, column...)] AS subquery;
• 新表的字段列表必须与子查询中的字段列表匹配 • 字段列表可以省略
CREATE TABLE student2 AS SELECT id, name, age+10 newAge FROM student WHERE id = 1;
• Select列表中的表达式列需要给定别名，如果没有别名会产生错误
表的分类
MySQL 数据表主要支持六种类型 ，分别是：BDB、HEAP、ISAM、MERGE、MYISAM、InnoBDB。
-
事务安全型(transaction-safe)：
-
BDB：Mysql最早的具有事务能力的表的类型，目前已经停止开发
-
InnoDB：在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。
-
-
非事务安全型(non-transaction-safe)：
-
HEAP：是MySQL表中访问最快的表，将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问（注意，这种类型下数据是非持久化设计的，它一般适应于临时表，如果MySQL或者服务器崩溃，表中数据全部丢失。）
-
ISAM：是MyISAM类型出现之前MySQL表使用的默认类型，现在已经被MyISAM代替。
-
MERGE：由一组MyISAM表组成，之所合并主要出于性能上考虑，因为它能够提高搜索速度，提高修复效率，节省磁盘空 间
-
MYISAM：基于ISAM类型，但它增加了许多有用的扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
-
数据类型与列定义
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 | |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度、浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度、浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 |
日期和时间类型：表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| DATE | 3 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 | |
| TIME | 3 '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 | |
| YEAR | 1 1901/2155 | YYYY | 年份值 | |
| DATETIME | 8 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | |
| TIMESTAMP | 4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型：字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
修改表的定义
-
修改表名 ALTER table tablename rename to newtablename;
-
添加列语法：ALTER TABLE table ADD (columnname datatype[DEFAULT expr] [, columnname datatype]...);
-
修改列语法：ALTER TABLE table MODIFY(columnname datatype[DEFAULT expr] [, columnname datatype]...);
-
删除列语法：ALTER TABLE table DROP (columnname [,columnname]);
示例：
-- 修改表名
ALTER table student rename to newstudent;
-- 添加列
ALTER TABLE student ADD column c_weight int(5) not null comment '体重' AFTER `age`; //在哪个字段后面添加
-- 修改列
ALTER TABLE student MODIFY c_weight varchar(20) COMMENT '体重';
-- 删除列
ALTER TABLE student DROP c_weight;
添加新列
ALTER TABLE table ADD (columnname datatype[DEFAULT expr] [, columnname datatype]...);
• 增加列原则：– 可以添加或修改列 – 可指定新添加列的位置，使用AFTER 字段。
修改已存在的列
• 列的修改可以修改数据类型，长度，及默认值。• 修改数据类型：已有的行数据必须为空。• 修改长度原则：数值型修改长度：当长度向小改时，已有行的数该列必须为空；当长度向大改时，可以随意修改。字符型修改长度：当长度向小改时，只要修改后的值能容纳下 当前已有数据的最大值即可，当长度向大改时，可以随意修改。• 修改列的默认值：
-默认值的修改不会影响已经存在的行，只影响新增加的行
• 把student表年龄(age) 列，修改为长度为2 ALTER TABLE student MODIFY age CHAR(2);
• 添加默认值 ALTER TABLE student MODIFY age DEFAULT ‘1’;
删除列
• 可以用DROP子句从表中删除列，包括列的定义和数据。• 删除列原则：
– 列可以有也可以没有数据。– 表中至少保留一列。– 列被删除后，不能再恢复。– 被外键引用的列，不能被删除。
• 删除列语法一 ALTER TABLE emp student COLUMN age;
• 删除列语法二 ALTER TABLE table student (columnname[,columnname]);
• 删除student表的两个字段“name”和“age”。ALTER TABLE student DROP (name,age);
删除、重命名与截断表
删除表
• 删除表语法：
DROP TABLE table；
– 只有表的创建者 – 或具有DROP ANY TABLE权限的用户才能删除表
• 删除表原则：– 表中所有的数据和结构都被删除。– 任何视图和同义词被保留但无效。– 所有与其相关的约束和索引被删除。– 任何未完成的事务被提交。
示例 DROP TABLE student;
重命名表
• 重命名语句语法：alter table RENAME old_name TO new_name; – 必须是对象的所有者
• 把emp表重新命名为empl alter table test_a rename to sys_app;
截断表
• 截断表语法：TRUNCATE TABLE table;
– 执行TRUNCATE语句的前提，必须是表的所有者 – 或者有DELETE ANY TABLE系统权限来截断表。
示例：TRUNCATE TABLE emp;
备注：清空表和截断表的区别 清空表：delete from users；清空表只是清空表中的逻辑数据，但是物理数据不清除，如主键值、索引等不被清除，还是原来的值。
截断表：truncate table users；截断表可以用于删除表中 的所有数据。截断表命令还会回收所有索引的分配页
约束的概念
• 约束 (constraints) 是防止无效数据输入到表中。约束做下面的事：– 多个表之间的具体关系，比如两个表之间的主外键关系。– 表在插入、更新行或者删除行的时候强制表中的数据遵循 约束规则。– 对于成功的操作，约束条件是必须被满足的。– 如果表之间有依赖关系，使用约束可以防止表或表中相关 数据的删除。
• 约束命名 – 约束命名原则：所有的约束定义存储在数据字典中。– 如果给约束一个有意义的名字，约束易于维护，约束命名 必须遵守标准的对象命名规则。– 如果没有给约束命名，Oracle服务器将用默认格式 SYS_Cn产生一个名字，这里 n 是一个唯一的整数，来保 证名称的唯一性。– 建议至少应该给表的主、外键按照命名原则来命名，如可 以采用这样的原则来命名，表名_字段名_约束类型。
约束的类型
| 约束 | 说明 |
|---|---|
| NOT NULL | 指定列不能包含空值 |
| DEFAULT | 默认值，用于保证该字段有默认值。 |
| UNIQUE | 指定列的值或者列的组合的值对于表中所有的行必须 是唯一的 |
| PRIMARY KEY | 表的每行的唯一性标识 |
| FOREIGN KEY | 在列和引用表的一个列建立并且强制的列之间关系 |
| CHECK | 指定一个必须为真的条件 |
生成与维护约束
• 约束的语法如下：CREATE TABLE [schema.] table (column datatype [ DEFAULT expr][column_constraint], ...[table_constraint][,...]);
– 约束可以在两个级别上定义，表级约束与列级约束。– 列级约束能够定义完整性约束的任何类型。– 表级约束除了NOT NULL之外，能够定义完整性约束的任 何类型。
示例
create table if not exists t_stuinfo(
id int primary key, #主键
stuName varchar(20) not null, #非空
gender char(1) check(gender='男' or gender='女'), #检查约束，MySql没有效果但不报错
seat int unique, #唯一约束
age int default 18, #默认（值）约束
majorId int references major(id) #外键约束，MySql没有效果，但不报错
);
现有表中增加/删除约束
• 增加约束语法如下：ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
• 删除约束语法 Alter TABLE tablename Drop Primary key | Unique (column)| Constraint constraint [Cascade]
示例：
-- 添加非空约束
alter table 表名称 modify column 列名 列类型 not null;
-- 添加默认约束
alter table 表名称 modify column 列名 列类型 default 默认值;
-- 添加主键
alter table 表名称 modify column 列名 列类型 primary key;
alter table 表名称 add primary key(id);
-- 删除默认约束
alter table 表名称 modify column 列名 列类型;
-- 删除主键
Alter table student modify column id int;;
Alter table student drop primary key;
-- 删除唯一
alter table 表名称 drop index(索引名) 设置唯一时的名称;
约束的启用和禁用
• 禁用约束 – 如果有大批量数据导入时，我们可以采用禁用约束的方法， 主要的好处，首先效率高，另外有主外键约束的表之间导 入时，不用考虑导入的先后顺序。
• 禁用外键约束语法：SET FOREIGN_KEY_CHECKS=0;
• 启用约束语法：SET FOREIGN_KEY_CHECKS=1;
/ 总结 /
本文主要讲了如何修改表结构等信息，在表创建有问题或者需要拓展时，可以参考文中的操作方法。
往期推荐：
关注我的公众号，学习技术或投稿
长按上图，识别图中二维码即可关注
扫一扫
337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
