MYSQL創建、刪除、索引和更改表

2024-07-24 12:55:35

字體：大中小

來源：轉載

供稿：網友

3.4 創建、刪除、索引和更改表
可利用create table、drop table 和alter table 語句創建表，然后，對它們進行刪除，更改它們的結構。對于它們中的每一條語句，存在mysql專有的擴充，這些擴充使各語句更為有用。create index 和drop index 語句使您能夠增加或刪除現有表上
的索引。
3.4.1create table 語句
用create table 語句創建表。此語句的完整語法是相當復雜的，因為存在那么多的可選子句，但在實際中此語句的應用相當簡單。如我們在第1章中使用的所有create table 語句都不那么復雜。有意思的是，大多數復雜東西都是一些子句，這些子句mysql在分析后扔掉。參閱附錄d 可看到這些復雜的東西。看看create table 語句的各項條款，注意該語句有多少語法是用于references constraint 和check 子句的。這些子句涉及外部鍵、引用完整性及輸入值約束。mysql不支持這些功能，但它分析其語法使其更容易利用在其他數據庫系統中建立的表定義。（可以用較少的編輯工作更容易地利用該代碼。）如果您從頭開始編寫自己的表描述，可以完全不管這些子句。本節中我們對它們也不多做介紹。create table 至少應該指出表名和表中列的清單。例如：

除構成表的列以外，在創建表時還可以說明它應該怎樣索引。另一個選擇是創建表時不進行索引，以后再增加索引。如果計劃在開始將表用于查詢前，用大量的數據填充此表，以后再創建索引是一個好辦法。在插入每一行時更新索引較裝載數據到一個未索引的表中然后
再創建索引要慢得多。我們已經在第1章中介紹了create table 語句的基本語法，并在第2章討論了怎樣描述列類型。這里假定您已經讀過了這兩章，因此我們就不重復這些內容了。在本節下面，我們將介紹一些mysql3.23 中對create table 語句的重要擴充，這些擴充在構造表方面提供了很大的靈活性，這些擴充為：
■ 表存儲類型說明符。
■ 僅當表不存在時才進行創建。
■ 在客戶機會話結束時自動刪除臨時表。
■ 通過選擇希望表存儲的數據來創建一個表。
1. 表存儲類型說明符
在mysql3.23 之前，所有用戶創建的表都利用的是isam 存儲方法。在mysql3.23中，可在create table 語句的列的列表之后指定type = type，以三種類型明確地創建表。其中type 可以為myisam、isam 或heap。例如：

