PHP-50-程式設(shè)計(jì)第-11-章SQL-基本與法介紹課件

PHP5.0程式設(shè)計(jì)

第11章SQL基本與法介紹講師：紀(jì)俊男cnchi@.tw本章大綱資料庫建立與刪除資料表規(guī)劃資料表建立與刪除資料列新增、修改、刪除正規(guī)化關(guān)聯(lián)性更改資料表結(jié)構(gòu)顯示多表格資料總整理資料庫建立與刪除起始MySQL資料庫查看MySQL服務(wù)是否啟動(dòng)psaux|grepmysql啟動(dòng)MySQL服務(wù)RedHat:/etc/rc.d/init.d/mysqldstart以用戶端登入MySQL伺服器有密碼：mysql–uroot–p無密碼：mysqlMySQL文字型用戶端提示號(hào)mysql>資料庫建立與刪除建立資料庫指令CREATEDATABASE資料庫名稱;範(fàn)例人事資料庫CREATEDATABASEHumanResource;資料庫建立與刪除查看資料庫清單格式SHOWDATABASES;範(fàn)例SHOWDATABASES;資料庫建立與刪除刪除資料庫格式DROPDATABASE資料庫名稱;範(fàn)例DROPDATABASEHumanResource;資料庫建立與刪除隨堂練習(xí)登入MySQL資料庫伺服器請用psaux檢查MySQL是否已經(jīng)啟動(dòng)？若沒有，請啟動(dòng)它。請用mysql登入MySQL伺服器。建立資料庫請用CREATEDATABASE建立HumanResource資料庫。請用SHOWDATABASES檢查HumanResource是否建立成功？刪除資料庫請用DROPDATABASE將HumanResource資料庫刪除看看。請用SHOWDATABASES確認(rèn)HumanResource資料庫已經(jīng)刪除。還原資料庫請?jiān)儆肅REATEDATABASE將HumanResource資料庫建回來，結(jié)束這次的練習(xí)。資料表規(guī)劃Employees資料表初步規(guī)劃如下IDNameAddressTelSexBirthdaySalaryMemo整數(shù)(INT)文字(VARCHAR)文字(VARCHAR)文字(VARCHAR)布林值(TINYINT)日期(DATE)浮點(diǎn)數(shù)(FLOAT)文字(TEXT)資料表規(guī)劃MySQL允許的欄位資料型態(tài)如下整數(shù)

TINYINT 1byteSMALLINT 2bytesMEDIUMINT 3bytesINT/INTEGER 4bytes

BIGINT 8bytes浮點(diǎn)數(shù)

FLOAT 4bytes

DOUBLE/REAL 8bytesDECIMAL 視情況NUMERIC 視情況文字

CHAR(M) MbytesVARCHAR(M) L+1bytes

(L:實(shí)際長度M:最大長度<=255)

TEXT L+2bytes

(L:實(shí)際長度M:最大長度<216)日期時(shí)間

YEAR 1byte

DATE 3bytesTIME 3bytesDATETIME 8bytesTIMESTAMP 4bytes二進(jìn)位資料

BLOB L+2bytes

(L:真實(shí)長度最大長度<=216)列舉與集合

ENUM(‘…’,…) 1or2bytesSET(‘…’,…) 1,2,4,8資料表規(guī)劃課堂討論一個(gè)資料表為什麼要有PrimaryKey？電話欄位(Tel)都是數(shù)字，為什麼不用INT要用CHAR？SQL92有為布林值定義專屬的資料型態(tài)嗎？什麼是TEXT型態(tài)？它跟CHAR/VARCHAR有何不同？什麼時(shí)候會(huì)用到它？如果我要把一張JPG照片直接塞入資料庫中，那我要用哪種欄位型態(tài)來存它？為什麼講師說不建議將二進(jìn)位資料塞入資料庫中？有什麼替代方案嗎？資料表建立與刪除建立資料表格式CREATETABLE

資料表名稱(

欄位定義1,

欄位定義2,

…

);欄位定義欄位名稱欄位型態(tài)

[NOTNULL]

[DEFAULT預(yù)設(shè)值]

[AUTO_INCREMENT]

