视图,是虚拟存在的表,视图中的数据在数据库中实际不存在,视图只保存查询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
至此完