溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家簡單講講MySQL中該如何約束及修改數據表，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL中該如何約束及修改數據表這篇文章可以給大家帶來一些實際幫助。
約束：
按功能劃為：
NOT NULL , PRIMARY KEY , UNIQUE KEY , DEFAULT , FOREIGN KEY
按數據列的數目劃為：
表級約束，列級約束
修改數據表：
針對字段的操作：添加 / 刪除字段、修改列定義、 修改列名稱等
針對約束的操作：添加 / 刪除各種約束
針對數據表的操作：數據表更名（兩種方式）
*** 約束保證數據的完整性和一致性
*** 約束分為表級約束和列級約束
*** 約束類型包括：
NOT NULL(非空約束)
PRIMARY KEY(主鍵約束)
UNIQUE KEY(唯一約束)
DEFAULT(默認約束)
FOREIGN KEY(外鍵約束)
1.外鍵約束的要求解析
*** 父表和子表必須使用相同的存儲引擎，而且禁止使用臨時表。
*** 數據表的存儲引擎只能為InnoDB。
*** 外鍵列和參照列必須具有相似的數據類型。其中數字的長度或是否有符號位必須相同；而字符的長度則可以不同。
主鍵是默認自帶索引的 而外鍵列創建時一般參照的是帶有主鍵那一列 因此如果外鍵列沒有創建索引的話就會被MYSQL根據參照列的索引創建一個索引
*** 外鍵列是不可以以一個沒有索引的列作為參照列的
1.參照列必須要創建一個索引（如果用的是主鍵默認自帶索引 所以不用創建）
2.外鍵列隨意 如果不創建索引會被創建
【1】編輯數據表的默認存儲引擎,配置文件/etc/my.cnf
1 [mysqld]2 default-storage-engine=INNODB
【2】 創建省份數據表，查看存儲引擎
mysql> USE test; mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL -> ); mysql> SHOW CREATE TABLE provinces;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb0 | CREATE TABLE `provinces` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `pname` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
【3】創建用戶數據表,其中外鍵列必須和參照列必須有相似的數據類型
mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) -> );
注意：其中有外鍵列的users表為子表，有參照列id列provinces為父表
創建主鍵時自動創建索引，查看父表自動創建的1個索引
mysql> SHOW INDEXES FROM provinces\G;*************************** 1. row *************************** Table: provinces Non_unique: 0 Key_name: PRIMARYSeq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec)
若外鍵列沒有索引，自動創建：
mysql> SHOW INDEXES FROM users\G; *************************** 1. row *************************** Table: users Non_unique: 0 Key_name: PRIMARYSeq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: users Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec)
2.外鍵約束的參照操作
*** cascade ：從父表刪除或更新且自動刪除或更新子表中匹配的行
*** set null ：從父表刪除或更新行，并設置子表中的外鍵列為NULL。如果使用該選項，必須保證子表列沒有指定NOT NULL
*** restrict ：拒絕對父表的刪除或更新操作
*** no action ：標準的SQL的關鍵詞，在MySQL中與restrict相同
CASCADE例：
mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> pname VARCHAR(20) NOT NULL
-> );
mysql> 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
-> );
mysql> INSERT provinces(pname) VALUES('Tom');
mysql> INSERT provinces(pname) VALUES('John');
mysql> INSERT provinces(pname) VALUES('Driver');
mysql> INSERT users1(username,pid) VALUES('Huang',2);
mysql> INSERT users1(username,pid) VALUES('Li',3);
mysql> INSERT users1(username,pid) VALUES('Pan',3);
mysql> INSERT users1(username,pid) VALUES('He',1);
mysql> INSERT users1(username,pid) VALUES('Long',2);
mysql> SELECT * FROM users1;+----+----------+------+| id | username | pid |+----+----------+------+| 1 | Huang | 2 || 2 | Li | 3 || 5 | Pan | 3 || 6 | He | 1 || 7 | Long | 2 |+----+----------+------+mysql> SELECT * FROM provinces;
+----+--------+| id | pname |+----+--------+| 1 | Tom || 2 | John || 3 | Driver |+----+--------+ mysql> DELETE FROM provinces WHERE id=3;
mysql> SELECT * FROM provinces;+----+-------+| id | pname |+----+-------+| 1 | Tom || 2 | John |+----+-------+mysql> SELECT * FROM users1;
+----+----------+------+| id | username | pid |+----+----------+------+| 1 | Huang | 2 || 6 | He | 1 || 7 | Long | 2 |+----+----------+------+
3.表級約束和列級約束
*** 對一個數據列建立的約束，稱為列級約束
*** 對多個數據列建立的約束，稱為表級約束
*** 列級約束既可以在列定義時聲明，也可以在列定義后聲明
*** 表級約束只能在列定以后聲明
4.修改數據表
添加單列
ALTER TABLE tbl_name(數據表名稱) ADD [COLUMN] col_name（列名） column_definition(列定義) [FIRST（插入列到最前面）|AFTER（插入列到指定列的后方） col_name(列名)（如果不寫FIRST和AFTER，則插入到最后一列）]
例：
mysql> SHOW COLUMNS FROM users1;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | |+----------+----------------------+------+-----+---------+----------------+
添加age列到最后一列：
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+
添加password列到username列的后面：
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || password | varchar(32) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+
添加truename列到第一列：
mysql> ALTER TABLE users1 ADD truename VARCHAR(32) NOT NULL FIRST; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| truename | varchar(32) | NO | | NULL | || id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || password | varchar(32) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+
刪除列：
ALTER TABLE tbl_name(數據表名稱) DROP [COLUMN] col_name（列名）
例，刪除truename列：
mysql> ALTER TABLE users1 DROP truename; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || password | varchar(32) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || age | tinyint(3) unsigned | NO | | 10 | |+----------+----------------------+------+-----+---------+----------------+
同時刪除age和password列，（也可同時增加刪除）用逗號分割：
mysql> ALTER TABLE users1 DROP age,DROP password; mysql> SHOW COLUMNS FROM users1; +----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || username | varchar(10) | NO | | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | |+----------+----------------------+------+-----+---------+----------------+
添加 / 刪除主鍵約束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) //添加 ALTER TABLE tbl_name DROP PRIMARY KEY //刪除
添加 / 刪除唯一約束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) //添加
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name //刪除
添加 / 刪除外鍵約束：
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition //添加 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol //刪除
添加 / 刪除默認約束：
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT} //添加
mysql> ALTER TABLE tbl_name ALTER age DROP DEFAULT; //刪除
添加主鍵約束：創建users2數據表，增加id列，設置id列為主鍵：
mysql> CREATE TABLE users2( -> username VARCHAR(20) NOT NULL, -> pid SMALLINT UNSIGNED -> ); mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED; mysql> ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (id); mysql> SHOW COLUMNS FROM users2;+----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(20) | NO | | NULL | || pid | smallint(5) unsigned | YES | | NULL | || id | smallint(5) unsigned | NO | PRI | 0 | |+----------+----------------------+------+-----+---------+-------+
刪除主鍵約束：
mysql> ALTER TABLE users2 DROP PRIMARY KEY;
添加唯一約束：添加username列為唯一約束：
mysql> ALTER TABLE users2 ADD CONSTRAINT OK_users2_username UNIQUE KEY (username)；
刪除唯一約束：
mysql> SHOW INDEXES FROM users2\G;*************************** 1. row *************************** Table: users2 Non_unique: 0 Key_name: OK_users2_username Seq_in_index: 1 Column_name: username Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: users2 Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec) mysql> ALTER TABLE users2 DROP INDEX OK_users2_username; mysql> SHOW INDEXES FROM users2\G; *************************** 1. row *************************** Table: users2 Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec)
添加外鍵約束：添加pid列為外鍵列，provincse數據表中的id為參照列：
mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
刪除外鍵約束：
mysql> SHOW CREATE TABLE users2; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users2 | CREATE TABLE `users2` ( `username` varchar(20) NOT NULL, `pid` smallint(5) unsigned DEFAULT NULL, `id` smallint(5) unsigned NOT NULL DEFAULT '0', `age` tinyint(4) NOT NULL, KEY `pid` (`pid`), CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
添加默認約束：添加age列，設置age列默認15
mysql> ALTER TABLE users2 ADD age TINYINT NOT NULL; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(20) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | 0 | || age | tinyint(4) | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(20) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | 0 | || age | tinyint(4) | NO | | 15 | |+----------+----------------------+------+-----+---------+-------+
刪除默認值：
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT; mysql> SHOW COLUMNS FROM users2; +----------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+-------+| username | varchar(20) | NO | UNI | NULL | || pid | smallint(5) unsigned | YES | MUL | NULL | || id | smallint(5) unsigned | NO | PRI | 0 | || age | tinyint(4) | NO | | NULL | |+----------+----------------------+------+-----+---------+-------+
修改列定義：
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
修改列名稱(不能隨便更改)：
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_dufinition [FIRST|AFTER col_name]
修改數據表名字(不能隨便更改)：
方法一：
ALTER TABLE old_tbl_name RENAME new_tbl_name
方法二：
RENAME TABLE old_tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
MySQL中該如何約束及修改數據表就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
