第6章 表

1、第六章,表,課程目標(biāo),創(chuàng)建表修改表重命名表刪除表,1. CREATE TABLE創(chuàng)建新表,使用CREATE TABLE可以創(chuàng)建一個新表，這一語句可以分為兩種寫法:一種用于直接創(chuàng)建新表;另一種用于參照其它表的定義創(chuàng)建一個新表。,1. CREATE TABLE創(chuàng)建新表,以下寫法用于直接創(chuàng)建新表：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名[(列定義1，列定義2，...，[

2、表級約束])][表選項][select表達(dá)式]其中列定義的基本寫法為：列名 列的類型,1. CREATE TABLE創(chuàng)建新表,以下代碼用于創(chuàng)建一個名為“student”的表，該表有兩列：ID：INT類型，用于存儲學(xué)生的編號；NAME：CHAR（8）類型，用于存儲學(xué)生的姓名。表的創(chuàng)建代碼如下：mysql> create table student -> ( -> ID int, -

3、> NAME char(8) -> );Query OK, 0 rows affected (0.09 sec),1. CREATE TABLE創(chuàng)建新表,MySQL將每一個表都單獨(dú)保存在一個“.frm”格式的文件中，該文件存儲在相應(yīng)的數(shù)據(jù)庫目錄中。表的存儲引擎也可能會創(chuàng)建其它文件，如對于MyISAM類型的表，存儲引擎會自動創(chuàng)建數(shù)據(jù)和索引文件。,1. CREATE TABLE創(chuàng)建新表,創(chuàng)建臨時表在創(chuàng)建表格時，可

4、以使用TEMPORARY關(guān)鍵詞使當(dāng)前建立的表為臨時表。臨時表只能在當(dāng)前連接中使用，當(dāng)連接關(guān)閉時，臨時表也被自動刪除。這意味著兩個不同的連接可以創(chuàng)建兩個名稱相同的臨時表，這兩個臨時表不會互相沖突，也不與原有的同名的非臨時表沖突。,1. CREATE TABLE創(chuàng)建新表,創(chuàng)建臨時表如果臨時表的名稱與數(shù)據(jù)庫中現(xiàn)有的非臨時表名稱相同，則非臨時表會被隱藏，直到該臨時表被刪除為止。創(chuàng)建臨時表要求當(dāng)前帳戶擁有CREATE TEMPORARY T

5、ABLES權(quán)限。,1. CREATE TABLE創(chuàng)建新表,避免創(chuàng)建已經(jīng)存在的表如果表已存在，則使用關(guān)鍵詞IF NOT EXISTS可以防止發(fā)生錯誤。注意，原有表的結(jié)構(gòu)與CREATE TABLE語句中表示的表的結(jié)構(gòu)是否相同，這一點(diǎn)沒有驗證。注釋：如果在CREATE TABLE...SELECT語句中使用IF NOT EXISTS，則不論表是否已存在，由SELECT部分選擇的記錄都會被插入。,1. CREATE TABLE創(chuàng)建新表,是

6、否允許列為空值在列定義中，可以加入以下選項以指明某一列是否允許出現(xiàn)空值：NULL：允許出現(xiàn)空值；NOT NULL：不允許出現(xiàn)空值。,1. CREATE TABLE創(chuàng)建新表,以下代碼在創(chuàng)建“student”表時，不允許學(xué)生的編號（ID）和姓名（NAME）為空，但允許住址（ADDR）為空。mysql> create table student -> ( -> ID int NOT NULL,

7、 -> NAME char(8) NOT NULL, -> ADDR char(60) NULL -> );Query OK, 0 rows affected (0.09 sec),1. CREATE TABLE創(chuàng)建新表,指定列的默認(rèn)值在列定義中，可以加入以下選項以指明某一列的默認(rèn)值：DEFAULT 默認(rèn)值,1. CREATE TABLE創(chuàng)建新表,默認(rèn)值必須為常數(shù)，不能是函數(shù)或表達(dá)式，例如，一

8、個日期列的默認(rèn)值不能被設(shè)置為一個函數(shù)，如NOW()或CURRENT_DATE。這里有一個特例，可以對TIMESTAMP列指定CURRENT_TIMESTAMP為默認(rèn)值。BLOB和TEXT類型的列不能指定默認(rèn)值。,1. CREATE TABLE創(chuàng)建新表,使列值自動增加如果一個列是整數(shù)類型，則可以使用“AUTO_INCREMENT”關(guān)鍵字指定該列的值自動增加。使用自動增加可以很方便的在表中的某一列上，為每一條記錄建立不同的列值，從而根

