MySQL之其他部分

1.视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

1.1创建视图

#语法：create [or replace] 视图名字 as 查询语句

#示例：
CREATE OR REPLACE VIEW myv1
AS
SELECT AVG(salary) AS ag_sal, job_id
FROM employees
GROUP BY job_id;

1.2视图的修改

#语法：alter view 视图名 as 查询语句

#示例：
ALTER VIEW myv1
AS
SELECT SUM(salary) AS sum_sal, job_id
FROM employees
GROUP BY job_id;

1.3查看视图

1.desc 视图名：查看视图结构
2.show create view 视图名：查看创建视图的sql语句

1.4删除视图

drop view 视图名字1, 视图名字2...

2.变量

2.1分类

• 系统变量

1. 全局变量：针对所有会话（连接）有效，但是不能跨重启
2. 会话变量：针对于当前会话（连接）有效

• 自定义变量

1. 用户变量：针对于当前会话（连接）有效，作用于等同于会话变量
2. 局部变量：仅仅在定义它的begin end块中有效

2.1.1系统变量

变量由系统定义，不是用户定义，属于服务器层面，全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写的话，默认就是会话级别的变量

（1）查看系统变量：

• show global|[session] variables [like '%xxx%']  查看系统变量值
• select @@global|[session] .系统变量名

（2）为系统变量赋值

• set @@global|[session].系统变量名 = 值

2.1.2自定义变量

自定义变量由用户定义，而不是系统提供，使用步骤：第一，声明变量；第二，为变量赋值；第三，程序使用变量。

• 用户变量

（1）声明并初始化：
        set @变量名 = 值;
        set @变量名 := 值
        select @变量名 := 值

（2）赋值

        方法一：与初始化形式相同

        方法二：select 字段名 into @变量名 from 表名

• 局部变量：

（1）声明：declare 变量名 类型 [default 默认值]

（2）赋值：

        方式一：

         set 变量名 = 值;
         set 变量名 := 值
         select 变量名 := 值

        方式二：

        select 字段名 into 变量名 from 表

3.存储过程

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

#创建存储过程的语法：
create procedure 数据库名.存储过程名([in|out|inout 参数1 参数类型,...])
begin
    存储过程体
end

• 参数列表包括：参数模式 参数名 参数类型
• 参数模式

1. in：该参数可以用作输入，调用存储过程的时候传入值
2. out：该参数可以用于输出，也就是该参数可以作为返回值（可以有多个out参数）
3. inout：该参数即可以用于输出，也可以用于输入

• 调用存储过程：call 存储过程名(实参列表)
• 删除存储过程：drop procedure 数据库.存储过程名（一次只能删除一个，不能一次删除多个）

/*
delimiter 结束标志符号
*/
#案例1：不带参数的存储过程 插入到admin表中的三条记录
delimiter $
CREATE PROCEDURE girls.myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('join', '000000'),('lily', '000000'),
	('rose', '000000');
END $

CALL girls.myp1()$

#案例2：创建带in模式和out模式参数的案例
#实现输入女神名查询对应男神信息
delimiter $
CREATE PROCEDURE girls.myp2(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	JOIN beauty b
	ON b.boyfriend_id = bo.id
	WHERE b.name = beautyName;
END $

SET @boyName := ''$
CALL girls.myp2('唐艺昕', @boyName)$
SELECT @boyName$

#案例3：创建带inout模式的存储过程
#传入a和b两个值，最终a和b翻2倍返回
CREATE PROCEDURE girls.myp3(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $

SET @a := 10$
SET @b := 8$
CALL girls.myp3(@a, @b)$
SELECT @a as A, @b as B$

4.函数

在sql中，函数和存储过程类似，但也有区别，区别如下：

1. 参数不同：存储过程分为in out inout三种参数，函数只有in参数
2. 返回结果不一样：存储过程可以有多个返回结果也可以没有返回结果，而函数有且只有一个返回结果

• 创建、调用和删除函数

#创建：
create function 数据库.函数名(参数列表) returns 返回值
#调用
select 数据库.函数名(实参...)
#删除
drop function 数据库.函数名

#案例：求和
CREATE FUNCTION girls.test(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE sum FLOAT DEFAULT 0;
	SET sum = num1 + num2;
	RETURN sum;
END $

SELECT girls.test(10, 20)$

5.流程控制语句

5.1分支语句if

#if的两种用法
（1）if(条件, 值1, 值2)：如果条件成立，则返回值1，如果条件不成立，则返回值2
（2）
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n
end if $

#案例：
CREATE FUNCTION girls.test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score >= 90 THEN SET ch = 'A';
	ELSEIF score >= 80 THEN SET ch = 'B';
	ELSEIF score >= 70 THEN SET ch = 'C';
	ELSEIF score >= 60 THEN SET ch = 'D';
	ELSE SET ch = 'E';
	END IF;
	RETURN ch;
END $

SELECT girls.test_if(90)$

5.2循环结构

在mysql中循环分为while、loop、repeat循环

#while循环语法：
[标签：] while 循环条件 do
    循环体
end while [标签]

#loop语法：
[标签:] loop
    循环体
end loop [标签]

#repeat语法：
[标签:] repeat 
    循环体
until 结束循环条件
end repeat [标签名]

循环控制：
（1）iterate：结束本次循环，继续下一次循环（类似于java等高级语言的continue）
（2）leave：结束当前所有循环（类似于java等高级语言的break）

#案例：计算1-100之间的所有偶数的和
CREATE FUNCTION func_sum() RETURNS INT
BEGIN
	DECLARE sum INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=100 DO
		IF MOD(i, 2) = 0 THEN SET sum = sum + i;
		END IF;
		SET i = i+1;
		END WHILE a;
	RETURN sum;
END $

SELECT func_sum()$