MySQL建立、修改和刪除表操作指南

2022-08-25 14:04:18

前言

表是資料庫儲存資料的基本單位。一個表包含若干個欄位或記錄。表的操作包括建立新表、修改表和刪除表。這些操作都是資料庫管理中最基本，也是最重要的操作。

一、建立表

建立表是指在已存在的資料庫中建立新表。這是建立資料庫最重要的一步，是進行其他表操作的基礎。

1、建立表的語法形式

MySQL中，建立表是通過SQL語句CREATE TABLE實現的。其語法格式如下：

create table 表名(屬性名資料型別 [完整性約束條件],
               屬性名資料型別 [完整性約束條件],
               ...
               );

其中，“表名”參數列示所要建立的表的名稱；“屬性名”參數列示表中欄位的名稱；“資料型別”引數指定欄位的資料型別，“完整性約束條件”引數指定欄位的某些特殊約束條件。

2、設定表的主鍵

主鍵是表的一個特殊欄位。該欄位能唯一地標識該表中的每條資訊。主鍵和記錄的關係，如同身份證和人的關係。主鍵用來標識每個記錄，每個記錄的主鍵值都不同。身份證是用來標明人的身份，每個人都具有唯一的身份證號。設定表的主鍵指在建立表時設定表的某個欄位為該表的主鍵。

主鍵的主要目的是幫助MySQL以最快的速度查詢到表中的某一個條資訊。主鍵必須滿足的條件就是主鍵必須是唯一的，表中任意兩條記錄的主鍵欄位的值不能相同；主鍵的值是非空值。主鍵可以是單一的欄位，也可以是多個欄位的組合。

1、單欄位主鍵

主鍵是由一個欄位構成時，可以直接在該欄位的後面加上PRIMARY KEY來設定主鍵。語法規則如下：

屬性名資料型別 PRIMARY KEY

其中，“屬性名”參數列示表中欄位的名稱；“資料型別”引數指定欄位的資料型別。

下面在example1表中設定stu_id作為主鍵，SQL程式碼如下：

create table example1(
	stu_id int PRIMARY KEY,
	stu_name VARCHAR(20),
	stu_sex boolean
);

執行建立語句後，example1表中包含3個欄位。stu_id欄位是整型；stu_name欄位是字串型；stu_sex是布林型。其中，stu_id欄位是主鍵。

2、多欄位主鍵

主鍵是由多個屬性組合而成時，在屬性定義完之後統一設定主鍵。語法規則如下：

primary key(屬性名1,屬性名2,...屬性名n)

3、設定表的外來鍵

外來鍵是表的一個特殊欄位。如果欄位sno是一個表A的屬性，且依賴於表B的主鍵。那麼，稱表B為父表，表A為子表，sno為表A的外來鍵。通過sno欄位將父表B和子表A建立關聯關係。設定表的外來鍵指在建立表設定某個欄位為外來鍵。

設定外來鍵的原則就是必須依賴於資料庫中已存在的父表的主鍵；外來鍵可以為空值。

外來鍵的作用是建立該表與其父表的關聯關係。父表中刪除某條資訊時，子表中與之對應的資訊也必須有相應的改變。例如，stu_id是student表的主鍵，stu_id是grade表的外來鍵。當stu_id為“123”同學退學了，需要從student表中刪除該學生的資訊。那麼，grade表中stu_id為“123”的所有資訊也應該同時刪除。這樣可以保證資訊的完整性。

設定外來鍵的基本語法規則如下：

constraint 外來鍵別名 foreign key (屬性1.1,屬性1.2,...,屬性1.n)
references 表名(屬性2.1,屬性2.2,...,屬性2.n)

其中，“外來鍵別名”引數是為外來鍵的代號；“屬性1”參數列是子表中設定的外來鍵；“表名”引數是指父表的名稱；“屬性2”參數列是父表的主鍵。

4、設定表的非空約束

