MySQL视图的创建、修改、删除

MySQL创建视图和查看视图（CREATE VIEW/SELECT VIEW）

创建基于单表的视图

mysql> CREATE VIEW view_students_info
    -> AS SELECT * FROM tb_students_info;

mysql> SELECT * FROM view_students_info;

mysql> CREATE VIEW v_students_info
    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    -> AS SELECT id,name,dept_id,age,sex,height,login_date
    -> FROM tb_students_info;

mysql> SELECT * FROM v_students_info;

创建基于多表的视图

mysql> CREATE VIEW v_students_info
    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    -> AS SELECT id,name,dept_id,age,sex,height,login_date
    -> FROM tb_students_info，tb_departments;

mysql> SELECT * FROM v_students_info;

查询视图结构：

DESCRIBE 视图名；

mysql> DESCRIBE v_students_info;

MySQL修改视图（ALTER VIEW）

mysql> ALTER VIEW view_students_info
    -> AS SELECT id,name,age
    -> FROM tb_students_info;

使用 UPDATE 语句更新视图 view_students_info，数据表里面的内容也会改变，跟表的update的效果一样

mysql> UPDATE view_students_info
    -> SET age=25 WHERE id=1;

修改视图名称

修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

MySQL删除视图（DORP VIEW）

mysql> DROP VIEW IF EXISTS v_students_info;