1，视图

  • 1.1，什么是视图
  • 1.2，视图的使用
  • 1.3，视图和表的对比
  • 1.4，视图和表的对比

2，变量

  • 2.1，系统变量
  • 2.2，自定义变量

3，存储过程

  • 3.1，创建存储过程
  • 3.2，存储过程的使用
  • 3.3，查看存储过程
  • 3.4，删除存储过程

4，函数

  • 4.1，创建函数
  • 4.2，调用函数
  • 4.3，查看函数
  • 4.4，删除函数

5，流程控制结构

  • 5.1，分支结构
  • 5.2，循环结构
视图


1，什么是视图？


  • 视图： MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
  • 应用场景：
    多个地方用到同样的查询结果
    该查询结果使用的sql语句较复杂
  • 视图的好处：

      ①，重用sql语句

      ②，简化复杂的sql操作，不必知道它的查询细节

      ③，保护数据，提高安全性


2，视图的使用


  • 创建视图的语法：
create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]
  • 修改视图的语法：
alter view view_name
As select_statement
[with|cascaded|local|check option]
  • 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的

      ①，包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

      ②，常量视图

      ③，where子句的子查询引用了from子句中的表

      ④， join

      ⑤， 用到了不能更新的视图

  • 删除视图的语法（用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限）
drop view [if exists] view_name,view_name …[restrict|cascade]
  • 查看视图的语法
show tables;
  • 如果需要查询某个视图的定义，可以使用show create view

3，视图和表的对比


视图和表的对比

创建语法的关键字

是否实际占用物理空间使用

视图

create view

只是保存了sql逻辑 增删改查，一般不能增删改

表

create table

保存了数据·增删改查

4，delete事务和truncate事务的使用


（案例）：

set autocommit = 0;
start transaction;
delete from account;
rollback;
set autocommit = 0;
start transaction;
truncate table account;
rollback;
  • truncate事务不支持回滚
变量


1，系统变量


     ①， 全局变量

     ②，会话变量

  • 说明：变量由系统提供，不是用户自定义，属于服务器层面使用的语法：
  • 注意：如果是全局级别（全局变量），则需要加 global，如果是会话级别，则需要加 session，如果不写，则默认session
  • 服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效
  • 查看所有的系统变量
show global variables;
  • 查看满足条件的部分系统变量
show global variables like '%char%';
  • 查看指定的某个系统变量的值
select @@global .系统变量;
  • 为某个系统变量赋值
方式一：
set global 系统变量名 = 值;

方式二：
set @@global .系统变量名 = 值;
  • 会话变量
    作用域：仅仅针对于当前会话（连接）有效
    服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
  • 查看所有的会话变量
show variables;
show session variables;
  • 查看部分的会话变量
show session variables like '%char%';
  • 查看指定的某个会话变量
select @@session .tx_isolation;
  • 为某个会话变量赋值
方式一：
set @@会话变量名 = 值;

方式二：
set session 会话变量名 = 值;


2，自定义变量


  • 说明：变量是用户自定义的，不是由系统的
  • 使用步骤：声明 -> 赋值 -> 使用（查看，比较，运算等）

①，用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域

位置：begin end里面，也可以放在外面

赋值的操作符：= 或 :=

  • 声明并初始化
set @用户变量名 = 值;
set @用户变量名 := 值;
select @用户变量名 := 值;
  • 赋值（更新用户变量的值）
方式一：通过set或select
参照上述初始化，如：
set @name = 'Tommey周';
set @name = 25334;


方式二：通过select into
select 字段 into 变量名 from 表;
select count(*) into @count from emp;
  • 使用（查看用户变量的值）
select @用户变量名;
select @count

②，局部变量

作用域：仅仅再定义它的begin end 中有效

位置：只能放在begin end中，而且只能放在第一句

  • 声明
declare 变量名 类型 [default 值];
  • 赋值或更新
方式一：
set 变量名 = 值;
set 变量名 := 值;
select @变量名 := 值;

方式二：
select 字段 into 变量名 from 表;
  • 使用
select 变量名
  • 对比用户变量和局部变量

对比

作用域

定义和使用的位置

语法

用户变量

当前会话

会话中的任何地方

必须加@符号，不用限定类型

局部变量

begin end中

只能在begin end 中，且为第一句话

一般不用加@符号，需要限定类型

  • （案例）用户变量的使用
set @a = 'Tommey';
set @b = '周';
set @sum = CONCAT(@a,@b) ;
select @sum;
存储过程
  • 说明：都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字
  • 好处：
    1、提高重用性
    2、sql语句简单
    3、减少了和数据库服务器连接的次数，提高了效率

1，创建存储过程


  • 创建语法
create procedure 存储过程名（参数列表）
begin
	存储过程体（一组合法的SQL语句）
end
  • 注意：
    参数列表包含三部分：参数模式，参数名，参数类型（ 举例：in username varchar(20) ）
  • 参数模式：
    in：该参数可以作为输入，也就是该参数需要调用方传入值
    out：该参数可以作为输出，也就是该参数可以作为返回值
    inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要参入值，又可以返回值
  • 如果存储过程体仅仅只有一句话，begin end 可以省略
  • 存储过程体中的每条SQL语句的结尾要求必须加分号。
  • 存储过程的结尾可以使用delimiter 重新设置
  • 语法：
    delimiter 结束标记
    案例：
delimiter $
  • 调用语法：
