10、视图、变量

视图

简介

-- 视图
/*
含义：虚拟表
MySQL5.1版本出现的新特性，是通过表动态生成的数据，只保存了SQL逻辑，不保存查询数据
应用场景：
1、多个地方用到同样的查询结果；
2、该查询结果使用的SQL语句较为复杂；
*/
# 查询学生中姓张的学生姓名和专业
# 以前我们的做法
USE studb;
SELECT name, majorname
FROM stu_info s
INNER JOIN major m
ON s.majorid = m.id
WHERE s.name LIKE '张%';

# 视图的做法,首先创建一张视图,保存SQL逻辑
CREATE VIEW my_view
AS
SELECT name, majorname
FROM stu_info
INNER JOIN major m ON stu_info.majorid = m.id;
# 使用刚刚创建的视图
SELECT * FROM my_view
WHERE name LIKE '张%';

创建视图

-- 1、创建视图
/*
CREATE VIEW 视图名
AS
查询语句
*/
# 查询姓名中包含a字符的员工名、部门名、工种信息
USE myemployees;
CREATE VIEW my_view
AS
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;

SELECT * FROM my_view
WHERE last_name LIKE '%a%';


# 查询各部门的平均工资级别
CREATE VIEW my_view1
AS
SELECT department_id, avg(salary) AS avgsalary
FROM employees
GROUP BY department_id;

SELECT my_view1.*, job_grades.grade_level
FROM my_view1
LEFT JOIN job_grades
ON my_view1.avgsalary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal
ORDER BY my_view1.department_id;


# 查询平均工资最低的部门信息
SELECT d.*, my_view1.avgsalary
FROM my_view1
INNER JOIN departments d ON my_view1.department_id = d.department_id
ORDER BY my_view1.avgsalary
LIMIT 1;
# 或者
CREATE VIEW my_view2
AS
SELECT my_view1.*
FROM my_view1
ORDER BY my_view1.avgsalary
LIMIT 1;
SELECT my_view2.avgsalary, d.*
FROM my_view2
INNER JOIN departments d ON my_view2.department_id = d.department_id;

注意：MySQL中视图不支持封装子查询查出来的数据。

视图一旦创建，系统会在视图对应的数据库文件夹下，创建一个对应的结构文件:frm文件。

视图的好处：

1、SQL语句的重用；

2、简化复杂的SQL操作，不必知道它的查询细节；

3、保护数据，提高安全性。

修改视图

-- 2、修改视图
/*
方式一：和创建类似
CREATE OR REPLACE VIEW 视图名
AS
查询语句
*/
# my_view2视图存储的是部门平均工资最低的部门编号和平均最低工资,现在修改为存储工种平均工资
CREATE OR REPLACE VIEW my_view2
AS
SELECT avg(salary), job_id
FROM employees e
GROUP BY e.job_id;
/*
方式二：和修改表类似
ALTER VIEW 视图名
AS
查询语句
*/
ALTER VIEW my_view2
AS
SELECT my_view1.*
FROM my_view1
ORDER BY my_view1.avgsalary
LIMIT 1;

查看视图和删除视图

-- 3、查看视图
/*
DESC 视图名;
*/
DESC my_view2;
SHOW CREATE VIEW my_view2;

-- 4、删除视图
/*
DROP VIEW 视图名,视图名,...;
*/
DROP VIEW my_view;

更新视图

-- 5、更新视图(针对数据的更新,DML语句)
CREATE OR REPLACE VIEW my_view3
AS
SELECT last_name, email
FROM employees;

# 插入数据
INSERT INTO my_view3  VALUES ('张飞', 'zhangfei@qq.com');
SELECT count(*) FROM my_view3;
-- 108,插入成功
SELECT count(*) FROM employees;
-- 108,更新视图数据,使得原始表中的数据也发生了改变

# 修改数据
UPDATE my_view3 SET last_name = '刘备' WHERE last_name = '张飞';

# 删除数据
DELETE FROM my_view3 WHERE last_name = '刘备';
SELECT count(*) FROM my_view3;
-- 107
SELECT count(*) FROM employees;
-- 107

-- 所以在更新视图时，会影响原始表中的数据，这种现象是不好的，所以我们需要给视图增加权限，只读
-- 其实我们上面的视图，是最简单的，实际中的视图会有许多复杂的SQL语句，是不具备可更新性的，所以更新视图很少很少用

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

视图与表的对比

-- 6、视图与表的对比
--          创建语法        是否实际占用物理空间          使用
-- 视图   CREATE VIEW         否（只有逻辑占用空间）     增删改查，一般不能增删改
-- 表    CREATE TABLE        是                       增删改查

变量

分类

-- 变量
/*
分类：
1、系统变量
    全局变量
    会话变量（客户端的一次连接）
2、自定义变量
    用户变量
    局部变量
*/
-- 一、系统变量
/*
变量由系统提供，不是用户定义，属于服务器层面
*/
-- 1、查看所有的系统变量
SHOW GLOBAL VARIABLES;
# 查看所有全局变量
SHOW SESSION VARIABLES;
# 查看所有会话变量，SESSION关键字可以省略
-- 2、查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
-- 3、查看指定的某个系统变量
SELECT @@CHARACTER_SET_SERVER;
-- 4、为某个具体的系统变量赋值
/*SET GLOBAL AUTOCOMMIT = 0;*/

系统变量

-- 二、全局变量
SHOW GLOBAL VARIABLES;
# 查看指定的全局变量
SELECT @@AUTOCOMMIT;
# 1
SELECT @@TX_ISOLATION;
# 查看事务的隔离级别
# REPEATABLE-READ
/*
作用域：可以跨连接（会话），不能跨重启
*/

-- 三、会话变量
SHOW SESSION VARIABLES;
# SESSION 可以省略
SHOW VARIABLES LIKE '%char%';
SELECT @@CHARACTER_SET_SERVER;
/*
作用域：只针对当前连接（会话）有效
*/

自定义变量

-- 四、自定义变量
/*
说明：变量是用户自定义的，不是系统的;
使用步骤：
1、声明
2、赋值
3、使用
*/
-- 1、用户变量
/*
针对当前会话/连接有效，
应用在任何地方，begin...end里面/begin...end外面，（存储过程）
*/
# 声明并初始化
  SET @用户变量名=值;（=有判断的意思）或者
  SET @用户变量名:=值; 或者
  SELECT @用户变量名:=值;
# 赋值
# 方式一（通过SET或者SELECT)
	SET @用户变量名=值; 或
 	SET @用户变量名:=值; 或
	SELECT @用户变量名:=值
# 方式二（通过SELECT...INTO）
	SELECT 字段 INTO 变量名 FROM 表名 （这个结果必须是一个值）***
# 使用（查看、比较、运算）
  SELECT @用户变量名;
  
-- 声明并初始化
SET @name:='john';
SELECT @name;
# john
SET @name:=200;
SELECT @name;
# 200
SET @count:=1;
-- 赋值
SELECT COUNT(*) INTO @count FROM beauty;
-- 查看
SELECT @count;
# 12

-- 2、局部变量
/*
作用域：在局部有效，定义它的begin...end中有效，并且是begin...end的第一句
*/
# 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
# 赋值
# 方式一：通过SET或者SELECT
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT @局部变量名:=值;
# 方式二：通过SELECT...INTO
	SELECT 字段 INTO 局部变量名 FROM 表名;
# 使用
SELECT 局部变量名; 

# 案例：声明两个变量赋初始值，并求和打印
-- 1、用户变量
SET @a=10;
SET @b=20;
SET @sum = @a + @b;
SELECT @sum;