视图

MySQL

创建视图

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

字段含义:

• create 表示创建新的视图
• replace 表示替换已经创建的视图
• algorithm 表示视图选择的算法
• view_name 为视图的名称
• column_list 为属性列
• select_statement 表示select语句
• with[cascaded | local] check option 参数表示视图在更新时保证在视图的权限范围之内.

# 在单表上创建视图
create view view_t as select quantity, price, quantity * price from t;
# 在多表上创建视图
create view stu_glass (id,name,glass) as select student.s_id, student.name, stu_info.glass
from student, stu_info where student.s_id=stu_info.s_id;

查看视图

describe 视图名;
desc 视图名;
# 查看视图基本信息
show table status like 'view_t' \G;
# 查看视图详情信息
show create view view_t \G;
# 在views表中查看视图详情
select * from information_schema.views \G;

修改视图

使用 create or replace view 语句修改视图

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

使用 alter 语句修改视图

alter [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]

更新视图

更新视图和更新表一样,个人理解一般不更新视图数据.

删除视图

# 语法
drop view [if exists]
view_name [, view_name] ...
[restrict | cascade]
# eg
drop view if exists stu_glass;

最近项目中的应用

CREATE OR REPLACE VIEW view_income_invoice as
            SELECT 'RKTZ' as bill_type,'入库通知' as title,
            a.bill_num as bill_num,b.date as date,c.id as supply_company_id,c.name as supply_company_name,
            a.invoice_num,a.invoice_weight,a.invoice_fee,
            a.acceptance_num as num,a.acceptance_weight as weight,a.tax_money as money,
            (a.acceptance_num - a.invoice_num) as uninvoice_num,
            (a.acceptance_weight - a.invoice_weight) as uninvoice_weight,
            (a.tax_money - a.invoice_fee) as uninvoice_fee,
            a.categorys_small_id,a.categorys_small_name,a.materials_id,a.materials_name,a.specs_id,a.specs_name,
            a.tax_price,a.tax_money,a.tax_rate,a.tax_diff_money
            FROM inventory_incoming_notice_detail as a
            INNER JOIN inventory_incoming_notice as b on a.bill_num_incoming_notice = b.bill_num
            LEFT JOIN basic_supplier as c on b.supply_company_id = c.id
            WHERE b.status=1 
            UNION DISTINCT
            SELECT 'KCST' as bill_type,if(b.goods_type=2,'临调实提','直发实提') as title,
            a.bill_num as bill_num,b.date as date,c.id as supply_company_id,c.name as supply_company_name,
            a.invoice_num,a.invoice_weight,a.invoice_fee,
            a.num_real as num,a.weight_real as weight,d.tax_money as money,
            (a.num_real - a.invoice_num) as uninvoice_num,
            (a.weight_real - a.invoice_weight) as uninvoice_weight,
            (d.tax_money - a.invoice_fee) as uninvoice_fee,
            a.categorys_small_id,a.categorys_small_name,a.materials_id,a.materials_name,a.specs_id,a.specs_name,
            d.tax_price,d.tax_money,d.tax_rate,d.tax_diff_money
            FROM inventory_real_lading_details as a
            INNER JOIN inventory_real_lading as b on a.bill_num_real_lading = b.bill_num
            LEFT JOIN basic_supplier as c on b.supply_company_id = c.id
            LEFT JOIN sales_lading_bill_details  as d on a.bill_num_sales_lading_bill_details = d.bill_num
            WHERE b.status=1 AND    b.goods_type IN (2,3)

参考资料:
MySQL5.7从入门到精通