Mysql学习笔记（018）-视图

  1 #视图
  2 /*
  3 含义：虚拟表，和普通表一样使用
  4 mysql5.1版本出现的新特性，是通过表动态生成的数据
  5 
  6 比如：舞蹈班和普通班的对比
  7     创建语法的关键字    是否实际占用物理空间    使用
  8 
  9 视图    create view        只是保存了sql逻辑    增删改查：一般不能增删改
 10 
 11 表    create table        保存了数据        增删改查：可以
 12 
 13 
 14 
 15 
 16 */
 17 SHOW VARIABLES LIKE ‘character_set_%‘;
 18 SHOW VARIABLES LIKE ‘collation_%‘;
 19 SET character_set_database =utf8;
 20 SET character_set_filesystem=utf8;
 21 SET character_set_results =utf8;
 22 SET character_set_server =utf8;
 23 SET character_set_system =utf8; /*此处utf-8也可以*/
 24 SET collation_server = utf8_general_ci;
 25 SET collation_database = utf8_general_ci;
 26 
 27 ALTER TABLE major CONVERT TO CHARACTER SET utf8;
 28 ALTER TABLE stuinfo CONVERT TO CHARACTER SET utf8;
 29 #案例：查询姓张的学生名和专业名
 30 SELECT stuname,majorName
 31 FROM stuinfo s
 32 INNER JOIN major m ON s.`majorid`=m.`majorName`
 33 WHERE s.`stuname` LIKE ‘张%‘;
 34 SHOW CREATE TABLE major;
 35 SHOW CREATE TABLE stuinfo;
 36 
 37 CREATE VIEW v1
 38 AS 
 39 SELECT stuname,majorName
 40 FROM stuinfo s
 41 INNER JOIN major m ON s.`majorid`=m.`majorName`
 42 
 43 SELECT * FROM v1 WHERE stuname LIKE ‘张%‘;
 44 
 45 #一、创建视图
 46 /*
 47 语法：
 48 create view 视图名
 49 as
 50 查询语句
 51 
 52 */
 53 USE myemployees;
 54 
 55 #案例讲解
 56 #1.查询邮箱中包含a字符的员工名、部门名和工种信息
 57 #①创建
 58 CREATE VIEW myv1
 59 AS
 60 
 61 SELECT last_name,department_name,job_title
 62 FROM employees e
 63 JOIN departments d ON e.department_id = d.department_id
 64 JOIN jobs j ON j.job_id = e.job_id;
 65 
 66 #②使用
 67 SELECT * FROM myv1 WHERE last_name LIKE ‘%a%‘;
 68 
 69 #2.查询各部门平均工资级别
 70 #创建视图查看每个部门的平均工资
 71 CREATE VIEW myv2
 72 AS
 73 SELECT AVG(salary) ag,department_id
 74 FROM employees
 75 GROUP BY department_id;
 76 
 77 #②使用
 78 SELECT myv2.ag, g.grade_level 
 79 FROM myv2
 80 JOIN job_grades g
 81 ON myv2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 82 
 83 
 84 
 85 #3.查询平均工资最低的部门信息
 86 
 87 SELECT * FROM myv2 ORDER BY ag LIMIT 1;
 88 
 89 #4.查询平均工资最低的部门名和工资
 90 
 91 CREATE VIEW myv3
 92 AS
 93 SELECT * FROM myv2 ORDER BY ag LIMIT 1;
 94 
 95 SELECT d.*,m.ag 
 96 FROM myv3 m
 97 JOIN departments d
 98 ON m.`department_id`=d.`department_id`;
 99 
