課程名稱：資料庫系統 授課老師：李春雄 博士

Presentation on theme: "課程名稱：資料庫系統 授課老師：李春雄 博士"— Presentation transcript:

1 課程名稱：資料庫系統 授課老師：李春雄 博士
第 六 章 結構化查詢語言SQL 課程名稱：資料庫系統 授課老師：李春雄 博士 各位同學大家好，我是李春雄老師，本學期所開設的課程名稱為「資料結構」， 今天所要為各位介紹的是第一章「資料結構導論」

2 本章學習目標 1.讓讀者瞭解結構化查詢語言SQL所提供的三種 語言(DDL, DML, DCL)。 2.讓讀者瞭解SQL語言的基本查詢。
開始： 本章學習目標 有二項：

3 本章內容 6-1 SQL語言的概念 6-2 Access查詢物件的SQL檢視 6-3 SQL的DDL指令介紹 6-4 SQL的DML指令介紹
6-5 SQL的DCL指令介紹 6-6 SQL的基本語法<完整例子在第七章> 1-1 認識資料與資訊的關係: 其中，「資料」轉換成「資訊」必須要經過一連串處理過程，而這一連串的處理過程就是透過「程式」來處理。 1-2 何謂資料結構？ 「資料結構」(Data Structures)主要是探討如何將資料更有組織地存放到電腦記憶體中，以提昇程式之執行效率的一 門學問。 1-3 何謂演算法?演算法就是「解決問題的方法」 1-4 程式設計概念: 步驟1. 分析所要解決的問題 步驟2. 設計解題的步驟 步驟3. 編寫程式 步驟4. 上機測試、偵測錯誤 步驟5. 編寫程 式說明書 1-5 結構化程式設計 利用「由上而下」的技巧，將程式分解成許多個獨立功能的模組。並且每一個模組都是由三種結構所組成。分別為循序結構、選擇結構及重複結構。 1-6 演算法的效率評估 指用來計算某些演算法所撰寫的程式，在經過編譯之後，實際執行所需要的時間。

4 6-1 SQL語言的概念 【定義】 SQL（Structured Query Language；結構化查詢語言)
它是一種與「資料庫」溝通的共通語言，並且它是為「資料庫處理」 而設計的第四代「非程序性」查詢語言。 【唸法】一般而言，它有兩種不同的唸法 1.三個字母獨立唸出來 S-Q-L 2.唸成 sequel ( 西擴) 【制定標準機構】 目前SQL語言已經被美國標準局(ANSI)與國際標準組織(ISO)制定為 SQL標準，因此，目前各家資料庫廠商都必須要符合此標準。 【目前使用的標準】ANSI SQL92 (1992年制定的版本)。

5 SQL語言提供三種語言 1.第一種為資料定義語言(Data Definition Language; DDL)
用來「定義」資料庫的結構、欄位型態及長度。 2.第二種為資料操作語言(Data Manipulation Language; DML) 用來「操作」資料庫的新增資料、修改資料、刪除資料、查詢資料 等功能。 3.第三種為資料控制語言(Data Control Language; DCL) 用來「控制」使用者對「資料庫內容」的存取權利。 因此，SQL語言透過DDL,DML及DCL來建立各種複雜的表格關聯，成為一個查詢資料庫的標準語言。

6 Relational Model(關聯式模式)
SQL語言與關聯式模式的關係　 在第五章節中，我們都已瞭解「關聯式模式」可以分為兩大類，分別為「關聯式代數」與「關聯式計算」，而本章所要介紹「SQL語言」則是關聯式代數(Relation algebra)與關聯式計算(Relation calculus)的綜合體。接下來，我們來探討「SQL語言」與「關聯式模式」的關係為何呢？如下表所示。 表6-1 SQL與Relational Model之比較表 SQL語言 Relational Model(關聯式模式) 資料表(Table) 關聯(Relation) 記錄(Record)或列(Row) 值組(Tuple) 欄位(Filed)或行(Column) 屬性(Attribute) 不一定要有主鍵 一定要有主鍵 屬性(欄位)有順序性 屬性(欄位)沒有順序性 有重覆的值組 沒有重覆的值組

