MySQL數(shù)據(jù)庫和表的創(chuàng)建與管理

數(shù)據(jù)庫和表的創(chuàng)立與管理1.項目知識要點知識能力目標(biāo)學(xué)時3.1任務(wù)1創(chuàng)建與管理數(shù)據(jù)庫3.1.1創(chuàng)建數(shù)據(jù)庫3.1.2管理數(shù)據(jù)庫3.2任務(wù)2創(chuàng)建與管理數(shù)據(jù)庫表3.2.1創(chuàng)建數(shù)據(jù)庫表3.2.2管理數(shù)據(jù)庫表3.3任務(wù)3圖形界面工具創(chuàng)建數(shù)據(jù)庫和表3.3.1圖形界面工具簡介3.3.2圖形界面工具創(chuàng)建數(shù)據(jù)庫3.4綜合實例建立Petstore數(shù)據(jù)庫表3.4.1Petstore數(shù)據(jù)庫表結(jié)構(gòu)分析3.4.2創(chuàng)建Petstore數(shù)據(jù)庫與表【情境】：創(chuàng)建Petstore數(shù)據(jù)庫【職業(yè)崗位】：數(shù)據(jù)庫管理員、程序員【典型工作任務(wù)】：學(xué)會創(chuàng)建與管理數(shù)據(jù)庫【技能要求】：1、學(xué)會命令行方式創(chuàng)建與管理數(shù)據(jù)庫2、學(xué)會圖形界面創(chuàng)建與管理數(shù)據(jù)庫【知識要求】：1、數(shù)據(jù)庫結(jié)構(gòu)2、數(shù)據(jù)庫操作的SQL語句2項目實訓(xùn)三創(chuàng)建“員工管理系統(tǒng)”數(shù)據(jù)庫2工程知識要點與目標(biāo)2.任務(wù)一創(chuàng)立與管理數(shù)據(jù)庫數(shù)據(jù)的物理模型：即指數(shù)據(jù)的存儲結(jié)構(gòu)，如對數(shù)據(jù)庫物理文件、索引文件的組織方式、文件的存取路徑，內(nèi)存的管理，等。物理模型不僅與數(shù)據(jù)庫管理系統(tǒng)有關(guān)，還和操作系統(tǒng)甚至硬件有關(guān)，物理模型對用戶是不可見的。按關(guān)系模型組織的數(shù)據(jù)表達方式簡潔、直觀，插入、刪除、修改操作方便，而按層次、網(wǎng)狀模型組織的數(shù)據(jù)表達方式復(fù)雜，插入、刪除、修改操作復(fù)雜。因此，關(guān)系模型得到了廣泛應(yīng)用，MySQL是一個典型的支持關(guān)系數(shù)據(jù)模型的數(shù)據(jù)庫管理系統(tǒng)。3.MySQL數(shù)據(jù)庫模型在數(shù)據(jù)庫效勞器中可以存儲多個數(shù)據(jù)庫文件，所以建立數(shù)據(jù)庫時要設(shè)定數(shù)據(jù)庫的文件名，每個數(shù)據(jù)庫有惟一的數(shù)據(jù)庫文件名作為與其它數(shù)據(jù)庫區(qū)別的標(biāo)識。數(shù)據(jù)庫文件：數(shù)據(jù)庫是由相關(guān)數(shù)據(jù)表組成，一個數(shù)據(jù)庫包括多個數(shù)據(jù)表。數(shù)據(jù)庫文件用于記錄數(shù)據(jù)庫中數(shù)據(jù)表構(gòu)成的信息。數(shù)據(jù)庫只能由得到授權(quán)的用戶訪問，這樣保證了數(shù)據(jù)庫的平安。成績數(shù)據(jù)庫xscj網(wǎng)絡(luò)圖書庫bookstore雇員管理庫bookstore……….庫MySQL服務(wù)器4.數(shù)據(jù)庫數(shù)據(jù)庫和表數(shù)據(jù)庫可以看成是一個存儲數(shù)據(jù)對象的容器，這些數(shù)據(jù)對象包括表、視圖、觸發(fā)器、存儲過程等，其中，表是最根本的數(shù)據(jù)對象，用以存放數(shù)據(jù)庫的數(shù)據(jù)的。必須首先創(chuàng)立數(shù)據(jù)庫，然后才能創(chuàng)立數(shù)據(jù)庫的數(shù)據(jù)對象。MySQL可以采用兩種方式創(chuàng)立、操作數(shù)據(jù)庫和數(shù)據(jù)對象〔1〕命令行方式〔2〕圖形界面工具方式數(shù)據(jù)表視圖存儲過程DELIMITER$$CREATEPROCEDUREDELETE_STUDENT(INXHCHAR(6))BEGIN

