数据库——第十次作业

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

create view view_info as select zff.name ab,zff.phone abc,mx.address,mx.name,mx.phone

from zhufangfang_worker zff right join zhufangfang_workshop mx on zff.shop_no =mx.no;

 2.查看“view_info”视图字段信息。

desc view_info;

 3.创建视图“view_ chisel”,查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址。

create view view_chisel as select zff.name,zff.phone,mx.address

 from zhufangfang_worker zff inner join zhufangfang_workshop mx on zff.shop_no =mx.no

where zff.shop_no ="WS1“；

 4.查看库中所有表。

show tables;

 5.创建视图“view_ standard”,查询生产出质量达标产品的产品编号、员工姓名、员工联系电话。

create view view_standard as select mz.no,zff.name,zff.phone

from zhufangfang_product mz inner join zhufangfang_worker zff on mz.work_no =zff.no

where is_qualified ="1";

 6.查看“view_ standard”视图创建语句。

show create view view_standard\G

 7.创建视图“view_ product”,查询所有产品的产品编号、是否合格、员工号、员工姓名、员工联系方式、车间编号、车间地址。

create view view_product as select mz.no ab,mz.is_qualified,zff.no abc,zff.name,zff.phone,mx.no,mx.address

 from zhufangfang_product mz inner join zhufangfang_worker zff on mz.work_no =zff.no

 inner join zhufangfang_workshop mx on zff.shop_no =mx.no;

 8.查看库中所有表。

 show tables;

 9.删除“view_ chisel”视图。

drop view view_chisel;

 10.创建视图“view_ leave”,查询存在离职员工的车间编号、车间主任、主任联系方式。

create view view_leave as select no,name,phone

 from zhufangfang_workshop where no in

 (select shop_no from zhufangfang_worker where is_onwork ="0");

 11.查看库中所有表。

show tables;