數(shù)據(jù)庫管理與應(yīng)用第章表

第六章表1課程目標(biāo)創(chuàng)建表修改表重命名表刪除表21.CREATETABLE創(chuàng)建新表使用CREATETABLE可以創(chuàng)建一個(gè)新表，這一語句可以分為兩種寫法:一種用于直接創(chuàng)建新表;另一種用于參照其它表的定義創(chuàng)建一個(gè)新表。31.CREATETABLE創(chuàng)建新表以下寫法用于直接創(chuàng)建新表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[(列定義1，列定義2，...，[表級(jí)約束])][表選項(xiàng)][select表達(dá)式]其中列定義的基本寫法為：列名列的類型41.CREATETABLE創(chuàng)建新表以下代碼用于創(chuàng)建一個(gè)名為“student”的表，該表有兩列：ID：INT類型，用于存儲(chǔ)學(xué)生的編號(hào)；NAME：CHAR（8）類型，用于存儲(chǔ)學(xué)生的姓名。表的創(chuàng)建代碼如下：mysql>createtablestudent->(->IDint,->NAMEchar(8)->);QueryOK,0rowsaffected(0.09sec)51.CREATETABLE創(chuàng)建新表MySQL將每一個(gè)表都單獨(dú)保存在一個(gè)“.frm”格式的文件中，該文件存儲(chǔ)在相應(yīng)的數(shù)據(jù)庫目錄中。表的存儲(chǔ)引擎也可能會(huì)創(chuàng)建其它文件，如對(duì)于MyISAM類型的表，存儲(chǔ)引擎會(huì)自動(dòng)創(chuàng)建數(shù)據(jù)和索引文件。61.CREATETABLE創(chuàng)建新表創(chuàng)建臨時(shí)表在創(chuàng)建表格時(shí)，可以使用TEMPORARY關(guān)鍵詞使當(dāng)前建立的表為臨時(shí)表。臨時(shí)表只能在當(dāng)前連接中使用，當(dāng)連接關(guān)閉時(shí)，臨時(shí)表也被自動(dòng)刪除。這意味著兩個(gè)不同的連接可以創(chuàng)建兩個(gè)名稱相同的臨時(shí)表，這兩個(gè)臨時(shí)表不會(huì)互相沖突，也不與原有的同名的非臨時(shí)表沖突。71.CREATETABLE創(chuàng)建新表創(chuàng)建臨時(shí)表如果臨時(shí)表的名稱與數(shù)據(jù)庫中現(xiàn)有的非臨時(shí)表名稱相同，則非臨時(shí)表會(huì)被隱藏，直到該臨時(shí)表被刪除為止。創(chuàng)建臨時(shí)表要求當(dāng)前帳戶擁有CREATETEMPORARYTABLES權(quán)限。81.CREATETABLE創(chuàng)建新表避免創(chuàng)建已經(jīng)存在的表如果表已存在，則使用關(guān)鍵詞IFNOTEXISTS可以防止發(fā)生錯(cuò)誤。注意，原有表的結(jié)構(gòu)與CREATETABLE語句中表示的表的結(jié)構(gòu)是否相同，這一點(diǎn)沒有驗(yàn)證。注釋：如果在CREATETABLE...SELECT語句中使用IFNOTEXISTS，則不論表是否已存在，由SELECT部分選擇的記錄都會(huì)被插入。91.CREATETABLE創(chuàng)建新表是否允許列為空值在列定義中，可以加入以下選項(xiàng)以指明某一列是否允許出現(xiàn)空值：NULL：允許出現(xiàn)空值；NOTNULL：不允許出現(xiàn)空值。101.CREATETABLE創(chuàng)建新表以下代碼在創(chuàng)建“student”表時(shí)，不允許學(xué)生的編號(hào)（ID）和姓名（NAME）為空，但允許住址（ADDR）為空。mysql>createtablestudent->(->IDintNOTNULL,->NAMEchar(8)NOTNULL,->ADDRchar(60)NULL->);QueryOK,0rowsaffected(0.09sec)111.CREATETABLE創(chuàng)建新表指定列的默認(rèn)值在列定義中，可以加入以下選項(xiàng)以指明某一列的默認(rèn)值：DEFAULT默認(rèn)值121.CREATETABLE創(chuàng)建新表默認(rèn)值必須為常數(shù)，不能是函數(shù)或表達(dá)式，例如，一個(gè)日期列的默認(rèn)值不能被設(shè)置為一個(gè)函數(shù)，如NOW()或CURRENT_DATE。這里有一個(gè)特例，可以對(duì)TIMESTAMP列指定CURRENT_TIMESTAMP為默認(rèn)值。BLOB和TEXT類型的列不能指定默認(rèn)值。131.CREATETABLE創(chuàng)建新表使列值自動(dòng)增加如果一個(gè)列是整數(shù)類型，則可以使用“AUTO_INCREMENT”關(guān)鍵字指定該列的值自動(dòng)增加。使用自動(dòng)增加可以很方便的在表中的某一列上，為每一條記錄建立不同的列值，從而根據(jù)該列的值可以唯一的確定表中的一條記錄。141.CREATETABLE創(chuàng)建新表當(dāng)指定某一列為自動(dòng)增加時(shí)，向該列插入一個(gè)NULL值（建議）或0時(shí)，該列會(huì)被自動(dòng)設(shè)置為比上一次插入時(shí)更大的值，通常情況下依次增加1。也就是說，新增加的列值總是當(dāng)前表中該列的最大值。如果新增加的記錄是表中的第一條記錄，則該值為1。151.CREATETABLE創(chuàng)建新表指定列的字符集如果某一列是字符列，則其定義可以包括一個(gè)CHARACTERSET屬性，用來指定字符集和校對(duì)規(guī)則?？梢詫HARACTERSET縮寫為CHARSET。161.CREATETABLE創(chuàng)建新表為列添加注釋為了記錄表中某些列的作用，可以在建表時(shí)將這些列的用法添加列的注釋中，添加注釋使用COMMENT關(guān)鍵字。可以通過SHOWCREATETABLE或SHOWFULLCOLUMNS語句來查看列的注釋。171.CREATETABLE創(chuàng)建新表設(shè)置主鍵主鍵就好比是表中每一條記錄的身份，一張表中不能出現(xiàn)兩條主鍵相同的記錄，換言之，只要主鍵的值確定了，就唯一的確定了表中的一條記錄。被設(shè)置為主鍵的列同時(shí)會(huì)被強(qiáng)制設(shè)置為NOTNULL。一張表只能設(shè)置一個(gè)主鍵，主鍵可以是一個(gè)列，也可以是多個(gè)列的組合。181.CREATETABLE創(chuàng)建新表設(shè)置主鍵將一個(gè)列設(shè)置為主鍵的方法是，在列的定義中使用“[PRIMARY]KEY”子句。以下代碼將“student”表的ID列設(shè)置為主鍵。Createtablestudent(IDintKEY,NAMEchar(8))191.CREATETABLE創(chuàng)建新表要將主鍵設(shè)置為多個(gè)列的組合，必須使用列級(jí)約束PRIMARYKEY子句。以下代碼演示了將表grade（成績(jī)）的列SID（學(xué)號(hào)），CID（課程號(hào)）設(shè)置為主鍵。createtablegrade(sidint,cidint,gradeint,primarykey(sid,cid))201.CREATETABLE創(chuàng)建新表使列值不重復(fù)在表中輸入數(shù)據(jù)時(shí)，有時(shí)需要某一列的內(nèi)容不含重復(fù)內(nèi)容，如書藉的出版號(hào)，此時(shí)可以使用UNIQUE[KEY]關(guān)鍵字來防止列值重復(fù)。如果在添加新行時(shí)插入的列值在表中已存在，則會(huì)出現(xiàn)錯(cuò)誤。例外情況是，如果索引中的一個(gè)列允許包含NULL值，NULL值可以重復(fù)出現(xiàn)。211.CREATETABLE創(chuàng)建新表使用索引可以使用如下方法在表中指定索引：INDEX索引名索引類型（索引列1，索引列2，…）索引用于加速表的查詢速度，有關(guān)索引的詳細(xì)信息可參考索引的管理。221.CREATETABLE創(chuàng)建新表SERIAL實(shí)際使用中，經(jīng)常需要將一個(gè)列設(shè)置為“BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTUNIQUE”屬性，為便于進(jìn)行這一操作，可以使用關(guān)鍵字“SERIAL”來替代這段很長(zhǎng)的代碼。231.CREATETABLE創(chuàng)建新表外鍵約束外鍵約束是指當(dāng)前表中的某些列與其它參考表中的某些列存在著參照關(guān)系，在當(dāng)前表中的列的值必須已經(jīng)出現(xiàn)在參考表的對(duì)應(yīng)列中。在列定義和表級(jí)約束中都可以指定表的外鍵約束。241.CREATETABLE創(chuàng)建新表外鍵定義服從下列情況：所有tables必須是InnoDB型，它們不能是臨時(shí)表。在引用表中，必須有一個(gè)索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個(gè)索引如果不存在，它必須在引用表里被自動(dòng)創(chuàng)建。在引用表中，必須有一個(gè)索引，被引用的列以同樣的順序被列在其中作為第一列。不支持對(duì)外鍵列的索引前綴。這樣的后果之一是BLOB和TEXT列不被包括在一個(gè)外鍵中，這是因?yàn)閷?duì)這些列的索引必須總是包含一個(gè)前綴長(zhǎng)度。如果CONSTRAINTsymbol被給出，它在數(shù)據(jù)庫里必須是唯一的。如果它沒有被給出，InnoDB自動(dòng)創(chuàng)建這個(gè)名字。251.CREATETABLE創(chuàng)建新表表選項(xiàng)表選項(xiàng)用于對(duì)表進(jìn)行優(yōu)化，以下介紹表選項(xiàng)的含義。ENGINE和TYPEENGINE和TYPE選項(xiàng)用于為表指定存儲(chǔ)引擎，ENGINE是首選的選項(xiàng)名稱。ENGINE和TYPE選項(xiàng)可參考MySQL備份與恢復(fù)中相關(guān)內(nèi)容。261.CREATETABLE創(chuàng)建新表表選項(xiàng)如果被指定的存儲(chǔ)引擎無法利用，則MySQL使用MyISAM代替。例如，一個(gè)表定義包括ENGINE=BDB選項(xiàng)，但是MySQL服務(wù)器不支持BDB表，則表被創(chuàng)建為MyISAM表。這樣，如果在主機(jī)上有事務(wù)表，但在從屬機(jī)上創(chuàng)建的是非交互式表（以加快速度）時(shí)，可以進(jìn)行復(fù)制設(shè)置。在MySQL5.1中，如果沒有遵守存儲(chǔ)引擎規(guī)約，則會(huì)出現(xiàn)警告。271.CREATETABLE創(chuàng)建新表AUTO_INCREMENT表的初始AUTO_INCREMENT值。在MySQL5.1中，本選項(xiàng)只適用于MyISAM和MEMORY表。InnoDB也支持本選項(xiàng)。如果引擎不支持AUTO_INCREMENT表選項(xiàng)，則要設(shè)置引擎的第一個(gè)auto-increment值，需插入一個(gè)“假”行。該行的值比創(chuàng)建表后的值小一，然后刪除該假行。281.CREATETABLE創(chuàng)建新表AVG_ROW_LENGTH表中平均行長(zhǎng)度的近似值。只需要對(duì)含尺寸可變的記錄的大型表進(jìn)行此項(xiàng)設(shè)置。[DEFAULT]CHARACTERSET用于為表指定一個(gè)默認(rèn)字符集。CHARSET是CHARACTERSET的同義詞。COLLATE

