MySQL教程（新體系-綜合應(yīng)用實例視頻）（第4版） 課件 第3、4章 數(shù)據(jù)類型;數(shù)據(jù)庫及表結(jié)構(gòu)設(shè)計

第3章

數(shù)據(jù)類型——數(shù)值類型及實例MySQL+實用教程（第4版）MySQL8.0包含豐富的數(shù)據(jù)類型，可以方便處理各種數(shù)據(jù)。系統(tǒng)數(shù)據(jù)類型如表3.1所示。數(shù)據(jù)類型符號標(biāo)識整數(shù)類型tinyint,smallint,mediumint,int,bigint向下兼容：bool(boolean)實數(shù)類型float,double,decimal,numeric日期與時間類型year,time,date,datetime,timestamp字符串類型char,varchar,tinytext,text,mediumtext,longtextbit,binary,varbinary,tinyblob,blob,mediumblob,longblob枚舉、集合類型enum,setJSON數(shù)據(jù)類型json空間數(shù)據(jù)類型point,multipoint,polygon,multipolygon,geometry,geometrycollection,linestring,multilinestring,01整數(shù)類型整數(shù)類型整數(shù)類型可表示有符號或無符號的整數(shù)數(shù)值，其最大值和最小值（含符號位）確定了默認(rèn)的顯示位數(shù)。表3.2列出了MySQL中所有的整數(shù)類型主要屬性。數(shù)據(jù)類型字節(jié)數(shù)有符號范圍無符號范圍tinyint1-128~127（-27~27-1）0~255（0~28-1）smallint2-32768~32767（-215~215-1）0~65535（0~216-1）mediumint3-8388608~8388607（-223~223-1）0~16777215（0~224-1）intinteger4-2147483648~2147483647（-231~231-1）0~4294967295（0~232-1）bigint8±9.22×1018（-263~263-1）0~1.84×1019（0~264-1）（1）上表“數(shù)據(jù)類型”定義的都是有符號的，加上unsigned關(guān)鍵字，定義成無符號的類型。（2）MySQL中是沒有布爾類型的，但為了兼容SQL標(biāo)準(zhǔn)，也可以定義聲明bool（boolean）類型，但它們最終都會被轉(zhuǎn)換成tinyint(1)類型存儲。02實數(shù)類型1.浮點數(shù)2.定點數(shù)3.數(shù)值顯示長度實數(shù)類型1.浮點數(shù)浮點數(shù)又有兩種格式：單精度浮點數(shù)（float）和雙精度浮點數(shù)（double），遵循IEEE754標(biāo)準(zhǔn)。real是double的同義詞，除非啟用了real_as_float模式才能作為單精度浮點數(shù)使用。在IEEE754標(biāo)準(zhǔn)下，浮點格式主要分為四種類型，即單精度格式、雙精度格式、擴(kuò)展單精度格式和擴(kuò)展雙精度格式。其中32位單精度格式與64位雙精度格式作為基礎(chǔ)格式更為常用，擴(kuò)展格式則有特殊目的，一般對用戶透明。浮點格式可分為符號位s、指數(shù)位e以及尾數(shù)位f三部分，如圖3.1所示。實數(shù)類型2.定點數(shù)定點數(shù)有decimal和numeric兩種類型，在MySQL中，numeric被實現(xiàn)為decimal，因此兩者具有相同的性質(zhì)。decimal類型通常寫為decimal(m,d)，用于存儲必須為確切精度的數(shù)值，占用m+2字節(jié)空間。其中：m：精度，表示總共的位數(shù)，取值范圍1～65，取0時會被設(shè)為默認(rèn)值，超出范圍會報錯。m的默認(rèn)取值為10。d：標(biāo)度（d≤m），表示小數(shù)的位數(shù)，取值范圍0～30，超出范圍會報錯。d的默認(rèn)取值為0。3.數(shù)值顯示長度創(chuàng)建表的時候，MySQL會為每種數(shù)值類型設(shè)定默認(rèn)的長度，這個默認(rèn)長度值是根據(jù)該類型所能表示取值范圍內(nèi)的最大數(shù)值位數(shù)確定的，有符號的整數(shù)類型tinyint、smallint和int在其取值范圍內(nèi)最大可能的數(shù)值位數(shù)分別為3、5、11，這就是它們的默認(rèn)長度。實數(shù)類型【例3.1】定義類型設(shè)置的長度與顯示長度的關(guān)系。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( i1 smallint, i2 smallint(3), f1 float, f2 float(6,2), f3 float(9,3), d1 decimal(9,3), c1 float(10,2) AS(f3*10), c2 decimal(10,2) AS(d1*10));INSERTINTOtest(i1,i2,f1,f2,f3,d1) VALUES(12345,-12345,1234567,1234.567,123456.734,123456.734);SELECT*FROMtest;實數(shù)類型運行結(jié)果如圖3.2所示。說明：（1）i1smallint列：最大長度為5位，123456就超過表達(dá)范圍。（2）i2smallint(3)列：雖然定義3位顯示長度，但-123456沒有超過表達(dá)范圍仍然可以存儲。（3）f1float列：最大有效位數(shù)6位，無法精確表達(dá)1234567，而只能將第7位四舍五入為123457存儲。（4）f2float(6,2)列：精度6位、小數(shù)2位，整數(shù)部分只能表達(dá)4位，超過無法表達(dá)。小數(shù)后3位四舍五入。（5）f3float(9,3)列：精度9位、小數(shù)3位，整數(shù)部分可表達(dá)6位，超過無法表達(dá)。即使精度定義超過9位，整數(shù)部分也不能超過6位，因為最大有效位數(shù)6位。（6）計算列c1:因為超過了float最大有效位數(shù)6位，數(shù)值計算結(jié)果就不準(zhǔn)確了。（7）計算列c2:定點數(shù)decimal沒有最大有效位數(shù)只有最大表達(dá)范圍，因為在其表達(dá)范圍，計算結(jié)果準(zhǔn)確。03位

型位型【例3.2】表位（bit）型類型列存儲數(shù)據(jù)測試。（1）創(chuàng)建測試表，bit類型列可以顯式指定位長，也可不指定。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( b1 bit, b2 bit(6));位型（3）當(dāng)插入數(shù)據(jù)的位數(shù)超出定義的位長，在嚴(yán)格模式下將被拒絕并提示錯誤，非嚴(yán)格模式時，當(dāng)超出位長，從低位（右）起往高位取定義位長的串，若超出部分（左）子串中含1，則存儲定義長度的全1串；否則，存儲從低位起取到的定義位長的串。（2）插入bit型數(shù)據(jù)其實就是存入二進(jìn)制的值，類似010110?？梢栽赟QL語句中直接寫二進(jìn)制串，也可以使用b'x'符號，其中x是使用0和1編寫的二進(jìn)制值。INSERTINTOtest(b1,b2)VALUES(0,1);INSERTINTOtest(b1,b2)VALUES(1,001);INSERTINTOtest(b1,b2)VALUES(0,b'101');SELECT*FROMtest;運行結(jié)果如圖3.3所示。第3章

數(shù)據(jù)類型——日期與時間類型及實例日期與時間類型及實例MySQL8.0的日期時間類型較為豐富，能表達(dá)多種多樣不同格式的日期時間信息。日期時間類型及其主要屬性如表3.3所示。類型名格

式日

期

范

