MySQL教程（第4版） 課件 第3章 數(shù)據(jù)庫(kù)和表

第3章

數(shù)據(jù)庫(kù)和表——MySQL數(shù)據(jù)庫(kù)MySQL數(shù)據(jù)庫(kù)安裝

MySQL

系統(tǒng)時(shí)，就生成了系統(tǒng)使用的數(shù)據(jù)庫(kù)，包括information_schema、mysql和performance_schema等，MySQL把有關(guān)DBMS自身的管理信息都保存在這幾個(gè)數(shù)據(jù)庫(kù)中，如果刪除了它們，MySQL將無(wú)法正常工作，故請(qǐng)讀者操作時(shí)千萬(wàn)留神！如果安裝時(shí)選擇安裝實(shí)例數(shù)據(jù)庫(kù)，則系統(tǒng)還有另外兩個(gè)實(shí)例數(shù)據(jù)庫(kù)sakila和world。通過(guò)以下命令可以查看MySQL已有的數(shù)據(jù)庫(kù)：SHOWDATABASES;命令執(zhí)行結(jié)果如圖。01創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句如下：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]數(shù)據(jù)庫(kù)名[創(chuàng)建選項(xiàng),...]其中，創(chuàng)建選項(xiàng)：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校對(duì)規(guī)則名IFNOTEXISTS：在創(chuàng)建數(shù)據(jù)庫(kù)前須進(jìn)行判斷，只有該數(shù)據(jù)庫(kù)目前尚不存在時(shí)才可執(zhí)行CREATEDATABASE操作。用此選項(xiàng)可以避免出現(xiàn)數(shù)據(jù)庫(kù)已經(jīng)存在卻再新建的錯(cuò)誤。DEFAULT：指定默認(rèn)值。CHARACTERSET：指定數(shù)據(jù)庫(kù)字符集。COLLATE：指定字符集的校對(duì)規(guī)則。說(shuō)明：如果在MySQL環(huán)境下采用下列命令設(shè)置了字符集，每個(gè)數(shù)據(jù)庫(kù)創(chuàng)建時(shí)不需要單獨(dú)重新設(shè)置：SETCHARACTER_SET_DATABASE='gbk';SETCHARACTER_SET_SERVER='gbk';創(chuàng)建數(shù)據(jù)庫(kù)【例】創(chuàng)建test數(shù)據(jù)庫(kù)。CREATEDATABASEtest;說(shuō)明：如果創(chuàng)建數(shù)據(jù)庫(kù)不指定選項(xiàng)就使用默認(rèn)選項(xiàng)參數(shù)。如果已經(jīng)創(chuàng)建了名為test的數(shù)據(jù)庫(kù)，重復(fù)創(chuàng)建時(shí)系統(tǒng)將會(huì)提示數(shù)據(jù)庫(kù)已經(jīng)存在的錯(cuò)誤信息，不能再創(chuàng)建。使用IFNOTEXISTS子句可不顯示錯(cuò)誤信息：CREATEDATABASEIFNOTEXISTStest;SHOWDATABASES; #顯示的數(shù)據(jù)庫(kù)中多了test02修改數(shù)據(jù)庫(kù)修改數(shù)據(jù)庫(kù)修改數(shù)據(jù)庫(kù)語(yǔ)句如下：ALTER{DATABASE|SCHEMA}[數(shù)據(jù)庫(kù)名]修改選項(xiàng)[,修改選項(xiàng)]...其中，修改選項(xiàng)：[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校對(duì)規(guī)則名說(shuō)明：ALTERDATABASE用于更改數(shù)據(jù)庫(kù)的全局特性，這些特性?xún)?chǔ)存在數(shù)據(jù)庫(kù)目錄中的db.opt文件中。用戶(hù)必須有對(duì)數(shù)據(jù)庫(kù)進(jìn)行修改的權(quán)限，才可使用ALTERDATABASE。修改數(shù)據(jù)庫(kù)的選項(xiàng)與創(chuàng)建數(shù)據(jù)庫(kù)的相同，功能不再重復(fù)說(shuō)明。如果語(yǔ)句中將數(shù)據(jù)庫(kù)名稱(chēng)忽略，則修改當(dāng)前（默認(rèn)）數(shù)據(jù)庫(kù)?！纠啃薷臄?shù)據(jù)庫(kù)test的默認(rèn)字符集和校對(duì)規(guī)則。ALTERDATABASEtestDEFAULTCHARACTERSETgb2312DEFAULTCOLLATEgb2312_chinese_ci;03刪除數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù)語(yǔ)句如下：DROPDATABASE[IFEXISTS]數(shù)據(jù)庫(kù)名使用IFEXISTS子句，可避免刪除不存在的數(shù)據(jù)庫(kù)時(shí)出現(xiàn)MySQL錯(cuò)誤信息。例如，刪除test數(shù)據(jù)庫(kù)：DROPDATABASEtest;SHOWDATABASES; #顯示數(shù)據(jù)庫(kù)中沒(méi)有test04打開(kāi)和關(guān)閉數(shù)據(jù)庫(kù)打開(kāi)和關(guān)閉數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)創(chuàng)建后，在同一個(gè)會(huì)話(huà)中就自動(dòng)打開(kāi)。下列語(yǔ)句打開(kāi)指定數(shù)據(jù)庫(kù)，使其成為當(dāng)前數(shù)據(jù)庫(kù)：USE數(shù)據(jù)庫(kù)名關(guān)閉數(shù)據(jù)庫(kù)，其后會(huì)話(huà)就沒(méi)有當(dāng)前數(shù)據(jù)庫(kù)了。第3章

數(shù)據(jù)庫(kù)和表——MySQL表01創(chuàng)

建

表列定義及基本屬性列鍵屬性列其他屬性列數(shù)據(jù)類(lèi)型虛擬列（生成列）由原有的表創(chuàng)建新表創(chuàng)

建

表創(chuàng)建表語(yǔ)句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(列定義,...[表索引][完整性約束])[表選項(xiàng)]說(shuō)明：TEMPORARY：包含此關(guān)鍵字表示新建的表為臨時(shí)表，否則創(chuàng)建的表通常稱(chēng)為持久表。IFNOTEXISTS：在創(chuàng)建表前加上一個(gè)判斷，只有該表目前尚不存在時(shí)才創(chuàng)建。列定義：列又稱(chēng)字段。列定義包括列名、數(shù)據(jù)類(lèi)型和寬度等，還可包含是否允許空值和完整性約束。表索引：UNIQUEKEY(...)|PRIMARYKEY(...)|INDEX(...)：第1項(xiàng)指定部分列（或單列）值的唯一性，第2項(xiàng)列作為主鍵，第3項(xiàng)列創(chuàng)建索引。完整性約束：CHECK、FOREIGNKEY(…)：前者定義部分列（或單列）值數(shù)據(jù)完整性，后者定義本表與參考表的記錄完整性。表選項(xiàng)：指定表的屬性。創(chuàng)

建

表1．列定義及基本屬性列按照下列形式定義：列名數(shù)據(jù)類(lèi)型[(長(zhǎng)度和小數(shù))][空值][鍵][字符集][列其他屬性][注釋]其中：列名：必須符合標(biāo)識(shí)符規(guī)則，長(zhǎng)度不能超過(guò)64個(gè)字符，而且在表中要唯一。如果有MySQL保留字則必須用單引號(hào)括起來(lái)。數(shù)據(jù)類(lèi)型[(長(zhǎng)度和小數(shù))]：列保存數(shù)據(jù)類(lèi)型。整數(shù)型、實(shí)數(shù)型和字符串型需要指定長(zhǎng)度，實(shí)數(shù)型還需要指定小數(shù)位?？罩担篘OTNULL|NULL：指定該列是否允許為空，前者為不允許為空，后者為可以為空。如果不指定，則默認(rèn)為NULL。字符集：如果列數(shù)據(jù)類(lèi)型為字符串型，可以指定存儲(chǔ)字符的字符集和校對(duì)規(guī)則：CHARACTERSET字符集名COLLATE校對(duì)規(guī)則名注釋?zhuān)篊OMMENT'注釋內(nèi)容'，列的描述內(nèi)容，說(shuō)明列的作用。創(chuàng)

建

