溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家簡單講講MySQL中該如何約束及修改數(shù)據(jù)表，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL中該如何約束及修改數(shù)據(jù)表這篇文章可以給大家?guī)硪恍嶋H幫助。
約束：
按功能劃為：
NOT NULL , PRIMARY KEY , UNIQUE KEY , DEFAULT , FOREIGN KEY
按數(shù)據(jù)列的數(shù)目劃為：
表級約束，列級約束
修改數(shù)據(jù)表：
針對字段的操作：添加 / 刪除字段、修改列定義、 修改列名稱等
針對約束的操作：添加 / 刪除各種約束
針對數(shù)據(jù)表的操作：數(shù)據(jù)表更名（兩種方式）
*** 約束保證數(shù)據(jù)的完整性和一致性
*** 約束分為表級約束和列級約束
*** 約束類型包括：
NOT NULL(非空約束)
PRIMARY KEY(主鍵約束)
UNIQUE KEY(唯一約束)
DEFAULT(默認約束)
FOREIGN KEY(外鍵約束)
1.外鍵約束的要求解析
*** 父表和子表必須使用相同的存儲引擎，而且禁止使用臨時表。
*** 數(shù)據(jù)表的存儲引擎只能為InnoDB。
*** 外鍵列和參照列必須具有相似的數(shù)據(jù)類型。其中數(shù)字的長度或是否有符號位必須相同；而字符的長度則可以不同。
主鍵是默認自帶索引的 而外鍵列創(chuàng)建時一般參照的是帶有主鍵那一列 因此如果外鍵列沒有創(chuàng)建索引的話就會被MYSQL根據(jù)參照列的索引創(chuàng)建一個索引
*** 外鍵列是不可以以一個沒有索引的列作為參照列的
1.參照列必須要創(chuàng)建一個索引（如果用的是主鍵默認自帶索引 所以不用創(chuàng)建）
2.外鍵列隨意 如果不創(chuàng)建索引會被創(chuàng)建
【1】編輯數(shù)據(jù)表的默認存儲引擎,配置文件/etc/my.cnf
1 [mysqld]2 default-storage-engine=INNODB
【2】 創(chuàng)建省份數(shù)據(jù)表，查看存儲引擎
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】創(chuàng)建用戶數(shù)據(jù)表,其中外鍵列必須和參照列必須有相似的數(shù)據(jù)類型
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為父表
創(chuàng)建主鍵時自動創(chuàng)建索引，查看父表自動創(chuàng)建的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)
若外鍵列沒有索引，自動創(chuàng)建：
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 ：從父表刪除或更新行，并設(shè)置子表中的外鍵列為NULL。如果使用該選項，必須保證子表列沒有指定NOT NULL
*** restrict ：拒絕對父表的刪除或更新操作
*** no action ：標準的SQL的關(guān)鍵詞，在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.表級約束和列級約束
*** 對一個數(shù)據(jù)列建立的約束，稱為列級約束
*** 對多個數(shù)據(jù)列建立的約束，稱為表級約束
*** 列級約束既可以在列定義時聲明，也可以在列定義后聲明
*** 表級約束只能在列定以后聲明
4.修改數(shù)據(jù)表
添加單列
ALTER TABLE tbl_name(數(shù)據(jù)表名稱) 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(數(shù)據(jù)表名稱) 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; //刪除
添加主鍵約束：創(chuàng)建users2數(shù)據(jù)表，增加id列，設(shè)置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數(shù)據(jù)表中的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列，設(shè)置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]
修改數(shù)據(jù)表名字(不能隨便更改)：
方法一：
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中該如何約束及修改數(shù)據(jù)表就先給大家講到這里，對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