2013-12-24上課上機補充指令

張貼日期：Dec 24, 2013 6:11:52 AM

CREATE VIEW 檢視(表)名稱

AS

  SELECT 欄位

  FROM 表格

  WHERE 條件

CREATE VIEW 書籍價格檢視表

AS

  SELECT bookname, price

  FROM Books

  WHERE price > 150

SELECT *

FROM 書籍價格檢視表;

ALTER VIEW 書籍價格檢視表

AS

  SELECT bookname, price

  FROM Books

  WHERE price > 100

---- DROP VIEW 檢視(表)名稱;

DROP VIEW 書籍價格檢視表;

---- 建立預存程序"課程資料報表"，並且執行預存程序。

CREATE PROCEDURE 書籍資料報表 

AS

BEGIN

    SELECT bookname, author, price

    FROM Books

END

EXECUTE 書籍資料報表;

ALTER PROCEDURE 書籍資料報表 

AS

BEGIN

    SELECT bookname, author, price

    FROM Books

    WHERE price > 150

END

DROP  PROC 書籍資料報表;

---- 建立參數為[薪水查詢]的預存程序，參數是薪水salary，可以顯示員工資料。

CREATE PROCEDURE 定價查詢

@價格 money

AS

BEGIN

    SELECT bookname, price

    FROM Books

    WHERE price > = @價格

END

EXECUTE 定價查詢 @價格=170

---- 在[BOB]資料庫的[Books]資料表建立名為[書籍管理]的觸發程序。

CREATE TRIGGER 書籍管理

ON Books

AFTER DELETE, UPDATE

AS 

    INSERT INTO Books_1

    SELECT * FROM Deleted

CREATE TABLE Books_1

(

id int NOT NULL,

bookname char(20) NOT NULL,

author char(10),

price int,

publisher char(25),

PRIMARY KEY (id)

);

DELETE FROM Books

WHERE bookname='三國演義';

DROP TRIGGER 書籍管理;

---- 建立索引：主要為加速查詢的動作

---- 在表格的schema中加PRIMARY KEY或UNIQUE的限制條件，則系統會自動產生對應索引

CREATE INDEX books_idx1

ON books(bookname);

DROP INDEX books_idx1 ON books;