數(shù)據(jù)庫原理與應(yīng)用（MySQL版） 課件 第4章 SQL語言基礎(chǔ)及基本表的定義

第4章SQL語言基礎(chǔ)及基本表的定義數(shù)據(jù)庫原理與應(yīng)用1本章內(nèi)容4.1SQL語言概述4.2數(shù)據(jù)類型4.3基本表24.1SQL語言概述4.1.1SQL語言的發(fā)展4.1.2SQL語言特點4.1.3SQL語言功能概述34.1.1SQL語言的發(fā)展SQL：StructuredEnglishQueryLanguage。1986年10月：美國ANSI頒布最早SQL標(biāo)準(zhǔn)。1989年4月，ISO提出了具有完整性特征的SQL，SQL-89。1992年8月：ISO和ANSI共同頒布了SQL的新標(biāo)準(zhǔn)——SQL-92或稱SQL2。1999年又頒布了新的SQL標(biāo)準(zhǔn)，稱為SQL-99或SQL3。44.1.2SQL語言特點一體化高度非過程化簡潔可以多種方式使用直接以命令方式交互使用嵌入到程序設(shè)計語言中使用54.1.3SQL語言功能概述SQL功能動詞數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)更改INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE、DENY6本章內(nèi)容4.1SQL語言概述4.2數(shù)據(jù)類型4.3基本表74.2數(shù)據(jù)類型4.2.1數(shù)值類型1.整數(shù)類型2.小數(shù)類型4.2.2字符串類型4.2.3日期時間類型81.整數(shù)類型MySQL整數(shù)類型說

明TINYINT存儲從–27（-128）到27?1（127）范圍的整數(shù)SMALLINT存儲從–215(–32768)到215?1(32767)范圍的整數(shù)，無符號整數(shù)的范圍是0到65535數(shù)MEDIUMINT存儲從–223(–8388608)到223?1(8388607)范圍的整數(shù)，無符號整數(shù)的范圍是0到16777215INT[(n)]或INTEGER[(n)]存儲從–231(–214483648)到231?1(2147483647)范圍的整數(shù)，無符號的范圍是0到4294967295BIGINT存儲從–263到263?1范圍的整數(shù)，無符號整數(shù)的范圍是0到264?19說明MySQL支持在INT類型中指定整數(shù)值的顯示位數(shù)，其方法是在類型關(guān)鍵字后面添加括號，并在括號內(nèi)指定整數(shù)值的顯示位數(shù)。例如，INT(5)表示顯示位數(shù)為5。102.小數(shù)類型11數(shù)據(jù)類型說

明解釋FLOAT[(M,D)]單精度浮點數(shù)M表示總共的數(shù)字位數(shù)，取值范圍1～255；D表示小數(shù)點后的數(shù)字位數(shù)，取值范圍1～30。如果省略了M和D，則FLOAT和DOUBLE將被保存為硬件所支持的最大精度。DOUBLE[(M,D)]雙精度浮點數(shù)DECIMAL[(M,D)]或DEC[(M,D)]定點小數(shù)M為精度，指定可以存儲的十進(jìn)制數(shù)字的最大位數(shù)，范圍為1到65。默認(rèn)值為10D為小數(shù)位數(shù)，范圍為0到30。D<=M。默認(rèn)D值為04.2.2字符串類型1.文本字符串和二進(jìn)制字符串2.ENUM類型3.SET類型121.文本字符串和二進(jìn)制字符串MySQL支持兩類字符串?dāng)?shù)據(jù)：文本字符串：進(jìn)行區(qū)分或不區(qū)分大小寫的串的比較，也可以進(jìn)行模式匹配查找。二進(jìn)制字符串：二進(jìn)制字符串類型的字段主要用于存儲由“0”和“1”組成的字符串。13文本字符串類型類型說

