数据库第十次作业-视图

1. 创建视图“view_info”,查询所有员工姓名、员工联系电话、车间地址、车间主任姓名、主任联系方式
2. 查看“view_info”视图字段信息

   desc view_info;

   
3. 创建视图“view_ chisel”,查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址
4. 查看库中所有表
5. 创建视图“view_ standard”,查询生产出质量达标产品的产品编号、员工姓名、员工联系电话
6. 查看“view_ standard”视图创建语句
7. 创建视图“view_ product”,查询所有产品的产品编号、是否合格、员工号、员工姓名、员工联系方式、车间编号、车间地址
8. 查看库中所有表
9. 删除“view_ chisel”视图
10. 创建视图“view_ leave”,查询存在离职员工的车间编号、车间主任、主任联系方式
11. 查看库中所有表

 

 1：create view view_info as select h.name,h.phone,hlx.address,hlx.name wsn,hlx.phone wsp from
    -> huanglexiang_worker h  right join huanglexiang_workshop hlx on
    -> h.shop_no=hlx.no;
select * from view_info

2：desc view_info;

3：create view view_chisel as select h.name,h.phone,hlx.address from
    -> huanglexiang_worker h inner join huanglexiang_workshop hlx on
    -> h.shop_no=hlx.no where shop_no = "WS1";

 select * from view_chisel;

4：show tables;

5：create view view_standard as select no,name,phone from  huanglexiang_worker where
    ->  is_onwork="1";

 select * from view_standard;

6：show create view view_standard\G;

7： create view view_product as
    -> select h.no ab,h.is_qualified,hlx.no abc,hlx.name,hlx.phone,x.no,x.address
    -> from huanglexiang_product  h inner join  huanglexiang_worker hlx
    -> on h.work_no = hlx.no
    -> inner join  huanglexiang_workshop x
    -> on hlx.shop_no = x.no;

 select * from view_product;

8：create view view_leave as
    -> select no,name,phone from huanglexiang_workshop  where no
    -> in (select shop_no from huanglexiang_worker where is_onwork = "0");

select * from view_leave;

9：drop view view_chisel;

10：select * from view_leave;

11 ：show tables;