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

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

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

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

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

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

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

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

{<列名><數據類型>[列級完整性約束定義

[...n]]}

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

28參數說明<表名>是所要定義的基本表的名字。<列名>是表中所包含的屬性列的名字。IFNOTEXISTS：只有當前數據庫中不存在該同名表時才執行CREATETABLE操作。標準SQL語言的建表語句沒有該選項，這是MySQL自己的擴展。29注意SQL語句在Windows操作系統中不區分大小寫，在Linux操作系統中區分大小寫。標點符號必須使用英文半角字符。定義表時可以同時定義與表有關的完整性約束。30完整性約束定義說明只涉及表中一列的約束列級完整性約束定義表級完整性約束定義涉及表中多列的約束表級完整性約束定義31文法描述中常用符號的含義方括號（[]）中的內容表示是可選的可出現0次或1次花括號（{}）與省略號（…）一起，表示其中的內容可以出現0次或多次。豎杠（｜）表示在多個選項中選擇一個term1|term2|term3：表示在三個選項中任選一項。322.完整性約束PRIMARYKEY：主鍵約束。FOREIGNKEY：外鍵約束。NOTNULL：非空約束。UNIQUE：唯一值約束。DEFAULT：默認值約束。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外鍵約束參數說明CONSTRAINT<外鍵約束名>：可選參數。FOREIGNKEY(<列名>)：指定外鍵的列名。REFERENCES<外表名>(<外表列名>)：指定外鍵參照的表名和列名。ONDELETE/ONUPDATE：父表刪除或更改操作時CASCADE:會自動刪除或更改子表中與之對應的記錄。SETNULL:會將子表中與之對應記錄的外鍵值自動設置為NULL。NOACTION：刪除或更改操作將失敗。RESTRICT:與“NOACTION”選項功能相同，且為級聯選項默認值。37非空（NOTNULL）約束非空約束用于限制一個列的取值不能有NULL值。例如，學生的姓名不能為空值。定義非空約束的語法格式為：

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

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

數據類型

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

圖書分類pressVARCHAR(32)

出版社pub_dateDATE

出版日期priceDECIMAL(6,2)

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

ISBNCHAR(13),

bnameVARCHAR(32)NOTNULL,

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

pressVARCHAR(32),

pub_dateDATE,

price

DECIMAL(6,2),

quantityINTDEFAULT1,

PRIMARYKEY(ISBN)--在表級定義主鍵);48borrow表的創建語句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自增主鍵標號ContentVARCHAR(20)非空內容4.3.2查看基本表結構1.顯示數據庫中全部表

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

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

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

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

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

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

EmailVARCHAR(30)UNIQUE;60（4）添加列ALTERTABLE<表名>ADD<新列名><數據類型>[約束條件][FIRST|AFTER<已經存在的列名>];FIRST：可選參數，將新添加的列設置為表中第一個字段。AFTER<已經存在的列名>：可選參數，將新添加的列添加到<已經存在的列名>的后面。如果省略這兩個參數，則默認將新添加的列作為表的最后列。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><數據類型>[FIRST|AFTER<列2>];<列1>：要修改位置的字段名；<數據類型>：指<列1>的數據類型；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）設置為新的主鍵。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