欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

【MySQL】(3)约束以及修改数据表_MySQL

程序员文章站 2022-06-03 23:25:27
...

1. 约束

约束保证数据的完整性和一致性,约束分为表级约束和列级约束。约束类型包括:NOT NULL(非空约束)、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、DEFAULT(默认约束)、FOREIGN KEY(外检约束)。

外键约束保证了数据的一致性、完整性,实现了一对一或一对多的关系

外键约束的要求:

(1). 父表和字表必须使用相同的存储引擎,而且禁止使用临时表。

(2). 数据表的存储引擎只能为InnoDB。

(3). 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。

(4). 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。

MySQL配置文件:

default-storage-engine=INNODB

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL);
SHOW CREATE TABLE provinces;
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id));
SHOW INDEX from provinces\G;
SHOW INDEX from users\G;

2. 外键约束的参照操作

(1). CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行

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

(3). RESTRICT:拒绝对父表的删除或更新操作。

(4). NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同

例如:

CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE);
INSERT provinces(pname) VALUES('A');
INSERT provinces(pname) VALUES('B');
INSERT provinces(pname) VALUES('C');
INSERT users1(username, pid) VALUES('Tom', 3);
INSERT users1(username, pid) VALUES('Jerry', 1);
#查看我们添加的数据
SELECT * FROM users1;
#删除一个省份
DELETE FROM provinces WHERE id=3;
#查看省份
SELECT * FROM provinces;
#再查看人员表
SELECT * FROM users1;

3. 表级约束与列级约束

对一个数据列简历的约束,称为列级约束;对多个数据列建立的约束,称为表级约束。列级约束既可以在列定义时声明,也可以在列定义后声明。表级约束只能在列定以后声明。

4. 修改数据表

(1). 添加/删除列

添加单列:

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];

添加多列:

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...);

删除列:

ALTER TABLE tbl_name DROP [COLUMN] col_name;

例如:

SHOW COLUMNS FROM users1;
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
#然后查看一下users1结构
SHOW COLUMNS FROM users1;
ALTER TABLE users1 DROP truename;
ALTER TABLE users1 DROP password, DROP age;
#然后查看一下users1结构
SHOW COLUMNS FROM users1;
(2). 添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);

例如:

CREATE TABLE users2(username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED);
SHOW CREATE TABLE users2;
ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
SHOW COLUMNS FROM users2;
#设置id为主键
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);
(3). 添加唯一约束:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...);

例如:

ALTER TABLE users2 ADD UNIQUE (username);
SHOW CREATE TABLE users2;
(4). 添加外键约束:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference definition;

例如:

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
#查看
SHOW CREATE TABLE users2;
(5). 添加/删除默认约束:

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
例如:

ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;
#设置默认值
ALTER TABLE users2 ALTER age SET DEFAULT 15;
SHOW COLUMNS FROM users2;
#删除默认值
ALTER TABLE users2 ALTER age DROP DEFAULT;
SHOW COLUMNS FROM users2;
(6). 删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

例如:

ALTER TABLE users2 DROP PRIMARY KEY;
# 查看
SHOW COLUMNS FROM users2;
(7). 删除唯一约束

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;

例如:

# 查看索引
SHOW INDEXES FROM users2\G;
ALTER TABLE users2 DROP INDEX username;
#查看
SHOW COLUMNS FROM users2;
# 再查看索引
SHOW INDEXES FROM users2\G;

(8). 删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

例如:

SHOW CREATE TABLE users2;
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
# 查看外键不见了,但是pid索引还在
SHOW CREATE TABLE users2;
ALTER TABLE users2 DROP INDEX pid;
# pid索引也删除了
SHOW CREATE TABLE users2;
(9). 修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

例如:

SHOW CREATE TABLE users2;
# 将id放在最前面
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED FIRST;
SHOW COLUMNS FROM users2;
# 修改字段类型。 注意:由大类型修改为小类型有可能会造成数据的丢失。
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED FIRST;
(10). 修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

例如:

#修改列名称和类型
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;
(11). 数据表更名

方法一:

ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;

方法二:

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]...;

例如:

ALTER TABLE users2 RENAME users3;
SHOW TABLES;
RENAME TABLE users3 TO users2;
SHOW TABLES;


5. 总结

(1). 约束:

按功能划分:NOT NULL,PRIMARY KEY,UNIQUE KEY,DEFAULT, FOREIGN KEY

按数据列的数目划分:表级约束、列级约束

(2). 修改数据表:

针对字段的操作:添加/删除字段、修改列定义、修改列名称等

针对约束的操作:添加/删除各种约束

针对数据表的操作:数据表更名(两种方式)

相关标签: 数据表