表2．列鍵屬性列鍵屬性如下：PRIMARYKEY：列設(shè)置為主鍵。一個(gè)表只能定義一個(gè)主鍵，主鍵一定要為NOTNULL。UNIQUE：列設(shè)置為唯一鍵。將確保所有值都有不同的值，只有NULL值可以重復(fù)。一個(gè)表可以設(shè)置多個(gè)列為唯一鍵。創(chuàng)

建

表3．列其他屬性列還可以指定下列屬性：AUTO_INCREMENT：設(shè)置自增屬性，只有數(shù)據(jù)類(lèi)型為整型的列才能設(shè)置此屬性。當(dāng)插入NULL值或0，將列原來(lái)值增1，順序從1開(kāi)始。每個(gè)表只能有一個(gè)AUTO_INCREMENT列，并且必須能被索引。DEFAULT：指定列默認(rèn)值，默認(rèn)值必須為一個(gè)常數(shù)。其中，BLOB和TEXT類(lèi)型列不能被賦予默認(rèn)值。UNSIGNED：對(duì)于整數(shù)類(lèi)型，指定為無(wú)符號(hào)整數(shù)。ZEROFILL：可用于任何數(shù)值類(lèi)型，用0填充所有剩余列空間。例如，無(wú)符號(hào)INT的默認(rèn)寬度是10，因此，當(dāng)值為4時(shí)，將它表示為0000000004。IDENTITY：包含系統(tǒng)所生成序號(hào)值的一個(gè)標(biāo)識(shí)列，該序號(hào)值唯一標(biāo)識(shí)表中的一列，可以作為鍵值。每個(gè)表只能有一個(gè)列被設(shè)置為標(biāo)識(shí)屬性，該列數(shù)據(jù)類(lèi)型只能是整型。定義標(biāo)識(shí)屬性時(shí)，可指定其種子（起始）值、增量值，二者的默認(rèn)值均為1。系統(tǒng)自動(dòng)更新標(biāo)識(shí)列值。例如：idintNOTNULLIDENTITYidintNOTNULLIDENTITY(1,1)創(chuàng)

建

表4．列數(shù)據(jù)類(lèi)型列數(shù)據(jù)類(lèi)型按照下列形式描述：整數(shù)類(lèi)型名[(長(zhǎng)度)][UNSIGNED][ZEROFILL]實(shí)數(shù)類(lèi)型名[(長(zhǎng)度.小數(shù)位)][UNSIGNED][ZEROFILL]大數(shù)據(jù)類(lèi)型名字符串類(lèi)型名(長(zhǎng)度)[BINARY|ASCII|UNICODE]文本類(lèi)型名[BINARY]日期時(shí)間類(lèi)型名空間類(lèi)型名位類(lèi)型：bit[n]枚舉類(lèi)型：enum(值,...)集合類(lèi)型：set(值,...)鍵值類(lèi)型：json其中，具體類(lèi)型名如下：整數(shù)類(lèi)型名：tinyint|smallint|mediumint|int|integer|bigint實(shí)數(shù)類(lèi)型名：real|double|decimal|numeric大數(shù)據(jù)類(lèi)型名：tinyblob|blob|mediumblob|longblob字符串類(lèi)型名：char|varchar|tinytext|text|mediumtext|longtext日期時(shí)間類(lèi)型名：date|time|datetime|timestamp|year創(chuàng)

建

表5．虛擬列（生成列）虛擬列又稱(chēng)生成列，按照下列形式描述：列名數(shù)據(jù)類(lèi)型GENERATEDALWAYSAS(列生成表達(dá)式)照表達(dá)式計(jì)算的值同步變化。【例】在xscj數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)學(xué)生表，表名為xs。輸入以下命令：CREATEDATABASExscj;USExscj;CREATETABLExs(學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,姓名 char(4) NOTNULL,專(zhuān)業(yè) char(10) NULL,性別 tinyint(1) NOTNULLDEFAULT1,出生日期 date NOTNULL,總學(xué)分 tinyint(1) NULL,備注 text NULL,照片 blob NULL);創(chuàng)

建

表說(shuō)明：（1）PRIMARYKEY：表示將“學(xué)號(hào)”列定義為主鍵。（2）DEFAULT1：表示“性別”的默認(rèn)值為1。實(shí)際上，性別如果僅保存2種狀態(tài)，可以定義為bit(1)。已經(jīng)創(chuàng)建的表可以使用以下命令顯示表結(jié)構(gòu)：DESCRIBE表名;例如：USExscj; #打開(kāi)xscj數(shù)據(jù)庫(kù)SHOWTABLES; #顯示xscj數(shù)據(jù)庫(kù)中包含的表DESCRIBExs; #顯示xs表結(jié)構(gòu)創(chuàng)

建

表6．由原有的表創(chuàng)建新表除了全新創(chuàng)建，用戶(hù)也可以直接復(fù)制數(shù)據(jù)庫(kù)中原有表的結(jié)構(gòu)和數(shù)據(jù)，用這種方式十分方便、快捷。由原有的表創(chuàng)建新表語(yǔ)句如下：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名[LIKE源表名]|[AS(SELECT語(yǔ)句)];說(shuō)明：（1）使用LIKE關(guān)鍵字創(chuàng)建一個(gè)與“源表”相同結(jié)構(gòu)的新表，源表的列名、數(shù)據(jù)類(lèi)型、是否空值、主鍵、默認(rèn)值、索引、約束、分區(qū)等都將被復(fù)制，但是源表的記錄不會(huì)復(fù)制，因此創(chuàng)建的新表是一個(gè)空表。（2）使用AS關(guān)鍵字可以復(fù)制SELECT語(yǔ)句查詢(xún)的結(jié)果表，但源表的一些屬性（如主鍵、生成列等）卻不會(huì)被復(fù)制。創(chuàng)

建

表【例】在xscj數(shù)據(jù)庫(kù)中，復(fù)制xs表創(chuàng)建表名為xs1的表；再創(chuàng)建一個(gè)名為xs2的表，包含xs表的部分指定列。打開(kāi)xscj數(shù)據(jù)庫(kù)：USExscj;CREATETABLExs1LIKExs; #復(fù)制xs表創(chuàng)建xs1表結(jié)構(gòu)CREATETABLExs2AS(SELECT學(xué)號(hào),姓名,專(zhuān)業(yè),總學(xué)分FROMxs); #復(fù)制xs表部分列創(chuàng)建xs2表

SHOWTABLES; #（a）DESCRIBExs2; #（b）顯示結(jié)果如圖。

02修

改

表增加修改刪除列增加刪除列、索引和完整性約束修改表選項(xiàng)修

改

