1 #视图 2 /* 3 含义：虚拟表，和普通表一样使用 4 mysql5.1版本出现的新特性，是通过表动态生成的数据 5 6 比如：舞蹈班和普通班的对比 7 8 9 创建语法的关键字 是否实际占用物理空间 使用 10 视图 create view 只是保存了sql逻辑 增删改查，一般不能增删改 11 表 create table 保存了数据 增删改查 12 13 */ 14 15 #案例：查询姓张的学生名和专业名 16 SELECT stuname,majorname 17 FROM stuinfo s 18 INNER JOIN major m ON s.majorid = m.id 19 WHERE s.stuname LIKE '张%'; 20 21 CREATE VIEW v1 22 AS 23 SELECT stuname,majorname 24 FROM stuinfo s 25 INNER JOIN major m ON s.majorid = m.id; 26 27 SELECT * FROM v1 WHERE s.stuname LIKE '张%'; 28 29 #一、创建视图 30 /* 31 create view 视图名 32 as 33 查询语句; 34 35 */ 36 USE myemployees; 37 #1.查询姓名中包含a字符的员工名、部门名和工种信息 38 #(1)创建 39 CREATE VIEW myv1 40 AS 41 SELECT last_name,department_name,job_title 42 FROM employees e 43 JOIN departments d ON e.department_id=d.department_id 44 JOIN jobs j ON e.job_id=j.job_id; 45 #(2)使用 46 SELECT * FROM myv1 47 WHERE last_name LIKE '%a%'; 48 49 #2.查询各部门的平均工资级别 50 CREATE VIEW myv2 51 AS 52 SELECT AVG(salary) avs,department_id 53 FROM employees 54 GROUP BY department_id; 55 56 SELECT grade_level,department_id FROM job_grades j 57 JOIN myv2 m ON m.avs BETWEEN j.lowest_sal AND j.highest_sal; 58 59 #3.查询平均工资最低的部门信息 60 SELECT * FROM departments d 61 JOIN myv2 m ON d.department_id=m.department_id 62 ORDER BY m.avs ASC LIMIT 1; 63 #4.查询平均工资最低的部门名和工资 64 CREATE VIEW myv3 65 AS 66 SELECT * FROM myv2 ORDER BY avs LIMIT 1; 67 68 SELECT department_name,avs FROM 69 departments d JOIN myv3 m ON d.department_id=m.department_id; 70 71 #二、视图的修改 72 #方式一： 73 /* 74 create or replace view 视图名 75 as 76 查询语句; 77 */ 78 79 SELECT * FROM myv3; 80 81 CREATE OR REPLACE VIEW myv3 82 AS 83 SELECT AVG(salary),job_id 84 FROM employees 85 GROUP BY job_id; 86 87 #方式二： 88 /* 89 语法： 90 alter view 视图名 91 as 92 查询语句; 93 */ 94 ALTER VIEW myv3 95 AS 96 SELECT * FROM employees; 97 98 #三、删除视图 99 /* 100 语法：drop view 视图名,视图名,...; 101 */ 102 DROP VIEW myv1,myv2,myv3; 103 104 #四、查看视图 105 DESC myv3; 106 107 SHOW CREATE VIEW myv3; -- 在mysql客户端里使用比较方便 108 109 #五、视图的更新 110 CREATE OR REPLACE VIEW myv1 111 AS 112 SELECT last_name,email 113 FROM employees; 114 115 SELECT * FROM myv1; 116 SELECT * FROM employees; 117 #1.插入 118 INSERT INTO myv1 VALUES('张飞','zf@qq.com'); 119 #2.修改 120 UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞'; 121 #3.删除 122 DELETE FROM myv1 WHERE last_name='张无忌'; 123 124 #具备以下特点的视图不允许更新 125 #(1)包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all 126 CREATE OR REPLACE VIEW myv1 127 AS 128 SELECT MAX(salary) m,department_id 129 FROM employees 130 GROUP BY department_id; 131 132 SELECT * FROM myv1; 133 #更新 134 UPDATE myv1 SET m=9000 WHERE department_id=10; -- 不允许更新 135 136 #(2)常量视图 137 CREATE OR REPLACE VIEW myv2 138 AS 139 SELECT 'john' NAME; 140 141 SELECT * FROM myv2; 142 #更新 143 UPDATE myv2 SET NAME='lucy'; -- 常量视图不允许更新 144 145 #(3)select中包含子查询 146 CREATE OR REPLACE VIEW myv3 147 AS 148 SELECT (SELECT MAX(salary) FROM employees) 最高工资; 149 150 #更新 151 SELECT * FROM myv3; 152 UPDATE myv3 SET 最高工资=10000; -- 包含子查询的select语句不允许更新 153 154 #(4)join 155 CREATE OR REPLACE VIEW myv4 156 AS 157 SELECT last_name,department_name 158 FROM employees e 159 JOIN departments d ON e.department_id=d.department_id; 160 161 #更新 162 SELECT * FROM myv4; 163 UPDATE myv4 SET last_name='张飞' WHERE last_name='Whalen'; 164 INSERT INTO myv4 VALUES('陈真','xxxx'); -- 有join的不能插入(更新) 165 166 #(5)from一个不能更新的视图 167 CREATE OR REPLACE VIEW myv5 168 AS 169 SELECT * FROM myv3; 170 171 #更新 172 SELECT * FROM myv5; 173 UPDATE myv5 SET 最高工资=10000 WHERE department_id=60; -- from一个不能更新的视图的，不能更新 174 175 #(6)where子句的子查询引用了from子句中的表 176 CREATE OR REPLACE VIEW myv6 177 AS 178 SELECT last_name,email,salary 179 FROM employees 180 WHERE employee_id IN ( 181 SELECT manager_id FROM employees 182 WHERE manager_id IS NOT NULL 183 ); 184 185 SELECT * FROM myv6; 186 #更新 187 UPDATE myv6 SET salary=10000 WHERE last_name='K_ing'; -- 不允许更新
lyt经典版MySQL基础——视图
每天进步一点点，快乐生活多一点。