第一章 视图

-- 创建表
create table students(
id INT PRIMARY KEY AUTO_INCREMENT,-- 主键且自增长
sname varchar(15), -- 姓名
gender varchar(2), -- 性别
age int,       -- 年龄
hometown varchar(80)  -- 住址
);

INSERT INTO students VALUES (NULL,'杨过','男',28,'绝情谷');
INSERT INTO students VALUES (NULL,'小龙女','女',28,'绝情谷');
INSERT INTO students VALUES (NULL,'郭靖','男',38,'襄阳');
INSERT INTO students VALUES (NULL,'黄蓉','女',28,'襄阳');
INSERT INTO students VALUES (NULL,'金轮法王','男',38,'吐蕃');

-- 创建视图[语法1] CREATE VIEW 视图名 AS SELECT 语句
CREATE VIEW stu_v1 AS SELECT sname,hometown FROM students;
-- 创建视图[语法2] CREATE OR REPLACE VIEW 视图名 AS SELECT 语句；
CREATE OR REPLACE VIEW stu_v1 AS SELECT * FROM students;

-- 查询视图
SELECT * FROM stu_v1;

--  怎么使用视图过滤不想要的数据[年龄大于28]
CREATE VIEW stu_v2 AS SELECT * FROM students WHERE age >28;

-- 更新试图
CREATE VIEW stu_v3 AS SELECT count(*)  num FROM students;
-- 删除视图里面的数据
DELETE FROM stu_v3 WHERE num = 4;

CREATE VIEW stu_v4 AS SELECT sname,hometown FROM students;

-- 修改视图语法
-- ALTER VIEW 视图名 AS SELECT 语句
ALTER VIEW stu_v4 AS SELECT * FROM students;

-- 删除视图语法  DROP VIEW 视图名
-- 删除视图4
DROP VIEW stu_v4;

-- 查看创建视图的语句 SHOW CREATE VIEW 视图名；
SHOW CREATE VIEW stu_v3;
-- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_v3` AS select count(0) AS `num` from `students`

-- 视图嵌套
CREATE VIEW stu_v5 AS SELECT * FROM stu_v1;