圍存儲空間yearyyyy1901～21551字節(jié)timehh:mm:ss-838:59:59～838:59:593字節(jié)dateyyyy-mm-dd1000-01-01～9999-12-313字節(jié)datetimeyyyy-mm-ddhh:mm:ss1000-01-0100:00:00～9999-12-3123:59:598字節(jié)timestampyyyy-mm-ddhh:mm:ss1970-01-0100:00:01utc～2038-01-1903:14:07utc4字節(jié)日期與時間類型及實例1.年（year）year(4)是四位數(shù)格式的一年。MySQL以yyyy格式顯示年份值，但允許使用字符串或數(shù)字將值賦給數(shù)據(jù)庫表year類型列。值顯示為1901到2155或0000。2.日期[時間]日期[時間]類型包含了date、datetime和timestamp這3種子類型。（1）date：僅表示一個日期，支持的范圍是“1000-01-01”到“9999-12-31”。（2）datetime：表示日期和時間的組合，支持的范圍是“1000-01-0100:00:00.000000”到“9999-12-3123:59:59.999999”。（3）timestamp：表示一個時間戳，能夠自動存儲記錄修改的時間，范圍是“1970-01-0100:00:01.000000UTC”到“2038-01-1903:14:07.999999UTC”。其中，UTC（UniversalTimeCoordinated）為世界標(biāo)準(zhǔn)時間，時間戳不能表示值'1970-01-0100:00:00'，因為它等于開始的0秒，而值0保留用于表示'00:00-00-0000:00:00'（即"0"時間戳值），將它設(shè)為null默認(rèn)為當(dāng)前的日期和時間。日期與時間類型及實例3.時間（time）（1）表示一段時間，范圍是'-838:59:59.000000'到'838:59:59.000000'。MySQL以'hh:mm:ss[.微秒]'格式顯示時間值，并允許使用字符串或數(shù)值兩種方式為時間列分配值。可以給出0到6長度范圍內(nèi)的可選fsp值，以指定小數(shù)秒精度。（2）如果開啟了嚴(yán)格模式（STRICT_TRANS_TABLES），并且年月日中任何一個可為0，但不允許插入零日期（NO_ZERO_DATE）,'0000-00-00'除外。SETSQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_DATE';（3）帶冒號字符串表示時間，最常見的是以標(biāo)準(zhǔn)的'hh:mm:ss'格式檢索和顯示時間值，對于較大的小時值可采用'hhh:mm:ss'或'dhh:mm:ss'格式。此外，MySQL還支持一些簡短的“非嚴(yán)格”語法，如：'hh:mm'、'dhh:mm'、'dhh'或'ss'等（這里d表示日，取值范圍0～31）。（4）'hhmmss'格式的沒有冒號間隔符的字符串表示時間，與此對應(yīng)的數(shù)值也可表達(dá)時間。日期與時間類型及實例【例3.3】日期、時間類型插入、存儲和顯示。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest(

日期 date,

日期時間 datetime,

時間戳 timestamp,

時間 time);INSERTINTOtest VALUES('1983-09-2507:25:16','1983-9-257:25:16','2013-11-3','8:5:9');#（a）INSERTINTOtest VALUES('19830925','19830925072516','20130113','144908'); #（b）INSERTINTOtest VALUES('99-9-25','990925072516','131103','29:28'); #（c）INSERTINTOtes VALUES(990925,19990925072516,19700101080001,'520'); #（d）SELECT*FROMtest;日期與時間類型及實例運行結(jié)果如圖3.4所示。說明：（a）用'yyyy-mm-dd'或'yyyy-mm-ddhh:mm:ss'標(biāo)準(zhǔn)字符串格式表示日期時間值，其中，yyyy表示年，mm表示月，dd表示日，hh表示時，mm表示分，ss表示秒。（b）用不含間隔符的字符串格式表示日期時間，形如'yyyymmdd'或'yyyymmddhhmmss'，其中的字符含義與標(biāo)準(zhǔn)字符串的相同，但這種寫法中的每個字符位置須嚴(yán)格對應(yīng)，不可再用單獨一位數(shù)表示小于10的值，例如，日期'2013-11-3'必須寫成'20131103'，簡寫成'2013113'系統(tǒng)就無法識別，只能插入默認(rèn)“零”值。（c）用兩位年值的字符串格式表示日期時間，可以包含也可不含間隔符，例如，'yy-mm-dd'、'yymmdd'、'yymmddhhmmss'等，其中兩位年值的轉(zhuǎn)換規(guī)則同前“year”類型，'01'～'69'對應(yīng)'2001'～'2069'，'70'～'99'對應(yīng)'1970'～'1999'。（d）這是以數(shù)字格式表示的日期時間，如果一個數(shù)字是8或14位長，則假定為yyyymmdd或yyyymmddhhmmss格式，前4位數(shù)表示年；如果數(shù)字是6或12位長，則假定為yymmdd或yymmddhhmmss格式，前2位數(shù)表示年。其它數(shù)字被解釋為仿佛用零填充到了最近的長度。第3章

數(shù)據(jù)類型——字符串類型及實例01文本字符串型1.常用的文本字符串型2.字符和文本存儲舉例文本字符串型MySQL8中根據(jù)保存的文本長度用途不同，可以選擇不同的文本數(shù)據(jù)類型。表3.4列出了MySQL文本字符串?dāng)?shù)據(jù)類型及其屬性。類

型

名

稱存

儲

空

間（實際字符數(shù)L）最

大

字

符

