第十四章 视图

1.基本描述

    SQL中的视图，作为来自一个或多个表的数据的逻辑子集。视图用于限制数据访问。视图不包含它自己的数据，但包含类似窗口，通过该窗口可以查看或更改表中的数据。视图所基于的表称为基表。

2.视图的特点

    视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作；
    视图不能被修改，表修改或者删除后应该删除视图再重建；
    视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性；
    视图可以被嵌套，一个视图中可以嵌套另一个视图；
    视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序。

3.视图的功能

    简化用户操作；
    能以不同的角度观察同一个数据库；
    对重构数据库提供了逻辑独立性，利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构。

4.基本样例

CREATE VIEW customer_vw 
(
    cust_id,
    fed_id,
    cust_type_cd,
    address,
    city,
    state,
    zipcode
) AS 
SELECT cust_id, concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd, address, city, state, postal_code FROM customer;

SELECT cust_id, fed_id, cust_type_cd FROM customer_vw;

describe customer_vw;

SELECT cust_type_cd, count(*) FROM customer_vw WHERE state = 'MA' GROUP BY cust_type_cd ORDER BY 1;

SELECT cst.cust_id, cst.fed_id, bus.name FROM customer_vw cst INNER JOIN business bus ON cst.cust_id = bus.cust_id;

CREATE VIEW business_customer_vw 
(
    cust_id,
    fed_id,
    cust_type_cd,
    address,
    city,
    state,
    zipcode
)
AS SELECT cust_id, concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd, address, city, state, postal_code FROM customer 
WHERE cust_type_cd = 'B';

CREATE VIEW customer_total_vw
(
    cust_id, 
    cust_type_cd,
    cust_name,
    num_accounts,
    tot_deposits
)
AS SELECT cst.cust_id, cst.cust_type_cd,
CASE 
    WHEN cst.cust_type_cd = 'B' THEN
        (SELECT bus.name FROM business bus WHERE bus.cust_id = cst.cust_id)
    ELSE
        (SELECT concat(ind.fname, ' ', ind.lname) FROM individual ind WHERE ind.cust_id = cst.cust_id)
    END cust_name,
    SUM(CASE WHEN act.status = 'ACTIVE' THEN 1 ELSE 0 END) tot_active_accounts,
    SUM(CASE WHEN act.status = 'ACTIVE' THEN act.avail_balance ELSE 0 END) tot_balance
FROM customer cst INNER JOIN account act ON act.cust_id = cst.cust_id
GROUP BY cst.cust_id, cst.cust_type_cd;

CREATE TABLE customer_totals AS SELECT * FROM customer_total_vw;

CREATE OR REPLACE VIEW customer_totals_vw
(
    cust_id, 
    cust_type_cd, 
    cust_name,
    num_accounts,
    tot_deposits
)
AS SELECT cust_id, cust_type_cd, cust_name, num_accounts, tot_deposits
FROM customer_totals;

CREATE VIEW customer_vw 
(
    cust_id,
    fed_id,
    cust_type_cd,
    address,
    city,
    state,
    zipcode
) AS 
SELECT cust_id, concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd, address, city, state, postal_code FROM customer;

UPDATE customer_vw SET city = 'Woooburn' WHERE city = 'Woburn';

SELECT DISTINCT city FROM customer;

###
UPDATE customer_vw SET city = 'Woburn', fed_id = '99999999' WHERE city = 'Woooburn';

INSERT INTO customer_vw (cust_id, cust_type_cd, city) VALUES (9999, 'I', 'Worcester');
###

CREATE VIEW business_customer_vw
(
    cust_id,
    fed_id,
    address,
    city,
    state,
    postal_code,
    business_name,
    state_id,
    incorp_date
)
AS SELECT cst.cust_id, 
cst.fed_id, cst.address,
cst.city, cst.state,
cst.postal_code,
bsn.name, bsn.state_id, bsn.incorp_date
FROM customer cst INNER JOIN business bsn 
ON cst.cust_id = bsn.cust_id
WHERE cust_type_cd = 'B';

UPDATE business_customer_vw SET postal_code = '99999' WHERE cust_id = 10;

UPDATE business_customer_vw SET incorp_date = '2008-11-17' WHERE cust_id = 10;

###
UPDATE business_customer_vw SET postal_code = '88888', incorp_date = '2008-10-31' WHERE cust_id = 10;
###

SELECT table_name, table_type FROM information_schema.tables WHERE 
table_schema = 'bank' ORDER BY 1;

SELECT table_name, table_type FROM information_schema.tables WHERE
table_schema = 'bank' AND table_type = 'BASE TABLE' ORDER BY 1;

SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'bank' ORDER BY 1;

SELECT column_name, data_type, character_maximum_length char_max_len, 
numeric_precision num_prcsn, numeric_scale num_scale FROM 
information_schema.columns WHERE table_schema = 'bank' AND table_name = 'account'
ORDER BY ordinal_position;

SELECT index_name, non_unique, seq_in_index, column_name FROM 
information_schema.statistics WHERE table_schema = 'bank' AND
table_name = 'account' ORDER BY 1, 3;

SELECT constraint_name, table_name, constraint_type FROM 
information_schema.table_constraints WHERE table_schema = 'bank' ORDER BY 3, 1;

SELECT 'CREATE TABLE customer (' create_table_statement UNION ALL
SELECT cols.txt 
FROM 
    (SELECT concat(' ', column_name, ' ', column_type,
            CASE 
                WHEN is_nullable = 'NO' THEN ' not null'
                ELSE ''
            END,
            CASC
                WHEN extra IS NOT NULL THEN concat(' ', extra)
                ELSE ''
            END,
            ',') txt
    FROM information_schema.columns WHERE table_schema = 'bank' AND table_name = 'customer'
    ORDER BY ordinal_position
    ) cols
    UNION ALL
    SELECT ')';

SET @qry = 'SELECT cust_id, cust_type_cd, fed_id FROM customer';
PREPARE dynsql1 FROM @qry;
EXECUTE dynsql1;
DEALLOCATE PREPARE dynsql1;