前言

表是數據庫存儲數據的基本單位。一個表包含若干個字段或記錄。表的操作包括創(chuàng)建新表、修改表和刪除表。這些操作都是數據庫管理中最基本，也是最重要的操作。

一、創(chuàng)建表

創(chuàng)建表是指在已存在的數據庫中建立新表。這是建立數據庫最重要的一步，是進行其他表操作的基礎。

1、創(chuàng)建表的語法形式

MySQL中，創(chuàng)建表是通過SQL語句CREATE TABLE實現的。其語法格式如下：

create table 表名(屬性名 數據類型 [完整性約束條件],
                  屬性名 數據類型 [完整性約束條件],
                  ...
                  );

其中，“表名”參數表示所要創(chuàng)建的表的名稱；“屬性名”參數表示表中字段的名稱；“數據類型”參數指定字段的數據類型，“完整性約束條件”參數指定字段的某些特殊約束條件。

2、設置表的主鍵

主鍵是表的一個特殊字段。該字段能唯一地標識該表中的每條信息。主鍵和記錄的關系，如同身份證和人的關系。主鍵用來標識每個記錄，每個記錄的主鍵值都不同。身份證是用來標明人的身份，每個人都具有唯一的身份證號。設置表的主鍵指在創(chuàng)建表時設置表的某個字段為該表的主鍵。

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

1、單字段主鍵

主鍵是由一個字段構成時，可以直接在該字段的后面加上PRIMARY KEY來設置主鍵。語法規(guī)則如下：

屬性名 數據類型 PRIMARY KEY

其中，“屬性名”參數表示表中字段的名稱；“數據類型”參數指定字段的數據類型。

下面在example1表中設置stu_id作為主鍵，SQL代碼如下：

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

運行創(chuàng)建語句后，example1表中包含3個字段。stu_id字段是整型；stu_name字段是字符串型；stu_sex是布爾型。其中，stu_id字段是主鍵。

2、多字段主鍵

主鍵是由多個屬性組合而成時，在屬性定義完之后統(tǒng)一設置主鍵。語法規(guī)則如下：

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

3、設置表的外鍵

外鍵是表的一個特殊字段。如果字段sno是一個表A的屬性，且依賴于表B的主鍵。那么，稱表B為父表，表A為子表，sno為表A的外鍵。通過sno字段將父表B和子表A建立關聯關系。設置表的外鍵指在創(chuàng)建表設置某個字段為外鍵。

設置外鍵的原則就是必須依賴于數據庫中已存在的父表的主鍵；外鍵可以為空值。

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

設置外鍵的基本語法規(guī)則如下：

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

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

4、設置表的非空約束

非空性是指字段的值不能為空值（NULL）。非空約束將保證所有記錄中該字段都有值。如果用戶新插入的記錄中，該字段為空值，則數據庫系統(tǒng)會報錯。例如，在id字段加上非空約束，id字段的值就不能為空值。如果插入記錄的id字段的值為空，該記錄將不能插入。設置表的非空約束是指在創(chuàng)建表時為表的某些特殊字段加上NOT NULL約束條件。

設置非空約束的基本語法規(guī)則如下：

屬性名 數據類型 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、設置表的唯一性約束

唯一性是指所有記錄中該字段的值不能重復出現。設置表的唯一性約束是指在創(chuàng)建表時，為表的某些特殊字段加上UNIQUE約束條件。唯一性約束將保證所有記錄中該字段的值不能重復出現。例如，在id字段加上唯一性約束，所以記錄中id字段上不能出現相同的值。例如，在表的id字段加上唯一性約束，那么每條記錄的id值都是唯一的，不能出現重復的情況。如果一條的記錄的id為‘0001’，那么該表中就不能出現另一條記錄的id為‘0001’。設置唯一性約束的基本語法規(guī)則如下：

屬性名 數據類型 UNIQUE

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

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

設置屬性值字段增加的基本語法規(guī)則如下：

屬性名 數據類型 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、設置表的屬性的默認值

在創(chuàng)建表時可以指定表中的字段的默認值。如果插入一條新的記錄時沒有為這個字段賦值，那么數據庫系統(tǒng)會自動為這個字段插入默認值。默認值是通過DEFAULT關鍵字來設置的。設置默認值的基本語法規(guī)則如下：

屬性名 數據類型 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、修改表名

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

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

其中，“舊表名”參數表示修改前的表名：“新表名”參數表示修改后的新表名；TO參數是可選參數，其是否在語句中出現不會影響語句的執(zhí)行。

下面是將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的表的結構。

我們發(fā)現表的類型長度已經變了。

3、修改字段名

字段名可以在一張表中唯一的確定一個字段。數據庫系統(tǒng)通過字段名來區(qū)分表中的不同字段。例如，student表中包含id字段。那么，id字段在student表中是唯一的。student表中不可能存在另一個名為“id”的字段。MySQL中，ALTER TABLE語句也可以修改表的字段名。其基本語法如下：

ALTER TABLE 表名 CHANGE 舊屬性名 新屬性名 新數據類型;