數(shù)char(m)m255varchar(m)L+165535tinytext(m)L+1255text(m)L+265535mediumtext(m)L+316777215longtext(m)L+44294967295（4GB）enum1（≤255個枚舉）2（≤65535個枚舉）65535set1、2、3、4或864個成員文本字符串型1.常用的文本字符串型最常用的文本字符串型包括char、varchar及各種text類型。（1）固定長度字符串型：charchar通常定義成char(m)的形式，m是字符串長，m≤255，實際占用的存儲空間為m*n，n為一個字符在字符集中的字節(jié)數(shù)。當(dāng)保存時字符個數(shù)L不足m，則在右側(cè)填充空格，以達(dá)到指定的長度m。當(dāng)檢索到char(m)值時，尾部的空格將被刪除。SQL語句定義表時，可不設(shè)定長度，默認(rèn)m值為1。（2）可變長度字符串型：varcharvarchar通常定義成varchar(m)存儲，m表示定義的字符串長度，m≤65535。當(dāng)保存時字符個數(shù)L不足m，實際存儲的字符串為L個字符和一個字符串結(jié)束符。varchar在值保存和檢索時尾部的空格仍保留。SQL語句定義表時，必須用(m)指定長度。（3）變長度文本型text也是變長字符串類型，其存儲需求取決于字符串值的實際長度而不是最大可能尺寸。text類型又分為4種（tinytext、text、mediumtext和longtext），不同種類的text類型的存儲空間和最大數(shù)據(jù)長度不同。文本字符串型2.字符和文本存儲舉例【例3.4】創(chuàng)建表，包含3種類型的文本字符串列，然后對其進(jìn)行測試。1）創(chuàng)建測試表，包含3種類型的文本字符串列。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( c1 char(8), c2 varchar(8), c3 text);2）插入帶空格的字符串。INSERTINTOtest VALUES('hello','hello','hello');文本字符串型3）插入數(shù)值、數(shù)字、中英文等各種類型數(shù)據(jù)。DELETEFROMtest;INSERTINTOtest VALUES(3.14,12345678,2.718281828459); #（a）SETSESSIONsql_mode='';INSERTINTOtest VALUES(3.1415926,1234567890,2.718E02); #（b）INSERTINTOtest VALUES('Hi中國夢我的夢','2022北京冬奧','Hello!世界！'); #（c）SELECT*FROMtest;運行結(jié)果如圖3.5所示。說明：（a）對于不超過類型長度的數(shù)值，MySQL將其以原樣字符串形式存儲。（b）對于超過類型長度的數(shù)值，在嚴(yán)格模式下插入會出錯；非嚴(yán)格模式下，MySQL將其截斷至定義長度存儲。以科學(xué)記數(shù)法表示的數(shù)值，MySQL會自動計算出其值并保存為字符串形式。（c）MySQL8.0中每個中文漢字與單個英文字母或數(shù)字一樣，都只占一個字符長度。02字符集編碼1.MySQL8字符集2.MySQL8字符集系統(tǒng)變量字符集編碼1.MySQL8字符集在MySQL8中，不同的編碼方案對應(yīng)不同的紙飛機(jī)，比較常用字符集如下：（1）ascii字符集：基于羅馬字母表的一套ASCII碼字符集，它采用1個字節(jié)的低7位表示字符，高位始終為0。（2）latin1字符集：相對于ASCII碼字符集做了擴(kuò)展，仍然使用一個字節(jié)表示字符，但啟用了高位，擴(kuò)展了字符集的表示范圍。（3）gb2312字符集：是GB2312-80中文編碼的字符集。（4）gbk字符集：支持中文，字符有一字節(jié)編碼和兩字節(jié)編碼兩種方式。（5）utf8字符集：采用1～3個字節(jié)表示UTF-8字符。utf8字符集表示Unicode中的基本多文種平面（BMP），任何不在BMP的Unicode字符都無法使用utf8字符集存儲，包括Emoji表情和很多不常用的漢字以及任何新增的Unicode字符等。而Emoji是一種特殊的Unicode編碼，常見于iOS和Android手機(jī)上（如微信程序）。（6）utf8mb4字符集：MySQL8默認(rèn)字符集為utf8mb4，采用1～4個字節(jié)表示UTF-8編碼的所有字符，還擴(kuò)展了其他字符?？赏ㄟ^下列語句：SHOWCHARACTERSET;字符集編碼【例3.5】創(chuàng)建表，設(shè)置4個列分別采用4種不同語言文字的字符集編碼，然后對其進(jìn)行操作。1）創(chuàng)建測試表。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( id int, c1 char(10) CHARACTERSETutf8, #通用編碼 c2 char(10) CHARACTERSETgb2312, #中文編碼 c3 char(10) CHARACTERSETsjis, #日文編碼 c4 char(10) CHARACTERSETeuckr #韓文編碼);說明：創(chuàng)建表時如果不顯式指定字符集，MySQL會采用默認(rèn)的字符集。MySQL5及更早前版本默認(rèn)字符集是utf8，從MySQL8.0開始，默認(rèn)采用增強(qiáng)的utf8mb4編碼，它是對utf8的擴(kuò)充，為每個字符分配4字節(jié)長度，可以表示Unicode中一些不常用的字符。字符集編碼2）輸入字符集測試。INSERTINTOtest(id,c1,c2) VALUES(1,'駿馬奔騰','駿馬奔騰');INSERTINTOtest(id,c1,c3) VALUES(2,'駿馬ペンティアム','駿馬ペンティアム');INSERTINTOtest(id,c1,c4) VALUES(3,'??????','??????');SELECT*FROMtest;INSERTINTOtest(id,c3)VALUES(4,'駿馬奔騰');INSERTINTOtest(id,c4)VALUES(5,'駿馬奔騰');INSERTINTOtest(id,c2)VALUES(6,'駿馬ペンティアム');INSERTINTOtest(id,c4)VALUES(7,'馬に乘る');INSERTINTOtest(id,c2)VALUES(8,'??????');INSERTINTOtest(id,c3)VALUES(9,'??????');SELECT*FROMtest;顯示結(jié)果如圖3.6所示。字符集編碼3）混合文字測試。DELETEFROMtest;INSERTINTOtest(id,c1,c2) VALUES(1,'駿馬ペンティアム','駿馬ペンティアム'); #中日混合INSERTINTOtest(id,c1,c4) VALUES(2,'???ペンティアム','???ペンティアム’); #韓日混合INSERTINTOtest(id,c1,c4) VALUES(3,'駿馬???','駿馬???'); #韓中混合SELECT*FROMtest;運行結(jié)果如圖3.7所示。字符集編碼說明:（1）c1列采用utf8編碼，中、日、韓三種文字皆可輸入，當(dāng)然包括英文等各種文字和各種符號。（2）c2列采用GB2312編碼，只能插入GB2312編碼中包含的6763個漢字和字符，不包含在其中的漢字是不能進(jìn)入的。而《康熙字典》共收錄漢字47035個，絕大多數(shù)漢字無法保存到這種編碼列。如果需要數(shù)據(jù)庫保存更多的漢字，需要采用GBK、GB18030等編碼，或者使用Unicode編碼。因為中文GB2312編碼基本集中本身就有日文平假名和片假名的編碼字符區(qū)，如圖3.8所示。（3）c3列采用sjis編碼，只能輸入日文編碼字符，不能輸入中文和韓文（注意：該列也不能存放中文輸入法輸入的日文字符）。日文編碼中部分中文字符（繁體中文）是日文的組成部分，它們當(dāng)然可與日文一起混合插入日文字符集編碼的列中，因為此時的這些中文字符本身就是日文。（4）c4列采用euckr編碼，可以存放韓文編碼字符。韓文編碼集中也有部分中文字符（繁體中文）和日文字符，這部分中日文也可與韓文一起混合錄。字符集編碼2.MySQL8字符集系統(tǒng)變量在MySQL8.0中，從服務(wù)器、數(shù)據(jù)庫、表和表列等均可指定字符集，并通過系統(tǒng)變量指定默認(rèn)字符集。從客戶端輸入命令到最終顯示查詢結(jié)果也通過不同系統(tǒng)變量對應(yīng)字符集，如圖3.9所示。03字符排序規(guī)則1.規(guī)則名和排序集2.默認(rèn)字符集及排序規(guī)則3.查看字符排序規(guī)則字符排序規(guī)則1.規(guī)則名和排序集字符排序又稱字符序，由collation指定排序的規(guī)則名，MySQL中眾多的排序規(guī)則名可以歸納為以下兩大類排序集：（1）字符集_語言/other_ci/cs這是默認(rèn)的排序集，當(dāng)字符無須區(qū)分大小寫時采用該集合中的規(guī)則，規(guī)則命名以其對應(yīng)的字符集作為前綴，例如：字符序utf8_general_ci，表明它是字符集utf8的字符序。ci即對大小寫不敏感，cs對大小寫敏感。utf8字符集對應(yīng)的collation沒有cs。（2）字符集_bin當(dāng)字符需要區(qū)分大小寫時，選擇該排序集。每個字符集對應(yīng)一定數(shù)量的排序規(guī)則，collation用于指定數(shù)據(jù)集如何排序以及字符串的排序規(guī)則。字符排序規(guī)則2.默認(rèn)字符集及排序規(guī)則兩個不同的字符集不能有相同的排序規(guī)則?！纠?.6】字符集的排序規(guī)則。1）大小寫不敏感排序規(guī)則設(shè)置和查詢。USEmydb;DROPTABLEIFEXISTSmytab; CREATETABLEmytab ( t1 int, t2 char(20)CHARACTERSETgbk, t3 float(6,2));SELECTcolumn_name,character_set_name,collation_nameFROMinformation_schema.columnsWHEREtable_name='mytab'; #（a）INSERTINTOmytab(t1,t2,t3)VALUES (3,'a',3.45), (4,'b',3.10), (5,'A',10.23);SELECT*FROMmytabWHEREt2='a'; #（b）SELECT*FROMmytabORDERBYt2; #（c）字符排序規(guī)則運行結(jié)果如圖3.10所示。