9、據(jù)該列的值可以唯一的確定表中的一條記錄。,1. CREATE TABLE創(chuàng)建新表,當(dāng)指定某一列為自動增加時，向該列插入一個NULL值（建議）或0時，該列會被自動設(shè)置為比上一次插入時更大的值，通常情況下依次增加1。也就是說，新增加的列值總是當(dāng)前表中該列的最大值。如果新增加的記錄是表中的第一條記錄，則該值為1。,1. CREATE TABLE創(chuàng)建新表,指定列的字符集如果某一列是字符列，則其定義可以包括一個CHARACTER SET屬性

10、，用來指定字符集和校對規(guī)則。可以將CHARACTER SET縮寫為CHARSET。,1. CREATE TABLE創(chuàng)建新表,為列添加注釋為了記錄表中某些列的作用，可以在建表時將這些列的用法添加列的注釋中，添加注釋使用COMMENT關(guān)鍵字。可以通過SHOW CREATE TABLE或SHOW FULL COLUMNS語句來查看列的注釋。,1. CREATE TABLE創(chuàng)建新表,設(shè)置主鍵主鍵就好比是表中每一條記錄的身份，一張表中不能

11、出現(xiàn)兩條主鍵相同的記錄，換言之，只要主鍵的值確定了，就唯一的確定了表中的一條記錄。被設(shè)置為主鍵的列同時會被強(qiáng)制設(shè)置為NOT NULL。一張表只能設(shè)置一個主鍵，主鍵可以是一個列，也可以是多個列的組合。,1. CREATE TABLE創(chuàng)建新表,設(shè)置主鍵將一個列設(shè)置為主鍵的方法是，在列的定義中使用“[PRIMARY] KEY”子句。以下代碼將“student”表的ID列設(shè)置為主鍵。Create table student(ID in

12、t KEY,NAME char(8)),1. CREATE TABLE創(chuàng)建新表,要將主鍵設(shè)置為多個列的組合，必須使用列級約束PRIMARY KEY子句。以下代碼演示了將表grade（成績）的列SID（學(xué)號），CID（課程號）設(shè)置為主鍵。create table grade(sid int,cid int,grade int,primary key (sid,cid)),1. CREATE TABLE創(chuàng)建新表,使列值

13、不重復(fù)在表中輸入數(shù)據(jù)時，有時需要某一列的內(nèi)容不含重復(fù)內(nèi)容，如書藉的出版號，此時可以使用UNIQUE [KEY]關(guān)鍵字來防止列值重復(fù)。如果在添加新行時插入的列值在表中已存在，則會出現(xiàn)錯誤。例外情況是，如果索引中的一個列允許包含NULL值，NULL值可以重復(fù)出現(xiàn)。,1. CREATE TABLE創(chuàng)建新表,使用索引可以使用如下方法在表中指定索引：INDEX 索引名 索引類型 （索引列1，索引列2，…）索引用于加速表的查詢速度，有

14、關(guān)索引的詳細(xì)信息可參考索引的管理。,1. CREATE TABLE創(chuàng)建新表,SERIAL實際使用中，經(jīng)常需要將一個列設(shè)置為“BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE”屬性，為便于進(jìn)行這一操作，可以使用關(guān)鍵字“SERIAL”來替代這段很長的代碼。,1. CREATE TABLE創(chuàng)建新表,外鍵約束外鍵約束是指當(dāng)前表中的某些列與其它參考表中的某些列存在著參照關(guān)系，在當(dāng)前表中的列的值必

15、須已經(jīng)出現(xiàn)在參考表的對應(yīng)列中。在列定義和表級約束中都可以指定表的外鍵約束。,1. CREATE TABLE創(chuàng)建新表,外鍵定義服從下列情況： 所有tables必須是InnoDB型，它們不能是臨時表。 在引用表中，必須有一個索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個索引如果不存在，它必須在引用表里被自動創(chuàng)建。 在引用表中，必須有一個索引，被引用的列以同樣的順序被列在其中作為第一列。 不支持對外鍵列的索引前綴。這樣的后果

16、之一是BLOB和TEXT列不被包括在一個外鍵中，這是因為對這些列的索引必須總是包含一個前綴長度。 如果CONSTRAINT symbol被給出，它在數(shù)據(jù)庫里必須是唯一的。如果它沒有被給出，InnoDB自動創(chuàng)建這個名字。,1. CREATE TABLE創(chuàng)建新表,表選項表選項用于對表進(jìn)行優(yōu)化，以下介紹表選項的含義。ENGINE和TYPEENGINE和TYPE選項用于為表指定存儲引擎，ENGINE是首選的選項名稱。ENGINE和TY

