【前文】
在Windows环境下使用命令行控制Mysql(一,基础操作):
http://blog.csdn.net/m0_38021128/article/details/68484633前文中介绍了在windows环境下使用命令行控制Mysql的基础操作,本文将通过一个报销流程项目的实例,来进行更详细的学习。
项目要求
- 定义一个简单的报销流程:
提交报销单 –》 审批报销单 –》 财务确认报销单 —》 报销单支付
- 一张报销单可能包括多个报销项,先暂时认为报销项有4类,交通费,餐费,办公用品,其他。
- 报销单支付目前暂时不考虑实际功能,仅作为一个手工的流程环节。
- 所有用户都可以提交报销单。
- 报销单由上级领导审核(通过组织机构获取)。
- 如果报销单的单笔金额大于2000元,或者某个月的某个用户的累计生效报销金额大于20000元,需要有两级审批。即除了直属领导外,还需要由领导的领导审批。
当前任务
设计并在mysql中初始化报销流程设计的两张表:报销记录、报销项记录
实施步骤
- 设计表单
报销记录/ExpenseClaimRecords
序号/ECno | 项目名称/Project_name | 日期/Date | 申请人/Applicant |
---|---|---|---|
1 | |||
2 | |||
3 |
报销项记录/ExpenseClaimDetails
序号/ECno | 交通费/Transportation | 餐费/Meals | 办公用品/Office_Stationery | 其他/Others | 合计/Total |
---|---|---|---|---|---|
1 | |||||
2 | |||||
3 |
- 在Mysql中建立这两张表
//使用名为jeesite的数据库
//因为本项目是基于jeesite框架的,不过阅读本日志并不需要了解jeesite
mysql> use jeesite
Database changed
//创建报销项记录表,报错
mysql> create table ExpenseClaimDetails(
-> ECno int,
-> Transportation money NOT NULL,
-> Meals money NOT NULL,
-> Office-Stationery money NOT NULL,
-> Others money NOT NULL,
-> Total money NOT NULL,
-> CONSTRAINT key_number PRIMARY KEY(ECno),
-> CONSTRAINT key_range CHECK (ECno>0)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'money NOT NULL,
Meals money NOT NULL,
Office-Stationery money NOT NULL,
Others m' at line 3
//错误问题1、money应该用decimal或者numeric类型来定义
//错误问题2、“Office-Stationery”应该为“Office_Stationery”
//检查后重新建表,成功
mysql> create table ExpenseClaimDetails(
-> ECno int,
-> Transportation decimal(6,2) NOT NULL,
-> Meals decimal(6,2) NOT NULL,
-> Office_Stationery decimal(6,2) NOT NULL,
-> Others decimal(6,2) NOT NULL,
-> Total decimal(6,2) NOT NULL,
-> CONSTRAINT key_number PRIMARY KEY(ECno),
-> CONSTRAINT key_range CHECK (ECno>0)
-> );
Query OK, 0 rows affected (0.05 sec)
//创建报销记录表
mysql> create table ExpenseClaimRecords(
-> ECno int NOT NULL,
-> Project_name varchar(30) NOT NULL,
-> Date date NOT NULL,
-> CONSTRAINT key_number FOREIGN KEY(ECno) REFERENCES ExpenseClaimDetails(ECno)
-> );
Query OK, 0 rows affected (0.04 sec)
//查看建好的表单是否正确
mysql> show tables;
+---------------------+
| Tables_in_jeesite |
+---------------------+
| act_evt_log |
| act_ge_bytearray |
| act_ge_property |
| act_hi_actinst |
| act_hi_attachment |
| act_hi_comment |
| act_hi_detail |
| act_hi_identitylink |
| act_hi_procinst |
| act_hi_taskinst |
| act_hi_varinst |
| act_id_group |
| act_id_info |
| act_id_membership |
| act_id_user |
| act_procdef_info |
| act_re_deployment |
| act_re_model |
| act_re_procdef |
| act_ru_event_subscr |
| act_ru_execution |
| act_ru_identitylink |
| act_ru_job |
| act_ru_task |
| act_ru_variable |
| cms_article |
| cms_article_data |
| cms_category |
| cms_comment |
| cms_guestbook |
| cms_link |
| cms_site |
| expenseclaimdetails | //这里报销项记录表
| expenseclaimrecords | //这里报销记录表
| gen_scheme |
| gen_table |
| gen_table_column |
| gen_template |
| oa_leave |
| oa_notify |
| oa_notify_record |
| oa_test_audit |
| sys_area |
| sys_dict |
| sys_log |
| sys_mdict |
| sys_menu |
| sys_office |
| sys_role |
| sys_role_menu |
| sys_role_office |
| sys_user |
| sys_user_role |
| test_data |
| test_data_child |
| test_data_main |
| test_tree |
+---------------------+
57 rows in set (0.00 sec)
mysql> describe expenseclaimrecords;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ECno | int(11) | NO | MUL | NULL | |
| Project_name | varchar(30) | NO | | NULL | |
| Date | date | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe expenseclaimdetails;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| ECno | int(11) | NO | PRI | NULL | |
| Transportation | decimal(6,2) | NO | | NULL | |
| Meals | decimal(6,2) | NO | | NULL | |
| Office_Stationery | decimal(6,2) | NO | | NULL | |
| Others | decimal(6,2) | NO | | NULL | |
| Total | decimal(6,2) | NO | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
可以看到两张表已经初始化建立成功了,在mysql目录下也可以找到它们。