5.創(chuàng)立數(shù)據(jù)庫MySQL安裝后，系統(tǒng)自動地創(chuàng)立information_scema和MySQL數(shù)據(jù)庫，MySQL把有關(guān)數(shù)據(jù)庫的信息存儲在這兩個數(shù)據(jù)庫中。如果刪除了這些數(shù)據(jù)庫，MySQL就不能正常工作。對于用戶的數(shù)據(jù)，需要創(chuàng)立新的數(shù)據(jù)庫來存放。

語法格式：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]數(shù)據(jù)庫名[[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校對規(guī)那么名]說明：語句中“[]〞內(nèi)為可選項。{|}表示二選一。創(chuàng)立數(shù)據(jù)庫6.命令行方式創(chuàng)立數(shù)據(jù)庫在SQL語言創(chuàng)立數(shù)據(jù)庫命令createdatabase中，如果省略語句中“[]〞中的所有可選項，其結(jié)構(gòu)形式如下：mysql>createdatabasename;name表示被創(chuàng)立數(shù)據(jù)庫名，數(shù)據(jù)庫名必須符合以下規(guī)那么：數(shù)據(jù)庫名必須唯一；名稱內(nèi)不能含有“/〞及“.〞等非法字符；最大不能超過64字節(jié)?！纠?.1】創(chuàng)立一個名為Bookstore的數(shù)據(jù)庫。CREATEDATABASEBookstore;7.創(chuàng)立數(shù)據(jù)庫時使用IFNOTEXISTS如果在效勞器已有test1數(shù)據(jù)庫的情況下，再創(chuàng)立一個名為test1的數(shù)據(jù)庫。因為MySQL不允許兩個數(shù)據(jù)庫使用相同的名字，所以會出錯。使用IFNOTEXISTS從句可以不顯示錯誤信息，例如：createdatabaseIFNOTEXISTStest1;為了表達問題簡單，在以后的例如中單獨描述命令而不需要界面結(jié)果時，在命令前省略“mysql>〞提示符。注意：在MySQL中，每一條SQL語句都以“;〞作為結(jié)束標(biāo)志。8.因為MySQL效勞器中有多個數(shù)據(jù)庫，可以使用USE命令可指定當(dāng)前數(shù)據(jù)庫。語法格式：USEdb_name;說明：這個語句也可以用來從一個數(shù)據(jù)庫“跳轉(zhuǎn)〞到另一個數(shù)據(jù)庫，在用CREATEDATABASE語句創(chuàng)立了數(shù)據(jù)庫之后，該數(shù)據(jù)庫不會自動成為當(dāng)前數(shù)據(jù)庫，需要用這條USE語句來指定?！纠繉?shù)據(jù)庫test1設(shè)置為當(dāng)前數(shù)據(jù)庫。Usetest1use命令后直接跟數(shù)據(jù)庫名，然后按下回車鍵即完成設(shè)定過程使用這個命令之后，所有SQL命令都會應(yīng)用于該數(shù)據(jù)庫，這是幾個少數(shù)不需要分號結(jié)尾的命令。翻開數(shù)據(jù)庫9.MySQL中的字符集和校對規(guī)那么字符集是一套符號和編碼。校對規(guī)那么是在字符集內(nèi)用于比較字符的一套規(guī)那么。ASCII字符集中四個字母：‘A’、‘B’、‘a(chǎn)’、‘b’，每個字母賦予一個數(shù)值：‘A’=65，‘B’=66，‘a(chǎn)’=113，‘b’=114。字母‘A’是一個符號，數(shù)字65是‘A’的編碼，字母和它們的編碼組合在一起是一個字符集。假設(shè)我們希望比較兩個字符串的值：‘A’和‘b’。比較的最簡單的方法是查找編碼：‘A’為65，‘b’為114。因為65小于114，我們可以說‘A’小于‘b’。我們做的僅僅是在我們的字符集上應(yīng)用了一個校對規(guī)那么。10.MySQL字符集和校對規(guī)那么應(yīng)用MySQL支持30多種字符集的70多種校對規(guī)那么。在同一臺效勞器、同一個數(shù)據(jù)庫或甚至在同一個表中使用不同字符集或校對規(guī)那么來混合字符串。兩個不同的字符集不能有相同的校對規(guī)那么。每個字符集有一個默認校對規(guī)那么。例如，latin1默認校對規(guī)那么是latin1_swedish_ci，而gb2312默認校對規(guī)那么是gb2312_chinese_ci。ASCII碼字符集：使用7位二進制數(shù)來表示所有的大寫和小寫字母，數(shù)字0到9、標(biāo)點符號，以及在美式英語中使用的特殊控制字符GB2312是中國國家標(biāo)準(zhǔn)的簡體中文字符集。GB2312收錄簡化漢字及一般符號、序號、數(shù)字、拉丁字母、日文假名、希臘字母、俄文字母、漢語拼音符號、漢語注音字母，共7445個圖形字符?！纠?.2】創(chuàng)立一個名為Bookstore的數(shù)據(jù)庫，采用字符集gb2312和校對規(guī)那么gb2312_chinese_ci。CREATEDATABASEBookstore

