mysql 视图改名 mysql 视图变量

转载

mob64ca14173efa 2023-10-19 10:34:15

文章标签 mysql 视图改名数据库 mysql sql 变量名 文章分类 MySQL 数据库

5 其他

5.1 视图

视图：MySQL从5.0.1版本开始提供视图功能。虚拟表（和普通表一样使用），并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

应用场景：

多个地方用同样的查询结果
该查询结果使用的sql语句较复杂

视图的好处：

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

	创建语法的关键字	是否实际占用物理空间	使用
视图	`create view`	占用较小，只保存`sql`逻辑	一般用于查询
表	`create table`	保存实际数据	增删改查

#案例：查询姓张的学生名和专业名(包装成视图)
CREATE VIEW v1
AS
SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m
ON s.`majorid` = m.`id`;

SELECT * FROM v1
WHERE stuname LIKE '张%';

5.1.1 创建视图

/*
语法：
create view 视图名
as
查询语句;
*/
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建视图
CREATE VIEW test1
AS
SELECT e.last_Name, d.department_name, j.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 test1
WHERE last_name LIKE '%a%';


#2.查询各部门的平均工资级别
#①创建视图 查看每个部门的平均工资
CREATE VIEW test2
AS
SELECT department_id dId, AVG(salary) ag
FROM employees
GROUP BY department_id;

#②使用
SELECT test2.ag, g.grade_level
FROM test2
INNER JOIN job_grades g
ON test2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#3.查询平均工资最低的部门信息
#使用视图
SELECT *
FROM test2
ORDER BY ag
LIMIT 1;


#4.查询平均工资最低的部门名和工资
#使用视图
SELECT test2.*, d.department_name
FROM test2
INNER JOIN departments d
ON test2.dId = d.`department_id`
ORDER BY test2.ag ASC
LIMIT 1;

#解法②
CREATE VIEW test3
AS
SELECT *
FROM test2
ORDER BY ag
LIMIT 1;

SELECT d.*, t.ag
FROM departments d
INNER JOIN test3 t
ON d.`department_id` = t.dId;

5.1.2 视图的修改

#方式一：
/*
语法：(存在就修改，不存在则创建)
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM test3;

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


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

5.1.3 删除、查看视图

#三、删除视图
#语法：drop view 视图名1，视图名2...;
DROP VIEW test1, test2, test3;


#四、查看视图
DESC test3;
SHOW CREATE VIEW test3;

5.1.4 视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name, email, 
	salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees;

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


SELECT * FROM myv1;
SELECT * FROM employees;

#1.插入数据(会插入原始表中)
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改(原始表也更更新)
UPDATE myv1 
SET last_name='张无忌' 
WHERE last_name='张飞';

#3.删除(原始表也删除了)
DELETE FROM myv1
WHERE last_name='张无忌';

具备以下特点的视图，不允许更新

#1.包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS 
SELECT MAX(salary) m, department_id
FROM employees
GROUP BY department_id;

SELECT * FROM myv1;
#更新(不可行，不允许更新)
UPDATE myv1
SET m=9000 
WHERE department_id = 10;


#2.常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

SELECT * FROM myv2;
#更新(不可行，不允许更新)
UPDATE myv2
SET NAME='lucy';


#3.Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (
	SELECT MAX(salary)
	FROM employees
) 最高工资;

SELECT * FROM myv3;
#更新(不可行)
UPDATE myv3 SET 最高工资=10000;



#4.join(包括所有连接)
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

SELECT * FROM myv4;
#更新(可行)、插入等不可行
UPDATE myv4 SET last_name='张飞'
WHERE last_name='whalen';

INSERT INTO myv4 VALUES('陈真','xxx');



#5.FROM一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv1;

SELECT * FROM myv5;
#更新
UPDATE myv5 SET m=100000 
WHERE department_id=60;



#6.WHERE子句的子查询引用了FROM子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name, email, salary
FROM employees
WHERE employee_id IN(
	SELECT manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);

SELECT * FROM myv6;
#更新(不可行)
UPDATE myv6 SET salary=10000
WHERE last_name = 'k_ing';

5.2 变量

分类：

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

5.2.1 系统变量

说明：变量由系统提供，不是用户定义，属于服务器层面

使用的语法：

#1.查看所有的系统变量
show global|【session】 variables; #global-全局，session-会话

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

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

#4.为某个系统变量赋值
方式一：
set global|【session】 系统变量名 = 值;

方式二：
set @@global|【session】.系统变量名 = 值;

注意：

如果是全局级别，则需要加global
如果是会话级别，则需要加session（若不写，默认session）

1. 全局变量

作用域：服务器每次启动将为所有的全部变量赋初始值，针对所有的会话(连接)有效，但不能跨重启。

#①查看所有的全局变量
SHOW GLOBAL VARIABLES;

#②查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

#③查找指定的全局变量的值
SELECT @@global.autocommit; #非0表真
SELECT @@global.tx_isolation;

#④为某个指定的全局变量赋值
#方式一：
SET @@global.autocommit=0;
#方式二：
SET GLOBAL autocommit=1;

2. 会话变量

作用域：仅针对于当前会话(连接)有效

#使用时，session可省略
#①查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;

#②查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';

#③查找指定的会话变量的值
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

#④为某个指定的会话变量赋值
#方式一：
SET @@session.tx_isolation='read-uncommitted';
SET @@tx_isolation='read-uncommitted';
#方式二：
SET SESSION tx_isolation='read-committed';
SET tx_isolation='read-committed';

5.2.2 自定义变量

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

1. 用户变量

作用域：针对于当前会话(连接)有效，同于会话变量的作用域
应用在任何地方

语法：

赋值的操作符： = 或 :=
#①声明并初始化
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

#②赋值（更新用户变量的值）
方式一：通过SET或SELECT
	SET @用户变量名=值;
	SET @用户变量名:=值;
	SELECT @用户变量名:=值;

方式二：通过 SELECT INTO
	
	SELECT 字段 INTO @变量名
	FROM 表

#③使用(查看用户变量的值)
SELECT @用户变量名;

案例：

#声明并初始化
SET @name='john';
SET @name=100; #可行 - 弱类型
#赋值
SET @count=1;
SELECT COUNT(*) INTO @count
FROM employees;
#查看
SELECT @count;

2. 局部变量

作用域：仅仅在定义它的begin end中有效
应用在begin end中的第一句话！！！

#①声明(可不初始化)
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

#②赋值
方式一：通过SET或SELECT
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT @局部变量名:=值;

方式二：通过SELECT INTO

	SELECT 字段 INTO 局部变量名
	FROM 表;
	
#③使用
SELECT 局部变量名;

案例：

#案例：声明两个变量并赋初始值，求和，并打印
#1.用户变量
SET @m=1;
SET @n=2;
SET @sum = @m + @n;
SELECT @sum;

#2.局部变量（局部的不允许在外面写）
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;

3. 用户变量和局部变量比较

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中任何地方	必须加@
局部变量	BEGIN END中	只能在BEGIN END中第一句话	一般不加@，需限定类型