Java开发物业管理系统：数据库表单设计
按照功能分为： 用户表 费用表 投诉建议表 第一步创建数据库： CREATE DATABASE yellowstar; 用户表 创建用户表 表名：yw_users CREATE TABLE IF NOT EXISTS yw_users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT 'UID', username VARCHAR(20) NOT NULL UNIQUE comment '用户名', password CHAR(32) NOT NULL COMMENT '密码：md5加密', user_type TINYINT UNSIGNED NOT NULL COMMENT '用户类型', create_time DATE NOT NULL COMMENT '创建时间' ); type字段说明： 1:超级管理员 2:物业 3:业主 插入测试数据 INSERT INTO yw_users (username,password,user_type,create_time) VALUES ('user1',123456,1,20181101), ('user2',123456,2,20181201), ('user3',123456,3,20181230); 业主房产信息表 房产信息表 表名：yw_houses CREATE TABLE IF NOT EXISTS yw_houses ( hid INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '房产号', username VARCHAR(20) NOT NULL comment '房产持有人', house_type VARCHAR(20) NOT NULL comment '房产类型', area DOUBLE UNSIGNED NOT NULL COMMENT '住房面积', buy_date DATE NOT NULL COMMENT '购房时间', building_no VARCHAR(20) NOT NULL comment '楼号', unit VARCHAR(10) NOT NULL comment '小区单元', card_id INT UNSIGNED NOT NULL COMMENT '门牌号', phone VARCHAR(11) NOT NULL comment '手机号码', ); 插入测试数据 INSERT INTO yw_houses (username,house_type,area,buy_date,building_no,unit,card_id,phone) VALUES ('张三','三室一厅',100,'2019-01-01','1号楼','一单元',902,'158****9177'), ('李四','三室二厅二卫',130,'2017-01-01','8号楼','二单元',102,'152****9127'), ('冠希','一室一厅',50,'2018-01-01','9号楼','一单元',401,'151****9147'); 费用表 费用单价表 表名：yw_perprice CREATE TABLE IF NOT EXISTS yw_perprice ( id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '单价编号', p_type INT UNSIGNED NOT NULL comment '单价类型', price DECIMAL(18,2) UNSIGNED NOT NULL comment '单价', p_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '定价时间' ); p_type说明: 1:水费 2:电费 3:燃气费 4:停车费 5:物业费 插入测试数据 INSERT INTO yw_perprice (p_type,price) VALUES (1,3.45), (2,0.5), (3,1.64), (4,5), (5,50); 水费、电费、燃气费、物业费表 表名：yw_totalprice CREATE TABLE IF NOT EXISTS yw_totalprice ( id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY COMMENT '编号', hid INT UNSIGNED NOT NULL COMMENT '外键房产id', p_type INT UNSIGNED NOT NULL comment '单价类型', price DECIMAL(18,2) UNSIGNED NOT NULL comment '单价', amount DECIMAL(18,2) UNSIGNED NOT NULL comment '用量', money DECIMAL(18,2) UNSIGNED NOT NULL comment '总额', buy_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '消费时间', FOREIGN KEY(hid) REFERENCES yw_houses(hid) ); 注意：这里使用了外键 插入测试数据 INSERT INTO yw_totalprice (hid,p_type,amount,price,money) VALUES (1,1,30,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),30*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,1,21,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),21*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,2,40,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),40*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,2,34,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),34*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,3,23,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),23*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,4,22,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),22*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,4,12,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,5,12,(SELECT price FROM yw_perprice WHERE p_type=5 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (1,5,12,(SELECT price FROM yw_perprice WHERE p_type=5 LIMIT 1),12*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,1,23,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),23*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,1,56,(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1),56*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,2,16,(SELECT price FROM yw_perprice WHERE p_type=2 LIMIT 1),16*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,3,15,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),15*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,3,90,(SELECT price FROM yw_perprice WHERE p_type=3 LIMIT 1),90*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,4,44,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),44*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)), (2,4,46,(SELECT price FROM yw_perprice WHERE p_type=4 LIMIT 1),46*(SELECT price FROM yw_perprice WHERE p_type=1 LIMIT 1)) ; 注意：这里插入数据使用了子查询 查询讲解：查询所有业主电费： SELECT hid as '业主物业ID',price as '单价',amount as '用量',money as '金额',buy_date as '时间' FROM yw_totalprice WHERE p_type = 1; 查询所有业主电费(增加显示业主姓名)： SELECT username as '业主物业姓名',price as '单价',amount as '用量',money as '金额',a.buy_date as '时间' FROM yw_totalprice AS A JOIN yw_houses AS b ON a.hid = b.hid WHERE p_type = 1; 知识点：这里使用了表连接 查询某业主的电费：在where里增加一个限定条件就可以了 SELECT username as '业主物业姓名',price as '单价',amount as '用量',money as '金额',a.buy_date as '时间' FROM yw_totalprice AS A JOIN yw_houses AS b ON a.hid = b.hid WHERE p_type = 1 AND a.hid = 1; 其他费用类推，基本就是改改p_type类型，需要什么数据传什么值就可以了。 投诉建议表（待添加）
