db3數(shù)據(jù)庫數(shù)據(jù)表創(chuàng)建管理

db3數(shù)據(jù)庫數(shù)據(jù)表創(chuàng)建管理第一頁，共65頁。創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表第二頁，共65頁。SQL語句的書寫規(guī)范在數(shù)據(jù)庫系統(tǒng)中，SQL語句不區(qū)分大小寫(建議用大寫)。但字符串常量區(qū)分大小寫。SQL語句可單行或多行書寫，以“;”結(jié)尾。關(guān)鍵詞不能跨多行或簡寫。用空格和縮進(jìn)來提高語句的可讀性。子句通常位于獨(dú)立行，便于編輯，提高可讀性。SELECT*FROMtb_table注釋：SQL標(biāo)準(zhǔn)：/**/。多行注釋“--”單行注釋MySql注釋：“#”第三頁，共65頁。創(chuàng)建數(shù)據(jù)庫建立數(shù)據(jù)庫操作：

語法：createdatabase數(shù)據(jù)庫名敘述：創(chuàng)建一個具有指定名稱的數(shù)據(jù)庫。如果要創(chuàng)建的數(shù)據(jù)庫已經(jīng)存在，或者沒有創(chuàng)建它的適當(dāng)權(quán)限，則此語句失敗。例：建立一個student庫。

mysql>createdatabasestudent;第四頁，共65頁。查看數(shù)據(jù)庫命令：showdatabases;列出當(dāng)前數(shù)據(jù)庫服務(wù)器實例下所有的數(shù)據(jù)庫切換數(shù)據(jù)庫：use數(shù)據(jù)庫的名字，例：usestudents;第五頁，共65頁。SHOWDATABASES語法：SHOWDATABASES[LIKEwild]；如果使用LIKEwild不符，wild字符串可以是一個使用SQL的”%”和”_”通配符的字符串。功能：列出在MySql服務(wù)器主機(jī)上的數(shù)據(jù)庫。MySql自帶數(shù)據(jù)庫：Information_schema:主要存儲了系統(tǒng)中的一些數(shù)據(jù)庫對象信息：如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等。(數(shù)據(jù)字典表)Mysql:存儲了系統(tǒng)的用戶權(quán)限信息及幫助信息。Test:系統(tǒng)自動創(chuàng)建的測試數(shù)據(jù)庫，任何用戶都可以使用。第六頁，共65頁。使用USE選用數(shù)據(jù)庫語法：USE數(shù)據(jù)庫名;功能：把指定數(shù)據(jù)庫作為默認(rèn)(當(dāng)前)數(shù)據(jù)庫使用，用于后續(xù)語句。第七頁，共65頁。修改數(shù)據(jù)庫查看數(shù)據(jù)庫信息:status;命令語句：alterdatabase數(shù)據(jù)庫名字charactersetgb2312修改字符集Defaultcollategb2312_Chinese_ci;修改排序規(guī)則