明CHAR[(M)]固定長度的字符串類型，M表示字符串的最大長度，取值范圍為0～255，默認(rèn)值為1VARCHAR(M)可變長度的字符串類型，M表示字符串的最大長度，取值范圍為0～65535TINYTEXT短文本字符串，最大可存儲28-1（255）字節(jié)TEXT長文本字符串，最大可存儲216-1（65535）字節(jié)MEDIUMTEXT中等長度文本字符串，最大可存儲224-1（16777215）字節(jié)LONGTEXT極大文本字符串，最大可存儲232-1（4294967295）字節(jié)14二進(jìn)制字符串類型類型說

明BINARY[(M)]固定長度二進(jìn)制字符串，M表示字符串的最大長度，取值范圍為0～255。M可以省略，默認(rèn)值為1VARBINARY(M)可變長度二進(jìn)制字符串，M表示字符串的最大長度，取值范圍為0～65535BIT(M)M位二進(jìn)制字符串，取值范圍是1～64。為與文本字符串區(qū)分，二進(jìn)制字符串在字符串前要加‘b’。TINYBLOB短二進(jìn)制字符串，最大可存儲28-1（255）字節(jié)BLOB長二進(jìn)制字符串，最大可存儲216-1（65535）字節(jié)MEDIUMBLOB中等長度二進(jìn)制字符串，最大可存儲224-1（16777215）字節(jié)LONGBLOB極大二進(jìn)制字符串，最大可存儲232-1（4294967295）字節(jié)15說明SQL中的字符串常量一般用單引號括起來。比如'計算機(jī)學(xué)院'。定長字符串類型表示不管實際字符需要多少空間，系統(tǒng)分配固定地字節(jié)數(shù)。如果空間未被占滿，系統(tǒng)將自動用空格填充?？勺冮L字符類型表示按實際字符需要的空間進(jìn)行分配。162.ENUM類型是一個字符串對象，由若干個子字符串常量的枚舉值構(gòu)成。當(dāng)創(chuàng)建表時，如果為列指定了ENUM類型，則該列的值必須是ENUM列表中指定的某個值。在創(chuàng)建表時為列指定ENUM類型的語法格式為：

字段名ENUM('值1','值2',…,'值n')17說明ENUM枚舉列表中最多可以有65535個成員。ENUM類型的字段只允許一次從枚舉集合中選取一個值。18說明示例：有如下列定義C1ENUM('a','b','c','d')C1列的值只能是{'a','b','c','d'}四個值中的某一個。ENUM列表中的枚舉值在內(nèi)部有一個索引值（用整數(shù)表示），從1開始編號。193.SET類型是字符串對象，可以有零或多個值。在創(chuàng)建表時為列指定SET類型的語法格式為：SET('值1','值2',…,'值n')SET對象中最多可以有64個值。SET與ENUM最大的區(qū)別：SET一次可以選取多個成員，ENUM一次只能選一個。20示例有如下列定義：C1SET('a','b','c','d')則C1列的值可以是：

'a'、a,b'、'a,b,d'、'a,b,c,d'、...21說明如果在SET類型的字段中插入的值有重復(fù)，則MySQL自動刪除重復(fù)的值。在SET類型的字段中插入的值的順序不重要，MySQL會在存入數(shù)據(jù)庫的時候，按照定義的順序顯示。如果插入了不正確的值，默認(rèn)情況下，MySQL將給出錯誤信息，并且不插入該數(shù)據(jù)。224.2.3日期時間類型類型說明YEAR年份類型，取值：1901~2155，0，格式：YYYYDATE日期類型，取值：‘1000-01-01’到‘9999-12-31'。默認(rèn)格式：YYYY-MM-DDTIME時間類型，取值：‘-838:59:59'到‘838:59:59'。默認(rèn)格式：HH:MM:SSDATETIME日期時間類型，取值：‘1000-01-0100:00:00.000000'到‘9999-12-3123:59:59.999999'。默認(rèn)格式：YYYY-MM-DDHH:MM:SSTIMESTAMP時間戳類型，取值：‘1970-01-0100:00:01'到‘2038-01-1903:14:07'。默認(rèn)格式：YYYYMMDDHHMMSS23注意本質(zhì)上，MySQL日期類型的數(shù)據(jù)是數(shù)值類型。每一個類型都有合法的取值范圍，當(dāng)插入不合法的值時，系統(tǒng)會將“0”值插入到字段中。TIMESTAMP和DATETIME的區(qū)別：DATETIME和所在的時區(qū)無關(guān)；TIMESTAMP和所在的時區(qū)有關(guān)。24本章內(nèi)容4.1SQL語言概述4.2數(shù)據(jù)類型4.3基本表254.3基本表4.3.1基本表的定義4.3.2查看基本表結(jié)構(gòu)4.3.3基本表的維護(hù)264.3.1基本表的定義表是數(shù)據(jù)庫中最重要的對象表用于存儲用戶的數(shù)據(jù)關(guān)系數(shù)據(jù)庫的表是簡單二維表在創(chuàng)建數(shù)據(jù)表之前，使用語句

