1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
# 创建视图
CREATE VIEW v1 AS SELECT * FROM t_book;
CREATE VIEW v2 AS SELECT bookName,price FROM t_book;
CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;
SELECT * FROM v1;
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;
SELECT * FROM v4;
DESC v5;
SHOW TABLE STATUS LIKE 'v5';
SHOW TABLE STATUS LIKE 't_book';
SHOW CREATE VIEW v5;
# 修改视图的两种方法
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;
|