第八頁，共65頁。數(shù)據(jù)庫對象第九頁，共65頁。數(shù)據(jù)庫對象的命名規(guī)則必須以字母開頭可包括數(shù)字和三個特殊字符（#_$）不要使用MySQL的保留字同一Schema下的對象不能同名第十頁，共65頁。建立表操作：語法：createtable表名（列名1列類型[<列的完整性約束>],列名2列類型[<列的完整性約束>],......);敘述：在當(dāng)前數(shù)據(jù)庫下新創(chuàng)建一個數(shù)據(jù)表。列類型：表示該列的數(shù)據(jù)類型。例：建立一個表school,其由兩列組成，第一列屬性為非空，并做為主鍵,并自增createtableschool(school_idint(10)notnullauto_incrementprimarykey,school_namevarchar(20) )；創(chuàng)建表第十一頁，共65頁。常見完整性約束：PRIMARYKEY 主碼約束(主鍵)UNIQUE 唯一性約束NOTNULL 非空值約束 AUTO_INCREMENT 用于整數(shù)列默認(rèn)自增1UNSIGNED無符號整數(shù)DEFAULTdefault_value 默認(rèn)值約束DEFAULTcur_timestamp創(chuàng)建新記錄時默認(rèn)保存當(dāng)前時間（僅適用timestamp數(shù)據(jù)列）ONUPDATEcur_timestamp修改記錄時默認(rèn)保存當(dāng)前時間（僅適用timestamp數(shù)據(jù)列）CHARACTERSETname指定字符集（僅適用字符串）第十二頁，共65頁。數(shù)據(jù)表類型在創(chuàng)建一個新的MySQL數(shù)據(jù)表時，可以為它設(shè)置一個類型，其中最重要的3種類型是MyISAM：成熟、穩(wěn)定和易于管理InnoDB：加入事務(wù)、數(shù)據(jù)行級鎖定機(jī)制、外鍵約束條件、崩潰恢復(fù)等新功能HEAP：只存在于內(nèi)存中，可做臨時表createtabletmp(…)ENGINE=MyISAM第十三頁，共65頁。列類型MySql支持多種列類型：數(shù)值類型日期/時間類型字符串(字符)類型第十四頁，共65頁。數(shù)值數(shù)據(jù)類型-整數(shù)第十五頁，共65頁。數(shù)值數(shù)據(jù)類型-浮點(diǎn)數(shù)第十六頁，共65頁。日期/時間類型第十七頁，共65頁。字符串類型第十八頁，共65頁。思考電話號碼一般使用什么數(shù)據(jù)類型存儲?性別一般使用什么數(shù)據(jù)類型存儲?年齡信息一般使用什么數(shù)據(jù)類型存儲?照片信息一般使用什么數(shù)據(jù)類型存儲?薪水一般使用什么數(shù)據(jù)類型存儲?第十九頁，共65頁。關(guān)于識別符第二十頁，共65頁。建表示例第二十一頁，共65頁。建表示例第二十二頁，共65頁。SHOW語法：SHOWTABLES[FROM數(shù)據(jù)庫名][LIKEwild];功能：顯示當(dāng)前數(shù)據(jù)庫中已有的數(shù)據(jù)表的信息第二十三頁，共65頁。顯示表結(jié)構(gòu)操作語法：describe表名；

desc 表名；敘述：用于顯示表的創(chuàng)建結(jié)構(gòu)。第二十四頁，共65頁。

刪除數(shù)據(jù)庫或表操作刪除表操作語法：droptable[ifexists]tab_name[,tab_name]...敘述：從數(shù)據(jù)庫中刪除給定的表。如果給出ifexists子句，則刪除不存在的表不會出錯。刪除數(shù)據(jù)庫操作語法：dropdatabase[ifexists]db_name敘述：刪除給定的數(shù)據(jù)庫。在刪除一個數(shù)據(jù)庫后，它就永遠(yuǎn)沒有了，因此要特別小心。如果給出ifexists子句，則刪除不存在的數(shù)據(jù)庫不會出錯。刪除命令要慎用第二十五頁，共65頁。用ALTERTABLE語句修改表的結(jié)構(gòu)修改列類型ALTERTABLE表名MODIFY列名列類型;增加列ALTERTABLE表名ADD列名列類型;刪除列ALTERTABLE表名DROP列名;列改名ALTERTABLE表名CHANGE舊列名新列名列類型；更改表名ALTERTABLE表名RENAME新表名；RENAMETABLE表名TO新表名；第二十六頁，共65頁。在表中增加字段使用ADD子句增加字段，新的字段只能被加到整個表的最后

–ALTERTABLEemployees –ADD(genderCHAR(1));第二十七頁，共65頁。修改字段可修改列的數(shù)據(jù)類型,大小和默認(rèn)值

–ALTERTABLEemployees –MODIFY(agenumber(1));不是任何情況都可以修改的，當(dāng)字段只包含空值時，類型、大小都可以修改，否則修改可能不能成功修改默認(rèn)值，只會對以后的插入有作用，對以前已經(jīng)存在的數(shù)據(jù)不會有影響第二十八頁，共65頁。刪除字段可以從表中刪除列：

–ALTERTABLEemployees –DROPCOLUMNsex;從每行中刪除掉字段占據(jù)的長度和數(shù)據(jù),釋放在數(shù)據(jù)塊中占用的空間.刪除大表中的字段將需要比較長的時間，因為還需要回收空間第二十九頁，共65頁。刪除表表中所有數(shù)據(jù)將被刪除沒有完成的事務(wù)被提交所有相關(guān)的索引被刪除不能回滾表結(jié)構(gòu)被刪除，表對象不再存在DROPTABLEtable_name;第三十頁，共65頁。更改表名renametable...to...支持一次重命名多個表。RENAMETABLEold_table1TOtmp_table,new_tableTOold_table,tmp_tableTOnew_table;－完成表名交換。第三十一頁，共65頁。MYSQL數(shù)據(jù)庫中的表用戶表: –被用戶創(chuàng)建和維護(hù)的一些表