表ALTERTABLE用于修改原有表的結(jié)構(gòu)。例如，可以增加（刪減）列、創(chuàng)建（取消）索引、更改原有列的類(lèi)型、重命名列或表，還可以更改表的注釋和表的類(lèi)型。修改表語(yǔ)句如下：ALTER[IGNORE]TABLE表名[ADD列定義] /*增加列*/[DROP列名] /*刪除列*/[MODIFY列名列屬性] /*修改列屬性*/[ALTER列名SETDEFAULT值|DROPDEFAULT] /*設(shè)置默認(rèn)值和刪除默認(rèn)值*/[RENAME表名 /*表更名*/[CHANGE原列名新列定義修改...] /*修改列名同時(shí)修改列屬性*/[ADD主鍵|索引|完整性約束] /*增加表索引和完整性約束*/[DROP列名|索引名|主鍵|完整性約束名] /*刪除表列、索引、主鍵和完整性約束*/[ORDERBY列名,...] /*列排序*/[表選項(xiàng)] /*增加修改表屬性*/修

改

表1．增加修改刪除列下面介紹增加修改刪除列描述形式。（1）增加列ADD[COLUMN]列定義[FIRST|AFTER列名]列定義參考CREATETABLE語(yǔ)句。FIRST：指定增加列為第1列。AFTER列名：增加在指定列后面。（2）修改和刪除指定列的默認(rèn)值A(chǔ)LTER[COLUMN]SETDEFAULT值|DROPDEFAULT（3）修改列的名稱(chēng)和定義CHANGE舊列名列定義[FIRST|AFTER列名]（4）修改列屬性MODIFY[COLUMN]列名列屬性（5）刪除列DROP列名修

改

表【例】修改xs2表結(jié)構(gòu)。USExscj;ALTERTABLExs2 ADDCOLUMN考評(píng)tinyintNULL;ALTERTABLExs2 CHANGE考評(píng)考評(píng)分tinyint;ALTERTABLExs2 DROP考評(píng)分;ALTERTABLExs2 MODIFY專(zhuān)業(yè)char(12)NOTNULL;DESCRIBExs2;說(shuō)明：第1句：在xs2表中增加新的一列“考評(píng)”。第2句：把xs2表“考評(píng)”列名變更為“考評(píng)分”。第3句：把xs2表的“考評(píng)分”列刪除。第4名：把xs2表“專(zhuān)業(yè)”列數(shù)據(jù)類(lèi)型改為char(12)。（6）指定記錄排序列ORDERBY列名,...用于在創(chuàng)建新表時(shí)，讓各行（記錄）按一定的順序排列。修

改

表2．增加刪除列、索引和完整性約束下面介紹增加刪除列、列索引和完整性約束的描述形式。（1）增加列、列索引和完整性約束ADD{INDEX|KEY}索引名索引定義|ADDPRIMARYKEY主鍵定義|ADDUNIQUE唯一性鍵名唯一性定義|ADDFOREIGNKEY外鍵名外鍵定義|ADDCHECK(完整性約束條件)【例】在xscj數(shù)據(jù)庫(kù)的xs2表中，增加主鍵和生成列（專(zhuān)業(yè)編號(hào)）。USExscj;ALTERTABLExs2 ADD專(zhuān)業(yè)編號(hào)char(2)GENERATEDALWAYSAS(SUBSTRING(學(xué)號(hào),3,2)), ADDPRIMARYKEY(學(xué)號(hào));DESCRIBExs2;修

改

表說(shuō)明：①xs2表采用“CREATE…ASSELECT…”方式創(chuàng)建，雖然原xs表包含“學(xué)號(hào)”列主鍵，但xs2表沒(méi)有主鍵。這里給xs2表增加“學(xué)號(hào)”列主鍵。②“專(zhuān)業(yè)編號(hào)”列為char(2)數(shù)據(jù)類(lèi)型，它由學(xué)號(hào)列的第3、4位生成，是虛擬的列。執(zhí)行后，xs2表的結(jié)構(gòu)如圖。修

改

表（2）刪除列、列索引、主鍵和完整性約束DROP[COLUMN]列名|DROP{INDEX|KEY}索引名|DROPPRIMARYKEY|DROPFOREIGNKEY外鍵約束名|CHECK完整性約束名（3）修改表索引名和表名RENAME{INDEX|KEY}原索引名TO新索引名3．修改表選項(xiàng)具體定義與CREATETABLE語(yǔ)句一樣。03表刪除和更名更改表名表刪除表刪除和更名1．更改表名除了上面的ALTERTABLE命令用“RENAME新表名”修改表名，還可以直接用下列語(yǔ)句來(lái)更改表的名字。RENAMETABLE

原表名TO新表名,...2．表刪除當(dāng)需要?jiǎng)h除一個(gè)表時(shí)可以使用下列語(yǔ)句。DROP[TEMPORARY]TABLE[IFEXISTS]表名,...說(shuō)明：這個(gè)命令將表的描述、完整性約束、索引及與表相關(guān)的權(quán)限等一并刪除。第3章

數(shù)據(jù)庫(kù)和表——表記錄的操作01插

入

記

錄插入新記錄插入圖片用已有表記錄插入當(dāng)前表記錄替換舊記錄系統(tǒng)模式插入記錄1．插入新記錄向表中插入全新的記錄用下列語(yǔ)句。INSERT[選項(xiàng)][INTO]表名[(列名,...)]VALUES({表達(dá)式|DEFAULT},...),...或者INSERT[選項(xiàng)][INTO]表名[(列名,...)]|SET列名={表達(dá)式|DEFAULT},...[ONDUPLICATEKEYUPDATE列名=表達(dá)式,...]插入記錄說(shuō)明：（1）INTO子句：如果只給表的部分列插入數(shù)據(jù)，需要指定這些列。若沒(méi)有指定列，表示對(duì)所有列插入數(shù)據(jù)，值的順序與表結(jié)構(gòu)定義的順序相同。（2）

VALUES子句：包含各列需要插入的數(shù)據(jù)清單，數(shù)據(jù)的順序要與列的順序相對(duì)應(yīng)。若表名后不給出列名，則要在VALUES子句中給出每一列（除IDENTITY和timestamp類(lèi)型的列）的值，如果列值為空，則值必須為NULL，否則會(huì)出錯(cuò)。（3）

選項(xiàng)：LOW_PRIORITY：可以使用在INSERT、DELETE和UPDATE等操作中，當(dāng)原有客戶(hù)端正在讀取數(shù)據(jù)時(shí)，延遲操作的執(zhí)行，直到?jīng)]有其他客戶(hù)端從表中讀取數(shù)據(jù)為止。DELAYED：若使用此關(guān)鍵字，則服務(wù)器會(huì)把待插入的行放到一個(gè)緩沖器中，而發(fā)送INSERTDELAYED語(yǔ)句的客戶(hù)端會(huì)繼續(xù)運(yùn)行。HIGH_PRIORITY：可以使用在SELECT和INSERT操作中，使操作優(yōu)先執(zhí)行。IGNORE：使用此關(guān)鍵字，在執(zhí)行語(yǔ)句時(shí)出現(xiàn)的錯(cuò)誤就會(huì)被當(dāng)作警告處理。ONDUPLICATEKEYUPDATE…：使用此選項(xiàng)插入行后，若導(dǎo)致UNIQUEKEY或PRIMARYKEY出現(xiàn)重復(fù)值，則根據(jù)UPDATE后的語(yǔ)句修改舊行（使用此選項(xiàng)時(shí)DELAYED被忽略）。（4）