100 
101 
102 #二、视图的修改
103 
104 #方式一：
105 /*
106 create or replace view
107 as
108 查询语句;
109 
110 
111 */
112 SELECT * FROM myv3;
113 
114 CREATE OR REPLACE  VIEW myv3
115 AS
116 SELECT AVG(salary),job_id
117 FROM employees
118 GROUP BY job_id;
119 
120 #方式二：
121 /*
122 语法：
123 alter view 视图名
124 as
125 查询语句;
126 
127 */
128 ALTER  VIEW myv3
129 AS 
130 SELECT * FROM employees;
131 
132 #三、删除视图
133 
134 /*
135 
136 语法:drop view 视图名,视图名...;
137 */
138 DROP VIEW myv1,myv2,myv3;
139 
140 #四、查看视图
141 DESC myv3;
142 SHOW CREATE VIEW myv3;
143 
144 #五、视图的更细
145 CREATE OR REPLACE VIEW myv1
146 AS
147 SELECT last_name,email,salary*12*(1+IFNULL(commission_pet,0)) "annual salary"
148 FROM employees;
149 
150 CREATE OR REPLACE VIEW myv1
151 AS
152 SELECT last_name,email
153 FROM employees;
154 
155 SELECT * FROM myv1;
156 #1.插入
157 
158 INSERT INTO myv1 VALUES(‘zhangfei‘,‘zf@qq.com‘);
159 
160 #2.修改
161 UPDATE myv1 SET last_name=‘zhangwuji‘ WHERE last_name=‘zhangfei‘;
162 #错误代码： 1423
163 #Field of view ‘myemployees.myv1‘ underlying table doesn‘t have a default value
164 # 造成该问题的原因是： 当向视图中插入数据时，同时也会向原表插入数据插入数据 ，而原表(employees)中存在多个字段不允许为空，所以无法插入 ，将这些不允许为空的字段修改为允许为空即可。
165 
166 
167 #3.删除
168 DELETE FROM myv1 WHERE last_name=‘zhangwuji‘;
169 
170 
171 #具备以下特点的视图是不允许更新的
172 
173 #①包含以下关键字的sql语句:分组函数、group by,distinct,having、union或有union all
174 
175 CREATE OR REPLACE VIEW myv1
176 AS
177 SELECT MAX(salary) m,department_id
178 FROM employees
179 GROUP BY department_id;
180 
181 SELECT * FROM myv1;
182 
183 #更新
184 UPDATE myv1 SET m=9000 WHERE department_id=10;
185 
186 #②常量视图
187 CREATE OR REPLACE VIEW myv2
188 AS
189 SELECT ‘john‘ NAME;
190 
191 SELECT * FROM myv2;
192 
193 #更新
194 UPDATE myv2 SET NAME=‘lucy‘;
195 
196 #③select中包含子查询
197 CREATE OR REPLACE VIEW myv3
198 AS
199 
200 SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资;
201 FROM departments
202 
203 #更新
204 SELECT * FROM myv3;
205 UPDATE myv3 SET 最高工资=10000;
206 
207 
208 #④join
209 CREATE OR REPLACE VIEW myv4
210 AS
211 SELECT last_name,department_name
212 FROM employees e
213 JOIN departments d
214 ON e.department_id = d.department_id;
215 
216 #更新
217 SELECT * FROM myv4;
218 UPDATE myv4 SET last_name = ‘张飞‘ WHERE last_name=‘Whalen‘;
219 INSERT INTO myv4 VALUES(‘陈真‘,‘xxx‘);
220 
221 #⑤from 一个不能更新的视图
222 CREATE OR REPLACE VIEW myv5
223 AS
224 
225 SELECT * FROM myv3;
226 
227 #更新
228 
229 SELECT * FROM myv5;
230 
231 UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
232 
233 
234 #⑥where子句的子查询引用了from子句中的表
235 
236 CREATE OR REPLACE VIEW myv6
237 AS
238 
239 SELECT last_name,email,salary
240 FROM employees
241 WHERE employee_id IN(
242     SELECT manager_id
243     FROM employees
244     WHERE manager_id IS NOT NULL
245 
246 );
247 
248 #更新
249 SELECT * FROM myv6;
250 
251 UPDATE myv6 SET salary=10000 WHERE last_name = ‘k_ing‘;