7 6-1.2. SQL提供三種語言 一般而言，用來處理資料庫的語言稱為資料庫語言(SQL)。 資料庫語言大致上具備了三項功能：
1.資料「定義」語言(Data Definition Language; DDL) 2.資料「操作」語言(Data Manipulation Language; DML) 3.資料「控制」語言(Data Control Language; DCL) 以上三種語言在整個「資料庫設計」中所扮演的角色如下圖 所示： 建立資料表結構 維護資料表結構 異動與查詢 管理使用者權限 設定使用者權限

8 6-2 Access查詢物件的SQL檢視 一般而言，在利用Access來進行查詢功能時，會有兩種不同的模式：
第一種模式：使用「查詢物件」或「查詢精靈」 優點：不須撰寫難困的SQL指令 缺點：無法有效的培養撰寫SQL語言的能力 適用對象：高職生或一般的使用者 第二種模式：使用「 SQL檢視」《本書中，以此種為主》 優點：1.可能培養撰寫SQL語言的能力 2.可以直接套用到程式語言中 3.較為有系統的學習方式 4.彈性較大，並且語法有理論基楚 缺點：須要花較長時間學習 適用對象：大專生或資訊相關科系

9 【作法】 　 步驟一： 首先，啟動已經「附錄一 Access的基本操作」所建立完成的「ch6\素材檔\ch6-2.accdb」資料庫之後，再按「建立／查詢設計」，此時，畫面上會出現「顯示資料表」的對話方塊，您不需要選取任何的資料表，直接按「關閉」鈕，即可進入「設計檢視」視窗。  

10 步驟二：在功能區中按【檢視／SQL檢視】，即可切換到【SQL檢視】
編輯視窗。如下圖所示：  

11 步驟三：此時，你可以在【SQL檢視】編輯視窗中，撰寫SQL指令，
並且按「執行」鈕，如下圖所示： 按「執行」鈕 撰寫SQL指令

12 步驟四：顯示執行結果

13 步驟五：按「儲存」鈕，來儲存撰寫SQL指令， 此時請輸入「學生基本資料查詢」，如下圖所示：。
輸入查詢名稱 儲存 按確定

14 步驟六：查詢目前已經建立的「查詢」  展示檔案：ch6\完成檔\ch6-2.accdb

15 6-3 SQL的DDL語言　 【定義】 資料定義語言(Data Definition Language; DDL)，利用DDL，使用者可以定義資料表(關聯綱目；基底資料表)和設定完整性限制。並且DDL允許資料庫使用者建立、更改或刪除(資料表(Table)、索引(Index)與檢視(View))資料庫物件。主要指令有三：CREATE、ALTER 與 DROP。 如表6-3所示。 表6-3 DDL語言提供的三種指令表 Table Index View (1)Create Table (2)Alter Table (3)Drop Table (1)Create Index (2)Alter Index (3)Drop Index (1)Create View (2)Alter View (3)Drop View

16 6-3.1 CREATE TABLE(建立資料表) 【定義】 Create Table命令是用來讓使用者定義一個新的關聯(資料表)，
並設定關聯(表格)的名稱、屬性及限制條件。 【建立新資料表的步驟】 (1)決定資料表名稱與相關欄位 (2)決定欄位的資料型態 (3)決定欄位的限制（指定值域） (4)決定那些欄位可以NULL(空值)與不可NULL的欄位 (5)找出必須具有唯一值的欄位(主鍵) (6)找出主鍵-外來鍵配對(兩個表格) (7)決定預設值(欄位值的初值設定)

