張貼日期：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;