MySQL学习笔记（三）修改数据表

最新推荐文章于 2025-05-15 14:35:53 发布

原创最新推荐文章于 2025-05-15 14:35:53 发布 · 523 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#约束 #修改 #ALTER

MySQL 专栏收录该内容

15 篇文章

订阅专栏

本文深入探讨MySQL中外键及各类约束的应用与实践，包括非空、主键、唯一、默认及外键约束等，同时讲解了外键的参照操作及其在实际开发中的注意事项。

约束

约束保证数据的完整性和一致性

根据字段的数目，约束分为表级约束（两个或两个以上）和列级约束（单个字段）

约束的类型包括：NOT NULL(非空约束)、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、DEFAULT(默认约束)、FOREIGN KEY(外键约束)

FOREIGN KEY

外键约束：实现一对一或一对多关系，MySQL为关系型数据库的原因之一

外键约束的要求：

（1）父表（子表参照的表）和子表（有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表

（2）数据表的存储引擎只能为InnoDB

（3）外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
（4）外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

编辑数据表的默认存储引擎

MySQL配置文件my.ini中，将default-storage-engine = INNODB

注意：

(1) 当创建表时，也可以采用如下格式修改表的所属引擎：CREATE TABLE user1() ENGINE = InnoDB;

(2) 当创建表之后，也可以修改表的所属引擎

示例：


CREATE TABLE province(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,pname VARCHAR(30) NOT NULL);//创建一个province表
//查看默认引擎是否为修改后的InnoDB
SHOW CREATE TABLE province;
//创建一个users数据表,其中(外键列)pid为外键,参考province表的(参照列)id字段
/*CREATE TABLE users(id SMALLINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL,
                                   //pid SMALLINT
                                   pid BIGINT,
                                   FOREIGN KEY (pid) REFERENCES province (id)
                                   );*/
//由于外键列(SMALLINT)与参照列(BIGINT)的数据类型不一致或符号位不同，因此上述创建的表将发生报错
//因此需要如下修改：
CREATE TABLE users(id SMALLINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL,
                   pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES province (id));
//查看外键列是否创建索引
SHOW INDEXS FROM province;
//纵向查看</span>
SHOW INDEXES FROM province\G;
//查看外键列是否创建索引
SHOW INDEXES FROM users\G;

外键约束的参照操作

（1）CASCADE：从父表删除或更新且自动删除或更新子表中的匹配行

（2）SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL

（3）RESTRICT：拒绝对父表的删除或更新操作

（4）NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

示例：说明外键约束为CASCADE的作用


CREATE TABLE user1(id SMALLINT AUTO_INCREMENT PRIMARY KEY,
                     username VARCHAR(30) NOT NULL,
                     pid SMALLINT UNSIGNED,
                     FOREIGN KEY (pid)REFERENCES province(id) ON DELETE CASCADE);//在参照表province创建的基础上，创建外键表user1

//插入信息时，必须先在父表中添加记录，然后在子表中添加记录
INSERT INTO province (pname) VALUES('A');
INSERT INTO province (pname) VALUES('B');
INSERT INTO province (pname) VALUES('C');
//显示province表结构
SELECT* FROM province;
//向表user1中增加记录
INSERT INTO user1 (username,pid) VULUES ('John',1);
INSERT INTO user1 (username,pid) VULUES (Tom',2);
INSERT INTO user1 (username,pid) VULUES ('Rose',3);
//INSERT INTO user1 (username,pid) VULUES ('John',7);       /*将会报错，因为父表的索引最大为3*/
//显示user1的结构
SELECT* FROM user1;#现在删除父表中索引为3的记录DELETE FROM province WHERE id = 3;
//分别显示父表和子表的结构
SELECT* FROM province;
SELECT* FROM user1;

</pre></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size: 18px; ">可以看到，删除父表索引id为3的记录时，子表中索引为3的记录也随之删除</span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">注意：</span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">实际开发时，<span style="color:#ff0000;">很少用物理的外键约束，因为只有InnoDB引擎支持，常用逻辑的外键约束（定义两张表结构时，按照存在的某种结构方式去定义，但不去使用FOREIGN KEY关键词来定义）</span></span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">列级约束（经常使用）既可以在定义时声明，也可以在列定以后声明；而表级约束（很少使用）只能在列定以后声明。</span></p></blockquote><p><span style="font-size:18px"> </span></p><p></p><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">修改数据表</span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">添加单列</span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="color:#ff00"><span style="font-size:18px"><span style="color:#ff0000;">ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];</span></span></span></p></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">注意：如果未设置[FIRST| AFTER col_name],则新增加的列将位于所有列之后           </span></p></blockquote><p><span style="font-size:18px"> </span></p><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p><span style="font-size:18px">示例：</span></p><p></p><pre name="code" class="sql" style="background-color: rgb(255, 255, 255); ">//查看user1表中的列
SHOW COLUMNS FROM user1;</span>
//增加一个age列
ALTER TABLE user1 age TINYINT UNSIGNED DEFAULT 10;
//显示增加的列
SHOW COLUMNS FROM user1;
//在username之后添加password列
ALTERTABLE user1 ADD COLUMN password VARCHAR(30) NOT NULL AFTER username;