Mysql高级-视图&存储过程&函数

视图

什么是视图

视图是基于查询的虚拟表。通俗的理解，视图就是一条select语句执行后返回的结果集。

select语句所查询的表称为视图的基表，而查询的结果集被称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基表数据发生改变，视图的数据也会跟着改变。

为什么使用视图

使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对试图进行查询，从而简化复杂的查询语句。

定义视图

create view 视图名 as select 列1，列2··· from 表(查询语句);

使用视图

select * from 视图名

删除视图

drop view 视图名

存储过程

概述

如果实现用户某些需求需要一组复杂SQL语句时，可以将这组复杂SQL语句集写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程简化了引用开发人员的很多工作，减少了数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程类似Java中的方法，需要先定义，使用时调用。存储过程可以定义参数，参数分为IN、OUT、INOUT三种类型。IN类型的参数表示接受调用者传入的数据，OUT类型的参数表示向调用者返回数据，INOUT类型的参数表示既可以接受调用者传入的数据，也可以向调用者返回数据。

Mysql存储过程

创建存储过程的语法格式

create procedure ([int 变量名 类型,out 参数2,...])
begin
	[declare 变量名 类型 [default 值];]
	存储过程语句块;
end;

语法解析：

1. 存储过程的参数分为IN、OUT、INOUT三种。
2. IN类型的参数表示接受调用者传入的数据。
3. OUT类型的参数表示向调用者返回数据。
4. INOUT类型的参数表示既可以接受调用者传入的数据，也可以向调用者返回数据。
5. 存储过程中的语句必须包含在begin和end之间。
6. declare中用来声明变量，变量默认赋值使用default，语句块中改变变量值，使用 set 变量=值。

存储过程使用

• 定义一个存储过程

create procedure test1()
begin
	declare name varchar(20) default 'haha';
	set name = 'balun';
	select name;
end;
-- 调用
call test1()

结果

• 定义一个有参数的存储过程

create procedure findUserCount(IN u_age INT,OUT u_count INT)
begin
	select count(*) into u_count from user where age = u_age;
	select u_count;
end;
-- 测试
call findUserCount(12,@u_count);

结果

• 流程控制语句 if else

create procedure test2(IN tday INT)
begin
	if tday = 0 then
		select "星期天";
	elseif tday = 1 then
		select "星期一";
	elseif tday = 2 then
		select "星期二";
	···
	else
		select "无效日期";
	end if;
end;
-- 测试
call test2(2);

结果

• case when

  create procedure test3(IN tday INT)
  begin
  	case when tday = 0 then
  		select "星期天";
  	else
  		select "星期一";
  	end case;
  end;
  -- 测试
  call test3(0);
  

  结果

  

• 循环

create procedure test4()
begin
	declare num INT default 0;
	-- 循环开始
	addnum:LOOP
		set num = num + 1; -- 循环语句
	-- 循环结束条件
	if num = 10 then
    	leave addnum;
    end if;
    end LOOP; -- 循环结束
    select num;
 end;
 -- 测试
 call test4();

结果

• 使用存储过程插入信息

create procedure saveUser(IN tname varchar(20),IN tage INT,OUT tmark INT)
begin
	declare count INT default 0;
		select count(*) into count from user where name = tname;
		if count = 0 then
			insert into user(name,age) values(tname,tage);
			set tmark = 0;
		else
			set tmark = 1;
		end if;
		select tmark;
end;
-- 测试
call saveUser('小明',14,@tmark);
call saveUser("小兰",21,@tmark);

结果

函数

函数语法

create function 函数名([参数列表])returns 数据类型
begin
	declare 变量;
		sql语句;
	return 值;
end;

注意：

1. 参数列表包含两个部分：参数名 参数类型
2. 函数体：肯定会有return语句，如果没有会报错
3. 函数体中仅有一句话，则可以省略begin end
4. 使用delimiter语句设置结束标记

-- 设置函数可以没有参数
set global log_bin_trust_function_creators=true;
-- 删除函数
drop function 函数名

• 不带参数

create function test5() returns INT
begin
	declare tnum INT;
	select count(*) into tnum from user;
	return tnum;
end;
-- 测试
select test5();

结果

• 带参数

create function findUserNameById(uid INT) returns varchar(10)
begin
	declare uname varchar(20);
	select name into uname from user where id = uid;
	return uname;
end;
-- 测试
select findUserNameById(2);

• 有参数，有判断

create function checkUserAge(uage INT) returns varchar(20)
begin
	if uage < 18 then
		return '未成年';
	else
		return '已成年';
	end if;
end;
-- 测试
select name,checkUserAge(age) from user;

结果