說明：（a）雖然t2列僅僅設(shè)置字符集（gbk），但同時也就設(shè)置了對應(yīng)的默認(rèn)排序規(guī)則（gbk_chinese_cs）。通過查詢information_schema.columns（系統(tǒng)數(shù)據(jù)庫表）可以獲取當(dāng)前t2列的字符排序規(guī)則對應(yīng)不區(qū)分大小寫。（b）由于mytab表t2列排序規(guī)則不區(qū)分大小寫，所以該語句將t2列值為“'A'”的記錄也顯示出來。（c）雖然ASCII碼'A'<'a'，但由于mytab表t2列的排序規(guī)則不區(qū)分大小寫，誰排列在前取決于查詢時先遇到的行，這里它把“'a'”排在前面。字符排序規(guī)則2）大小寫敏感排序規(guī)則設(shè)置和查詢。USEmydb;ALTERTABLEmytab MODIFYt2char(20)CHARACTERSETgbkCOLLATEgbk_bin; #（a）SELECT*FROMmytabWHEREt2='a'; #（b）SELECT*FROMmytabORDERBYt2; #（c）運行結(jié)果如圖3.11所示。

說明：（a）修改設(shè)置t2列為字符集（gbk）對應(yīng)區(qū)分大小寫排序規(guī)則（gbk_bin）。（b）由于mytab表t2列排序規(guī)則區(qū)分大小寫，所以該語句僅顯示t2列值為'a'的記錄。（c）由于ASCII碼大寫字符小于小寫字符，而ORDERBYt2默認(rèn)從小到大排列，故這里把字符為'A'記錄排在前面，然后是字符為'a'和“'b'”記錄。字符排序規(guī)則3.查看字符排序規(guī)則（1）查看所有的字符排序規(guī)則，用下面語句：SHOWCOLLATION;（2）查看當(dāng)前字符集CHARACTER_SET_CONNECT、CHARACTER_SET_DATABASE、CHARACTER_SET_SERVER對應(yīng)的排序規(guī)則環(huán)境變量，用下面語句：SHOWVARIABLESLIKE'COLLATION_%';運行結(jié)果如圖3.12所示。字符排序規(guī)則（3）查詢字符集匹配的所有排序規(guī)則，用下面語句：SHOWcollationLIKE'字符集名%'例如，查詢以utf8mb4打頭和_cs結(jié)尾的排序規(guī)則：showcollationlike'utf8mb4%_cs';上述語句相對于條件查詢information_schema系統(tǒng)數(shù)據(jù)庫collations表記錄：select*frominformation_schema.collationswherecollation_namelike'utf8%';（4）查看指定表列的排序規(guī)則，例如：SHOWFULLCOLUMNSFROMmytab;可看到表mytab的完整結(jié)構(gòu)屬性及列上定義的排序規(guī)則，如圖3.13所示。04二進(jìn)制字符串類型1.二進(jìn)制字符串類型介紹2.二進(jìn)制字符串的性質(zhì)3.二進(jìn)制字符串存儲不同類型二進(jìn)制字符串類型1.二進(jìn)制字符串類型介紹二進(jìn)制字符串類型一般用于存儲圖像、聲音、視頻等內(nèi)容，MySQL中可以定義的二進(jìn)制字符串類型如表3.5所示。類

型

名

稱存

儲

需

求（實際字符數(shù)L）最

大

字

符

數(shù)（m≤）binary(m)m255varbinary(m)m+165535tinyblob(m)L+1255blob(m)L+265535mediumblob(m)L+316777215longblob(m)L+44294967295或4GB說明：（1）binary和varbinary類型類似于char和varchar，不同點在于它們存儲的是二進(jìn)制字節(jié)形式的字符串。（2）blob是一個二進(jìn)制大對象，用來存儲可變數(shù)量的數(shù)據(jù)，它又分tinyblob、blob、mediumblob和longblob幾種，每種可容納值的最大長度不同。二進(jìn)制字符串類型2.二進(jìn)制字符串的性質(zhì)【例3.7】創(chuàng)建表，測試二進(jìn)制字符串?dāng)?shù)據(jù)的性質(zhì)。1）創(chuàng)建測試表。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( b1 binary(8), b2 varbinary(8), b3 blob);二進(jìn)制字符串類型2）插入整數(shù)數(shù)值。INSERTINTOtestVALUES(13,13,13);SELECTBIN(b1+0),BIN(b2+0),BIN(b3+0)FROMtest; #（a）SELECTLENGTH(b1),LENGTH(b2),LENGTH(b3)FROMtest; #（b）說明：（a）專用于查看對應(yīng)列內(nèi)容的二進(jìn)制串，可通過Navicat查看表中數(shù)據(jù)，運行結(jié)果如圖3.14所示。

其中，blob數(shù)據(jù)不是直接可見的，只顯示了類型和字節(jié)數(shù)，13的二進(jìn)制1101占一個字節(jié)，blob在此基礎(chǔ)上加1，即2字節(jié)存儲。所有二進(jìn)制字符串型數(shù)據(jù)都無法直接通過Navicat表查看器更改。（b）用于查看二進(jìn)制字符串列內(nèi)容占用的字節(jié)長，運行結(jié)果如圖3.15所示。二進(jìn)制字符串類型3.二進(jìn)制字符串存儲不同類型【例3.7續(xù)】采用上例test表，測試二進(jìn)制字符串型對各種不同類型數(shù)據(jù)的存儲性質(zhì)。1）插入字符串、數(shù)值。USEmydb;DELETEFROMtest;INSERTINTOtestVALUES('hello','hello','hello'); #（a）INSERTINTOtestVALUES('2022','中國','2022中國夢'); #（b）INSERTINTOtestVALUES('mysql8','mysql','123mysql'); #（c）INSERTINTOtestVALUES(3.1416,2.718e03,3.1415926); #（d）SELECTLENGTH(b1),LENGTH(b2),LENGTH(b3)FROMtest; #（e）二進(jìn)制字符串類型說明：（a）對于字符型數(shù)據(jù)，binary依舊是補(bǔ)足至定義長度，varbinary和blob按照實際字節(jié)數(shù)（一個字符占一個字節(jié)）存儲。（b）儲存一個漢字要占用16個二進(jìn)制位，即2字節(jié)，varbinary和blob在其上加1存儲，故一個漢字要用3個字節(jié)存儲，這樣“'2022中國夢'”一共占用4＋3×3＝13個字節(jié)。（c）英文字符、數(shù)字、空格也都是占用一個字節(jié)。（d）若直接插入浮點數(shù)，MySQL將其每個數(shù)字位作為一個字符看待，若數(shù)值是以科學(xué)記數(shù)法格式給出，MySQL會先計算其值，然后再以同樣規(guī)則存儲。test表記錄如圖3.16所示。（e）查詢得到test表中各數(shù)據(jù)的長度（字節(jié)）如圖3.17所示。

