SQL-99： 纲要定义、基本限制与查询 (1)

國立聯合大學資訊管理學系陳士杰老師SQL-99:綱要定義、基本限制與查詢(1)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)▊OutlinesDatabaseLanguagesSQL資料型態DataQueryLanguage,DQL(資料查詢語言)DataDefinitionLanguage,DDL(資料定義語言)DataManipulationLanguage,DML(資料處理語言)DataControlLanguage,DCL(資料控制語言)(seeChapter8,Chapter9;實作講義Ch.5~Ch.7)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)▊DatabaseLanguagesSQL(StructuredQueryLanguage,結構化查詢語言)是一種標準的關連式資料庫語言，由IBM於1970年代所研發出來的。SQL為用來與關連式資料庫系統對話而使用的語言幾乎目前所有的資料庫管理系統都支援SQL目前版本為1999年所提出的SQL/99或稱SQL/3，對物件導向DB與分散式DB有提供支援，並加入了程式設計的功能預存程式(storedprocedure)(早期版本：SQL/87或稱SQL/1,SQL/92或稱SQL/2)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)SQL指令的種類：資料定義語言(DataDefinitionLanguage,DDL)用來宣告、建立資料庫物件(即：建立(Create),刪除(Drop),更改(Alter))即：針對Table或View的Schema資料處理語言(DataManipulationLanguage,DML)用來操作資料庫中的資料(即：插入(Insert)，更新(Update)，刪除(Delete))針對Table內的Data資料查詢語言(DataQueryLanguage,DQL)用來查詢資料庫中的資料，主要指令是Select資料控制語言(DataControlLanguage,DCL)用來從事資料庫的權限控管，如：GRANT,REVOKE,ALTERPASSWORD資料管理指令(DataAdministrationCommands)用來從事資料庫的稽核與分析交易控制指令(TransactionalControlCommands)用來管理資料庫的交易動作國立聯合大學資訊管理學系資料庫系統課程(陳士杰)各家的資料庫系統對於絕大多數的國際標準SQL語法(ANSISQL)皆有支援。然而，在各資料庫系統中還是留有一些專屬的功能。因此，了解各家資料庫系統SQL語法與ANSISQL之差異點至為重要。國立聯合大學資訊管理學系資料庫系統課程(陳士杰)▊SQL資料型態ANSISQL的字串(CharacterStrings)CHAR(n)：固定長度字元串(n為字元個數)VARCHAR(n)：變動長度字元串BIT(n)：固定長度位元串(n為位元個數)BITVARING(n)：變動長度位元串MySQL的字串型態：國立聯合大學資訊管理學系資料庫系統課程(陳士杰)何謂列舉型態與集合型態？列舉型態ENUM：(多選一)這種型態的欄位，其存放的值只能是列舉清單中的其中一項。列舉清單中最多可有65535個項目，也可以有NULL值。範例：Field1ENUM('Am','Pm')DEFAULT‘Am’Field2ENUM(‘經理’,‘工程師’,‘技術員’)DEFAULT‘工程師’集合型態SET：(多選多)SET型態欄位可以存放清單中的多個項目。它適合應用於網頁表單中，選擇多個項目儲存的場合。範例：Field1SET(‘工程師’,‘技術員’,‘專案經理’)。若Field1這個欄位表示“職位”，且公司中有一位工程師同時身兼某專案的專案經理，則在資料庫中插入此工程師之職位時之指令為：INSERTINTOField1VALUES(‘工程師’,‘專案經理’)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)ANSISQL數值串(NumericStrings)INT,INTEGER：整數(4bytes;0~0-4294967295)DEC(m,n),DECIMAL(m,n),NUMERIC(m,n)：格式化數值(m：總位數或精確度，n：小數位數)SMALLINT：短整數(2bytes;0~65535)FLOAT：浮點數(4bytes)REAL：單精度實數(4bytes)DOUBLEPRECISION：雙精度實數(8bytes)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)MySQL的數值串型態：國立聯合大學資訊管理學系資料庫系統課程(陳士杰)ANSISQL日期/時間(Date/Time)資料型態DATE：一般格式為YYYY-MM-DDTIME：一般格式為HH:MM:SSTIMESTAMP：時間戳記，由DATE+TIME+六位以上小數秒數用以記錄交易進入系統的時間順序INTERVAL：時間區間MySQL的日期/時間型態：國立聯合大學資訊管理學系資料庫系統課程(陳士杰)供應商(Supplier)零件(Component)專案(Project)專案供應零件(Project_supp_Component)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)▊DataDefinitionLanguage,DDL(資料定義語言)--(1)DDL主要有CREATE,DROP,ALTER三個指令，並可針對以下三個物件進行操作：資料庫(database)表格(Table)觀點(View)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)建立、刪除資料庫在進行Create、Drop資料庫的指令操作時，對“資料庫”這個關鍵字有時是用“Schema”來表示。CREATESCHEMA(DATABASE):建立一個新的資料庫(資料庫綱要)CREATESCHEMA資料庫名;或CREATEDATABASE資料庫名;例：CREATESCHEMA/DATABASEJacy_Database;DROPSCHEMA(DATABASE):刪除一個資料庫(資料庫綱要)DROPSCHEMA資料庫名;或DROPDATABASE資料庫名;例：DROPSCHEMA/DATABASEJacy_Database;國立聯合大學資訊管理學系資料庫系統課程(陳士杰)在MySQL中，建立一個新的資料庫(資料庫綱目)CREATESCHEMA資料庫名CREATEDATABASE資料庫名刪除一個資料庫(資料庫綱目)DROPSCHEMA資料庫名[CASCADE/RESTRICT];DROPDATABASE資料庫名[CASCADE/RESTRICT];資料庫欄位及名稱有分大小寫，SQL指令並無區分。國立聯合大學資訊管理學系資料庫系統課程(陳士杰)建立、刪除、更改表格CREATETABLE:建立一個新的關聯(表格)CREATETABLEtablename(欄位名1datatype[Null/Notnull][DEFAULT預設值],欄位名2datatype[Null/Notnull][DEFAULT預設值],⋮欄位名ndatatype[Null/Notnull][DEFAULT預設值],PRIMARYKEY(欄位名),UNIQUE(欄位名),FOREIGNKEY(欄位名)REFERENCES表格名欄位名[ONDELETE…/ONUPDATE…]);指定表格中的欄位對具有特定用途之欄位加以設定括號內最後一行指令不需要逗號!!國立聯合大學資訊管理學系資料庫系統課程(陳士杰)CREATETABLEDepartment(DnameCHAR(10)NOTNULL,DnoINTNOTNULL,DaddCHAR(20),PRIMARYKEY(Dno));CREATETABLEProject(PnameCHAR(10)NOTNULL,PnoINTNOTNULL,PRIMARYKEY(Pno));國立聯合大學資訊管理學系資料庫系統課程(陳士杰)CREATETABLEEmployee_01(SsnCHAR(10)NOTNULL,Emp_idCHAR(10)NOTNULL,AddressVARCHAR(50),Dept_idINT,Proj_idINT,SalaryNUMERIC(8,1)NOTNULLDEFAULT18000,PrimaryKey(Ssn));國立聯合大學資訊管理學系資料庫系統課程(陳士杰)CREATETABLEEmployee_02(SsnCHAR(10)NOTNULL,Emp_idCHAR(10)NOTNULL,AddressVARCHAR(50),Dept_idINT,Proj_idINT,SalaryNUMERIC(8,1)NOTNULLDEFAULT18000,PRIMARYKEY(Ssn),UNIQUE(Emp_id),FOREIGNKEY(Proj_id)REFERENCESProject(Pno),FOREIGNKEY(Dept_id)REFERENCESDepartment(Dno)ONDeleteCASCADE);國立聯合大學資訊管理學系資料庫系統課程(陳士杰)註：設定外來鍵時，關於ONDELETE與ONUPDATE的處理動作有以下幾種：NOACTION：發生違反完整性限制的操作時，外來鍵內的資料不會有任何動作RESTRICT：發生違反完整性限制的操作時，DBMS不允許該操作執行CASCADE：發生違反參考完整性限制的操作時，外來鍵內的資料連帶更新或刪除SETNULL：發生違反參考完整性限制的操作時，外來鍵內的資料設為空值SETDEFAULT：發生違反完整性限制的操作時，外來鍵內的資料設為預設值國立聯合大學資訊管理學系資料庫系統課程(陳士杰)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)國立聯合大學資訊管理學系資料庫系統課程(陳士杰)關於MySQL資料表格有兩種常用的類型：MyISAM為MySQL預設的表格類型(當ENGINE那一行指令未填寫的話)成熟、穩定、容易管理。若無特殊需求，應以此類型為主。InnoDB支援交易(Transaction)機制、外來鍵(ForeignKey)、當機復原(若檔案系統未遭受損壞時使用)MyISAMv.s.InnoDB若想追求使用空間與執行效率，建議採用MyISAM若著重交易工作、安全性考量或是可能有多人同時修改資料的情況，則建議採用InnoDB國立聯合大學資訊管理學系資料庫系統課程(陳士杰)在MySQL中，建立一個新的表格：CREATETABLEtablename(attributename1datatype[(not)null][defaultvalue],attributename2datatype[(not)null][defaultvalue],⋮attributenamendatatype[(not)null][defaultvalue],PRIMARYKEY(attributename),UNIQUE(attributename),FOREIGNKEY(attributename)REFERENCEStablename(attributename),FOREIGNKEY(attributename)REFERENCEStablename(attributename)ONDELETE…/ONUPDATE…,)ENGINE=MyISAM/InnoDB;(此行若不打，則預設為MyISAM格式)刪除、更改一個表格可採用後面即將介紹的SQL標準語法。國立聯合大學資訊管