修改mysql视图可以采用关键字_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 SELECTstuname,majorname18 FROMstuinfo s19 INNER JOIN major m ON s.`majorid`=m.`id`20 WHERE s.`stuname` LIKE '张%';21

22 CREATE VIEWv123 AS

24 SELECTstuname,majorname25 FROMstuinfo s26 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 as36 查询语句;37

38 */

39 USEmyemployees;40

41 #1.查询姓名中包含a字符的员工名、部门名和工种信息42 #①创建43 CREATE VIEWmyv144 AS

45

46 SELECTlast_name,department_name,job_title47 FROMemployees e48 JOIN departments d ON e.department_id =d.department_id49 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 VIEWmyv264 AS

65 SELECT AVG(salary) ag,department_id66 FROMemployees67 GROUP BYdepartment_id;68

69 #②使用70 SELECTmyv2.`ag`,g.grade_level71 FROMmyv272 JOINjob_grades g73 ON myv2.`ag` BETWEEN g.`lowest_sal` ANDg.`highest_sal`;74

75

76

77 #3.查询平均工资最低的部门信息78

79 SELECT * FROM myv2 ORDER BY ag LIMIT 1;80

81 #4.查询平均工资最低的部门名和工资82

83 CREATE VIEWmyv384 AS

85 SELECT * FROM myv2 ORDER BY ag LIMIT 1;86

87

88 SELECT d.*,m.ag89 FROMmyv3 m90 JOINdepartments d91 ON m.`department_id`=d.`department_id`;92

93

94

95

96 #二、视图的修改97

98 #方式一：99 /*

100 create or replace view 视图名101 as102 查询语句;103

104 */

105 SELECT * FROMmyv3106

107 CREATE OR REPLACE VIEWmyv3108 AS

109 SELECT AVG(salary),job_id110 FROMemployees111 GROUP BYjob_id;112

113 #方式二：114 /*

115 语法：116 alter view 视图名117 as118 查询语句;119

120 */

121 ALTER VIEWmyv3122 AS

123 SELECT * FROMemployees;124

125 #三、删除视图126

127 /*

128

129 语法：drop view 视图名,视图名,...;130 */

131

132 DROP VIEWemp_v1,emp_v2,myv3;133

134

135 #四、查看视图136

137 DESCmyv3;138

139 SHOW CREATE VIEWmyv3;140

141

142 #五、视图的更新143

144 CREATE OR REPLACE VIEWmyv1145 AS

146 SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"147 FROMemployees;148

149 CREATE OR REPLACE VIEWmyv1150 AS

151 SELECTlast_name,email152 FROMemployees;153

154

155 SELECT * FROMmyv1;156 SELECT * FROMemployees;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 VIEWmyv1173 AS

174 SELECT MAX(salary) m,department_id175 FROMemployees176 GROUP BYdepartment_id;177

178 SELECT * FROMmyv1;179

180 #更新181 UPDATE myv1 SET m=9000 WHERE department_id=10;182

183 #②常量视图184 CREATE OR REPLACE VIEWmyv2185 AS

186

187 SELECT 'john'NAME;188

189 SELECT * FROMmyv2;190

191 #更新192 UPDATE myv2 SET NAME='lucy';193

194

195

196

197

198 #③Select中包含子查询199

200 CREATE OR REPLACE VIEWmyv3201 AS

202

203 SELECT department_id,(SELECT MAX(salary) FROMemployees) 最高工资204 FROMdepartments;205

206 #更新207 SELECT * FROMmyv3;208 UPDATE myv3 SET 最高工资=100000;209

210

211 #④join

212 CREATE OR REPLACE VIEWmyv4213 AS

214

215 SELECTlast_name,department_name216 FROMemployees e217 JOINdepartments d218 ON e.department_id =d.department_id;219

220 #更新221

222 SELECT * FROMmyv4;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 VIEWmyv5230 AS

231

232 SELECT * FROMmyv3;233

234 #更新235

236 SELECT * FROMmyv5;237

238 UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;239

240

241

242 #⑥where子句的子查询引用了from子句中的表243

244 CREATE OR REPLACE VIEWmyv6245 AS

246

247 SELECTlast_name,email,salary248 FROMemployees249 WHERE employee_id IN(250 SELECTmanager_id251 FROMemployees252 WHERE manager_id IS NOT NULL

253 );254

255 #更新256 SELECT * FROMmyv6;257 UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';