用于為表指定一個(gè)默認(rèn)整序。291.CREATETABLE創(chuàng)建新表CHECKSUM如果希望MySQL隨時(shí)對(duì)所有行進(jìn)行實(shí)時(shí)檢驗(yàn)求和（也就是，表變更后，MySQL自動(dòng)更新檢驗(yàn)求和），則應(yīng)把此項(xiàng)設(shè)置為1。這樣做，表的更新速度會(huì)略微慢些，但是更容易尋找到受損的表。CHECKSUMTABLE語句用于報(bào)告檢驗(yàn)求和（僅限于MyISAM）。COMMENT表的注釋，最長(zhǎng)60個(gè)字符。301.CREATETABLE創(chuàng)建新表CONNECTIONFEDERATED表的連接字符串。（注釋：較早版本的MySQL使用COMMENT選項(xiàng)用于連接字符串。MAX_ROWS打算儲(chǔ)存在表中的行數(shù)目的最大值。這不是一個(gè)硬性限值，而更像一個(gè)指示語句，指示出表必須能存儲(chǔ)至少這么多行。MIN_ROWS打算存儲(chǔ)在表中的行數(shù)目的最小值。311.CREATETABLE創(chuàng)建新表PACK_KEYS

如果希望索引更小，則把此選項(xiàng)設(shè)置為1。這樣做通常使更新速度變慢，同時(shí)閱讀速度加快。把選項(xiàng)設(shè)置為0可以取消所有的關(guān)鍵字壓縮。把此選項(xiàng)設(shè)置為DEFAULT時(shí)，存儲(chǔ)引擎只壓縮長(zhǎng)的CHAR或VARCHAR列（僅限于MyISAM）。如果不使用PACK_KEYS，則默認(rèn)操作是只壓縮字符串，但不壓縮數(shù)字。如果使用PACK_KEYS=1，則對(duì)數(shù)字也進(jìn)行壓縮。321.CREATETABLE創(chuàng)建新表PASSWORD使用密碼對(duì).frm文件加密。在標(biāo)準(zhǔn)MySQL版本中，本選項(xiàng)不起任何作用。DELAY_KEY_WRITE如果想要延遲對(duì)關(guān)鍵字的更新，等到表關(guān)閉后再更新，則把此項(xiàng)設(shè)置為1（僅限于MyISAM）。331.CREATETABLE創(chuàng)建新表ROW_FORMAT