17、PE選項可參考MySQL備份與恢復(fù)中相關(guān)內(nèi)容。,1. CREATE TABLE創(chuàng)建新表,表選項如果被指定的存儲引擎無法利用，則MySQL使用MyISAM代替。例如，一個表定義包括ENGINE=BDB選項，但是MySQL服務(wù)器不支持BDB表，則表被創(chuàng)建為MyISAM表。這樣，如果在主機(jī)上有事務(wù)表，但在從屬機(jī)上創(chuàng)建的是非交互式表（以加快速度）時，可以進(jìn)行復(fù)制設(shè)置。在MySQL 5.1中，如果沒有遵守存儲引擎規(guī)約，則會出現(xiàn)警告。,1.

18、CREATE TABLE創(chuàng)建新表,AUTO_INCREMENT 表的初始AUTO_INCREMENT值。在MySQL 5.1中，本選項只適用于MyISAM和MEMORY表。InnoDB也支持本選項。如果引擎不支持AUTO_INCREMENT表選項，則要設(shè)置引擎的第一個auto-increment值，需插入一個“假”行。該行的值比創(chuàng)建表后的值小一，然后刪除該假行。,1. CREATE TABLE創(chuàng)建新表,AVG_ROW_LENGTH

19、表中平均行長度的近似值。只需要對含尺寸可變的記錄的大型表進(jìn)行此項設(shè)置。[DEFAULT] CHARACTER SET 用于為表指定一個默認(rèn)字符集。CHARSET是CHARACTER SET的同義詞。COLLATE 用于為表指定一個默認(rèn)整序。,1. CREATE TABLE創(chuàng)建新表,CHECKSUM如果希望MySQL隨時對所有行進(jìn)行實時檢驗求和（也就是，表變更后，MySQL自動更新檢驗求和），則應(yīng)把此項設(shè)置為1。這樣做，表的

20、更新速度會略微慢些，但是更容易尋找到受損的表。CHECKSUM TABLE語句用于報告檢驗求和（僅限于MyISAM）。COMMENT 表的注釋，最長60個字符。,1. CREATE TABLE創(chuàng)建新表,CONNECTION FEDERATED表的連接字符串。（ 注釋：較早版本的MySQL使用COMMENT選項用于連接字符串。MAX_ROWS 打算儲存在表中的行數(shù)目的最大值。這不是一個硬性限值，而更像一個指示語句，指示出表必須

21、能存儲至少這么多行。MIN_ROWS 打算存儲在表中的行數(shù)目的最小值。,1. CREATE TABLE創(chuàng)建新表,PACK_KEYS 如果希望索引更小，則把此選項設(shè)置為1。這樣做通常使更新速度變慢，同時閱讀速度加快。把選項設(shè)置為0可以取消所有的關(guān)鍵字壓縮。把此選項設(shè)置為DEFAULT時，存儲引擎只壓縮長的CHAR或VARCHAR列（僅限于MyISAM）。如果不使用PACK_KEYS，則默認(rèn)操作是只壓縮字符串，但不壓縮數(shù)字。如果使

22、用PACK_KEYS=1，則對數(shù)字也進(jìn)行壓縮。,1. CREATE TABLE創(chuàng)建新表,PASSWORD 使用密碼對.frm文件加密。在標(biāo)準(zhǔn)MySQL版本中，本選項不起任何作用。DELAY_KEY_WRITE 如果想要延遲對關(guān)鍵字的更新，等到表關(guān)閉后再更新，則把此項設(shè)置為1（僅限于MyISAM）。,1. CREATE TABLE創(chuàng)建新表,ROW_FORMAT 定義各行應(yīng)如何儲存。當(dāng)前，此選項只適用于MyISAM表。對于靜態(tài)行

23、或長度可變行，此選項值可以為FIXED或DYNAMIC。myisampack用于把類型設(shè)置為COMPRESSED。在默認(rèn)情況下，InnoDB記錄以壓縮格式存儲（ROW_FORMAT=COMPACT）。通過指定ROW_FORMAT=REDUNDANT，仍然可以申請用于較早版本的MySQL中的非壓縮格式。,1. CREATE TABLE創(chuàng)建新表,RAID_TYPE 在MySQL 5.0中,RAID支持被刪除了。 UNION 當(dāng)想要

