数据库 视图 存储过程

MySQL的视图

•视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。

•数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。

•

•使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用

•简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。

•

•安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

-- 视图的创建
create [or replace] [algorithm = {undefined | merge | temptable}]view view_name [(column_list)]as select_statement[with [cascaded | local] check option]参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement
：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。--创建 数据库mydb6_view,然后在该数据库下执行sql脚本view_data.sql 导入数据
create database mydb6_view;
create or replace view view1_emp
as 
select ename,job from emp; -- 查看表和视图 
show full tables;
-- 修改视图：修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。
alter view 视图名 as select语句
alter view view1_emp
as 
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where a.deptno = b.deptno;
-- 更新视图
create or replace view view1_emp
as 
select ename,job from emp;update view1_emp set ename = '周瑜' where ename = '鲁肃';  -- 可以修改
insert into view1_emp values('孙权','文员');  -- 不可以插入-- ----------视图包含聚合函数不可更新--------------
create or replace view view2_emp
as 
select count(*) cnt from emp;insert into view2_emp values(100);
update view2_emp set cnt = 100; 
-- ----------视图包含distinct不可更新---------
create or replace view view3_emp
as 
select distinct job from emp;insert into view3_emp values('财务');-- ----------视图包含goup by 、having不可更新------------------create or replace view view4_emp
as 
select deptno ,count(*) cnt from emp group by deptno having  cnt > 2;insert into view4_emp values(30,100);
-- ----------------视图包含union或者union all不可更新----------------
create or replace view view5_emp
as 
select empno,ename from emp where empno <= 1005
union 
select empno,ename from emp where empno > 1005;insert into view5_emp values(1015,'韦小宝');-- -------------------视图包含子查询不可更新--------------------
create or replace view view6_emp
as 
select empno,ename,sal from emp where sal = (select max(sal) from emp);insert into view6_emp values(1015,'韦小宝',30000);
-- ----------------------视图包含join不可更新-----------------
create or replace view view7_emp
as 
select dname,ename,sal from emp a join  dept b  on a.deptno = b.deptno;insert into view7_emp(dname,ename,sal) values('行政部','韦小宝',30000);-- --------------------视图包含常量文字值不可更新-------------------
create or replace view view8_emp
as 
select '行政部' dname,'杨过'  ename;insert into view8_emp values('行政部','韦小宝');
--重命名视图　　
-- rename table 视图名 to 新视图名; 
rename table view1_emp to my_view1
-- 删除视图 删除视图时，只能删除视图的定义，不会删除数据
-- drop view 视图名[,视图名…];
drop view if exists view_student;

Mysql的存储过程

什么是存储过程

•MySQL5.0 版本开始支持存储过程。

•

•简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

•

存储过就是数据库SQL语言层面的代码封装与重用

有哪些特性

•有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

•

•函数的普遍特性：模块化，封装，代码复用；

•

•速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
beginsql语句
end 自定义的结束符合
delimiter ;
-- 1：创建数据库 
create database mydb7_procedure; -- 2：在该数据库下导入sql脚本:procedure_data.sql
-- 创建存储过程
delimiter $$
create procedure proc01()
beginselect empno,ename from emp; 
end  $$
delimiter ;-- 调用存储过程
call proc01(); 
-- 局部变量 用户自定义 在begin/end块中有效
语法： 声明变量 declare var_name type [default var_value]; 
举例：declare nickname varchar(32);
delimiter $$
create procedure proc02()
begindeclare var_name01 varchar(20) default ‘aaa’;  -- 定义局部变量set var_name01 = ‘zhangsan’;  select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();
-- mysql中还可以使用select init 语句为变量赋值 语法
select col_name [...] into var_name[,...] 
from table_name wehre condition 其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
delimiter $$
create procedure proc03()
begindeclare my_ename varchar(20) ;select ename into my_ename from emp where empno=1001;select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();
-- 用户自定义，当前会话（连接）有效。类比java的成员变量 
delimiter $$
create procedure proc04()
beginset @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01  ;  --可以看到结果
-- 全局变量 语法：
@@global.var_name
-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;
-- 变量定义 语法
@@session.var_name
-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

存储过程传参 in out inout

