【MySQL】(3)約束以及修改數據表

【MySQL】(3)約束以及修改數據表

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). 修改數據表：

針對字段的操作：添加/刪除字段、修改列定義、修改列名稱等

針對約束的操作：添加/刪除各種約束

針對數據表的操作：數據表更名（兩種方式）