定義各行應(yīng)如何儲(chǔ)存。當(dāng)前，此選項(xiàng)只適用于MyISAM表。對(duì)于靜態(tài)行或長(zhǎng)度可變行，此選項(xiàng)值可以為FIXED或DYNAMIC。myisampack用于把類型設(shè)置為COMPRESSED。在默認(rèn)情況下，InnoDB記錄以壓縮格式存儲(chǔ)（ROW_FORMAT=COMPACT）。通過指定ROW_FORMAT=REDUNDANT，仍然可以申請(qǐng)用于較早版本的MySQL中的非壓縮格式。341.CREATETABLE創(chuàng)建新表RAID_TYPE在MySQL5.0中,RAID支持被刪除了。UNION當(dāng)想要把一組相同的表當(dāng)作一個(gè)表使用時(shí)，采用UNION。UNION僅適用于MERGE表。對(duì)于映射到一個(gè)MERGE表上的表，必須擁有SELECT,UPDATE和DELETE權(quán)限。351.CREATETABLE創(chuàng)建新表INSERT_METHOD如果希望在MERGE表中插入數(shù)據(jù)，必須用INSERT_METHOD指定應(yīng)插入行的表。INSERT_METHOD選項(xiàng)僅用于MERGE表。使用FIRST或LAST把行插入到第一個(gè)或最后一個(gè)表中；或者使用NO，阻止插入行。DATADIRECTORY,INDEXDIRECTORY

