单表视图
# 原始表
CREATE TABLE t (quantity INT, price INT);
INSERT INTO t VALUES(3, 50);
INSERT INTO t VALUES(5, 560);
# 基于原始表,创建视图:view_t
CREATE VIEW view_t AS SELECT quantity, price, quantity *price total FROM t;
#仅一个查询,就获得乘积
SELECT * FROM view_t;
#基于原始表,创建视图:view_t2
CREATE VIEW view_t2(quantity, price, minus ) AS SELECT quantity, price, quantity - price FROM t;
#仅一个查询就获得差值
SELECT * FROM view_t2;
多表视图
是一张虚拟表,操作上与真实表一样
作用:类似存储过程,简化操作
#示例数据表
CREATE TABLE student
(
s_id INT,
name VARCHAR(40)
);
CREATE TABLE stu_info
(
s_id INT,
glass VARCHAR(40),
addr VARCHAR(90)
);
INSERT INTO student VALUES(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
INSERT INTO stu_info VALUES(1, 'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
#在表student和表stu_info上创建视图stu_glass,展示分班信息:id,姓名,班级:
CREATE VIEW stu_glass
AS SELECT student.s_id id,student.name name,stu_info.glass glass
FROM student ,stu_info
WHERE student.s_id=stu_info.s_id;
SELECT * FROM stu_glass;
查看视图
DESCRIBE view_t;
#SHOW TABLE STATUS命令查看视图信息,代码如下:
SHOW TABLE STATUS LIKE 'view_t' \G;
SHOW TABLE STATUS LIKE 't' \G;
#SHOW CREATE VIEW查看视图的详细定义,代码如下:
SHOW CREATE VIEW view_t \G;
#在views表中查看视图的详细定义,代码如下:
SELECT * FROM information_schema.views \G;
修改视图
#修改视图view_t,代码如下:
DESC view_t;
CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;
DESC view_t;
#使用ALTER语句修改视图view_t,代码如下:
DESC view_t;
ALTER VIEW view_t AS SELECT quantity FROM t;
DESC view_t;
更新视图
- 实际上是对原始表的更新,会对基于原始表的所有视图产生影响
#使用UPDATE语句更新视图view_t:
SELECT * FROM view_t; /*查看更新之前的视图*/
SELECT * FROM t; /*查看更新之前的表*/
UPDATE view_t SET quantity=5; /*更新视图*/
SELECT * FROM t; /*查看更新之后的表*/
SELECT * FROM view_t; /*查看更新之后的视图*/
SELECT * FROM view_t2;
# 从视图插入时注意:含运算列,不能插入,如下语句,不能插入:
INSERT INTO view_t VALUES (3,5);
# 使用INSERT语句在基本表t中插入一条记录,代码如下:
INSERT INTO t VALUES (3,5);
SELECT * FROM t;
SELECT * FROM view_t2;
# 删除stu_glass视图,代码如下:
DROP VIEW IF EXISTS stu_glass;
SHOW CREATE VIEW stu_glass;