MySQL06-视图&存储过程&流程控制

NiuMT 2020-06-03 20:58:30

MySQL

视图

视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

《sql础教程第2版》用一句话非常凝练的概括了视图与表的区别—“==是否保存了实际的数据==”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

那既然已经有数据表了，为什么还需要视图呢？主要有以下几点原因：

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

创建视图

/*语法：
create view 视图名
as
查询语句;
*/

USE myemployees;

#查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;

#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

视图修改、删除、查看

#方式一：
/*
create or replace view  视图名
as
查询语句;
*/
SELECT * FROM myv3 

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;


# 删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;


# 查看视图
DESC myv3;
SHOW CREATE VIEW myv3;

视图的更新

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

#插入、会影响原始表
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#修改、会影响原始表
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

#删除、会影响原始表
DELETE FROM myv1 WHERE last_name = '张无忌';

/*
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表
*/

创建语法的关键字是否实际占用物理空间使用

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

表 create table 保存了数据增删改查

变量、存储过程和函数

变量

系统变量：全局变量、会话变量

自定义变量：用户变量、局部变量

系统变量：说明：变量由系统定义，不是用户定义，属于服务器层面；注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，==默认会话级别==；全局变量作用域：针对于所有会话（连接）有效，但不能跨重启；会话变量作用域：针对于当前会话（连接）有效。

1、查看所有系统变量
show global|【session】variables;

2、查看满足条件的部分系统变量
show global|【session】 variables like ‘%char%’;

3、查看指定的系统变量的值
select @@global|【session】系统变量名;

4、为某个系统变量赋值；方式一：
set global|【session】系统变量名=值；方式二：
set @@global|【session】系统变量名=值;

自定义变量：说明：变量由用户自定义，而不是系统提供的；使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）；

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

赋值操作符：=或 :=

①声明并初始化: SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

②赋值（更新变量的值）
方式一：SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二：SELECT 字段 INTO @变量名
FROM 表;
③使用（查看变量的值）：SELECT @变量名;

局部变量：作用域：仅仅在定义它的begin end块中有效，应用==在 begin end中的第一句话==

①声明：
DECLARE 变量名类型； DECLARE 变量名类型【DEFAULT 值】;

②赋值（更新变量的值）
方式一：SET 局部变量名=值;
SET 局部变量名:=值;
  SELECT 局部变量名:=值;
方式二：SELECT 字段 INTO 具备变量名
FROM 表;
③使用（查看变量的值）
SELECT 局部变量名;

用户变量和局部变量的对比：

作用域定义位置语法

用户变量当前会话会话的任何地方加@符号，不用指定类型

局部变量定义它的BEGIN END中 BEGIN END的第一句话一般不用加@,需要指定类型

存储过程

存储过程和函数：类似于java中的方法

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

#创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体（一组合法的SQL语句）
END
/*参数列表包含三部分
参数模式  参数名  参数类型
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
举例：
in stuname varchar(20)

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/


#调用语法
CALL 存储过程名(实参列表);
#案例 创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;#声明并初始化
    SELECT COUNT(*) INTO result#赋值
    FROM admin
    WHERE admin.username = username
    AND admin.password = PASSWORD;
    SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888')$
#案例 根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
    SELECT boys.boyname, boys.usercp INTO boyname,usercp
    FROM boys 
    RIGHT JOIN
    beauty b ON b.boyfriend_id = boys.id
    WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$


#存储过程的删除，不能修改存储过程
#语法：drop procedure 存储过程名，一次只能删除一个
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×


#存储过程的查看
DESC myp2;×
SHOW CREATE PROCEDURE  myp2;

函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

#创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END
/*注意：
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记*/


#调用语法
SELECT 函数名(参数列表)
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;#定义局部变量
    SELECT COUNT(*) INTO c#赋值
    FROM employees;
    RETURN c;
END $
SELECT myf1()$
#案例 根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE ;
    SELECT AVG(salary) INTO sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name=deptName;
    RETURN sal;
END $
SELECT myf3('IT')$


#查看和删除
SHOW CREATE FUNCTION myf3;
DROP FUNCTION myf3;

流程控制结构

分支结构

if函数

语法：if(条件,值1，值2)

功能：实现双分支，应用任何地方。

if结构

语法：

if 条件1 then 语句1;

elseif 条件2 then 语句2;

….

else 语句n;

end if;

功能：类似于多重if

只能应用在begin end 中

#案例 创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION 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>60 THEN SET ch='C';
    ELSE SET ch='D';
    END IF;
    RETURN ch;
END $
SELECT test_if(87)$

#案例 创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
    IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
    ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
    ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
    END IF;
END $
CALL test_if_pro(2100)$

case结构

语法：

情况1：类似于switch

case 变量 | 表达式 | 字段

when 值1 then 语句1;

when 值2 then 语句2;

…

else 语句n或要返回的值n;

end
情况2：

case

when 条件1 then 语句1;

when 条件2 then 语句2;

…

else 语句n或要返回的值n;

end

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

else可以省略，若else省略，并when的所有条件都不满足，则返回null。

#案例 创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
    DECLARE ch CHAR DEFAULT 'A';
    CASE 
    WHEN score>90 THEN SET ch='A';
    WHEN score>80 THEN SET ch='B';
    WHEN score>60 THEN SET ch='C';
    ELSE SET ch='D';
    END CASE;
    RETURN ch;
END $
SELECT test_case(56)$

循环结构

分类：while、loop、repeat

循环控制：iterate类似于 continue，继续，结束本次循环，继续下一次；leave 类似于 break，跳出，结束当前所在的循环。

while

语法：

【标签:】while 循环条件 do

循环体;

end while【标签】;

#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
        SET i=i+1;
    END WHILE;
END $
CALL pro_while1(100)$

#添加leave语句
#案例 批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<=insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        IF i>=20 THEN LEAVE a;
        END IF;
        SET i=i+1;
    END WHILE a;
END $
CALL test_while1(100)$

loop

语法：

【标签:】loop

循环体;

end loop 【标签】;

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

repeat

语法：使用 leave 跳出循环。

【标签：】repeat

循环体;

until 结束循环的条件

end repeat 【标签】;

/*已知表stringcontent
其中字段：
id 自增长
content varchar(20)

向该表插入指定个数的，随机的字符串
*/
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(20)
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startIndex INT;#代表初始索引
    DECLARE len INT;#代表截取的字符长度
    WHILE i<=insertcount DO
        SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
        SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）
        INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
        SET i=i+1;
    END WHILE;
END $

CALL test_randstr_insert(10)$

MySQL05-TCL

JDBC-01 概览&连接数据库方式