8.17 day29 jave

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

应用场景：
多个地方用到相同的查询结果。
该查询结果使用的sql语句比较复杂。

好处：
实现了sql语句的重用
简化了复杂的sql操作，不必知道其细节
保护数据，提高安全性

类型 关键字 是否实际占用物理空间 使用
视图 VIEW 仅保持了sql逻辑 增删改查，一般只能查
表 TABLE 保存了数据 增删改查
案例 查询姓张的学生名和专业名
不使用视图

SELECT stuName,majorName
FROM stuinfo s
INNER JOIN major m ON s.majorId=m.id
WHERE s.stuname LIKE '张%';
//使用视图

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 '张%';

1 创建视图
语法：

CREATE VIEW 视图名 AS 查询语句;
1
案例1 查询姓名中包含a字符的员工名，部门名和工种信息

CREATE VIEW v2
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 v2
WHERE last_name LIKE '%a%';

案例2 查询各部门的平均工资级别

CREATE VIEW v3
AS
SELECT AVG(salary) asl,department_id
FROM employees
GROUP BY department_id;

SELECT v3.*,g.`grade` FROM v3
JOIN sal_grade g
ON v3.`asl` BETWEEN g.`min_salary` AND g.`max_salary`;
案例3 查询平均工资最低的部门信息

SELECT * FROM departments
WHERE department_id=
(SELECT department_id FROM v3 ORDER BY asl LIMIT 1);

案例4 查询平均工资最低的部门名和工资

CREATE VIEW v4
AS
SELECT * FROM v3 ORDER BY asl LIMIT 1;

SELECT d.`department_name`,v4.`asl` FROM v4
JOIN departments d
ON d.`department_id`=v4.`department_id`;

案例5 创建视图emp_v1,要求查询电话号码以’011’开头的员工姓名和工资、邮箱

CREATE VIEW emp_v1
AS
SELECT last_name,salary,email FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;
案例6 创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息

CREATE VIEW emp_v2
AS
SELECT department_id FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

SELECT d.* FROM departments d
JOIN emp_v2 ON d.`department_id` = emp_v2.`department_id`;
2 修改视图
方式一 存在则修改，不存在则创建

CREATE OR REPLACE VIEW 视图名 AS 查询语句;

方式二

ALTER VIEW 视图名 AS 查询语句;

3 删除视图
语法：

DROP VIEW 视图名1,视图名2...;

4 查看视图
查看视图结构
语法： desc 视图名;
例：desc v3;

查看视图创建过程
语法:SHOW CREATE VIEW 视图名;
例: SHOW CREATE VIEW v3;

5 视图的更新
特点：
视图的可更新性和视图中查询的定义有关，以下类型的视图不能更新：

包含以下关键字: 分组函数,DISTINCT,GROUP BY,HAVING,UNION,UNION ALL

常量视图，例:

CREATE VIEW v AS SELECT 'abc' NAME;

SELECT 中包含子查询，例:

CEATE VIEW v AS SELECT(SELECT 1) AS number;

JOIN，例:

CREATE VIEW v AS
SELECT * FROM employees e JOIN departments d
ON e.department_id=d.department_id;

FROM 一个不能更新的视图，例：

CREATE VIEW v2 AS
SELECT * FROM v;

WHERE 子句的子查询引用了 FROM 子句中的表，例：

CREATE VIEW v AS
SELECT * FROM employees
WHERE employees_id IN (SELECT DISTINCT manager_id FROM employees);

环境准备

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

1 插入数据

INSERT INTO v1 VALUES('宁缺','abc@qq.com');

2 修改数据

UPDATE v1 SET last_name='桑桑' WHERE last_name='宁缺';

3 删除数据

DELETE FROM v1 WHERE last_name='桑桑';
综合练习
1 创建Book表，字段如下：
bid 整形，要求主键
bname 字符型，要求设置唯一非空
price 浮点型，要求默认值10
btypeId 类型编号，要求引用bookType表的id字段
已知bookType表字段如下：id，name

CREATE TABLE Book(
bid INT PRIMARY KEY AUTO_INCREMENT,
bname VARCHAR(10) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeId INT,
CONSTRAINT fk FOREIGN KEY(btypeId) REFERENCES bookType(id)
);
2 开启事务，向表中插入一行数据并结束

SET autocommit=0;
INSERT INTO Book VALUES(1,'将夜',20,2);
COMMIT;

3 创建视图，实现查询价格大于100的书名和类型CREATE VIEW v AS

SELECT bname,btypeId FROM Book b
JOIN bookType t ON b.btypeId=t.id
WHERE b.price>100;

 

 修改视图，实现查询价格在90-120之间的书名和价格

CREATE OR REPLACE v AS
SELECT bname,price
FROM Book
WHERE price BETWEEN 90 AND 120;

删除刚才创建的视图

DROP VIEW v;
变量
系统变量
根据作用范围分为全局变量，会话变量
全局变量是由系统提供的，属于服务器范围，服务器重启后重新赋值
会话变量仅针对当前会话（连接）有效

语法：
1 查看所有的全局变量

SHOW GLOBAL VARIABLES;

 查看所有的会话变量

SHOW SESSION VARIABLES;
SHOW VARIABLES;默认查看会话变量
查看部分系统变量
例: 查看字符集相关的系统变量

SHOW GLOBAL VARIABLES LIKE '%char%';
SHOW [SESSION] VARIABLES LIKE '%char%';
4 查看某个系统变量

SELECT @@global.系统变量名;
SELECT @@[session.]系统变量名;不写默认为session
例：查看自动提交

SELECT @@global.autocommit;
SELECT @@[session.]autocommit;
例：查看隔离级别

SELECT @@global.transaction_isolation;
SELECT @@[session.]transaction_isolation;
5 为某个系统变量赋值

SET GLOBAL|[SESSION] 系统变量名 =值;
SET @@GLOBAL|[SESSION].系统变量名 =值;
如果是全局级别需要加global，如果是会话级别加session或不写。

自定义变量
说明： 变量是用户自定义的，不是由系统提供
使用步骤： ①声明②赋值③使用
根据作用范围分为用户变量，局部变量

用户变量
作用域： 针对于当前会话（连接）有效，同于会话变量的作用域
应用在任何地方，可以放在 BEGIN END 里面或外面
①声明并初始化

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

SET @用户变量名= 值;
SET @用户变量名: = 值;
SELECT @用户变量名: = 值;
例：

SET @name='abc';
SET @name=1;
方式二：通过 SELECT INTO

SELECT 字段 INTO @用户变量名 FROM 表;
例：

SET @count=1;
SELECT COUNT(*) INTO @count FROM employees;
SELECT @count;//107
③查询

SELECT @变量名;
案例 声明2个用户变量并赋初值，求和并打印

SET @num1 = 1;
SET @num2 = 2;
SET @sum = @num1 + @num2;
SELECT @sum; //3
局部变量
作用域： 仅在定义它的 BEGIN END 中有效
应用在 BEGIN END 中的第一句话
使用：
①声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
②赋值
方式一：通过 SET 或 SELECT

SET 局部变量名= 值;
SET 局部变量名: = 值;
SELECT @局部变量名: = 值;
方式二：通过 SELECT INTO

SELECT 字段 INTO 局部变量名 FROM 表;
③查询

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