mysql視圖詳細筆記

  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('張飛','[email protected]');
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';