USE<數(shù)據(jù)庫名>在同一個數(shù)據(jù)庫中，表名不能有重名。271.創(chuàng)建基本表CREATETABLE[IFNOTEXISTS]<表名>(

{<列名><數(shù)據(jù)類型>[列級完整性約束定義

[...n]]}

[表級完整性約束定義][,...n]);

28參數(shù)說明<表名>是所要定義的基本表的名字。<列名>是表中所包含的屬性列的名字。IFNOTEXISTS：只有當(dāng)前數(shù)據(jù)庫中不存在該同名表時才執(zhí)行CREATETABLE操作。標(biāo)準(zhǔn)SQL語言的建表語句沒有該選項，這是MySQL自己的擴(kuò)展。29注意SQL語句在Windows操作系統(tǒng)中不區(qū)分大小寫，在Linux操作系統(tǒng)中區(qū)分大小寫。標(biāo)點符號必須使用英文半角字符。定義表時可以同時定義與表有關(guān)的完整性約束。30完整性約束定義說明只涉及表中一列的約束列級完整性約束定義表級完整性約束定義涉及表中多列的約束表級完整性約束定義31文法描述中常用符號的含義方括號（[]）中的內(nèi)容表示是可選的可出現(xiàn)0次或1次花括號（{}）與省略號（…）一起，表示其中的內(nèi)容可以出現(xiàn)0次或多次。豎杠（｜）表示在多個選項中選擇一個term1|term2|term3：表示在三個選項中任選一項。322.完整性約束PRIMARYKEY：主鍵約束。FOREIGNKEY：外鍵約束。NOTNULL：非空約束。UNIQUE：唯一值約束。DEFAULT：默認(rèn)值約束。AUTO_INCREMENT：自增約束。CHECK：列取值范圍約束。33說明NOTNULL和DEFAULT只能定義在“列級完整性約束定義”其他約束均可在“列級完整性約束定義”和“表級完整性約束定義”處定義。34主鍵（PRIMARYKEY）約束定義主鍵約束的語法格式：

[CONSTRAINT<主鍵約束名>]PRIMARYKEY[(<列名>[,…n])]注意：在列級完整性約束處定義單列主鍵，則可省略“[(<列名>[,…n])]”部分。35外鍵（FOREIGNKEY）約束定義主鍵約束的語法格式：[CONSTRAINT<外鍵約束名>]FOREIGNKEY(<列名>[,…n])］REFERENCES<外表名>(<外表列名>[,…n])[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]注意：在列級完整性約束處定義外鍵，則可以省略“FOREIGNKEY(<列名>)”部分。36外鍵約束參數(shù)說明CONSTRAINT<外鍵約束名>：可選參數(shù)。FOREIGNKEY(<列名>)：指定外鍵的列名。REFERENCES<外表名>(<外表列名>)：指定外鍵參照的表名和列名。ONDELETE/ONUPDATE：父表刪除或更改操作時CASCADE:會自動刪除或更改子表中與之對應(yīng)的記錄。SETNULL:會將子表中與之對應(yīng)記錄的外鍵值自動設(shè)置為NULL。NOACTION：刪除或更改操作將失敗。RESTRICT:與“NOACTION”選項功能相同，且為級聯(lián)選項默認(rèn)值。37非空（NOTNULL）約束非空約束用于限制一個列的取值不能有NULL值。例如，學(xué)生的姓名不能為空值。定義非空約束的語法格式為：