通過使用DATADIRECTORY='directory'或INDEXDIRECTORY='directory'，可以指定MyISAM存儲(chǔ)引擎放置表格數(shù)據(jù)文件和索引文件的位置。注意，目錄應(yīng)是通向目錄的完整路徑（不是相對(duì)路徑）。361.CREATETABLE創(chuàng)建新表在一個(gè)表的基礎(chǔ)上創(chuàng)建表可以在CREATETABLE語句的末尾添加一個(gè)SELECT語句，在一個(gè)表的基礎(chǔ)上創(chuàng)建表。CREATETABLE新表名SELECT*FROM老表名;MySQL會(huì)對(duì)SELECT中的所有項(xiàng)創(chuàng)建新列。371.CREATETABLE創(chuàng)建新表參照其它表的定義創(chuàng)建一個(gè)新表以下寫法用于參照其它表的定義創(chuàng)建一個(gè)新表：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名LIKE參照表的表名382.ALTERTABLE修改表ALTER[IGNORE]TABLE表名修改項(xiàng)1[,修改項(xiàng)2]...其中，修改項(xiàng)寫法如下：ADD[COLUMN]列定義[FIRST|AFTER列名]|ADD[COLUMN](列定義1,列定義2)|ADDINDEX[索引名][索引類型](索引列名,...)|ADD[CONSTRAINT[約束名]]PRIMARYKEY[索引類型](索引列名,...)|ADD[CONSTRAINT[約束名]]UNIQUE[索引名][索引類型](索引列名,...)392.ALTERTABLE修改表|ADD[FULLTEXT|SPATIAL][索引名](索引列名,...)|ADD[CONSTRAINT[約束名]]FOREIGNKEY[索引名](索引列名,...)[外鍵約束定義]|ALTER[COLUMN]列名{SETDEFAULTliteral|DROPDEFAULT}|CHANGE[COLUMN]舊列名列定義

[FIRST|AFTER列名]|MODIFY[COLUMN]列定義[FIRST|AFTER列名]|DROP[COLUMN]列名

|DROPPRIMARYKEY|DROPINDEX索引名402.ALTERTABLE修改表|DROPFOREIGNKEY外鍵約束名

|DISABLEKEYS|ENABLEKEYS|RENAME[TO]新表名

|ORDERBY列名

