USE myemployees;
CREATE VIEW myv2
AS
SELECT b.email,b.`last_name`,a.`department_name`,c.`job_title`
FROM `departments` a,`employees` b,`jobs` c
WHERE a.`department_id`=b.`department_id` AND b.`job_id`=c.`job_id`;
SELECT * FROM myv2;
SELECT * FROM myv2 WHERE last_name LIKE '%a%';
CREATE VIEW myv3
AS
SELECT AVG(salary) AS '每个部门的平均工资', FROM
GROUP BY `department_id`;
SELECT * FROM myv3;
SELECT * FROM myv3 ORDER BY '每个部门的平均工资';
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),`department_id`
FROM `employees` GROUP BY `department_id`;
DESC myv3;
CREATE VIEW myv3
AS
SELECT * FROM`employees`;
DROP VIEW myv1,myv2,myv3;
SHOW CREATE VIEW myv3;
CREATE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM `employees` GROUP BY `department_id`
HAVING MAX(salary)>12000;
SELECT * FROM `emp_v2`;
DESC myv3;
SHOW CREATE VIEW myv3;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,12*salary*(1+IFNULL(`commission_pct`,0)) AS 'annuak salary'
FROM `employees`;
SELECT * FROM myv1;
INSERT INTO myv1 (last_name,email) VALUES("张飞","1230@qq.com");
UPDATE myv1 SET last_name='张无忌' WHERE last_name='Gietz';
DELETE FROM myv1 WHERE last_name='张无忌';
/*
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查,一般不能增删改
表 create table 保存了数据 增删改查