视图

视图的语法

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

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

测试题

测试题1

测试题2

测试题3

测试题4

测试题5

复习

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