非空性是指欄位的值不能為空值（NULL）。非空約束將保證所有記錄中該欄位都有值。如果使用者新插入的記錄中，該欄位為空值，則資料庫系統會報錯。例如，在id欄位加上非空約束，id欄位的值就不能為空值。如果插入記錄的id欄位的值為空，該記錄將不能插入。設定表的非空約束是指在建立表時為表的某些特殊欄位加上NOT NULL約束條件。

設定非空約束的基本語法規則如下：

屬性名資料型別 NOT NULL

下面在example4表中設定欄位id和name的非空約束。SQL程式碼如下：

create table example4(
	id int not null primary key,
	name varchar(20) not null,
	stu_id int,
	constraint d_fk foreign key (stu_id)
	references example1(stu_id)
);

程式碼執行後，example4表中包含3個欄位。其中，id欄位為主鍵；id和name欄位為非空欄位，這兩個欄位的值不能為空值（NULL）；stu_id欄位為外來鍵；d_fk為外來鍵的別名；example1表為example4表的父表；example4的外來鍵依賴於父表的主鍵stu_id。

5、設定表的唯一性約束

唯一性是指所有記錄中該欄位的值不能重複出現。設定表的唯一性約束是指在建立表時，為表的某些特殊欄位加上UNIQUE約束條件。唯一性約束將保證所有記錄中該欄位的值不能重複出現。例如，在id欄位加上唯一性約束，所以記錄中id欄位上不能出現相同的值。例如，在表的id欄位加上唯一性約束，那麼每條記錄的id值都是唯一的，不能出現重複的情況。如果一條的記錄的id為‘0001’，那麼該表中就不能出現另一條記錄的id為‘0001’。設定唯一性約束的基本語法規則如下：

屬性名資料型別 UNIQUE

6、設定表的屬性值自動增加

AUTO_INCREMENT是MySQL資料庫中一個特殊的約束條件。其主要用於為表中插入的新記錄自動生成唯一的ID。一個表只能有一個欄位使用AUTO_INCREMENT約束，且該欄位必須為主鍵的一部分。AUTO_INCREMENT約束的欄位可以是任何整數型別（TINYINT、SMALLINT、INT和BIGINT等）。預設情況下，該欄位的值是從1開始自增。

設定屬性值欄位增加的基本語法規則如下：

屬性名資料型別 AUTO_INCREMENT

下面在example6表中設定欄位id的值自動增加。SQL程式碼如下：

create table example6(
	id int primary key auto_increment,
	stu_id int unique,
	name varchar(20) not null
);

程式碼執行後，example6表中包含3個欄位。其中，id欄位為主鍵，且每插入一條新記錄id的值會自動增加；stu_id欄位為唯一值，該欄位的值不能重複；name欄位為非空欄位，該欄位的值不能為空值（NULL）。

在插入記錄時，預設的情況下自增欄位的值從1開始自增。例如，example6表中的id欄位被設定成自動增加，預設情況第一條記錄的id值為1。以後每增加一條記錄，該記錄的id值都會在前一條記錄的基礎上加1。

如果第一條記錄設定了該欄位的初值，那麼新增加的記錄就從初值開始自增。例如，如果example6表中插入的第一條記錄的id值設定為8，那麼再插入記錄的id值就會從8開始往上增加。

7、設定表的屬性的預設值

在建立表時可以指定表中的欄位的預設值。如果插入一條新的記錄時沒有為這個欄位賦值，那麼資料庫系統會自動為這個欄位插入預設值。預設值是通過DEFAULT關鍵字來設定的。設定預設值的基本語法規則如下：

屬性名資料型別 DEFAULT 預設值

下面在example7表中設定欄位id的值自動增加。SQL程式碼如下：

create table example7(
	id int primary key auto_increment,
	stu_id int unique,
	name varchar(20) not null,
	English varchar(20) default 'zero',
	Math float default 0,
	Computer float default 0
);

