1 / 42

SQL-99: 綱要定義、基本限制與查詢 (2)

SQL-99: 綱要定義、基本限制與查詢 (2). ▊ Outlines. Database Languages SQL 資料型態 Data Definition Language, DDL ( 資料定義語言 ) Data Manipulation Language, DML ( 資料處理語言 ) Data Query Language, DQL ( 資料查詢語言 ) Data Control Language, DCL ( 資料控制語言 ) (see Chapter 8, Chapter 9; 實作講義 Ch. 5~Ch. 7).

doris-foreman
Download Presentation

SQL-99: 綱要定義、基本限制與查詢 (2)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL-99: 綱要定義、基本限制與查詢 (2)

  2. ▊ Outlines • Database Languages • SQL資料型態 • Data Definition Language, DDL (資料定義語言) • Data Manipulation Language, DML (資料處理語言) • Data Query Language, DQL (資料查詢語言) • Data Control Language, DCL (資料控制語言) (see Chapter 8, Chapter 9; 實作講義Ch. 5~Ch. 7)

  3. ▊ Data Query Language, DQL (資料查詢語言) • 建立好資料表及其所有的資料後，可以使用DQL來查詢資料。 • DQL只有一個指令– Select，用以查詢資料庫的相關資料，語法如下： SELECT <attribute list> FROM <table list> WHERE <condition> GROUP BY <grouping attributes> HAVING <grouping condition> ORDER BY <column name> ASC/DESC • SQL查詢語言由上述六個子句所組成，只有Select和From是必要的。 • 執行順序： • FROM→WHERE →GROUP BY →HAVING →SELECT →ORDER BY //指定查詢時，所欲輸出的欄位(屬性) //指定查詢時，所會用到的表格有哪些 //指定查詢的條件 //將SELECT的結果依照某屬性欄位加以分群 //GROUP BY的限制條件，必須配合GROUP BY使用 //依照某欄位值作遞增(ASE)或遞減(DESC)排列 (預設為ASC)

  4. 使用說明： • SELECT *表示選擇FROM中關聯表格的所有屬性 • 查詢中若沒有WHERE，表示沒有限制條件，即: 將FROM所指定之表格的所有資料，根據SELECT所要求的欄位全部列出。 • 若有多個表格中的欄位名稱重覆，可改寫成 “table_name . attribute_name” 加以區分 • 如：假設A表格與B表格中，皆有一個名為ID的欄位，且在資料查詢時此兩表格皆同時用上，為了怕混肴，則以 A.ID 與 B.ID 來區分。 • 在做資料查詢時，可將使用到的表格名稱或是欄位名稱另取別名： • “表格名稱 AS 別名” 或是 “表格名稱 別名” • Ex: Project_supp_Component as PSC 或 Project_supp_Component PSC • 亦可對欄位取別名

  5. 範例題組1 (一般查詢: 無條件、有條件) • 一般查詢可分成無條件查詢與有條件查詢 • 依照slide 9的四個表格，用SQL回答下列問題： • 列出個別專案的資料。(無條件) Sol: • 列出所有重量在20以上，且不為黑色的零件名稱、顏色、重量。(有條件) Sol:

  6. SQL語法的運算子 • SQL語法中可能會用到的運算子：

  7. 範例題組2 (排序查詢、分組查詢、聚合函數) • 在從事資料查詢時，除了可以在where子句加上所需要之限制條件外，尚可將查詢的結果做一些加工，如： • 根據某欄位，將查詢結果做排序 • 根據某欄位，將查詢結果做分組 • 根據某欄位，將查詢結果利用一些聚合函數做運算

  8. 依照slide 9的四個表格，用SQL回答下列問題： • 依照數量由小到大列出供應商S1所參與之專案名稱、零件名稱，以及數量。 (排序) Sol:

  9. 由專案供應零件中，列出零件代號並統計各零件之總數量。(分組 + 聚合) Sol: • 由專案供應零件中，總數超過1000之零件的總數量並列出其代號。(聚合) Sol:

  10. SQL語法的函數 • 聚合函數： • COUNT(attribute_name): 計算屬性值個數 • SUM(attribute_name): 計算屬性中數值的總合 • AVG(attribute_name): 計算屬性中數值的平均 • MAX(attribute_name): 找出屬性中數值的最大值 • MIN(attribute_name): 找出屬性中數值的最小值 (其它類型的SQL函數請參考網路講義)

  11. 聚合函數中COUNT的用法： • COUNT(*): 計算有幾筆記錄 • COUNT(attribute_name): 此屬性有幾筆非空值 • COUNT(DISTINCT attribute_name): 此屬性有幾筆不同的非空值 • 例： 表格Employee共有1000筆記錄，其中有900人分別屬於10個不同的部門，100人無部門。 • SELECT COUNT(*) FROM Employee;  共 1000筆 • SELECT COUNT(Dept) FROM Employee;  共 900筆 • SELECT COUNT(Eno) FROM Employee;  共 1000筆 • SELECT COUNT(DISTINCT Dept) FROM Employee;  共 10筆

  12. 依照slide 9的四個表格，用SQL回答下列問題： • 列出所有供應商 “大勝” 有供應的零件中，重量最重之重量為何。 (聚合) Sol: • 列出位於高雄的供應商數目。 (聚合) Sol:

  13. 範例題組3 (巢狀查詢，或稱子查詢) • 何謂子查詢(Subquery)？ • 在一個 SQL 語法的條件子句中，放入另一個 SQL 語法 • 當我們在WHERE子句或HAVING等條件子句中，插入另一個 SQL 語句時，我們就有一個子查詢 (Subquery) 的架構。 • 語法： SELECT … FROM <表格> WHERE <欄位名或欄位運算式> <比較運算子> (SELECT … FROM <表格名> WHERE <條件>);

  14. 根據子查詢語句所回傳之記錄值狀況，區分成：根據子查詢語句所回傳之記錄值狀況，區分成： • 單一記錄值子查詢 • 此類子查詢只傳回單一記錄值(如：李四的學生基本資料) • 所有的邏輯比較運算子 (如：>，=，<，…等) 皆可使用 • 多記錄值子查詢 • 此類子查詢傳回的是一組記錄值(如：張三、李四、王五的學生基本資料) • 須使用多值比較運算子 SELECT … FROM <表格> WHERE <欄位名或欄位運算式> <比較運算子> (SELECT … FROM <表格名> WHERE <條件>);

  15. 單一記錄值子查詢 • 此類子查詢只傳回單一記錄值，因此所有邏輯運算子(如 ＞，=，＜ 等等) 都可以用。

  16. 依照slide 9的四個表格，用SQL回答下列問題： • 列出供應商 “大勝” 所供應之零件總數量 Sol：

  17. 多記錄值子查詢 • 此類子查詢傳回不是單一值，而是某特定欄位的所有資料。這種查詢必須用多值比較運算子與主查詢相連繫。 • [Not] IN: • 比較IN之前的數值，是否和IN後面集合中任一數值相同，及判別是否在此集合中。 • NOT IN則為IN的反義 • [Not] ALL, [Not] ANY：用來做數值的比較 (需搭配邏輯運算子) • ALL是將某值與集合中的所有內容值做比較 • ANY是指與集合中任何值做比較 • [Not] EXISTS: • 若子查詢有結果存在時，則列出父查詢的查詢結果 (無關子查詢) • 找出存在於子查詢結果表格中的資料 (相關子查詢) • 所有變數中只要有一個為true，EXISTS即成立 • NOT EXISTS為EXISTS的反義詞，所有變數皆必須為FALSE，NOT EXISTS才成立

  18. 注意 單一記錄子查詢所使用的邏輯運算子，和多記錄子查詢所使用的多值比較運算子IN, ANY, ALL (EXISTS除外)，需在運算子前、後分別指定父查詢的某特定欄位與子查詢傳回結果的某特定欄位，且子查詢傳回欄位的個數及資料型態必須要與父查詢匹配，才能使該運算子正常工作。

  19. 依照slide 9的四個表格，用SQL回答下列問題： • 列出所有有參與J2專案的供應商名稱。 Sol:

  20. 列出所有沒有參與J2專案的供應商名稱。 Sol:

  21. 列出所有有參與J2專案、且位於台南的供應商名稱。列出所有有參與J2專案、且位於台南的供應商名稱。 Sol:

  22. 列出所有有供應黑色零件的供應商代號與名稱。列出所有有供應黑色零件的供應商代號與名稱。 Sol:

  23. 列出所有有供應黑色零件的供應商代號與名稱，且重覆資料只列出一筆。列出所有有供應黑色零件的供應商代號與名稱，且重覆資料只列出一筆。 Sol: • DISTINCT：表示相同的資料只列出一筆。

  24. 列出所有沒有供應黑色零件的供應商代號與名稱列出所有沒有供應黑色零件的供應商代號與名稱 Sol: (第二個查詢結果重覆資料只出現一次)

  25. 當Where子句後的子查詢有結果存在 (為True)時，列出父查詢工作之結果 (即：所有供應商的名稱與編號) Sol: 此查詢工作同義於下列查詢：

  26. 無關子查詢 vs. 相關子查詢 • 相關子查詢是指WHERE 條件中的子查詢，有引用主查詢的查詢資料。反之我們稱之無關子查詢。 • 前面所有的子查詢範例皆為無關子查詢 • 依照slide 9的四個表格，用SQL回答下列問題： • 列出有支援專案 J3所需零件 的所有供應商代號與名稱。 Sol: • 無關子查詢中，其父查詢與子查詢可視為兩個獨立的查詢工作，這兩個查詢工作之結果透過比較運算子加以結合。

  27. 列出所有沒有銷售黑色零件的供應商代號與名稱。列出所有沒有銷售黑色零件的供應商代號與名稱。 Sol 1: Sol 2:

  28. 列出所有供應零件P1的供應商名稱。 Sol 1: Sol 2:

  29. 上一查詢工作的Sol 2，若沒有加上 “DISTINCT” 會產生什麼結果？

  30. 範例題組4(其它比較運算子) • LIKE：利用萬用字元(%及_)做相似字串的比對 • 百分符號 % 表示0~多個任意字元 • 底線符號 _ 表示單一個任意字元 • 例：%大_ _  在 “大” 字前可有任意個字元； 在 “大” 字後為恰有兩個字元

  31. 找出所在城市為未知的供應商名稱 (假設有一個供應商 “大聯合工坊”，其代號為 “S5”，其所在城市未知) Sol:

  32. 找出名稱的第一個字為 ‘大’ 的供應商之名稱與代號 Sol:

  33. ▊ Data Definition Language, DDL (資料定義語言) - - (2) • DDL主要有CREATE, DROP, ALTER三個指令，並可針對以下三個物件進行操作： • 資料庫 (database) • 表格 (Table) • 觀點 (View)

  34. 父母 男/女朋友 老闆 姓名 修習課程 成績 工作經驗 姓名 住址 電話 興趣 男女朋友 個性 … … … … 姓名 修習課程 成績 男女朋友 … … … … … … 虛擬表格 … … … 學生資料庫 姓名 住址 電話 修習課程 成績 興趣 男女朋友 工作經驗 個性 … 實際表格 … … … … … … … … … … 何謂 View (觀點、視觀)？

  35. 建立、刪除觀點 (View) • 觀點 (View): • 不以實體表格的形式存在，可視為一個虛擬表格 • 其內容是以SELECT指令的執行結果構成，所以其定義方式正是以SELECT為基礎 • 在MySQL中， • 可使用SQL標準語法 (即：CREATE VIEW) 來建構 view。 • 利用show tables，可以看到所建立出來的view和其它的表格是放在一起的，就像是一個真正的表格一樣，只是它是屬於虛擬表格。 • 正是因為它是虛擬表格，所以不能使用DROP TABLE來刪除它!! 可使用SQL標準語法的DROP VIEW來刪除一個view。

  36. CREATE VIEW: 建立一個新的觀點 • 指令： • CREATE VIEW <view name> AS SELECT… FROM… WHERE…

  37. 例: • 為名稱的第一個字為 ‘大’ 的所有供應商之名稱與代號建立View，其View的名稱為 “Supplier_Da”。 • 同時，請對此View做資料查詢。

  38. DROP VIEW: 刪除一個觀點 DROP VIEW <view name> • 例: DROP VIEW Supplier_Da; • View的優點： • 可提供邏輯資料獨立性 (Logical Data Independence) • 隱藏不需要或具私密性的資料 • 同一關聯可建立多種不同的觀點，讓使用者以不同的角度看同一份資料。 • View的缺點： • 觀點的更新(Update)有諸多限制，無法與關聯提供相同的功能。

  39. ▊ Data Control Language, DCL (資料控制語言) • 此語言主要從事資料庫的權限控管，包含Grant, Revoke, Alter Password…等指令。 • Grant：增加資料庫的權限內容。 GRANT <authority> ON <object> TO <users> • 例： • GRANT Select ON EMPLOYEE(FName,LName) TO PUBLIC; • GRANT Delete ON EMPLOYEE TO Jacy; • Revoke: 取消某使用者之權限。 REVOKE <authority> ON <object> FROM <users> • 例： • REVOKE Delete ON EMPLOYEE FROM Jacy;

  40. 在MySQL增加資料庫使用者 • 在mysql中我們使用grant敘述，來設定新使用者並給予其相應之權限。 grant privileges on what to user identified by “密碼” • 說明： • Privleges: 設定權限等級給新使用者。可分成 “使用者層級權限” 與 “管理者層級權限” • What: 設定新使用者所能使用到的資料庫名稱，及相關之資料表格 • *.*: 第一個 * 可指定某一個資料庫名稱，第二個 * 可指定該資料庫之下的某一個資料表名稱。若都是用 *，則表示可使用所有資料庫的所有資料表。 • User:設定新的使用者，包括使用者名稱與主機名稱。如：jacy@localhost • @符號之前是新使用者名稱，@之後是所在的主機 • 密碼: 指定使用者所用的密碼。如果新增的使用者沒有指定(identified)密碼，這代表該使用者沒有密碼。

  41. 使用者層級權限 管理者層級權限

  42. 在MySQL取消使用者權限與移除使用者 • 要取消某個使用者的權限，可用revoke敘述。 revoke priviledges on what from user • 例：使用revoke來取消lulu在資料庫主機localhost的所有MySQL存取權限。 revoke all on *.* from lulu@localhost • revoke敘述只移除權限而非移除使用者，該使用者的項目仍存於user資料表中，所以使用者依舊可以連到伺服器上。 • 要完整的移除使用者，必須使用delete指令明確的由user資料表上，將使用者紀錄刪除。 • 在MySQL資料庫系統中，在上述delete指令移除使用者後，需使用flush privileges來更新MySQL資料庫的權限內容。

More Related