MySQL基础——高级功能简介

五、视图

1、简单介绍

视图其实就是虚拟表，和普通表一样使用，MySQL5.1版本出现的新特性，是通过表动态生成的数据。

舞蹈班和普通班：其实没有舞蹈班，只有领导来了以后，才临时搭建的舞蹈班，领导走了以后自动解散，普通班不是这样的。

示例（没用视图之前）：

#查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuname s
INNER JOIN major m
ON s.`majorid` = m.`id`
WHERE s.`stuname` LIKE '张%';
复制代码

示例（有了视图）：

#还是得先查询一遍，封装成视图v1
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuname s
INNER JOIN major m
ON s.`majorid` = m.`id`
WHERE s.`stuname` LIKE '张%';

#再次查询v1即可
SELECT * FROM v1 WHERE stuname LIKE '张%';
复制代码

2、视图的创建

#语法
CREATE VIEW 视图名
AS
要查询的语句;
复制代码

视图的好处：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

3、视图的修改

#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
复制代码

#方式二：
ALTER VIEW 视图名
AS
查询语句;
复制代码

4、视图的删除

DROP VIEW 视图名1,视图名2;
复制代码

5、视图的查看

DESC 视图名;

#这个也行，不过不推荐
SHOW CREATE VIEW 视图名;
复制代码

6、视图的更新

用于更改视图中的数据。其实和表的更新一样。

INSERT INTO 视图名 (...) VALUES (...);
#其他也一样
复制代码

不过有特殊情况：

• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all;
• 常量视图；
• Select中包含子查询；
• join；
• from一个不能更新的视图；
• where子句的子查询引用了from子句中的表；

视图和表的区别

• 一个是create view，一个是create table
• 是否实际占用物理空间？视图只占用了一小部分，表占用了物理空间
• 视图一般不能增删改（有限制），表可以增删改查

六、变量

1、变量的分类

• 系统变量：
  • 全局变量
  • 会话变量
• 自定义变量：
  • 用户变量
  • 局部变量

2、系统变量

由系统提供，不是用户定义，属于服务器层面的。

#查看所有的系统变量，什么都不写默认是SESSION，下面一样
SHOW GLOBAL|【SESSION】 VARIABLES;

#查看部分系统变量
SHOW GLOBAL|【SESSION】 VARIABLES LIKE 查询条件;

#查看某个系统变量
SELECT @@GLOBAL|【SESSION】 系统变量名;

#为某个系统变量赋值
#方式一
SET GLOBAL|【SESSION】系统变量名 = 值;
#方式二
SET @@GLOBAL|【SESSION】.系统变量名 = 值;
复制代码

服务器每次启动都会为全局变量赋初始值，针对于所有的会话都有效，但不能跨重启，如果想真正重启也修改，就需要修改配置文件了。

会话变量的作用域针对于当前会话（连接），换一个连接就不一样了。

3、自定义变量

变量是自己定义的。

3.1 用户变量

作用域：针对于当前会话有效的，换一个连接就无效了，和Session的作用域一样。

使用在任何地方，begin end里面或外面都行。

#声明并初始化 用=或:=赋值
SET @用户变量名=值;
#另一种方式
SET @用户变量名:=值;
#另一种方式
SELECT @用户变量名:=值;


#更新用户变量的值
#方式一：同上，既是声明又是更新赋值
SET @用户变量名=值;
#另一种方式
SET @用户变量名:=值;
#另一种方式
SELECT @用户变量名:=值;
#方式二：SELECT INTO来赋值
SELECT 字段 INTO @变量名 FROM 表;

#使用
SELECT @用户变量名;
复制代码

3.2 局部变量

作用域：仅仅在局部有效，也就是begin end里有效。

#声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

#赋值
#方式一
SET 局部变量名=值;
#另一种方式
SET 局部变量名:=值;
#另一种方式
SELECT @局部变量名:=值;

#方式二：SELECT INTO来赋值
SELECT 字段 INTO 变量名 FROM 表;

#使用
SELECT 局部变量名;
复制代码

七、存储过程和函数

1、简单介绍

存储过程和函数类似Java的方法。

存储过程和函数定义：事先经过编译并存储在数据库中的一段sql语句的集合。

好处：

• 简化应用开发人员的很多工作
• 减少数据在数据库和应用服务器之间的传输
• 提高了数据处理的效率

2、存储过程

2.1 创建存储过程

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组SQL语句）;
END;
复制代码

注意：

• 参数列表包含三部分：参数模式、参数名、参数类型

#举例
IN stuname VARCHAR(20);
复制代码

• 参数模式：
  • IN：该参数可以作为输入，也就是需要调用方传入值
  • OUT：该参数可以作为输出，也就是该参数可以作为返回值
  • INOUT：该参数既可以作为输入，又可以作为输出，既需要调用方传入值，又可以作为返回值
• 如果存储过程体仅仅只有一句话，BEGIN END可以省略，存储过程体种的每条sql语句都需要加分号结尾，存储过程的结尾可以使用delimiter重新设置

#语法
DELIMITER 结束标记

#案例
DELIMITER $
复制代码

2.2 调用存储过程

#语法
CALL 存储过程名(实参列表);
复制代码

2.3 案例

