PHP-50-程式设计第-11-章SQL-基本与法介绍课件

PHP5.0程式設計

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

TINYINT 1byteSMALLINT 2bytesMEDIUMINT 3bytesINT/INTEGER 4bytes

BIGINT 8bytes浮點數

FLOAT 4bytes

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

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

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

TEXT L+2bytes

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

YEAR 1byte

DATE 3bytesTIME 3bytesDATETIME 8bytesTIMESTAMP 4bytes二進位資料

BLOB L+2bytes

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

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

資料表名稱(

欄位定義1,

欄位定義2,

…

);欄位定義欄位名稱欄位型態

[NOTNULL]

[DEFAULT預設值]

[AUTO_INCREMENT]

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

VALUES(值1,值2,…);範例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條件];範例SELECT*

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

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

WHERE條件;範例UPDATEEmployees

SETempAddress=‘137-14MainStreet’

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

WHERE條件;範例DELETEFROMEmployees

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

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

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

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

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

SELECT欄位1,欄位2,…

FROM資料表名稱

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

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

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

與“外鍵