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

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

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

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

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

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