二進(jìn)制字符串類型2）對于長度超出定義的情形，binary和varbinary類型都會報錯，拒絕插入。INSERTINTOtest(b1)VALUES(3.1415926); #（a）INSERTINTOtest(b2)VALUES('中國夢'); #（b）INSERTINTOtest(b2)VALUES('2022beijing'); #（c）執(zhí)行結(jié)果出錯，出錯信息分別如圖3.18（a）、（b）、（c）所示。

第3章

數(shù)據(jù)類型——枚舉類型和集合類型01枚舉類型和集合類型枚舉類型和集合類型枚舉類型：enum（1）定義enum('值1','值2','值3'...,'值n')定義需要enum需要設(shè)置成員表，成員為字符串（尾部空格將自動被刪除），即使成員是數(shù)字，也必須是加引號的字符串。成員之間用“,”分隔。（2）存儲enum類型存儲不是成員的字符串，而是成員的索引編號，定義1～255個成員的枚舉索引編號需要1個字節(jié)存儲；對于256～65535個成員，索引編號需要2個字節(jié)存儲。例如，“專業(yè)”枚舉定義enum('計算機(jī)','通信工程','人工智能')，該列可以取的值和對應(yīng)的索引編號如表3.6所示。成員索引編號NULLNULL“0'計算機(jī)'1'通信工程'2'人工智能'302集合類型集合類型集合類型：set（1）定義SET('值1','值2','值3'...,'值n')定義set類型基本形式與enum類型一樣。set類型成員不能為空，最小需要一個成員，最多包含64個成員。實際內(nèi)容這些成員構(gòu)成的組合，1～8成員的集合占1個字節(jié)，9～16成員的集合占2個字節(jié)，17～24成員的集合占3個字節(jié)，25～32成員的集合占4個字節(jié)，33～64成員的集合占8個字節(jié)。（2）存儲set類型存儲不是成員的字符串，而是二進(jìn)制位信息。例如：“興趣”集合定義set('唱歌','跳舞','智力游戲','足球運動','書法')?！芭d趣”成員和存儲值表示如表3.7所示。成員二進(jìn)制值十進(jìn)制值'唱歌'11'跳舞'102'智力游戲'1004'足球運動'10008'書法'1000016集合類型【例3.8】enum類型與set類型的應(yīng)用。1）創(chuàng)建enum類型與set類型測試表。USEmydb;DROPTABLEIFEXISTSxs;CREATETABLExs(

姓名 char(4),

專業(yè) enum('計算機(jī)','通信工程','人工智能'),

總學(xué)分 int,

興趣 set('唱歌','跳舞','智力游戲','足球運動','書法'));2）插入記錄。INSERTINTOxs VALUES('劉文','計算機(jī)',32,'智力游戲'); #（a）INSERTINTOxs VALUES('周和進(jìn)','通信工程',30,'足球運動,書法'); #（b）INSERTINTOxs VALUES('劉一龍',2,32,'足球運動,唱歌'); #（c）INSERTINTOxs VALUES('顧紅',1,34,6); #（d）集合類型說明：（a）刪除'智力游戲'字符串后的空格后再匹配興趣成員。（b）'足球運動,書法'包含兩個興趣成員，“，”之間不能包含空格。（c）（enum）專業(yè)枚舉值'通信工程'采用索引編號。'足球運動,唱歌'興趣沒有按照定義順序，但仍然按照定義時的先后順序保存。（d）（set）興趣值'跳舞'和'智力游戲'采用成員對應(yīng)十進(jìn)制值相加（2+4=6）表示。6不能采用二進(jìn)制表示（0b110）。集合類型3）查詢記錄。SELECT*FROMxs; #（a）SELECT*FROMxsWHERE專業(yè)='計算機(jī)'; #（b）SELECT*FROMxsWHERE專業(yè)=1; #（b）SELECT*FROMxsWHERE興趣='唱歌,足球運動'; #（c）SELECT*FROMxsWHERE興趣=9; #（c）SELECT*FROMxsWHERE興趣='足球運動,唱歌'; #（d）運行結(jié)果如圖3.19所示。

集合類型說明：（a）顯示所有記錄，存儲的是數(shù)字，顯示的是成員字符串。（b）enum類型查詢內(nèi)容表達(dá)既可以是成員字符串，又可以是數(shù)字。（c）set類型查詢內(nèi)容表達(dá)可以是按定義順序包含的成員字符串，或者成員對應(yīng)十進(jìn)制值相加，不能采用二進(jìn)制表示。（d）set類型查詢內(nèi)容表達(dá)不是按定義順序包含的成員字符串，找不到記錄。集合類型4）更新記錄。UPDATExsSET興趣=(興趣|3)WHERE專業(yè)='通信工程'; #（a）SELECT*FROMxs; #（a）UPDATExsSET興趣=(興趣&0b11110)WHERE專業(yè)='通信工程'; #（b）SELECT*FROMxs; #（b）運行結(jié)果如圖3.20所示。說明：（a）set類型加入一個成員，只要將原來的值位或（|）該成員的數(shù)字。（b）set類型減去一個成員，只要將原來的值位與（&）該成員二進(jìn)制為0其他成員為1（或者對應(yīng)的十進(jìn)制數(shù)）。第3章