[PRIMARYKEY]PRIMARYKEY(欄位名稱1,欄位名稱2,…)FOREIGNKEY(欄位名稱1,欄位名稱2,…)UNIQUE(欄位名稱1,欄位名稱2,…)INDEX(欄位名稱1,欄位名稱2,…)CHECK(欄位條件)資料表建立與刪除建立資料表範(fàn)例欄位名稱欄位型態(tài)特殊要求IDINTPRIMARYKEY,AUTO_INCREMENT,NOTNULLNameVARCHAR(20)NOTNULLAddressVARCHAR(50)TelVARCHAR(20)SexTINYINT(1)NOTNULLBirthdayDATESalaryFLOAT(4)MemoTEXT資料表建立與刪除建立資料表範(fàn)例CREATETABLEEmployees(empIDINTPRIMARYKEYAUTO_INCREMENTNOTNULL,empNameVARCHAR(20)NOTNULL,empAddressVARCHAR(50),empTelVARCHAR(20),empSexTINYINT(1)NOTNULL,empBirthdayDATE,empSalaryFLOAT(4),empMemoTEXT);資料表建立與刪除查看資料表清單格式SHOWTABLES;範(fàn)例秀出剛剛建立的EmployeesSHOWTABLES;資料表建立與刪除查看資料表結(jié)構(gòu)格式DESCRIBE資料表名稱;範(fàn)例DESCRIBEEmployees;資料表建立與刪除變更資料表名稱格式RENAMETABLE舊名稱TO新名稱;範(fàn)例RENAMETABLEEmployeesTOWorkers;資料表建立與刪除刪除資料表格式DROPTABLE資料表名稱;範(fàn)例DROPTABLEWorkers;資料表建立與刪除隨堂練習(xí)建立資料表請依照講義的示範(fàn)，造出Employees資料表來。顯示資料表清單請用SHOWTABLES指令看看Employees是否已經(jīng)成功地造出來了。顯示資料表結(jié)構(gòu)請用DESCRIBE指令看看Employees表格的結(jié)構(gòu)是否與我們設(shè)定的相同。更改資料表名稱請用RENAMETABLE將Employees資料表改名為Workers刪除資料表請用DROPTABLE指令將Workers資料表刪除。還原請將Employees資料表再度造回來，以結(jié)束這一次的練習(xí)。資料列新增、修改、刪除新增一筆資料列格式INSERTINTO資料表名稱(欄位1,欄位2,…)

VALUES(值1,值2,…);範(fàn)例INSERTINTOEmployees( empName,empAddress,empTel, empSex,empBirthday,empSalary, empMemo)VALUES( ‘RobertChi’,‘1245ParkAve’,‘(02)23939889’, 1,‘1971-9-14’,35000.0,‘GoodPersonality’);資料列新增、修改、刪除觀看資料列格式SELECT欄位1,欄位2,…

FROM資料表名稱

[WHERE條件];範(fàn)例SELECT*

FROMEmployees;資料列新增、修改、刪除修改資料列格式UPDATE資料表名稱

SET欄位1=值1,欄位2=值2,…

WHERE條件;範(fàn)例UPDATEEmployees

SETempAddress=‘137-14MainStreet’

WHEREempName=‘RobertChi’;資料列新增、修改、刪除刪除資料列格式DELETEFROM資料表名稱

WHERE條件;範(fàn)例DELETEFROMEmployees

WHEREempName=‘RobertChi’;資料列新增、修改、刪除隨堂練習(xí)新增資料列請依照講義內(nèi)容，將‘RobertChi’的資料新增到資料庫內(nèi)。觀察新增的資料列請用SELECT指令觀察新增的資料列。請問一下，為什麼我們沒有對empID新增任何資料，卻自動(dòng)有值出現(xiàn)？修改資料列請用UPDATE指令將RobertChi的住址改為137-14MainStreet刪除資料列請用DELETEFROM指令將RobertChi這筆資料刪除。還原請將次頁資料新增至Employees資料表之內(nèi)為什麼新增的資料列編號(hào)不是從1開始？資料列新增、修改、刪除隨堂練習(xí)empNameempAddressempTelempSexempBirthdayempSalaryempMemoRobertChi1245ParkAve(02)2393988911971-9-1435000.0GoodPersonalityGloriaChen34-33MapleAve(02)2766200001979-3-241000.0ManagerLeoWang145-77MainSt(02)2882525211970-5-1245000.0RichGuy正規(guī)化為何需要正規(guī)化empNameempAddressempTelempSexempBirthdayempSalaryempMemoRobertChi1245ParkAve(02)2393988911971-9-1435000.0GoodPersonalityGloriaChen34-33MapleAve(02)2766200001979-3-241000.0ManagerLeoWang145-77MainSt(02)2882525211970-5-1245000.0RichGuy(02)23838938無法加入第二個(gè)電話或地址正規(guī)化為何需要正規(guī)化EmployeesempIDempNameempAddr1empAddr2empTel1empTel21RobertChiXxxxxxxx2GloriaChenxxxxxxxx3LeoWangxxxxxxxxxxxxxxxx比較不好的作法缺點(diǎn)：為了少數(shù)人浪費(fèi)大量空間