SET子句：SET子句用于給列指定值，使用SET子句時(shí)表名的后面省略列名。要插入數(shù)據(jù)的列名在SET子句中指定，列名等號(hào)后面為指定數(shù)據(jù)，未指定的列，其值為默認(rèn)值。插入記錄【例】向xscj數(shù)據(jù)庫(kù)的xs表（表中列包括學(xué)號(hào)、姓名、專(zhuān)業(yè)、性別、出生日期、總學(xué)分、照片、備注）中插入如下一行記錄：221101，王林，計(jì)算機(jī)，1，2004-02-10，15使用下列語(yǔ)句插入記錄：USExscj;INSERTINTOxsVALUES('221101','王林','計(jì)算機(jī)',1,'2004-02-10',15,NULL,NULL);若xs表中性別采用默認(rèn)值，照片和備注為NULL，插入記錄：INSERTINTOxs(學(xué)號(hào),姓名,專(zhuān)業(yè),出生日期,總學(xué)分)VALUES('221104','韋嚴(yán)平','計(jì)算機(jī)','2004-08-26',12);使用SET子句插入記錄：INSERTINTOxsSET學(xué)號(hào)='221201',姓名='劉華',專(zhuān)業(yè)='通信工程',性別=DEFAULT,出生日期='2004-06-10',總學(xué)分=13;插入記錄2．插入圖片MySQL還支持圖片的插入，圖片一般可以以路徑的形式來(lái)存儲(chǔ)，即插入圖片時(shí)可以采用插入圖片的存儲(chǔ)路徑的方式。【例】向xs表中插入一行記錄：221102，程明，計(jì)算機(jī)，1，2005-02-01，15，照片E:\mysql5\data\chenmin.jpg（1）照片列保存文件名INSERTINTOxsVALUES('221102','程明','計(jì)算機(jī)',1,'2005-02-01',15,NULL,'E:\mysql5\data\chenmin.jpg');SELECT*FROMxs;（2）照片列直接存儲(chǔ)圖片本身INSERTINTOxsVALUES('221102','程明','計(jì)算機(jī)',1,'2005-02-01',15,NULL,LOAD_FILE('E:\mysql5\data\chenmin.jpg'));執(zhí)行結(jié)果如圖。插入記錄3．用已有表記錄插入當(dāng)前表記錄下列語(yǔ)句可以從已有表中查詢(xún)記錄插入指定表：INSERT[選項(xiàng)][INTO]表名[(列名,...)]SELECT語(yǔ)句|LIKE[ONDUPLICATEKEYUPDATE列名=表達(dá)式,...]說(shuō)明：（1）SELECT語(yǔ)句中返回的是一個(gè)查詢(xún)到的結(jié)果集，INSERT語(yǔ)句將這個(gè)結(jié)果集插入指定表中，但結(jié)果集中每行數(shù)據(jù)的列數(shù)、列的數(shù)據(jù)類(lèi)型要與被操作的表完全一致。（2）若當(dāng)前表結(jié)構(gòu)中存在主鍵或唯一性列，而插入的數(shù)據(jù)行中含有與原有行中相同的列值，則INSERT語(yǔ)句無(wú)法插入此行。如果希望替換原來(lái)記錄，需要使用REPLACE語(yǔ)句。【例】向xs1表中插入xs表中的所有記錄。USExscj;DROPTABLEIFEXISTSxs1; #刪除xs1表CREATETABLExs1LIKExs; #創(chuàng)建xs1表結(jié)構(gòu)INSERTINTOxs1 SELECT*FROMxs; #插入xs表所有記錄到xs1表INSERTINTOxs2(學(xué)號(hào),姓名,專(zhuān)業(yè),總學(xué)分) SELECT學(xué)號(hào),姓名,專(zhuān)業(yè),總學(xué)分FROMxs; #插入xs表所有記錄部分列到xs2表插入記錄4．替換舊記錄REPLACE語(yǔ)句與INSERT語(yǔ)句基本相同。如果存在相同的記錄，則REPLACE語(yǔ)句可以先刪除舊記錄，再插入新記錄。相當(dāng)于替換舊記錄?！纠吭趚s1表替換下列記錄：081211，劉華，通信工程，1，1995-03-08，48，輔修計(jì)算機(jī)專(zhuān)業(yè)，空因?yàn)槿糁苯邮褂肐NSERT語(yǔ)句，則會(huì)產(chǎn)生如下錯(cuò)誤。使用REPLACE語(yǔ)句，則可以成功替換原來(lái)記錄：USExscj;REPLACEINTOxs1VALUES('221201','劉華','通信工程',1,'2004-06-10',13,'輔修計(jì)算機(jī)',NULL);SELECT*FROMxs1;說(shuō)明：因?yàn)閤s1表包含（學(xué)號(hào)列）主鍵，由于學(xué)號(hào)為“221201”記錄已經(jīng)存在，上述語(yǔ)句替換原來(lái)記錄。插入記錄5．系統(tǒng)模式在系統(tǒng)寬松模式（set@@sql_mode=''）下，數(shù)據(jù)庫(kù)表數(shù)據(jù)輸入不正確也不會(huì)報(bào)告錯(cuò)誤，而且還會(huì)保存到表中，例如：向char(10)類(lèi)型列輸入超過(guò)10個(gè)字符、向日期類(lèi)型列輸入'2000-00-09'，插入和修改表內(nèi)容的值為被0除的表達(dá)式。如果修改系統(tǒng)為嚴(yán)格模式（set

@@sql_mode=TRADITIONAL|…組合使用各種設(shè)置項(xiàng)），出現(xiàn)上述問(wèn)題，系統(tǒng)就會(huì)顯示錯(cuò)誤信息，而且不會(huì)將數(shù)據(jù)加入數(shù)據(jù)庫(kù)表中。02修

改

記

錄修改單個(gè)表修改多個(gè)表修改記錄修改表（單表或者多表）中的記錄時(shí)可使用下列語(yǔ)句。UPDATE[選項(xiàng)]表名,... SET列名=表達(dá)式,...] [WHERE條件] [ORDERBY...] [LIMIT行數(shù)]SET

子句：用表達(dá)式修改列名對(duì)應(yīng)的列（數(shù)據(jù)類(lèi)型需要相同）?？砂鄠€(gè)項(xiàng)，中間用逗號(hào)隔開(kāi)，同時(shí)修改所在數(shù)據(jù)行的多個(gè)列值。WHERE子句：指定對(duì)符合條件的數(shù)據(jù)行進(jìn)行修改，否則更新所有行。ORDERBY子句：指定修改記錄行的順序，但與LIMIT子句聯(lián)用時(shí)才起作用。LIMIT子句：指定被修改行的最大值。修改記錄1．修改單個(gè)表【例】將xs1表中的所有學(xué)生的總學(xué)分都增加1。將姓名為“劉華”的學(xué)生的學(xué)號(hào)修改為“221200”備注改為“輔修計(jì)算機(jī)專(zhuān)業(yè)”。USExscj;UPDATExs1SET總學(xué)分=總學(xué)分+1;UPDATExs1SET學(xué)號(hào)='221200',備注='輔修計(jì)算機(jī)專(zhuān)業(yè)'WHERE姓名='劉華';xs1表中所有學(xué)生的總學(xué)分都增加了1；姓名為“劉華”的學(xué)生學(xué)號(hào)修改為“221200”，備注改為“輔修計(jì)算機(jī)專(zhuān)業(yè)”。修改記錄2．修改多個(gè)表【例】將xs1表和xs2表中所有學(xué)生的總學(xué)分都加4。USExscj;UPDATExs1,xs2SETxs2.總學(xué)分=xs2.總學(xué)分+4,xs1.總學(xué)分=xs1.總學(xué)分+4WHERExs2.學(xué)號(hào)=xs1.學(xué)號(hào);SELECT學(xué)號(hào),姓名,總學(xué)分FROMxs1; #（a）SELECT學(xué)號(hào),姓名,總學(xué)分FROMxs2; #（b）其中，WHERE包含xs1和xs2兩個(gè)表的連接條件，命令執(zhí)行后xs1表和xs2表記錄如圖。

03刪

除

記

錄刪除表符合條件的記錄快速清除表所有記錄刪除記錄1．刪除表符合條件的記錄DELETE[選項(xiàng)]FROM表名 [WHERE條件] [ORDERBY...] [LIMIT行數(shù)]FROM子句：要?jiǎng)h除數(shù)據(jù)的表名。WHERE子句：指定的刪除條件。如果省略WHERE子句則刪除該表的所有行。ORDERBY子句：指定刪除的順序，此子句只在與LIMIT子句聯(lián)用時(shí)才起作用。LIMIT子句：指定被刪除行的最大值。選項(xiàng)：指定刪除記錄參數(shù)，可參考有關(guān)文檔。刪除記錄【例】刪除xs2表中姓名為劉華的記錄?？墒褂萌缦抡Z(yǔ)句：USExscj;DELETEFROMxs2WHERE姓名='劉華';SELECT*FROMxs2;查詢(xún)結(jié)果如圖。也可以一次刪除多個(gè)表記錄：DELETE[選項(xiàng)]表名[.*],...FROM表名1,...][WHERE條件]刪除記錄2．快速清除表所有記錄使用TRUNCATETABLE語(yǔ)句：TRUNCATETABLE表名說(shuō)明：（1）該語(yǔ)句將刪除指定表中的所有數(shù)據(jù)，也稱(chēng)其為清除表數(shù)據(jù)語(yǔ)句。使用時(shí)必須十分小心?。?）雖然不帶WHERE子句的DELETE語(yǔ)句也能刪除表中的全部行，但TRUNCATETABLE比DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少。（3）對(duì)于參與了索引和視圖的表，不能使用TRUNCATETABLE刪除數(shù)據(jù)，而應(yīng)使用DELETE語(yǔ)句?！纠?.14】刪除xs2表中所有記錄。USExscj;TRUNCATETABLExs2;SELECT*FROMxs2;DROPTABLExs2;SELECT*FROMxs2;第3章

