1 #视图
2 /*
3 含义:虚拟表,和普通表一样使用
4 mysql5.1版本出现的新特性,是通过表动态生成的数据
5
6 比如:舞蹈班和普通班级的对比
7 创建语法的关键字 是否实际占用物理空间 使用
8
9 视图 create view 只是保存了sql逻辑 增删改查,只是一般不能增删改
10
11 表 create table 保存了数据 增删改查
12
13
14 */
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 /*
33 语法:
34 create view 视图名
35 as
36 查询语句;
37
38 */
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 /*
100 create or replace view 视图名
101 as
102 查询语句;
103
104 */
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 /*
115 语法:
116 alter view 视图名
117 as
118 查询语句;
119
120 */
121 ALTER VIEW myv3
122 AS
123 SELECT * FROM employees;
124
125 #三、删除视图
126
127 /*
128
129 语法:drop view 视图名,视图名,...;
130 */
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';