MySQL數(shù)據(jù)庫原理與應(yīng)用(微課版)ch05數(shù)據(jù)表操作

第5章

數(shù)據(jù)表操作本章概述在數(shù)據(jù)庫中，針對數(shù)據(jù)表對象的操作有，創(chuàng)建數(shù)據(jù)表、查看表結(jié)構(gòu)、修改表結(jié)構(gòu)、重命名數(shù)據(jù)表、復(fù)制數(shù)據(jù)表和刪除數(shù)據(jù)表。在創(chuàng)建數(shù)據(jù)表時，需要為表字段設(shè)置所存儲數(shù)據(jù)的類型、約束，還可以為數(shù)據(jù)表對象設(shè)置索引，以提高數(shù)據(jù)表檢索效率。本章學(xué)習(xí)目標掌握數(shù)據(jù)表操作，包括創(chuàng)建數(shù)據(jù)表、查看表結(jié)構(gòu)、修改表結(jié)構(gòu)、重命名數(shù)據(jù)表、復(fù)制數(shù)據(jù)表和刪除數(shù)據(jù)表。熟悉數(shù)據(jù)類型并能夠為字段選擇恰當?shù)臄?shù)據(jù)類型。重點掌握數(shù)字類型、字符串類型、日期時間類型。掌握表約束操作，能夠恰當?shù)貫閿?shù)據(jù)表設(shè)置表約束。掌握索引操作，能夠?qū)?shù)據(jù)表設(shè)置合適的索引，以提高數(shù)據(jù)檢索效率。目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.1創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表，使用CREATETABLE語句，語法如下。CREATE[TEMPORARY]TABLE[IFNOTEXISTS]數(shù)據(jù)表名[(create_definition,…)][table_options][select_statement]關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.2查看數(shù)據(jù)表使用SHOWCOLUMNS語句查看數(shù)據(jù)表結(jié)構(gòu)：SHOW[FULL]COLUMNSFROM數(shù)據(jù)表名[FROM數(shù)據(jù)庫名];或SHOW[FULL]COLUMNSFROM數(shù)據(jù)表名.數(shù)據(jù)庫名;關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.3復(fù)制數(shù)據(jù)表在一個已經(jīng)存在的數(shù)據(jù)表的基礎(chǔ)上創(chuàng)建該表的備份：CREATETABLE[IFNOTEXISTS]數(shù)據(jù)表名{LIKE源數(shù)據(jù)表名|(LIKE源數(shù)據(jù)表名)}關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.4修改表結(jié)構(gòu)可以使用ALTERTABLE語句來實現(xiàn)，語法格式如下。ALTER[IGNORE]TABLE數(shù)據(jù)表名alter_spec[,alter_spec]…|table_options關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.5重命名數(shù)據(jù)表重命名數(shù)據(jù)表可以使用RENAMETABLE語句來實現(xiàn)：RENAMETABLE數(shù)據(jù)表名1To數(shù)據(jù)表名2例如：RENAMETABLEtb_admin_oldTOtb_admin_old1;關(guān)系數(shù)據(jù)庫基礎(chǔ)5.1.6刪除數(shù)據(jù)表使用DROPTABLE語句即可刪除數(shù)據(jù)表：DROPTABLE[IFEXISTS]數(shù)據(jù)表名;目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)數(shù)據(jù)類型5.2.1數(shù)字類型：數(shù)字類型總體可以分成整數(shù)和浮點兩種數(shù)據(jù)類型。整數(shù)類型：數(shù)據(jù)類型取值范圍說明單位TINYINT符號值：-128~127，無符號值：0~255最小的整數(shù)1字節(jié)BIT符號值：-128~127，無符號值：0~255最小的整數(shù)1字節(jié)BOOL符號值：-128~127，無符號值：0~255最小的整數(shù)1字節(jié)SMALLINT符號值：-3276832767無符號值：0~65535小型整數(shù)2字節(jié)MEDIUMINT符號值：-8388608~8388607無符號值：0~16777215中型整數(shù)3字節(jié)INT符號值：-2147683648~2147683647無符號值：0~4294967295標準整數(shù)4字節(jié)BIGINT符號值：-9223372036854775808~9223372036854775807無符號值：0~18446744073709551615大型整數(shù)8字節(jié)數(shù)據(jù)類型5.2.1數(shù)字類型：數(shù)字類型總體可以分成整數(shù)和浮點兩種數(shù)據(jù)類型。浮點類型：數(shù)據(jù)類型取值范圍說明單位FLOAT+(-)3.402823466E+38單精度浮點數(shù)8或4字節(jié)DOUBLE+(-)1.7976931348623157E+308雙精度浮點數(shù)8字節(jié)DECIMAL+(-)2.2250738585072014E-308一般整數(shù)自定義長度數(shù)據(jù)類型5.2.2字符串類型字符串類型包括3類：普通文本字符串類型（CHAR和VARCHAR）、可變類型（TEXT和BLOB）和特殊類型（SET和ENUM）。數(shù)據(jù)類型5.2.2字符串類型普通文本字符串類型（CHAR和VARCHAR）數(shù)據(jù)類型取值范圍說明NationalCHAR(M)[binary|ASCII|unicode]0~255個字符固定長度為M的字符串，其中M的取值范圍為0~255。national關(guān)鍵字指定了應(yīng)該使用的默認字符集。binary關(guān)鍵字指定了數(shù)據(jù)是否區(qū)分大小寫(默認是區(qū)分大小寫的)。ASCII關(guān)鍵字指定了在列中使用latin1字符。unicode關(guān)鍵字指定了使用UCS字符集CHAR1~255個字符與CHAR(M)類似[national]VARCHAR(M)[binary]0~255個字符長度可變，其他與CHAR(M)類似數(shù)據(jù)類型5.2.2字符串類型可變類型數(shù)據(jù)類型最大長度（字節(jié)數(shù)）說明TINYBLOB2^8-1(255)小BLOB字段TINYTEXT2^8-1(255)小TEXT字段BLOB2^16-1(65535)常規(guī)BLOB字段TEXT2^16-1(65535)常規(guī)TEXT字段MEDIUMBLOB2^24-1(16777215)中型BLOB字段MEDIUMTEXT2^24-1(16777215)中型TEXT字段LONGBLOB2^32-1(4294967295)長BLOB字段LONGTEXT2^32-1(4294967295)長TEXT字段數(shù)據(jù)類型5.2.2字符串類型特殊類型類型最大值說明ENUM("valuel","value2",...)65535該類型的列只可以容納所列值之一或為NULLSET("valuel","value2",...)64該類型的列可以容納一組值或為NULL數(shù)據(jù)類型5.2.3日期和時間類型日期和時間類型包括DATE、DATETIME、TIME、TIMESTAMP和YEAR。其中每種類型都有其取值范圍，如賦予它一個不合法的值，將會被“0”代替。日期和時間類型如表所示。類型取值范圍說明DATE1000-01-01~9999-12-31日期，格式為YYYY-MM-DDTIME-838:58:59~835:59:59時間，格式為HH:MM:SSDATETIME1000-01-0100:00:00~9999-12-3123:59:59日期和時間，格式為YYYY-MMDDHH:MM:SSTIMESTAMP1970-01-0100:00:00~2037年的某個時間時間標簽，在處理報告時使用顯示格式取決于M的值YEAR1901~2155年份可指定兩位數(shù)字和四位數(shù)字的格式數(shù)據(jù)類型5.2.4如何選擇類型整數(shù)和浮點數(shù)浮點數(shù)和定點數(shù)日期與時間類型CHAR與VARCHAR之間的特點與選擇ENUM和SETBLOB和TEXT目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)表約束操作完整性約束條件是對字段進行限制的，要求用戶對該屬性進行的操作符合特定的要求。如果不滿足完整性約束條件，數(shù)據(jù)庫系統(tǒng)就不再執(zhí)行用戶的操作。MySQL中基本的完整性約束條件如表所示。類型取值范圍PRIMARYKEY主鍵，可以唯一地標識對應(yīng)的元組FOREIGNKEY外鍵，是與之聯(lián)系的某表的主鍵NOTNULL字段值不能為空UNIQUE字段值唯一AUTOINCREMENT字段取值自動增加，這是MySOL特色DEFAULT設(shè)置默認值表約束操作當數(shù)據(jù)庫表中的某個字段上的內(nèi)容不希望設(shè)置為NULL時，則可以使用非空（NOTNULL）約束進行設(shè)置。CREATETABLEtablename(PropNamepropTypeNOTNULL,……);設(shè)置表字段的非空約束表約束操作當向數(shù)據(jù)表中插入一條新記錄時，如果沒有為某個字段賦值，那么數(shù)據(jù)庫系統(tǒng)會自動為這個字段插入默認值。CREATETABLEtablename(propNamepropTypeDEFAULTdefaultValue,……);設(shè)置表字段的默認值表約束操作當數(shù)據(jù)庫表中某個字段上的內(nèi)容不允許重復(fù)時，可以使用唯一（UNIQVE，UK）約束進行設(shè)置。CREATETABLEtablename(propNamepropTypeUNIQUE,……);設(shè)置表字段唯一約束（UNIQUE，UK）表約束操作當數(shù)據(jù)庫表中某個字段上的內(nèi)容不允許重復(fù)時，可以使用唯一（UNIQVE，UK）約束進行設(shè)置。CREATETABELtablename(propNamepropTypePRIMARYKEY,……);設(shè)置表字段的主鍵約束表約束操作AUTO_INCREMENT是MySQL唯一擴展的完整性約束，當為數(shù)據(jù)庫表中插入新記錄時，字段上的值會自動生成唯一的ID。CREATETABLEtablename(propNamepropTypeAUTO_INCREMENT,……);設(shè)置表字段值自動增加表約束操作外鍵（FOREIGNKEY，F(xiàn)K）是表的一個特殊字段，外鍵約束用于保證多個表（通常為兩個表）之間的參照完整性，即構(gòu)建與兩個表的字段之間的參照關(guān)系。CREATETABLEtablename_1(propName1_1propType1_1,propName1_2propType1_2,……CONSTRAINTFK_propFOREIGNKEY(propName1_1)REFERENCEStablename_2(propName2_1));設(shè)置表字段的外鍵約束目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)索引操作普通索引唯一性索引單列索引多列索引空間索引索引概述索引操作在建立數(shù)據(jù)表時可以直接創(chuàng)建索引，這種方式比較直接，且方便、易用。在建立數(shù)據(jù)表時創(chuàng)建索引的基本語法結(jié)構(gòu)如下：CREATETABLEtable_name(屬性名數(shù)據(jù)類型[約束條件],屬性名數(shù)據(jù)類型[約束條件]…屬性名數(shù)據(jù)類型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[別名](屬性名1[(長度)][ASC|DESC]));創(chuàng)建索引索引操作在MySQL中，不但可以在創(chuàng)建數(shù)據(jù)表時創(chuàng)建索引，也可以直接在已經(jīng)創(chuàng)建的表中，在已經(jīng)存在的一個或幾個字段中創(chuàng)建索引。語法結(jié)構(gòu)如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(屬性[(length)][ASC|DESC]);創(chuàng)建索引索引操作刪除索引可以通過DROP語句來實現(xiàn)，語法結(jié)構(gòu)如下：DROPINDEXindex_nameONtable_name;刪除索引目錄01020304表約束操作數(shù)據(jù)類型索引操作數(shù)據(jù)表基本操作05本章小結(jié)本章小結(jié)本章主要介紹了MySQL數(shù)