CREATE TABLE `admin` ?(
? `admin_id` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员编号',
? `admin_loginname` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员登录用户名',
? `admin_password` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员登录密码',
? `admin_username` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员姓名',
? PRIMARY KEY (`admin_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `power` ?(
? `power_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电表编号',
? `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
? `power_count` decimal(9, 2) NOT NULL COMMENT '电表跑数',
? `power_time` date NOT NULL COMMENT '电表时间',
? `power_status` tinyint(1) NOT NULL COMMENT '电费缴费状态',
? `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电费价格编号',
? `power_beforecount` decimal(9, 2) NOT NULL COMMENT '上月电表跑数',
? PRIMARY KEY (`power_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `price` ?(
? `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '价格编号',
? `price_time` date NOT NULL COMMENT '价格日期',
? `isactive` tinyint(1) NOT NULL COMMENT '价格状态',
? `waterprice` decimal(4, 2) NOT NULL COMMENT '水费价格',
? `powerprice` decimal(4, 2) NOT NULL COMMENT '电费价格',
? PRIMARY KEY (`price_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `rent` ?(
? `id` int(10) NOT NULL,
? `admin_id` varchar(3) NOT NULL COMMENT '管理员编号',
? `user_id` varchar(10) NOT NULL COMMENT '用户编号',
? `water_id` varchar(10) NOT NULL COMMENT '消费编号',
? `price_id` varchar(10) NOT NULL COMMENT '价格编号',
? `power_id` varchar(10) NOT NULL COMMENT '电费编号',
? `create_time` date NOT NULL DEFAULT now() COMMENT '添加时间',
? PRIMARY KEY (`admin_id`, `user_id`, `water_id`, `price_id`, `power_id`, `id`)
);
CREATE TABLE `users` ?(
? `user_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
? `user_loginname` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户登录名',
? `user_password` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户登录密码',
? `user_username` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
? `user_address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户地址',
? `user_phone` int(11) NOT NULL COMMENT '用户电话',
? `price_id` varchar(10) NULL,
? PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `water` ?(
? `water_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '水表编号',
? `userid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
? `water_count` decimal(9, 2) NOT NULL COMMENT '水表跑数',
? `water_time` date NOT NULL COMMENT '水表时间',
? `water_status` decimal(1, 0) NOT NULL COMMENT '水费缴费状态',
? `price_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '水费价格编号',
? `water_beforecount` decimal(9, 2) NOT NULL COMMENT '上月水表跑数',
? PRIMARY KEY (`water_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
ALTER TABLE `rent`ADD INDEX(`admin_id`);
ALTER TABLE `rent`ADD INDEX(`power_id`);
ALTER TABLE `rent`ADD INDEX(`price_id`);
ALTER TABLE `rent`ADD INDEX(`user_id`);
ALTER TABLE `rent`ADD INDEX(`water_id`); //防止出现：ERROR 1005: Can't create table (errno: 150)
ALTER TABLE `admin` ADD CONSTRAINT `fk_admin_rent_1` FOREIGN KEY (`admin_id`) REFERENCES `rent` (`admin_id`);
ALTER TABLE `power` ADD CONSTRAINT `fk_power_rent_1` FOREIGN KEY (`power_id`) REFERENCES `rent` (`power_id`);
ALTER TABLE `price` ADD CONSTRAINT `fk_price_rent_1` FOREIGN KEY (`price_id`) REFERENCES `rent` (`price_id`);
ALTER TABLE `users` ADD CONSTRAINT `fk_users_rent_1` FOREIGN KEY (`user_id`) REFERENCES `rent` (`user_id`);
ALTER TABLE `water` ADD CONSTRAINT `fk_water_rent_1` FOREIGN KEY (`water_id`) REFERENCES `rent` (`water_id`);
CREATE VIEW `用户缴费情况` AS SELECT DISTINCT
?? ?rent.id,?
?? ?rent.create_time,?
?? ?users.user_username,?
?? ?water.water_count,?
?? ?water.water_time,?
?? ?water.water_status,?
?? ?water.water_beforecount,?
?? ?price.waterprice,?
?? ?price.powerprice,?
?? ?power.power_count,?
?? ?power.power_time,?
?? ?power.power_status,?
?? ?power.power_beforecount
FROM
?? ?rent,
?? ?users,
?? ?water,
?? ?price,
?? ?power
WHERE
?? ?rent.user_id = users.user_id AND
?? ?rent.price_id = price.price_id;
———————————————————————————————————————————
好像外键设反了，作如下修改
ALTER TABLE `test`.`power` DROP FOREIGN KEY `fk_power_rent_1`;
ALTER TABLE `test`.`water` DROP FOREIGN KEY `fk_water_rent_1`;
ALTER TABLE `test`.`admin` DROP FOREIGN KEY `fk_admin_rent_1`;
ALTER TABLE `test`.`price` DROP FOREIGN KEY `fk_price_rent_1`;
ALTER TABLE `test`.`users` DROP FOREIGN KEY `fk_users_rent_1`;
ALTER TABLE `test`.`rent`?
ADD CONSTRAINT `fk_power_rent_1` FOREIGN KEY (`power_id`) REFERENCES `test`.`power` (`power_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_water_rent_1` FOREIGN KEY (`water_id`) REFERENCES `test`.`water` (`water_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_admin_rent_1` FOREIGN KEY (`admin_id`) REFERENCES `test`.`admin` (`admin_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_price_rent_1` FOREIGN KEY (`price_id`) REFERENCES `test`.`price` (`price_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT `fk_users_rent_1` FOREIGN KEY (`user_id`) REFERENCES `test`.`users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