24、把一組相同的表當(dāng)作一個表使用時，采用UNION。UNION僅適用于MERGE表。對于映射到一個MERGE表上的表，必須擁有SELECT, UPDATE和DELETE權(quán)限。,1. CREATE TABLE創(chuàng)建新表,INSERT_METHOD 如果希望在MERGE表中插入數(shù)據(jù)，必須用INSERT_METHOD指定應(yīng)插入行的表。INSERT_METHOD選項僅用于MERGE表。使用FIRST或LAST把行插入到第一個或最后一個表中；或者

25、使用NO，阻止插入行。DATA DIRECTORY, INDEX DIRECTORY 通過使用DATA DIRECTORY='directory'或INDEX DIRECTORY='directory'，可以指定MyISAM存儲引擎放置表格數(shù)據(jù)文件和索引文件的位置。注意，目錄應(yīng)是通向目錄的完整路徑（不是相對路徑）。,1. CREATE TABLE創(chuàng)建新表,在一個表的基礎(chǔ)上創(chuàng)建表可以在CREATE

26、TABLE語句的末尾添加一個SELECT語句，在一個表的基礎(chǔ)上創(chuàng)建表。CREATE TABLE 新表名SELECT * FROM 老表名;MySQL會對SELECT中的所有項創(chuàng)建新列。,1. CREATE TABLE創(chuàng)建新表,參照其它表的定義創(chuàng)建一個新表以下寫法用于參照其它表的定義創(chuàng)建一個新表：CREATE [TEMPORARY] TABLE[IF NOT EXISTS] 表名LIKE 參照表的表名,2. ALTER T

27、ABLE修改表,ALTER [IGNORE] TABLE 表名修改項1 [, 修改項2] ... 其中，修改項寫法如下：ADD [COLUMN] 列定義 [FIRST | AFTER 列名] | ADD [COLUMN] (列定義1,列定義2) | ADD INDEX [索引名] [索引類型] (索引列名,...) | ADD [CONSTRAINT [約束名]] PRIMARY KEY [索引類型]

28、(索引列名,...) | ADD [CONSTRAINT [約束名]] UNIQUE [索引名] [索引類型] (索引列名,...),2. ALTER TABLE修改表,| ADD [FULLTEXT|SPATIAL] [索引名] (索引列名,...) | ADD [CONSTRAINT [約束名]] FOREIGN KEY [索引名] (索引列名,...) [外鍵約束定義]

29、 | ALTER [COLUMN] 列名 {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] 舊列名 列定義 [FIRST|AFTER 列名] | MODIFY [COLUMN] 列定義 [FIRST | AFTER 列名] | DROP [COLUMN] 列名 | DROP PRIMARY KEY | DROP INDEX 索引名,2.

30、ALTER TABLE修改表,| DROP FOREIGN KEY 外鍵約束名 | DISABLE KEYS | ENABLE KEYS | RENAME [TO] 新表名 | ORDER BY 列名 | CONVERT TO CHARACTER SET 字符集 [COLLATE 校對規(guī)則] | [DEFAULT] CHARACTER SET 字符集 [COLLATE 校對規(guī)則] | DISCARD TABL

31、ESPACE | IMPORT TABLESPACE | 表選項,2. ALTER TABLE修改表,ALTER TABLE用于更改原有表的結(jié)構(gòu)。例如，可以增加或刪減列，創(chuàng)建或取消索引，更改原有列的類型，或重新命名列或表。還可以更改表的評注和表的類型。允許進(jìn)行的修改中，許多子句的語法與CREATE TABLE中的子句的語法相近。其中包括表選項修改，選項有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH

32、等?？梢詤⒖肌癈REATE TABLE語法”。,2. ALTER TABLE修改表,IGNOREIGNORE是MySQL相對于標(biāo)準(zhǔn)SQL的擴(kuò)展。如果在新表中有重復(fù)關(guān)鍵字，或者當(dāng)STRICT模式啟動后出現(xiàn)警告，則使用IGNORE控制ALTER TABLE的運(yùn)行。如果沒有指定IGNORE，當(dāng)重復(fù)關(guān)鍵字錯誤發(fā)生時，復(fù)制操作被放棄，返回前一步驟。如果指定了IGNORE，則對于有重復(fù)關(guān)鍵字的行，只使用第一行，其它有沖突的行被刪除。并且，對錯誤值

33、進(jìn)行修正，使之盡量接近正確值。,2. ALTER TABLE修改表,使用多個子句可以在一個ALTER TABLE語句里寫入多個ADD, ALTER, DROP和CHANGE子句，中間用逗號分開。這是MySQL相對于標(biāo)準(zhǔn)SQL的擴(kuò)展。在標(biāo)準(zhǔn)SQL中，每個ALTER TABLE語句中每個子句只允許使用一次。例如，在一個語句中取消多個列：mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN

34、 d;,2. ALTER TABLE修改表,CHANGE和MODIFY可以使用“CHANGE 舊列名 新列定義”子句對列進(jìn)行重命名。重命名時，需給定舊的和新的列名稱和列當(dāng)前的類型。例如：要把一個INTEGER列的名稱從a變更到b，需要如下操作：mysql> ALTER TABLE t1 CHANGE a b INTEGER;,2. ALTER TABLE修改表,FIRST和AFTER可以使用“FIRST 列名”或“AFTER

35、 列名”在一個表行中的某個特定位置添加列。默認(rèn)把列添加到最后。也可以在CHANGE或MODIFY語句中使用FIRST和AFTER。修改缺省值“ALTER ... SET DEFAULT”或“ALTER ... DROP DEFAULT”用于指定列的新默認(rèn)值，或刪除舊的默認(rèn)值。如果舊的默認(rèn)值被刪除同時列值為NULL，則新的默認(rèn)值為NULL。如果列值不能為NULL，MySQL會指定一個默認(rèn)值。,2. ALTER TABLE修改表,修改索

36、引DROP INDEX用于取消索引。這是MySQL相對于標(biāo)準(zhǔn)SQL的擴(kuò)展。如果列從表中被取消了，則這些列也從相應(yīng)的索引中被取消。如果組成一個索引的所有列均被取消，則該索引也被取消。刪除列如果一個表只包含一列，則此列不能被取消。如果想要取消表，應(yīng)使用DROP TABLE。,2. ALTER TABLE修改表,DROP PRIMAY DEY刪除主鍵DROP PRIMAY DEY用于取消主索引。注釋：在MySQL較早的版本中，如果沒

37、有主索引，則DROP PRIMARY KEY會取消表中的第一個UNIQUE索引。在MySQL 5.1中不會出現(xiàn)這種情況。如果在MySQL 5.1中對沒有主鍵的表使用DROP PRIMARY KEY，則會出現(xiàn)錯誤信息。,2. ALTER TABLE修改表,添加唯一約束和主鍵約束如果向表中添加UNIQUE KEY或PRIMARY KEY，則UNIQUE KEY或PRIMARY KEY會被儲存在非唯一索引之前，這樣MySQL就可以盡早地檢查

38、出重復(fù)關(guān)鍵字。ORDER BYORDER BY用于在創(chuàng)建新表時，讓各行按一定的順序排列。注意，在插入和刪除后，表不會仍保持此順序。當(dāng)知道多數(shù)情況下會按照特定的順序查詢各行時，可以使用這個選項；在對表進(jìn)行了大的改動后，通過使用此選項，可以提高查詢效率。在有些情況下，如果表按列排序，對于MySQL來說，排序可能會更簡單。,2. ALTER TABLE修改表,更改字符集如果想要把表默認(rèn)的字符集和所有字符列（CHAR, VARCHAR,

39、TEXT）改為新的字符集，應(yīng)使用如下語句：ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集;,3. RENAME TABLE重命名表,RENAME TABLE 表名TO 新表名[, 表名2 TO 新表名2] ...本語句用于對一個或多個表進(jìn)行重命名，當(dāng)對表進(jìn)行重命名時，其它線程不能讀取任何表。,3. RENAME TABLE重命名表,如果此語句用于對多個表進(jìn)行重命名，則重命名操作從左至右進(jìn)行。

40、如果想要交換兩個表的名稱，可以這樣做（假設(shè)不存在名稱為tmp_table的表）：RENAME TABLE old_table TO tmp_table , new_table TO old_table , tmp_table TO new_table;,3. RENAME TABLE重命名表,只要兩個數(shù)據(jù)庫位于同一文件系統(tǒng)中，還可以對表進(jìn)行重命名，把表從一個數(shù)據(jù)庫中移動到另一個數(shù)據(jù)庫中：RENAME TABLE current_db

41、.tbl_name TO other_db.tbl_name;,4. DROP TABLE刪除表,DROP [TEMPORARY] TABLE[IF EXISTS]表名1 [, 表名2] ... [RESTRICT | CASCADE]DROP TABLE用于刪除一個或多個表。必須有每個表的DROP權(quán)限。所有的表數(shù)據(jù)和表定義會被取消，所以使用本語句要小心！,4. DROP TABLE刪除表,TEMPORARY關(guān)鍵詞具有以下作用：