程式碼執行後，example7表中包含6個欄位。其中，id欄位為主鍵，且每插入一條新記錄id的值會自動增加；stu_id欄位為唯一值，該欄位的值不能重複；name欄位為非空欄位，該欄位的值不能為空值（NULL）；English欄位的預設值為zero；Math欄位和Computer欄位的預設值為0。如果沒有使用DEFAULT關鍵字指定欄位的預設值，也沒有指定欄位為非空，那麼欄位的預設值為空（NULL）。

二、檢視表結構

檢視表結構是指檢視資料庫中已存在的表的定義。檢視表結構的語句包括DESCRIBE語句和SHOW CREATE TABLE語句。通過這兩個語句，可以檢視表的欄位名、欄位的資料型別和完整性約束條件等。

1、檢視表基本結構語句DESCRIBE

MySQL中，DESCRIBE語句可以檢視表的基本定義。其中包括，欄位名、欄位資料型別、是否為主鍵和預設值等。DESCRIBE語句的語法形式如下：

DESCRIBE 表名;

其中，“表名”引數指所要檢視的表的名稱。

下面用DESCRIBE語句檢視example1表的定義，程式碼如下：

DESCRIBE example1;

通過DESCRIBE語句，可以查出example1表包含stu_id、stu_name和stu_sex欄位。同時，結果中顯示了欄位的資料型別（Type）、是否為空（Null）、是否為主外來鍵（Key）、預設值（Default）和額外資訊（Extra）。

DESCRIBE可以縮寫成DESC。

下面直接使用DESC檢視example1表的結構。程式碼如下：

DESC example1;

2、檢視表詳細結構語句SHOW CREATE TABLE

MySQL中，SHOW CREATE TABLE語句可以檢視表的詳細定義。該語句可以檢視表的欄位名、欄位的資料型別、完整性約束條件等資訊。除此之外，還可以檢視表預設的儲存引擎和字元編碼。SHOW CREATE TABLE語句的語法形式如下：

show create table 表名;

其中，“表名”引數指所要檢視的表的名稱。

下面是用SHOW CREATE TABLE語句檢視example1表的定義，程式碼如下：

show create table example1

三、修改表

修改表是指修改資料庫中已存在的表的定義。修改表比重新定義表簡單，不需要重新載入資料，也不會影響正在進行的服務。MySQL中通過ALTER TABLE語句來修改表。修改表包括修改表名、修改欄位資料型別、修改欄位名、增加欄位、刪除欄位、修改欄位的排列位置、更改預設儲存引擎和刪除表的外來鍵約束等。

1、修改表名

表名可以在一個資料庫中唯一的確定一張表。資料庫系統通過表名來區分不同的表。例如，資料庫school中有student表。那麼，student表就是唯一的。在資料庫school中不可能存在另一個名為student的表。MySQL中，修改表名是通過SQL語句ALTER TABLE實現的。其語法形式如下：

ALTER TABLE 舊錶名 RENAME [TO] 新表名;

其中，“舊錶名”參數列示修改前的表名：“新表名”參數列示修改後的新表名；TO引數是可選引數，其是否在語句中出現不會影響語句的執行。

下面是將example0改名為user表，SQL程式碼如下；

alter table example0 rename user;

2、修改欄位的資料型別

欄位的資料型別包括整數型別、浮點數型、字串型、二進位制型別、日期和時間型別等。資料型別決定了資料的儲存格式、約束條件和有效範圍。表中的每個欄位都有資料型別。MySQL中，ALTER TABLE語句也可以修改欄位的資料型別。其基本語法如下：

ALTER TABLE 表名 MODIFY 屬性名資料型別;

其中，“表名”引數指所要修改的表的名稱；“屬性名”引數指需要修改的欄位的名稱；“資料型別”引數指修改後的新資料型別。

下面將修改user表中name欄位的資料型別。SQL程式碼如下：

alter table user MODIFY name varchar(30)

程式碼執行成功後檢視user的表的結構。

我們發現表的型別長度已經變了。

