項目3創(chuàng)建和管理表教材編寫組任務1認識表元素一、表中常用的數(shù)據(jù)類型1.數(shù)值類型MySQL中的數(shù)值類型用來存儲能夠進行算術運算的數(shù)據(jù)，分為整數(shù)類型、浮點數(shù)類型和定點數(shù)類型。1）整數(shù)類型整數(shù)類型的取值范圍如表3-1所示。任務1認識表元素一、表中常用的數(shù)據(jù)類型1.數(shù)值類型2）浮點數(shù)類型浮點數(shù)類型不能精確表示數(shù)據(jù)的精度，使用這種類型來存儲某些數(shù)值時，有可能會損失一些精度，所以也稱近似類型，通常用來處理取值范圍非常大且對精度要求不太高的數(shù)據(jù)，如一些統(tǒng)計量。3）定點數(shù)類型如果要存儲精度相對要求較高的數(shù)據(jù)，如財務數(shù)據(jù)、科學數(shù)據(jù)等，就要使用定點數(shù)類型，其小數(shù)位數(shù)是固定的。定點數(shù)類型有numeric|decimal，兩者等價，定義格式為numeric[(m,d)]或decimal[(m,d)]，d默認為0，存儲為（m+2）字節(jié)。任務1認識表元素一、表中常用的數(shù)據(jù)類型2.字符串類型MySQL中的字符串類型用來存儲字符數(shù)據(jù)，包括普通文本字符串（char、varchar）、二進制字符串類型（blob）、大文本字符串類型（text）和單選項數(shù)據(jù)類型（enum）和特殊類型(set）。如表3-2所示。任務1認識表元素一、表中常用的數(shù)據(jù)類型3.日期和時間類型日期和時間類型具有特定的格式，專用于表示日期、時間。主要類型如表3-3所示。任務1認識表元素一、表中常用的數(shù)據(jù)類型4.Json類型Json類型是MySQL結合結構化存儲和非結構化存儲設計出來的一種類型，用于互聯(lián)網(wǎng)應用服務之間的數(shù)據(jù)交換。它可以用來存儲任何類型的Json數(shù)據(jù)，如序列化的對象和數(shù)組。Json對象是由{}括住的內(nèi)容，包含一組由逗號分隔的鍵值對，鍵與值之間用“:”分隔，鍵必須是字符串。例如：{"name":"John","age":30,"city":"NewYork"}。類似于Python中的字典。Json數(shù)組是由[]括起來的一組值。類似于Python中的列表。例如：[80,78,64,89,56]，["apple","banana","peach","oranage"]在Json數(shù)組元素和Json對象鍵值中允許嵌套。例如：在Json數(shù)組中嵌入數(shù)組和對象。[80,{"name":"周穎","age":19,"city":"XianYang"},["計算機網(wǎng)絡技術","Python程序設計","Linux操作系統(tǒng)"]]。在Json對象中嵌入數(shù)組和對象的值。{"name":"張小果","address":{"country":"china","city":"xianyang"},"electives":["書法"，"茶藝"]}任務1認識表元素二、MySQL的約束MySQL的約束主要完成對數(shù)據(jù)的檢驗，是指對表中數(shù)據(jù)的一種約束行為，可以實現(xiàn)數(shù)據(jù)完整性。數(shù)據(jù)完整性指數(shù)據(jù)庫中數(shù)據(jù)的正確性和一致性，主要分為實體完整性、域完整性和參照完整性。例如，兩個表中如果有相互依賴的數(shù)據(jù)，可保證數(shù)據(jù)不能刪除或更改。設置約束的主要目的是為了保證數(shù)據(jù)的完整性。MySQL的約束主要包括主鍵約束、唯一鍵約束、非空約束、默認值約束、檢查約束和外鍵約束六種，它們和完整性之間的關系如表3-4所示。任務1認識表元素二、MySQL的約束1.實體完整性實體完整性用來保證表中記錄的唯一性。可通過主鍵約束和唯一鍵約束實現(xiàn)。1）主鍵約束（primarykey）主鍵是用來唯一標識表中每條記錄的一個或多個字段組合。由多個字段組合而成的主鍵也稱為組合主鍵，主鍵值必須唯一,不能有重復值，也不允許為空（NULL）值，一個表只能定義一個主鍵。可以在創(chuàng)建表或修改表時通過primarykey關鍵字設置主鍵約束。2）唯一鍵約束（unique）也稱候選鍵約束，與主鍵約束類似。唯一鍵約束用于非主鍵的一個或多個字段組合。唯一鍵可以為空，一個表可以定義多個唯一鍵約束。唯一鍵約束可以在創(chuàng)建表或修改表時使用unique關鍵字來定義。任務1認識表元素二、MySQL的約束2.域完整性域完整性要求輸入的值必須為指定的數(shù)據(jù)類型、取值范圍，確定是否允許為空和輸入的值類型和范圍是否有效?？赏ㄟ^以下三種約束來實現(xiàn)。1）非空值約束（notnull）非空約束是指字段的值不能為空。使用了非空約束的字段，如果用戶在添加數(shù)據(jù)時沒有給其指定值，數(shù)據(jù)庫系統(tǒng)會報錯。非空約束可以在創(chuàng)建表時設置，還可以在已創(chuàng)建的表中添加。2）默認值約束（defalut）如果某字段的某個值出現(xiàn)概率較大，為了提高用戶輸入數(shù)據(jù)的效率，可以設置字段的默認值。為字段指定默認值后，在添加記錄時，系統(tǒng)會自動為該字段輸入指定的默認值。3）檢查約束（check）通常使用檢查約束對字段進行數(shù)據(jù)輸入值的限定，指定某個字段可接受的值。例如，在設置學生性別字段值為“男”、“女”后，如果用戶輸入的性別不是“男”或“女”，系統(tǒng)會提示錯誤信息。任務1認識表元素二、MySQL的約束3.參照完整性參照完整性又稱引用完整性,是建立在主鍵與外鍵之間的一種引用規(guī)則，來保證主表（主鍵所在表）數(shù)據(jù)和從表（外鍵所在表）數(shù)據(jù)的一致性,防止出現(xiàn)數(shù)據(jù)丟失和無效數(shù)據(jù)的產(chǎn)生。例如，成績表中參加考試的學生應該是學生表中的學生，所以，成績表中的學號是外鍵，應是學生表中存在的學號（主鍵）。參照完整性可通過外鍵約束（foreignkey）來實現(xiàn)。在實際應用中，數(shù)據(jù)庫中不同表的數(shù)據(jù)通常存在聯(lián)系，必須先在主表中定義主鍵約束，然后再在從表中設置外鍵以實現(xiàn)表之間數(shù)據(jù)的關聯(lián)和參照。設置外鍵約束后，將不允許用戶進行下列操作：（1）在主表中沒有關聯(lián)的記錄時,將記錄添加或更改到相關的從表中。（2）更改主表的值,導致相關從表中生成孤立記錄。（3）從主表中刪除了記錄,但仍然存在與該記錄匹配的相關記錄在從表中。任務2創(chuàng)建表一、創(chuàng)建表1.語法格式CREATETABLE表名(字段名1數(shù)據(jù)類型[(寬度)][NOTNULL][DEFAULT默認值][PRIMARYKEY][UNIQUE][CHECK條件表達式][,字段名2……]|[,CONSTRAINT[約束名]]PRIMARYKEY(關鍵字)|[,CONSTRAINT[約束名]]UNIQUE(關鍵字)|[,CONSTRAINT[約束名]]FOREIGNKEY(列名)REFERENCES主表(主鍵)|[,CHECK條件表達式])任務2創(chuàng)建表一、創(chuàng)建表2.創(chuàng)建表并定義非空約束【例3.1】在數(shù)據(jù)庫dbschool中創(chuàng)建class表，只定義非空約束。usedbschool;showtables;#查看當前數(shù)據(jù)庫的表createtableclass(classidchar(6)notnull,classnamevarchar(10)notnull,departmentvarchar(10)notnull);showtables;任務2創(chuàng)建表一、創(chuàng)建表3.創(chuàng)建表并定義主鍵【例3.2】在數(shù)據(jù)庫dbschool中創(chuàng)建包含主鍵約束和非空約束的course表。createtablecourse(cnochar(5)primarykey,cnamevarchar(10)notnull,periodint,creditint,termchar(1));showtables;任務2創(chuàng)建表一、創(chuàng)建表4.創(chuàng)建表并定義主鍵、默認值和檢查約束【例3.3】在數(shù)據(jù)庫dbschool中創(chuàng)建student表，包含主鍵約束、檢查約束和默認值約束。createtablestudent(snochar(8),snamevarchar(10)notnull,genderchar(1)notnullcheck(gender='男'orgender='女'),birthdaydate,nationvarchar(10)default'漢',subjectvarchar(10),classidchar(6),primarykey(sno));任務2創(chuàng)建表一、創(chuàng)建表5.創(chuàng)建表并定義外鍵約束【例3.4】在dbschool中創(chuàng)建study表，定義組合主鍵，并分別在學號和課程號字段上定義外鍵。createtablestudy(snochar(8)notnull,cnochar(5)notnull,scorefloat(4,1),primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno));任務2創(chuàng)建表二、查看表1.查看當前數(shù)據(jù)庫中的表在MySQLWorkbench客戶端和MySQL命令行客戶端均可查看，前者更直觀。在命令行查看表的語句格式是：SHOWTABLES;【例3.5】使用兩種方法查看dbschool數(shù)據(jù)庫中表。在MySQLWorkbench客戶端，可以直觀地查看到表的信息，如圖3-5所示。圖3-5在MySQLWorkbench客戶端查看dbschool數(shù)據(jù)庫中的表任務2創(chuàng)建表二、查看表1.查看當前數(shù)據(jù)庫中的表在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-6所示。圖3-6在MySQL命令行客戶端查看dbschool數(shù)據(jù)庫中的表任務2創(chuàng)建表二、查看表2.查看表的基本結構在MySQL中，可以使用DESCRIBE/DESC語句查看指定表的基本結構，包括表中的列名、數(shù)據(jù)類型和寬度、是否允許NULL值、默認值、數(shù)據(jù)完整性約束等。語法格式：DESCRIBE|DESC表名【例3.6】查看dbschool數(shù)據(jù)庫中student表的基本結構。在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-7所示。圖3-7查看student表的基本結構任務2創(chuàng)建表二、查看表3.查看表的定義腳本在MySQL中，使用SHOWCREATETABLE語句可以查看表的定義語句，還可以查看存儲引擎和字符編碼。語法格式：SHOWCREATETABLE表名;或者SHOWCREATETABLE表名\G任務2創(chuàng)建表二、查看表3.查看表的定義腳本【例3.7】查看數(shù)據(jù)庫dbschool中表student表的詳細信息。在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-8所示。圖3-8查看student表的定義語句任務3管理表一、復制表1.使用LIKE關鍵字復制表結構語法格式：CREATETABLE[IFNOTEXISTS]新表名LIKE參照表名說明：創(chuàng)建一個與被復制表結構相同的新表，包括列名、數(shù)據(jù)類型、數(shù)據(jù)完整性約束，復制的新表是一個空表。任務3管理表一、復制表1.使用LIKE關鍵字復制表結構【例3.8】在dbschool數(shù)據(jù)庫中，復制一個和student表結構相同的新表s并查看其定義語句。在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-9所示。圖3-9復制student表結構并查看新表的定義任務3管理表一、復制表2.使用AS關鍵字復制表結構和記錄語法格式：CREATETABLE[IFNOTEXISTS]新表名ASSELECT*FROM參照表名【例3.9】在dbschool數(shù)據(jù)庫中，復制一個和course表字段內(nèi)容均相同的表c。在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-10所示。圖3-10復制course為c并查看兩表的定義任務3管理表二、修改表結構1.刪除字段語法格式：ALTERTABLE表名DROP字段名【例3.10】在數(shù)據(jù)庫dbschool中，刪除s表中的字段nationa并查看是否完成刪除。在MySQL命令行客戶端執(zhí)行的命令及結果如圖3-11所示。圖3-11在s表中刪除已有字段nation任務3管理表二、修改表結構2.增加新字段語法格式：ALTERTABLE表名ADD新字段名數(shù)據(jù)類型(寬度)[FIRST|AFTER已有字段名]說明：如果省略[FIRST|AFTER已有字段名]，則默認在末字段后增加新字段；如果選擇FIRST選項，則在首字段前增加新字段；如果選擇AFTER已有字段名，則在已有字段名后增加新字段。任務3管理表二、修改表結構2.增加新字段【例3.11】對dbschool數(shù)據(jù)庫中的s表，在第一列前添加一個新字段id，數(shù)據(jù)類型為int，不允許為空;在末字段后增加phone字段，類型為varchar(12);在birthday字段后插入mz字段，類型為char(10)。在MySQL命令行客戶端執(zhí)行命令及結果如圖3-12所示。圖3-12在s表中增加新字段任務3管理表二、修改表結構3.修改字段名語法格式：ALTERTABLE表名CHANGE已有字段名新字段名數(shù)據(jù)類型(寬度)【例3.12】在數(shù)據(jù)庫dbschool中，將s表中的字段名mz改為nation。在MySQL命令行客戶端執(zhí)行命令及結果如圖3-13所示。圖3-13在s表修改字段名任務3管理表二、修改表結構4.修改字段類型或?qū)挾日Z法格式：ALTERTABLE表名MODIFY已有字段名數(shù)據(jù)類型(寬度)【例3.13】在數(shù)據(jù)庫dbschool中，將s表中的nation字段類型改為varchar。

在MySQL命令行客戶端執(zhí)行命令及結果如圖3-14所示。圖3-14在s表修改字段類型任務3管理表三、添加/刪除表約束1.添加/刪除主鍵約束語法格式：ALTERTABLE表名ADDPRIMARYKEY(字段名)|DROPPRIMARYKEY【例3.14】在class表的classid字段上添加主鍵約束。命令及執(zhí)行結果如圖3-15所示。圖3-15在class表中添加主鍵約束任務3管理表三、添加/刪除表約束2.添加/刪除外鍵約束語法格式：ALTERTABLE表名ADDFOREIGNKEY(字段名)REFERENCES主表(主鍵)|DROPFOREIGNKEY外鍵約束名其中的“外鍵約束名”，可以通過查看表的定義語句獲得?！纠?.15】在student表的classid字段上定義外鍵，關聯(lián)class表的主鍵classid。命令如下：altertablestudentaddforeignkey(classid)referencesclass(classid);刪除外鍵的命令及執(zhí)行結果如圖3-16所示：圖3-16刪除student表在classid字段上的外鍵約束任務3管理表三、添加/刪除表約束3.添加/刪除唯一鍵約束語法格式：ALTERTABLE表名ADDUNIQUE[KEY](字段名)|DROPKEY<唯一鍵約束名>這里的“唯一鍵約束名”就是字段名。【例3.16】在class表的