|CONVERTTOCHARACTERSET字符集[COLLATE校對(duì)規(guī)則]|[DEFAULT]CHARACTERSET字符集[COLLATE校對(duì)規(guī)則]|DISCARDTABLESPACE|IMPORTTABLESPACE|表選項(xiàng)412.ALTERTABLE修改表ALTERTABLE用于更改原有表的結(jié)構(gòu)。例如，可以增加或刪減列，創(chuàng)建或取消索引，更改原有列的類型，或重新命名列或表。還可以更改表的評(píng)注和表的類型。允許進(jìn)行的修改中，許多子句的語法與CREATETABLE中的子句的語法相近。其中包括表選項(xiàng)修改，選項(xiàng)有ENGINE,AUTO_INCREMENT和AVG_ROW_LENGTH等?？梢詤⒖肌癈REATETABLE語法”。422.ALTERTABLE修改表IGNOREIGNORE是MySQL相對(duì)于標(biāo)準(zhǔn)SQL的擴(kuò)展。如果在新表中有重復(fù)關(guān)鍵字，或者當(dāng)STRICT模式啟動(dòng)后出現(xiàn)警告，則使用IGNORE控制ALTERTABLE的運(yùn)行。如果沒有指定IGNORE，當(dāng)重復(fù)關(guān)鍵字錯(cuò)誤發(fā)生時(shí)，復(fù)制操作被放棄，返回前一步驟。如果指定了IGNORE，則對(duì)于有重復(fù)關(guān)鍵字的行，只使用第一行，其它有沖突的行被刪除。并且，對(duì)錯(cuò)誤值進(jìn)行修正，使之盡量接近正確值。432.ALTERTABLE修改表使用多個(gè)子句可以在一個(gè)ALTERTABLE語句里寫入多個(gè)ADD,ALTER,DROP和CHANGE子句，中間用逗號(hào)分開。這是MySQL相對(duì)于標(biāo)準(zhǔn)SQL的擴(kuò)展。在標(biāo)準(zhǔn)SQL中，每個(gè)ALTERTABLE語句中每個(gè)子句只允許使用一次。例如，在一個(gè)語句中取消多個(gè)列：mysql>ALTERTABLEt2DROPCOLUMNc,DROPCOLUMNd;442.ALTERTABLE修改表CHANGE和MODIFY可以使用“CHANGE舊列名新列定義”子句對(duì)列進(jìn)行重命名。重命名時(shí)，需給定舊的和新的列名稱和列當(dāng)前的類型。例如：要把一個(gè)INTEGER列的名稱從a變更到b，需要如下操作：mysql>ALTERTABLEt1CHANGEabINTEGER;452.ALTERTABLE修改表FIRST和AFTER可以使用“FIRST列名”或“AFTER列名”在一個(gè)表行中的某個(gè)特定位置添加列。默認(rèn)把列添加到最后。也可以在CHANGE或MODIFY語句中使用FIRST和AFTER。修改缺省值“ALTER...SETDEFAULT”或“ALTER...DROPDEFAULT”用于指定列的新默認(rèn)值，或刪除舊的默認(rèn)值。如果舊的默認(rèn)值被刪除同時(shí)列值為NULL，則新的默認(rèn)值為NULL。如果列值不能為NULL，MySQL會(huì)指定一個(gè)默認(rèn)值。462.ALTERTABLE修改表修改索引DROPINDEX用于取消索引。這是MySQL相對(duì)于標(biāo)準(zhǔn)SQL的擴(kuò)展。如果列從表中被取消了，則這些列也從相應(yīng)的索引中被取消。如果組成一個(gè)索引的所有列均被取消，則該索引也被取消。刪除列如果一個(gè)表只包含一列，則此列不能被取消。如果想要取消表，應(yīng)使用DROPTABLE。472.ALTERTABLE修改表DROPPRIMAYDEY刪除主鍵DROPPRIMAYDEY用于取消主索引。注釋：在MySQL較早的版本中，如果沒有主索引，則DROPPRIMARYKEY會(huì)取消表中的第一個(gè)UNIQUE索引。在MySQL5.1中不會(huì)出現(xiàn)這種情況。如果在MySQL5.1中對(duì)沒有主鍵的表使用DROPPRIMARYKEY，則會(huì)出現(xiàn)錯(cuò)誤信息。482.ALTERTABLE修改表添加唯一約束和主鍵約束如果向表中添加UNIQUEKEY或PRIMARYKEY，則UNIQUEKEY或PRIMARYKEY會(huì)被儲(chǔ)存在非唯一索引之前，這樣MySQL就可以盡早地檢查出重復(fù)關(guān)鍵字。ORDERBYORDERBY用于在創(chuàng)建新表時(shí)，讓各行按一定的順序排列。注意，在插入和刪除后，表不會(huì)仍保持此順序。當(dāng)知道多數(shù)情況下會(huì)按照特定的順序查詢各行時(shí)，可以使用這個(gè)選項(xiàng)；在對(duì)表進(jìn)行了大的改動(dòng)后，通過使用此選項(xiàng)，可