call 存储过程名（实参列表）

（案例）：

delimiter $
create procedure test01()
begin
	insert into admin(username,pass) values("Tommey周",'0000');
end $

call test01()$


2，存储过程的使用


  • （案例）创建带in模式参数的存储过程，实现用户是否登录成功
create procedure test02(in username varchar(20),in pass varchar(20))
begin
	declare result varchar(20) default 0;
	select count(1) from admin a where a.username =  username and a.pass = pass;
	select if(result>0,'成功','失败');
end $

call test02("Tommey周",'0000')$
  • （案例）创建带out模式的存储过程，根据员工编号，返回员工姓名和部门编号
create procedure test03(in empno varchar(20),out username varchar(20),out deptId int)
begin

	select a.username,a.dept_id into username,deptId 
	from emp a where a.empno = empno;
end $

call test03('2466',@aName,@aId)$
select @aName,@aId$
  • （案例）创建带inout模式的存储过程，传入a和b两个值，最终a和b都翻倍并返回
create procedure test04(inout a int,inout b int)
begin
	select a = a*2;
	select b = b*2;
end $

set @m = 10$
set @n = 20$
select test04(@m,@n)$


3，查看存储过程 


show create procedure 存储过程名;


4，存储过程的使用 


drop procedure 存储过程名;
函数
  • 函数的含义：一组预先编译好的SQl语句的集合，理解成批处理语句
  • 函数和存储过程的区别：
    存储过程：可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
    函数：有且仅有1个返回，适合做处理数据后返回一个结果

1，创建函数


  • 创建语法
create function 函数名（参数列表） returns 返回类型
begin
	函数体
end
  • 注意：
    参数列表包含两部分：参数名，参数类型
    函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议
    函数体中仅有一句话，则可以省略begin end
    使用delimiter语句设置结束标记

2，调用函数


  • 调用语法
select 函数名（参数列表）
  • （案例）根据部门名，返回该部门的平均工资
create function test05(deptName varchar(20)) return double
begin 
	declare sal double;
	select avg(salary) into sal
	from emp e
	join dept d on e.deptId = d.deptId
	where d.deptname = deptName;
	return sal;
end $
select test05('软件开发部')$


3，查看函数 


show create function 函数名


4，删除函数 


drop function 函数名
流程控制结构
  • 顺序结构：程序从上往下依次执行
  • 分支结构：程序从两条或多条路径中选择一条去执行
  • 循环结构：程序再满足一定条件的基础上，重复执行一段代码

1，分支结构


①，if函数

功能：实现简单双分支
语法：

if(条件，值1，值2)

②，case结构

功能：实现多分支

  • 语法1（类似于java中的switch语句，一般用于实现等值判断）：
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2；
..
else 语句n;
end [case];
  • 语法2（类似于Java中的多重if语句，一般用于实现区间判断）：
case 
when 条件1 then 语句1;
when 条件2 then 语句2；
..
else 语句n;
end [case];

位置：可以放在任何位置，如果放在begin end 外面，作为表达式结合着其他语句使用；如果放在begin end 里面，一般作为独立的语句使用

  • 如果我when中的值满足或条件成立，则执行对应then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值
  • else可以省略，如果else省略了，并且所有when条件都不满足，则返回null

执行顺序：如果条件成立，则if函数返回值1，否则返回值2

位置：可以作为表达式放在任何位置

  • （案例）创建存储过程，根据传入的工资来显示等级，比如传入的工资15000-20000显示A,10000-15000显示B，5000-10000显示C，0-5000显示D
create procedure test_case(in sal double)
begin
	case 
	when sal >= 15000 and sal <= 20000 then select 'A';
	when sal >= 10000 then select 'B';
	when sal >= 5000 then select 'C';
	else select 'D';
	end case;
end $

call test_case(8888)$

③，if结构

功能：实现多重分支

语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句 语句n]
end if;
  • 应用在begin end中
  • （案例）根据传入的工资来显示等级，比如传入的工资15000-20000显示A,10000-15000显示B，5000-10000显示C，0-5000显示D
create function test_if(sal double) return char
begin
	if sal >= 15000 and sal <= 20000 then return 'A';
	elseif sal >= 10000 then return 'B';
	elseif sal >= 5000 then return 'C';
	else return 'D';
	end if;
end $

call test_if(8888)$


2，循环结构


  • 分类：while,loop,repeat
  • 位置：只能放在begin end中
  • 特点：都能实现循环结构
  • 对比：

①，这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称

②，loop 一般用于实现简单的死循环，while 先判断后执行，repeat 先执行后判断，无条件至少执行一次

  • 循环控制：
    iterate 类似于 continue ，继续，结束本次循环，继续下一次
    leave 类似于break，跳出，结束当前所在的循环
  • while(先判断后执行)

语法：

【名称:】while 循环条件 do
		循环体
end while 【名称】;
  • loop(没有条件的死循环)
语法：
【名称：】loop
		循环体
end loop 【名称】;
  • repeat(先执行后判断)

语法：

【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;
  • （案例）批量插入，根据次数插入到admin表中多条记录，只插入偶数次
create procedure test_while(in insertCount int)
begin 
	declare i int default 1;
	a:while i <= inserCount do
		set i = i+1;
		if mod(i,2) != 0 then iterate a;
		end if;
		insert into admin(username,pass) values(concat('Tommey周',i),'0000');
	end while a;
end $

call test_while(10)$