SQL 数据库里是否可以通过视图更改表中的数据?

2011-09-14 11:23呱呱的蛙  分类：数据库DB | 浏览
 6809 次
书上说"视图被看成是虚拟表,它并不代表任何的物理数据,只是用来查看数据的视窗而已。数据库中只储存视图的定义，并不是以一组数据的形式储存在数据库中"。“无论在什么时候修改试图的数据，实际上都是在修改对应的数据表中的数据”。这两句话不矛盾么？我查过百度了,有些说可以改,有些说不可以改,那到底行不行呢? 小弟刚开始学习SQL，请大虾为我解答一下，尽量详细点 ~



分享到：







2011-09-14 12:32


#行家新兵训练营，缤纷好礼等你赢！#

提问者采纳




对于没有复杂处理的 单表 视图, 是可以 UPDATE 的。
例如这样的视图
CREATE VIEW  v_1  AS  SELECT * FROM 某表

但是 假如你的视图， 是 单表的， 带统计的， 那恐怕是不能 直接 UPDATE 了。
例如这样的视图
CREATE VIEW v_2  AS  SELECT  id,  COUNT(*)  as num  FROM 某表

--- 前面是单表的， 下面说多表的 ---

对于2张表的，假如你的查询里面， 这2张表是 一对一的关系。 且视图里面， 包含了主键，那么是可以 UPDATE 的。
例如下面这样的 视图
CREATE VIEW temp_view AS
SELECT
  t1.name  AS  t1Name,
  t1.age   AS  t1Age,
  t2.name  AS  t2Name,
  t2.age   AS  t2Age
FROM
  t1, t2
WHERE
  t1.name = t2.name
 
然后直接更新视图。
UPDATE temp_view  SET  t1Age = t2Age;

完整的例子， 可参考
http://hi.baidu.com/wangzhiqing999/blog/item/767be28f634ddb00c8fc7aba.html

但是假如你的视图里面的 2个表， 不是一一对应的。
或者是带了 SUM / COUNT 等统计函数的， 那么可能是没办法更新了。

提问者评价

谢谢你的简单而又容易理解例子~
也谢谢全部回答我的人了

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';

数据窗口的多表更新（文/Kukoc）

原理分析：

数据窗口数据源来自两个或两个以上的表，相当于多个表连接建立的一个视图，对于这种数据窗口，PB默认是不能修改的。当然我们可以通过设置它的Update 属性，数据窗口的Update Properties用来设置数据窗口是否可Update、可Update的表、可Update列等，但不能同时设置两个表可更新；所以当修改它的数据项时，我们不能简单地用dw_1.update()来更新table，我们可以在程序中设置数据窗口可更新的一个表A（及其可更新列），其他表为不可更新，更新完表A后，再设置另一表B为可更新，表A设置为不可更新，依次类推。

解决示例：

我们以PowerBuilder7自带的ASA6数据库为例：

1、新建一个数据窗口d_grid_dep_emp，它的Select语句为

SELECT department.dept_id, 

        department.dept_name,

        employee.emp_id, 

        employee.emp_fname,

        employee.emp_lname

FROM department, employee

where employee.dept_id = department.dept_id


2、设置数据窗口d_grid_dep_emp的属性，将列的taborder改为非0值；并点击菜单Rows——>Update Properties，设置此数据窗口Allow Updates，Table to Update设为department，Updateable Columns为department.dept_id，department.dept_name。


3、在窗口中更新数据窗口按钮的clicked事件编写脚本：

    long ll_rtn

// 修改Department表(Department表在第2步已设置为可更新)

ll_rtn = dw_1.update(true, false)


if ll_rtn = 1 then

    //关闭对Department表的修改

    dw_1.Modify("department_dept_name.Update = 'No'")

    dw_1.Modify("department_dept_id.Update = 'No'")

    dw_1.Modify("department_dept_id.Key = 'No'")

    

    //设置Employee表成为新的可修改表

    dw_1.Modify("DataWindow.Table.UpdateTable = 'employee'")     

    dw_1.Modify("employee_emp_id.Update = 'Yes'")

    dw_1.Modify("employee_emp_fname.Update = 'Yes'")

    dw_1.Modify("employee_emp_lname.Update = 'Yes'")

    dw_1.Modify("employee_emp_id.Key = 'Yes'")

    

    //修改Employee表

    ll_rtn = dw_1.Update()

    IF ll_rtn = 1 THEN

            COMMIT USING SQLCA;

            dw_1.retrieve()

            messagebox('提示信息','更新成功！')

    ELSE

            ROLLBACK USING SQLCA;

            MessageBox('提示信息', '更新失败！')

    END IF


    //重设修改标志    

    dw_1.Modify("department_dept_name.Update = 'Yes'")

    dw_1.Modify("department_dept_id.Update = 'Yes'")

    dw_1.Modify("department_dept_id.Key = 'Yes'")

    dw_1.Modify("DataWindow.Table.UpdateTable = 'department'")    

    dw_1.Modify("employee_emp_id.Update = 'No'")

    dw_1.Modify("employee_emp_fname.Update = 'No'")

    dw_1.Modify("employee_emp_lname.Update = 'No'")

    dw_1.Modify("employee_emp_id.Key = 'No'")

ELSE

    ROLLBACK USING SQLCA;

    MessageBox('提示信息', '更新失败！')

END IF
    这样就完成了对两个表的更新。当然我们可以将上面功能编成一个函数，在需要时调用。
→柯建勋 PB编程俱乐部

1 视图
视图实际上就是一个查询封装，或者更简单你可以认为是别名，类似动态库，只有你使用时才会给你生成结果集到内存，它只会保存生成该结果的语句，而结果是不会被保存到物理内存的。


视图不算难，一般我们只需要知道视图如何创建和修改即可。而视图的更新一般是不允许的，因为会影响到原表，所以我们对视图的更新不需要太看重，但是需要了解。
#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比

		创建语法的关键字	是否实际占用物理空间	使用
视图	create view			只是保存了sql逻辑		增删改查，只是一般不能增删改
表		create table		保存了数据				增删改查


*/

#案例：查询姓张的学生名和专业名
#以前做法
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';
#视图做法
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
SELECT * FROM v1 WHERE stuname LIKE '张%';

#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/
USE myemployees;

#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';


#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资
#①创建视图，获取平均最低工资和其部门id
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
#②使用，获取平均工资最低的部门信息(部门名，工资)和最低工资(可不要)
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;


#二、视图的修改
#方式一：
/*
create or replace view  视图名
as
查询语句;
*/
SELECT * FROM myv3 
#案例
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;

#三、删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2;


#四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;


#五、视图的更新
#注意：视图的更新是对数据进行改变，例如某个字段的值，或者添加行数。
#而上面视图的修改是对视图的结构进行改变，是整个查询语句的改变而非数据。
#并且不建议对视图进行更新。

#创建视图用于测试
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;

#注意：下面对更新视图会影响到原表
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

#具备以下特点的视图不允许更新
#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;#更新失败

#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
#更新
UPDATE myv2 SET NAME='lucy';#更新失败

#③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;#更新失败


#④join(92语法的连接也不能更新即",")
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;
#更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';#成功。
INSERT INTO myv4 VALUES('陈真','xxxx');#失败。注：join可以update，但不能insert，所以也叫不能更新


#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;#更新失败


#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
	SELECT  manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);
#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';#更新失败