項目三創建和管理數據表教學目標能力目標◎能夠根據需求建立合適、規范數據表和數據完整性約束；◎能熟練查看數據表并按需進行修改和復制操作。知識目標◎熟悉數據表的數據類型、鍵和完整性約束等元素；◎掌握查看、建立和修改數據表的語法應用。學習重點◎按應用需要建立規范的數據表和數據完整性約束；◎復制數據表和修改數據表；學習難點◎修改數據表約束。任務目標任務1認識數據表元素任務2

創建數據表任務1認識數據表元素創建數據表

主要內容:查看數據表應用數據類型創建數據庫表重點難點:建立數據表查看數據表查看當前庫中的表語法：SHOWTABLES;例：mysql>USEmysql;mysql>SHOWTABLES;查看表結構語法：DESC[RIBE]表名例：mysql>USEmysql;mysql>DESCuser;應用數據類型數據類型應用數據表由多個字段構成，每個字段可以指定不同的數據類型；數據類型用于規定數據的存儲格式、約束和有效范圍。在定義數據表字段時，應根據實際情況選擇合適的數據類型。MySQL提供的數據類型主要包括數值類型、日期和時間類型、字符串類型以及JSON類型。MySQL支持所有的ANSISQL92數值類型，包括如下：準確數字的數值類型：NUMERIC\DECIMAL\INTEGER和SMALLINT近似數字的數值類型：FLOAT\REAL和DOUBLE，其中INT是INTEGER的同義詞，DEC是DECIMAL的同義詞；數據類型應用案例展示例如：建立db_shop的顧客表USEdb_shop;CREATETABLE`customer`(`customer_id`int(8)AUTO_INCREMENTPRIMARYKEY,#主鍵`customer_name`varchar(10)NOTNULL,`sex`enum('F','M')DEFAULT'F',`identity_id`char(18),`age`tinyint(3),`join_date`date,`hobby`set('ball','art','music'),`cost`double(5,1)UNSIGNED,`balance`decimal(5,2),`photo`varbinary(250),`Address`json)ENGINE=InnoDBCHARACTERSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciROW_FORMAT=Dynamic;INSERTINTOassociatorVALUES(1,'李紅','F',,23,CURDATE(),'football,basketball',5000.1,300.01,NULL,'{"city":"廣州","road":"天河北"}')#JSON:'{"key1":"value1","key2":"value2"}';查看數據類型1、使用MySQL幫助文檔查看支持的數據類型查看幫助文檔目錄語法：HELPCONTENTS;例：mysql>HELPCONTENTS;#可以看到有DATATYPES目錄查看支持的數據類型語法：HELPDATATYPES;例：mysql>HELPDATATYPES；#可以看到所有支持的數值類型查看數據類型使用語法：HELP數據類型例：mysql>HELPINT;#可以查看到類型應用格式和存儲數值范圍2、查閱網上官方應用幫助文檔：查閱數據類型：/doc/refman/8.0/en/data-types.html查閱類型常量應用：/doc/refman/8.0/en/literals.html數值類型數值類型MySQL數值類型分為整數類型、浮點數類型和定點數類型：整數類型查看整數類型例：mysql>HELPTINYINT查閱官網說明文檔/doc/refman/8.0/en/integer-types.html數值類型浮點數類型和定點數類型查看浮點數類型例：mysql>HELPDOUBLE查看官方幫助文檔：/doc/refman/8.0/en/numeric-types.html創建表時，應用數值類型應遵循：選擇最小的可用類型；對于完全都是數字的，可以選擇整數類型；浮點類型用于可能具有小數部分的數據；字符串類型字符串類型可以分3類：普通文本字符串類型：CHAR、VARCHAR可變類型：TEXT、BLOB特殊類型：SET（用于多選項數據）

ENUM（用于單選項數據）查看字符串類型例：mysql>HELPCHAR;查閱網上官方應用幫助文檔：/doc/refman/8.0/en日期類型日期和時間類型：YEAR，DATE，TIME，DATETIME和TIMESTAMP查看日期和時間類型例：mysql>HELPDATE;查閱網上官方應用幫助文檔：/doc/refman/8.0/en/date-and-time-types.html說明：向日期和時間類型的字段插入數據時，最好使用引號將值包含起來JSON類型JSON(JavaScriptObjectNotation)類型是一種輕量級的數據交換格式，任何支持的類型都可以通過JSON來表示，例如字符串、數字、對象、數組等。但是對象和數組是比較特殊且常用的兩種類型：對象表示為鍵值對，數據由逗號分隔；花括號保存對象，方括號保存數組；查看使用格式：/doc/refman/8.0/en/json.html#json-values查看JSON類型例：mysql>HELPJSON;查閱網上官方應用幫助文檔：/doc/refman/8.0/en/json.html考考你MySQL數據庫的數據類型有哪些？使用HELP指令查看、閱讀各個數據類型的應用說明；使用官方網站幫助文檔查看、閱讀各個數據類型的應用；認識數據表數據表是我們訪問數據的基本邏輯對象，一切可訪問的數據都來源于數據表。數據表是由行、列結構組成來實現存儲信息數據的。一個數據表對應一個關系。數據表的列，也稱字段，對應關系或E-R圖中的屬性。數據表的行稱為記錄，對應E-R圖中的一個實體值或關系中的元組。數據表字段定義：我們建立數據表時，需要對關系表中每個關系屬性的取值進行詳細定義，即數據表的字段定義，包括數據包括數據類型、長度、是否允許空、是否鍵值、約束條件等鍵鍵主鍵（又稱關鍵字）是用來唯一標識數據表記錄的字段，對應關系中的主碼。一個數據表只能有一個主鍵。主鍵值不能為空，且不能重復。如：員工的身份證號是唯一的，可用來作為員工表的主鍵。唯一鍵數據表中除了主鍵之外，若還有其他字段值也要求是唯一的，則可將其設為唯一鍵，表示該字段值也不允許有重復值。一個數據表可以有多個唯一鍵，可允許空，但只能有一條記錄為空值。外鍵若數據表的字段值必須引用參照于另一個數據表的主鍵或唯一鍵中的值，則可將此字段設為外鍵，來保證多個數據表之間數據的關聯。一個數據表可以有多個外鍵，外鍵值充許為空，也充記錄片有重復值。外鍵值必須是它引用參照表主鍵或唯一鍵中存在的值關系數據的完整性域完整性域完整性要求輸入的值應為指定的數據類型、取值范圍。實體完整性要求每個實體都具有唯一標識，且不能為空。參照完整性參照完整性是兩個關系屬性之間的引用參照的約束。若一個關系的屬性值（外碼）對應依賴于另一個關系的主碼，則這兩個表具有參照依賴關系。用戶定義的完整性用戶根據業務規則定義的檢查滿足條件。MySQL支持的約束類型關系數據庫的完整性，在DBMS中是通過各種約束技術來實現的，MySQL支持的約束類型有：PRIMARYKEY:主鍵約束，實現實體完整性DEFAULT:默認值約束，UNIQUEKEY:唯一約束，NOTNULL:非空約束，FOREIGNKEY:外鍵約束，實現參照完整性CHECK:檢查約束，實現用戶自定義完整性（MySQL8.0.16以上版本才支持）任務實施按下列步驟實施完成db_shopping數據庫顧客表的建立，查看表信息和字段類型應用。1.選擇db_shopping數據庫。執行語句如下：mysql>USEdb_shopping;2.查看當前數據庫包含的所有表。執行語句如下：mysql>SHOWTABLES;3.運行如下創建數據表語句，創建顧客表，觀察表結構中的字段類型、key和默認值。執行語句如下：mysql>CREATETABLEcustomer(idintAUTO_INCREMENTPRIMARYKEY,#主鍵customer_namevarchar(20)NOTNULL,sexenum('F','M')NULLDEFAULT'F',identity_idchar(18)NULL,birthdaydateNULL,hobbyset('ball','art','music'),consumption_amountdecimal(11,2)UNSIGNEDNULLDEFAULT0.00,menber_balancedecimal(11,2)UNSIGNEDNULLDEFAULT0.00,photovarbinary(250)NULL,addressjsonNULL)ENGINE=InnoDBCHARACTERSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciROW_FORMAT=Dynamic;4.查看customer表結構信息。執行語句如下：mysql>DESCcustomer；5.查看customer表的定義腳本。執行語句如下：mysql>SHOWCREATETABLEcustomer；6.添加顧客記錄,觀察各字段取值范圍。執行語句如下：mysql>INSERTINTOcustomer(id,customer_name,sex,identity_id,birthday,hobby,consumption_amount,menber_balance,address)VALUES(1,'陳強','M',,'1998-09-01','ball,art',5000.00,0.00,'{"city":"廣州2","road":"黃埔","room":16}'),(2,'李紅','F',NULL,'2000-09-01','art',8000.00,1000.00,'{"city":"廣州","road":"黃埔","room":26}'),(3,'陳紅玲','F',NULL,'1995-06-01','ball',1200.00,0.00,NULL,NULL,'{"city":"潮州","road":"朝陽街","room":"9}');任務實施4.查看customer表結構信息。執行語句如下：mysql>DESCcustomer；5.查看customer表的定義腳本。執行語句如下：mysql>SHOWCREATETABLEcustomer；6.添加顧客記錄,觀察各字段取值范圍。執行語句如下：mysql>INSERTINTOcustomer(id,customer_name,sex,identity_id,birthday,hobby,consumption_amount,menber_balance,address)VALUES(1,'陳強','M',,'1998-09-01','ball,art',5000.00,0.00,'{"city":"廣州2","road":"黃埔","room":16}'),(2,'李紅','F',NULL,'2000-09-01','art',8000.00,1000.00,'{"city":"廣州","road":"黃埔","room":26}'),(3,'陳紅玲','F',NULL,'1995-06-01','ball',1200.00,0.00,NULL,NULL,'{"city":"潮州","road":"朝陽街","room":"9}');任務2創建數據表建立數據表創建數據表查看建表語法：HELPCREATETABLEmysql>HELPCREATETABLE;查看官方網站幫助文檔：/doc/refman/8.0/en/create-table.html語法：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name

(col_namedata_type[NOTNULL|NULL][DEFAULT{literal|(expr)}][AUTO_INCREMENT][UNIQUE[KEY]][[PRIMARY]KEY][COMMENT'string'])|[CONSTRAINT[symbol]]PRIMARYKEY[index_type](key_part,...)[index_option]...--主鍵鍵約束|[CONSTRAINT[symbol]]UNIQUE[INDEX|KEY][index_name][index_type](key_part,...)[index_option]...--唯一鍵約束|[CONSTRAINT[symbol]]FOREIGNKEY[index_name](col_name,...)REFERENCEStbl_name(key_part,...)--外鍵約束|check_constraint_definition--用戶業務自定義檢查約束

......

[table_options]--表的一些特性參數

|[ASSELECT

...]--SELECT語句描述部分，用此可以快速創建表|[LIKEold_tbl_name]--復制表結構建立數據表--舉例設置表的主鍵和唯一鍵例1：在網上購物系統中，我們從前一項目中知道，部門的編號、員工的編號都是主碼，應該定義為主鍵，員工的部門應參照于部門的編號，應該定義為外鍵。外鍵表應于其主鍵表建立之后建立。--部門信息的建立mysql>USEdb_shop;mysql>CREATETABLEdepartment#部門信息(dept_idCHAR(3)NOTNULLPRIMARYKEY,#部門編號（定義主鍵）dept_nameVARCHAR(20)NOTNULLUNIQUE,#部門名稱（定義唯一鍵）dept_phoneCHAR(13),--部門電話dept_memoVARCHAR(100)--部門備注)；mysql>SHOWTABLES;--查看庫表mysql>DESCdepartment;--查看表結構建立數據表--舉例設置表的外鍵、檢查約束和默認值約束#建立職員表mysql>USEdb_shop;mysql>CREATETABLEstaffer--職員信息(staff_idCHAR(5)NOTNULLPRIMARYKEY,--員工號（定義主鍵）

dept_idCHAR(5)NOTNULL,--部門編號staff_nameVARCHAR(10)NOTNULL,--姓名（不允許空）sexenum('F','M')DEFAULT'F',--性別（使用默認值）phone

CHAR(11),--地址salaryDOUBLE(6,1)CHECK(salary>0ANDsalary<10000),--用戶自定義檢查約束，MySQL8.0.16版本有效,之前版本忽略

staff_memoVARCHAR(100),--員工備注

FOREIGNKEY(dept_id)REFERENCESdepartment(dept_id)--（定義外鍵）);mysql>SHOWTABLES;--查看庫表mysql>DESCdepartment;--查看表結構建立數據表--舉例設置表的默認存儲引擎例如：建立db_shop的顧客表mysql>USEdb_shop;mysql>CREATETABLEcustomer1(customer_idintAUTO_INCREMENTPRIMARYKEY,#主鍵

customer_namevarchar(10)NOTNULL,sexenum('F','M')DEFAULT'F',birthdaydate,hobbyset('ball','art','music'),consumption_amountdouble(5,1)UNSIGNED,--消費金額menber_balancedecimal(5,2)UNSIGNED,--會員余額photovarbinary(250),addressjson)ENGINE=MyISAM;建立數據表--舉例復制表結構語法：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_nameLIKEold_tbl_name--復制表結構(包括主鍵、索引、自動編號)例如：復制一份與staffer職員表一樣的空表mysql>USEdb_shop;mysql>CREATETABLEIFNOTEXISTSstaffer_bakLIKEstaffer;mysql>SELECT*FROMstaffer_bak;建立數據表--舉例復制表結構和記錄語法：CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_nameASSELECT*FROMold_tbl_name--復制表結構和記錄（不復制主鍵、索引、自動編號）例如：復制一份與staffer職員表一樣的數據表mysql>CREATETABLEIFNOTEXISTSstaffer_bak2 ASSELECT*FROMstaffer;mysql>SELECT*FROMstaffer_bak2;考考你數據庫的主鍵和外鍵有什么作用？數據庫的完整性包括哪些？在網上購物系統中。按表信息建立如下數據表：1、建立完成前面演示的部門表、職員表；2、繼續建立完成：供應商表、顧客表、商品表、網上直銷訂單表、訂單項目表。修改數據表結構修改數據庫查看建表語法：HELPCREATETABLEmysql>HELPALTERTABLE;查看官方網站幫助文檔：/doc/refman/8.0/en/alter-table.html語法：ALTERTABLEtbl_name[alter_specification[,alter_specification]...][partition_options]修改表結構alter_specification:table_options|ADD[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]|ADD[COLUMN](col_namecolumn_definition,...)|ADD[CONSTRAINT[symbol]]PRIMARYKEY(index_col_name)