無法確定多開兩個(gè)欄位是否足夠正規(guī)化為何需要正規(guī)化比較好的作法EmployeesempIDempName1RobertChi2GloriaChen3LeoWangEmpAddraddrIDempIDAddress11XXXXX21XXXXX32XXXXX43XXXXXEmpTeltelIDempIDTel11XXXXX22XXXXX32XXXXX43XXXXX正規(guī)化何謂正規(guī)化(Normalization)將資料表依據(jù)一定的程序，分割成利於關(guān)聯(lián)式資料庫搜尋，比對，儲(chǔ)存的方法紀(jì)子定義：“切割表格”的“標(biāo)準(zhǔn)程序”正規(guī)化正規(guī)化五大步驟第一正規(guī)化(1NF,1stNormalForm)第二正規(guī)化(2NF,2ndNormalForm)第三正規(guī)化(3NF,3rdNormalForm)第四正規(guī)化(4NF,4thNormalForm)第五正規(guī)化(5NF,5thNormalForm)使用者編號(hào)姓名性別用戶種類地址1電話11魏安富男1公園路10號(hào)239398892岳京男1新生南路112號(hào)288252523魏笙綿女1濟(jì)南路4號(hào)，南昌街15號(hào)80076666,238389384曾桃燕女1華西街7號(hào)223344555駱宏男3瑞安街18號(hào)0806094496梅愛姿女6北宜路三段99號(hào)85211314正規(guī)化第一正規(guī)化(FirstNormalForm,1NF)定義：一個(gè)欄位只能放一種資料錯(cuò)誤示範(fàn)錯(cuò)誤原因：不方便排序、搜尋…等針對欄位運(yùn)作的動(dòng)作。正規(guī)化第二正規(guī)化(SecondNormalForm,2NF)定義：同一個(gè)表格內(nèi)的所有欄位，要與主欄位有意義上的相關(guān)錯(cuò)誤示範(fàn)錯(cuò)誤原因：真正需要“員工薪水”的表格，要參考這個(gè)值時(shí)可能不太方便。正規(guī)化第三正規(guī)化(ThirdNormalForm,3NF)定義：若在同一個(gè)表格內(nèi)，出現(xiàn)兩個(gè)主欄位，各自領(lǐng)導(dǎo)一群意義上相關(guān)的附屬欄位，則這種表格要切開。錯(cuò)誤示範(fàn)正規(guī)化第三正規(guī)化正確示範(fàn)原因：自成一群的欄位，代表他們有自己所代表的意義。獨(dú)立出來，才可供其它表格參考之。正規(guī)化第四正規(guī)化(ForthNormalForm,4NF)定義：若表格中出現(xiàn)有可能“一對多”的情況，則要把表格切開。錯(cuò)誤示範(fàn)正規(guī)化第四正規(guī)化正確示範(fàn)原因：這種一對多的欄位，你永遠(yuǎn)也不知道這個(gè)“多”到底是多少。留多了不好，留少了不夠。乾脆獨(dú)立出去，要多要少任君挑選。正規(guī)化第五正規(guī)化(FifthNormalForm,5NF)規(guī)定：將欄位再細(xì)分成“常用”與“不常用”兩種，將它們割開。示範(fàn)：原因：此舉是為了使表格越小越好，以利網(wǎng)路傳輸。如果您的系統(tǒng)沒有網(wǎng)路傳輸?shù)目剂?如：一切系統(tǒng)都在區(qū)域網(wǎng)路內(nèi)執(zhí)行)，您可以將表格切到4NF即可。正規(guī)化紀(jì)子要訣一人住一房，不得外人藏﹔若能成一家，自行走他方。屋內(nèi)不暇管，各自買一幢﹔莫居三合院，喜住小套房。不要擠有關(guān)係我獨(dú)大不多管小而美1NF2NF3NF4NF5NF正規(guī)化課堂討論請敘述第一正規(guī)化到第五正規(guī)化的定義正規(guī)化的原因關(guān)聯(lián)性何謂關(guān)聯(lián)性(Relationships)所謂關(guān)聯(lián)式資料庫，就是利用“正規(guī)化”將表格切開，再用“關(guān)聯(lián)性”將表格“黏”回原來的樣子。關(guān)聯(lián)性的種類一對一(OnetoOne,1-1)一對多(OnetoMany,1-∞)多對多(ManytoMany,∞-∞)關(guān)聯(lián)性一對一(OnetoOne,1-1)兩個(gè)表格之間的記錄，是一筆對一筆的每一筆“員工”的記錄，只會(huì)對應(yīng)到一筆“員工細(xì)部資料”。關(guān)聯(lián)性一對多(OnetoMany,1-∞)兩個(gè)表格之間的記錄，是一筆對多筆的。每一筆“使用者”表格內(nèi)的資料，都會(huì)對應(yīng)到一或多筆“電話”的資料。關(guān)聯(lián)性多對多

