SQL Server編程必知必會（插入/刪除/更新資料，視圖） -- （80-85 點總結）

標籤：sql

------------------------插入資料-----------------------
-- 80.1. 插入完整的行
-- 各個列必須以他們在表定義中出現的次序填充
INSERT INTO customers
VALUES (‘Pep E. LaPew‘,‘100 Main Street‘,‘LOS Angeles‘,‘CA‘,‘90046‘,‘USA‘,NULL,NULL)

-- INSERT 語句的安全操作方法，指定列, INTO 關鍵字是可選的
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES (‘Pep E. LaPew‘,‘100 Main Street‘,‘LOS Angeles‘,‘CA‘,‘90046‘,‘USA‘,NULL,NULL)

-- 80.2.  插入行的一部分
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (‘Pep E. LaPew‘,‘100 Main Street‘,‘LOS Angeles‘,‘CA‘,‘90046‘,‘USA‘)

-- 80.3. 插入多行,一次只能插入一行值，單條INSERT語句不支援多個VALUES子句
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (‘Pep E. LaPew‘,‘100 Main Street‘,‘LOS Angeles‘,‘CA‘,‘90046‘,‘USA‘)
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES (‘M. Martian‘,‘42 Galaxy Way‘,‘New York‘,‘NY‘,‘11213‘,‘USA‘)

-- 80.4. 插入某些查詢的結果
INSERT INTO customers(
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
SELECT cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM customers
where cust_id=10001

-- 80.5. 建立一個包含從一個或多個表檢索出的行，可以用SELECT INTO 表，此表必須不存在，否則將出錯；
SELECT cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
INTO customersExport
FROM customers

------------------------更新資料-----------------------

-- 要更新的表
-- 列名和它們的新值
-- 確定要更新行的過濾條件

-- 81.1 更新表中特定行:更新客戶10005的電子郵件地址
update customers
set cust_email =‘[email protected]‘
where cust_id = 10005

update customers
set cust_email = NULL
where cust_id = 10005

-- 81.2 更新表中所有行:更新所有客戶的電子郵件地址，為‘[email protected]‘
update customers
set cust_email =‘[email protected]‘

-- 81.3 更新客戶10005的多個列,電子郵件地址和姓名
update customers
set cust_email =‘[email protected]‘,
    cust_name = ‘The Fudds‘
where cust_id = 10005

------------------------刪除資料-----------------------

-- 刪除和更新的指導規則： 最好使用帶where的update和delete子句, SQL Server沒有撤銷under按鈕。所有必須小心地使用update和delete；

-- 82.1 從表中刪除特定的行:刪除cust_id=10006的行
DELETE FROM customers
WHERE cust_id= 10006

-- 82.2 從表中刪除所有行
DELETE FROM customers

------------------------建立和動作表-----------------------

-- 建立表的兩種方法： 1. 使用SSMS 等建立和管理表的公交 。 2.使用SQL 陳述式操縱

-- 建立表基礎： 新表的名字，表列的名字和定義，用逗號分隔；

-- 83.1  建立customers表，主鍵為cust_id 唯一併且自動成長，也可以用多個列組成;NULL值用關鍵字NULL而不是空串指定,空串‘‘是表示一個有效值，它不是無值；
create table customers
(
cust_id         INT        NOT NULL IDENTITY(1,1),
cust_name       NCHAR(50)  NOT NULL,
cust_address    NCHAR(50)  NULL,
cust_city       NCHAR(50)  NULL,
cust_state      NCHAR(5)   NULL,
cust_zip        NCHAR(10)  NULL,
cust_country    NCHAR(50)  NULL,
cust_contact    NCHAR(50)  NULL,
cust_email      NCHAR(255) NULL,
PRIMARY KEY (cust_id)
);

create table orderitems
(
order_num     INT         NOT NULL,
order_item    INT         NOT NULL,
prod_id       NCHAR(10)   NOT NULL,
quantity      INT         NOT NULL,
item_price    MONEY       NOT NULL,
PRIMARY KEY (order_num,order_item)
)

-- IDENTITY(1,1) 表示從種子1開始，每次產生新編號的增量為1； IDENTITY(100,10)表示從種子100開始，增量為10；

-- 83.2 更新表，給表orderitems添加一個列vend_phone，類型為char(20),即使表中存在資料也可以添加列，但是不建議這樣做
ALTER TABLE orderitems
ADD vend_phone CHAR(20)

-- 83.3 更新表，給表orderitems刪除一個列vend_phone,但是刪除之前請確認，表中該列資料也會刪除
ALTER TABLE orderitems
DROP COLUMN vend_phone

-- 83.4 更新表，添加外鍵

-- 給表orderitems添加外鍵fk_ordertiems_orders,即表orders的主鍵order_num
ALTER TABLE orderitems
ADD CONSTRAINT fk_ordertiems_orders FOREIGN KEY (order_num)
REFERENCES orders(order_num)
 
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id)
REFERENCES vendors(vend_id)

-- ALTER TABLE可以用來更改表列（或約束，索引等對象）

-- 83.5 表結構更改的手動刪除過程，最好在操作前先做個完全備份，需要遵守以下步驟：

