工具猿之MySQL教程-08-关于视图操作

视图操作-01

认识视图  视图特性  创建视图  查看视图  修改视图  删除视图

认识视图

从用户的角度来看，视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，视图是由select语句查询定义的虚拟表。从数据库外部来看，视图就如同一张表，即对表进行的增删改查操作都可以应用于视图。
视图是一张虚拟表，定义视图所引用的表称为基本表。试图的作用类似于筛选，定义视图的筛选可以来自当前或者其他数据库的一个或多个表或其它的视图。
视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样又在数据库中存储一份，通过视图看到的数据只是存放在基本表中的数据。
当对视图看到的数据进行修改时，相应的基本表的数据也要发生变化。若基本表的数据发生变化，则这种变化也可以自动地反映到试图中。

视图特性

1、简单性。看到的就是需要的。
2、安全性。用户通过视图只能查询和修改他们所能看到的数据，但不能授权到数据库特定行和特定列上。通过视图，用户可以被限制在数据的不同子集上，使用权限可被限制在另一个视图的一个子集上，或是一些视图和基表合并后的子集上。

创建视图

注意事项：
  1、要求具有针对视图的 create view 权限，以及针对由 select 语句选择的每一列上的某些权限。对于在 select 语句中其他地方使用的列，必须具有 select 权限。如果还有 or replace 子句，必须在视图上具有 drop 权限。
  2、在试图定义中命名的表必须存在，视图必须具有唯一的列名，不得有重复，就像基本表那样。
  3、视图名不能与表同名。
  4、在试图的 from 子句中不能使用子查询。
  5、在视图的 select 语句中不能引用系统或者用户变量。
  6、在视图的 select 语句中不能引用预处理语句参数。
  7、在试图定义中允许使用 order by ，但是，如果从特定视图进行选择，而该视图使用了具有自己 order by 的语句，它将被忽略。
  8、在定义中引用的表或视图必须存在。但是，创建视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可以使用 check table 语句。
  9、在定义中不能引用 temporary 表，不能创建 temporary 视图。
  10、不能将触发的程序与视图关联在一起。
1、基于一个基本表创建视图。
  a、在 StuMS 数据库中创建视图 view_course 。

mysql> create or replace view view_course as select course_num,course_name from course;

  b、在 test 数据库中创建基于StuMS数据库中的 students 表的视图 view_students 。

mysql> create or replace view view_students
    -> as select * from StuMS.students;

2、基于多个基本表创建视图。
  a、创建视图 view_sca，包括学号、课程名、成绩 。

mysql> create view view_sca(学号,课程名,成绩)
    -> as select students.stu_num,course_name,course_report
    -> from students,course,achievement
    -> where students.stu_num=achievement.stu_num
    -> and achievement.course_num=course.course_num;

3、基于视图创建视图。
  a、创建视图 view_view_sca，统计包括某位学生某门课的总成绩、平均成绩。

mysql> create view view_view_sca
    -> as select 学号,课程名,sum(成绩),avg(成绩)
    -> from view_sca
    -> group by 学号;

当我们想要查看视图时，出现 ‘this is incompatible with sql_mode=only_full_group_by’ 报错。
问题原因：
MySQL 启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。

用sql语句查询 sql_mode 的值：

mysql> select @@global.sql_mode;

查询结果为：

去掉ONLY_FULL_GROUP_BY，重新设置值，
退出 MySQL，编辑 /etc/my.cnf 文件，在 [mysqld] 下加入一行：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后退出，重启MySQL

进入MySQL，查询 sql_mode，这里就没有了 ONLY_FULL_GROUP_BY 。

然后在查询视图

mysql> select * from view_view_sca;

查看视图

1、查看已创建视图

mysql> show tables;

2、查看视图结构

mysql> desc view_view_sca;

3、查看视图定义

mysql> show create view view_view_sca;

4、查看视图数据列表

mysql> select * from view_sca;

修改视图

1、修改视图 view_students ，用 as 定义别名，并增加 ‘人数’ 字段，统计各个院系男女人数。

mysql> alter view StuMS.view_students
    -> as select dep_num as 院系,stu_sex as 性别,count(*) as 人数
    -> from StuMS.students
    -> group by dep_num,stu_sex;

删除视图

1、删除 view_course 视图

mysql> drop view view_course;