mysql 表操作

MySQL中create table語句的基本文法是：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]  

[table_options] [select_statement]  

TEMPORARY：該關鍵字表示用MySQL create table建立的表為臨時表，此表在目前會話結束後将自動消失。臨時表主要被應用于存儲過程中，對于目前尚不支援存儲過程的MySQL，該關鍵字一般不用。

IF NOT EXISTS：實際上是在建表前加上一個判斷，隻有該表目前尚不存在時才執行create table操作。用此選項可以避免出現表已經存在無法再建立的錯誤。

tbl_name：你所要建立的表的表名。該表名必須符合辨別符規則。通常的做法是在表名中僅使用字母、數字及下劃線。例如titles、our_sales、my_user1等都應該算是比較規範的表名。

create_definition：這是MySQL create table語句中關鍵部分所在。在該部分具體定義了表中各列的屬性。

create_definition的基本語句是：

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]  

[PRIMARY KEY] [reference_definition]  

or PRIMARY KEY (index_col_name,...)  

or KEY [index_name] (index_col_name,...)  

or INDEX [index_name] (index_col_name,...)  

or UNIQUE [INDEX] [index_name] (index_col_name,...)  

or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)  

[reference_definition]  

or CHECK (expr) 

col_name：表中列的名字。必須符合辨別符規則，而且在表中要唯一。

type：列的資料類型。有的資料類型需要指明長度n，并用括号括起。目前MySQL提供的資料類型詳見MySQL進階_列類型篇。

NOT NULL | NULL：指定該列是否允許為空。如果既不指定NULL也不指定NOT NULL，列被認為指定了NULL。

DEFAULT default_value：為列指定預設值。如果沒有為列指定預設值，MySQL自動地配置設定一個。如果列可以取NULL作為值，預設值是NULL。如果列被聲明為NOT NULL，預設值取決于列類型：

1、對于沒有聲明AUTO_INCREMENT屬性的數字類型，預設值是0。對于一個AUTO_INCREMENT列，預設值是在順序中的下一個值。

2、對于除TIMESTAMP的日期和時間類型，預設值是該類型适當的“零”值。對于表中第一個TIMESTAMP列，預設值是目前的日期和時間。

3、對于除ENUM的字元串類型，預設是空字元串。對于ENUM，預設值是第一個枚舉值。

AUTO_INCREMENT：設定該列有自增屬性，隻有整型列才能設定此屬性。當你插入NULL值或0到一個AUTO_INCREMENT列中 時，列被設定為value+1，在這裡 value是此前表中該列的最大值。AUTO_INCREMENT順序從1開始。每個表隻能有一個AUTO_INCREMENT列，并且它必須被索引。

另轉：

通常，企圖建立一個已經存在的表時将産生錯誤。但在兩種情況下不會出錯。第一種情況，如果給出IF NOT EXISTS 子句，則不建立已經存在的這個表，但也不出錯。第二種情況，給出TEMPORARY，則原來名為tbl_name 的表在臨時表存在期間對客戶機是隐藏的，這樣也不會出錯。但原來的表對其他客戶機仍保持可見。在下一次客戶機會話或對臨時表明确釋出DROP TABLE 後，原來的表再次可見。

createdefinition清單指定希望建立的列和索引。如果通過後跟SELECT 語句的方法建立此表，此清單可省略。table_options 子句允許指定表的各種屬性。如果給出後面的select_statement（以任意SELECT語句形式），則利用該SELECT 語句傳回的結果建立該表。後面的段落中将對這些子句進行更詳細的介紹。

TEMPORARY關鍵字、IF NOT EXISTS 子句、table_options 子句以及能夠利用SELECT語句的結果建立表等都是在MySQL 3.23 中引入的。create_definition 可以是列定義、索引定義、FOREIGN KEY 子句、referencedefinition或CHECK子句。後三者在其資料庫系統中是相容的，但其餘的不行。

列定義coldeclaration以列名col_name 和類型col_type 開始，後面可跟幾個可選的關鍵字。列類型可以是附錄B 中列出的任意類型。請參閱該附錄中的類型專用屬性。可跟在列類型之後的可選關鍵字如下：

■ NULL 或NOT NULL 指出該列是否可以包含NULL 值。如果兩者都不指出，預設為N U L L。

■ DEFAULT defaultvalue 給出該列的預設值。這種屬性不能用于BLOB 或TEXT 類型。

如果無預設說明，自動配置設定一個預設值。對于可取NULL 值的列，預設值為N U L L。

對于不能為NULL 的列，預設值如下：

■ 對于數值列，除AUTO_INCREMENT 列外，預設值為0。而對于AUTO_INCREMENT 列，其預設值為該列的序列中的下一個數。

■ 對于非TIMESTAMP 的日期和時間類型，預設值為該類型的“零”值（例如，DATE 類型的“零”值為“0000-00-00”）。對于TIMESTAMP 類型，預設值為目前日期和時間。

