1 / 132

挑戰資料庫管理系統 A Challenge to Database Management Systems

挑戰資料庫管理系統 A Challenge to Database Management Systems. 第 8 章 SQL 進階. 大綱. 8.1 前言 8.2 建立檢視表 (View) 8.3 安全控管機制 8.4 T-SQL 程式設計 8.5 預存程序 (Stored Procedure) 8.6 觸發程序 (Trigger) 8.7 資料指標 (Cursor) 8.8 函數 8.9 如何讓查詢的速度更快 8.10 執行 SQL 的環境. 8.1 前言. SQL 建立資料庫、資料表、索引

aquila
Download Presentation

挑戰資料庫管理系統 A Challenge to Database Management Systems

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. 挑戰資料庫管理系統 A Challenge to Database Management Systems 第8章 SQL 進階 挑戰資料庫管理系統

  2. 大綱 • 8.1 前言 • 8.2 建立檢視表(View) • 8.3 安全控管機制 • 8.4 T-SQL程式設計 • 8.5 預存程序(Stored Procedure) • 8.6 觸發程序(Trigger) • 8.7 資料指標(Cursor) • 8.8 函數 • 8.9 如何讓查詢的速度更快 • 8.10 執行SQL的環境 挑戰資料庫管理系統

  3. 8.1 前言 • SQL • 建立資料庫、資料表、索引 • 新增、修改、刪除資料 • 查詢資料 • SQL 進階 • 檢視表 • 安全控管機制 • 預存程序 • 觸發程序 • 函數 挑戰資料庫管理系統

  4. 8.2 建立檢視表(View) 圖8.1 由一個資料表產生檢視表 挑戰資料庫管理系統

  5. 8.2 建立檢視表(View) 圖8.2 由兩個資料表產生檢視表 挑戰資料庫管理系統

  6. 8.2 建立檢視表(View) 【SQL語法】 CREATE VIEW view_name [ ( column [ ,...n ] ) ] ASselect_statement[ WITH CHECK OPTION ] 挑戰資料庫管理系統

  7. 8.2 建立檢視表(View) Emp 資料表 Dept 資料表 挑戰資料庫管理系統

  8. 8.2.1 挑選特定資料行以產生檢視表 • 【例1】 【執行】 USE EmpDB GO CREATE View Emp_v1 ASSELECT Emp_no, Emp_name, SexFROM Emp SELECT * FROM Emp_v1 挑戰資料庫管理系統

  9. 8.2.1 挑選特定資料行以產生檢視表 • 【例2】 SELECT * FROM Emp_v1 WHERE Emp_no LIKE 'A%' AND Sex='男' SELECT Emp_no, Emp_name, Sex FROM Emp WHERE Emp_no LIKE 'A%' AND Sex='男' 挑戰資料庫管理系統

  10. 8.2.1 挑選特定資料行以產生檢視表 • 【例3】 【執行】 sp_helptextEmp_v1 挑戰資料庫管理系統

  11. 8.2.2 挑選特定資料列以產生檢視表 • 【例1】 • 使用WHERE條件來限制使用者所能看到的資料列 CREATE View Emp_v2 AS SELECT Emp_no, Emp_name, Sex FROM Emp WHERE Sex='男' SELECT * FROM Emp_v2 挑戰資料庫管理系統

  12. 8.2.3 聯結多個資料表以產生檢視表 • 【例1】 CREATE View Emp_v3 AS SELECT Emp_no, Emp_name, Sex, Dept FROM Emp A, Dept B WHERE A.dept_code=B.dept_code AND Sex='男' SELECT * FROM Emp_v3 挑戰資料庫管理系統

  13. 8.2.4 檢視表只顯示彙總(Aggregate)資訊 • 【例1】 CREATE View Emp_v4 AS SELECT Dept AS 部門, COUNT(*) AS 總人數, AVG(Salary) AS 平均薪資,MAX(Salary) AS 最高薪, MIN(Salary) AS 最低薪 FROM Emp A, Dept B WHERE A.dept_code=B.dept_code GROUP BY Dept SELECT * FROM Emp_v4 挑戰資料庫管理系統

  14. 8.2.4 檢視表只顯示彙總(Aggregate)資訊 • 【例2】 • 例1可改寫成 CREATE View Emp_v4(部門, 總人數, 平均薪資 最高薪, 最低薪) AS SELECT Dept, COUNT(*), AVG(Salary), MAX(Salary), MIN(Salary) FROM Emp A, Dept B WHERE A.dept_code=B.dept_code GROUP BY Dept 挑戰資料庫管理系統

  15. 8.2.5 檢視表的資料異動 • 【例1】 • 增加一筆資料到Emp_v1檢視表 【執行】 INSERT INTO Emp_v1 VALUES ('Y001','許效舜','男') SELECT * FROM Emp_v1 挑戰資料庫管理系統

  16. 8.2.5 檢視表的資料異動 • 【例1】 【執行】 SELECT * FROM Emp 挑戰資料庫管理系統

  17. 8.2.5 檢視表的資料異動 • 【例2】 • 增加一筆資料到Emp_v2檢視表 【執行】 INSERT INTO Emp_v2 VALUES (‘G001’,‘那英’,‘女’) SELECT * FROM Emp_v2 SELECT * FROM Emp Emp_v2檢視表的內容 Emp 資料表的內容 挑戰資料庫管理系統

  18. 8.2.5 檢視表的資料異動 • 【例3】 CREATE View Emp_v2_check AS SELECT Emp_no, Emp_name, Sex FROM Emp WHERE Sex=‘男’ WITH CHECK OPTION 【執行】 INSERT INTO Emp_v2_check VALUES (‘F001’,‘孫燕姿’,‘女’) 【結果】 嘗試插入或更新已經失敗，因為目標檢視表指定了 WITH CHECK OPTION 或跨越指定了 WITH CHECK OPTION 的檢視表，且動作的一個或多個資料列在 CHECK OPTION 條件約束下並不合格。 挑戰資料庫管理系統

  19. 8.2.5 檢視表的資料異動 • 【例4】 【執行】 INSERT INTO Emp_v3 VALUES ('H001','劉德華','男','企劃部') 【結果】 由於此修改會影響多個基底資料表，檢視表或函數 'Emp_v3' 無法更新。 挑戰資料庫管理系統

  20. 8.2.5 檢視表的資料異動 • 【例5】 • 將Emp_v3檢視表中 Emp_no=’B001’ 之員工(永邦)，由「公關部」調至「客服部」 【執行】 UPDATE Emp_v3 SET Dept=‘客服部’ WHERE Emp_no='B001' 【結果】 無法用唯一索引 'Dept_index2' 在物件 'Dept' 中插入重複的索引鍵資料列。 挑戰資料庫管理系統

  21. 8.2.5 檢視表的資料異動 • 【例6】 • 將Emp_v3檢視表中 Emp_no=’B001’ 之員工(永邦)，由「公關部」調至「行銷部 」 • 表面上看來是成功地完成修改動作，但實際上卻是「將公關部改名為行銷部」而非將 ’B001’ 員工調到「行銷部」工作 【執行】 UPDATE Emp_v3 SET Dept=‘行銷部’ WHERE Emp_no='B001' 挑戰資料庫管理系統

  22. 8.2.5 檢視表的資料異動 • 【例7】 • 不可異動包含彙總欄位的檢視表 【執行】 INSERT INTO emp_v4 VALUES (‘倉儲部’,5,5000, 52000,48000) 【結果】 檢視表或函數 'emp_v4' 無法更新，因為其中包含彙總。 挑戰資料庫管理系統

  23. 8.2.6 檢視表的優缺點與限制 挑戰資料庫管理系統

  24. 8.2.6 檢視表的優缺點與限制 挑戰資料庫管理系統

  25. 8.3 安全控管機制 • GRANT(授權) • REVOKE(撤銷) 挑戰資料庫管理系統

  26. 8.3.1 Grant(授予使用權) • 陳述式權限(Statement Permission) • 物件權限(Object Permission) 【SQL語法】 GRANT { ALL | statement [ ,...n ] } TO security_account [ ,...n ] 【SQL語法】 GRANT { ALL [ PRIVILEGES ] | permission [ ,...n ] } {   | ON { table | view } [ ( column [ ,...n ] ) ]     | ON { stored_procedure | extended_procedure }     | ON { user_defined_function } } TO { PUBLIC | security_account } [ WITH GRANT OPTION ] 挑戰資料庫管理系統

  27. 8.3.1 Grant(授予使用權) • 【例1】 • dbo授權使用者User1和User2具備在EmpDB資料庫裡建立資料表和建立檢視表的權限 【執行】 -- 由dbo 執行 USE EmpDB GO GRANT CREATE TABLE, CREATE VIEW TO User1, User2 挑戰資料庫管理系統

  28. 8.3.1 Grant(授予使用權) • 【例2】 • dbo授權User1查詢Emp資料表的Emp_no、Emp_name和Sex等3個欄位，以及刪除資料列的權限 • 外並允許User1將其所取得的權限再授予他人 【執行】 -- 由dbo 執行 GRANT SELECT(Emp_no, Emp_name, Sex), DELETE ON Emp TO User1 WITH GRANT OPTION 挑戰資料庫管理系統

  29. 8.3.1 Grant(授予使用權) • 【例2】 • User1將DELETE權限授予User3 【執行】 -- 由User1 執行 GRANT DELETE ON Emp TO User3 挑戰資料庫管理系統

  30. 8.3.1 Grant(授予使用權) • 【例3】 • dbo授予User2對於Emp資料表有INSERT、UPDATE、DELETE、SELECT等權限 • 並允許User2再將取得的權限授予他人 【執行】 -- 由dbo 執行 GRANT ALL ON Emp TO User2 WITH GRANT OPTION 挑戰資料庫管理系統

  31. 8.3.2 Revoke(撤銷使用權) • 陳述式權限(Statement Permission) • 物件權限(Object Permission) 【SQL語法】 REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] 【SQL語法】 REVOKE [ GRANT OPTION FOR ]    { ALL [ PRIVILEGES ] | permission [ ,...n ] }    {[ ( column [ ,...n ] ) ] ON { table | view }     | ON { table | view } [ ( column [ ,...n ] ) ]     | ON { stored_procedure | extended_procedure }     | ON { user_defined_function }    } { TO | FROM } security_account [ ,...n ] [ CASCADE ] 挑戰資料庫管理系統

  32. 8.3.2 Revoke(撤銷使用權) • 【例1】 • dbo授權User1和User2在EmpDB資料庫裡建立資料表和建立檢視表的權限 【執行】 -- 由dbo 執行 REVOKE SELECT, DELETE ON Emp FROM User1 CASCADE 挑戰資料庫管理系統

  33. 8.4. T-SQL程式設計 • T-SQL符合SQL-92的語法規則 • 另外增加了變數、程式區塊、流程控制…等第三代程式語言的功能 • T-SQL提供的程式設計語法 • 註解 【T-SQL語法】 /* 註解內容 */ 或 -- 註解內容 挑戰資料庫管理系統

  34. 8.4. T-SQL程式設計 • 變數的宣告和初值設定 • DECLARE • SET • 以@帶頭者為區域變數 • 以@@帶頭者為全域變數 • 程式區塊 【T-SQL語法】 BEGIN { sql_statement | statement_block } END 挑戰資料庫管理系統

  35. 8.4. T-SQL程式設計 • 流程控制 挑戰資料庫管理系統

  36. 8.4.T-SQL程式設計 • 流程控制 挑戰資料庫管理系統

  37. 8.4. T-SQL程式設計 • 列印 【T-SQL語法】 PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr 挑戰資料庫管理系統

  38. 8.4. T-SQL程式設計 • 【例1】 【執行】 USE EmpDB GO DECLARE@salary INT SET @salary=70000 SELECT * FROM Emp WHERE Salary > @salary 挑戰資料庫管理系統

  39. 8.4. T-SQL程式設計 • 【例2】 • 印出員工平均薪資、 印出張惠妹的薪資 • 將張惠妹的薪資與平均薪資做一比較 【執行】 USE EmpDB GO DECLARE @average INT, @salary INT -- 印出員工平均薪資 SELECT @average=AVG(Salary) FROM Emp PRINT‘員工的平均薪資為:’ + CONVERT(CHAR(6), @average) + ‘元’ 挑戰資料庫管理系統

  40. 8.4. T-SQL程式設計 -- 印出張惠妹的薪資 SELECT @salary=Salary FROM Emp WHERE Emp_name=‘張惠妹’ PRINT '張惠妹的薪資為:' + CONVERT(CHAR(6), @salary) + ‘元’ -- 比較張惠妹的薪資與平均薪資 IF @salary > @average PRINT ‘張惠妹的薪資<高於>平均薪資’ ELSE BEGIN IF @salary > @average PRINT ‘張惠妹的薪資<低於>平均薪資’ ELSE PRINT ‘張惠妹的薪資<等於>平均薪資’ END 挑戰資料庫管理系統

  41. 8.4. T-SQL程式設計 • CONVERT(CHAR(6), @average) • 將整數@average轉換成CHAR(6)資料型態 【執行訊息】 員工的平均薪資為:70833 元 張惠妹的薪資為:80000 元 張惠妹的薪資<高於>平均薪資 挑戰資料庫管理系統

  42. 8.4. T-SQL程式設計 • 【例3】 • 用CASE來做代碼轉換 【執行】 USE EmpDB GO SELECT Emp_name, Sex, Salary, CASE Dept_code WHEN 1 THEN ‘企劃部’ WHEN 2 THEN ‘公關部’ WHEN 3 THEN ‘客服部’ WHEN 4 THEN ‘研發部’ ELSE ‘尚未分發’ END FROM Emp WHERE Sex=‘男’ GO 挑戰資料庫管理系統

  43. 8.4. T-SQL程式設計 • 【例4】 【執行】 USE EmpDB GO DECLARE @sqlVARCHAR(256) SET @sql='SELECT * FROM Emp WHERE Salary BETWEEN 70000 AND 80000 ORDER BY Salary' EXEC(@sql) 挑戰資料庫管理系統

  44. 8.5 預存程序(Stored Procedure) • 預存程序是一組用T-SQL語法所撰寫的程式 • 擷取資料庫中的資料 • 處理輸入、輸出 • 處理程式邏輯 • 控制程式流程 • 預存程序很像一般程式語言的副程式 • 可宣告變數 • 可傳遞參數 • 可編譯(Compile) • 可被重複地呼叫、執行 • 可傳回結果值(集) 挑戰資料庫管理系統

  45. 8.5 預存程序(Stored Procedure) • 儲存和執行T-SQL程式 • 將T-SQL程式儲存在用戶(Client)端 • 將T-SQL程式儲存為SQL Server的預存程序 • MS SQL Server的預存程序 • 系統預存程序 • 以sp_或xp_開頭 • 進入SQL Server Enterprise Manager、展開【資料庫】、展開 【master 】 資料庫，點選 【預存程序】 • 使用者預存程序 挑戰資料庫管理系統

  46. 8.5 預存程序(Stored Procedure) 挑戰資料庫管理系統

  47. 8.5 預存程序(Stored Procedure) 挑戰資料庫管理系統

  48. 8.5.1 預存程序的用途和優缺點 • 預存程序的功能 • 可以執行一系列T-SQL程式指令 • 存取資料庫的SQL指令 • 控制程式流程的指令 • 執行預存程序時可以傳遞參數，執行後傳回結果 • 可以從預存程序中呼叫另一個預存程序 • 不能在運算式中呼叫預存程序 • 執行 • EXECUTE 預存程序名稱 • EXEC 預存程序名稱 挑戰資料庫管理系統

  49. 8.5.1 預存程序的用途和優缺點 • 預存程序的優點： • 可將T-SQL程式模組化，供不同使用者重複使用 • 執行速度較一般T-SQL程式快 • 可降低網路流量 • 增加資料庫的安全性 • 預存程序的缺點： • 可攜性較差 挑戰資料庫管理系統

  50. 8.5.2 預存程序的應用實例 【SQL語法】 CREATEPROC[EDURE]procedure_name [{ @parameter data_type }  [ VARYING ] [ = default ] [ OUTPUT ]] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] ASsql_statement [ ...n ] 挑戰資料庫管理系統

More Related