MySql教程

MySQL-视图

本文主要是介绍MySQL-视图,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

视图

image

image

image

视图的语法

image

案例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%';

image

image

image

image

视图的优点

image

视图的修改

image

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

SELECT * FROM myv3

image

image

删除视图

image

查看视图

image

案例:创建视图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;

image

案例:创建视图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`

image

视图的更新

先创建一个视图

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

SELECT * FROM myv1;

image

1.插入

#1.插入
INSERT INTO myv1 VALUES('张飞','ggh@163.com');

image

2.修改

#2.修改
UPDATE myv1 SET `last_name`='张无忌' WHERE `last_name`='张飞'

image

image

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`='张无忌'

image

视图和表的大对比

image

delete和truncate在事物中的区别

先创建一个表

CREATE TABLE account(
    id INT,
    username VARCHAR(20),
    balance INT
)

image

插入两行测试数据

INSERT INTO account VALUES(1,'张无忌',1000),(2,'赵敏',1000)

SELECT * FROM `account`

image

演示delete

# 演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

image

演示truncate

# 演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

image

其中 delete from 和truncate table 都是删除表的内容,DROP TABLE 是把整个表进行删除

测试题

测试题1

image

测试题2

image

测试题3

image

测试题4

image

测试题5

image

复习

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

外键中 删除主表的记录(一般先删除从表 再删除主表),下面的语句可以帮助你删除主表的记录

image

image

image

image

image

image

image

image

这篇关于MySQL-视图的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!