|ADD[CONSTRAINT[symbol]]UNIQUE[INDEX|KEY][index_name](index_col_name)

|ADD[CONSTRAINT[symbol]]FOREIGNKEY[index_name](col_name,...)reference_definition|ADDcheck_constraint_definition|DROPCHECKsymbol|ALTERCHECKsymbol[NOT]ENFORCED|ALTER[COLUMN]col_name{SETDEFAULTliteral|DROPDEFAULT}|CHANGE[COLUMN]old_col_namenew_col_namecolumn_definition[FIRST|AFTERcol_name]|[DEFAULT]CHARACTERSET[=]charset_name[COLLATE[=]collation_name]|DROP[COLUMN]col_name|DROP{INDEX|KEY}index_name|DROPPRIMARYKEY|DROPFOREIGNKEYfk_symbol|MODIFY[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]|RENAMECOLUMNold_col_nameTOnew_col_name|RENAME[TO|AS]new_tbl_name|table_options修改表結構--添加字段添加表字段語法：ALTERTABLEtbl_nameADD[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]例1：mysql>DESCstaffer_bak；#先查看修改前的表結構mysql>ALTERTABLEstaffer_bakADDemail2varchar(50)NULL;

mysql>DESCstaffer_bak；#先查看修改后的表結構修改表結構--修改字段修改字段類型語法：ALTERTABLEtbl_name