將表轉換為heap 類型可能不是一個好主意，但是，如果希望表一直維持到服務器關閉，可以進行這個轉換。heap 表在服務器退出之前，一直保留在內存中。這三種表類型的一般特點如下：
■ myisam 表。myisam 存儲格式自版本3.23 以來是mysql中的缺省類型，它有下列特點：
■ 如果操作系統自身允許更大的文件，那么文件比isam 存儲方法的大。
■ 數據以低字節優先的機器獨立格式存儲。這表示可將表從一種機器拷貝到另一種機器，即使它們的體系結構不同也可以拷貝。
■ 數值索引值占的存儲空間較少，因為它們是按高字節優先存儲的。索引值在低位字節中變化很快，因此高位字節更容易比較。
■ auto_increment 處理比isam 的表更好。詳細內容在第2章討論。
■ 減少了幾個索引限制。例如，可對含null 值的列進行索引，還可以對blob 和text 類型的列進行索引。
■ 為了改善表的完整性檢查，每個表都具有一個標志，在myisamchk 對表進行過檢查后，設置該標志?？衫胢yisamchk - fast 跳過對自前次檢查以來尚未被修改過表的檢查，這樣使此管理任務更快。表中還有一個指示表是否正常關閉的標志。如果服務器關閉不正常，或機器崩潰，此標志可用來檢測出服務器起動時需要檢查的表。
■ isam 表。isam 存儲格式是mysql3.23 所用的最舊的格式，但當前仍然可用。通常，相對于isam 表來說，寧可使用myisam 表，因為它們的限制較少。對isam 表的支持隨著此存儲格式被myisam 表格式所支持很有可能會逐漸消失。
■ heap 表。heap 存儲格式建立利用定長行的內存中的表，這使表運行得非?？?。在服務器停止時，它們將會消失。在這種意義上，這些表是臨時的。但是，與用create temporary table 所創建的臨時表相比，heap 表是其他客戶機可見的。heap 表有幾個限制，這些限制對myisam 或isam 表沒有，如下所示：
■ 索引僅用于“=”和“< = >”比較。
■ 索引列中不能有null 值。
■ 不能使用blob 和text 列。
■ 不能使用auto_increment 列。
2. 創建不存在的表
要創建一個不存在的表，使用create table if not exists 即可。在某種應用程序中，無法確定要用的表是否已經存在，因此，要創建這種表。if not exists 修飾符對于作為用mysql運行的批量作業的腳本極為有用。在這里，普通的create table 語句工作得
不是很好。因為作業第一次運行時，建立這些表，如果這些表已經存在，則第二次運行時將出錯。如果用if not exists語句，就不會有問題。每一次運行作業時，像前面一樣創建表。如果這些表已經存在，在第二次運行時，創建表失敗，但不出錯。這使得作業可以繼續運行，就像創建表的企圖已經成功了一樣。
3. 臨時表
可用create temporary table 來創建臨時表，這些表在會話結束時會自動消失。使用臨時表很方便，因為不必費心發布drop table 語句明確地刪除這些表，而且如果您的會話不正常結束，這些表不會滯留。例如，如果某個文件中有一個用mysql運行的查詢，您決定不等到其結束，那么可以在其執行的中途停止這個查詢，而且毫無問題，服務器將刪除所創建的任意臨時表。在舊版的mysql中，沒有真正的臨時表，除了您在自己的頭腦中認為它們是臨時的除外。對于需要這樣的表的應用程序，必須自己記住刪除這些表。如果忘了刪除，或在前面使其存在的客戶機中出現錯誤時，這些表在有人注意到并刪除它們以前會一直存在。臨時表僅對創建該表的客戶機可見。其名稱可與一個現有的永久表相同。這不是錯誤，也不會使已有的永久表出問題。假如在samp_db 數據庫中創建了一個名為member 的臨時表。原來的member 表變成隱藏的（不可訪問），對member 的引用將引用臨時表。如果發布一條drop table member 語句，這個臨時表將被刪除，而原來的member 表“重新出現”。如果您簡單地中斷與服務器的連接而沒有刪除臨時表，服務器會自動地刪除它。下一次連接時，
原來的member 表再次可見。名稱隱藏機制僅在一個級別上起作用。即，不能創建兩個具有同一個名稱的臨時表。
4. 利用select 的結果創建表
關系數據庫的一個重要概念是，任何數據都表示為行和列組成的表，而每條select 語句的結果也都是一個行和列組成的表。在許多情況下，來自select 的“表”僅是一個隨著您的工作在顯示屏上滾動的行和列的圖像。在mysql3.23 以前，如果想將select 的結果保存在一個表中以便以后的查詢使用，必須進行特殊的安排：
1) 運行describe 或show columns 查詢以確定想從中獲取信息的表中的列類型。
2) 創建一個表，明確地指定剛才查看到的列的名稱和類型。
3) 在創建了該表后，發布一條insert ... select 查詢，檢索出結果并將它們插入所創建的表中。
在mysql3.23 中，全都作了改動。create table ... select 語句消除了這些浪費時間的東西，使得能利用select 查詢的結果直接得出一個新表。只需一步就可以完成任務，不必知道或指定所檢索的列的數據類型。這使得很容易創建一個完全用所喜歡的數據填充的表，并且為進一步查詢作了準備?？梢酝ㄟ^選擇一個表的全部內容（無where 子句）來拷貝一個表，或利用一個總是失
敗的where 子句來創建一個空表，如：

如果希望利用load data 將一個數據文件裝入原來的文件中，而不敢肯定是否具有指定的正確數據格式時，創建空拷貝很有用。您并不希望在第一次未得到正確的選項時以原來表中畸形的記錄而告終。利用原表的空拷貝允許對特定的列和行分隔符用load data 的選項進行試驗，直到對輸入數據的解釋滿意時為止。在滿意之后，就可以將數據裝入原表了?？山Y合使用create temporary table 與select 來創建一個臨時表作為它自身的拷貝，如：

