数据库应用

一.创建视图
use test;
select*from student;
use runoob;


视图view
视图是数据库对象之一，在sql语句中体现的角色与表一致。
但是视图并非一张真实存在的表，它只是一个查询语句对应的结果集。


create view v_emp_gm
as
select sno,sname,sage
from student;


desc v_emp_gm;
select*from v_emp_gm;


视图对应的子查询中的字段可以指定别名，
这样该视图对应的字段名就是这个别名，
当一个字段是函数或者表达式，那么该
字段必须指定别名
create or replace view v_emp_linsa
as
select sno id,sname name,sage age,ssex sex
from student;


select*from v_emp_linsa;


select*from teacher,v_emp_linsa;


insert into v_emp_linsa(id,name,age,sex) values('s011','李四',24,'男');


select*from v_emp_linsa;


为视图添加查询选项，可以避免对视图操作而导致的对基表的数据污染
with check option
该选项要求对视图进行DML操作后，该记录必须对视图可见
create view v_student_gm1
as
select sno,sname
from student
with check option;


select*from v_student_gm1;
insert into v_student_gm1 values("s012","linsa");


只读选项
with read only
只读选项要求对视图仅能进行查询操作不能进行任何的DML操作


二.创建及调用过程


use tedustore;
select*from books;


create procedure pro_getbook()
begin
 select*from books;
end


call pro_getbook();


drop procedure pro_id;
create procedure pro_id(in a int)
begin
 select*from linsa where id=a;
end


call pro_id(1);


use test;


drop procedure proc_id;
create procedure pro_id(in a varchar(20))
begin
 select*from student where sno=a;
end


call pro_id('s001');


drop procedure proc_sc1;
create procedure pro_sc1(
out max decimal(6,2),
out avg decimal(6,2),
out min decimal(6,2)
)
begin
select max(score) into max from sc;
select avg(score) into avg from sc;
select min(score) into min from sc;
end


call pro_sc1(@max,@avg,@min);
select @max,@avg,@min;


use tedustore;
select*from books;


create procedure proc_sc2()
begin
declare a,b varchar(40);
set a="java";
set b="12345";
insert into books values(null,a,b);
end


call proc_sc2();