PHP50程式设计第11章SQL基本与法介绍课件.ppt

1、PHP 5.0 程式設計第 11 章 SQL 基本與法介紹講師：紀俊男.tw本章大綱n資料庫建立與刪除n資料表規劃n資料表建立與刪除n資料列新增、修改、刪除n正規化n關聯性n更改資料表結構n顯示多表格資料n總整理資料庫建立與刪除n起始 MySQL 資料庫查看 MySQL 服務是否啟動nps aux|grep mysql啟動 MySQL 服務nRedHat:/etc/rc.d/init.d/mysqld start以用戶端登入 MySQL 伺服器n有密碼：mysql u root pn無密碼：mysqlMySQL 文字型用戶端提示號nmysql資料庫建立與刪除n建立資料庫指令nCREATE DA

2、TABASE 資料庫名稱資料庫名稱;範例n人事資料庫nCREATE DATABASE HumanResource;資料庫建立與刪除n查看資料庫清單格式nSHOW DATABASES;範例nSHOW DATABASES;資料庫建立與刪除n刪除資料庫格式nDROP DATABASE 資料庫名稱資料庫名稱;範例nDROP DATABASE HumanResource;資料庫建立與刪除n隨堂練習登入 MySQL 資料庫伺服器n請用 ps aux 檢查 MySQL 是否已經啟動？若沒有，請啟動它。n請用 mysql 登入 MySQL 伺服器。建立資料庫n請用 CREATE DATABASE 建立 Hum

3、anResource 資料庫。n請用 SHOW DATABASES 檢查 HumanResource 是否建立成功？刪除資料庫n請用 DROP DATABASE 將 HumanResource 資料庫刪除看看。n請用 SHOW DATABASES 確認 HumanResource 資料庫已經刪除。還原資料庫n請再用 CREATE DATABASE 將 HumanResource 資料庫建回來，結束這次的練習。資料表規劃nEmployees 資料表初步規劃如下IDNameAddressTelSexBirthdaySalaryMemo整數整數(INT)文字文字(VARCHAR)文字文字(VARCH

4、AR)文字文字(VARCHAR)布林值布林值(TINYINT)日期日期(DATE)浮點數浮點數(FLOAT)文字文字(TEXT)資料表規劃nMySQL 允許的欄位資料型態如下整數整數 TINYINT1 byte SMALLINT2 bytes MEDIUMINT3 bytes INT/INTEGER4 bytes BIGINT8 bytes浮點數浮點數 FLOAT4 bytes DOUBLE/REAL8 bytes DECIMAL視情況 NUMERIC視情況文字文字 CHAR(M)M bytes VARCHAR(M)L+1 bytes (L:實際長度 M:最大長度=255)TEXTL+2 by

5、tes (L:實際長度 M:最大長度 216)日期時間日期時間 YEAR1 byte DATE3 bytes TIME3 bytes DATETIME8 bytesTIMESTAMP4 bytes二進位資料二進位資料 BLOBL+2 bytes (L:真實長度 最大長度=216)列舉與集合列舉與集合 ENUM(,)1 or 2 bytes SET(,)1,2,4,8資料表規劃n課堂討論一個資料表為什麼要有 Primary Key？電話欄位(Tel)都是數字，為什麼不用 INT 要用 CHAR？SQL92 有為布林值定義專屬的資料型態嗎？什麼是 TEXT 型態？它跟 CHAR/VARCHAR 有

6、何不同？什麼時候會用到它？如果我要把一張 JPG 照片直接塞入資料庫中，那我要用哪種欄位型態來存它？為什麼講師說不建議將二進位資料塞入資料庫中？有什麼替代方案嗎？資料表建立與刪除n建立資料表格式nCREATE TABLE 資料表名稱(欄位定義1,欄位定義2,);欄位定義n欄位名稱 欄位型態NOT NULLDEFAULT 預設值AUTO_INCREMENTPRIMARY KEYnPRIMARY KEY(欄位名稱1,欄位名稱2,)nFOREIGN KEY(欄位名稱1,欄位名稱2,)nUNIQUE(欄位名稱1,欄位名稱2,)nINDEX(欄位名稱1,欄位名稱2,)nCHECK(欄位條件)資料表建立與