這允許修改my_tbl 的內容而不影響原來的內容。在希望試驗對某些修改表內容的查詢，而又不想更改原表內容時，這樣做很有用。為了使用利用原表名的預先編寫的腳本，不需要為引用不同的表而編輯這些腳本；只需在腳本的起始處增加create temporary table語句即可。相應的腳本將創建一個臨時拷貝，并對此拷貝進行操作，當腳本結束時服務器會自動刪除這個拷貝。
要創建一個作為自身的空拷貝的表，可以與create temporary ... select 一起使用where 0 子句，例如：

但創建空表時有幾點要注意。在創建一個通過選擇數據填充的表時，其列名來自所選擇的列名。如果某個列作為表達式的結果計算，則該列的“名稱”為表達式的文本。表達式不是合法的列名，可在mysql中運行下列查詢了解這一點：

如果選擇了來自不同表的具有相同名稱的列，將會出現一定的困難。假定表t1和t2 兩者都具有列c，而您希望創建一個來自兩個表中行的所有組合的表。那么可以提供別名指定新表中惟一性的列名，如：

通過選擇數據進行填充來創建一個表并會自動拷貝原表的索引。
3.4.2 drop table 語句
刪除表比創建表要容易得多，因為不需要指定有關其內容的任何東西；只需指定其名稱即可，如：
drop table tb1_name
mysql對drop table 語句在某些有用的方面做了擴充。首先，可在同一語句中指定幾個表對它們進行刪除，如：
drop table tb1_name1,tb1_name2,......
其次，如果不能肯定一個表是否存在，但希望如果它存在就刪除它。那么可在此語句中增加if exists。這樣，如果drop table 語句中給出的表不存在，mysql不會發出錯誤信息。如：
drop table if exists tb1_name
if exists 在mysql所用的腳本中很有用，因為缺省情況下， mysql將在出錯時退出。例如，有一個安裝腳本能夠創建表，這些表將在其他腳本中繼續使用。在此情形下，希望保證此創建表的腳本在開始運行時無后顧之憂。如果在該腳本開始處使用普通的drop table，那么它在第一次運行時將會失敗，因為這些表從未創建過。如果使用if exists，就不會產生問題了。當表已經存在時，將它們刪除；如果不存在，腳本繼續運行。
3.4.3 創建和刪除索引
索引是加速表內容訪問的主要手段，特別對涉及多個表的連接的查詢更是如此。這是第4章“查詢優化”中的一個重要內容，第4章討論了為什么需要索引，索引如何工作以及怎樣利用它們來優化查詢。本節中，我們將介紹索引的特點，以及創建和刪除索引的語法。
1. 索引的特點
mysql對構造索引提供了很大的靈活性?？蓪瘟谢蚨嗔械慕M合進行索引。如果希望能夠從一個表的不同列中找出一個值，還可以在一個表上構造不止一個索引。如果某列為串類型而非enum 或set 類型，可以選擇只對該列最左邊的n 個字符進行索引。如果該列的前n個字符最具有唯一性，這樣做一般不會犧牲性能，而且還會對性能有大的改善：用索引列的前綴而非整個列可使索引更小且訪問更快。雖然隨著mysql的進一步開發創建索引的約束將會越來越少，但現在還是存在一些約束的。下面的表根據索引的特性，給出了isam 表和myisam 表之間的差別：

從此表中可以看到，對于isam 表來說，其索引列必須定義為not null，并且不能對blob 和text 列進行索引。myisam 表類型去掉了這些限制，而且減緩了其他的一些限制。兩種表類型的索引特性的差異表明，根據所使用的mysql版本的不同，有可能對某些列不能進行索引。例如，如果使用3.23 版以前的版本，則不能對包含null 值的列進行索引。
如果使用的是mysql3.23版或更新的版本，但表是過去以isam 表創建的，可利用alter table 很方便地將它們轉換為myisam 存儲格式，這樣使您能利用某些較新的索引功能，如：
alter table tb1_name type=myisam
2. 創建索引
在執行create table 語句時，可為新表創建索引，也可以用create index 或alter table 來為一個已有的表增加索引。create index 是在mysql3.23版中引入的，但如果使用3.23 版以前的版本，可利用alter table 語句創建索引（mysql通常在內部將cre ate index 映射到alter table）?？梢砸幎ㄋ饕芊癜貜偷闹?。如果不包含，則索引應該創建為primary key 或unique 索引。對于單列惟一索引，這保證了列不包含重復的值。對于多列惟一索引，它保證值的組合不重復。
primary key 索引和unique 索引非常類似。事實上， primary key 索引僅是一個具有名稱primary 的unique 索引。這表示一個表只能包含一個primary key，因為一個表中不可能具有兩個同名的索引。同一個表中可有多個unique 索引，雖然這樣做意義不
大。
為了給現有的表增加一個索引，可使用alter table 或create index 語句。alter table 最常用，因為可用它來創建普通索引、unique 索引或primary key 索引，如：