數(shù)據(jù)類型——JSON和空間數(shù)據(jù)類型及實例01JSON數(shù)據(jù)類型1.JSON對象2.JSON數(shù)組JSON數(shù)據(jù)類型1.JSON對象JSON類型的數(shù)據(jù)是以JSON對象的形式提供的，一個標(biāo)準(zhǔn)的JSON對象包含一組鍵值對，用逗號分隔，以“{”和“}”字符括起來：{"鍵1":"值1",...}創(chuàng)建JSON對象采用JSON_OBJECT系統(tǒng)函數(shù)：JSON_OBJECT(鍵1,值1,鍵2,值2,...)其中，鍵與值必須成對出現(xiàn)，且不能為空。一旦出現(xiàn)鍵名為空或參數(shù)總數(shù)為奇數(shù)的情形，系統(tǒng)就會報錯。創(chuàng)建JSON對象和存儲遵循如下一些基本的規(guī)則：（1）鍵名（無論中英文）都必須以雙引號（"）括起來。（2）鍵值是字符串型（無論中英文）必須加雙引號（"）或單引號（'）。（3）鍵值是數(shù)值型（整數(shù)或?qū)崝?shù)類型），可以以數(shù)值或字符串兩種格式存儲。當(dāng)以數(shù)值存儲時，直接寫出數(shù)值即可；而以字符串存儲時，鍵值需加雙引號或單引號。（4）鍵值是日期時間型，可以用兩種方式給出：一種是直接以字符串形式寫出；另一種是通過系統(tǒng)內(nèi)置的日期時間函數(shù)獲得，不同函數(shù)返回的日期時間字符串的格式不同，均以字符串的形式存儲到JSON對象中。JSON數(shù)據(jù)類型【例3.9】JSON對象的創(chuàng)建和存儲。1）首先創(chuàng)建一個含JSON類型列的測試表。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( j json);2）向表中插入一組JSON對象。INSERTINTOtestVALUES(JSON_OBJECT("帳號","b02020622","金額",1758.82,"時間",NOW())); INSERTINTOtestVALUES('{"帳號":'y06111577',"金額":63888,"時間":"2020-05-2814:01:21"}'); INSERTINTOtestVALUES(JSON_OBJECT("Accounts",'k08439558',"amount","456.5","CTime",CURTIME())); SELECT*FROMtest;JSON數(shù)據(jù)類型運行結(jié)果如圖3.21所示。JSON數(shù)據(jù)類型2.JSON數(shù)組當(dāng)需要同時存儲很多JSON對象或者標(biāo)量值時，可將它們集中在一起構(gòu)造出一個數(shù)組數(shù)據(jù)結(jié)構(gòu)來統(tǒng)一管理。JSON數(shù)組包含多個值，這些值由逗號分隔：JSON_ARRAY(值,...)JSON數(shù)組元素顯示包含在“[”和“]”中：[{鍵1":"值1",...},...]JSON數(shù)據(jù)類型【例3.9續(xù)】插入一些不同的標(biāo)量值與JSON對象一起作為JSON數(shù)組的元素，并嘗試嵌套的結(jié)構(gòu)，插入上例為j（JSON類型）列test表。向插入測試表JSON數(shù)組的元素：USEmydb;DELETEFROMtest;INSERTINTOtest VALUES(JSON_ARRAY("1B0601", JSON_OBJECT("商品","碭山梨10斤箱裝大果"), 19.90,"2020-07-2912:18:29.698" ) );INSERTINTOtest VALUES(JSON_ARRAY (JSON_OBJECT("類別",JSON_ARRAY("3C","海參")), JSON_ARRAY("A3","大連凱洋世界海鮮有限公司") ) );SELECT*FROMtest;JSON數(shù)據(jù)類型運行結(jié)果如圖3.22所示。02空間數(shù)據(jù)類型1.基于位置信息信息轉(zhuǎn)化（地理信息系統(tǒng)）2.幾何值類型介紹3.幾何值類型表的創(chuàng)建、列插入和查詢4.集合類5.空間數(shù)據(jù)內(nèi)部格式（WKB）空間數(shù)據(jù)類型1.基于位置信息信息轉(zhuǎn)化（地理信息系統(tǒng)）（1）平面直角坐標(biāo)系二維平面其中的點表示為P（x，y），其中x是點P到x軸上的原點（0,0）的距離，而y是與y軸上的原點（0,0）的距離。因此，如果知道一個點的x，y，則可以將其定位在坐標(biāo)面上。計算P點到原點的距離為x和y平方和的平方根。如圖3.23所示。空間數(shù)據(jù)類型（2）球面直角坐標(biāo)系球面遵循大地參考系來繪制表面上的位置，大地參考系P（x，y）基于經(jīng)度和緯度值。地球上某個位置（點）的緯度是它與赤道的度數(shù)（沿地球旋轉(zhuǎn)軸測量時）。地球上某個位置的經(jīng)度是距本初子午線的度數(shù)（沿赤道測量時）。緯度和經(jīng)度都是從地球中心測量的。緯度的最大值為90°，最小值為-90°。同樣，經(jīng)度的最大值為180°，而最小值為-180°。如圖3.24所示?？臻g數(shù)據(jù)類型表面上的點（P（x，y）可以基于其所在的坐標(biāo)系而具有不同的含義，需要一個與點簽名關(guān)聯(lián)的坐標(biāo)參考系統(tǒng)標(biāo)識符（即SRID），SRID是唯一整數(shù)。MySQL數(shù)據(jù)庫附帶了5000多個這樣的坐標(biāo)系，可以在系統(tǒng)數(shù)據(jù)庫information_schema中使用以下查詢：SELECT*FROMST_SPATIAL_REFERENCE_SYSTEMS;其中，SRS_NAME是空間參考系統(tǒng)名稱，SRS_ID是空間參考系統(tǒng)的ID標(biāo)識。查詢指定SRS_ID的空間參考系統(tǒng)信息，SQL語句如下：SELECT*FROMinformation_schema.ST_SPATIAL_REFERENCE_SYSTEMSWHERESRS_ID=4326\G;空間數(shù)據(jù)類型2.幾何值類型介紹單一幾何值類型是一類比較簡單的空間數(shù)據(jù)類型，包括：POINT（點）、LINESTRING（線）、POLYGON（多邊形）、GEOMETRY，其中GEOMETRY是通用的幾何類型，可以存儲任何點、線和多邊形值。（1）單一幾何類POINT:點，有一個坐標(biāo)值，沒有長度、面積、邊界。點用經(jīng)緯度表示，經(jīng)度（longitude）在前，緯度（latitude）在后，用空格分隔。例如，表示一個點：POINT(1520)。LINESTRING:線，由一系列點連接而成。如果線從頭至尾沒有交叉，那就是簡單的（simple）；如果起點和終點重疊，那就是封閉的（closed）。點與點之間用逗號分隔，一個點中的經(jīng)緯度用空格分隔，與POINT格式一致。例如，表示一條線：LINESTRING(00,1010,2025,5060)，其中，點坐標(biāo)對由逗號分隔。POLYGON:多邊形，可以是一個實心平面形，即沒有內(nèi)部邊界，也可以有空洞，類似紐扣。由一個表示外部邊界的LineString和0個或多個表示內(nèi)部邊界的LineString組成，最簡單的就是只有一個外邊界的情況。例如，表示一個外環(huán)和一個內(nèi)環(huán)的多邊形：POLYGON((00,100,1010,010,00),(55,75,77,57,55))?？臻g數(shù)據(jù)類型（2）基類：GEOMETRYGEOMETRY是所有擴(kuò)展中類型的基類，POINT,LINESTRING,POLYGON等都是GEOMETRY的子類。GEOMETRY有一些屬性，這些屬性是所有其他幾何類的共有屬性。type:幾何值類型。如POINT,LINESTRING,...。srid:幾何對象的坐標(biāo)空間的空間坐標(biāo)系統(tǒng)。coordinates:坐標(biāo)值。interior,boundary,exterior:interior是幾何對象所展空間的部分，boundary是幾何對象的邊界，exterior是幾何對象未占有的空間。mbr:能夠覆蓋幾何對象的最小矩形，可以想象成信封，它由幾何對象中最大最小的坐標(biāo)值組合而成：((minxminy,maxxminy,maxxmaxy,minxmaxy,minxminy))simple/nonsimple:幾何對象是否簡單closed/notclosed:幾何對象是否封閉dimension:維度數(shù)（POINT:0,LINESTRING:1,POLYGON:2）。（3）集合類?？臻g數(shù)據(jù)類型3.幾何值類型表的創(chuàng)建、列插入和查詢在數(shù)據(jù)庫表中可以根據(jù)需要定義表中列的空間類型，下面通過實例進(jìn)行說明?！纠?.10】單一幾何值類型的存儲。（1）創(chuàng)建包含幾種單一幾何值類型的列測試表。USEmydb;DROPTABLEIFEXISTStest;CREATETABLEtest( g1 point, #點 g2 linestring, #線 g3 polygon, #多邊形 g4 geometry #通用幾何型);空間數(shù)據(jù)類型（2）插入空間列數(shù)據(jù)。OGC提供了兩種創(chuàng)建空間對象的數(shù)據(jù)格式：WKT（文本格式）和WKB（WKT的二進(jìn)制表示格式）。通常使用WKT文本格式表示幾何值對象。MySQL使用ST_GeomFromText函數(shù)將WKT文本轉(zhuǎn)換為幾何值對象。例如，向表中插入幾個幾何類型對象的數(shù)據(jù)：INSERTINTOtest(g1,g2,g3,g4)VALUES( ST_GeomFromText('POINT(11)'), ST_GeomFromText('LINESTRING(00,11,22)'), ST_GeomFromText('POLYGON((00,100,1010,010,00),(55,75,77,57,55))'), ST_GeomFromText('LINESTRING(00,1010,2025,5060)'));SELECT*FROMtest;geometry類型的列接受任意類型的單一幾何值，查看記錄結(jié)果如圖3.25所示?？臻g數(shù)據(jù)類型4.集合類集合類包括MULTIPOINT,MULTILINESTRING,MULTIPOLYGON,GEOMETRYCOLLECTION，是多個POINT、LINESTRING或POLYGON組合在一起而成。例如：一個三點值的多點：MULTIPOINT(00,2020,6060)。具有兩個LINESTRING值的多線：MULTILINESTRING((1010,2020),(1515,3015))。一個具有兩個多邊形值的多多邊形，其中第1個多邊形又由內(nèi)外兩個環(huán)組成：MULTIPOLYGON( ((11,51,55,15,11),(22,23,33,32,22)), ((63,92,94,63)))空間數(shù)據(jù)類型5.空間數(shù)據(jù)內(nèi)部格式（WKB）在MySQL內(nèi)部采用二進(jìn)制的WKB格式來表示空間數(shù)據(jù)，它包含了幾何WKB信息的BLOB值表示的二進(jìn)制流。例如ST_GeomFromText('POINT(1-1)')WKB格式如下：000000000101000000000000000000f03f000000000000f0bf其組成結(jié)構(gòu)如表3.8所示。組