數(shù)據(jù)庫(kù)和表——表操作綜合01準(zhǔn)備系統(tǒng)查詢(xún)需要表完善學(xué)生（xs）表樣本記錄創(chuàng)建課程表（kc）結(jié)構(gòu)和加入樣本記錄創(chuàng)建成績(jī)表（cj）結(jié)構(gòu)和加入樣本記錄準(zhǔn)備系統(tǒng)查詢(xún)需要表【例】創(chuàng)建學(xué)生成績(jī)數(shù)據(jù)庫(kù)（xscj）表結(jié)構(gòu)和表記錄。1．完善學(xué)生（xs）表樣本記錄學(xué)生（xs）表結(jié)構(gòu)已經(jīng)創(chuàng)建，同時(shí)已經(jīng)加入了部分樣本記錄。這里參考附錄A，加入其他記錄。2．創(chuàng)建課程表（kc）結(jié)構(gòu)和加入樣本記錄創(chuàng)建課程表（kc）結(jié)構(gòu)：USExscj;CREATETABLEkc(

課程號(hào) char(3) NOTNULLPRIMARYKEY,

課程名 varchar(8) NOTNULL,

開(kāi)課學(xué)期 tinyint NOTNULL,

學(xué)時(shí) tinyint NOTNULL,

學(xué)分 tinyint NOTNULL);準(zhǔn)備系統(tǒng)查詢(xún)需要表3．創(chuàng)建成績(jī)表（cj）結(jié)構(gòu)和加入樣本記錄創(chuàng)建成績(jī)表（cj）結(jié)構(gòu)：CREATETABLEcj(

學(xué)號(hào) char(6) NOTNULL,

課程號(hào) char(3) NOTNULL,

成績(jī) tinyint NULL, PRIMARYKEY(學(xué)號(hào),課程號(hào)));02非基本數(shù)據(jù)類(lèi)型表操作創(chuàng)建學(xué)生擴(kuò)展表（xsk）結(jié)構(gòu)插入學(xué)生擴(kuò)展表（xsk）記錄修改學(xué)生（xsk）擴(kuò)展表記錄非基本數(shù)據(jù)類(lèi)型表操作【例】在xscj數(shù)據(jù)庫(kù)加入學(xué)生擴(kuò)展表（xsk）。1．創(chuàng)建學(xué)生擴(kuò)展表（xsk）結(jié)構(gòu)參考附錄A，在xscj數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)學(xué)生擴(kuò)展表結(jié)構(gòu)，表名為xsk。USExscj;CREATETABLExsk(

學(xué)號(hào) `char(6)NOTNULLPRIMARYKEY,

愛(ài)好 set('書(shū)法','繪畫(huà)','音樂(lè)','運(yùn)動(dòng)')NULL,

畢業(yè)去向 enum('直接就業(yè)','考研','考公務(wù)員','出國(guó)留學(xué)','創(chuàng)業(yè)'),

家庭地址 json,

地理位置 geometry);說(shuō)明：（1）“愛(ài)好”列，set（集合）數(shù)據(jù)類(lèi)型，可實(shí)現(xiàn)多選。（2）“畢業(yè)去向”列，enum（枚舉）數(shù)據(jù)類(lèi)型，可實(shí)現(xiàn)單選。（3）“家庭地址”列，json數(shù)據(jù)類(lèi)型，可實(shí)現(xiàn)用簡(jiǎn)化XML格式描述省、市、區(qū)、街道等規(guī)范信息。（4）“地理位置”列，geometry數(shù)據(jù)類(lèi)型，可描述家庭位置。非基本數(shù)據(jù)類(lèi)型表操作2．插入學(xué)生擴(kuò)展表（xsk）記錄參考附錄A，往學(xué)生擴(kuò)展表xsk中插入記錄。USExscj;INSERTINTOxsk (學(xué)號(hào),愛(ài)好,畢業(yè)去向,家庭地址,地理位置) VALUES ( '201101','書(shū)法,繪畫(huà)','考研', '{"省":"江蘇","市":"南京","區(qū)縣":"棲霞","街道":"仙林智谷","電話(huà)":}', ST_GeomFromText('POINT(118.91200032.096790)') );INSERTINTOxsk (學(xué)號(hào),愛(ài)好,畢業(yè)去向,家庭地址,地理位置) VALUES ( '201103','書(shū)法','直接就業(yè)', '{"省":"山東","市":"威海","區(qū)縣":"龍城","街道":"成山大道102號(hào)","電話(huà)":}', ST_GeomFromText('POINT(122.25362100137.103460)') );非基本數(shù)據(jù)類(lèi)型表操作INSERTINTOxsk (學(xué)號(hào),愛(ài)好,畢業(yè)去向,家庭地址,地理位置) VALUES ( '201203','音樂(lè),運(yùn)動(dòng)','直接就業(yè)',NULL,NULL);INSERTINTOxsk (學(xué)號(hào),愛(ài)好,畢業(yè)去向,家庭地址,地理位置) VALUES ( '201205','繪畫(huà)','創(chuàng)業(yè)', '{"省":"江蘇","市":"南京","區(qū)縣":"浦口","電話(huà)":,"街道":"沿江鎮(zhèn)學(xué)府路8號(hào)"}',NULL );SELECT*FROMxsk;查詢(xún)結(jié)果如圖。非基本數(shù)據(jù)類(lèi)型表操作3．修改學(xué)生（xsk）擴(kuò)展表記錄（1）修改表家庭地址（json類(lèi)型）列數(shù)據(jù)USExscj;SELECT*FROMxskWHERE家庭地址ISNULL; #顯示家庭地址為NULL的記錄UPDATExsk SET家庭地址=JSON_OBJECT("省","黑龍江","市","大慶","區(qū)縣","高新","街道","學(xué)府街99號(hào)") WHERE學(xué)號(hào)='201203';UPDATExsk SET家庭地址=JSON_INSERT(家庭地址,'$."收件人"',"歐陽(yáng)紅",'$."電話(huà)"',"1538099366X") WHERE學(xué)號(hào)='201203';UPDATExsk SET家庭地址=JSON_REMOVE(家庭地址,'$."電話(huà)"') WHERE學(xué)號(hào)='201203'; 非基本數(shù)據(jù)類(lèi)型表操作（2）修改表地理位置（geometry類(lèi)型）列數(shù)據(jù)UPDATExsk SET地理位置=ST_GeomFromText('POINT(125.14140346.588425)') WHERE學(xué)號(hào)='201203';SELECT*FROMxsk;查詢(xún)結(jié)果如圖。第3章

數(shù)據(jù)庫(kù)和表——表

選

項(xiàng)01存

儲(chǔ)

引

擎MyISAM存儲(chǔ)引擎InnoDB存儲(chǔ)引擎CSV存儲(chǔ)引擎Memory存儲(chǔ)引擎Merge存儲(chǔ)引擎Cluster/NDB存儲(chǔ)引擎存儲(chǔ)引擎MySQL支持很多存儲(chǔ)引擎，包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、ARCHIVE、NDBCluster等，其中InnoDB和BDB支持事務(wù)安全。下列語(yǔ)句査看系統(tǒng)所支持的存儲(chǔ)引擎：SHOWENGINES;或者SELECT*FROMINFORMATION_SCHEMA.ENGINES;1．MyISAM存儲(chǔ)引擎每個(gè)MyISAM在磁盤(pán)上存儲(chǔ)為3個(gè)文件，文件名和表名相同，擴(kuò)展名frm存儲(chǔ)表定義，myd存儲(chǔ)數(shù)據(jù)，myi存儲(chǔ)索引。在創(chuàng)建表的時(shí)候通過(guò)DATADIRECTORY和INDEXDIRECTORY屬性來(lái)指定數(shù)據(jù)文件和索引文件的存儲(chǔ)路徑，這樣可平均分布IO，加快訪(fǎng)問(wèn)速度。支持3種不同的存儲(chǔ)格式：（1）靜態(tài)表（fixed）：默認(rèn)的存儲(chǔ)格式。靜態(tài)表中的字段都是非變長(zhǎng)字段，每個(gè)記錄都是固定的長(zhǎng)度，當(dāng)表不包含變長(zhǎng)列（例如varchar、text、blob）時(shí)，使用這個(gè)格式。（2）動(dòng)態(tài)表（dynamic）：包含變長(zhǎng)列或者該表創(chuàng)建時(shí)用ROW_FORMAT=dynamic指定，則該表使用動(dòng)態(tài)格式存儲(chǔ)。它占用空間小，但頻繁的更新和刪除操作會(huì)產(chǎn)生碎片，需要定期用OPTIMIZETABLE（優(yōu)化）語(yǔ)句或myisamchk-r命令來(lái)改善性能，并且在出現(xiàn)故障后較難恢復(fù)。（3）壓縮表：由myisampack工具創(chuàng)建，占據(jù)磁盤(pán)空間較小，因?yàn)槊總€(gè)記錄都是被單獨(dú)壓縮的。存儲(chǔ)引擎2．InnoDB存儲(chǔ)引擎MySQL5.5之后的默認(rèn)存儲(chǔ)引擎，支持事務(wù)和外鍵。如果應(yīng)用對(duì)事務(wù)的完整性有較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作中包含讀、插入、刪除和更新，InnoDB是最好的選擇。但相比較于MyISAM，寫(xiě)的處理效率差一點(diǎn)，并且會(huì)占用更多的磁盤(pán)空間來(lái)存儲(chǔ)數(shù)據(jù)和索引。特點(diǎn)：（1）自動(dòng)增長(zhǎng)列必須是索引，如果是組合索引，也必須是其第一列。而MyISAM表的自動(dòng)增長(zhǎng)列可以是組合索引的其他列。（2）支持外鍵約束。這樣當(dāng)某個(gè)表被其它表創(chuàng)建了外鍵參照，那么該表對(duì)應(yīng)的索引和主鍵禁止被刪除。（3）存儲(chǔ)數(shù)據(jù)和索引有共享表空間和獨(dú)立表空間兩種存儲(chǔ)方式，通過(guò)參數(shù)innodb_file_per_table控制，0（或OFF）表示共享表空間（也是默認(rèn)的），1（或ON）表示獨(dú)立表空間。表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在idb文件中。存儲(chǔ)引擎3．CSV存儲(chǔ)引擎該存儲(chǔ)引擎表在MySQL安裝目錄“Data\數(shù)據(jù)庫(kù)名”子目錄中生成一個(gè).CSV文件。它是一種普通文本文件，每個(gè)記錄占用一個(gè)文本行，各列數(shù)據(jù)由逗號(hào)分隔。但不支持索引，即表沒(méi)有主鍵列，不允許表中的字段為空?！纠緾SV存儲(chǔ)引擎表測(cè)試。（1）創(chuàng)建CSV存儲(chǔ)引擎表，插入記錄：CREATEDATABASEtest;USEtest;CREATETABLEexcelb( id intNOTNULL, name varchar(11)NOTNULL, salary decimal(8,2)NOTNULL)ENGINE=CSV;INSERTINTOexcelbVALUES (1,'A',1.45), (2,'A',0.99);SELECT*FROMexcelb;顯示查詢(xún)結(jié)果如圖。存儲(chǔ)引擎（2）用Excel打開(kāi)MySQL安裝目錄下Data子目錄test數(shù)據(jù)庫(kù)子目錄中（C:\ProgramData\MySQL\MySQLServer5.7\Data\test）的excelb.csv文件，可看到上面INSERT語(yǔ)句插入的兩條記錄。如圖。存儲(chǔ)引擎4．Memory存儲(chǔ)引擎該存儲(chǔ)引擎通過(guò)在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)存儲(chǔ)數(shù)據(jù)。每個(gè)表對(duì)應(yīng)一個(gè)只存儲(chǔ)表結(jié)構(gòu)的磁盤(pán)文件，該文件的文件名和表名是相同的，類(lèi)型為.frm。由于它的數(shù)據(jù)是存放在內(nèi)存中的，并且默認(rèn)使用HASH索引，所以訪(fǎng)問(wèn)速度特別快，但同時(shí)也造成了缺點(diǎn)，就是數(shù)據(jù)庫(kù)服務(wù)一旦關(guān)閉，數(shù)據(jù)就會(huì)丟失，另外對(duì)表的大小有限制。每個(gè)表中可存儲(chǔ)數(shù)據(jù)量的大小受到max_heap_table_size變量的約束，初始值是16MB，可以在定義表的時(shí)候通過(guò)max_rows指定表的最大行數(shù)。MEMORY的主要特性如下：（1）每個(gè)表可以有多達(dá)32個(gè)索引、每個(gè)索引16列以及最大鍵長(zhǎng)度500字節(jié)。在表中可以有非唯一鍵；對(duì)可包含NULL值的列索引；可執(zhí)行HASH和BTREE索引。（2）表使用一個(gè)固定的記錄長(zhǎng)度格式。（3）支持AUTOINCREMENT列，不支持BLOB或TEXT列。（4）表在所有客戶(hù)端之間共享。存儲(chǔ)引擎【例】Memory存儲(chǔ)引擎測(cè)試。（1）創(chuàng)建memory存儲(chǔ)引擎表，插入記錄：USEtest;CREATETABLEmemoryb( id intNOTNULL, name varchar(11)NOTNULL, salary decimal(8,2)NOTNULL)ENGINE=memory;INSERTINTOmemorybVALUES (1,'A',1.45), (2,'A',0.99);SELECT*FROMmemoryb;顯示查詢(xún)結(jié)果如圖。存儲(chǔ)引擎（2）停止MySQL服務(wù)，然后重新啟動(dòng)，查詢(xún)memoryb記錄。USEtest;SELECT*FROMmemoryb;顯示查詢(xún)結(jié)果如圖。存儲(chǔ)引擎Merge表在磁盤(pán)上保留兩個(gè)文件，.frm文件存儲(chǔ)表的定義，.mrg文件存儲(chǔ)組合表的信息。【例】Merge存儲(chǔ)引擎測(cè)試。（1）創(chuàng)建表指定存儲(chǔ)引擎。USEtest;DROPTABLEIFEXISTSmerge1,merge2,mergeg; CREATETABLEmerge1( id int, name varchar(11), salary decimal(8,2))ENGINE=MYISAM;CREATETABLEmerge2LIKEmerge1;CREATETABLEmergeg( id int, name varchar(11), salary decimal(8,2))ENGINE=MERGEUNION=(merge1,merge2)INSERT_METHOD=LAST;存儲(chǔ)引擎（2）向表中插入記錄。USEtest;INSERTINTOmerge1VALUES (1,'A',1.45), (2,'A',0.99);INSERTINTOmerge2VALUES (3,'B',2.10), (4,'B',4.29);INSERTINTOmergegVALUES (5,'B',3.10), (6,'B',4.36);SELECT*FROMmerge1; #（a）SELECT*FROMmerge2; #（b）SELECT*FROMmergeg; #（c）顯示merge1和merge2表中所有記錄存儲(chǔ)引擎運(yùn)行結(jié)果如圖。

（3）MERGE表刪除記錄。USEtest;DELETEFROMmergeg;SELECT*FROMmerge1;SELECT*FROMmerge2;存儲(chǔ)引擎6．Cluster/NDB存儲(chǔ)引擎所謂“集群”是一種被廣泛使用的分布式數(shù)據(jù)庫(kù)系統(tǒng)，由眾多網(wǎng)絡(luò)數(shù)據(jù)庫(kù)NDB節(jié)點(diǎn)計(jì)算機(jī)組成一個(gè)群體，每個(gè)NDB上都存有完整的數(shù)據(jù)庫(kù)副本；集群中有一臺(tái)管理它的主機(jī)，可為整個(gè)集群配置NDB節(jié)點(diǎn)和監(jiān)控各節(jié)點(diǎn)的狀態(tài)；外部用戶(hù)或應(yīng)用程序則通過(guò)SQL節(jié)點(diǎn)來(lái)訪(fǎng)問(wèn)集群數(shù)據(jù)，一個(gè)典型的集群系統(tǒng)的架構(gòu)原理如圖。02表

空

間表空間類(lèi)型表空間創(chuàng)建和使用表空間中表的移動(dòng)刪除表空間表

空

間1．表空間類(lèi)型（1）系統(tǒng)表空間系統(tǒng)表空間是由InnoDB引擎管理的一個(gè)特殊的共享表空間。默認(rèn)情況下，用戶(hù)創(chuàng)建的表存放在系統(tǒng)表空間中，文件存放在MySQL默認(rèn)的目錄，采用默認(rèn)的文件名。但用戶(hù)可以在配置文件中通過(guò)下列參數(shù)進(jìn)行配置。innodb_data_file_path：設(shè)定表空間大小及文件。例如：innodb_data_file_path=ibdata1:50M; ... ibdata2:50M:autoextend[:max:空間大小]其中，autoextend表示自動(dòng)擴(kuò)展（默認(rèn)每次擴(kuò)展64M），max為最大文件大小，只能在最后一個(gè)文件上指定。默認(rèn)值為ibdata1:12M:autoextend。innodb_data_home_dir：設(shè)定表空間的存放位置格式為：innodb_data_home_dir=/文件路徑表

空

間（2）通用表空間通用表空間是用來(lái)存放用戶(hù)創(chuàng)建的表數(shù)據(jù)及索引的一個(gè)共享表空間，可指定多個(gè)表存放在同一通用表空間內(nèi)，表空間文件的存放路徑是用戶(hù)創(chuàng)建時(shí)指定的絕對(duì)路徑，否則將存放在數(shù)據(jù)庫(kù)默認(rèn)路徑下。通用表空間是用戶(hù)創(chuàng)建和命名的，用名稱(chēng)引用。（3）臨時(shí)表空間臨時(shí)表空間用于暫存MySQL中的臨時(shí)表，通過(guò)innodb_temp_data_file_path參數(shù)配置表空間臨時(shí)數(shù)據(jù)文件的相對(duì)路徑、名稱(chēng)、大小和屬性。臨時(shí)表空間在每次啟動(dòng)MySQL服務(wù)器時(shí)創(chuàng)建，在正常關(guān)閉時(shí)將被刪除，但服務(wù)器意外停止時(shí)則不會(huì)刪除，這種情況下需要數(shù)據(jù)庫(kù)管理員手動(dòng)刪除臨時(shí)表空間或重新啟動(dòng)MySQL服務(wù)器。表

空

間（4）日志表空間MySQL日志表空間包括重做日志表空間（REDO表空間）和撤銷(xiāo)日志表空間（UNDO表空間）。REDO表空間用于在數(shù)據(jù)庫(kù)崩潰后進(jìn)行數(shù)據(jù)恢復(fù)，保證數(shù)據(jù)完整性，表空間位于數(shù)據(jù)庫(kù)默認(rèn)路徑下的ib_logfile0、ib_logfile1等文件中。UNDO表空間用于事務(wù)回滾和多版本控制（MVCC），位于數(shù)據(jù)庫(kù)默認(rèn)路徑下的ibdata1文件中。（5）獨(dú)立表空間獨(dú)立表空間每一個(gè)表對(duì)應(yīng)一個(gè).ibd文件存儲(chǔ)表的數(shù)據(jù)內(nèi)容以及索引。該文件可以在不同的數(shù)據(jù)庫(kù)中移動(dòng)?！癉ROPTABLE表名”操作自動(dòng)回收表空間，刪除大量數(shù)據(jù)后通過(guò)“ALTERTABLE表名ENGINE=INNODB”和“TURNCATETABLE表名”回縮不用的空間。表

空

間2．表空間創(chuàng)建和使用通過(guò)下列語(yǔ)句創(chuàng)建表空間：CREATE[UNDO]TABLESPACE表空間名 ADDDATAFILE文件名;其中，帶UNDO指明創(chuàng)建UNDO日志表空間，否則創(chuàng)建的是通用表空間；ADDDATAFILE后的“文件名”是對(duì)應(yīng)表空間文件的名稱(chēng)。【例】通用表空間的創(chuàng)建和使用。（1）創(chuàng)建通用表空間。CREATETABLESPACEmyGSpace ADDDATAFILE'myGSpace.ibd’ Engine=InnoDB;此時(shí)，在MySQL的數(shù)據(jù)目錄（…\Data）下可找到該表空間對(duì)應(yīng)的數(shù)據(jù)文件myGSpace.ibd，如圖。表

空

間（2）在創(chuàng)建表結(jié)構(gòu)時(shí)指定表空間。USExscj;DROPTABLEIFEXISTSxsb;CREATETABLExsb(學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,姓名 char(4) NOTNULL,專(zhuān)業(yè) char(10) NULL,性別 tinyint(1) NOTNULLDEFAULT1,出生日期 date NOTNULL,總學(xué)分 tinyint(1) NULL,備注 text NULL,照片 blob NULL)TABLESPACEmyGSpace;INSERTINTOxsbSELECT*FROMxs;說(shuō)明：雖然在xscj數(shù)據(jù)庫(kù)上創(chuàng)建xsb表，但由于指定了表空間，實(shí)際創(chuàng)建的表存儲(chǔ)在（…\Data）下的myGSpace.ibd文件中，而xscj數(shù)據(jù)庫(kù)目錄中只有xsb.frm文件而沒(méi)有xsb.ibd文件。表

空

間（3）在修改表結(jié)構(gòu)時(shí)指定表空間。USExscj;DROPTABLEIFEXISTScjb;CREATETABLEcjbASSELECT*FROMcj;ALTERTABLEcjbTABLESPACEmyGSpace;（4）查看通用表空間信息。SELECTNAME,FLAG FROMinformation_schema.INNODB_SYS_TABLESWHERESPACE_TYPE='General';查詢(xún)結(jié)果如圖。說(shuō)明：“General”表示表空間類(lèi)型是通用表空間，表采用的表空間的類(lèi)型信息只能從information_schema系統(tǒng)庫(kù)的INNODB_TABLES表中查到。表

空

間【例】獨(dú)立表空間的創(chuàng)建和使用。SETGLOBALinnodb_file_per_table=ON;DROPTABLEIFEXISTSkcb;CREATETABLEkcbASSELECT*FROMkc;SELECTNAME,SPACE_TYPEFROMinformation_schema.INNODB_SYS_TABLES WHERENAME='xscj/kcb';查詢(xún)結(jié)果如圖。說(shuō)明：因?yàn)楫?dāng)前會(huì)話(huà)前設(shè)置了innodb_file_per_table=ON，而CREATETABLEkcb...創(chuàng)建表又沒(méi)有指定表空間，該表默認(rèn)就為獨(dú)立表空間?！癝ingle”表示表空間類(lèi)型是獨(dú)立表空間。表

空

間3．表空間中表的移動(dòng)ALTERTABLE語(yǔ)句通過(guò)指定表空間項(xiàng)，可使表在系統(tǒng)表空間、獨(dú)立表空間、通用表空間等不同類(lèi)型的表空間之間自由移動(dòng)，語(yǔ)句格式為：ALTERTABLE表名TABLESPACE=表空間名/類(lèi)型;其中，“表空間名”是要移動(dòng)到的表空間的名稱(chēng)；“類(lèi)型”用來(lái)標(biāo)識(shí)系統(tǒng)表空間或獨(dú)立表空間，“innodb_system”表示系統(tǒng)表空間，“innodb_file_per_table”是獨(dú)立表空間。表

空

間【例】表空間移動(dòng)。（1）將kcb表由獨(dú)立表空間移入系統(tǒng)表空間。ALTERTABLExscj.kcbTABLESPACE=innodb_system;此時(shí)，在…\Data\xscj目錄下只有kcb.frm文件，沒(méi)有該表獨(dú)立表空間的kcb.ibd文件。（2）將kcb表由系統(tǒng)表空間移入獨(dú)立表空間。ALTERTABLExscj.kcbTABLESPACE=

innodb_file_per_table;此時(shí)，在…\Data\xscj目錄下又產(chǎn)生了獨(dú)立表空間的kcb.ibd文件。（3）將kcb表由獨(dú)立表空間移至通用表空間myGSpace。ALTERTABLExscj.kcbTABLESPACE=

myGSpace;SELECTNAME,FLAGFROMinformation_schema.INNODB_SYS_TABLES WHERESPACE_TYPE='General';此時(shí)在…\Data\xscj目錄下獨(dú)立表空間的kcb.ibd文件又不見(jiàn)了，而通用表空間myGSpace中則包含了3個(gè)表xsb、cjb和kcb。表

空

間4．刪除表空間刪除表空間使用下列語(yǔ)句：DROP[UNDO]TABLESPACE表空間名;對(duì)于不同類(lèi)型的表空間，刪除時(shí)需要滿(mǎn)足不同的要求。共享表空間必須先刪除表后才能刪除表空間。03表記錄分區(qū)范圍分區(qū)列表分區(qū)散列分區(qū)鍵分區(qū)子分區(qū)分區(qū)管理表記錄分區(qū)1．范圍分區(qū)每個(gè)分區(qū)包含分區(qū)表達(dá)式值位于給定范圍內(nèi)的行，范圍應(yīng)該是連續(xù)的而不是重疊的。范圍分區(qū)定義如下：PARTITIONBYRANGE(表達(dá)式|列名) #（a）|PARTITIONBYRANGECOLUMNS(列名表) #（b）PARTITIONS數(shù)量[( PARTITION分區(qū)名VALUESLESSTHAN(值表), ...)];說(shuō)明：（a）范圍分區(qū)（BYRANGE）含列表達(dá)式或者列只能為整數(shù)類(lèi)型。分區(qū)的列值為NULL，將其視為小于任何其他值，表達(dá)式可以包含部分系統(tǒng)函數(shù)，例如YEAR(出生日期)。（b）范圍列（BYRANGECOLUMNS）接受一個(gè)或多個(gè)列的列名表，列的數(shù)據(jù)類(lèi)型可以整數(shù)、字符串（text和blob除外）、日期（日期時(shí)間）。表記錄分區(qū)1）創(chuàng)建分區(qū)表xsb【例】創(chuàng)建xscj數(shù)據(jù)庫(kù)一個(gè)分區(qū)表xsb，按照學(xué)生入學(xué)年份劃分為3個(gè)分區(qū)。USExscj;DROPTABLEIFEXISTSxsb;#SETGLOBALinnodb_file_per_table=ON; #（e）CREATETABLExsb(

學(xué)號(hào) char(6) NOTNULLPRIMARYKEY,

姓名 char(4) NOTNULL,

專(zhuān)業(yè) char(10) NULL,

性別 tinyint(1) NOTNULLDEFAULT1,

出生日期 date NOTNULL,

總學(xué)分 tinyint(1) NULL,

備注 text NULL,

照片 blob NULL) ENGINE=INNODB PARTITIONBYRANGECOLUMNS(學(xué)號(hào)) #（a） PARTITIONS3 ( PARTITIONp0VALUESLESSTHAN('21'), #（b） PARTITIONp1VALUESLESSTHAN('22'), PARTITIONp2VALUESLESSTHANMAXVALUE #（c） );INSERTINTOxsbSELECT*FROMxs; #（d）表記錄分區(qū)說(shuō)明：（a）因?yàn)閷W(xué)號(hào)列為字符型，所以不能采用RANGE(學(xué)號(hào))分區(qū)。（b）定義每一個(gè)分區(qū)：PARTITION后面跟的是分區(qū)的名稱(chēng)（p0，p1，p2），名稱(chēng)遵循標(biāo)識(shí)符的規(guī)則，不區(qū)分大小寫(xiě)。如果沒(méi)有指定分區(qū)的名稱(chēng)，自動(dòng)為分區(qū)命名p0、p1、p2、…、pn-1（n是分區(qū)數(shù)量）。（c）因?yàn)榘凑辗秶˙YRANGE）小于（LESSTHAN）分區(qū)，最后一個(gè)采用MAXVALUE表示最大值。（d）插入記錄到分區(qū)表xsb中。（e）如果創(chuàng)建獨(dú)立表空間表xsb，那么，一個(gè)分區(qū)就會(huì)對(duì)應(yīng)一個(gè)數(shù)據(jù)文件，如圖為xsb表p0、p1、p2分區(qū)數(shù)據(jù)文件和表結(jié)構(gòu)等信息文件。表記錄分區(qū)2）查詢(xún)分區(qū)信息MySQL的表分區(qū)信息統(tǒng)一存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)information_schema的PARTITIONS表中，用戶(hù)可根據(jù)需要查詢(xún)指定表分區(qū)的情況?！纠坎樵?xún)xsb表分區(qū)信息。SELECT PARTITION_NAME分區(qū)名稱(chēng), PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分區(qū)類(lèi)型, PARTITION_EXPRESSION表達(dá)式, PARTITION_DESCRIPTION描述, CREATE_TIME創(chuàng)建時(shí)間, TABLE_ROWSAS記錄數(shù) FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='xsb';分區(qū)信息如圖。表記錄分區(qū)3）查詢(xún)分區(qū)數(shù)據(jù)記錄在對(duì)表分區(qū)后，就可以使用包含PARTITION(分區(qū)名,...)子句的SELECT語(yǔ)句分別單獨(dú)查詢(xún)存儲(chǔ)在不同分區(qū)中的數(shù)據(jù)記錄?！纠坎樵?xún)xsb表分區(qū)記錄。USExscj;SELECT學(xué)號(hào),姓名,性別FROMxsbPARTITION(p1); #（a）SELECT學(xué)號(hào),姓名,性別 FROMxsbPARTITION(p1,p2) WHERE性別=0; #（b）SELECT學(xué)號(hào),姓名FROMxsbWHERE學(xué)號(hào)>'2212'; #（c）SELECT學(xué)號(hào),姓名,出生日期FROMxsbWHEREYEAR(出生日期)>2003; #（d）顯示結(jié)果如圖。

表記錄分區(qū)4）修改分區(qū)表修改分區(qū)表就是在A(yíng)LTERTABLE語(yǔ)句修改表結(jié)構(gòu)的同時(shí)使用PARTITIONBY子句描述修改的分區(qū)信息，包括對(duì)未分區(qū)的表進(jìn)行分區(qū)和對(duì)已分區(qū)的表重新規(guī)劃分區(qū)，語(yǔ)句格式如下：ALTERTABLE表名 PARTITIONBY分區(qū)類(lèi)型(分區(qū)表達(dá)式) (

分區(qū)定義,... );這實(shí)際上就是將新的分區(qū)類(lèi)型及定義信息完整寫(xiě)出來(lái)，用以覆蓋已有的分區(qū)。表記錄分區(qū)【例】修改cjb表結(jié)構(gòu)，加入表分區(qū)信息。按照課程號(hào)分為3個(gè)分區(qū)。（1）創(chuàng)建cjb表：USExscj;DROPTABLEIFEXISTScjb;CREATETABLEcjbSELECT*FROMcj;（2）修改cjb表結(jié)構(gòu)，加入分區(qū)信息：ALTERTABLEcjb ADDPRIMARYKEY(學(xué)號(hào),課程號(hào)) #（a） PARTITIONBYRANGECOLUMNS(課程號(hào)) #（b） PARTITIONS3 ( PARTITIONCj100VALUESLESSTHAN('200'), PARTITIONcj200VALUESLESSTHAN('300'), PARTITIONcj300VALUESLESSTHANMAXVALUE );SELECTcount(*)FROMcjbPARTITION(cj100); 查詢(xún)顯示結(jié)果如圖。表記錄分區(qū)2．列表分區(qū)列表分區(qū)中，每個(gè)分區(qū)都是根據(jù)一組值表中的一個(gè)列值的成員關(guān)系來(lái)定義和選擇的，而不是根據(jù)一個(gè)連續(xù)的值范圍來(lái)選擇。它也有兩種形式如下。PARTITIONBYLIST(表達(dá)式|列名) #（a）|PARTITIONBYLISTCOLUMNS(列名表) #（b）PARTITIONS數(shù)量[( PARTITION分區(qū)名VALUESIN(值表), #（a） ...)];列表分區(qū)與范圍分區(qū)相比有下列不同。（1）范圍分區(qū)是小于指定值的范圍內(nèi)的記錄均進(jìn)入分區(qū)，而列表分區(qū)只有列的值或者表達(dá)式的值在值表中才能加入分區(qū)。也就是說(shuō)，范圍分區(qū)的條件是一條線(xiàn)，而列表分區(qū)條件是多個(gè)點(diǎn)。（2）由于列表分區(qū)的記錄值只能在分區(qū)定義的IN子句后的值表中選擇，向這種分區(qū)表中不能插入任意值的記錄。（3）列表分區(qū)將空值NULL也看作是一個(gè)值，像對(duì)待任何其他的值一樣。但當(dāng)且僅當(dāng)分區(qū)定義中的某一個(gè)分區(qū)使用包含NULL的值表定義時(shí)，列表分區(qū)表才允許分區(qū)列上的NULL值插入。表記錄分區(qū)【例】對(duì)kcb表按“開(kāi)課學(xué)期”年度分區(qū)。USExscj;DROPTABLEIFEXISTSkcb;CREATETABLEkcbSELECT*FROMkc;ALTERTABLEkcb PARTITIONBYLIST(開(kāi)課學(xué)期) ( PARTITION一學(xué)年課VALUESIN(1,2), PARTITION二學(xué)年課VALUESIN(3,4), PARTITION三學(xué)年課VALUESIN(5,6), PARTITION四學(xué)年課VALUESIN(7) );SELECT*FROMkcbPARTITION(一學(xué)年課);顯示結(jié)果如圖。表記錄分區(qū)