7、刪除n建立資料表範例欄位名稱欄位型態特殊要求IDINTPRIMARY KEY,AUTO_INCREMENT,NOT NULLNameVARCHAR(20)NOT NULLAddressVARCHAR(50)TelVARCHAR(20)SexTINYINT(1)NOT NULLBirthdayDATESalaryFLOAT(4)MemoTEXT資料表建立與刪除n建立資料表範例CREATE TABLE Employees(empID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,empName VARCHAR(20)NOT NULL,empAddress VARC

8、HAR(50),empTel VARCHAR(20),empSex TINYINT(1)NOT NULL,empBirthday DATE,empSalary FLOAT(4),empMemo TEXT);資料表建立與刪除n查看資料表清單格式nSHOW TABLES;範例n秀出剛剛建立的 EmployeesnSHOW TABLES;資料表建立與刪除n查看資料表結構格式nDESCRIBE 資料表名稱資料表名稱;範例nDESCRIBE Employees;資料表建立與刪除n變更資料表名稱格式nRENAME TABLE 舊名稱 TO 新名稱;範例nRENAME TABLE Employees TO

9、Workers;資料表建立與刪除n刪除資料表格式nDROP TABLE 資料表名稱資料表名稱;範例nDROP TABLE Workers;資料表建立與刪除n隨堂練習建立資料表n請依照講義的示範，造出 Employees 資料表來。顯示資料表清單n請用 SHOW TABLES 指令看看 Employees 是否已經成功地造出來了。顯示資料表結構n請用 DESCRIBE 指令看看 Employees 表格的結構是否與我們設定的相同。更改資料表名稱n請用 RENAME TABLE 將 Employees 資料表改名為 Workers刪除資料表n請用 DROP TABLE 指令將 Workers 資料

10、表刪除。還原n請將 Employees 資料表再度造回來，以結束這一次的練習。資料列新增、修改、刪除n新增一筆資料列格式nINSERT INTO 資料表名稱資料表名稱(欄位欄位1,欄位欄位2,)VALUES(值值1,值值2,);範例INSERT INTO Employees(empName,empAddress,empTel,empSex,empBirthday,empSalary,empMemo)VALUES(Robert Chi,1245 Park Ave,(02)23939889,1,1971-9-14,35000.0,Good Personality);資料列新增、修改、刪除n觀看資料

11、列格式nSELECT 欄位欄位1,欄位欄位2,FROM 資料表名稱資料表名稱WHERE 條件條件;範例nSELECT *FROM Employees;資料列新增、修改、刪除n修改資料列格式nUPDATE 資料表名稱資料表名稱SET 欄位欄位1=值值1,欄位欄位2=值值2,WHERE 條件條件;範例nUPDATE EmployeesSET empAddress=137-14 Main StreetWHERE empName=Robert Chi;資料列新增、修改、刪除n刪除資料列格式nDELETE FROM 資料表名稱資料表名稱WHERE 條件條件;範例nDELETE FROM Employee

12、sWHERE empName=Robert Chi;資料列新增、修改、刪除n隨堂練習新增資料列n請依照講義內容，將 Robert Chi 的資料新增到資料庫內。觀察新增的資料列n請用 SELECT 指令觀察新增的資料列。n請問一下，為什麼我們沒有對 empID 新增任何資料，卻自動有值出現？修改資料列n請用 UPDATE 指令將 Robert Chi 的住址改為 137-14 Main Street刪除資料列n請用 DELETE FROM 指令將 Robert Chi 這筆資料刪除。還原n請將次頁資料新增至 Employees 資料表之內n為什麼新增的資料列編號不是從 1 開始？資料列新增、修

13、改、刪除n隨堂練習empNameempAddressempTelempSexempBirthdayempSalaryempMemoRobert Chi1245 Park Ave(02)2393988911971-9-1435000.0Good PersonalityGloria Chen34-33 Maple Ave(02)2766200001979-3-241000.0ManagerLeo Wang145-77 Main St(02)2882525211970-5-1245000.0Rich Guy正規化n為何需要正規化empNameempAddressempTelempSexempBirt

