MySQL自學篇（三）

標籤：mysql

查看資料表結構

使用SQL語句建立好資料表之後，可以查看錶結構的定義，以確定表的定義是否正確。在MySQL中，查看錶結構可以使用DESCRIBE 和 SHOW CREATE TABLE語句。

1、查看錶基本結構語句DESCRIBE

 

DESCRIBE/DESC語句可以查看錶的欄位資訊，其中包括：欄位名。欄位資料類型、是否為主鍵、是否有預設值。文法規則：

DESCRIBE 表名；

後者簡寫為：

DESC 表名；

各個欄位的含義分別為：

NULL：表示該列是否可以儲存NULL指

Key：表示該列是否已編製索引。PRI表示該列是表主鍵的一部分；UNI表示該列是UNIQUE索引的一部分；MUL表示在該列中某個給定值允許出現多次。

Default:表示該列是否有預設值。如果有，是多少。

Extra：表示可以擷取的給定列有關的附加資訊，例如AUTO_INCREMENT等。

 

 

2、查看錶詳細結構語句SHOW CREATE TABLE

SHOW CREATE TABLE 語句用來顯示建立表時的CREATE TABLE語句，文法格式如下：

SHOW CREATE TABLE<表名\G>;

使用SHOW CREATE TABLE語句，不僅可以查看建立時候的詳細語句，而且還可以查看儲存引擎和字元編碼

PS：

如果不佳“\G”參數，顯示的結果可能非常混亂，加上參數“\G”之後，可使顯示結果更加直觀，易於觀察。

 

 

修改資料表1、修改表名

 

MySQL是通過ALTER TABLE語句來實現表名的修改的，具體的文法規則如下：

ALTER  TABLE  <舊錶名>  RENAME  TO  <新表名>;

其中TO為選擇性參數，使用與否均不影響結果。

範例：

將資料表department改名為dept

alter table department rename dept;

可以使用DESC查看修改前後兩個表的結構，修改表名並不修改表結構，因此修改名稱後的表和修改之前的表結構是完全相同的。

 

2、修改欄位的資料類型

 

修改欄位的資料類型就是把欄位的資料類型轉換成另一種資料類型。在MySQL中修改欄位資料類型的文法規則如下：

ATTER  TABLE  <表名>  MODIFY  <欄位名>  <資料類型>

範例：

將資料表department2中的name欄位的資料類型有VARCHAR(22)修改成VARCHAR(30)。

alter table department2 modify name varchar(30);

 

3、修改欄位名

 

MySQL中修改欄位名的文法規則如下：

ALTER  TABLE  <表名>  CHANGE  <舊欄位名>  <新欄位名>  <新資料類型>

其中，“舊欄位名”是指的修改前的欄位名；“新欄位名”指修改後的欄位名；“新資料類型”指的是修改後的資料類型；如果不需要修改欄位的資料類型，可以將新資料類型設定成與原來一樣即可，但是資料類型不可為空。

範例：

將資料表department欄位location改名為local，資料類型保持不變，SQL語句如下：

alter table department change location local varchar(50);

 

 

4、添加欄位

添加欄位文法格式如下：

ALTER  TABLE  <表名>  ADD  <新欄位名>  <資料類型>  [約束條件]  [FIRST |  AFTER  已存在的欄位名 ] ;

新欄位名為需要添加的欄位名稱；“FIRST”為選擇性參數，其作用是將新添加的欄位設定為表的第一個欄位；“AFTER”為選擇性參數，其作用是將新添加的欄位添加到指定的“已存在欄位名”的後面。如果SQL語句中沒有“FIRST”、“AFTER”這兩個參數，則預設將這個欄位放在資料表的最後列。

A、添加無完整性條件約束的欄位

範例：在資料表department中添加一個沒有添加完整性條件約束的INT類型的欄位managerId（部門經理編號），SQL語句如下：