–包括了用戶自己的信息數(shù)據(jù)字典表(視圖): –被數(shù)據(jù)庫系統(tǒng)創(chuàng)建和維護(hù)的一些表

–包括了數(shù)據(jù)庫的信息第三十二頁，共65頁。MYSQL數(shù)據(jù)庫字典由information_schema數(shù)據(jù)庫負(fù)責(zé)維護(hù)tables－存放數(shù)據(jù)庫里所有的數(shù)據(jù)表、以及每個表所在數(shù)據(jù)庫。schema－存放數(shù)據(jù)庫里所有的數(shù)據(jù)庫信息views－存放數(shù)據(jù)庫里所有的視圖信息。columns－存放數(shù)據(jù)庫里所有的列信息。triggers－存放數(shù)據(jù)庫里所有的觸發(fā)器。routines－存放數(shù)據(jù)庫里所有存儲過程和函數(shù)。key_column_usage－存放數(shù)據(jù)庫所有的主外鍵table_constraints－存放數(shù)據(jù)庫全部約束。statistics－存放了數(shù)據(jù)表的索引。第三十三頁，共65頁。什么是約束約束是在表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗規(guī)則.約束主要用于保證數(shù)據(jù)庫的完整性。當(dāng)表中數(shù)據(jù)有相互依賴性時,可以保護(hù)相關(guān)的數(shù)據(jù)不被刪除.大部分?jǐn)?shù)據(jù)庫支持下面五類完整性約束:NOTNULL非空UNIQUEKey唯一鍵PRIMARYKEY主鍵FOREIGNKEY外鍵CHECK檢查第三十四頁，共65頁。約束概述約束作為數(shù)據(jù)庫對象，存放在系統(tǒng)表中，也有自己的名字創(chuàng)建約束的時機(jī)在建表的同時創(chuàng)建建表后創(chuàng)建（修改表）可定義列級或表級約束有單列約束和多列約束第三十五頁，共65頁。定義約束的語法第三十六頁，共65頁。非空約束(NOTNULL)列級約束，只能使用列級約束語法定義。確保字段值不允許為空只能在字段級定義第三十七頁，共65頁。NULL值所有數(shù)據(jù)類型的值都可以是NULL?？兆址坏扔贜ULL。0也不等于NULL。第三十八頁，共65頁。非空約束(NOTNULL)CREATETABLEtb_student( idINTPRIMARYKEYAUTO_INCREMENT, NAMEVARCHAR(18)NOTNULL )第三十九頁，共65頁。唯一約束唯一性約束條件確保所在的字段或者字段組合不出現(xiàn)重復(fù)值唯一性約束條件的字段允許出現(xiàn)多個NULL同一張表內(nèi)可建多個唯一約束唯一約束可由多列組合而成建唯一約束時MySQL會為之建立對應(yīng)的索引。如果不給唯一約束起名，該唯一約束默認(rèn)與列名相同。第四十頁，共65頁。唯一約束CREATETABLEtb_student( idINTPRIMARYKEYAUTO_INCREMENT, NAMEVARCHAR(18)UNIQUENOTNULL)第四十一頁，共65頁。主鍵約束主鍵從功能上看相當(dāng)于非空且唯一一個表中只允許一個主鍵主鍵是表中唯一確定一行數(shù)據(jù)的字段主鍵字段可以是單字段或者是多字段的組合當(dāng)建立主鍵約束時，MySQL為主鍵創(chuàng)建對應(yīng)的索引主鍵約束名總為PRIMARY。第四十二頁，共65頁。外鍵約束外鍵是構(gòu)建于一個表的兩個字段或者兩個表的兩個字段之間的關(guān)系外鍵確保了相關(guān)的兩個字段的兩個關(guān)系：子(從)表外鍵列的值必須在主表參照列值的范圍內(nèi)，或者為空（也可以加非空約束，強(qiáng)制不允許為空）。當(dāng)主表的記錄被子表參照時，主表記錄不允許被刪除。外鍵參照的只能是主表主鍵或者唯一鍵，保證子表記錄可以準(zhǔn)確定位到被參照的記錄。格式FOREIGNKEY(外鍵列名)REFERENCES主表(參照列)第四十三頁，共65頁。主鍵與外鍵數(shù)據(jù)表之間的關(guān)聯(lián)/引用關(guān)系是依靠具體的主鍵（primarykey）和外鍵（foreignkey）建立起來的。主鍵：幫助MySQL以最快的速度把一條特點(diǎn)的數(shù)據(jù)記錄的位置確定下來。主鍵必須是唯一的主鍵應(yīng)該是緊湊的，因此整數(shù)類型比較適合外鍵：引用另外一個數(shù)據(jù)表的某條記錄。外鍵列類型盡可能與主鍵列類型保持一致外鍵列應(yīng)該加上NOTNULL第四十四頁，共65頁。主鍵createtablestudent( sidintnotnullauto_increment, namevarchar(20)notnull,

primarykey(sid));外鍵（自動檢查外鍵是否匹配，僅適用InnoDB）createtablescore( cidintnotnullauto_incrementprimarykey, scoreint, sidint,

foreignkey(sid)referencesstudent(sid));第四十五頁，共65頁。主表和從表1、當(dāng)主表中沒有對應(yīng)的記錄時，不能將記錄添加到子表

