資料庫(MySQL)表基本操作

標籤：約束   資料庫操作   use   ack   開始   儲存引擎   結構   schema   core   

資料庫表基本操作

• 思前想後，最終還是把部落格的名字改成了資料庫表基本操作，以前叫SQL語句大全，感覺用“大全”這個名詞太大了，而文章中介紹的內容有限而且也不深。如果您在閱讀的過程中發現有不完善的地方，歡迎補充說明。

建立資料庫

　　一提筆就講建立資料庫，感覺好像是從半道上開始的。建立資料庫前總得安裝資料庫吧，不管是SQL Server、mysql、Oracle。但是如果這樣寫下去，不知道這篇部落格還能不能完成，那就預設是安裝好資料庫了吧，本片部落格如下都是使用mysql資料庫，首先是登入資料庫操作（mysql -u root -p）。

1. 查看當前所存在的資料庫：show databases;查詢結果中的mysql資料庫是必須的，用來描述使用者的存取權限；information_schema資料庫提供了訪問資料庫中繼資料的方式，其中儲存著關於MySQL伺服器所維護的所有其他資料庫的資訊。如資料庫名，資料庫的表，表欄的資料類型與存取權限等；performance_schema用於收集資料庫伺服器績效參數。
2. 建立新的資料庫：create database test_db;test_db為要建立的資料庫名稱，該名稱不能與現有的資料庫重名。
3. 查看建立好的資料庫定義：show create database test_db;

4. 刪除資料庫：drop database test_db;test_db為要刪除的資料庫名稱。

5. 查看資料庫儲存引擎：show engines;Support列為YES表示引擎可用，DEFAULT表示資料庫當前預設的引擎。可以通過my.ini檔案設定資料庫的預設儲存引擎。

   

建立資料表

　　在資料庫中，資料表是最重要、最基本的操作對象，資料存放區的基本單位。本節將介紹資料表的增、刪、改、查操作，使讀者能夠熟練掌握資料表的基本操作，理解約束、預設和規則的含義。

• 建立資料表：資料表屬於資料庫，在建立資料表前，應該使用use test_db;指定在哪個資料庫（test_db）下進行操作，如果沒有指定資料庫會拋出No database selected錯誤。建立資料表的文法規則如下：

create table <表名>(

欄位名 資料類型 [列層級約束條件] [預設值],

...

[表層級約束條件]

);

1. 沒有任何約束員工表的建立：create table user(id int(11), name varchar(20), sex tinyint, age tinyint, salary float);執行該語句後，會建立一個名稱為user的資料表。
2. 使用主鍵約束建立員工表，主鍵是表中一列或多列的組合，主鍵約束要求主鍵列的資料唯一且不空。主鍵能夠唯一地標識表中的一條記錄，可以結合外鍵來定義不同資料表間的關係，並且可以加快資料庫查詢的速度。主鍵分為兩種：單欄位主鍵和多欄位聯合主鍵。
   • 單欄位主鍵：create table user1(id int(11) primary key, name varchar(20), sex tinyint, age tinyint, salary float);同：create table user1(id int(11), name varchar(20), sex tinyint, age tinyint, salary float, primary key(id));
   • 多欄位主鍵：create table user1(name varchar(20), sex tinyint, age tinyint, salary float, primary key(name,age));
3. 使用外鍵約束：外鍵用來在兩個表之間建立連結，可以是一列或者多列。一個表可以有一個或者多個外鍵。外鍵對應的是參照完整性，一個表的外鍵可以為空白，若不為空白，則每個外索引值必須等於另一個表中主鍵的某個值。外鍵可以不是本表的主鍵，但對應另一個表的主鍵，外鍵主要作用是保證資料引用的完整性，定義外鍵後，不允許刪除在另一個表中具有關聯關係的行。
   • 建立外鍵規則：[constraint <外鍵名>] foreign key 欄位名1[,欄位名2,...] references<主表名> 主鍵列1[,主鍵列2,...]，外鍵名為定義的外鍵約束的名稱，一個表中不能有相同名稱的外鍵。如下以學生表、班級表、課程表、成績表為例說明。
   • 建立班級表：create table tb_class(id int primary key, name varchar(20));
   • 建立課程表：create table tb_course(id int primary key, name varchar(20));
   • 建立學生表：create table tb_student(id int primary key, name varchar(20), classID int, constraint fk_class foreign key(classID) references tb_class(id));
   • 建立成績表：create table tb_score(sID int, cID int, score float, primary key(sID,cID), constraint fk_student foreign key(sID) references tb_student(id), constraint fk_course foreign key(cID) references tb_course(id));
4. 使用非空約束：非空約束指欄位的值不為空白，對於指定非空約束的欄位，如果使用者在添加資料時沒有指定值，資料庫系統會報錯。約束規則為：欄位名 資料類型 not null，語句：create table user2(id int primary key, name varchar(20) not null);
5. 使用唯一性限制式：唯一性限制式要求該列唯一，允許為空白，但只能出現一個空值，唯一性限制式可以確保一列或者幾列不出現重複值。約束規則：欄位名 資料類型 unique，語句：create table user3(id int primary key, name varchar(22) unique);一個表中可以有多個欄位聲明為unique，但只能有一個為primary key，primary key的列不允許為空白，而unique可以存在一個null。
6. 使用預設約束：指定某列的預設值。預設約束的文法規則：欄位名 資料類型 default 預設值，預設約束語句：create table user4(id int primary key, name varchar(22), salary float default 3000.00);指定員工預設薪水為3000元。
7. 設定表的屬性值自動增加，通過為表的主鍵添加auto_increment關鍵字來實現。預設mysql中auto_increment的初始值為1，每增加一條新記錄，欄位值自動加1，一個表只能有一個欄位使用auto_increment約束，且該欄位必須為主鍵的一部分。auto_increment約束的欄位可以是任何整數類型（tinyint、smallint、int、bigint等）。文法規則：欄位名 資料類型 auto_increment。sql語句：create table user5(id int primary key auto_increment, name varchar(20), salary float);