ALTER  TABLE  department  ADD  managerId  INT(10);

 

B、添加有完整性條件約束的欄位

範例：在資料表department中添加一個不可為空的VARCHAR(25)類型的欄位column1，SQL語句如下：

ALTER TABLE department ADD column1 VARCHAR(12) NOT NULL;

 

 

C、在表的第一列添加一個欄位

範例：
在資料表department中添加一個INT類型的欄位column2，SQL語句如下

alter table department add column2 int(10) first;

 

D、在表的指定列之後添加一個欄位

範例：

在資料表department中添加一個惡INT類型的欄位column3在column2列之後。

alter table department add column3 int(10) after column2;

 

 

5、刪除欄位

刪除欄位是將資料表中的某個欄位從表中刪除，文法格式為：

ALTER  TABLE  <表名>  DROP  <欄位名>

範例：

刪除資料表department中column2欄位

alter table department drop column2;

 

 

6、修改欄位的排列位置

文法格式：

ALTER  TABLE  <表名>  MODIFY  <欄位1>  <資料類型>  FIRST|AFTER  <欄位2>

“欄位1”值要修改位置的欄位，“資料類型”值“欄位1”的資料類型，“FIRST”為選擇性參數，指“欄位1”修改為表的第一個欄位，“AFTER欄位2”指的是將“欄位1”插入到“欄位2”後面。

A、修改欄位為表的第一個欄位

範例：

將資料表department中的column1欄位修改為表的第一個欄位，SQL語句：

alter table department modify column1 varchar(12) first;

 

 

B、修改欄位到表的指定列之後

範例：

將資料表department中的column1欄位修改為表的managerId欄位的後面。SQL語句如下：

 alter table department modify column1 varchar(12) after managerId;

 

 

7、修改表的儲存引擎

 

儲存引擎是MySQL中的資料存放區在檔案或者記憶體中時採用的不同技術實現。MySQL中主要的儲存引擎有:MyISAM、InnoDB、MEMORY、BDB、FEDERATED等。可以使用SHOW ENGINES；語句查看系統支援的儲存引擎。

更改表的儲存引擎的文法格式如下：

ALTER  TABLE  <表名>  ENGINE=<更改後的儲存引擎名>；

範例：

將資料表student的儲存引擎修改為MyISAM。

 

 

 

8、刪除表的外鍵約束

刪除外鍵的文法：

ALTER  TABLE  <表名>  DROP  FOREIGN  KEY  <外鍵約束名>

範例：

建立employee9

 

 create table employee9

(

id INT(11) primary key ,

name varchar(20),

deptId INT(11),

salary float,

constraint fk_emp_dept foreign key(deptId) references department(id)

);

 

刪除外鍵

alter table employee9 drop foreign key fk_emp_dept;

 

 

 

刪除資料表1、刪除沒有被關聯的表

MySQL中，使用DROP TABLE可以一次刪除一個或多個沒有被其他表關聯的資料表。文法格式：

DROP  TABLE  [IF  EXISTS]  表1，表2，...表n；

 

 

 

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

 

資料表之間存在外部索引鍵關聯的情況，如果直接刪除父表，結果會顯示失敗。原因是直接刪除，將破壞表的參照完整性。

如果一定要刪除，可以先刪除與之關聯的子表，在刪除父表，只是這樣同時刪除了兩個表中的資料。如果要將子表保留，這是要單獨刪除父表，只需將關聯的表的外鍵約束條件取消，然後刪除父表

範例：

建立dept表

create table dept(id INT(11) primary key ,name varchar(10),location varchar(30));

 

建立emp表

 create table emp

(

id INT(10) primary key,

name varchar(20),

deptId INT(10),

constraint fk_emp_dept foreign key(deptId) references dept(id)

);

 

修改外部索引鍵關聯

alter table emp drop foreign key  fk_emp_dept;

 

刪除dept表

drop dept;

MySQL自學篇（三）