视图:
- 视图:是一种虚拟的表。视图中的数据在数据库中并不实际存在,行和列的数据来自自定义视图中查询使用的表,并且是在使用视图时动态生成的。
- 创建视图:create or replace view viewname as select 语句 [with[cascaded|local|check option]]
- 例如:create or REPLACE view View_GetUsers as select * from tb_users;
- 查询视图:
- 查看创建视图语句:show create view View_GetUsers
- 查看视图数据:select * from View_GetUsers
- 修改
- create or replace view viewname as select ...... [with[cascaded|local|check option]]
- alter view viewname as select ...... [with[cascaded|local|check option]]
- 删除:drop view if exists viewname
注:[with[cascaded|local|check option]],为可选项.................
存储过程:
特点:封装、复用,可以接受参数,也可以返回数据,减少网络交互,效率提升
语法:
- 创建
- create procudure 存储过程名称(参数列表)
- begin
- -----------sql
- end;
- 调用
- call 存储过程名称(参数列表)
-- --------------------------------存储过程-------------------------------- -- 创建 create PROCEDURE proctest() begin select count(*) from tb_users; end; -- 调用 call proctest(); -- 查看 -- 查询指定数据库的存储过程及状态信息 select * from information_schema.ROUTINES where ROUTINE_SCHEMA='proctest'; -- 查询某个存储过程的定义 show create PROCEDURE proctest; -- 查看结果如下 CREATE DEFINER=`root`@`localhost` PROCEDURE `proctest`() begin select count(*) from tb_users; end
-- 删除
drop PROCEDURE if EXISTS proctest;
- 变量 —— 默认session
- 系统变量:是Mysql服务器提供,不是用户定义的,属于服务器层面。分为全局变量(golbal)、回话变量(session)
- 查看系统变量
- show [session|global] variables;——查询所有系统变量
- show [session|global] variables like '......';——可以通过like模糊匹配方式查找变量
- select @@[session|global] 系统变量名; ——查看指定变量的值
- 设置系统变量
- set [session|global] 系统变量名 = 值;
- set @@ [session|global] 系统变量名 = 值;
-
-- -- -- -- -- -- -- -- -- -- -- -- -- 变量-- -- -- -- -- -- -- -- -- -- -- -- show session variables; show session variables like 'auto%'; show global variables; show global variables like 'auto%'; select @@session.autocommit; select @@global.autocommit; set SESSION autocommit =0; set SESSION autocommit =1;
- 查看系统变量
- 用户自定义变量
- 定义:是用户根据需要自己定义的变量,用户变量不用提前生命,在用的时候直接用 @变量名称就可以使用。其作用域为当前连接。
- 赋值:
- set @var_name=expr,.......
- set @var_name:=expr,.......
- select @var_name := exper.....
- select 字段名 into @var_name from 表名
- 使用:select @var_name;
- 示例如下所示:
-
-- 用户自定义变量 -- 赋值 set @myname='sunkun'; set @myage:=18; set @mygender:='boy',@myhobby:='C#'; select @mynickname:='kun'; select count(*) into @mycunt from tb_users; -- 使用 select @myname,@myage,@mygender,@myhobby,@mynickname,@mycunt;
注:用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL。
- 局部变量
- 定义:是根据需要定义在局部生效的变量,访问之前,需要declare声明。可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的begin..............end块。
- 声明:declare 变量名 变量类型 [default...]; 变量类型就是数据库字段类型:int\bigint\char\varchar\date\time等。
- 赋值:set 变量名 = 值、set 变量名 := 值、select 字段名 into 变量名 from 表名....
- 示例如下所示:
-
-- 创建 create PROCEDURE proctest2() begin DECLARE stucount int DEFAULT 0; DECLARE stuname varchar(50); set stucount =18,stuname='sunwugang'; select stuname,stucount; end; -- 调用 call proctest2();
- 系统变量:是Mysql服务器提供,不是用户定义的,属于服务器层面。分为全局变量(golbal)、回话变量(session)
- IF
语法: if 条件 then ...... ELSEIF 条件 THEN ...... -- 可选 else ...... -- 可选 end if
-
- 示例如下所示:
create PROCEDURE proc3() BEGIN DECLARE age int DEFAULT 22; DECLARE temp VARCHAR(20); if age>=18 and age<=30 then set temp:='初级'; ELSEIF age >=30 and age<=40 THEN set temp='中级'; else set temp :='高级'; end if; select temp; END; call proc3(); -- return 初级
- 参数
-
-
-
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 输入输出参数示例 drop PROCEDURE if EXISTS proc4; create PROCEDURE proc4(in age int,out temp varchar(20)) BEGIN -- DECLARE age int DEFAULT 22; -- DECLARE temp VARCHAR(20); if age<=25 then set temp:='初级'; ELSEIF age<=40 THEN set temp='中级'; else set temp :='高级'; end if; select temp; END; call proc4(28,@temp); select @temp; drop PROCEDURE if EXISTS proc5; create PROCEDURE proc5(in age int,in name varchar(50),out temp varchar(20)) BEGIN -- DECLARE age int DEFAULT 22; -- DECLARE temp VARCHAR(20); if age>=18 and age<=30 then set temp:=CONCAT(name,',初级'); ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级'); else set temp := CONCAT(name,',高级'); end if; END; call proc5(28,'sunwugang',@temp); select @temp; drop PROCEDURE if EXISTS proc6; create PROCEDURE proc6(in name varchar(50),inout age int,out temp varchar(20)) BEGIN -- DECLARE age int DEFAULT 22; -- DECLARE temp VARCHAR(20); set age:= age+10; if age>=18 and age<=30 then set temp:=CONCAT(name,',初级'); ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级'); else set temp := CONCAT(name,',高级'); end if; END; set @age=22; call proc6('sunwugang',@age,@temp); select @age,@temp;
-
- case
-
-- case when else end /* ( case 字段 when xxx then xxx when xxx then xxx else xxx end )as 重命名字段 */ select t.*, (case content when '001' then '一级戒备' when '002' then '二级戒备' else '三级戒备' end)戒备戒备 from remarkinfo t ORDER BY t.content;
-
-
drop PROCEDURE if EXISTS proc7; create PROCEDURE proc7(in month int) BEGIN DECLARE result VARCHAR(100); CASE when month>=1 and month<=3 then set result:='第一季度'; when month>=4 and month<=6 then set result:='第二季度'; when month>=7 and month<=9 then set result:='第三季度'; when month>=10 and month<=12 then set result:='第四季度'; else set result:='无效参数'; end case; select CONCAT('输入的月份为:',month,',所属季度为:',result); END; call proc7(8);
-
- 循环
- while:满足条件执行循环
-
drop PROCEDURE if EXISTS proc8; create PROCEDURE proc8(in num int,out count int) BEGIN DECLARE result int DEFAULT 0; while num >0 do set result := result + num; set num := num -1; end WHILE; set count:=result; END; call proc8(4,@count); select @count;
-
- repeat:当满足条件的时候退出循环
-
-
-- repeat 循环 drop PROCEDURE if EXISTS proc9; create PROCEDURE proc9(in num int,out count int) BEGIN DECLARE result int DEFAULT 0; repeat set result := result + num; set num := num -1; until num<=0 end repeat; set count:=result; END; call proc9(4,@count); select @count;
-
- loop
- 实现简单的循环,如果不在sql逻辑中增加退出循环的条件,可以用其来实现简单的死循环。Loop可以配合以下两个语句使用:
- LEAVE:配合循环使用,退出循环;
- ITERATE:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环;
- 示例如下所示:
-
-- LOOP 计算从1累加到N的值(只计算偶数),N为入参 drop PROCEDURE if EXISTS proc10; create PROCEDURE proc10(in num int,out count int) BEGIN DECLARE total int DEFAULT 0; sum:LOOP if num <=0 then leave sum; -- leave 退出循环 end if; set total := total + num; set num := num -1; end LOOP sum; set count:=total; END; call proc10(4,@count); select @count; /* leave 退出循环 iterate 类似continue */ -- LOOP 计算从1累加到N的值,N为入参 drop PROCEDURE if EXISTS proc11; create PROCEDURE proc11(in num int,out count int) BEGIN DECLARE total int DEFAULT 0; sum:LOOP if num <=0 then leave sum; -- end if; if num %2 =1 then set num := num -1; iterate sum; -- iterate 类似continue end if; set total := total + num; set num := num -1; end LOOP sum; set count:=total; END; call proc11(4,@count); select @count;
- cursor游标
- 游标,是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。
- 游标的使用包括:游标的声明、OPEN、FETCH、CLOSE,其语法如下所示:
- 声明游标:declare 游标名称 cursor for 查询语句;
- 打开游标:open 游标名称;
- 获取游标记录:fetch 游标名称 into 变量1,变量2,变量N;
- 关闭游标:close 游标名称;
- 条件处理程序
- 条件处理程序(handler)可用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤,语法如下:
-
-
- 示例如下所示:实现将age =18的用户信息插入 tb_users2
- select * from tb_users;
-
-
-- -------------------------------------游标------------------------------------- -- 存储过程,实现age =18的 数据插入 tb_users2 drop PROCEDURE if EXISTS proc12; create PROCEDURE proc12(in inage int) BEGIN declare uname varchar(100); declare uage int; declare ustatus char(1); declare ugender char(1); declare uphone varchar(11); -- 1.声明游标 DECLARE user_cursor cursor for select NAME,age,status,gender,phone from tb_users where age = inage; -- 方式一条件处理程序,当SQLSTATE为 02000时,退出游标 为状态码 -- DECLARE exit handler for SQLSTATE '02000' CLOSE user_cursor; -- 方式二 DECLARE exit handler for not found CLOSE user_cursor; -- 创建表,可将建表部分至于存储过程之外 DROP table if EXISTS tb_users2; create table if not exists tb_users2 ( id int auto_increment PRIMARY key COMMENT '主键', name VARCHAR(100) not null unique COMMENT '姓名', age INT CHECK (age >0 && a<=120), status char(1) DEFAULT '1' COMMENT '状态', gender char(1) COMMENT'性别', phone VARCHAR(11) COMMENT '手机号' )COMMENT'用户表'; -- 2.打开游标 open user_cursor; while true do -- 3.循环操作数据 fetch user_cursor into uname,uage,ustatus,ugender,uphone; insert into tb_users2 values(null,uname,uage,ustatus,ugender,uphone); end while; -- 4.关闭游标 close user_cursor; END; call proc12(18); SELECT * from tb_users2;
- while:满足条件执行循环
存储函数:
- 存储函数是有返回值的存储过程,存储函数的参数只能是 IN 类型的。其语法如下所示
-
- 示例如下所示:
-
-- -----------------------------存储函数----------------------------- drop function if EXISTS fun1; create function fun1(num int) returns int DETERMINISTIC BEGIN declare total int DEFAULT 0; -- DECLARE stucount int DEFAULT 0; while num>0 do set total:=total + num; set num:=num -1; end while; return total; END; -- 调用存储函数 select fun1(4);
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!