mysql视图详细笔记 - 自律即自由

  1 #视图
  2 
 15 
 16 #案例：查询姓张的学生名和专业名
 17 SELECT stuname,majorname
 18 FROM stuinfo s
 19 INNER JOIN major m ON s.`majorid`= m.`id`
 20 WHERE s.`stuname` LIKE "张%";
 21 
 22 CREATE VIEW v1
 23 AS
 24 SELECT stuname,majorname
 25 FROM stuinfo s
 26 INNER JOIN major m ON s.`majorid`= m.`id`;
 27 
 28 SELECT * FROM v1 WHERE stuname LIKE "张%";
 29 
 30 
 31 #一、创建视图
 32 
 39 USE myemployees;
 40 
 41 #1.查询姓名中包含a字符的员工名、部门名和工种信息
 42 #①创建
 43 CREATE VIEW myv1
 44 AS
 45 
 46 SELECT last_name,department_name,job_title
 47 FROM employees e
 48 JOIN departments d ON e.department_id  = d.department_id
 49 JOIN jobs j ON j.job_id  = e.job_id;
 50 
 51 
 52 #②使用
 53 SELECT * FROM myv1 WHERE last_name LIKE "%a%";
 54 
 55 
 56 
 57 
 58 
 59 
 60 #2.查询各部门的平均工资级别
 61 
 62 #①创建视图查看每个部门的平均工资
 63 CREATE VIEW myv2
 64 AS
 65 SELECT AVG(salary) ag,department_id
 66 FROM employees
 67 GROUP BY department_id;
 68 
 69 #②使用
 70 SELECT myv2.`ag`,g.grade_level
 71 FROM myv2
 72 JOIN job_grades g
 73 ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 74 
 75 
 76 
 77 #3.查询平均工资最低的部门信息
 78 
 79 SELECT * FROM myv2 ORDER BY ag LIMIT 1;
 80 
 81 #4.查询平均工资最低的部门名和工资
 82 
 83 CREATE VIEW myv3
 84 AS
 85 SELECT * FROM myv2 ORDER BY ag LIMIT 1;
 86 
 87 
 88 SELECT d.*,m.ag
 89 FROM myv3 m
 90 JOIN departments d
 91 ON m.`department_id`=d.`department_id`;
 92 
 93 
 94 
 95 
 96 #二、视图的修改
 97 
 98 #方式一：
 99 
105 SELECT * FROM myv3 
106 
107 CREATE OR REPLACE VIEW myv3
108 AS
109 SELECT AVG(salary),job_id
110 FROM employees
111 GROUP BY job_id;
112 
113 #方式二：
114 
121 ALTER VIEW myv3
122 AS
123 SELECT * FROM employees;
124 
125 #三、删除视图
126 
127 
131 
132 DROP VIEW emp_v1,emp_v2,myv3;
133 
134 
135 #四、查看视图
136 
137 DESC myv3;
138 
139 SHOW CREATE VIEW myv3;
140 
141 
142 #五、视图的更新
143 
144 CREATE OR REPLACE VIEW myv1
145 AS
146 SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
147 FROM employees;
148 
149 CREATE OR REPLACE VIEW myv1
150 AS
151 SELECT last_name,email
152 FROM employees;
153 
154 
155 SELECT * FROM myv1;
156 SELECT * FROM employees;
157 #1.插入
158 
159 INSERT INTO myv1 VALUES("张飞","zf@qq.com");
160 
161 #2.修改
162 UPDATE myv1 SET last_name = "张无忌" WHERE last_name="张飞";
163 
164 #3.删除
165 DELETE FROM myv1 WHERE last_name = "张无忌";
166 
167 #具备以下特点的视图不允许更新
168 
169 
170 #①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
171 
172 CREATE OR REPLACE VIEW myv1
173 AS
174 SELECT MAX(salary) m,department_id
175 FROM employees
176 GROUP BY department_id;
177 
178 SELECT * FROM myv1;
179 
180 #更新
181 UPDATE myv1 SET m=9000 WHERE department_id=10;
182 
183 #②常量视图
184 CREATE OR REPLACE VIEW myv2
185 AS
186 
187 SELECT "john" NAME;
188 
189 SELECT * FROM myv2;
190 
191 #更新
192 UPDATE myv2 SET NAME="lucy";
193 
194 
195 
196 
197 
198 #③Select中包含子查询
199 
200 CREATE OR REPLACE VIEW myv3
201 AS
202 
203 SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
204 FROM departments;
205 
206 #更新
207 SELECT * FROM myv3;
208 UPDATE myv3 SET 最高工资=100000;
209 
210 
211 #④join
212 CREATE OR REPLACE VIEW myv4
213 AS
214 
215 SELECT last_name,department_name
216 FROM employees e
217 JOIN departments d
218 ON e.department_id  = d.department_id;
219 
220 #更新
221 
222 SELECT * FROM myv4;
223 UPDATE myv4 SET last_name  = "张飞" WHERE last_name="Whalen";
224 INSERT INTO myv4 VALUES("陈真","xxxx");
225 
226 
227 
228 #⑤from一个不能更新的视图
229 CREATE OR REPLACE VIEW myv5
230 AS
231 
232 SELECT * FROM myv3;
233 
234 #更新
235 
236 SELECT * FROM myv5;
237 
238 UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
239 
240 
241 
242 #⑥where子句的子查询引用了from子句中的表
243 
244 CREATE OR REPLACE VIEW myv6
245 AS
246 
247 SELECT last_name,email,salary
248 FROM employees
249 WHERE employee_id IN(
250     SELECT  manager_id
251     FROM employees
252     WHERE manager_id IS NOT NULL
253 );
254 
255 #更新
256 SELECT * FROM myv6;
257 UPDATE myv6 SET salary=10000 WHERE last_name = "k_ing";