其中tbl_name 是要增加索引的表名，而column_list 指出對哪些列進行索引。如果索引由不止一列組成，各列名之間用逗號分隔。索引名index_name 是可選的，因此可以不寫它，mysql將根據第一個索引列賦給它一個名稱。alter table 允許在單個語句中指定多個表的更改，因此可以在同時創建多個索引。
create index 可對表增加普通索引或unique 索引，如：

tbl _ name、index_name 和column_list 具有與alter table 語句中相同的含義。這里索引名不可選。不能用create index 語句創建primary key 索引。要想在發布create table 語句時為新表創建索引，所使用的語法類似于alter table 語句的語法，但是應該在您定義表列的語句部分指定索引創建子句，如下所示：

與alter table 一樣，索引名對于index 和unique 都是可選的，如果未給出，mysql將為其選一個。有一種特殊情形：可在列定義之后增加primary key 創建一個單列的primary key索引，如下所示：

前面所有表創建樣例都對索引列指定了not null。如果是isam 表，這是必須的，因為不能對可能包含null 值的列進行索引。如果是myisam 表，索引列可以為null，只要該索引不是primary key 索引即可。
如果對某個串列的前綴進行索引（列值的最左邊n 個字符），應用column_list 說明符表示該列的語法為col_name(n) 而不用c o l _ name。例如，下面第一條語句創建了一個具有兩個char 列的表和一個由這兩列組成的索引。第二條語句類似，但只對每個列的前綴進行索引：

在某些情況下，可能會發現必須對列的前綴進行索引。例如，索引行的長度有一個最大上限，因此，如果索引列的長度超過了這個上限，那么就可能需要利用前綴進行索引。在myisam 表索引中，對blob 或text 列也需要前綴索引。對一個列的前綴進行索引限制了以后對該列的更改；不能在不刪除該索引并使用較短前綴的情況下，將該列縮短為一個長度小于索引所用前綴的長度的列。
3. 刪除索引
可利用drop index 或alter table 語句來刪除索引。類似于create index 語句，drop index 通常在內部作為一條alter table 語句處理，并且drop index 是在mysql3.22 中引入的。刪除索引語句的語法如下：

前兩條語句是等價的。第三條語句只在刪除primary key 索引時使用；在此情形中，不需要索引名，因為一個表只可能具有一個這樣的索引。如果沒有明確地創建作為primary key 的索引，但該表具有一個或多個unique 索引，則mysql將刪除這些unique 索引中的第一個。
如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。
3.4.4 alter table 語句
alter table 語句是mysql中一條通用的語句，可用它來做許多事情。我們已經看過了它的幾種功能（創建和刪除索引以及將表從一種存儲格式轉換為另一種存儲格式）。本節中，我們將介紹它的一些其他功能。alter table 的完整語法在附錄d 中介紹。
在發現某個表的結構不再反映所希望的東西時， alter table很有用處?？赡芟Ｍ迷摫碛涗浧渌畔?，或者它含有多余的值。或者有的列太小，或者其定義較實際需要來說太大，需要將它們改小以節省存儲空間?；蛘甙l布create table 語句時給出的表名不對。等等，諸如此類的問題，都可以用alter table 語句來解決。下面是一些例子：
■ 您正操縱一個基于web 的問卷，將每份提交的問卷作為表中的一個記錄。后來決定修改此問卷，增加一些問題。這時必須對表增加一些列以存放新問題。
■ 您正在管理一個研究項目。用auto_increment 列分配案例號來研究記錄。您不希望經費延期太長產生多于50 000 個以上的記錄，因此，令該列的類型為unsigned smallint，它能存儲的最大惟一值為65 535。但是，項目的經費延長了，似乎可能另外產生50 000 個記錄。這時，需要使該列的類型更大一些以便存儲更多的件號。
■ 大小的更改也可能是反方向的?？赡軇摻艘粋€char(255) 列，但現在發現表中沒有比100 個字符更長的串。這時可縮短該列以節省存儲空間。alter table 的語法如下：