14、hdayempSalaryempMemoRobert Chi1245 Park Ave(02)2393988911971-9-1435000.0Good PersonalityGloria Chen34-33 Maple Ave(02)2766200001979-3-241000.0ManagerLeo Wang145-77 Main St(02)2882525211970-5-1245000.0Rich Guy(02)23838938無法加入第二個電話或地址無法加入第二個電話或地址正規化n為何需要正規化EmployeesempIDempNameempAddr1empAddr2empTel1e

15、mpTel21Robert ChiXxxxxxxx2Gloria Chenxxxxxxxx3Leo Wangxxxxxxxxxxxxxxxx比較不好的作法比較不好的作法缺點：為了少數人浪費大量空間缺點：為了少數人浪費大量空間 無法確定多開兩個欄位是否足夠無法確定多開兩個欄位是否足夠正規化n為何需要正規化比較好的作法比較好的作法EmployeesempIDempName1Robert Chi2Gloria Chen3Leo WangEmpAddraddrIDempIDAddress11XXXXX21XXXXX32XXXXX43XXXXXEmpTeltelIDempIDTel11XXXXX22XX

16、XXX32XXXXX43XXXXX正規化n何謂正規化(Normalization)將資料表依據一定的程序，分割成利於關聯式資料庫搜尋，比對，儲存的方法紀子定義：“切割表格”的“標準程序”正規化n正規化五大步驟第一正規化(1NF,1st Normal Form)第二正規化(2NF,2nd Normal Form)第三正規化(3NF,3rd Normal Form)第四正規化(4NF,4th Normal Form)第五正規化(5NF,5th Normal Form)使用者使用者編號編號姓名姓名性別性別用戶種類用戶種類地址地址1 1電話電話1 11魏安富男1公園路10號239398892岳 京男1

17、新生南路112號288252523魏笙綿女1濟南路4號，南昌街15號80076666,238389384曾桃燕女1華西街7號223344555駱 宏男3瑞安街18號0806094496梅愛姿女6北宜路三段99號85211314正規化n第一正規化(First Normal Form,1NF)定義：一個欄位只能放一種資料錯誤示範錯誤原因：不方便排序、搜尋錯誤原因：不方便排序、搜尋等針對欄位運作的動作。等針對欄位運作的動作。正規化n第二正規化(Second Normal Form,2NF)定義：同一個表格內的所有欄位，要與主欄位有意義上的相關錯誤示範訂單訂單 訂單編號 訂戶姓名 訂閱日期 訂閱期數

18、員工薪水?錯誤原因：真正需要錯誤原因：真正需要“員工薪水員工薪水”的表格，要參考這個值時可能不太方便。的表格，要參考這個值時可能不太方便。正規化n第三正規化(Third Normal Form,3NF)定義：若在同一個表格內，出現兩個主欄位，各自領導一群意義上相關的附屬欄位，則這種表格要切開。錯誤示範訂單訂單 訂單編號 訂戶姓名 訂戶電話 訂戶地址 訂閱日期 訂閱期數 付款方式 正規化n第三正規化正確示範訂單訂單 訂單編號 訂戶編號 訂閱日期 訂閱期數 付款方式 訂戶訂戶 訂戶編號 訂戶姓名 訂戶地址 訂戶電話 原因：自成一群的欄位，代表他們有自己所代表的意義。原因：自成一群的欄位，代表他們有

19、自己所代表的意義。獨立出來，才可供其它表格參考之。獨立出來，才可供其它表格參考之。正規化n第四正規化(Forth Normal Form,4NF)定義：若表格中出現有可能“一對多”的情況，則要把表格切開。錯誤示範使用者使用者 編號編號 姓名姓名 性別性別 用戶種類用戶種類 地址地址 1 地址地址 2 電話電話 1 電話電話 2 1 魏安富 男 1 xxxx xxxx 2 岳 京 男 1 xxxx xxxx 3 魏笙綿 女 1 xxxx xxxx xxxx xxxx 4 曾桃燕 女 1 xxxx xxxx 5 駱 宏 男 3 xxxx xxxx 6 梅愛姿 女 6 xxxx xxxx 正規化n第

