天天看點
傳回

SQL必知必會（第3版）學習筆記【16-22章】

第16章 更新和删除資料

更新資料

  • UPDATE語句
    • 更新表中特定行
    • 更新表中所有行 
UPDATE Customers 
SET cust_email = '[email protected]' 
WHERE cust_id = '1000000005';
  • 更新多個列 
UPDATE Customers 
SET cust_contact = 'Sam Roberts', 
    cust_email = '[email protected]' 
WHERE cust_id = '1000000006';

删除資料

  • DELETE語句
    • 從表中删除特定行
    • 從表中删除所有行 
DELETE FROM Customers 
WHERE cust_id = '100000006';
  • DELETE删除整行而不是删除列
  • 删除指定的列，可使用UPDATE語句
  • DELETE不删除表本身
  • 删除所有行，可使用TRUNCATE TABLE語句，速度更快（因為不記錄資料的變動）

更新和删除的指導原則

  • 除非确實打算更新和删除每一行，否則絕對要帶有WHERE子句
  • 保證每個表都有主鍵
  • 在對UPDATE或DELETE語句使用WHERE子句前，應該先用SELECT進行測試，保證它過濾的是正确的記錄
  • 使用強制實施引用完整性的資料庫，這樣DBMS将不允許删除具有與其他表相關聯的資料的行

第17章 建立和操縱表

建立表

  • 兩種建立表的方法
    • 多數DBMS具有互動式建立和管理表的工具
    • 直接用SQL語句操縱
  • CREATE TABLE語句

表建立基礎

為利用CREATE TALBE建立表，必須給出下列資訊：

- 新表的名字

- 表列的名字和定義，用逗号分隔

- 有的DBMS要求指定表的位置

CREATE TABLE Products 
(
    prod_id    CHAR()    NOT NULL,
    vend_id    CHAR()    NOT NULL,
    prod_name    CHAR()    NOT NULL,
    prod_price    DECIMAL(,)    NOT NULL,
    prod_desc    VARCHAR()    NULL
);

使用NULL值

  • 每個表列或者是NULL列，或者是NOT NULL列，這種狀态在建立時由表的定義規定
  • 如果不指定，則預設是NULL
  • 隻有不允許NULL值的列才可用于主鍵

指定預設值

  • 關鍵字DEFAULT 
CREATE TABLE OrderItems 
(
    order_num    INTEGER    NOT NULL,
    order_item    INTEGER    NOT NULL,
    prod_id    CHAR()    NOT NULL,
    quantity    INTEGER    NOT NULL    DEFAULT ,
    item_price    DECIMAL(,)    NOT NULL
);

擷取系統日期

資料庫 函數/變量
Access NOW()
DB2/PostgreSQL CURRENT_DATE
MySQL CURRENT_DATE()
Oracle SYSDATE
SQL Server/Sybase GETDATE()

更新表（表的定義）

  • ALTER TABLE語句
    • 表中包含資料時一般不要更新
    • 所有DBMS都允許給現有的表增加列
    • 許多DBMS不允許删除或更改表中的列
    • 多數DBMS允許重新命名表中的列
    • 許多DBMS對已經填有資料的列的更改有限制，對未填有資料的列幾乎沒有限制
  • 為了使用ALTER TABLE更改表結構，必須給出下面的資訊：
    • 在ALTER TABLE之後給出要更改的表名
    • 所做更改的清單 
ALTER TABLE Vendors 
ADD vend_phone CHAR();

删除表

  • DROP TABLE語句

重命名表

  • DB2、MySQL、Oracle和PostgreSQL可使用RENAME語句
  • SQL Server和Sybase可使用sp_rename

第18章 使用視圖

視圖

  • 視圖是虛拟的表，它隻包含使用時動态檢索資料的查詢
  • 某個查詢包裝成一個虛拟表（名為ProductCustomers）
  • 可以如下檢索出相同的資料： 
SELECT cust_name, cust_contact 
FROM ProductCustomers 
WHERE prod_id = 'RGAN01';
  • 視圖不包含任何列或資料，它包含的是一個查詢

為什麼使用視圖

  • 重用SQL語句
  • 簡化複雜的SQL操作
  • 使用表的組成部分而不是整個表
  • 保護資料，可以給使用者授予表的特定部分的通路權限，而不是整個表的通路權限
  • 更改資料格式和表示，視圖可傳回與底層表的表示和格式不同的資料

在視圖建立之後，可以用與表基本相同的方式利用它們。（虛拟表）

視圖的規則和限制

  • 視圖必須唯一命名
  • 對于可以建立的視圖數目沒有限制
  • 為了建立視圖，必須具有足夠的通路權限
  • 視圖可以嵌套，即：可以利用從其他視圖中檢索資料的查詢來構造一個視圖
  • 許多DBMS禁止在視圖查詢中使用ORDER BY子句
  • 有的DBMS要求命名傳回的所有列，如果列是計算字段，則需要使用别名
  • 視圖不能索引，也不能有關聯的觸發器或預設值
  • 有的DBMS把視圖作為隻讀的查詢
  • 有的DBMS允許建立這樣的視圖：它不允許進行導緻行不再術語視圖的插入或更新

建立視圖

  • CREATE VIEW語句
  • DROP VIEW viewname語句來删除視圖
  • 覆寫（或更新）視圖：必須先DROP它，再重新建立它

利用視圖簡化複雜的聯結

－　視圖最常見的應用之一是隐藏複雜的SQL，這通常都會涉及聯結

CREATE VIEW ProductCustomers AS 
SELECT cust_name, cust_contact, prod_id 
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id 
    AND OrderItems.order_num = Orders.order_num;