3、修改欄位名

欄位名可以在一張表中唯一的確定一個欄位。資料庫系統通過欄位名來區分表中的不同欄位。例如，student表中包含id欄位。那麼，id欄位在student表中是唯一的。student表中不可能存在另一個名為“id”的欄位。MySQL中，ALTER TABLE語句也可以修改表的欄位名。其基本語法如下：

ALTER TABLE 表名 CHANGE 舊屬性名新屬性名新資料型別;

其中，“舊屬性名”引數指修改前的欄位名；“新屬性名”引數指修改後的欄位名；“新引數型別”引數修改後的資料型別，如不需要修改，則將新資料型別設定成與原來一樣。

1、只修改欄位名

使用ALTER TABLE語句可以直接修改欄位名，不改變該欄位的資料型別。

下面將example1表中stu_name欄位改名為name，且不改變資料型別。由於不改變該欄位的資料型別，需要知道該欄位現在的資料型別。

在執行ALTER TABLE語句之前，先用DESC語句檢視example1表的結構。其中可以看到example1表中存在stu_name欄位，以便與修改後進行對比。並且可以知道stu_name欄位現在的資料型別。desc語句執行後的顯示結果如下：

從查詢結果可以看出，example1表中存在stu_name欄位，且資料型別為varchar(20)。修改example1表中欄位的SQL程式碼如下：

alter table example1 change stu_name name varchar(20);

程式碼執行完畢後，驗證一下是否已經修改了屬性的名字，使用desc語句重新檢視。

查詢結果顯示，example1表中已經不存在stu_name欄位，取而代之的是name欄位。而且，欄位的資料型別沒有發生改變。

2、修改欄位名稱和欄位資料型別

使用ALTER TABLE語句可以直接修改欄位名和該欄位的資料型別。

下面將example1表中stu_sex欄位改名為sex，且資料型別改為INT(2)。SQL程式碼如下：

alter table example1 change stu_sex sex int(2)

我們檢視是否修改成功：

從查詢結果可以看出，我們已經修改成功了。

4、增加欄位

在建立表時，表中的欄位就已經定義完成。如果要增加新的欄位，可以通過ALTER TABLE語句進行增加。在MySQL中，ALTER TABLE語句增加欄位的基本語法如下：

alter table 表名 add 屬性名1 資料型別 [完整性約束條件] [FIRST | AFTER 屬性名2];

其中，“屬性名1”引數指需要增加的欄位的名稱；“資料型別”引數指新增加欄位的資料型別；“完整性約束條件”是可選引數，用來設定新增欄位的完整性約束條件；“FIRST”引數也是可選引數，其作用是將新增欄位新增到“屬性名2”所指的欄位後。如果執行的SQL語句中沒有“FIRST” “AFTER 屬性名2”引數指定新增欄位的位置，新增的欄位預設為表的最後一個欄位。

1、增加無完整性約束條件的欄位

一個完整的欄位包括欄位名、資料型別和完整性約束條件。增加欄位一般包括上述內容。根據實際情況，一些欄位可以不用完整性約束條件進行約束。

下面將在user表中增加一個沒有完整性約束條件約束的phone欄位。SQL程式碼如下：

alter table user add phone varchar(20);

使用desc語句檢視一下是否新增成功。

2、增加有完整性約束條件的欄位

增加欄位時可以設定該欄位的完整性約束條件，如設定欄位是否為空（Null）、是否為主外來鍵（Key）、預設值（Default）和是否為自增型別等約束條件。

下面將在user表中增加一個有非空約束的age欄位，SQL程式碼如下；

alter table user add age int(4) not null;

使用desc查詢是否增加成功。

3、表的第一個位置增加欄位

預設情況下，新增欄位為表的最後一個欄位。如果加上FIRST引數，則可以將新增欄位設定為表的第一個欄位。

下面將在user表中第一個位置增加num欄位，並設定num欄位為主鍵。SQL程式碼如下：

