导读：之前，在“数据和云”公众号发表过一篇文章《如何使用SQL计算宝宝每次吃奶的时间间隔（分析函数的使用）》，本文为续篇，如何提升程序的易用性。
因为我工作繁忙，时常不能及时帮助媳妇儿记录，为了让不懂数据库的媳妇儿也可以自己用手机熟练操作。我继续做了一些修正和封装：
1.给媳妇儿的手机下载一个terminal终端软件termius，实现只需打开app就可以连接到环境；
2.跟媳妇儿进一步沟通需求，按她能够接受的使用习惯来修正并封装程序，原则是尽可能的简化操作。
先来看下改进后最终的使用效果：
--1.手机上打开termius软件，点击进入到终端，输入 h 可以看到所有命令的说明：
[oracle@jystdrac1 ~]$ h**Command for Baby's feed_time:**Usage: v - View Today's Result. vv <'mmdd'> - View one day's Result. i - Insert a row using current time. ii <'mmdd hh24:mi'> - Insert a row using input time. d <id> - Delete a mistake row by id. u <label> <id> - Update a mistake row by id. h - Help
--2.输入 v 可以看到今天的喂奶时间和间隔：
[oracle@jystdrac1 ~]$ vView Today's Result:
ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.01
8 rows selected.
--3.输入 i 可以直接以当前系统时间插入一条数据，并显示插入后的结果：
[oracle@jystdrac1 ~]$ iInsert a row using current time:1 row created.Commit complete.View Today's Result: ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.01 94 12-15 23:02 N 227 3.789 rows selected.
--4.输入 d <id> 可以删除id对应的那行记录，并显示删除后的结果：
[oracle@jystdrac1 ~]$ d 94Delete a mistake row by id:Enter value for id: old 1: delete from t_baby where id = &idnew 1: delete from t_baby where id = &idnew 1: delete from t_baby where id = 941 row deleted.Commit complete.View Today's Result: ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.018 rows selected.
--5.输入 u <label> <id> 可以更新指定id的label值，比如将id=55的记录label值修改为'L'，代表这次喂奶量很少，不参与计算
[oracle@jystdrac1 ~]$ u l 55Update a mistake row by id:Enter value for label: Enter value for id: old 1: update t_baby set label = upper('&label') where id = &idnew 1: update t_baby set label = upper('l') where id = 551 row updated.Commit complete.View Today's Result: ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 56 12-15 10:40 N 392 6.53 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.017 rows selected.
--6.输入 u <label> <id> 可以更新指定id的label值，比如将id=55的记录label值修改回'N',代表这次喂奶量正常，参与计算
[oracle@jystdrac1 ~]$ u n 55Update a mistake row by id:Enter value for label: Enter value for id: old 1: update t_baby set label = upper('&label') where id = &idnew 1: update t_baby set label = upper('n') where id = 551 row updated.Commit complete.View Today's Result: ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.018 rows selected.
--7.输入 ii <'mmdd hh24:mi'> 可以插入指定日期和时间
[oracle@jystdrac1 ~]$ ii '1215 23:00'Insert a row using input time(mmdd mi:ss) eg: 1215 10:00Enter value for feed_time: old 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi'))new 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1215 23:00','mmdd hh24:mi'))1 row created.Commit complete.View Today's Result: ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.01 95 12-15 23:00 N 225 3.749 rows selected.
--8.删除id=95的这条测试记录
[oracle@jystdrac1 ~]$ d 95Delete a mistake row by id:Enter value for id: old 1: delete from t_baby where id = &idnew 1: delete from t_baby where id = 95
1 row deleted.
Commit complete.
View Today's Result:
ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 53 12-15 01:00 N 140 2.33 54 12-15 04:08 N 188 3.13 55 12-15 07:35 N 207 3.45 56 12-15 10:40 N 185 3.08 69 12-15 13:16 N 156 2.6 70 12-15 14:32 N 76 1.27 82 12-15 17:14 N 163 2.71 83 12-15 19:15 N 121 2.01
8 rows selected.
--9.使用 vv 'mmdd' 可以显示指定日期的喂奶间隔情况：
[oracle@jystdrac1 ~]$ vv 1214View Today's Result:Enter value for feed_time: Enter value for feed_time: old 2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time >= to_date('&feed_time','mmdd') and feed_time < to_date('&feed_time','mmdd') + 1new 2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time >= to_date('1214','mmdd') and feed_time < to_date('1214','mmdd') + 1
ID FEED_TIME L LAG(min) LAG(h)---------- ----------- - ---------- ---------- 41 12-14 01:55 N 265 4.42 42 12-14 04:58 N 183 3.05 43 12-14 08:25 N 207 3.45 44 12-14 11:23 N 178 2.97 45 12-14 12:23 N 60 1 46 12-14 15:00 N 157 2.62 47 12-14 17:22 N 142 2.37 48 12-14 18:45 N 83 1.38 49 12-14 22:40 N 235 3.92
9 rows selected.
经过一番演示，媳妇儿的反馈是非常满意的，实际她最常用的还是v和i，非常方便，其他命令偶尔使用，其实只需记住h可以获取到帮助即可。下面将本次的修正和封装过程记录一下：
1.环境变量配置别名
2.系统shell脚本
3.底层SQL文本
1. 环境变量配置别名
为了简化操作，我将环境变量设置一些别名。
[oracle@jystdrac1 ~]$ cat ~/.bash_profile # 这里省略原有环境变量内容,下面为在源文件基础上追加的内容：# Alias for baby's feed_time:alias h=/home/oracle/baby_readme.shalias v=/home/oracle/baby_view.shalias i=/home/oracle/baby_insert.shalias d=/home/oracle/baby_delete.shalias u=/home/oracle/baby_update.shalias ii=/home/oracle/baby_insert_diy.shalias vv=/home/oracle/baby_view_diy.sh
2. 系统shell脚本
##baby_readme.shvi baby_readme.sh#!/bin/bash# script:baby_readme.sh# version:1.01#-------------------------------------------------------------echo "**Command for Baby's feed_time:**"echo "Usage:"echo " v - View Today's Result."echo " vv <'mmdd'> - View one day's Result."echo " i - Insert a row using current time."echo " ii <'mmdd hh24:mi'> - Insert a row using input time."echo " d <id> - Delete a mistake row by id."echo " u <label> <id> - Update a mistake row by id."echo " h - Help"#-------------------------------------------------------------##baby_view.shvi baby_view.sh#!/bin/bash# script:baby_view.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_insert.shvi baby_insert.sh#!/bin/bash# script:baby_insert.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Insert a row using current time:@i1PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_delete.shvi baby_delete.sh#!/bin/bash# script:baby_delete.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Delete a mistake row by id:@d1$1commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_insert_diy.shvi baby_insert_diy.sh#!/bin/bash# script:baby_insert_diy.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Insert a row using input time(mmdd mi:ss) eg: 1215 10:00@i2$1commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_update.shvi baby_update.sh#!/bin/bash# script:baby_update.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Update a mistake row by id:@u1$1$2commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_view_diy.shvi baby_view_diy.sh#!/bin/bash# script:baby_view.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT View one day's Result:@v4$1$1EOF#-------------------------------------------------------------
3. 底层SQL文本
--echo "v - View Today's Result."[oracle@jystdrac1 ~]$ cat v3.sqlSELECT id, to_char(feed_time, 'mm-dd hh24:mi') "FEED_TIME", label, round((feed_time - l_time) * 24 * 60) "LAG(min)", round((feed_time - l_time) * 24, 2) "LAG(h)"FROM ( SELECT t.*, LAG(feed_time) OVER(ORDER BY id) l_time FROM t_baby t WHERE label <> 'L' )WHERE feed_time >= trunc(sysdate)/
-- echo " vv <'mmdd'> - View one day's Result."[oracle@jystdrac1 ~]$ cat v4.sql SELECT id, to_char(feed_time, 'mm-dd hh24:mi') "FEED_TIME", label, round((feed_time - l_time) * 24 * 60) "LAG(min)", round((feed_time - l_time) * 24, 2) "LAG(h)"FROM ( SELECT t.*, LAG(feed_time) OVER(ORDER BY id) l_time FROM t_baby t WHERE label <> 'L' )WHERE feed_time >= TO_DATE('&feed_time', 'mmdd') AND feed_time < TO_DATE('&feed_time', 'mmdd') + 1/
--echo "i - Insert a row using current time."[oracle@jystdrac1 ~]$ cat i1.sql insert into t_baby(id,feed_time) values(s1.nextval,sysdate)/commit/
--echo "ii - Insert a row using input time."[oracle@jystdrac1 ~]$ cat i2.sql insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi'))/
--echo "d - Delete a mistake row by id. "[oracle@jystdrac1 ~]$ cat d1.sql delete from t_baby where id = &id/
--echo "u - Update a mistake row by id."[oracle@jystdrac1 ~]$ cat u1.sql update t_baby set label = upper('&label') where id = &id/
SQL文本独立出来也方便后续需求有变化时快速更改。
墨天轮原文链接：https://www.modb.pro/db/22267（复制到浏览器中打开或者点击左下角的“阅读原文”）
推荐阅读：144页！分享珍藏已久的数据库技术年刊
点击下图查看更多 ↓
云和恩墨大讲堂 | 一个分享交流的地方
长按，识别二维码，加入万人交流社群
请备注：云和恩墨大讲堂
点个“在看”
你的喜欢会被看到❤