mysql（六）：mysql视图

1.比如一张表100个字段，我们只需要20个字段，这时可以用视图，使得操作简便化；

单表创建视图

例： CREATE VIEW v1 AS SELECT * FROM t_book; //创建t_book表的视图

CREATE VIEW v2 AS SELECT bookName,price FROM t_book;//从t_book表中取出两个字段创建视图

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;//给两个字段取别名

多表创建视图

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;  //给两张表创建视图

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;  //给两张表取名字

例：DESC v5;

    SHOW TABLE STATUS LIKE 'v5';

    SHOW TABLE STATUS LIKE 't_book';

    SHOW CREATE VIEW v5;

例：SELECT  * FROM v1;

         

      CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

         

          ALTER VIEW v1 AS SELECT * FROM t_book;

         

例： INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

       UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

       DELETE FROM v1 WHERE id=5;

例：DROP VIEW IF EXISTS v4;