1 / 156

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

挑戰資料庫管理系統 A Challenge to Database Management Systems. 第 7 章 SQL. 大綱. 7.1 前言 7.2 SQL 的演進、分類與使用 7.3 建立資料庫 7.4 建立資料表 7.5 建立索引與刪除索引 7.6 案例研討 7.7 新增資料、修改資料、刪除資料 7.8 修改資料 -UPDATE 7.9 刪除資料 -DELETE 和 TRUNCATE 7.10 查詢資料 -SELECT. 7.1 前言. 本章將學習 SQL 的基本應用

neil-carroll
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 第7章 SQL 挑戰資料庫管理系統

  2. 大綱 • 7.1 前言 • 7.2 SQL的演進、分類與使用 • 7.3 建立資料庫 • 7.4 建立資料表 • 7.5 建立索引與刪除索引 • 7.6 案例研討 • 7.7 新增資料、修改資料、刪除資料 • 7.8 修改資料-UPDATE • 7.9 刪除資料-DELETE和TRUNCATE • 7.10 查詢資料-SELECT 挑戰資料庫管理系統

  3. 7.1 前言 • 本章將學習SQL的基本應用 • 用SQL的DDL指令來建立資料庫、資料表、及索引 • 用SQL的DML指令來新增、修改、刪除、查詢資料 • 第8章將學習SQL的進階應用 • 檢視表 • 授權 • T-SQL • 預存程序 • 觸發程序 • 函數 挑戰資料庫管理系統

  4. 7.2 SQL的演進、分類與使用 • SQL(Structured Query Language)讀為「SEQUAL」 • SEQUEL-XRM(1974-1975) • SEQUEL/2 • SQL-92(SQL2)為關聯式資料庫的共通標準語言 • SQL-99(SQL3)則適用於物件導向資料庫 • Oracle：Procedure Language extension to SQL (簡稱PL/SQL) • Sybase ：Transact SQL(簡稱T-SQL) • Microsoft SQL Server ：Transact SQL(簡稱T-SQL) 挑戰資料庫管理系統

  5. 7.2 SQL的演進、分類與使用 • SQL：集合為導向(Set Oriented)的資料庫語言 • 資料表 • 檢視表 • SQL資料表是由直行(Column)及橫列(Row)所組成 • 直行又稱為資料行，它用來描述資料的屬性(Attribute)或特徵(Property) • 橫列(稱為資料列)就代表一筆記錄(Record)。 挑戰資料庫管理系統

  6. 7.2 SQL的演進、分類與使用 • SQL涵蓋了DDL、DML和DCL • 資料定義語言(Data Definition Language， 簡稱DDL) • 資料處理語言(Data Manipulation Language， 簡稱DML) • 資料控制語言(Data Control Language， 簡稱DCL) 挑戰資料庫管理系統

  7. 7.2 SQL的演進、分類與使用 挑戰資料庫管理系統

  8. 7.2 SQL的演進、分類與使用 • 資料定義語言(Data Definition Language， 簡稱DDL) • 用來定義資料庫物件的組織架構 • 資料庫物件：資料庫、資料表、檢視表、索引 • CREATE/ALTER/DROP DATABASE • CREATE/ALTER /DROP TABLE • CREATE/ALTER /DROP INDEX • CREATE PROCEDURE • CREATE TRIGGER • CREATE FUNCTION 挑戰資料庫管理系統

  9. 7.2 SQL的演進、分類與使用 • 資料處理語言(Data Manipulation Language， 簡稱DML) • 新增、 修改、 刪除、查詢 • SELECT(查詢) • INSERT(新增) • UPDATE(修改) • DELETE(刪除) 挑戰資料庫管理系統

  10. 7.2 SQL的演進、分類與使用 • 資料控制語言(Data Control Language， 簡稱DCL) • 控制資料的存取權限 • GRANT(授予使用權) • REVOKE(撤銷使用權) • COMMIT(確認交易) • ROLLBACK(撤回交易) • LOCK(鎖住) • UNLOCK(解鎖) 挑戰資料庫管理系統

  11. 7.2 SQL的演進、分類與使用 • 主語言(Host Language)和SQL互動的方式有以下三種： • 主語言呼叫SQL預存程序(Stored Procedure) • 在主語言中內嵌(Embedded)靜態的SQL指令 • 在主語言中內嵌(Embedded)動態SQL指令 挑戰資料庫管理系統

  12. 7.3 建立資料庫 • 建立一個「圖書資料庫」(BookDB) 【執行】 CREATE DATABASEBookDB 【執行訊息】 CREATE DATABASE 處理序正在配置 0.75 MB 於磁碟 'BookDB'。 CREATE DATABASE 處理序正在配置 0.49 MB 於磁碟 'BookDB_log'。 挑戰資料庫管理系統

  13. 7.3 建立資料庫 • CREATE DATABASE 【SQL語法】 CREATE DATABASEdatabase_name [ ON [ < filespec > [ ,...n ] ]      [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] [ COLLATE collation_name ][ FOR LOAD | FOR ATTACH ] -------------------------------------------------------- < filespec > ::= [ PRIMARY ]( [ NAME = logical_file_name , ]    FILENAME = 'os_file_name'    [ , SIZE = size ]    [ , MAXSIZE = { max_size | UNLIMITED } ]    [ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::= FILEGROUP filegroup_name < filespec > [ ,...n ] 挑戰資料庫管理系統

  14. 7.3 建立資料庫 • 查看CREATE DATABASE語法 挑戰資料庫管理系統

  15. 7.3 建立資料庫 • 查看CREATE DATABASE語法 挑戰資料庫管理系統

  16. 7.3 建立資料庫 • 建立資料庫(採用SQL Server Enterprise Manager) • 方法1. • 直接點選 新增資料庫。 • 方法2. • 點選樹狀目錄裡的 [資料庫] 資料夾，按右鍵，選 [新增資料庫] • 方法3. • 點選樹狀目錄裡的 [資料庫] 資料夾，點選 新增資料庫 • 方法4 • 點選樹狀目錄裡的 [資料庫] 資料夾，點選 [執行]，選 [新增資料庫] 挑戰資料庫管理系統

  17. 3 1 4 2 7.3 建立資料庫 • 建立資料庫(採用SQL Server Enterprise Manager) 挑戰資料庫管理系統

  18. 7.3 建立資料庫 • 建立資料庫(採用SQL Server Enterprise Manager) 挑戰資料庫管理系統

  19. 7.4 建立資料表 • 「圖書資料庫」(BookDB)裡包含四張資料表(Table) • Student(學生)資料表 • Author(作者)資料表 • Book(書籍)資料表 • Loan(租借)資料表 挑戰資料庫管理系統

  20. 7.4 建立資料表 • Student(學生)資料表的結構 挑戰資料庫管理系統

  21. 7.4 建立資料表 • Student(學生)資料表的資料 挑戰資料庫管理系統

  22. 7.4 建立資料表 • Author(作者)資料表的結構 挑戰資料庫管理系統

  23. 7.4 建立資料表 • Author(作者)資料表的資料 挑戰資料庫管理系統

  24. 7.4 建立資料表 • Book(書籍)資料表的結構 挑戰資料庫管理系統

  25. 7.4 建立資料表 • Book(書籍)資料表的資料 挑戰資料庫管理系統

  26. 7.4 建立資料表 • Loan(租借)資料表的結構 挑戰資料庫管理系統

  27. 7.4 建立資料表 • Loan(租借)資料表的資料 挑戰資料庫管理系統

  28. 7.4 建立資料表 • 實體關係圖 挑戰資料庫管理系統

  29. 7.4 建立資料表 • MS SQL Server提供的資料型別 挑戰資料庫管理系統

  30. 7.4 建立資料表 • 叢集索引 • B-tree結構：平衡樹(Balance Tree) 挑戰資料庫管理系統

  31. 7.4 建立資料表 • B-tree的特性 • 索引頁 • 資料頁 • 是一棵平衡樹 • 從樹根節點到樹葉的距離都相同 • 搜尋每一筆資料的效率都相同 • 樹葉節點間亦按鍵值順序，用索引指標相互連結 • 資料已經依鍵值大小排序 挑戰資料庫管理系統

  32. 7.4 建立資料表 • 建立資料表、主要鍵索引及外來鍵之參照關係 【執行】 -- 建立學生資料表Student CREATE TABLEStudent (S_id char(9) NOT NULLPRIMARY KEY, S_name char(8) NOT NULL , S_sex char(2) NULL , S_grade char(4) NOT NULL , S_address varchar(50) NOT NULL , S_tel char(14) NOT NULL , S_email varchar(25) NOT NULL ) 挑戰資料庫管理系統

  33. 7.4 建立資料表 • 建立資料表、主要鍵索引及外來鍵之參照關係 【執行】 --建立作者資料表Author CREATE TABLEAuthor(Au_id char(6) NOT NULL PRIMARY KEY, Au_name char(24) NOT NULL , Au_birthday smalldatetime NOT NULL , Au_sex char(2) NOT NULL , Pro varchar40) NOT NULL ) 挑戰資料庫管理系統

  34. 7.4 建立資料表 • 建立資料表、主要鍵索引及外來鍵之參照關係 【執行】 -- 建立書籍資料表Book CREATE TABLEBook(B_code char(9) NOT NULL PRIMARY KEY CLUSTERED, B_name varchar(30) NOT NULL, Au_id char(6) NOT NULLCONSTRAINT FK_Book_Author1 FOREIGN KEY REFERENCES Author (Au_id), Publisher varchar(20) NOT NULL , Buydate smalldatetime NOT NULL ) 挑戰資料庫管理系統

  35. 7.4 建立資料表 • 建立資料表、主要鍵索引及外來鍵之參照關係 【執行】 --建立租借資料表Loan CREATE TABLELoan(S_id char (9) NOT NULL , B_code char (9) NOT NULL , Loandate smalldatetime NOT NULL , Returndate smalldatetime NOT NULL ,CONSTRAINT PK_Loan PRIMARY KEY CLUSTERED (B_code), CONSTRAINT FK_Loan_Book FOREIGN KEY (B_code) REFERENCES Book (B_code), CONSTRAINT FK_Loan_Student1 FOREIGN KEY (S_id) REFERENCES Student (S_id)) 挑戰資料庫管理系統

  36. 7.4 建立資料表 • 使用中文名稱 【執行】 --建立成績資料表 CREATE TABLE 成績 (學號 char (9) NOT NULL PRIMARY KEY,姓名 char (8) NOT NULL,計算機概論 smallint DEFAULT(0),資料庫 smallint DEFAULT(0),資料結構 smallint DEFAULT(0),總分 AS (計算機概論 + 資料庫+資料結構),平均 AS (計算機概論 + 資料庫+資料結構)/3) 挑戰資料庫管理系統

  37. 7.4.1 修改資料表 • 【例1】修改Student資料表 • 增加S_fax及S_email2兩個新資料行 【執行】 -- 修改Student資料表，增加S_fax及S_email2兩個新資料行 ALTER TABLEStudent ADD S_fax char (10) NULL, S_email2 varchar (25) NULL 挑戰資料庫管理系統

  38. 7.4.1 修改資料表 • 【例2】修改Student資料表 • 刪除S_fax及S_email2兩個資料行 【執行】 -- 修改Student資料表，刪除S_fax及S_email2兩個資料行 ALTER TABLE StudentDROP COLUMN S_fax,S_email2 挑戰資料庫管理系統

  39. 7.4.1 修改資料表 • 【例3】修改Student資料表 • 將S_sex由Null改為Not Null 【執行】 -- 修改Student資料表，將S_sex由Null改為Not Null ALTER TABLE Student ALTER COLUMN S_sex char (2) NOT NULL 挑戰資料庫管理系統

  40. 7.4.1 修改資料表 • 【例4】修改Student資料表 • 增加CK_sex條件約束 • 規定S_sex資料行之值域為 {'男'，'女' } 【執行】 -- 修改Student資料表，增加CK_sex條件約束 ALTER TABLE Student WITH CHECK ADD CONSTRAINT CK_sex CHECK (S_sex = '男' OR S_sex ='女') 挑戰資料庫管理系統

  41. 7.4.1 修改資料表 • 【例5】修改Student資料表 • 增加CK_tel條件約束 • 規定S_tel資料行之格式為「(xx)xxxx-xxxx」或「xxxx-xxxxxx」 • 其中 x 可以為 0 到 9 的任何阿拉伯數字 【執行】 -- 修改Student資料表，增加CK_tel條件約束 ALTER TABLE Student ADD CONSTRAINT CK_tel CHECK (S_tel LIKE '([0-9][0-9])[0-9][0-9] [0-9][0-9]-[0-9][0-9][0-9][0-9]' OR S_tel LIKE '[0-9][0-9][0-9][0-9]-[0-9] [0-9][0-9][0-9][0-9][0-9]') 挑戰資料庫管理系統

  42. 7.4.1 修改資料表 • 【例6】修改Student資料表之結構 • 將S_tel、S_email由Not Null改為Null，並增加CK_tel_email條件約束，規定S_tel、S_email兩資料行不能同時為Null 【執行】 ALTER TABLE Student ALTER COLUMNS_tel char (14) NULL ALTER TABLE Student ALTER COLUMNS_email varchar (25) NULL ALTER TABLE Student WITH CHECK ADD CONSTRAINT CK_tel_email CHECK (S_tel is NOT NULL OR S_email is NOT NULL) 挑戰資料庫管理系統

  43. 7.4.1 修改資料表 • 【例7】刪除條件約束 • 刪除Student資料表之CK_tel_email條件約束 【執行】 -- 刪除Student資料表之CK_tel_email條件約束 ALTER TABLE Student DROP CONSTRAINT CK_tel_email 挑戰資料庫管理系統

  44. 7.4.2 刪除資料表 • DROP TABLE 【SQL語法】 DROP TABLEtable_name 挑戰資料庫管理系統

  45. 7.4.2 刪除資料表 • 【例1】刪除Loan及Student資料表 • 如果先刪除Student再來刪除Loan，則會失敗 【執行】 DROP TABLELoan GO DROP TABLEBookDB.dbo.Student --也可以用 資料庫名稱.擁有者.資料表名稱 GO 挑戰資料庫管理系統

  46. 7.4.3 使用交談模式 • SQL Server Enterprise Manager 挑戰資料庫管理系統

  47. 7.4.3 使用交談模式 • SQL Server Enterprise Manager 挑戰資料庫管理系統

  48. 7.4.3 使用交談模式 • SQL Server Enterprise Manager 挑戰資料庫管理系統

  49. 7.4.3 使用交談模式 • SQL Server Enterprise Manager 挑戰資料庫管理系統

  50. 7.5 建立索引與刪除索引 • CREATE INDEX • DROP INDEX 【SQL語法】 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEXindex_nameON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) 【SQL語法】 DROP INDEX'table.index | view.index' [ ,...n ] 挑戰資料庫管理系統

More Related