17 【格式】 說明： (1)PRIMARY KEY 用來定義某一欄位為主鍵，不可為空值
(2)UNIQUE 用來定義某一欄位具有唯一的索引值，可以有空值 (3)NULL/NOT NULL 可以為空值/不可為空值 (4)FOREIGN KEY 用來定義某一欄位為外部鍵 (5)CHECK 用來額外的檢查條件

18 【舉例】 請利用Create Table來建立「學生選課系統」的關聯式資料庫，其相關的資料表有三個，如下所示：
【舉例】　 請利用Create Table來建立「學生選課系統」的關聯式資料庫，其相關的資料表有三個，如下所示： 【分析1】辨別「父關聯表」與「子關聯表」 在利用Create Table來建立資料表時，必須要先了解那些資料表是屬於父關聯表(一對多，一的那方；亦即箭頭被指的方向)與子關聯表(一對多，多的那方)，例如：上表中的「學生表」與「課程表」 【分析2】先建立「父關聯表」之後，再建立「子關聯表」 例如：上表中的「選課表」

19 1.先建立「父關聯表」 主鍵 主鍵 建立「學生表」 CREATE TABLE 學生表 (學號 CHAR(8) NOT NULL,
1.先建立「父關聯表」 　 建立「學生表」 CREATE TABLE 學生表 (學號 CHAR(8) NOT NULL, 姓名 CHAR(4), 電話 CHAR(12), 地址 CHAR(20), PRIMARY KEY(學號), UNIQUE(電話)); 主鍵 建立「課程表」 CREATE TABLE 課程表 (課號 CHAR(5) NOT NULL, 課名 CHAR(20) NULL, 學分數 INT NULL, 必選修 CHAR(2) NULL, PRIMARY KEY(課號) ); 主鍵

20 2.再建立「子關聯表」 複合主鍵 建立「選課表」 CREATE TABLE 選課表 (學號 CHAR(8) NOT NULL,
2.再建立「子關聯表」 　 建立「選課表」 CREATE TABLE 選課表 (學號 CHAR(8) NOT NULL, 課號 CHAR(5) NOT NULL, 成績 INT, PRIMARY KEY(學號,課號) ); 複合主鍵

21 【上機練習】 請利用Create Table來建立以下四個資料表。 學生表(學號,姓名,電話,地址) 建立順序：1
學生表(學號,姓名,電話,地址) 建立順序：1 選課表(學號,課號,成績,選課日期) 建立順序：3 課程表(課號,課名,學分數,必選修,老師編號) 建立順序：2 老師表(老師編號,老師姓名,研究領域) 建立順序：1

22 【Access上機實作1】　 請利用VB介面來撰寫SQL指令以建立一個新的資料表「學生表」，並且建立四個欄位，分別為「學號」、「姓名」、「電話」及「地址」。 【解答】 步驟一：請您先自行撰寫以下的SQL指令 利用Create指令建立「學生表」 CREATE TABLE 學生表 ( 學號 CHAR(8), 姓名 CHAR(4), 電話 CHAR(12), 地址 CHAR(50) )

23 步驟二：撰寫一段VB程式碼 程式碼：ch6-3.sln Imports System.Data
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 Imports System.Data Imports System.Data.OleDb Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim dbpath As String = “ch6-3.accdb” ‘宣告資料庫所在的路徑變數 Dim Source As String '宣告連線的字串 Source = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & dbpath Dim conn As OleDbConnection '宣告連線的物件 conn = New OleDbConnection(Source) '連線 conn.Open() '開啟資料庫 Dim CreateCmd As String CreateCmd = TextBox1.Text Dim Cmd As OleDbCommand = New OleDbCommand(CreateCmd, conn) Cmd.ExecuteNonQuery() conn.Close() MsgBox("執行「SQL指令」成功！") End Sub End Class 註：開啟附書光碟中「 01_附書光碟\01_Access 2010(範例資料庫)\ch6\ch6-3\ch6-3.sln」

24 步驟三：開啟VB程式，檔案路徑在 ch6\ch6-3\ ch6-3.sln
1.撰寫SQL指令 2

25 步驟四：執行結果 開啟Access資料庫中的「學生表」
步驟四：執行結果　 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb

26 6-3.2 ALTER TABLE(修改資料表) 【定義】 ALTER TABLE命令是用來對已存在的資料表增加欄位、修改欄位、
刪除欄位，並且增加定義、修改定義或刪除定義等。 【格式】 【符號說明】 { | }代表在大括號內的項目是必要項，但可以擇一。 [ ] 代表在中括號內的項目是非必要項，依實際情況來選擇。

27 【分析1】「新增」欄位定義　

28 【分析2】「修改」欄位定義 【分析3】「刪除」欄位定義 原來的學生資料表中的「地址」之資料型態(大小2050)。
ALTER TABLE 學生資料表 ALTER 地址 CHAR(50); 【分析3】「刪除」欄位定義 將學生資料表中的「地址」欄位刪除。 ALTER TABLE 學生資料表 DROP 地址;

29 【Access上機實作2】 承上機實作1，再將「學號」欄位設定為主鍵(Primary Key)。 【解答】 步驟三： 執行結果
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 利用Alter指令設定「學號」為主鍵 Alter Table 學生表 Add primary key(學號) 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb 步驟二：執行SQL指令

30 【Access上機實作3】 承上機實作2，再增加一個「系碼」欄位。 【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 利用Alter指令增加「系碼」欄位 Alter Table 學生表 Add 系碼 char(4) 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb 步驟二：執行SQL指令

31 【Access上機實作4】 承上機實作3，將「地址」之資料型態(大小50100)。 【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 利用Alter指令修改資料型態 Alter Table 學生表 Alter 地址 char(100) 步驟二：執行SQL指令 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb

32 【Access上機實作5】 承上機練習4，將「電話」與「地址」欄位刪除。 【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 利用Alter指令刪除欄位名稱 Alter Table 學生表 Drop 電話 步驟二：執行SQL指令 註：再重複步驟一與步驟二 來刪除「地址」欄位 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb

33 【上機作業】 請利用Create Table來建立「學生選課系統」的關聯式資料庫，其相關的資料表有三個，如下所示： 【解答】
【上機作業】 　 請利用Create Table來建立「學生選課系統」的關聯式資料庫，其相關的資料表有三個，如下所示： 【解答】 建立「學生表」 CREATE TABLE 學生表 (學號 CHAR(5), 姓名 CHAR(4), 系碼 CHAR(4), PRIMARY KEY(學號) ); 上機練習1~5已經完成

34 請再撰寫這兩段SQL指令，並上機執行。 建立「課程表」 CREATE TABLE 課程表 (課號 CHAR(5) NOT NULL,
課名 CHAR(20) NULL, 學分數 INT NULL, 必選修 CHAR(2) NULL, PRIMARY KEY(課號) ); 請再撰寫這兩段SQL指令，並上機執行。 建立「選課表」 CREATE TABLE 選課表 (學號 CHAR(8) NOT NULL, 課號 CHAR(5) NOT NULL, 成績 INT, PRIMARY KEY(學號,課號) ); 開啟Access資料庫中的「學生選課系統」 檔案路徑：在 ch6\ch6-3\ch6-3\bin\Debug\ch6-3.accdb

35 DROP TABLE　 【定義】 是指用來刪除資料表定義，當然，如果一個資料表內還有剩餘的紀錄，則這些紀錄會一併被刪除，因為如果資料表定義被刪除，則資料表的紀錄就沒有存在的意義了。 當資料表與資料表之間可能存在參考關係，比如「選課表」 <子關聯表>參考到「學生表」<父關聯表>時，則在刪除時，必須要先從<子關聯表>刪除，否則如果先從<父關聯表>刪除時，將會導致<子關聯表>參考不到上一層的<父關聯表>，亦即將「學生表」被刪除之後，則另一個「選課表」的記錄就變成沒有意義了。因此，產生所謂的「孤鳥」。

36 表示在「學生表」沒被其他《子關聯表》參考時， 才可被刪除。
【格式】 【分析1】DROP TABLE 學生表; 表示在「學生表」沒被其他《子關聯表》參考時， 才可被刪除。 DROP TABLE 資料表名稱

37 【舉例】　 在ch6-3.1中，建立資料表的順序是先建立<父關聯表>，才能建立<子關聯表>，其主要的原因為<子關聯表>參考(相依於)<父關聯表>的關係。相反地，欲刪除資料表時，則必須要先刪除<子關聯表>，才能刪除<父關聯表>。 請利用DROP Table來刪除「學生選課系統」的關聯式資料庫，其相關的資料表有三個，如下所示：

38 【舉例】<續…> 【分析1】辨別「父關聯表」與「子關聯表」
【舉例】<續…>　 【分析1】辨別「父關聯表」與「子關聯表」 在利用DROP Table來刪除資料表時，必須要先了解那些資料表是屬於父關聯表(一對多，一的那方；亦即箭頭被指的方向)與子關聯表(一對多，多的那方)，例如：上表中的「學生表」與「課程表」 【分析2】先刪除「子關聯表」之後，再建立「父關聯表」 例如：上表中的「選課表」

39 【上機練習】 請利用DROP Table來刪除以下三個資料表。 學生表(學號,姓名,電話,地址) 刪除順序：2
學生表(學號,姓名,電話,地址) 刪除順序：2 選課表(學號,課號,成績,選課日期) 刪除順序：1 課程表(課號,課名,學分數,必選修) 刪除順序：2 規則： 1.建立資料表的順序是先建立<父關聯表>，才能建立<子關聯表> 2.刪除資料表時，則必須要先刪除<子關聯表>，才能刪除<父關聯表>

40 6-4 SQL的DML指令介紹　 資料操作語言(Data Manipulation Language; DML)，利用DML，使用者可以從事對資料表記錄的新增、修改、刪除及查詢等功能。 DML有四種基本指令： 1. INSERT(新增) 2. UPDATE(修改) 3. DELETE(刪除) 4. SELECT(查詢)

41 6-4.1 INSERT指令 【定義】指新增一筆記錄到新的資料表內。 【格式】
【實例1】未指定欄位串列的新增(但是欲新增資料值必須能夠配合欄位型態及個數) 假設現在新增「產品代號」為A005,「品名」為桌球衣及「單價」為 1200的記錄到產品資料表中。 [註]在VALUES子句內的屬性值串列的排列順序要跟當初定義時一致。 INSERT INTO 資料表名稱 <欄位串列> VALUES(<欄位值串列> | <SELECT指令>)

42 【實例2】指定欄位串列的新增 假設現在新增產品代號為D002，品名為桌球皮的記錄到產品資料表中。
【實例2】指定欄位串列的新增　 欲新增資料值個數可以自行指定，不一定要與定義的欄位個數相同 假設現在新增產品代號為D002，品名為桌球皮的記錄到產品資料表中。 註：未指定對映的屬性，會被設定為DEFAULT值或NULL值， 如「產生資料表」的單價屬性值為NULL。

43 【實例3】 將羽球相關的品名從「產品資料表」中整批新增到另一個資料表中。 註：將查詢的結果「整批新增」到其他資料表中。
【實例3】　 將羽球相關的品名從「產品資料表」中整批新增到另一個資料表中。 註：將查詢的結果「整批新增」到其他資料表中。 注意：此功能只能在Access 2003版本或SQL Server上執行。

44 【Access上機實作6A】 承上機實作5，新增一筆記錄到「學生表」中。 【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 「新增記錄」Insert INSERT INTO 學生表 VALUES (‘S0001’, ‘小明', 'D001'); 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-4\ch6-4\bin\Debug\ch6-4.accdb 步驟二：執行SQL指令 開啟VB程式，檔案路徑在 ch6\ch6-4\ ch6-4.sln 注意：如果相同的資料，再新增一次時，則會 產生錯誤，因為主鍵不可以重複。

45 【Access上機實作6B】 假設有一個「學生資料表」及一個「科系代碼表」，其目前的欄位名稱及內容如下所示： 資料庫路徑：
ch6\ch6-4_1_Show\ch6-4_1_Show\bin\Debug\ ch6-4_1.accdb

46 現在欲新增學號為S0005, 姓名為「李崴」及就讀的系名為「電子系」的記錄到「學生資料表」中。
[註]在VALUES子句內的屬性值串列的排列順序要跟當初定義時一致。

47 【程式碼】 程式碼：ch6-4_1_Show.sln 01 02 03 04 05 06 07 08 09 10 11 12 13 14
【程式碼】　 程式碼：ch6-4_1_Show.sln 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 Private Sub Button1_Click(……) Handles Button1.Click Dim dbpath As String = "ch6-4_1.accdb" '宣告資料庫所在的路徑變數 Dim Source As String '宣告連線的字串 Source = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath Dim conn As OleDbConnection '宣告連線的物件 conn = New OleDbConnection(Source) '連線 conn.Open() '開啟資料庫 Dim InsertCmd As String InsertCmd = "Insert Into 學生資料表(學號,姓名,系碼) Values('" & TextBox1.Text.Trim() & "','" & TextBox2.Text.Trim() & "','" & Mid(ComboBox1.Text.Trim(), 1, 4) & "')" Dim cmd As OleDbCommand = New OleDbCommand(InsertCmd, conn) cmd.ExecuteNonQuery() conn.Close() ' 關閉資料庫 MsgBox("新增成功！") Call Show_Record() End Sub

48 【執行結果】 ch6\ch6-4_1_Show\ch6-4_1_Show\ch6-4_1_Show.sln 資料庫路徑：
【執行結果】 　 ch6\ch6-4_1_Show\ch6-4_1_Show\ch6-4_1_Show.sln 按「新增」鈕 輸入資料 顯示結果 資料庫路徑： ch6\ch6-4_1_Show\ch6-4_1_Show\bin\Debug\ ch6-4_1.accdb

49 6-4.2 UPDATE指令 【定義】指修改一個資料表中某些值組(記錄)之屬性值。 【格式】 UPDATE 資料表名稱
SET {<欄位名稱1>=<欄位值1>,…, <欄位名稱n>=<欄位值n>} [WHERE <條件子句>]

50 [實例] 在「產品資料表」中，有關桌球相關產品單價調升30%。 【解答】 產品資料表 SQL指令 UPDATE 產品資料表
[實例]　 在「產品資料表」中，有關桌球相關產品單價調升30%。 【解答】 產品資料表 SQL指令 UPDATE 產品資料表 SET 單價 = 單價*1.3 WHERE 品名 LIKE '桌球%'

51 【Access上機實作7A】 承上機實作6，修改學號為’S0001’的學生之「姓名」為”李安”。 【解答】 步驟三： 執行結果
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 「修改記錄」Update UPDATE 學生表 SET 姓名= '李安' WHERE 學號=‘S0001'; 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-4\ch6-4\bin\Debug\ch6-4.accdb 步驟二：執行SQL指令 開啟VB程式，檔案路徑在 ch6\ch6-4\ ch6-4.sln

52 【Access上機實作7B】 假設有一個「學生資料表」及一個「科系代碼表」，其目前的欄位名稱及內容如下所示： 資料庫路徑：
ch6\ch6-4_2_Show\ch6-4_2_Show\bin\Debug\ ch6-4_2.accdb

53 現在欲在「學生資料表」中，將李崴同學姓名改為「李碩崴」。

54 【程式碼】　 程式碼：ch6-4.2.sln 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 Private Sub Button3_Click(……) Handles Button3.Click Dim dbpath As String = “ch6-4_2.accdb" '宣告資料庫所在的路徑變數 Dim Source As String '宣告連線的字串 Source = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath Dim conn As OleDbConnection '宣告連線的物件 conn = New OleDbConnection(Source) '連線 conn.Open() '開啟資料庫 Dim UpdateCmd As String UpdateCmd = "UPDATE 學生資料表 SET 學號='" & TextBox1.Text.Trim() & "',姓名='" & TextBox2.Text.Trim() & "' ,系碼='" & Mid(ComboBox1.Text.Trim(), 1, 4) & "' WHERE 學號='" & TextBox1.Text.Trim() & "'" Dim cmd As OleDbCommand = New OleDbCommand(UpdateCmd, conn) cmd.ExecuteNonQuery() conn.Close() MsgBox("修改成功！") Call Show_Record() End Sub

55 【執行結果】 　 按「查詢」鈕 按「修改」鈕 顯示查詢結果 修改姓名資料 按「確定」鈕 輸入學號

56 6-4.3 DELETE指令 【定義】把合乎條件的值組(記錄)，從資料表中刪除。 【格式】 DELETE FROM 資料表名稱
[WHERE <條件式>]

57 [實例1]　 將尚未決定單價的產品記錄刪除。

58 [實例2] 將在「產品資料表」中刪除單價未達2500的產品記錄。 SQL指令 DELETE FROM 產品資料表 WHERE產品代號 IN
[實例2]　 將在「產品資料表」中刪除單價未達2500的產品記錄。 SQL指令 DELETE FROM 產品資料表 WHERE產品代號 IN (SELECT產品代號 FROM 產品資料表 WHERE 單價<=2500)

59 【Access上機實作8A】 承上機實作7，刪除學號為’S0001’的學生記錄。 【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令
【解答】 步驟三： 執行結果 步驟一：撰寫SQL指令 「刪除記錄」Delete DELETE * FROM 學生表 WHERE 學號=‘ S0001' 開啟Access資料庫中的「學生表」 檔案路徑：在 ch6\ch6-4\ch6-4\bin\Debug\ch6-4.accdb 步驟二：執行SQL指令 開啟VB程式，檔案路徑在 ch6\ch6-4\ ch6-4.sln

60 【Access上機實作8B】 假設有一個「學生資料表」及一個「科系代碼表」，其目前的欄位名稱及內容如下所示： 資料庫路徑：
ch6\ch6-4_3_Show\ch6-4_3_Show\bin\Debug\ ch6-4_3.accdb

61 現在欲在「學生資料表」中，將學號「S0005」學生資料記錄刪除。

62 【程式碼】　 程式碼：ch6-4.3.sln 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 Private Sub Button4_Click(……) Handles Button4.Click Dim dbpath As String = "ch6-4_3.accdb" '宣告資料庫所在的路徑變數 Dim Source As String '宣告連線的字串 Source = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath Dim conn As OleDbConnection '宣告連線的物件 conn = New OleDbConnection(Source) '連線 conn.Open() '開啟資料庫 Dim UpdateCmd As String UpdateCmd = "Delete From 學生資料表WHERE 學號='" & TextBox1.Text.Trim() & "'" Dim cmd As OleDbCommand = New OleDbCommand(UpdateCmd, conn) cmd.ExecuteNonQuery() conn.Close() MsgBox("刪除成功！") TextBox1.Clear() TextBox2.Clear() TextBox3.Clear() TextBox4.Clear() Call Show_Record() End Sub End Class

63 【執行結果】 　

64 6-4.4 SELECT指令簡介 【定義】是指用來過濾資料表中符合條件的記錄。 【格式】
SELECT [DISTINCT] <欄位串列> FROM (資料表名稱 {<別名>} | JOIN資料表名稱) [WHERE <條件式>] [GROUP BY <群組欄位> ] [HAVING <群組條件>] [ORDER BY <欄位> [ASC | DESC]]

65 【實例1】　 請顯示下列「產品資料表」中所有產品記錄 產品資料表 【解答】 【執行結果】 SELECT * FROM 產品資料表

66 6-5 DCL的指令介紹 【定義】 資料控制語言(Data Control Language; DCL)，DCL控制使用者對
資料庫內容的存取權利。 【指令】 1.GRANT(授權) 2.REVOKE(移除權限) 【DCL提供交易控制指令】 (1)COMMIT指令：確認(儲存)資料庫的交易。 (2)ROLLBACK指令：回復(復原Recovery)資料庫的交易。 (3)SAVEPOINT指令：設定群組內交易的記號，以方便ROLLBACK。

67 6-5.1 GRANT指令 【定義】 GRANT指令用來取得現有資料庫使用者帳號的權限。 【格式】
其中，「權限」可分為四種：Insert, Update, Delete, Select GRANT 權限 ON 資料表名稱 TO 使用者

68 [實例1] 對USER1與USER2提供SELECT與INSERT對客戶資料表的使用者權限功能。 【解答】 SQL語法
[實例1]　 對USER1與USER2提供SELECT與INSERT對客戶資料表的使用者權限功能。 【解答】 SQL語法 GRANT SELECT, INSERT ON 客戶資料表 TO USER1, USER2

69 [實例2]　 對所有的使用者提供Select的功能權限。 【解答】 SQL語法 GRANT SELECT ON 客戶資料表 TO PUBLIC

70 6-5.2 REVOKE指令 【定義】REVOKE指令用來取消資料庫使用者已取得的權限。 【格式】
【實例】表示從USER2帳號移除對的INSERT權限。 REVOKE 權限 資料表名稱 FROM 使用者 SQL語法 REVOKE INSERT ON 客戶資料表 FROM USER2

71 6-5.3 COMMIT指令 【定義】 是指用來確認最後一次COMMIT指令對資料庫所做的交易。 【格式】 【實例】確認對資料庫所做的交易
COMMIT [WORK]; SQL語法 DELETE FROM 產品資料表 WHERE 產品代號='D002' COMMIT ; 保証永久DELETE

72 6-5.4 ROLLBACK指令 【定義】 ROLLBACK指令用來回復(Recovery)尚未被COMMIT的資料庫交易。 【格式】
ROLLBACK [TO SAVEPOINT]

73 [實例] SELECT * FROM 產品資料表 UPDATE 產品資料表 SET 單價=單價*1.5 SELECT * FROM產品資料表
[實例]　 SELECT * FROM 產品資料表 UPDATE 產品資料表 SET 單價=單價*1.5 SELECT * FROM產品資料表 ROLLBACK;

74 6-5.5 SAVEPOINT指令 【定義】 是指交易內的點，回復動作可以SAVEPOINT為指標點，而不必回復整個交易。 【格式】

75 【實例】 SAVEPOINT P1; DELETE FROM 產品資料表 WHERE 產品代號 LIKE 'A%';
【實例】　 SAVEPOINT P1; DELETE FROM 產品資料表 WHERE 產品代號 LIKE 'A%'; SAVEPOINT P2; DELETE FROM 產品資料表 WHERE 產品代號 LIKE 'B%'; SAVEPOINT P3; 若希望回復到SAVEPOINT P2之處，則 ROLLBACK P2; COMMINT; 結果只刪除產品代號A開頭的所有產品。

76 【注意】　 COMMIT, ROLLBACK, SAVEPOINT等三個交易控制指令只能與DML的INSERT, UPDATE, DELETE一起使用。 而SELECT指令是唯讀(Read-only)，故不必Commit或Rollback。