成長

度值SRID400000000（4字節(jié)整數(shù)）字節(jié)順序101WKB類型401000000X坐標(biāo)8000000000000F03F（IEEE754標(biāo)準(zhǔn)雙精度數(shù)字）Y坐標(biāo)8000000000000F0BF（IEEE754標(biāo)準(zhǔn)雙精度數(shù)字）空間數(shù)據(jù)類型說明：（1）字節(jié)順序指示器是1表示小端字節(jié)存儲或0表示大端字節(jié)存儲，小端字節(jié)順序和大端字節(jié)順序也分別稱為網(wǎng)絡(luò)數(shù)據(jù)表示（NDR）和外部數(shù)據(jù)表示（XDR）。（2）WKB類型指示幾何類型的代碼。MySQL使用1到7的值來表示point、linestring、polygon、multipoint、multilinestring、multipolygon和

geometrycollection。（3）X坐標(biāo)和Y坐標(biāo)笛卡爾坐標(biāo)存儲在空間參考系統(tǒng)的長度單位中，X值在X坐標(biāo)中，Y值在Y坐標(biāo)中。軸的方向是由空間參考系統(tǒng)指定的。地理坐標(biāo)存儲在空間基準(zhǔn)系統(tǒng)的角度單元中，經(jīng)度在X坐標(biāo)中，緯度在Y坐標(biāo)中。軸向和子午線是由空間基準(zhǔn)系統(tǒng)指定的。每個坐標(biāo)都表示為雙精度值。第4章

數(shù)據(jù)庫及表結(jié)構(gòu)設(shè)計——數(shù)據(jù)庫及基本操作MySQL+實用教程（第4版）01系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫在安裝MySQL8.0后，就產(chǎn)生了4個系統(tǒng)數(shù)據(jù)庫：包括information_schema、mysql、performance_schema和sys，在登錄MySQL服務(wù)器后，可查看MySQL系統(tǒng)已有的數(shù)據(jù)庫：SHOWDATABASES;下面簡單介紹4個系統(tǒng)數(shù)據(jù)庫的作用,另外還有2個實例數(shù)據(jù)庫。（1）information_schema數(shù)據(jù)庫它保存了mysql服務(wù)器所有數(shù)據(jù)庫的信息。比如數(shù)據(jù)庫的名稱、數(shù)據(jù)庫的表、訪問權(quán)限、數(shù)據(jù)庫表的數(shù)據(jù)類型、數(shù)據(jù)庫索引的信息等等。有時用于表述該信息的其他術(shù)語包括“數(shù)據(jù)字典”和“系統(tǒng)目錄”。（2）mysql數(shù)據(jù)庫它是MySQL的核心數(shù)據(jù)庫，類似于SQLServer中的master表，主要負(fù)責(zé)存儲數(shù)據(jù)庫的用戶、權(quán)限設(shè)置、關(guān)鍵字等MySQL自己需要使用的控制和管理信息。（3）performance_schema數(shù)據(jù)庫主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)，可用于監(jiān)控服務(wù)器在一個較低級別的運行過程中的資源消耗、資源等待等情況。（4）sys數(shù)據(jù)庫庫中所有的數(shù)據(jù)來自performance_schema。目標(biāo)是把performance_schema的復(fù)雜度降低，讓DBA能更好的閱讀這個庫里的內(nèi)容，了解數(shù)據(jù)庫的運行情況。（5）sakila和world實例數(shù)據(jù)庫如果用戶在安裝MySQL時，在“ChoosingaSetupType”（安裝類型）頁選擇“Examplesandtutorials：”（實例和教程）項，則系統(tǒng)中還會看到另外2個實例數(shù)據(jù)庫sakila和world。02數(shù)據(jù)庫創(chuàng)建和修改1.數(shù)據(jù)庫的創(chuàng)建和修改2.數(shù)據(jù)庫刪除數(shù)據(jù)庫創(chuàng)建和修改1.數(shù)據(jù)庫的創(chuàng)建和修改創(chuàng)建MySQL8數(shù)據(jù)庫：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]數(shù)據(jù)庫名[DEFAULT]CHARACTERSET[=]字符集名|[DEFAULT]COLLATE[=]排序規(guī)則名|DEFAULTENCRYPTION[=]{'Y'|'N'}數(shù)據(jù)庫名后面描述當(dāng)前定義的數(shù)據(jù)庫的屬性，包括：字符集編碼、字符排序規(guī)則和是否加密，它被存儲在數(shù)據(jù)字典中。數(shù)據(jù)庫名前加文件夾路徑?！纠?.1】創(chuàng)建網(wǎng)上商城數(shù)據(jù)庫（emarket），采用gbk字符集和gbk_bin排序規(guī)則。CREATEDATABASEIFNOTEXISTSemarket DEFAULTCHARACTERSETgbk DEFAULTCOLLATEgbk_bin;語句執(zhí)行后，在MySQL8.0安裝的文件夾（默認(rèn)為：C:\ProgramData\MySQL\MySQLServer8.0\Data）下就會生成一個“數(shù)據(jù)庫名”的子目錄“\emarket”，此后在該數(shù)據(jù)庫中創(chuàng)建的所有對象（包括表及其數(shù)據(jù)）都會以文件存儲在該子目錄下。數(shù)據(jù)庫創(chuàng)建和修改2.數(shù)據(jù)庫刪除數(shù)據(jù)庫創(chuàng)建后，如果需要修改數(shù)據(jù)庫的參數(shù)，可以使用“ALTERDATABASE...”語句，選項與CREATEDATABASE相同，這里就不再重復(fù)?？梢酝ㄟ^下列語句顯示數(shù)據(jù)庫屬性。SHOWCREATEDATABASE數(shù)據(jù)庫名已經(jīng)創(chuàng)建的數(shù)據(jù)庫需要刪除，使用下列語句。DROP{DATABASE|SCHEMA}[IFEXISTS]數(shù)據(jù)庫名第4章