<列名>數(shù)據(jù)類型NOTNULL38唯一值（UNIQUE）約束唯一值約束用于限制列取值不重復(fù)。每個人的身份證號、駕駛證號等均不能有重復(fù)值。定義唯一值約束的語法格式為：

[CONSTRAINT<約束名>]UNIQUE[(<列名>[,…n])]注意：在列級完整性約束處定義單列的唯一值約束，則可省略“[(<列名>[,…n])]”部分。39默認(rèn)值（DEFAULT）約束一個默認(rèn)值約束只能為一個列提供默認(rèn)值。默認(rèn)值約束必須是列級約束。默認(rèn)值約束的定義有兩種形式在定義表時指定默認(rèn)值約束在修改表結(jié)構(gòu)時添加默認(rèn)值約束。在創(chuàng)建表時定義DEFAULT約束語法如下:<列名>數(shù)據(jù)類型DEFAULT默認(rèn)值40自增（AUTO_INCREMENT）約束在一個表中，只能為一個字段使用AUTO_INCREMENT約束。AUTO_INCREMENT約束的字段必須是整數(shù)類型的。在MySQL中，設(shè)置AUTO_INCREMENT約束的字段必須是有主鍵約束或唯一值約束的列。定義自增約束列的語法格式如下：<列名>

數(shù)據(jù)類型