——成績表中不能出現(xiàn)在學(xué)員信息表中不存在的學(xué)號；2、不能更改主表中的值而導(dǎo)致子表中的記錄孤立

——把學(xué)員信息表中的學(xué)號改變了，學(xué)員成績表中的學(xué)號也應(yīng)當(dāng)隨之改變；3、子表存在與主表對應(yīng)的記錄，不能從主表中刪除該行

——不能把有成績的學(xué)員刪除了4、刪除主表前，先刪子表

——先刪學(xué)員成績表、后刪除學(xué)員信息表第四十六頁，共65頁。選擇主鍵的原則最少性盡量選擇單個鍵作為主鍵穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵第四十七頁，共65頁。主鍵約束CREATETABLEtb_student( idINTPRIMARYKEYAUTO_INCREMENT, NAMEVARCHAR(18))第四十八頁，共65頁。外鍵約束CREATETABLEtb_dept( dept_idINTPRIMARYKEY, NAMEVARCHAR(18), descriptionVARCHAR(255) );CREATETABLEtb_employee( employee_idINTPRIMARYKEY, NAMEVARCHAR(18), genderVARCHAR(10), dept_idINTREFERENCEStb_dept(dept_id), addressVARCHAR(255));第四十九頁，共65頁。外鍵約束CREATETABLEtb_dept( dept_idINTPRIMARYKEY, NAMEVARCHAR(18), descriptionVARCHAR(255) );CREATETABLEtb_employee( employee_idINTPRIMARYKEY, NAMEVARCHAR(18), genderVARCHAR(10), dept_idINT, addressVARCHAR(255),

CONSTRAINTFOREIGNKEYtb_employee_fk(dept_id)REFERENCEStb_dept(dept_id));第五十頁，共65頁。Check約束既可作為列級約束，也可作為表級約束定義在字段上的每一記錄都要滿足的條件在check中定義檢查的條件表達(dá)式，數(shù)據(jù)需要符合設(shè)置的條件條件表達(dá)式不允許使用–參照其他記錄的值

CREATETABLEtb_student( idINTPRIMARYKEYAUTO_INCREMENT, NAMEVARCHAR(18),

ageINTCHECK(age>18ANDage<60) )第五十一頁，共65頁。建立表之間的關(guān)系第五十二頁，共65頁。示例第五十三頁，共65頁。列級約束與表級約束列級約束直接跟在列后定義，不再需要指定列名，與列定義之間用空格分開表級約束通常放在所有的列定義之后定義，要顯式指定對哪些列建立列級約束。與列定義之間采用英語逗號,隔開。如果是對多列建聯(lián)合約束，只能使用表級約束語法。第五十四頁，共65頁。增加約束可增加或刪除約束，但不能直接修改可使約束啟用和禁用非空約束必須使用MODIFY子句增加只要是可以使用列級約束語法來定義的約束，都可以通過modify來增加該約束。

–ALTERTABLEtable –ADD[CONSTRAINTconstraint]type(column);第五十五頁，共65頁。增加約束加FOREIGNKEY約束到EMP表

–ALTERTABLEtb_employee ADDCONSTRAINTtb_employee_dept_id_fk FOREIGNKEY(dept_id) REFERENCEStb_dept(dept_id);第五十六頁，共65