(ManytoMany,∞-∞)兩個(gè)表格之間的記錄，是多筆對多筆的。關(guān)聯(lián)式資料庫做不出來多對多的關(guān)係。如果遇到多對多的關(guān)係時(shí)，就在其中插入一個(gè)新表格，使之成為兩個(gè)一對多關(guān)係。關(guān)聯(lián)性經(jīng)過正規(guī)化後的Employees資料表EmployeesempIDempNameempSexempBirthdayempSalaryempMemoEmpAddressaddrIDempIDAddressEmpTelTelIDempIDTel關(guān)聯(lián)性課堂討論請說明關(guān)聯(lián)式資料庫如何做多對多的連結(jié)更改資料表結(jié)構(gòu)資料表最終長相EmployeesempIDempNameempSexempBirthdayempSalaryempMemoEmpAddressaddrIDempIDAddressEmpTelTelIDempIDTelEmployeesempIDempNameempAddressempTelempSexempBirthdayempSalaryempMemo更改資料表結(jié)構(gòu)資料表變更程序建造新表格CREATETABLEEmpAddressCREATETABLEEmpTel將舊資料轉(zhuǎn)換到新表格刪除舊資料表的兩個(gè)欄位更改資料表結(jié)構(gòu)建造新表格CREATETABLE

EmpAddress(addrIDINTPRIMARYKEYAUTO_INCREMENTNOTNULL,empIDINTNOTNULL,AddressVARCHAR(50));CREATETABLE

EmpTel(telIDINTPRIMARYKEYAUTO_INCREMENTNOTNULL,empIDINTNOTNULL,TelVARCHAR(20));更改資料表結(jié)構(gòu)將舊資料轉(zhuǎn)換到新表格格式INSERTINTO資料表名稱(欄位1,欄位2,…)

SELECT欄位1,欄位2,…

FROM資料表名稱

[WHERE條件];範(fàn)例INSERTINTOEmpAddress(empID,Address)SELECTempID,empAddressFROMEmployees;INSERTINTOEmpTel(empID,Tel)SELECTempID,empTelFROMEmployees;更改資料表結(jié)構(gòu)刪除舊資料表的欄位格式ALTERTABLE資料表名稱

[ADDCOLUMN欄位定義[FIRST|AFTER欄位]]

[DROPCOLUMN欄位名稱];範(fàn)例ALTERTABLEEmployeesDROPCOLUMNempAddress;ALTERTABLEEmployeesDROPCOLUMNempTel;更改資料表結(jié)構(gòu)隨堂練習(xí)建造新表格請依照講義的指示，造出EmpAddress以及EmpTel兩個(gè)表格出來。轉(zhuǎn)換資料請用INSERTINTO~SELECT指令，將原來屬於舊表格的資料轉(zhuǎn)到新表格來。刪除欄位請用ALTERTABLE指令，將兩個(gè)不要的empAddress以及empTel欄位刪除。顯示多表格資料如何同時(shí)顯示多表格的資料利用“連結(jié)”(Join)何謂表格連結(jié)兩個(gè)表格間，利用“主鍵(PrimaryKey)”

與“外鍵