用視圖重新格式化檢索出的資料

  • 視圖的另一常見用途之一

用視圖過濾不想要的資料

使用視圖與計算字段

  • 簡化計算字段
  • 有些類似于程式設計裡的子產品/函數（提供了一種封裝SELECT語句的層次）

第19章 使用存儲過程

  • 為以後的使用而儲存的一條或多條SQL語句的集合
  • 可将其視為批檔案，雖然它們的作用不僅限于批處理

為什麼要使用存儲過程

  • 通過把處理封裝在容易使用的單元中，簡化複雜的操作
  • 由于不要求反複建立一系列處理步驟，保證了資料的一緻性（防止錯誤）
  • 簡化對變動的管理（保證安全性）
  • 存儲過程通常以編譯過的形式存儲，是以DBMS為處理指令所做的工作較少，提高性能
  • 存在一些隻能用在單個請求中的SQL元素和特性，存儲過程可以使用它們來編寫功能更強更靈活的代碼
  • 簡單、安全、高性能

-缺點：

- 不同DBMS的存儲過程文法有所不同

- 存儲過程的編寫比基本SQL語句複雜

執行存儲過程

  • EXECUTE語句
  • EXECUTE接受存儲過程名和需要傳遞給它的任何參數

建立存儲過程

  • 編寫代碼

第20章 管理事務處理

事務處理

  • 事務處理用來維護資料庫的完整性，它保證成批的SQL操作要麼完全執行，要麼完全不執行
  • 事務處理是一種機制，用來管理必須成批執行的SQL操作，以保證資料庫不包含不完整的操作結果（好比運作過程中突然斷電了，導緻批處理意外終止）
  • 事務：指一組SQL語句
  • 回退：rollback，撤銷指定SQL語句的過程
  • 送出：commit，将未存儲的SQL語句結果寫入資料庫表
  • 保留點：臨時占位符
  • 可以回退INSERT、UPDATE、DELETE語句
  • 不能回退CREATE、DROP、SELECT（回退SELECT也沒有必要）

控制事務處理

  • ROLLBACK語句
  • COMMMIT語句
  • SAVEPOINT語句

第21章 使用遊标

  • 有時，需要在檢索出來的行中前進或後退一行或多行，是以使用遊标（cursor）
  • 在存儲了遊标之後，應用程式可以根據需要滾動或浏覽其中的資料
  • 特性：
    • 能夠标記遊标為隻讀，資料不能更新和删除
    • 能控制可以執行的定向操作（向前、向後、第一、最後、絕對位置、相對位置等）
    • 能标記某些列為可編輯的，某些列為不可編輯的
    • 規定範圍，使遊标對建立它的特定請求（如存儲過程）或對所有請求可通路
    • 訓示DBMS對檢索出的資料（而不是指出表中活動資料）做複制，使在遊标打開和通路期間資料不變化
  • 遊标主要用于互動式應用，其中使用者需要滾動螢幕上的資料，并對資料進行浏覽或做出更改

使用遊标

步驟：

- 在能夠使用遊标前，必須聲明（定義）它

- 一旦聲明後，必須打開遊标以供使用（資料檢索出來）

- 對于填有資料的遊标，根據需要取出（檢索）各行

- 在結束遊标使用時，必須關閉遊标，而且可能的話，釋放遊标

建立遊标

  • DECLARE語句 
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers 
WHERE cust_email IS NULL;

使用遊标

  • OPEN CURSOR語句 
OPNE CURSOR CustCursor
  • FETCH語句通路遊标資料

關閉遊标 

CLOSE CustCursor

第22章 了解進階SQL特性

進階資料處理特性

限制

管理如何插入或處理資料庫資料的規則

引用完整性

- 主鍵（一種特殊的限制），建立主鍵：PRIMARY KEY

- 外鍵：外鍵是表中的一個列，其值必須在另一表的主鍵中列出（REFERENCES關鍵字）

- 唯一限制：用來保證一個列（或一組列）中的資料唯一（UNIQUE限制）

- 表可包含多個唯一限制，但每個表隻允許一個主鍵

- 唯一限制列可包含NULL值

- 唯一限制列可修改或更新

- 唯一限制列的值可重複使用

- 與主鍵不一樣，唯一限制列不能用來定義外鍵

  • 檢查限制（CHECK）
    • 檢查最小或最大值
    • 指定範圍
    • 隻允許特定的值

索引

用來排序資料，以加快搜尋和排序操作的速度。

- 索引改善檢索操作的性能，但降低資料插入、修改和删除的性能。在執行這些操作時，DBMS必須動态地更新索引

- 索引資料可能要占用大量的存儲空間

- 并非所有資料都适合于索引。唯一性不好的資料從索引得到的好處不必具有更多可能值的資料從索引得到的好處多

- 索引用于資料過濾和資料排序，如果經常以某種特定的順序排序資料，則該資料可能是索引的備選

- 可以在索引中定義多個列

  • CREATE INDEX語句

觸發器

觸發器是特殊對存儲過程，它在特定的資料庫活動發生時自動執行。

- 保證資料一緻（如大小寫自動轉換）

- 基于某個表的變動在其他表上執行活動

- 進行額外的驗證并根據需要回退資料

- 計算計算列的值或更新時間戳

一般來說，限制的處理比觸發器快，是以在可能的時候，盡量使用限制

資料庫安全

  • 利用管理機制授予或限制對資料的通路
  • 任何安全系統的基礎都是使用者授權和身份确認
  • GRANT、REVOKE語句
sql 資料庫 必知必會 sql學習 結構化查詢語言
上一篇: java酒店管理系統_酒店管理系統
下一篇: SQL必知必會（第3版）學習筆記【1-5章】

繼續閱讀