mysql求数据库平均成绩视图_MySQL数据库视图
1. 修改、查询、删除记录时都会提⽰多少条记录被影响,但建表不会提⽰。当表的数据修改后反映到视图。
修改、查询、删除视图的命令跟建表时的⼀样。
视图是从⼀个或多个表(或视图)导出的表。视图是数据库的⽤户使⽤数据库的观点。可以根据他们的不同需求,在物理的数据库上定义他们对数据库所要求的数据结构,这种根据⽤户观点所定义的数据结构就是视图。
视图与表(有时为与视图区别,也称表为基本表――Base Table)不同,视图是⼀个虚表,即视图所对应的数据不进⾏实际存储,数据库中只存储视图的定义,对视图的数据进⾏操作时,系统根据视图的定义去操作与视图相关联的基本表。
视图⼀经定义以后,就可以像表⼀样被查询、修改、删除和更新。2.使⽤视图有下列优点:
(1)为⽤户集中数据,简化⽤户的数据查询和处理。有时⽤户所需要的数据分散在多个表中,定义视图可将它们集中在⼀起,从⽽⽅便⽤户的数据查询和处理。 www.2cto
(2)屏蔽数据库的复杂性。⽤户不必了解复杂的数据库中的表结构,并且数据库表的更改也不影响⽤户对数据库的使⽤。
(3)简化⽤户权限的管理。只需授予⽤户使⽤视图的权限,⽽不必指定⽤户只能使⽤表的特定列,也增加了安全性。
(4)便于数据共享。各⽤户不必都定义和存储⾃⼰所需的数据,可共享数据库的数据,这样同样的数据只需存储⼀次。
(5)可以重新组织数据以便输出到其他应⽤程序中。
3. 使⽤create view语句创建视图
语法格式:
CREATE [re replace] [algorithm = {undefined | merge | temptable}] view 视图名[(column_list)]
as select_statement [with [cascaded| local] check option]
例:create view v_xs
as select *from xs;(在xsdb库下创建)
|xsdb.xs(在飞xsdb库下建xsdb⾥的表的视图)
说明:
● column_list:要想为视图的列定义明确的名称,可使⽤可选的column_list⼦句,列出由逗号隔开的列名。column_list中的名称数⽬必须等于SELECT语句检索的列数。若使⽤与源表或视图中相同的列名时可以省略column_list。 www.2cto
● or replace:给定了OR REPLACE⼦句,语句能够替换已有的同名视图。
● algorithm⼦句:可选的ALGORITHM⼦句是对标准SQL的MySQL扩展,规定了MySQL的算法,算法会影响MySQL处理视图的⽅式。ALGORITHM可取3个值:MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM⼦句,默认算法是UNDEFINED(未定义的)。指定了MERGE选项,会将引⽤视图的语句的⽂本与视图定义合并起来,使得视图定义的某⼀部分取代语句的对应部分。MERGE算法要求视图中的⾏和基表中的⾏具有⼀对⼀的关系,如果不具有该关系,必须使⽤临时表取⽽代之。指定了TEMPTABLE选项,视图的结果将被置于临时表中,然后使⽤它执⾏语句。
● select_statement:⽤来创建视图的SELECT语句,可在SELECT语句中查询多个表或视图。但对S
ELECT语句有以下的限制:
(1)定义视图的⽤户必须对所参照的表或视图有查询(即可执⾏SELECT语句)权限;
(2)不能包含FROM⼦句中的⼦查询;
(3)不能引⽤系统或⽤户变量;
(4)不能引⽤预处理语句参数;
(5)在定义中引⽤的表或视图必须存在;
(6)若引⽤不是当前数据库的表或视图时,要在表或视图前加上数据库的名称;
(7)在视图定义中允许使⽤ORDER BY,但是,如果从特定视图进⾏了选择,⽽该视图使⽤了具有⾃⼰ORDER BY的语句,则视图定义中的ORDER BY将被忽略。
(8)对于SELECT语句中的其他选项或⼦句,若视图中也包含了这些选项,则效果未定义。例如,如果在视图定义中包含LIMIT⼦句,⽽SELECT语句使⽤了⾃⼰的LIMIT⼦句,MySQL对使⽤哪个LIMIT未做定义。
● WITH CHECK OPTION:指出在可更新视图上所进⾏的修改都要符合select_statement所指定的限制条件,这样可以确保数据修改后,仍可通过视图看到修改的数据。当视图是根据另⼀个视图定义的时,WITH CHECK OPTION给出两个参数:LOCAL和CASCADED。它们决定了检查测试的范围。Local关键字使CHECK OPTION只对定义的视图进⾏检查,cascaded则会对所有视图进⾏检查。如果未给定任⼀关键字,默认值为CASCADED。
4. 注意,使⽤视图时,要注意下列事项:
mysql中delete语句(1)在默认情况下,将在当前数据库创建新视图。要想在给定数据库中明确创建视图,创建时,应将名称指定为db_name.view_name。
www.2cto
(2)视图的命名必须遵循标志符命名规则,不能与表同名,且对每个⽤户视图名必须是唯⼀的,即对不同⽤户,即使是定义相同的视图,也必须使⽤不同的名字。
(3)不能把规则、默认值或触发器与视图相关联。
(4)不能在视图上建⽴任何索引,包括全⽂索引。
5.例1: 假设当前数据库是TEST,创建XSCJ数据库上的CS_KC视图,包括计算机专业各学⽣的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。
CREATEOR REPLACE VIEW XSCJ.CS_KC
AS SELECT XS.学号,课程号,成绩
FROMXSCJ.XS, XSCJ.XS_KC
WHERE XS.学号 = XS_KC.学号 AND XS.专业名 = '计算机' WITH CHECK OPTION;
例2: 查平均成绩在80分以上的学⽣的学号和平均成绩。
本例⾸先创建学⽣平均成绩视图XS_KC_AVG,包括学号(在视图中列名为num)和平均成绩(在视图中列名为score_avg)。
www.2cto
创建学⽣平均成绩视图XS_KC_AVG:
CREATEVIEW XS_KC_AVG ( num,score_avg )
AS SELECT 学号, AVG(成绩)
FROMXS_KC GROUP BY 学号;
再对XS_KC_AVG视图进⾏查询。
SELECT* FROM XS_KC_AVG
WHEREscore_avg>=80;
从以上两例可以看出,创建视图可以向最终⽤户隐藏复杂的表连接,简化了⽤户的SQL程序设计。
注意:使⽤视图查询时,若其关联的基本表中添加了新字段,则该视图将不包含新字段。例如,视图CS_XS中的列关联了XS表中所有列,若XS表新增了“籍贯”字段,那么CS_XS视图中将查询不到“籍贯”字段的数据。
如果与视图相关联的表或视图被删除,则该视图将不能再使⽤。
6. 可更新视图
要通过视图更新基本表数据,必须保证视图是可更新视图,即可以在INSET、UPDATE或DELETE等
语句当中使⽤它们。对于可更新的视图,在视图中的⾏和基表中的⾏之间必须具有⼀对⼀的关系。还有⼀些特定的其他结构,这类结构会使得视图不可更新。
如果视图包含下述结构中的任何⼀种,那么它就是不可更新的:
www.2cto
(1)聚合函数;
(2)DISTINCT关键字;
(3)GROUP BY⼦句;
(4)ORDER BY⼦句;
(5)HAVING⼦句;
(6)UNION运算符;
(7)位于选择列表中的⼦查询;
(8)FROM⼦句中包含多个表;
(9)SELECT语句中引⽤了不可更新视图;
(10)WHERE⼦句中的⼦查询,引⽤FROM⼦句中的表;
(11)ALGORITHM 选项指定为TEMPTABLE(使⽤临时表总会使视图成为不可更新的)。
7. 插⼊数据
使⽤INSERT语句通过视图向基本表插⼊数据
例: 创建视图CS_XS,视图中包含计算机专业的学⽣信息,并向CS_XS视图中插⼊⼀条记录:('081255','李牧','计算机',1,'1990-10-21',50,NULL,NULL)。
⾸先创建视图CS_XS:(以下的删除、修改都是⽤该表)
CREATEOR REPLACE VIEW CS_XS
www.2cto
AS SELECT* FROM XS
WHERE 专业名 = '计算机' WITH CHECK OPTION;
注意:在创建视图的时候加上WITH CHECK OPTION⼦句,是因为WITH CHECK OPTION⼦句会在更新数据的时候检查新数据是否符合视图定义中WHERE⼦句的条件。WITH CHECKOPTION⼦句只能和可更新视图⼀起使⽤。
接下来插⼊记录:
INSERTINTO CS_XS
VALUES('081255', '李牧', '计算机', 1, '1990-10-14',50, NULL, NULL);
注意:这⾥插⼊记录时专业名只能为“计算机”。
这时,使⽤SELECT语句查询CS_XS视图和基本表XS,就可发现XS表中该记录已经被添加。
当视图所依赖的基本表有多个时,不能向该视图插⼊数据,因为这将会影响多个基本表。例如,不能向视图CS_KC插⼊数据,因为CS_KC 依赖两个基本表:XS和XS_KC。
对INSERT语句还有⼀个限制:SELECT语句中必须包含FROM⼦句中指定表的所有不能为空的列。例如,若CS_XS视图定义的时候不加上“姓名”字段,则插⼊数据的时候会出错。
www.2cto
8. 修改数据
使⽤UPDATE语句可以通过视图修改基本表的数据
例: 将CS_XS视图中所有学⽣的总学分增加8。
UPDATECS_XS SET 总学分 = 总学分+ 8;
该语句实际上是将CS_XS视图所依赖的基本表XS中所有记录的总学分字段值在原来基础上增加8。
若⼀个视图依赖于多个基本表,则⼀次修改该视图只能变动⼀个基本表的数据。
例: 将CS_KC视图中学号为081101的学⽣的101课程成绩改为90。
UPDATECS_KC SET 成绩=90
WHERE 学号='081101' AND 课程号='101';
本例中,视图CS_KC依赖于两个基本表:XS和XS_KC,对CS_KC视图的⼀次修改只能改变学号(源于XS表)或者课程号和成绩(源于
XS_KC表)。
以下的修改是错误的:
UPDATECS_KC SET 学号='081120',课程号='208'
www.2cto
WHERE 成绩=90;
9. 删除数据
使⽤DELETE语句可以通过视图删除基本表的数据
例: 删除CS_XS中⼥同学的记录。
DELETEFROM CS_XS WHERE 性别 = 0;
注意:对依赖于多个基本表的视图,不能使⽤DELETE语句。例如,不能通过对CS_KC视图执⾏DELETE语句⽽删除与之相关的基本表XS 及XS_KC表的数据。
10.使⽤ALTER语句可以对已有视图的定义进⾏修改。
语法格式:
ALTER[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
ALTERVIEW语句的语法和CREATE VIEW类似
例: 将CS_XS视图修改为只包含计算机专业学⽣的学号、姓名和总学分。
USEXSCJ;
ALTERVIEW CS_XS
AS SELECT 学号,姓名,总学分 FROM XS
WHERE 专业名 = '计算机';
11. 使⽤SQL语句删除视图
www.2cto
语法格式:
dropVIEW [IF EXISTS] 视图名1 [,视图名2]...
[RESTRICT | CASCADE]
声明了IF EXISTS,若视图不存在的话,也不会出现错误信息。也可以声明restrict和cascade,但它们没什么影响。使⽤DROP VIEW⼀次可删除多个视图。例如:
DROP VIEW CS_KC, CS_XS;将删除视图CS_KC和CS_XS。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论