MODIFY[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name]例1：mysql>DESCstaffer_bak#查看修改前表結構mysql>ALTERTABLEstaffer_bakMODIFYbirthdaydatetime(6);mysql>DESCstaffer_bak#查看修改后表結構例2：mysql>ALTERTABLEstaffer_bakMODIFYbirthdaydatetime(6)AFTERphone;

mysql>DESCstaffer_bak修改表結構--添加約束添加表字段約束語法：ALTERTABLEtbl_nameADD[CONSTRAINT[symbol]]PRIMARYKEY(index_col_name);例：mysql>DESCstaffer_bak2;#查看表結構mysql>ALTERTABLEstaffer_bak2ADDPRIMARYKEY(staff_id);mysql>SHOWCREATETABLEstaffer_bak2;#查看表定義修改表結構--刪除字段刪除表字段語法：ALTERTABLEtbl_nameDROP[COLUMN]col_name例：mysql>DESCstaffer_bak；#先查看刪除前的表結構mysql>ALTERTABLEstaffer_bakDROPemail2mysql>DESCstaffer_bak；#先查看刪除后的表結構修改表結構--刪除約束刪除表約束--主鍵約束語法：ALTERTABLEtbl_name

DROPPRIMARYKEY例1：mysql>SHOWCREATETABLEstaffer_bak2#查看表定義mysql>ALTERTABLEstaffer_bak2DROPPRIMARYKEY;修改表結構--刪除約束刪除表約束--外鍵約束語法：ALTERTABLEtbl_name

