MySQL 视图之创建、查看、修改

MySQL 视图之创建、查看、修改

视图是从数据库中一个或多个表中导出来的虚拟表，方便用户对数据的操作。

数据库中只有数据的定义，没有存放视图的数据，那些数据是在原来的表里。

视图具有这些作用：数据安全；数据表与应用程序独立；简化用户的操作。

前提：查询用户是否有select和create view的权限。

/*我有两个mysql，当我将WampSever开启后，命令行直接进入了这个5.6.17版本，而另一个设置了环境变量的5.5.28直接忽略了。吓死我了，开始以为发生了什么诡异事件。新建一个数据库mydb，并新建一张表，导入写好的数据（我懒得一条一条的插入了）。*/mysql> use mydb;Database changedmysql> select select_priv,create_view_priv from mysql.user where user='root';+-------------+------------------+| select_priv | create_view_priv |+-------------+------------------+| Y | Y || Y | Y || Y | Y |+-------------+------------------+3 rows in set (0.00 sec) mysql> create table pet (breed varchar(10), sum int);mysql> create table person (name varchar(10), age int);load data local infile "D:/c.csv" into table person character set 'utf8' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';mysql> create view person_view1(P_nm,P_ag) as select name,age from person;Query OK, 0 rows affected (0.11 sec)mysql> select * from person_view1;+----------+------+| P_nm | P_ag |+----------+------+| ?Stephen | 19 || Elena | 18 || Demon | 19 || David | 20 || jordan | 25 || James | 24 || Jane | 23 || Dannis | 23 || Rose | 27 | | NULL |+----------+------+10 rows in set (0.00 sec)/* 在MySql workBench里没有？乱码，为什么命令行会有。*//* 两张表的视图*/mysql> create algorithm=merge view per_pet(name,age,breed,sum) as select name,age,breed,sum from person,pet where person.age=pet.sum with local check option;Query OK, 0 rows affected (0.08 sec)mysql> select * from per_pet;+----------+------+--------+------+| name | age | breed | sum |+----------+------+--------+------+| ?Stephen | 19 | ?dog | 19 || Demon | 19 | ?dog | 19 || Elena | 18 | cat | 18 || ?Stephen | 19 | tiger | 19 || Demon | 19 | tiger | 19 || David | 20 | pig | 20 || jordan | 25 | beef | 25 || James | 24 | snake | 24 || Jane | 23 | monkey | 23 || Dannis | 23 | monkey | 23 || Jane | 23 | fish | 23 || Dannis | 23 | fish | 23 || Rose | 27 | cow | 27 |+----------+------+--------+------+13 rows in set (0.00 sec)/* 又一次刚刚那个问题。可是在workbench上能正常显示：# name, age, breed, sum'Stephen', '19', 'dog', '19''Demon', '19', 'dog', '19''Elena', '18', 'cat', '18''Stephen', '19', 'tiger', '19''Demon', '19', 'tiger', '19''David', '20', 'pig', '20''jordan', '25', 'beef', '25''James', '24', 'snake', '24''Jane', '23', 'monkey', '23''Dannis', '23', 'monkey', '23''Jane', '23', 'fish', '23''Dannis', '23', 'fish', '23''Rose', '27', 'cow', '27'cmd 窗口是ASCII gbk编码的，但是我set names gbk, utf8, latin1 全是？乱码。。*/

查看视图：

重要的三种语句：

describe；

show table status like；

show create view

mysql> desc per_pet;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name | varchar(10) | YES | | NULL | || age | int(11) | YES | | NULL | || breed | varchar(10) | YES | | NULL | || sum | int(11) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)mysql> show table status like 'per_pet'\G;*************************** 1. row *************************** Name: per_pet Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULLMax_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW1 row in set (0.00 sec)ERROR:No query specified/* 和下面的实表相比虚拟表的Engine，Rows等都是空值 */mysql> show table status like 'person'\G;*************************** 1. row *************************** Name: person Engine: InnoDB Version: 10 Row_format: Compact Rows: 10 Avg_row_length: 1638 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 6291456 Auto_increment: NULL Create_time: 2016-02-05 20:00:11 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:1 row in set (0.00 sec)ERROR:No query specifiedmysql> show create view per_pet\G;*************************** 1. row *************************** View: per_pet Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `per_pet` AS select `person`.`name` AS `name`,`person`.`age` AS `age`,`pet`.`breed` AS `breed`,`pet`.`sum` AS `sum` from (`person` join `pet`) where (`person`.`age` = `pet`.`sum`) WITH LOCAL CHECK OPTIONcharacter_set_client: latin1collation_connection: latin1_swedish_ci1 row in set (0.00 sec)ERROR:No query specified

修改视图：

create or replace view 此语句比较灵活，当视图不存在时可创建视图。

如果在创建视图时使用了with check option，with encryption，view_metadata 等选项，如果想要保留这些功能，修改时需要将他们包括进去。

mysql> create or replace algorithm=temptable view per_pet (p1,p2,w1,w2) as select name,age,breed,sum from person,pet;Query OK, 0 rows affected (0.06 sec)mysql> desc per_pet;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| p1 | varchar(10) | YES | | NULL | || p2 | int(11) | YES | | NULL | || w1 | varchar(10) | YES | | NULL | || w2 | int(11) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.02 sec)mysql> alter view per_pet(p1,w1,w2) as select name,breed,sum from person,pet where person.age=pet.sum;Query OK, 0 rows affected (0.08 sec)mysql> desc per_pet;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| p1 | varchar(10) | YES | | NULL | || w1 | varchar(10) | YES | | NULL | || w2 | int(11) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。