mysql视图详细笔记自律即自由
1#视图 2/*3含义：虚拟表，和普通表一样使用
4mysql5.1版本出现的新特性，是通过表动态生成的数据
5
6比如：舞蹈班和普通班级的对比
7 创建语法的关键字 是否实际占用物理空间 使用
8
9视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
10
11表 create table 保存了数据 增删改查
12
13
14*/
15
16#案例：查询姓张的学生名和专业名
17SELECT stuname,majorname
18FROM stuinfo s
19INNERJOIN major m ON s.`majorid`= m.`id`
20WHERE s.`stuname` LIKE"张%";
21
22CREATEVIEW v1
23AS
24SELECT stuname,majorname
25FROM stuinfo s
26INNERJOIN major m ON s.`majorid`= m.`id`;
27
28SELECT*FROM v1 WHERE stuname LIKE"张%";
29
30
31#一、创建视图
32/*
33语法：
34create view 视图名
35as
36查询语句;
37
38*/
39USE myemployees;
40
41 #1.查询姓名中包含a字符的员工名、部门名和工种信息
42#①创建
43CREATEVIEW myv1
44AS
45
46SELECT last_name,department_name,job_title
47FROM employees e
48JOIN departments d ON e.department_id = d.department_id
49JOIN jobs j ON j.job_id = e.job_id;
50
51
52#②使用
53SELECT*FROM myv1 WHERE last_name LIKE"%a%";
54
55
56
57
58
59
60 #2.查询各部门的平均工资级别
61
62#①创建视图查看每个部门的平均工资
63CREATEVIEW myv2
64AS
65SELECTAVG(salary) ag,department_id
66FROM employees
67GROUPBY department_id;
68
69#②使用
70SELECT myv2.`ag`,g.grade_level
71FROM myv2
72JOIN job_grades g
73ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
74
75
76
77 #3.查询平均工资最低的部门信息
78
79SELECT*FROM myv2 ORDERBY ag LIMIT 1;
80
81 #4.查询平均工资最低的部门名和工资
82
83CREATEVIEW myv3
84AS
85SELECT*FROM myv2 ORDERBY ag LIMIT 1;
86
87
88SELECT d.*,m.ag
89FROM myv3 m
90JOIN departments d
91ON m.`department_id`=d.`department_id`;
92
93
94
95
96#二、视图的修改
97
98#方式一：
99/*
100create or replace view 视图名
101as
102查询语句;
103
104*/
105SELECT*FROM myv3
106
107CREATEORREPLACEVIEW myv3
108AS
109SELECTAVG(salary),job_id
110FROM employees
111GROUPBY job_id;
112
113#方式二：
114/*
115语法：
116alter view 视图名
117as
118查询语句;
119
120*/
121ALTERVIEW myv3
122AS
123SELECT*FROM employees;
124
125#三、删除视图
126
127/*
128
129语法：drop view 视图名,视图名,...;
130*/
131
132DROPVIEW emp_v1,emp_v2,myv3;
133
134
135#四、查看视图
136
137DESC myv3;
138
139 SHOW CREATEVIEW myv3;
140
141
142#五、视图的更新
143
144CREATEORREPLACEVIEW myv1
145AS
146SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
147FROM employees;
148
149CREATEORREPLACEVIEW myv1
150AS
151SELECT last_name,email
152FROM employees;
153
154
155SELECT*FROM myv1;
156SELECT*FROM employees;
157 #1.插入
158
159INSERTINTO myv1 VALUES("张飞","zf@qq.com");
160
161 #2.修改
162UPDATE myv1 SET last_name ="张无忌"WHERE last_name="张飞";
163
164 #3.删除
165DELETEFROM myv1 WHERE last_name ="张无忌";
166
167#具备以下特点的视图不允许更新
168
169
170 #①包含以下关键字的sql语句：分组函数、distinct、groupby、having、union或者union all
171
172CREATEORREPLACEVIEW myv1
173AS
174SELECTMAX(salary) m,department_id
175FROM employees
176GROUPBY department_id;
177
178SELECT*FROM myv1;
179
180#更新
181UPDATE myv1 SET m=9000WHERE department_id=10;
182
183#②常量视图
184CREATEORREPLACEVIEW myv2
185AS
186
187SELECT"john" NAME;
188
189SELECT*FROM myv2;
190
191#更新
192UPDATE myv2 SET NAME="lucy";
193
194
195
196
197
198#③Select中包含子查询
199
200CREATEORREPLACEVIEW myv3
201AS
202
203SELECT department_id,(SELECTMAX(salary) FROM employees) 最高工资
204FROM departments;
205
206#更新
207SELECT*FROM myv3;
208UPDATE myv3 SET 最高工资=100000;
209
210
211 #④join
212CREATEORREPLACEVIEW myv4
213AS
214
215SELECT last_name,department_name
216FROM employees e
217JOIN departments d
218ON e.department_id = d.department_id;
219
220#更新
221
222SELECT*FROM myv4;
223UPDATE myv4 SET last_name ="张飞"WHERE last_name="Whalen";
224INSERTINTO myv4 VALUES("陈真","xxxx");
225
226
227
228#⑤from一个不能更新的视图
229CREATEORREPLACEVIEW myv5
230AS
231
232SELECT*FROM myv3;
233
234#更新
235
236SELECT*FROM myv5;
237
238UPDATE myv5 SET 最高工资=10000WHERE department_id=60;
239
240
241
242#⑥where子句的子查询引用了from子句中的表
243
244CREATEORREPLACEVIEW myv6
245AS
246
247SELECT last_name,email,salary
248FROM employees
249WHERE employee_id IN(
250SELECT manager_id
251FROM employees
252WHERE manager_id ISNOTNULL
253);
254
255#更新
256SELECT*FROM myv6;
257UPDATE myv6 SET salary=10000WHERE last_name ="k_ing";
以上是 mysql视图详细笔记自律即自由 的全部内容， 来源链接： utcz.com/z/534812.html
