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类型,需要什么数据传什么值就可以了。
投诉建议表(待添加)
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Python零基础学习代码实践 —— 打印5位数的回文数并统计个数
代码: num = 10000 sum = 0 while num <= 99999: a = num % 10 b = num // 10000 c = num % 1000 // 10 d = (num - (num//10000)*10000)//1000 if a == b and c == d: print(num) sum += 1 num += 1 print("5位数的回文数个数为:"sum) 执行结果: 10001 11011 12021 13031 14041 15051 16061 17071 18081 19091 20002 21012 22022 23032 24042 25052 26062 27072 28082 29092 30003 31013 32023 33033 34043 35053 36063 37073 38083 39093 40004 41014 42024 43034 44044 45054 46064 47074 48084 49094 50005 51015 52025 53035 54045 55055 56065 57...
- 下一篇
Java图形化:布局方式
布局方式 FlowLayout:流布局 BorderLayout:边框布局 GridLayout:网格布局 FlowLayout(流布局) 像Word打字,组件从左向右排列,一列排满后自动换下一行。组件默认居中对齐,可以设置左/右对齐。流布局会维持组件的原始大小。流布局是JPanel(面板)的默认布局。容器可以使用setLayout()方法改变布局。 示例代码: import java.awt.FlowLayout; import javax.swing.JButton; import javax.swing.JFrame; public class TestLowLayout { public static void main(String[] args) { JFrame jf = new JFrame("流布局Demo"); FlowLayout fl = new FlowLayout(); jf.setLayout(fl); JButton jb1 = new JButton("按钮1"); JButton jb2 = new JButton("按钮2"); jf.add(jb...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS关闭SELinux安全模块
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- CentOS7安装Docker,走上虚拟化容器引擎之路
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- CentOS8编译安装MySQL8.0.19
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Hadoop3单机部署,实现最简伪集群