有点难，案例记录一下！！

• 普通类型参数的存储过程

#往admin表里插入五条记录
#写存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) VALUES 
		('zhangsan1','001'),
		('zhangsan2','002'),
		('zhangsan3','003'),
		('zhangsan4','004'),
		('zhangsan5','005');
END $

#使用存储过程
CALL myp1()$
复制代码

• 带in模式参数的存储过程

#根据女神名，查询对应的男神信息
#写存储过程
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#使用存储过程
CALL myp2('柳岩')$
复制代码

#用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))
BEGIN
	DECLARE result INT(20) DEFAULT '';#声明并初始化
	
	SELECT count(*) INTO result #赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = `password`;
	
	SELECT IF(result>0,'成功','失败)';#判断是否成功
END $

#调用
CALL myp3('张三','0001')$
复制代码

• 带out模式参数的存储过程

#根据女神名，查询对应的男神信息
#写存储过程
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN 
	SELECT bo.boyName INTO boyName
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#使用存储过程
SET @bName$
CALL myp5('柳岩',@bName)$
SELECT @bName$
复制代码

• 带inout模式参数的存储过程

#传入a和b，最终a和b都翻倍并返回
#写存储过程
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a*2;
	SET b = b*2;
END $

#使用存储过程
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$

#查询结果
SELECT @m，@n $
复制代码

2.4 删除存储过程

不能进行修改存储过程里面的内容，如果想修改，那就删掉原来的，再创建新的。

#语法
DROP PROCEDURE 存储过程名;
#一次只能删除一个
复制代码

2.5 查看存储过程

#语法
SHOW CREATE PROCEDURE 存储过程名;
复制代码

3、函数

存储过程和函数的区别：

• 函数只能有一个返回，存储过程可以又任意个，0个也行，多个也行
• 增删改比较适合存储过程，查询一个值比较适合函数

3.1 创建函数

#创建语法
CREATE FUNCTION 函数名(函数名) RETURNS 返回类型
BEGIN
	函数体
END
复制代码

参数列表包含两部分：参数名、参数类型；函数更接近于Java中的方法。

函数体：肯定有return语句，如果没有会报错，如果return语句没放在函数体最后也不报错，但是没任何意义。

如果存储过程体仅仅只有一句话，BEGIN END可以省略，存储过程的结尾可以使用delimiter。

3.2 调用语法

SELECT 函数名(参数列表)
复制代码

3.3 案例

• 无参有返回的

#返回公司的员工个数
#创建函数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
END $

#调用函数
SELECT myf1()$
复制代码

• 有参有返回的

#根据员工名返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义一个用户变量
	SELECT salary INTO @sal#赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

#调用函数
SELECT myf2('k_ing');
复制代码

3.3 查看函数

#语法
SHOW CREATE FUNCTION 函数名;
复制代码

3.4 删除函数

#语法
DROP FUNCTION 函数名;
复制代码

八、流程控制结构

1、结构分类

顺序结构：程序从上向下依次执行；

分支结构：程序可以从多条路径中选择一条去执行；

循环结构：程序在满足一定条件的基础上，重复执行一段代码；

2、分支结构

2.1 if函数

可以实现简单的双分支。可以应用在任何地方。

#语法
IF(表达式1，表达式2，表达式3)
#如果 表达式1成立，返回表达式2的值，否则返回表达式3的值（三目运算符）
复制代码

2.2 case结构

情况1：类似于switch语句，一般用于等值判断

#语法
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1[或语句1;]
WHEN 要判断的值 THEN 返回的值2[或语句2;]
...
ELSE 要返回的值n[或语句n;]
END CASE;
复制代码

情况2：类似于多重if，一般用于实现区间判断，看到底在哪个区间

#语法
CASE 
WHEN 要判断的条件1 THEN 返回的值1[或语句1;]
WHEN 要判断的条件2 THEN 返回的值2[或语句2;]
...
ELSE 要返回的值n[或语句N;]
END CASE;
复制代码

可作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end的外面；也可以作为独立的语句中使用，只能放在begin end中。

2.3 if结构

实现多重分支。只能应用在begin end中。

#语法
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
【ELSE 语句n;】
END IF;
复制代码

3、循环结构

3.1 简单介绍

分类：while、loop、repeat。

循环控制：iterate类似于Java中的continue，结束当前循环，进行下一次循环；leave相当于Java中的break

3.2 while循环

#语法
【标签：】WHILE 循环条件 DO 
	循环体;
END WHILE 【标签】;
复制代码

案例

#批量插入，插入到admin表多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
	INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');
	SET i = i + 1;
	END WHILE a;
END $

CALL pro_while1(100)$
复制代码

含leave的语句

#批量插入，插入到admin表多条记录,插20条结束
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');
        IF i>=20 THEN LEAVE a;
        END IF;
        SET i = i + 1;
	END WHILE a;
END $

CALL pro_while1(100)$
复制代码

3.3 loop循环

#语法
【标签：】LOOP
	循环体;
END LOOP 【标签】;
复制代码

可以用来模拟简单的死循环。

3.4 repeat循环

#语法
【标签：】REPEAT 循环体； 
UNTIL 结束循环的条件
END REPEAT【标签】;
复制代码