其中，“舊屬性名”參數指修改前的字段名；“新屬性名”參數指修改后的字段名；“新參數類型”參數修改后的數據類型，如不需要修改，則將新數據類型設置成與原來一樣。

1、只修改字段名

使用ALTER TABLE語句可以直接修改字段名，不改變該字段的數據類型。

下面將example1表中stu_name字段改名為name，且不改變數據類型。由于不改變該字段的數據類型，需要知道該字段現在的數據類型。

在執(zhí)行ALTER TABLE語句之前，先用DESC語句查看example1表的結構。其中可以看到example1表中存在stu_name字段，以便與修改后進行對比。并且可以知道stu_name字段現在的數據類型。desc語句執(zhí)行后的顯示結果如下：

從查詢結果可以看出，example1表中存在stu_name字段，且數據類型為varchar(20)。修改example1表中字段的SQL代碼如下：

alter table example1 change stu_name name varchar(20);

代碼執(zhí)行完畢后，驗證一下是否已經修改了屬性的名字，使用desc語句重新查看。

查詢結果顯示，example1表中已經不存在stu_name字段，取而代之的是name字段。而且，字段的數據類型沒有發(fā)生改變。

2、修改字段名稱和字段數據類型

使用ALTER TABLE語句可以直接修改字段名和該字段的數據類型。

下面將example1表中stu_sex字段改名為sex，且數據類型改為INT(2)。SQL代碼如下：

alter table example1 change stu_sex sex int(2)

我們查看是否修改成功：

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

4、增加字段

在創(chuàng)建表時，表中的字段就已經定義完成。如果要增加新的字段，可以通過ALTER TABLE語句進行增加。在MySQL中，ALTER TABLE語句增加字段的基本語法如下：

alter table 表名 add 屬性名1 數據類型 [完整性約束條件] [FIRST | AFTER 屬性名2];

其中，“屬性名1”參數指需要增加的字段的名稱；“數據類型”參數指新增加字段的數據類型；“完整性約束條件”是可選參數，用來設置新增字段的完整性約束條件；“FIRST”參數也是可選參數，其作用是將新增字段添加到“屬性名2”所指的字段后。如果執(zhí)行的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、刪除字段

刪除字段是指刪除已經定義好的表中的某個字段。在表創(chuàng)建完之后，如果發(fā)現某個字段需要刪除，可以采用將整個表都刪除，然后重新創(chuàng)建一張表的做法。這樣做是可以達到目的，但必然會影響到表中的數據。而且，操作比較麻煩。MySQL中，ALTER TABLE語句也可以刪除表中的字段。其基本語法如下：

alter table 表名 drop 屬性名；

其中，“屬性名”參數指需要從表中刪除的字段的名稱。

下面將從user表中刪除id字段。SQL代碼如下：

alter table user drop id;

我們使用desc來查看是否刪除成功。

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

6、修改字段的排列位置

創(chuàng)建表的時候，字段在表中的排列位置就已經確定了。如果要改變字段在表中的排列位置，則需要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等。不同的表類型有著不同的優(yōu)缺點，在創(chuàng)建表時，存儲引擎就已經設定好了。如果要改變，可以通過重新常見一張表來實現。這樣做是可以達到目的，但必然會影響到表中的數據。而且，操作比較麻煩。MySQL中，ALTER TABLE語句也可以更改表的存儲引擎的類型。其基本語法如下：

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

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

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

alter table user engine=MyISAM;

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

可以看到已經修改成功。

8、刪除表的外鍵約束

外鍵是一個特殊字段，其將某一表與其父表建立關聯關系。在創(chuàng)建表的時候，外鍵約束就已經設定好了。由于特殊需要，與父表之間的關聯關系需要去除，要求刪除外鍵約束。MySQL中，ALTER TABLE 語句也可以刪除表的外鍵約束。其基本語法如下：

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

其中，“外鍵別名”參數指創(chuàng)建表時設置的外鍵的代號。

四、刪除表

刪除表是指刪除數據庫中已存在的表。刪除表時，會刪除表中的所有數據。因此，在刪除表時要特別注意。MySQL中通過DROP TABLE語句來刪除表。由于創(chuàng)建表時可能存在外鍵約束，一些表成為了與之關聯的表的父表。要刪除這些父表，情況比較復雜。

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表的外鍵別名，執(zhí)行如下：

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

alter table example4 drop foreign key d_fk;

現在我們再次查看example4表中還有沒有外鍵約束。

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

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

drop table example1;

補充：MySQL 常用字段類型

一個數據表是由若干個字段組成的，一個表十幾個字段也很正常，每個字段表示不同的信息，需要使用不同類型的數據。

所以在創(chuàng)建表的時候，要為每個字段指定適合的數據類型。

MySQL 中常用的字段類型有以下這些：

1. 整數類型

2. 字符串類型

3. 小數類型

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

4. 時間類型

5. 枚舉類型

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

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

五、總結

這里的相關內容還沒有整理完畢，文章后面持續(xù)更新，建議收藏。

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

到此這篇關于MySQL創(chuàng)建、修改和刪除表操作的文章就介紹到這了,更多相關MySQL創(chuàng)建 修改 刪除表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論