AUTO_INCREMENT41自增（AUTO_INCREMENT）約束例子要將T1表的自增約束的起始值設(shè)置為100：ALTERTABLET1AUTO_INCREMENT=100;要將步長設(shè)置為3：SETAUTO_INCREMENT_INCREMENT=3;42檢查（CHECK）約束用于限制列的取值在指定范圍內(nèi)。人的性別只能是“男”或“女”工資必須大于等于2000（假設(shè)最低工資為2000）定義CHECK約束的語法格式：[CONSTRAINT<約束名>]CHECK(邏輯表達(dá)式）注意：CHECK所限制的列必須在同一個表中。如果CHECK約束是定義多列之間的取值約束，則只能在“表級完整性約束定義”處定義。43例4-1用SQL語句創(chuàng)建如下三張表students表結(jié)構(gòu)44字段名字段類型約束字段含義SIDCHAR(9)主鍵學(xué)號snameVARCHAR(20)非空姓名genderCHAR(2)非空，取值范圍：{男，女}性別collegeVARCHAR(20)非空所在學(xué)院EmailVARCHAR(30)取值不重學(xué)生郵箱books表的結(jié)構(gòu)字段名字段類型約束字段含義及說明ISBNCHAR(13)主鍵國際標(biāo)準(zhǔn)書號bnameVARCHAR(32)非空圖書書名categoryCHAR(2)取值范圍：{TP,I,H,F}

圖書分類pressVARCHAR(32)

出版社pub_dateDATE

出版日期priceDECIMAL(6,2)

圖書價格quantityINT默認(rèn)值為1在館數(shù)量45borrow表的結(jié)構(gòu)字段名字段類型約束字段含義ISBNCHAR(13)主鍵列，參照books表的外鍵圖書ISBNSIDCHAR(9)主鍵列，參照students表的外鍵讀者編號borrow_timeDATETIME主鍵列借書時間return_timeDATETIME取值約束：晚于借書時間還書時間46students表的創(chuàng)建語句CREATETABLEstudents(SIDCHAR(9)PRIMARYKEY,--在列級定義主鍵snameVARCHAR(20)NOTNULL,genderCHAR(2)NOTNULLCHECK(gender='男'orgender='女'),collegeVARCHAR(20)NOTNULL,EmailVARCHAR(30)UNIQUE);47books表的創(chuàng)建語句CREATETABLEbooks(

ISBNCHAR(13),

bnameVARCHAR(32)NOTNULL,

categoryENUM('TP','I','H','F'),

pressVARCHAR(32),

pub_dateDATE,

price

DECIMAL(6,2),

quantityINTDEFAULT1,

PRIMARYKEY(ISBN)--在表級定義主鍵);48borrow表的創(chuàng)建語句CREATETABLEborrow(ISBNCHAR(13),SIDCHAR(9),borrow_timeDATETIME,return_timeDATETIME,PRIMARYKEY(ISBN,SID,borrow_time),--多列做主鍵定義在表級FOREIGNKEY(ISBN)REFERENCESbooks(ISBN),FOREIGNKEY(SID)REFERENCESstudents(SID),CHECK(borrow_time<return_time)--多列CHECK約束定義在表級);49例4-2定義自增約束列的例子TestTable1表CREATETABLETestTable1(AutoIDINTPRIMARYKEYAUTO_INCREMENT,ContentVARCHAR(20)NOTNULL);50字段名字段類型約束字段含義AutoIDINT自增主鍵標(biāo)號ContentVARCHAR(20)非空內(nèi)容4.3.2查看基本表結(jié)構(gòu)1.顯示數(shù)據(jù)庫中全部表

SHOWTABLES;2.顯示表的結(jié)構(gòu)以表格方式顯示表的定義使用DESCRIBE語句：DESC[RIBE]<表名>;以腳本方式顯示表的定義使用SHOW語句：SHOWCREATETABLE<表名>;514.3.3基本表的維護(hù)修改表結(jié)構(gòu)使用ALTERTABLE語句實現(xiàn)。（1）修改表名及列名（2）修改列的數(shù)據(jù)類型（3）添加及刪除列（4）添加和刪除約束52（1）修改表名修改表名的ALTERTABLE語句語法格式為：ALTERTABLE<舊表名>RENAME[TO]<新表名>;例4-6把TestTable1表的表名改為TestTab。ALTERTABLETestTable1RENAMETestTab;53（2）修改列的數(shù)據(jù)類型和相關(guān)約束ALTERTABLE<表名>MODIFY<列名><數(shù)據(jù)類型>[DEFAULT<默認(rèn)值>|NOTNULL|UNIQUE];<列名>指需要修改的字段<數(shù)據(jù)類型>指修改后字段的新數(shù)據(jù)類型。DEFAULT<默認(rèn)值>、NOTNULL和UNIQUE是可選項，分別為該列設(shè)置默認(rèn)值、非空或唯一值約束使用ALTERTABLE語句設(shè)置唯一值約束時需要注意，要確保該列的數(shù)據(jù)沒有重復(fù)值，否則設(shè)置唯一值約束將失敗。54例4-6只更改列的數(shù)據(jù)類型將Students表的sname的數(shù)據(jù)類型從VARCHAR(20)改為VARCHAR(50)。ALTERTABLEStudentsMODIFYsname

VARCHAR(50);55添加非空約束和默認(rèn)值約束的例子例4-7為Students表的sname列添加非空約束。ALTERTABLEStudentsMODIFYsname

VARCHAR(50)NOTNULL;例4-8為Students表的gender列添加默認(rèn)值：男。ALTERTABLEStudentsMODIFYsname

VARCHAR(50)DEFAULT'男';56例4-9為sname列同時添加默認(rèn)值約束和非空約束ALTERTABLEStudentsMODIFYsnameVARCHAR(50)DEFAULT'男'NOTNULL;

或：ALTERTABLEStudentsMODIFYsnameVARCHAR(50)NOTNULLDEFAULT'男';57（3）修改列名和相關(guān)約束ALTERTABLE<表名>CHANGE<舊列名><新列名><新數(shù)據(jù)類型>[DEFAULT<默認(rèn)值>|NOTNULL|UNIQUE];<舊列名>指修改前的列名；<新列名>指修改后的列名；<新數(shù)據(jù)類型>指修改后的數(shù)據(jù)類型；[DEFAULT<默認(rèn)值>|NOTNULL|UNIQUE]為可選項。58說明將<舊列名>和<新列名>設(shè)置為相同的名稱，則只改變數(shù)據(jù)類型。如果不需要修改字段的數(shù)據(jù)類型，可將<新數(shù)據(jù)類型>設(shè)置成與原來一樣，但數(shù)據(jù)類型不能為空。若只修改列名而不改變列的數(shù)據(jù)類型，則該修改操作將失敗。修改列后，該列的NOTNULL、DEFAULT約束將會失效（若有的話）。59修改列名和相關(guān)約束的例子例4-10將Students表的college列名變?yōu)閟chool，數(shù)據(jù)類型為VARCHAR(30)。ALTERTABLEStudentsCHANGEcollege

