mysql创建视图语法⽰例_mysql——视图——⽰例
1、创建视图
视图可以建⽴在⼀张表上，也可以建⽴在多张表上
语法格式：mysql视图和存储过程
create [ algorithm = { undefined | merge | temptable} ]
view 视图名 [(属性清单)]
as select 语句
[ with { cascaded | loocal } check option ];
===========================================================
前期表准备：
create table employee ( num int(50),
d_idint(50),
namevarchar(50),
ageint(50),
sexvarchar(50),
homeaddvarchar(50)
);insert into employee values(1,1001,'zhangsan',26,'nan','beijing');insert into employee
values(2,1001,'lisi',24,'nv','hunan');insert into employee values(3,1002,'wangwu',25,'nan','jiangsu');insert into employee values(4,1004,'aric',15,'nan','yingguo');select * fromemployee;create table department ( d_id int(50),
d_namevarchar(50),
functionevarchar(50),
addressvarchar(50)
);insert into department values(1001,'keyanbu','yanfachanpin','3lou5hao');insert into department values(1002,'shengchanbu','shengchanchanp','5louyiceng');insert into department
values(1003,'xiaoshoubu','cehuaxiaoshou','1louxiaoshoudating');select * from department;
select * from employee;
select * from department;
==================================================
在单表上创建视图：
/*在单表上创建视图*/
create view department_view1 as select * fromdepartment;/*创建视图*/
descdepartment_view1;/*查看视图*/
select * fromdepartment_view1;select d_id,d_name,functione from department_view1;
=======================================================
单表创建视图，⽰例02：
create view department_view2(name2,functione2,location2) as select d_name,functione,address fromdepartment;descdepartment_view2;select * from department_view2;
=======================================================
在多表上创建视图：
create view department_view3(d_id,name,age,sex,homeadd,bmmc,gongn,bangonglouceng) as
select
employee.d_id,employee.name,employee.age,employee.sex,employee.homeadd,department.d_name,department.functione,departmen
fromemployee,departmentwhere employee.d_id =department.d_id;
describe department_view3;
select * fromdepartment_view3;
======================================================================================================================================查看视图
describe 视图名称；
或者
desc 视图名称；
describe department_view1;desc department_view1;