视图的创建

浅浅的更一篇

往下看叭

 

 创建goods表，并插入5条记录

mysql> create table goods(
    -> id int(11) primary key auto_increment,
    -> type varchar(30),
    -> name varchar(30),
    -> price decimal(7,2),
    -> num int(11),
    -> add_time datetime
    -> );
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> insert into goods values(1,"书籍","西游记",50.4,20,20180101124040),
    ->                         (2,"糖类","牛奶糖",7.5,200,20180202134040),
    ->                         (3,"糖类","水果糖",2.5,100,20180203134040),
    ->                         (4,"服饰","休闲西装",800,null,20180204134040),
    ->                         (5,"饮品","果汁",3,70,20180205134040);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

 2.创建视图 （id,name）

mysql> create view v_table as select id,name from goods;
Query OK, 0 rows affected (0.02 sec)

 3.查看

mysql> select *from v_table;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 西游记       |
|  2 | 牛奶糖       |
|  3 | 水果糖       |
|  4 | 休闲西装     |
|  5 | 果汁         |
+----+--------------+
5 rows in set (0.00 sec)

 4.视图呈现显示其他名字（改名字）

mysql> create view v_table2(goods_id,goods_name) as select id,name from goods;
Query OK, 0 rows affected (0.02 sec)

 5.查看修改后视图呈现的名字

mysql> select *from v_table2;
+----------+--------------+
| goods_id | goods_name   |
+----------+--------------+
|        1 | 西游记       |
|        2 | 牛奶糖       |
|        3 | 水果糖       |
|        4 | 休闲西装     |
|        5 | 果汁         |
+----------+--------------+
5 rows in set (0.00 sec)

 6.创建orders表并插入数据

mysql> create table orders(
    -> o_id int(11) primary key,
    -> add_time date,
    -> goods_id int(11)
    -> );
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> insert into orders values(1,20190402,3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders values(2,20190403,1),
    -> (3,20190405,5),
    -> (4,20190401,1),
    -> (5,20190403,15),
    -> (6,20190403,4);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

 7.以goods表和orders表为基，创建视图

mysql> create view v_table3(o_id,name) as select orders.o_id,goods.name
    -> from goods join orders on goods.id=orders.o_id;
Query OK, 0 rows affected (0.02 sec)

 8.查看视图

mysql> desc v_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

 所有视图的详细信息都存在系统数据库information_schema下的views 表中，通过views表，可以查看数据库所有视图详细新信息

 9.改一下视图里的内容

mysql> create or replace view v_table as select id,name,price from goods;
Query OK, 0 rows affected (0.02 sec)

mysql> desc v_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | NO   |     | 0       |       |
| name  | varchar(30)  | YES  |     | NULL    |       |
| price | decimal(7,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)