schoolVARCHAR(30);若創(chuàng)建表時沒有為Students表中的Email列定義唯一值約束，可用如下SQL語句為Email列添加唯一值約束。ALTERTABLEStudentsCHANGEEmail

EmailVARCHAR(30)UNIQUE;60（4）添加列ALTERTABLE<表名>ADD<新列名><數(shù)據(jù)類型>[約束條件][FIRST|AFTER<已經(jīng)存在的列名>];FIRST：可選參數(shù)，將新添加的列設(shè)置為表中第一個字段。AFTER<已經(jīng)存在的列名>：可選參數(shù)，將新添加的列添加到<已經(jīng)存在的列名>的后面。如果省略這兩個參數(shù)，則默認(rèn)將新添加的列作為表的最后列。61添加列的例子例4-11在Students表中增加一個電話列，列名：phone，類型：CHAR(11)，非空。ALTERTABLEStudentsADDphoneCHAR(11)NOTNULL;例4-12在Students表中增加一個新列，列名：TestID，類型：CHAR(11)。此列將作為Students表的第一個列。ALTERTABLEStudentsADDTestIDCHAR(11)FIRST;62（5）刪除列語法如下：ALTERTABLE<表名>DROP<列名>;例4-13刪除Students表中的TestID列。ALTERTABLEStudentsDROPTestID;63（6）改變表中列的排列順序ALTERTALBE<表名>MODIFY<列1><數(shù)據(jù)類型>[FIRST|AFTER<列2>];<列1>：要修改位置的字段名；<數(shù)據(jù)類型>：指<列1>的數(shù)據(jù)類型；FIRST：將<列1>修改為表的第一個字段；AFTER<列2>：將<列1>放置到<列2>后面。64改變表中列的排列順序的例子例4-14將Students表中的phone列放置到Email列的前面。ALTERTABLEStudentsMODIFYEmail

VARCHAR(30)AFTERphone;65（7）修改表的主鍵約束修改表的主鍵約束的語句的語法如下：ALTERTABLE<表名>DROPPRIMARYKEY,ADDPRIMARYKEY(新主鍵列名);例4-15修改borrow表的主鍵，刪除原來的主鍵，并將（ISBN，SID）設(shè)置為新的主鍵。ALTERTABLEborrowDROPPRIMARYKEY,ADDPRIMARYKEY(ISBN,SID);66（8）添加和刪除表的外鍵約束添加表的外鍵約束的語法為：ALTERTABLE<表名>ADD[CONSTRAINT<約束名>]FOREIGNKEY(<列名>)REFERENCES<外表名>(<外表列名>)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]67例4-16為borrow表的SID列添加外鍵約束外鍵約束名為fksid，參照Students表的SID列。ALTERTABLEborrowADDCONSTRAINTfksidFOREIGNKEY(SID)REFERENCESStudents(SID);68刪除表的外鍵約束MySQL中刪除外鍵的語句的語法為：ALTERTABLE<表名>DROPFOREIGNKEY<外鍵約束名>;例4-17刪除borrow表的外鍵約束fksid。AL