CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ] [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ] AS query;
DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];
--删除表 order_info DROP TABLE IF EXISTS order_info; --创建表 order_info CREATE TABLE order_info ( order_id int PRIMARY KEY ,order_date date not null ,supplier_id int not null ,goods_id char(20) not null ,goods_name char(20) not null ,goods_home varchar(100) not null ,goods_number int not null ,goods_amount int not null );
--删除表 sell_list_info DROP TABLE IF EXISTS sell_list_info; --创建表 sell_list_info CREATE TABLE sell_list_info ( sell_id int PRIMARY KEY ,sell_date date not null ,goods_id char(20) not null ,goods_name char(20) not null ,goods_number int not null ,sell_goods_amount int not null );
--删除表 goods_info DROP TABLE IF EXISTS goods_info; --创建表 goods_info CREATE TABLE goods_info ( goods_id char(20) PRIMARY KEY ,goods_code varchar(50) not null ,goods_name char(20) not null ,goods_home varchar(100) not null ,goods_number int not null ,purchase_goods_amount int not null ,sell_goods_amount int not null ,supplier_id int not null ,warehouse_id int not null );
--删除视图 DROP VIEW IF EXISTS order_info_view; --依据订单表,按商品名称及订货日期统计商品总数和总价、平均价格。 CREATE VIEW order_info_view AS SELECT goods_name ,order_date ,SUM(goods_number) AS total_number ,SUM(goods_number * goods_amount) AS total_amount ,AVG(goods_amount) AS avg_amount FROM order_info GROUP BY goods_name, order_date;
--删除视图 DROP VIEW IF EXISTS sell_list_sum_view; --依据销售表,按年度统计所有销售商品的销售总数量和总销售额,以及其对应的仓库编号和供应商编号。 CREATE VIEW sell_list_sum_view AS SELECT t1.goods_id ,t1.goods_name ,t1.s_year ,t1.total_number ,t1.total_amount ,t2.supplier_id ,t2.warehouse_id FROM (SELECT goods_id ,EXTRACT(YEAR FROM sell_date) AS s_year ,goods_name ,SUM(goods_number) AS total_number ,SUM(goods_number * sell_goods_amount) AS total_amount FROM sell_list_info GROUP BY goods_id ,EXTRACT(YEAR FROM sell_date) ,goods_name ) t1 LEFT JOIN (SELECT goods_id ,supplier_id ,warehouse_id FROM goods_info ) t2 ON t1.goods_id =t2.goods_id
--删除视图 DROP VIEW IF EXISTS goods_info_view; --只允许某个用户或角色访问某些数据(指定列) --例如,给用户zhangsan 访问“商品信息管理表(无此表访问权限)”的字段“商品名称、商品存量、商品售价”。 --赋予zhangsan访问此视图的权限 CREATE VIEW goods_info_view AS SELECT goods_name ,goods_number ,sell_goods_amount FROM goods_info; --赋予zhangsan查询权限 GRANT SELECT ON goods_info_VIEW TO zhangsan;
--删除视图 DROP VIEW IF EXISTS student_c_view; --基于上一篇文章中的学生课程成绩表,生成一张视图:获取学生的姓名、课程、成绩、代课老师的信息汇总数据 CREATE VIEW student_c_view AS SELECT t2.sname ,t3.cname ,t1.scgrade ,t3.cteacher FROM (SELECT sno ,cno ,scgrade FROM sc ) t1 LEFT JOIN students t2 ON t1.sno=t2.sno left join course t3 ON t1.cno=t3.cno; --查看结果 SELECT * FROM student_c_view;
如果您发现该资源为电子书等存在侵权的资源或对该资源描述不正确等,可点击“私信”按钮向作者进行反馈;如作者无回复可进行平台仲裁,我们会在第一时间进行处理!
加入交流群
请使用微信扫一扫!