mysql总结(3)-视图

mysql总结（数据库版本：8.0.21）

注意：mysql客户端(navicat15)， 新建查询执行以下语句

视图说明
1> 视图的列可以来自不同的表
2> 视图是由基本表（实表）产生的表（虚表）
3> 视图的建立和删除不影响基本表
4> 对视图内容的更新（添加、删除和修改）影响基本表
5> 当视图来自多个基本表时，不允许添加和删除数据

准备 示例表、示例数据、示例视图
-- 水果表
CREATE TABLE t_product(
    id INT auto_increment PRIMARY KEY COMMENT '水果id',
    name VARCHAR(20) COMMENT '水果名称',
  price DECIMAL(10,2) COMMENT '水果价格'
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '水果表'

INSERT INTO t_product(name, price) VALUES('苹果', 6.99);
INSERT INTO t_product(name, price) VALUES('草莓', 12.99);
INSERT INTO t_product(name, price) VALUES('香蕉', 7.99);

--学生表
CREATE TABLE t_student(
    id INT auto_increment PRIMARY KEY COMMENT '学生id',
    name VARCHAR(40) COMMENT '学生姓名',
    sex VARCHAR(10) COMMENT '学生性别',
    group_id INT COMMENT '分组id'
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '学生表'

INSERT INTO t_student(name, sex, group_id)VALUES('Tom', '男', 1);
INSERT INTO t_student(name, sex, group_id)VALUES('Amy', '女', 2);
INSERT INTO t_student(name, sex, group_id)VALUES('Green', '男', 3);

--分组表
CREATE TABLE t_group(
    id INT auto_increment PRIMARY KEY COMMENT '分组id',
    name VARCHAR(20) COMMENT '分组名称'
)ENGINE=INNODB DEFAULT charset=utf8 COMMENT '分组表'

INSERT INTO t_group(name) VALUES('体育特长');
INSERT INTO t_group(name) VALUES('绘画特长');
INSERT INTO t_group(name) VALUES('英语特长');

创建视图
CREATE VIEW view_product
AS SELECT id, name FROM t_product;

调用视图（查询视图）
SELECT * FROM view_product;

创建各种视图（实际上是封装查询语句）
1、创建查询常量的视图，查看视图
CREATE VIEW view_test1 AS 
SELECT 3.1415926 as '圆周率';

select * from view_test1;

2、创建使用聚合函数（sum，min, max, count等）查询语句的视图
CREATE VIEW view_test2 AS
SELECT COUNT(NAME) as '学生数量' FROM t_student;

SELECT * FROM view_test2;

3、创建包含排序功能的视图
CREATE VIEW view_test3 AS
SELECT * FROM t_student ORDER BY id DESC;

SELECT * FROM view_test3;

4、创建包含内连接查询的视图
CREATE VIEW view_test4 AS
SELECT s.name FROM t_student s, t_group g
WHERE s.group_id = g.id AND g.id = 2;

SELECT * FROM view_test4;

5、创建实现表左外连接的视图（left join 和 right join）
CREATE VIEW view_test5 AS
SELECT s.name, g.name AS group_name FROM t_student s LEFT JOIN t_group g ON s.group_id = g.id
WHERE g.id = 3;

SELECT * FROM view_test5;

6、创建包含子查询语句的视图
CREATE VIEW view_test6 AS
SELECT s.name FROM t_student s WHERE s.group_id in(SELECT id FROM t_group);

SELECT * FROM view_test6;

7、创建包含记录联合的视图（union 和 union all）
CREATE VIEW view_test7 AS
SELECT id, name FROM t_student
UNION
SELECT id,name FROM t_group;

SELECT * FROM view_test7;

查看库中所有视图
SHOW TABLE STATUS FROM flyfish LIKE 'view_%';
查看视图定义信息
SHOW CREATE VIEW view_test7;
查看视图设计信息
DESC view_test7;

修改视图
ALTER VIEW view_test2 AS 
SELECT COUNT(NAME) as '学生数量' FROM t_student;

删除视图（可以删除多个，以逗号隔开）
DROP VIEW view_test7,view_test6;

利用视图操作基本表
准备：由水果表t_product创建出查询所有字段的视图view_product
由于上面已经创建view_product, 这里只是稍做修改
ALTER VIEW view_product AS
SELECT id, name, price FROM t_product;

1、添加
INSERT INTO view_product(name, price)VALUES('梨子', 12.3);
2、更新
UPDATE view_product SET price = 10.2 WHERE id = 4;
3、删除
DELETE FROM view_product WHERE id = 4;