20、四正規化正確示範編號編號 姓名姓名 性別性別 用戶種類用戶種類 1 魏安富 男 1 2 岳 京 男 1 3 魏笙綿 女 1 4 曾桃燕 女 1 5 駱 宏 男 3 6 梅愛姿 女 6 地址編號地址編號 用戶編號用戶編號 地址地址 1 1 Xxxx 2 2 Xxxx 3 3 Xxxx 4 3 Xxxx 5 4 Xxxx 6 5 Xxxx 7 6 xxxx 電話編號電話編號 用戶編號用戶編號 電話電話 1 1 Xxxx 2 2 Xxxx 3 3 Xxxx 4 3 Xxxx 5 4 Xxxx 6 5 Xxxx 7 6 xxxx 原因：這種一對多的欄位，你永遠也不知道這個原因：這種一對多的欄位，你永遠

21、也不知道這個“多多”到底是多少。到底是多少。留多了不好，留少了不夠。乾脆獨立出去，要多要少任君挑選。留多了不好，留少了不夠。乾脆獨立出去，要多要少任君挑選。正規化n第五正規化(Fifth Normal Form,5NF)規定：將欄位再細分成“常用”與“不常用”兩種，將它們割開。示範：員工員工 編號 姓名 性別 生日 薪資 上司 職稱 常用常用不常用不常用員工細部資料員工細部資料 編號 生日 薪資 職稱 員工員工 編號 姓名 性別 上司 原因：此舉是為了使表格越小越好，以利網路傳輸。如果您的系統沒有網路原因：此舉是為了使表格越小越好，以利網路傳輸。如果您的系統沒有網路傳輸的考量傳輸的考量(如：一

22、切系統都在區域網路內執行如：一切系統都在區域網路內執行)，您可以將表格切到，您可以將表格切到4NF即可。即可。正規化n紀子要訣一人住一房，不得外人藏若能成一家，自行走他方。屋內不暇管，各自買一幢莫居三合院，喜住小套房。不要擠不要擠有關係有關係我獨大我獨大不多管不多管小而美小而美1NF2NF3NF4NF5NF正規化n課堂討論請敘述第一正規化到第五正規化的n定義n正規化的原因關聯性n何謂關聯性(Relationships)所謂關聯式資料庫，就是利用“正規化”將表格切開，再用“關聯性”將表格“黏”回原來的樣子。n關聯性的種類一對一(One to One,1-1)一對多(One to Many,1-)

23、多對多(Many to Many,-)關聯性n一對一(One to One,1-1)兩個表格之間的記錄，是一筆對一筆的 員工細部資料員工細部資料 編號 生日 薪資 職稱 員工員工 編號 姓名 性別 上司 每一筆每一筆“員工員工”的記錄，只會對應到一筆的記錄，只會對應到一筆“員工細部資料員工細部資料”。關聯性n一對多(One to Many,1-)兩個表格之間的記錄，是一筆對多筆的。編號編號 姓名姓名 性別性別 用戶種類用戶種類 1 魏安富 男 1 2 岳 京 男 1 3 魏笙綿 女 1 4 曾桃燕 女 1 5 駱 宏 男 3 6 梅愛姿 女 6 電話編號電話編號 用戶編號用戶編號 電話電話 1

24、 1 Xxxx 2 2 Xxxx 3 3 Xxxx 4 3 Xxxx 5 4 Xxxx 6 5 Xxxx 7 6 xxxx 每一筆每一筆“使用者使用者”表格內的資料，都會對應到一或多筆表格內的資料，都會對應到一或多筆“電話電話”的資料。的資料。關聯性n多對多(Many to Many,-)兩個表格之間的記錄，是多筆對多筆的。關聯式資料庫做不出來多對多的關係。如果遇到多對多的關係時，就在其中插入一個新表格，使之成為兩個一對多關係。書籍書籍 書籍編號 書名 出版社 出版日 作者編號 作者作者 作者編號 作者名 作者性別 作者生日 書籍編號?多對多多對多書籍書籍 書籍編號 書名 出版社 出版日 作者