-- 存储过程传参-in
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
beginselect * from emp where empno = param_empno;
end $$delimiter ;
call dec_param01('1001');
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
beginselect * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$delimiter ;
call dec_param0x('学工部',20000);
-- 存储过程传参- out
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
beginselect ename into out_ename from emp where emp.empno = empno;
end $$delimiter ;call proc08(1001, @o_ename);
select @o_ename;
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
beginselect ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$delimiter ;call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;
-- 存储过程传参 inout
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
beginselect  concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;set inout_sal = inout_sal * 12;  
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

存储过程流程控制

-- 流程控制-判断IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：
-- 语法
if search_condition_1 then statement_list_1[elseif search_condition_2 then statement_list_2] ...[else statement_list_n]
end if
-- 输入学生的成绩，来判断成绩的级别：
/*score < 60 :不及格score >= 60  , score <80 :及格score >= 80 , score < 90 :良好score >= 90 , score <= 100 :优秀score > 100 :成绩错误
*/
delimiter  $$
create procedure proc_12_if(in score int)
beginif score < 60 thenselect '不及格';elseif  score < 80thenselect '及格' ;elseif score >= 80 and score < 90then select '良好';elseif score >= 90 and score <= 100then select '优秀';elseselect '成绩错误';end if;
end $$
delimiter  ;
call proc_12_if(120)
-- 流程控制- case CASE是另一个条件判-- 语法一（类比java的switch）：
case case_valuewhen when_value then statement_list[when when_value then statement_list] ...[else statement_list]
end case
-- 语法二：
casewhen search_condition then statement_list[when search_condition then statement_list] ...[else statement_list]
end case
断的语句，类似于编程语言中的switch语法
-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begincase pay_typewhen  1 then select '微信支付' ;when  2 then select '支付宝支付' ;when  3 then select '银行卡支付';else select '其他方式支付';end case ;
end $$
delimiter ;call proc14_case(2);
call proc14_case(4);

流程控制-循坏

•循环是一段在程序中只出现一次,但可能会连续运行多次的代码。

•循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

循环分类：

Øwhile

Ørepeat

Øloop

循环控制：

Øleave类似于 break，跳出，结束当前所在的循环

Øiterate类似于 continue，继续，结束本次循环，继续下一次

-- 流程控制- 循坏 -while
【标签:】while 循环条件 do循环体;
end while【 标签】;
-- 创建测试表
create table user (uid int primary_key,username varchar ( 50 ),password varchar ( 50 )
);
-- 操作
-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begindeclare i int default 1;label:while i<=insertcount doinsert into user(uid,username,`password`) values(i,concat('user-',i),'123456');set i=i+1;end while label;
end $$
delimiter ;call proc16_while(10);-- 流程控制 -混怀 -repeat 格式
repeat 循环体;
until 条件表达式
end repeat [标签];
-- -------存储过程-循环控制-repeat 
use mysql7_procedure;
truncate table user;delimiter $$
create procedure proc18_repeat(in insertCount int)
begindeclare i int default 1;label:repeatinsert into user(uid, username, password) values(i,concat('user-',i),'123456');set i = i + 1;until  i  > insertCountend repeat label;select '循环结束';
end $$
delimiter ;call proc18_repeat(100);
流程控制-循坏 -loop 语法
 loop循环体;if 条件表达式 then leave [标签]; end if;
end loop;
-- -------存储过程-循环控制-loop
truncate table user;delimiter $$
create procedure proc19_loop(in insertCount int) 
begindeclare i int default 1;label:loopinsert into user(uid, username, password) values(i,concat('user-',i),'123456');set i = i + 1;if i > 5 then leave label;end if;end loop label;select '循环结束';
end $$
delimiter ;call proc19_loop(10);

存储过程-游标

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name
-- 操作
use mysql7_procedure;
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin-- 定义局部变量declare var_empno varchar(50);declare var_ename varchar(50);declare var_sal  decimal(7,2);-- 声明游标declare my_cursor cursor forselect empno , ename, sal from  dept a ,emp bwhere a.deptno = b.deptno and a.dname = in_dname;-- 打开游标open my_cursor;-- 通过游标获取每一行数据label:loopfetch my_cursor into var_empno, var_ename, var_sal;select var_empno, var_ename, var_sal;end loop label;-- 关闭游标close my_cursor;
end-- 调用存储过程call proc20_cursor('销售部');

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！