alter table user add num int(8) primary key first;

我們使用desc檢視是否新增成功。

可以看到新增成功。

4、表的指定位置之後增加欄位

在增加欄位時，由於特殊原因需要在表的指定位置增加欄位。如果加上“AFITER 屬性名2”引數，那麼新增的欄位插入在“屬性名2”後面。

下面將在user表的phone欄位後增加address欄位，並設定address欄位為非空。SQL程式碼如下：

alter table user add address varchar(30) not null after phone;

我們使用desc來檢視是否新增屬性正確。

我們看到確實屬性新增到指定位置了。

5、刪除欄位

刪除欄位是指刪除已經定義好的表中的某個欄位。在表建立完之後，如果發現某個欄位需要刪除，可以採用將整個表都刪除，然後重新建立一張表的做法。這樣做是可以達到目的，但必然會影響到表中的資料。而且，操作比較麻煩。MySQL中，ALTER TABLE語句也可以刪除表中的欄位。其基本語法如下：

alter table 表名 drop 屬性名；

其中，“屬性名”引數指需要從表中刪除的欄位的名稱。

下面將從user表中刪除id欄位。SQL程式碼如下：

alter table user drop id;

我們使用desc來檢視是否刪除成功。

我們可以看到沒有了id屬性了。

6、修改欄位的排列位置

建立表的時候，欄位在表中的排列位置就已經確定了。如果要改變欄位在表中的排列位置，則需要ALTER TABLE語句來處理。MySQL中，修改欄位排列位置的ALTER TABLE語句的基本語法如下：

alter table 表名 modify 屬性名1 資料型別 first | after 屬性名2;

其中，“屬性名1”引數指需要修改位置的欄位的名稱：“資料型別”引數指“屬性名1”的資料型別；“first”引數指定位置為表的第一個位置；“after 屬性名2”引數指定“屬性名1”插入在“屬性名2”之後。

1、欄位修改到第一個位置

FIRST引數可以指定欄位為表的第一個欄位。

下面將user表中name欄位修改為該表的第一個欄位。SQL程式碼如下：

alter table user modify name varchar(30) first;

使用desc檢視一下是否修改成功。

我們可以看到name已經到第一個欄位了。

2、欄位修改到指定位置

“AFTER”引數可以將欄位排在表中指定的欄位之後。

下面將user表中sex欄位修改到age欄位之後，SQL程式碼如下：

alter table user modify sex tinyint(1) after age;

我們通過desc來檢視是否修改成功

我們可以看到已經修改成功。

7、更改表的儲存引擎

MySQL儲存引擎是指MySQL資料庫中表的儲存型別。MySQL儲存引擎包括InnoDB、MyISAM、MEMORY等。不同的表型別有著不同的優缺點，在建立表時，儲存引擎就已經設定好了。如果要改變，可以通過重新常見一張表來實現。這樣做是可以達到目的，但必然會影響到表中的資料。而且，操作比較麻煩。MySQL中，ALTER TABLE語句也可以更改表的儲存引擎的型別。其基本語法如下：

ALTER TABLE 表名 ENGINE=儲存引擎名;

其中，“儲存引擎名”引數指設定的新儲存引擎的名稱。

下面將user表的儲存引擎改為MyISAM，SQL程式碼如下：

alter table user engine=MyISAM;

我們使用show create table語句檢視一下是否修改成功。

可以看到已經修改成功。

8、刪除表的外來鍵約束

外來鍵是一個特殊欄位，其將某一表與其父表建立關聯關係。在建立表的時候，外來鍵約束就已經設定好了。由於特殊需要，與父表之間的關聯關係需要去除，要求刪除外來鍵約束。MySQL中，ALTER TABLE 語句也可以刪除表的外來鍵約束。其基本語法如下：

alter table 表名 drop foreign key 外來鍵別名;

其中，“外來鍵別名”引數指建立表時設定的外來鍵的代號。

四、刪除表