25、編號 作者作者 作者編號 作者名 作者性別 作者生日 書籍編號 書籍書籍-作者作者 書籍作者編號 書籍編號 作者編號 一對多一對多一對多一對多關聯性n經過正規化後的 Employees 資料表EmployeesempIDempNameempSexempBirthdayempSalaryempMemoEmpAddressaddrIDempIDAddressEmpTelTelIDempIDTel關聯性n課堂討論請說明關聯式資料庫如何做多對多的連結更改資料表結構n資料表最終長相EmployeesempIDempNameempSexempBirthdayempSalaryempMemoEmpAddre

26、ssaddrIDempIDAddressEmpTelTelIDempIDTelEmployeesempIDempNameempAddressempTelempSexempBirthdayempSalaryempMemo更改資料表結構n資料表變更程序建造新表格nCREATE TABLE EmpAddressnCREATE TABLE EmpTel將舊資料轉換到新表格刪除舊資料表的兩個欄位更改資料表結構n建造新表格CREATE TABLE EmpAddress(addrID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,empID INT NOT NULL,Add

27、ress VARCHAR(50);CREATE TABLE EmpTel(telID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,empID INT NOT NULL,Tel VARCHAR(20);更改資料表結構n將舊資料轉換到新表格格式nINSERT INTO 資料表名稱資料表名稱(欄位欄位1,欄位欄位2,)SELECT 欄位欄位1,欄位欄位2,FROM 資料表名稱資料表名稱WHERE 條件條件;範例INSERT INTO EmpAddress(empID,Address)SELECT empID,empAddressFROM Employees;INS

28、ERT INTO EmpTel(empID,Tel)SELECT empID,empTelFROM Employees;更改資料表結構n刪除舊資料表的欄位格式nALTER TABLE 資料表名稱ADD COLUMN 欄位定義 FIRST|AFTER 欄位DROP COLUMN 欄位名稱;範例ALTER TABLE EmployeesDROP COLUMN empAddress;ALTER TABLE EmployeesDROP COLUMN empTel;更改資料表結構n隨堂練習建造新表格n請依照講義的指示，造出 EmpAddress 以及 EmpTel 兩個表格出來。轉換資料n請用 INSE

29、RT INTO SELECT 指令，將原來屬於舊表格的資料轉到新表格來。刪除欄位n請用 ALTER TABLE 指令，將兩個不要的 empAddress 以及 empTel 欄位刪除。顯示多表格資料n如何同時顯示多表格的資料利用“連結”(Join)n何謂表格連結兩個表格間，利用“主鍵(Primary Key)”與“外鍵(Foreign Key)”將兩個表格黏在一起的方法。n表格連結的種類INNER JOINOUTER JOINnLEFT OUTER JOINnRIGHT OUTER JOINnFULL OUTER JOIN顯示多表格資料nINNER JOIN顯示多表格資料nLEFT OUTER

30、 JOIN顯示多表格資料nRIGHT OUTER JOIN顯示多表格資料nFULL OUTER JOIN顯示多表格資料n格式SELECT 表格化名.欄位1,表格化名.欄位2FROM 表格名稱1 表格化名1 INNER JOIN|LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN表格名稱2 表格化名2WHERE 表格黏合條件n範例將 Employees,EmpAddress,EmpTel 資料表的內容顯示在畫面上SELECT E.empName,A.Address,T.Tel,E.empSex,E.empBirthday,E.empSalaryFRO

31、M Employees E JOIN EmpAddress A JOIN EmpTel TWHERE E.empID=A.empID AND E.empID=T.empID;顯示多表格資料n隨堂練習請依照前一頁的範例，將 Employees,EmpAddress,EmpTel 三個表格黏合起來後秀出。總整理n資料庫建造資料庫的指令格式為何？刪除資料庫的指令格式為何？n資料表建造一個資料表的指令格式為何？用何種指令可以為已存在的資料表更名？如何新增或刪除資料表的某個欄位？格式為何？如何刪除某個資料表？n資料如何新增一筆資料？如何將某個表格的資料移轉到另一個表格中？如何觀看某個資料表內的資料？如何將多個表格黏合在一起？如何更新某一筆資料？如何刪除一筆資料？n第一至第五正規化的作用為何？n關聯式資料庫有哪三種關聯性？如果出現多對多的關聯如何解決？