按照功能分为:
第一步创建数据库:
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类型,需要什么数据传什么值就可以了。