DEFAULTCHARACTERSETgb2312COLLATEgb2312_chinese_ci;11.修改數(shù)據(jù)庫數(shù)據(jù)庫創(chuàng)立后，如果需要修改數(shù)據(jù)庫的參數(shù)，可以使用ALTERDATABASE命令。語法格式：ALTER{DATABASE|SCHEMA}[數(shù)據(jù)庫名][[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校對規(guī)那么名]

【例3.3】修改數(shù)據(jù)庫Pet的默認字符集為latin1，校對規(guī)那么為latin1_swedish_ci。ALTERDATABASEPetDEFAULTCHARACTERSETlatin1DEFAULTCOLLATElatin1_swedish_ci;12.刪除和顯示數(shù)據(jù)庫已經(jīng)創(chuàng)立的數(shù)據(jù)庫需要刪除，使用DROPDATABASE命令。語法格式：DROPDATABASE[IFEXISTS]數(shù)據(jù)庫名修改數(shù)據(jù)庫test2命令如下：DROPDATABASEtest2;顯示效勞器中已建立的數(shù)據(jù)庫，使用SHOWDATABASES命令。語法格式：SHOWDATABASES；13.任務(wù)二

數(shù)據(jù)庫表的操作

圖書編號書名出版時間單價數(shù)量…ISBN7115126836/tp.4235跟我學(xué)網(wǎng)頁設(shè)計2009-09-1023.5012ISBN730205701x/tp.3361網(wǎng)頁編程技術(shù)2008-10-0154.1523ISBN7301063423/tp.0731數(shù)據(jù)庫應(yīng)用技術(shù)2007-06-1033.2331圖書目錄表的表結(jié)構(gòu)分析字段名圖書編號書名出版時間單價數(shù)量…字段值的表示方法用20個字符編號用40個字符表示yyyy-mm-dd表示用帶有2位小數(shù)的5位數(shù)字表示用5位整數(shù)表示數(shù)據(jù)類型Char(20)Varchar(40)dateFloat(5)Int(5)14.數(shù)據(jù)類型〔1〕數(shù)值類型MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。嚴(yán)格數(shù)值數(shù)據(jù)類型：INTEGER或INT，SMALLINT，DECIMAL或DEC，NUMERIC近似數(shù)值數(shù)據(jù)類型：FLOAT，REAL，PRECISIONMySQL支持選擇在該類型關(guān)鍵字后面的括號內(nèi)指定整數(shù)值的顯示寬度(例如，INT(4))。對于浮點列類型，在MySQL中單精度值使用4個字節(jié)，雙精度值使用8個字節(jié)。MySQL允許使用FLOAT(M,D)或REAL(M,D)或DOUBLEPRECISION(M,D)格式。“(M,D)〞表示該值一共顯示M位整數(shù)，其中D位位于小數(shù)點后面。例如，定義為FLOAT(7,4)的一個列可以顯示為-999.9999。MySQL保存值時進行四舍五入，因此如果在FLOAT(7,4)列內(nèi)插入999.00009，近似結(jié)果是999.0001。15.字符串類型-CHAR〔2〕字符串類型：字符串類型的數(shù)據(jù)主要是由字母、漢字、數(shù)字符號、特殊符號構(gòu)成的數(shù)據(jù)對象。按照字符個數(shù)多少的不同分為以下幾類：CHAR：列的長度固定為創(chuàng)立表時聲明的長度。長度可以為從0到255的任何值。當(dāng)保存CHAR值時，在它們的右邊填充空格以到達指定的長度。例如,在學(xué)生情況表中，如果設(shè)定“namechar(8)〞，表示“name〞是8個字符長度的字符串，可以做賦值引用即：name=｀張三｀，這里name的值包括8個字符，其中2個漢字〔按照4個字符處理〕和4個空格。name右側(cè)會有假設(shè)干個空格占位。這樣浪費了磁盤的存儲空間。16.字符串類型-VARCHARVARCHAR：列中的值為可變長字符串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532字節(jié)〕。同CHAR比照，VARCHAR值保存時只保存需要的字符數(shù)。

例如,在學(xué)生情況表中，如果設(shè)定“namevarchar(8)〞，可以做賦值引用即：name=｀張三｀，這里姓名的值包括5個字符，其中2個漢字〔按照4個字符處理〕和1個空格。name右側(cè)只有一個空格占位。17.BLOB和TEXT類型BLOB：二進制字符串(字節(jié)字符串)。BLOB列沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。這種類型數(shù)據(jù)用于存儲聲音、視頻、圖像等數(shù)據(jù)。例如，圖書數(shù)據(jù)處理中的圖書封面、會員照片可以設(shè)定成為blob類型。TEXT：非二進制字符串(字符字符串)。TEXT列有一個字符集，并且根據(jù)字符集的校對規(guī)那么對值進行排序和比較。在實際應(yīng)用中像個人履歷、獎懲情況、職業(yè)說明、內(nèi)容簡介等設(shè)定為text的數(shù)據(jù)類型。例如，圖書數(shù)據(jù)處理中的內(nèi)容簡介可以設(shè)定為text類型。BLOB和TEXT列不能有默認值。BLOB或TEXT對象的最大大小由其類型確定，但在客戶端和效勞器之間實際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小，但必須同時修改效勞器和客戶端程序。18.日期和時間類型〔3〕日期和時間類型date：表示日期，輸入數(shù)據(jù)的格式是：yyyy-mm-dd。支持的范圍是'1000-01-01'到'9999-12-31'。time：表示時間，輸入數(shù)據(jù)的格式是：hh:mm:ss。TIME值的范圍可以從'-838:59:59'到'838:59:59'。小時局部會因此大的原因是TIME類型不僅可以用于表示一天的時間(必須小于24小時)，還可能為某個事件過去的時間或兩個事件之間的時間間隔(可以大于24小時，或者甚至為負)。datetime:表示日期時間，格式是：yyyy-mm-ddhh:mm:ss。支持的范圍為'1000-01-0100:00:00'到'9999-12-3123:59:59'。例如，在圖書銷售信息管理中注冊時間、訂購時間可以設(shè)定成為datetime類型。19.創(chuàng)立數(shù)據(jù)庫表為數(shù)據(jù)庫創(chuàng)立數(shù)據(jù)表，可以使用SQL命令createtable來完成。此語句的完整語法是相當(dāng)復(fù)雜的，因為存在很多的可選子句，但在實際中此語句的應(yīng)用相當(dāng)簡單。其根本結(jié)構(gòu)形式為：createtabletbl_name(create_definition,...)tbl_name為要創(chuàng)立的表名，括號內(nèi)為表的字段，多個字段用逗號隔開。20.CREATETABLE命令創(chuàng)立表使用CREATETABLE命令。語法格式：CREATETABLE[IFNOTEXISTS]表名〔列名數(shù)據(jù)類型[NOTNULL|NULL][DEFAULT列默認值]…〕ENGINE=存儲引擎 CREATETABLE命令的根本格式：CREATETABLEtbl_name〔列名1數(shù)據(jù)類型1[NOTNULL|NULL],列名2數(shù)據(jù)類型2[NOTNULL|NULL],…..〕

21.案例分析-創(chuàng)立學(xué)生根本情況表字段名字段類型寬度說明圖書編號char20字符,非NULL，主鍵書名varchar40字符,非NULL出版時間date20日期,非NULL單價Float(5,2)兩位小數(shù)，非NULL數(shù)量int

3位整數(shù),非NULL…圖書目錄表如下，在數(shù)據(jù)庫Bookstore中創(chuàng)立圖書目錄表Book。數(shù)據(jù)表結(jié)構(gòu)分析：圖書編號書名出版時間單價數(shù)量…ISBN7115126836/tp.4235網(wǎng)頁設(shè)計2009-09-1023.5012ISBN730205701x/tp.3361網(wǎng)頁編程技術(shù)2008-10-0154.1523ISBN7301063423/tp.0731數(shù)據(jù)庫技術(shù)2007-06-1033.233122.案例分析-創(chuàng)立XS表命令CREATETABLEbook(

圖書編號char(20)NOTNULLPRIMARYKEY,

圖書類別varchar(20)NOTNULLDEFAULT'計算機',

書名varchar(40)NOTNULL,

作者char(10)NOTNULL,

出版社varchar(20)NOTNULL,

出版時間dateNOTNULL,

單價float(5)NOTNULL,

數(shù)量int(5),

折扣float(3),

封面圖片blob)ENGINE=InnoDB;命令代碼：23.課堂練習(xí)創(chuàng)立實訓(xùn)3中的YGGL數(shù)據(jù)庫，并在該數(shù)據(jù)庫中創(chuàng)立員工信息表Employees，請寫出相關(guān)命令24.修改表ALTERTABLE用于更改原有表的結(jié)構(gòu)。例如，可以增加或刪減列，創(chuàng)立或取消索引，更改原有列的類型，重新命名列或表，還可以更改表的評注和表的類型。語法格式：ALTER[IGNORE]TABLE表名ADD[COLUMN]列名[FIRST|AFTER列名]/*添加列*/ |ALTER[COLUMN]列名{SETDEFAULT默認值|DROPDEFAULT}/*修改默認值*/|CHANGE[COLUMN]舊列名列定義 /*對列重命名*/[FIRST|AFTER列名] |MODIFY[COLUMN]列定義[FIRST|AFTER列名]/*修改列類型*/|DROP[COLUMN]列名 /*刪除列*/|RENAME[TO]新表名 /*重命名該表*/25.修改表舉例

【例3.5】假設(shè)已經(jīng)在數(shù)據(jù)庫Bookstore中創(chuàng)立了表book，表中存在“書名〞列。在表book中增加“瀏覽次數(shù)〞列并將表中的“書名〞列刪除。ALTERTABLEbookADD瀏覽次數(shù)tinyintNULL,DROPCOLUMN書名;【例3.6】假設(shè)數(shù)據(jù)庫Bookstore中已經(jīng)存在table1表，將table1表重命名為student。ALTERTABLEtable1RENAMETOstudent;

修改表名除了上面的ALTERTABLE命令，還可以直接用RENAMETABLE語句來更改表的名字。語法格式：RENAMETABLE舊表名1TO新表名1[,舊表名2TO新表名2]...【例3.7】假設(shè)數(shù)據(jù)庫BookStore中已經(jīng)存在table2表和table3表，將table2表重命名為orders，table3表重命名為orderlist。RENAMETABLEtable2TOorders,Table3TOorderlist;26.復(fù)制表語法格式：CREATETABLE[IFNOTEXISTS]新表名[LIKE參照表名]|[AS(select語句)] 使用LIKE關(guān)鍵字創(chuàng)立一個與old_table_name表相同結(jié)構(gòu)的新表，列名、數(shù)據(jù)類型、空指定和索引也將復(fù)制，但是表的內(nèi)容不會復(fù)制，因此創(chuàng)立的新表是一個空表。使用AS關(guān)鍵字可以復(fù)制表的內(nèi)容，但索引和完整性約束是不會復(fù)制的。【例3.8】假設(shè)數(shù)據(jù)庫BookStore中有一個表Book，創(chuàng)立Book表的一個名為book_copy1的拷貝。CREATETABLEbook_copy1LIKEBook;【例3.9】創(chuàng)立表Book的一個名為book_copy2的拷貝，并且復(fù)制其內(nèi)容。CREATETABLEbook_copy2AS(SELECT*FROMBook);27.刪除表需要刪除一個表時可以使用DROPTABLE語句。語法格式：

DROPTABLE[IFEXISTS]表名1[,表名2

]...這個命令將表的描述、表的完整性約束、索引及和表相關(guān)的權(quán)限等都全部刪除?！纠?.10】刪除表test。

USEXSCJDROPTABLEIFEXISTStest;28.顯示數(shù)據(jù)表信息〔1〕顯示數(shù)據(jù)表文件名語法格式：SHOWTABLES【例3.11】顯示Bookstore數(shù)據(jù)庫建立的數(shù)據(jù)表文件:USEBookstoreSHOWTABLES;〔2〕顯示數(shù)據(jù)表結(jié)構(gòu)語法格式：{DESCRIBE|DESC}表名[列名|通配符]DESC是DESCRIBE的簡寫，二者用法相同?！纠?.12】用DESCRIBE語句查看Book表的列的信息。USEBookstoreDESCRIBEBook;【例3.13】查看Book表圖書編號列的信息。USEBookstoreDESCBook圖書編號;29.課堂練習(xí)1、對departments表增加一列負責(zé)人〔manager)2、對employees表增加一列照片〔photo)，用以存放雇員照片，該字段可以為空。3、對salary表增加序號列(No)，用以對每筆工資收入支出進行編號。4、將salary表的income列改為整數(shù)型，6位4、復(fù)制departments表的結(jié)構(gòu)到bm表5、顯示gygl數(shù)據(jù)庫中的所有表6、顯示departments表的結(jié)構(gòu)7、刪除bm表30.31任務(wù)三圖形界面工具創(chuàng)立數(shù)據(jù)庫和表NavicatforMySQL：1.連接MySQL效勞器NavicatforMySQL是一個桌面版MySQL數(shù)據(jù)庫管理和開發(fā)工具，和微軟SQLServer的管理器很像，易學(xué)易用，很受大家的歡送。在上圖窗口輸入連接名〔任取〕，在“密碼(p)：〞輸入密碼，單擊“確定〞按鈕，登錄到數(shù)據(jù)庫效勞器中。31