查看資料表結構

 　　使用sql語句建立資料庫表之後，可以查看錶結構的定義，以確認表的定義是否正確，在mysql中可以使用describe和show create table語句，本節將對兩個語句分別進行介紹。

• 查看錶基本結構語句describe
  • describe 表名;或者desc 表名;

Null：表示該列是否可以儲存null值

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

Default:如果該欄位為null，表示該沒有預設值

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

• 查看錶詳細結構語句show create table
  • 文法格式：show create table 表名\G;

修改資料表

　　修改表是指修改資料庫中已經存在的資料表的結構。mysql使用alter table語句修改表。常用的修改表操作有：修改表名、修改欄位資料類型、或欄位名、增加或刪除欄位、修改欄位的排列位置、更改表的儲存引擎、刪除表的外鍵約束等。本節將對和修改表相關的操作進行說明。

• 修改表名

mysql通過alter table語句來實現表名的修改，具體文法規則：alter table <舊錶名> rename [to] <新表名>;其中to為選擇性參數。

• 修改欄位的資料類型

把欄位的資料類型轉換成另一種資料類型，mysql中的文法規則：alter table <表名> modify <欄位名> <資料類型>;

• 修改欄位名

mysql中修改欄位名的文法規則為：alter table <表名> change <舊欄位名><新欄位名><新資料類型>;其中舊欄位名指修改前的欄位名，新資料類型為修改後的資料類型，如果不需要修改欄位的資料類型，可以將新資料類型設定成原來一樣即可，但資料類型不可為空。同時需要注意該語句不能修改主鍵的欄位名。

• 添加欄位

　　隨著業務需求的變化，可能需要在已經存在的表中添加新的欄位，一個完整的欄位包括欄位名、資料類型、完整性條件約束，添加欄位的文法格式如下：

　　alter table <表名> add <新欄位名> <資料類型> [約束條件][first|after 以存在欄位名];

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

• 刪除欄位

刪除欄位是將資料表中的某個欄位從表中刪除，文法格式為：alter table <表名> drop <欄位名>;

• 修改欄位的排列位置

　　在資料表建立完成之後，欄位在表中的排列順序就已經確定了，但表的結構並不是完全不可變的，可以通過alter table來改變表中欄位的相對位置，文法格式如下：

alter table<表名>modify<欄位1><資料類型>first|after<欄位2>;

　　欄位1指要修改位置的欄位，資料類型指欄位1的資料類型，first為選擇性參數，指將欄位1修改為表的第一個欄位，after<欄位2>指將欄位1插入到欄位2的後面。

• 刪除表的外鍵約束

　　對於資料庫中定義的外鍵，如果不在需要，可以將其刪除。外鍵一旦刪除，就會解除主表和從表之間的關聯關係，外鍵刪除文法為：

alter table <表名> drop foreign key <外鍵約束名>;

　　外鍵約束名是在定義表時constraint關鍵字後面的參數，詳細見建立資料表章節。

• 添加表的外鍵約束

　　如上刪除了tb_score表對tb_course表的外部索引鍵關聯，那麼如下就是如何將外鍵約束添加回來，規則如下：

alter table <從表> add constraint fk_course foreign key (外鍵) references <主表>(關聯主鍵);

刪除資料表

 　　辛辛苦苦建立的資料表，僅僅一條刪除資料表的語句，就能夠將表的定義和表中所有的資料刪除，因此在進行刪除操作前，一定要謹慎考慮。

• 刪除沒有關聯的資料表

　　在mysql中，使用drop table可以一次刪除一個或多個沒有被其他表關聯的資料表，文法為：drop table [if exists]表1,表2,...表n;

•  刪除被其他表關聯的表

　　如果直接執行drop table <表名>或報錯：Cannot delete or update a parent now：a foreign key constraint fails。因此在刪除該表前，需要首先解除其他表與該表的關聯關係，如tb_course與tb_score關聯，然後就可以將tb_course表刪除。

總結

　　最後做一個小小的總結吧，①表刪除操作一定要謹慎，因為mysql在執行刪除操作時，不會有任何的確認資訊，而且刪除表操作是將表中的資料一併刪除，因此在刪除操作之前最好對錶中的資料做備份。②資料庫中並不是每一個表都存在主鍵，一般，多個表之間進行串連操作時，需要用到主鍵，因此並不需要為每個表建立主鍵，而且有些情況最好不要使用主鍵。③在mysql資料庫中，帶auto_increment約束的欄位值是從1開始的，如果在表中插入第一條記錄，同時指定auto_increment欄位值為8，那麼以後插入的該欄位值會從9開始往上增加。

資料庫(MySQL)表基本操作