DROPFOREIGNKEYfk_symbol例1：mysql>SHOWCREATETABLEstaf_bak2;#查看表結構定義mysql>ALTERTABLEstaf_bak2#建立外鍵約束

ADDFOREIGNKEY(dept_id)REFERENCESdepartment_BAK(dept_id);mysql>SHOWCREATETABLEstaf_bak2;mysql>ALTERTABLEstaf_bak2#刪除外鍵約束

DROPFOREIGNKEYstaf_bak2_ibfk_1mysql>SHOWCREATETABLEstaf_bak2;修改表結構--修改字段名修改字段名語法：ALTERTABLEtbl_nameRENAMECOLUMNold_col_nameTOnew_col_name例：mysql>DESCstaffer_bak2;mysql>ALTERTABLEstaffer_bak2 RENAMECOLUMNstaff_idTOstaffER_id;mysql>DESCstaffer_bak2;修改表結構--修改存儲引擎修改存儲引擎語法：ALTERTABLEtbl_nameENGINE=e_name;例：mysql>SHOWCREATETABLEstaf_bak2;

mysql>ALTERTABLEstaffer_bak2 ENGINE=MyISAM修改表名修改表名語法：ALTERTABLEold_table_name

RENAME[TO|AS]new_tbl_name例：mysql>SHOWTABLES;mysql>ALTERTABLEstaffer_bak2RENAMETOstaf_bak2;

mysql>SHOWTABLES;刪除表刪除表語法：DROPTABLE[IFEXISTS]table_name1[,table_name2,...];例：mysql>DROPTABLEstaf_bak2;考考你1、復制一份職員表包括其記錄,名稱staffer_bak2。2、查看staffer_bak2的表結構。3、給staffer_bak2添加一個字段（根據實際，自己分析還可以給職員添加的信息）；4、刪除剛才添加的字段；5、給staffer_bak2添加主鍵和外