MySQL-视图
视图
视图的语法
案例1.查询姓名中包含a字符的员工名、部门名和工种信息
#1.查询姓名中包含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 e.`job_id`=j.`job_id`
# 使用
select * from myv1
where `last_name` like '%a%';
视图的优点
视图的修改
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`;
SELECT * FROM myv3
删除视图
查看视图
案例：创建视图emp_v1,要求查询电话号码以"011"开头的员工姓名和工资、邮箱
# 案例：创建视图emp_v1,要求查询电话号码以"011"开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT `last_name`,`salary`,`email`
FROM `employees`
WHERE `phone_number` LIKE '011%';
SELECT * FROM emp_v1;
案例：创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
# 案例：创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(`salary`) mx_dep,`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MAX(`salary`)>12000;
SELECT * FROM emp_v2;
SELECT d.*,emp_v2.`mx_dep`
FROM `departments` d
JOIN emp_v2
ON d.`department_id`=emp_v2.`department_id`
视图的更新
先创建一个视图
CREATE OR REPLACE VIEW myv1
AS
SELECT `last_name`,`email`
FROM `employees`;
SELECT * FROM myv1;
1.插入
#1.插入
INSERT INTO myv1 VALUES('张飞','ggh@163.com');
2.修改
#2.修改
UPDATE myv1 SET `last_name`='张无忌' WHERE `last_name`='张飞'
3.删除
#3.删除
DELETE FROM myv1 WHERE `last_name`='张无忌'
这时候视图和原始表里面 张无忌这一条记录都被删掉了
总体代码：
# 视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT `last_name`,`email`
FROM `employees`;
SELECT * FROM myv1;
#1.插入
INSERT INTO myv1 VALUES('张飞','ggh@163.com');
SELECT * FROM `employees`;
#2.修改
UPDATE myv1 SET `last_name`='张无忌' WHERE `last_name`='张飞'
#3.删除
DELETE FROM myv1 WHERE `last_name`='张无忌'
视图和表的大对比
delete和truncate在事物中的区别
先创建一个表
CREATE TABLE account(
id INT,
username VARCHAR(20),
balance INT
)
插入两行测试数据
INSERT INTO account VALUES(1,'张无忌',1000),(2,'赵敏',1000)
SELECT * FROM `account`
演示delete
# 演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
演示truncate
# 演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;