數(shù)據(jù)庫及表結(jié)構(gòu)設(shè)計——創(chuàng)建表結(jié)構(gòu)創(chuàng)建表結(jié)構(gòu)1.創(chuàng)建表結(jié)構(gòu)（1）使用CREATETABLE語句創(chuàng)建表結(jié)構(gòu)CREATETABLE表名[(

列定義, ... [表約束])][表選項][表結(jié)構(gòu)或表記錄源]列定義:列名列數(shù)據(jù)類型[長度和小數(shù)][空值][虛擬][鍵][注釋][默認(rèn)值][其他屬性]創(chuàng)建表結(jié)構(gòu)（2）Navicat工具創(chuàng)建表結(jié)構(gòu)【例4.2】采用Navicat創(chuàng)建emarket數(shù)據(jù)庫中商品分類表（category）表結(jié)構(gòu)。在創(chuàng)建的連接下雙擊emarket數(shù)據(jù)庫，再選擇“表”，按右鍵，在快捷菜單中單擊“新建表”，出現(xiàn)創(chuàng)建表窗口，輸入內(nèi)容如圖4.1所示。單擊“保存”按鈕，在出現(xiàn)的“表名”對話框中輸入“category”，單擊“確定”，category表創(chuàng)建完成。在emarket數(shù)據(jù)庫的“表”下就會出現(xiàn)“category”。創(chuàng)建表結(jié)構(gòu)（3）顯示表屬性在表創(chuàng)建后，可以通過下面語句顯示指定表的創(chuàng)建屬性：SHOWCREATETABLE表名通過下面語句顯示表屬性或者表指定列的屬性：DESC表名[列名]【例4.2續(xù)】顯示category表屬性。USEemarket;SHOWCREATETABLEcategory; #（a）DESCcategory; #（b）DESCcategory類別編號; #（c）顯示category表“類別編號”列屬性創(chuàng)建表結(jié)構(gòu)顯示結(jié)果分別如圖4.2（a）、（b）、（c）所示。說明：（a）以創(chuàng)建表語句方式顯示category表結(jié)構(gòu)，雖然category表結(jié)構(gòu)并不是語句方式創(chuàng)建的。通過該方式，可以了解采用Navicat創(chuàng)建表結(jié)構(gòu)與創(chuàng)建表語句方式創(chuàng)建表結(jié)構(gòu)的對應(yīng)關(guān)系。（b）以列方式顯示category表結(jié)構(gòu)。（c）顯示category表指定列結(jié)構(gòu)。創(chuàng)建表結(jié)構(gòu)2.刪除表表結(jié)構(gòu)創(chuàng)建后，可以修改表結(jié)構(gòu)。有了表結(jié)構(gòu)，就可以增加、修改和刪除表記錄。表記錄的操作將在下一章系統(tǒng)介紹。下列語句可以刪除表，刪除表后表結(jié)構(gòu)和表記錄均不存在，一般用于重新定義表。DROPTABLEIFEXISTS表名;01列及其常用屬性1.列名2.列數(shù)據(jù)類型3.長度和小數(shù)4.空值限制：NOTNULL/NULL5.虛擬6.鍵7.注釋：COMMENT列及其常用屬性1.列名列又稱字段，列名又稱字段名。列名必須符合標(biāo)識符規(guī)則，中英文均可，長度不能超過64個字符，而且在表中要唯一。如果采用MySQL保留字必須用單引號括起來。列名一般可以采用英文、漢語拼音、中文等，采用中文作為列名，閱讀方便；但從編程角度，英文、漢語拼音輸入容易些。本書前面介紹MySQL基礎(chǔ)知識采用中文作為列名，后面實習(xí)系統(tǒng)開發(fā)時則采用英文列名。列及其常用屬性2.列數(shù)據(jù)類型下面說明數(shù)據(jù)類型選擇原則：（1）不需要表達(dá)小數(shù)用整數(shù)，無正負(fù)用無符號（unsigned），以可能存儲的最大值選擇整數(shù)類型。（2）包含小數(shù)但精度要求高不能選擇浮點數(shù)而要選擇定點數(shù)。（3）保存字符個數(shù)差別較大選擇變長，需要進(jìn)行字符運算即使保存全面為數(shù)字也要選擇字符型，例如學(xué)號可能包含專業(yè)入學(xué)時間等信息。需要保存的多媒體信息（例如圖形、聲音、視頻等）用二進(jìn)制字符型。（4）需要進(jìn)行日期運算的選擇日期型，需要包含時間的選擇日期時間型。（5）內(nèi)容規(guī)范的字符串選擇枚舉型，同時包含不確定規(guī)范信息選擇集合型。列及其常用屬性3.長度和小數(shù)（1）字符類型（char和varchar）長度為本列最大存放的字符個數(shù)，一個英文和一個中文均算作1個字符，例如：存放“abc中文系統(tǒng)”為7個字符。默認(rèn)長度為255。列占用的空間大小與列采用的字符集有關(guān)。如果采用gbk字符集，“abc中文系統(tǒng)”占用11個字節(jié)，因為gbk字符集英文占用1個字節(jié)，漢字占用2個字節(jié)；如果采用utf8mb4字符集，每一個字符占用4個字節(jié)，這樣“abc中文系統(tǒng)”就占用28個字節(jié)。（2）浮點數(shù)類型（float、double）如果不指定長度和小數(shù)位，默認(rèn)為整數(shù)，在最大數(shù)據(jù)范圍內(nèi)，數(shù)據(jù)超過有效數(shù)字位數(shù)的部分低位為0；如果指定長度和小數(shù)位，只要在數(shù)據(jù)類型允許的范圍內(nèi)，整數(shù)部分顯示的位數(shù)=長度-小數(shù)位數(shù)-1。（3）定點數(shù)類型（decimal）用于保存準(zhǔn)確數(shù)字?jǐn)?shù)據(jù)，長度和小數(shù)位可以根據(jù)用戶需要指定。例如：支付金額可能達(dá)到xxxxx.xx，數(shù)據(jù)不大，但有效位數(shù)超過6位，為了準(zhǔn)確表達(dá)，可以采用定點數(shù)類型decimal。支付金額 decimal(8.2)其他數(shù)據(jù)類型指定長度和小數(shù)位沒有意義。列及其常用屬性4.空值限制：NOTNULL/NULLNOTNULL表示列內(nèi)容不允許為空，NULL或者不寫此項表示允許為空。例如：商品名稱 varchar(32) NOTNULL商品圖片 blob NULLNavicat中勾選“不是null”項，表示非空（NOTNULL），否則表示允許空（NULL）。說明：（1）對于字符型，NULL并不是空格；對于數(shù)值型和位（bit）型，NULL并不是0。NULL值是一個值，是沒有賦值的值，可以與NULL進(jìn)行等于（=）比較。（2）如果列指定為“NULL”或者沒有指定，當(dāng)增加一行時，即使該列不指定值也沒有默認(rèn)值，該行也能夠成功保存，并且該列保存為NULL值。（3）如果列指定為“NOTNULL”，當(dāng)增加一行時，在非嚴(yán)格模式下，列名表NOTNULL列可