mysql 视图-优快云博客

本文链接：https://blog.youkuaiyun.com/wsjzzcbq/article/details/127568891

文章讲述了如何在数据库中创建、查询、修改和删除视图。首先展示了创建字典表`w_dict`的SQL语句及插入数据，接着创建了一个名为`dict`的视图，然后演示了查询视图数据、查看视图创建语句以及两种修改视图的方法。最后，介绍了删除视图的语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

视图，是虚拟存在的表，视图中的数据在数据库中实际不存在，视图只保存查询SQL的逻辑，不保存查询结果

建表sql

DROP TABLE IF EXISTS `w_dict`;
CREATE TABLE `w_dict`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `label` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `value` int(0) NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sort` int(0) NULL DEFAULT NULL,
  `create_date` datetime(0) NULL DEFAULT NULL,
  `create_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_date` datetime(0) NULL DEFAULT NULL,
  `update_by` datetime(0) NULL DEFAULT NULL,
  `del_flag` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of w_dict
-- ----------------------------
INSERT INTO `w_dict` VALUES (1, '未支付', 1, 'payment', 1, '2022-10-28 03:46:15', NULL, NULL, NULL, 0);
INSERT INTO `w_dict` VALUES (2, '支付中', 2, 'payment', 2, '2022-10-28 03:49:20', NULL, NULL, NULL, 0);
INSERT INTO `w_dict` VALUES (3, '已支付', 3, 'payment', 3, '2022-10-28 03:49:20', NULL, NULL, NULL, 0);
INSERT INTO `w_dict` VALUES (4, '审核中', 1, 'approval', 1, '2022-10-28 03:49:20', NULL, NULL, NULL, 0);
INSERT INTO `w_dict` VALUES (5, '审核通过', 2, 'approval', 2, '2022-10-28 03:49:20', NULL, NULL, NULL, 0);
INSERT INTO `w_dict` VALUES (6, '审核驳回', 3, 'approval', 3, '2022-10-28 03:49:20', NULL, NULL, NULL, 0);

创建字典表 w_dict

创建视图 dict

create or replace view dict as select label, value, type, sort from w_dict

查询视图数据

select * from dict

查看视图创建语句

show create view dict

修改视图

方式1重新创建

添加创建时间 create_date 字段

create or replace view dict as select label, value, type, sort, create_date from w_dict

方式2

alter view dict as select label, value, type, sort, create_date from w_dict

删除视图

drop view if exists dict

至此完