每個action 表示對表所做的一個修改。mysql擴充了alter table 語句，允許指定多個動作，各動作間以逗號分隔。這對于減少鍵盤輸入很有用，但這個擴充的更為重要的原因是，除非能同時將所有varchar 列更改為char 列，否則不可能將表從行可變長的表更改為行定長的表。
下面的例子示出了某些alter table 的功能。
■ 對表重新命名。這很簡單；只需給出舊表名和新表名即可：

在mysql3.23 中有臨時表，重命名一個臨時表為數據庫中已經存在的名稱將隱藏原始表，只要臨時表存在就會隱藏原始表。這類似于通過用相同的名字創建一個臨時表來隱藏一個表的方法。
■ 更改列類型。為了更改列的類型，可使用change 或modify 子句。假如表my _ t b l中的列為smallint unsigned 的，希望將其更改為mediumint unsigned 的列。用下面的任何一個命令都可完成此項工作：

為什么在change 命令中給出列名兩次？因為change 可以做的而modify 不能做的一樁事是，除了更改類型外還能更改列名。如果希望在更改類型的同時重新將i 命名為j，可按如下進行：

重要的是命名了希望更改的列，并說明了一個包括列名的列的完整定義。即使不更改列名，也需要在定義中包括相應的列名。
更改列類型的一個重要原因是為了改善比較兩個表的連接查詢的效率。在兩個列的類型相同時，比較更快。假如執行如下的查詢：

如果t1.name 為char( 10 )，而t2.name 為char( 15 )，此查詢的運行速度沒有它們兩者都為char(15) 時的快。那么可以用下面的任一條命令更改t1.name 使它們的類型相同：

對于3 . 2 3以前的mysql版本，所連接的列必須是同樣類型的這一點很重要，否則索引不能用于比較。對于版本3.23 或以上的版本，索引可用于不同的類型，但如果類型相同，查詢仍然更快。
■ 將表從可變長行轉換為定長行。假如有一個表chartbl 具有varchar 列，想要把它轉換為char 列，看看能夠得到什么樣的性能改善。（定長行的表一般比變長行的表處理更快。）這個表如下創建：

這里的問題是需要在相同的alter table 語句中一次更改所有的列。不可能一次一列地改完，或者說這個企圖將不起作用。如果執行describe chartbl，會發現兩個列仍然是varchar 的列！原因是如果每次更改一列， mysql注意到表仍然包含有可變長的列，則會把已經更改過的列重新轉換為varchar 以節省空間。為了處理這個問題，應該同時更改所有varchar 列：

現在describe 將顯示該表包含的都是char 列。確實，這種類型的操作很重要，因為它使alter table 能在相同的語句中支持多個動作。
這里要注意，在希望轉換這樣的表時：如果表中存在blob 或text 列將使轉換表為定長行格式的企圖失敗。即使表中只有一個可變長的列都將會使表有可變長的行，因為這些可變長的列類型沒有定長的等價物。
■ 將表從定長行轉換為可變長的行。雖然， chartbl 用定長行更快，但它要占用更多的空間，因此決定將它轉換回原來的形式以節省空間。這種轉換更為容易。只需將某個char 列轉換為varchar 列，mysql就自動地轉換其他的char 列。要想轉換chartbl 表，用下列任一條語句都可以：

■ 轉換表的類型。如果從mysql3.23 版以前的版本升級到3.23 版或更高，那么可能會有一些原來創建為isam 表的舊表。如果希望使它們為myisam 格式，如下操作：

為什么要這樣做呢？正如在“創建和刪除索引”小節中所介紹的那樣，一個原因是myisam 存儲格式具有某些isam 格式沒有的索引特性，例如能夠對null 值、blob 和text 列類型進行索引。另一個原因為， myisam 表是獨立于機器的，因此可通過將它們直接拷貝來將它們移到其他機器上，即使那些機器具有不同的硬件體系結構也同樣。這在第11章中將要作進一步的介紹。

不行，這不是我想要找的，我要再搜一搜

上一篇：MySQL的命名規則

下一篇：獲取MYSQL數據庫和表的有關信息