刪除表是指刪除資料庫中已存在的表。刪除表時，會刪除表中的所有資料。因此，在刪除表時要特別注意。MySQL中通過DROP TABLE語句來刪除表。由於建立表時可能存在外來鍵約束，一些表成為了與之關聯的表的父表。要刪除這些父表，情況比較複雜。

1、刪除沒有被關聯的普通表

MySQL中，直接使用drop table語句可以刪除沒有被其他關聯的普通表。其基本語法如下：

drop table 表名;

其中，“表名”引數為要刪除的表的名稱。

下面將刪除example5表。SQL程式碼如下：

drop table example5;

2、刪除被其他表關聯的父表

下面我們要刪除example1表。SQL程式碼如下：

drop table example1;

可以看到刪除結果失敗，原因為有外來鍵依賴於該表。因為example4表依賴於example1表。example4表的外來鍵stu_id依賴於example1表的主鍵。example1表是example4表的父表。如果要刪除example4表，必須先去掉這種依賴關係。最簡單直接的辦法是，先刪除子表example4，然後再刪除父表example1。但這樣可能會影響子表的其他資料；另一種辦法是，先刪除子表的外來鍵約束，然後再刪除父表。這種辦法，不會影響子表的其他資料，可以保證資料庫的安全。

首先，刪除example4表的外來鍵約束。先用show create table語句檢視example4表的外來鍵別名，執行如下：

查詢結果顯示，example4表的外來鍵別名為d_fk。然後執行alter table語句，刪除example4表的外來鍵約束。刪除example4表的外來鍵的SQL語句如下：

alter table example4 drop foreign key d_fk;

現在我們再次檢視example4表中還有沒有外來鍵約束。

可以看到已經沒有外來鍵約束了。

現在，可以直接使用drop table語句刪除example1表。SQL程式碼如下：

drop table example1;

補充：MySQL 常用欄位型別

一個資料表是由若干個欄位組成的，一個表十幾個欄位也很正常，每個欄位表示不同的資訊，需要使用不同型別的資料。

所以在建立表的時候，要為每個欄位指定適合的資料型別。

MySQL 中常用的欄位型別有以下這些：

1. 整數型別

資料型別	資料範圍
TINYINT	-128 -- 127
SMALLINT	-32768 -- 32767
MEDIUMINT	-2^23 -- 2^23-1
INT	-2^31 -- 2^31-1
BIGINT	-2^63 -- 2^63-1

2. 字串型別

資料型別	位元組範圍	用途
CHAR(n)	0 -- 255位元組	定長字串
VARCHAR(n)	0 -- 65535位元組	變長字串
TEXT	0 -- 65535位元組	長文字資料
LONGTEXT	0 -- 2^32-1位元組	極大文字資料
BLOB	0 -- 65535位元組	二進位制長文字資料
LONGBLOB	0 -- 2^32-1位元組	二進位制極大文字資料

3. 小數型別

m 表示浮點數的總長度，n 表示小數點後有效位數。

資料型別	資料用法	資料範圍
Float	Float(m,n)	7位有效數
Double	Double(m,n)	15位有效數
Decimal	Decimal(m,n)	28位元有效數

4. 時間型別

資料型別	格式	用途
DATE	YYYY-MM-DD	日期
TIME	HH:MM:SS	時間
YEAR	YYYY	年份
DATETIME	YYYY-MM-DD HH:MM:SS	日期和時間
TIMESTAMP	10位或13位整數（秒數）	時間戳

5. 列舉型別

enum(列舉值1,列舉值2,...)

列舉型別只能在列出的值中選擇一個，如性別。

五、總結

這裡的相關內容還沒有整理完畢，文章後面持續更新，建議收藏。

文章中涉及到的命令大家一定要像我一樣每個都敲幾遍，只有在敲的過程中才能發現自己對命令是否真正的掌握了。

到此這篇關於MySQL建立、修改和刪除表操作的文章就介紹到這了,更多相關MySQL建立修改刪除表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！