python物业管理系统_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类型，需要什么数据传什么值就可以了。

投诉建议表(待添加)