■ 對于非ENUM 的串類型，預設值為空串。對于ENUM 類型，預設值為第一個枚舉元素。

■ A U TO_INCREMENT 此關鍵字僅用于整數列類型。A U TO_INCREMENT 列在插入NULL 時是很特殊的，這時插入的值實際為一個大于列中目前最大值的整數。AUTO_INCREMENT 值預設時從1 開始，第一個值可用AUTO_INCREMENT 表選項确明指定。

這樣的列還必須指定為UNIQUE 索引或PRIMARY KEY，而且應該為NOT NULL 列。每個表中至少有一個AUTO_INCREMENT 列。

■ PRIMARY KEY 指定該列為一個PRIMARY KEY。

■ UNIQUE 指定該列為UNIQUE 索引列。自MySQL 3.23 版以來都可指定此屬性。

PRIMARY KEY 、UNIQUE、INDEX 和KEY 子句定義索引。PRIMARY KEY 與UNIQUE 指定必須包含唯一值的索引。INDEX 和KEY 意義相同，它們都指定可以包含重複值的索引。索引基于index_columns中所指定的列，其中的每個列都必須是tblname 中的列。

如果有多個列，必須用逗号将它們分隔。對于CHAR 和VARCHAR 列，可對列的字首進行索引。如果未給出索引名index_name，系統會根據第一個索引列的名稱自動選一個。在MySQL 3.23 以前的版本中，索引列必須定義為NOT NULL。自MySQL 3.23 以來，隻有PRIMARY KEY 中的列必須定義為NOT NULL。

自MySQL 3.23 以來可使用table options 子句。如果給出此子句，它可以包含下面清單中的一個或多個選項。如果給出多個選項，不應用逗号分隔它們。除非另有說明，否則每個說明符都可用于所有表類型。

■ AUTO_INCREMENT =n 為表生成的第一個AUTO_INCREMENT 值（僅用于MyISAM 表）。

■ AVG_ROW_LENGTH = n 表的近似平均行長。MySQL 用此選項來确定資料檔案的最大尺寸，隻有具有BLOB 或TEXT 列的表需要此選項，具有這兩種列的表可能會增大超過4GB。

■ CHECKSUM = {0 | 1} 如果設定為1，MySQL 将對每個表行保持一個校驗和。這樣對于表的更新将會有某種輕微的損失，但改善了表的校驗過程（僅用于MyISAM 表）。

■ COMMENT = “string” 表的注釋。最大長度為60 個字元。

■ D E L AY_KEY_WRITE = {0 | 1} 如果設定為1，表的索引高速緩存隻是偶爾重新整理，而不是每個插入操作後都重新整理（隻用于MyISAM 表）。

■ MAX_ROWS = n 計劃存儲在表中的最多行數。

■ MIN_ROWS = n 計劃存儲在表中的最少行數。

■ PACK_KEYS = {0 | 1} 如果設定為1，索引塊以較平常高的百分比進行壓縮。其一般效果是更新性能受損，但檢索性能得以改善。如果設定為0，索引塊不進行特殊的壓縮。在此情形，隻 有具有8 個字元或更多字元的CHAR 或VARCHAR 鍵值進行壓縮（隻用于MyISAM 和ISAM 表）。

■ PASSWORD =“string” 為加密表的描述檔案指定一個密碼。一般不起作用；隻對具有一定支援協定的顧客可用。

■ TYPE = {ISAM | MYISAM | HEAP} 指定表的存儲格式。這些存儲格式的特性在第3章的“表存儲類型說明符”一節介紹。MySQL 自版本3.23 以來的預設格式為MyISAM。對MySQL 3.23 以前的版本，CREATE TABLE 總是以ISAM 格式建立表。

如果給出一個select statement子句（如一條SELECT 查詢），該表利用查詢結果的内容進行建立。惟一索引中重複值的行根據指定的是IGNORE 或REPLACE，或者忽略或者替換現有行。如果兩者都未指定，重複值時将出錯，其餘記錄忽略。

下面建立一個具有三列的表。id 列為PRIMARY KEY，其餘兩列組成一個索引：

create table name

(

id int not null auto_increment,

first_name varchar(30) not null,

last_name  varchar(30) not null,

primary key (id),

index (last_name,first_name)

)

下面建立一個臨時表并使其為一個H E A P（記憶體中）表以加取得更快的速度：

create temporary table tem_tbl

id mediumint not null unique,

name char(40)

type=heap

下面建立一個表作為另一表的拷貝：

create table prez_copy select * from tbl

下面隻用另一表的部分建立一個表：

create table prez_newcopy select id,first_name,last_name from user where id<10

如果指定所要建立的表的建立定義并利用字尾SELECT 語句進行填充，則此定義在表的内容已經插入表中時才使用。例如，可以定義将某個標明的列做成一個PRIMARY KEY：

create table new_tbl (primary key (a)) select id,first_name,last_name from user