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

第3章

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

MySQL

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

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

建

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

建

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

建

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

建

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

建

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

建

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

建

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

建

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

建

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

建

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

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

02修

改

表增加修改刪除列增加刪除列、索引和完整性約束修改表選項修

改

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

改

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

改

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

改

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

改

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

改

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

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

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

入

記

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

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

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

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

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

改

記

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

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

03刪

除

記

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

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

課程號 char(3) NOTNULLPRIMARYKEY,

課程名 varchar(8) NOTNULL,

開課學(xué)期 tinyint NOTNULL,

學(xué)時 tinyint NOTNULL,

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

學(xué)號 char(6) NOTNULL,

課程號 char(3) NOTNULL,

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

學(xué)號 `char(6)NOTNULLPRIMARYKEY,

愛好 set('書法','繪畫','音樂','運動')NULL,

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

家庭地址 json,

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

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

選

項01存

儲

引

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

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

空

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

空

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

空

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

空

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

空

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

空

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

空

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

空

間【例】獨立表空間的創(chuàng)建和使用。SETGLOBALinnodb_file_per_table=ON;DROPTABLEIFEXISTSkcb;CREATETABLEkcbASSELECT*FROMkc;SELECTNAME,SPACE_TYPEFROMinformation_schema.INNODB_SYS_TABLES WHERENAME='xscj/kcb';查詢結(jié)果如圖。說明：因為當(dāng)前會話前設(shè)置了innodb_file_per_table=ON，而CREATETABLEkcb...創(chuàng)建表又沒有指定表空間，該表默認就為獨立表空間。“Single”表示表空間類型是獨立表空間。表

空

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

空

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

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

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

空

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

學(xué)號 char(6) NOTNULLPRIMARYKEY,

姓名 char(4) NOTNULL,

專業(yè) char(10) NULL,

性別 tinyint(1) NOTNULLDEFAULT1,

出生日期 date NOTNULL,

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

備注 text NULL,

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

表記錄分區(qū)4）修改分區(qū)表修改分區(qū)表就是在ALTERTABLE語句修改表結(jié)構(gòu)的同時使用PARTITIONBY子句描述修改的分區(qū)信息，包括對未分區(qū)的表進行分區(qū)和對已分區(qū)的表重新規(guī)劃分區(qū)，語句格式如下：ALTERTABLE表名 PARTITIONBY分區(qū)類型(分區(qū)表達式) (

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