-- a. 建立一個新的表，新的表名，使之包含新的列
CREATE TABLE orderitems_temp
  (
  order_num INT NOT NULL,
  order_item INT NOT NULL,
  prod_id  nchar(10) NOT NULL,
  quantity INT NOT NULL,
  item_price  MONEY  NOT NULL,
  item_price2  MONEY  NOT NULL,
  PRIMARY KEY (order_num,order_item) 
  )

-- b. 使用INSERT SELECT INTO 語句將舊錶的資料複製到新表
  INSERT INTO orderitems_temp
  SELECT * FROM orderitems

-- c. 檢驗新表的資料包含舊錶的資訊，select * from orderitems_temp

-- d. 重新命名舊錶，建議重新命名舊錶不要刪除，待驗證成功後，可刪除： exec sp_rename ‘orderitems‘, ‘orderitems_old‘

-- e. 用舊錶原來的名字重新命名新表： exec sp_rename ‘orderitems_temp‘, ‘orderitems‘

-- f. 根據需要重新建立觸發器，預存程序，索引和外鍵； 下面重新建立外鍵

ALTER TABLE orderitems_old
DROP CONSTRAINT fk_ordertiems_orders

ALTER TABLE orderitems_old
DROP CONSTRAINT fk_orderitems_products

ALTER TABLE orderitems
ADD CONSTRAINT fk_ordertiems_orders FOREIGN KEY (order_num) REFERENCES orders(order_num) 

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products(prod_id)

-- 84.1 刪除表orderitems_old,該語句不需要確認，也不能撤銷，執行後將永久刪除該表。

DROP TABLE orderitems_old

------------------------使用視圖-----------------------

-- 85.1 視圖是虛擬表，與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢； 視圖包含的是一個SQL 查詢

-- 85.2 為什麼使用視圖

-- a. 可以重用SQL 陳述式；
-- b. 簡化複雜的SQL 操作； 可以一次性編寫基礎的SQL, 然後根據需要多次使用；
-- c. 使用表的組成部分而不是整個表；
-- d. 保護資料，可以咯使用者授予表的特定部分的存取權限而不是整個表的存取權限；
-- e. 更改資料格式和表示，視圖可返回與底層表的表示和格式的不同的資料；
-- f. 視圖本身不包含資料，返回的資料是從其他表檢索出來的，在添加或更改這些表中的資料時，視圖將返回改變過的資料。

-- 85.3 視圖的規則和限制

-- a. 與表一樣，視圖必須唯一命名；
-- b. 對於可以建立的視圖數目沒有限制；
-- c. SQL SERVER視圖可能只包含不多於1024列；
-- d. 為了建立視圖，必須具有足夠的存取權限。這些限制通常由資料庫管理員授予；
-- e. 視圖可以嵌套，即可以從其他視圖中檢索資料的查詢來構造一個視圖；
-- f. order by 不可以用在視圖中，但可以用在從視圖中檢索資料的select 語句裡；
-- g. 視圖不能索引，也不能有關聯的觸發器或預設值；
-- h. 視圖可以和表一起使用，如編寫一條連接表和視圖的select語句

-- 85.4 視圖的使用

-- a. 建立視圖productcustomers，返回已訂購任意產品的所有客戶的列表

-- 建立視圖productcustomers
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 * FROM productcustomers

-- 返回訂購了產品TNT2的客戶的列表
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = ‘TNT2‘

-- b. 建立視圖vendorlocations，用來重新格式化檢索出的資料
CREATE VIEW verdorlocations AS
SELECT RTrim(vend_name) + ‘(‘ + RTrim(vend_country) + ‘)‘
AS vend_title
FROM vendors

SELECT *
FROM verdorlocations
ORDER BY vend_title

-- c. 建立視圖customer_email_list，過濾掉不要的資料，返回沒有電子郵件地址的客戶
CREATE VIEW customer_email_list AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL

SELECT *
FROM customer_email_list

-- d. 建立視圖orderitemsexpanded，簡化計算欄位,檢索訂單為20005的詳細內容；

CREATE VIEW orderitemsexpanded AS
SELECT
  order_num,
  prod_id,
  quantity,
  item_price,
  quantity*item_price AS expanded_price
FROM orderitems

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005

-- e.刪除視圖orderitemsexpanded：

DROP VIEW orderitemsexpanded

-- 85.5 視圖的更新：實際上是對基表的增加或刪除行； 視圖主要是用於資料檢索的；

-- 視圖是可更新的，可對他們使用INSERT,UPDATE和DELETE操作；

-- 並非所有視圖都可更新，如果視圖定義中有以下情況，視圖將不可以進行更新；

-- a. 有多個基表
-- b. 有分組(使用GROUP BY 和HAVING)
-- c. 有連接(JOIN)
-- d. 有子查詢
-- e. 有並（UNION）
-- f. 有聚集合函式（Min(),Count(),Sum()等）
-- g. 有DISTINCT 關鍵字
-- h. 有匯出（計算）列

本文出自 “Ricky's Blog” 部落格，請務必保留此出處http://57388.blog.51cto.com/47388/1705546

SQL Server編程必知必會（插入/刪除/更新資料，視圖） -- （80-85 點總結）