基本语法：

CREATE VIEW view_name AS SELECT ... FROM table_name;

例如，可以创建一个包含部分数据的视图：

CREATE VIEW departments_viewer AS SELECT department_id, department_name FROM departments;

使用具体案例

案例 1：查询包含’я’的员工信息

CREATE VIEW employees_viewer AS SELECT last_name, department_name, job_title FROM employees
JOIN departments ON departments.department_id = employees.department_id
WHERE last_name LIKE '%亚';

查询指定部门的平均工资

CREATE VIEW salary_avg_viewer AS SELECT AVG(salary) average_salary FROM employees
GROUP BY department_id;

结合多个表的复杂查询

CREATE VIEW customers_order_viewer AS SELECT customer_name, order_amount, order_date FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE order_amount > 1000;

替换整个视图：

CREATE OR REPLACE VIEW view_name AS SELECT ... FROM table_name;

例如：

CREATE OR REPLACE VIEW salary_avg_viewer AS SELECT AVG(salary) FROM employees GROUP BY department_id;

仅修改部分字段：

ALTER VIEW view_name AS SELECT ... FROM table_name;

例如：

ALTER VIEW departments_viewer AS SELECT department_name, number_of_employees FROM departments;

删除视图：

DROP VIEW view_name;

例如：

DROP VIEW departments_viewer;

使用 DESC 命令：

DESC view_name;

查看视图的创建语句：

SHOW CREATE VIEW view_name;

插入新数据：

INSERT INTO emp_v1 VALUES('张飞', 'ZF@QQ.COM', 100000);

更新数据：

UPDATE emp_v1 SET last_name = '张无忌' WHERE last_name = '张飞';

删除数据：

DELETE FROM emp_v1 WHERE last_name = '张无忌';

不允许更新的视图： 如果视图包含以下关键字，则不能对其进行更新操作：

• GROUP BY、DISTINCT、HAVING、UNION 等聚合函数
• 约束条件中的子查询
• 使用 JOIN keyword 时引用了一个不能更新的表

例如：

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);

常量视图： 如果需要频繁查询某个固定值，可以通过视图实现：

CREATE OR REPLACE VIEW const_view AS SELECT 'جارð' AS name;

插入一行数据并提交事务：

SET autocommit = 0;
INSERT INTO book(bid, bname, price, btypeid) VALUES(1, '小李飞刀', 100, 1);
COMMIT;

创建一个查询价格大于100的书名和类型的视图：

CREATE VIEW myv1 AS SELECT bname, btype_name FROM book b
INNER JOIN bookType t ON b.btypeid = t.id WHERE price > 100;

修改视图以支持价格范围查询：

CREATE OR REPLACE VIEW myv2 AS SELECT bname, price FROM book WHERE price BETWEEN 90 AND 120;

删除视图：

DROP VIEW myv1;