随笔2

24、SQLite中Oracle函数的替代方法

1) nvl => ifnull

nvl直接替换为ifnull即可

2) decode => case when then

Oracle

SELECT ID, DECODE(inParam, 'byComparedParam', '值1' , '值2') name FROM test_table;

--如果第一个参数 inParam == 'byComparedParam'，则 select 得到的 name 显示为值1
--如果第一个参数 inParam != 'byComparedParam'，则 select 得到的 name 显示为值2

SQLite

select report_code, year, month, day, wind_speed,
case
    when wind_speed >= 40 then 'HIGH'
    when wind_speed >= 30 and wind_speed < 40 then 'MODERATE'
    else 'LOW'
end as wind_severity
from station_data

3) to_char => strftime

Oracle

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

SQLite

select strftime('%Y-%m-%d %H:%M:%S', 'now') from t_b_database_basic t
--select strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') from t_b_database_basic t
select strftime('%Y-%m-%d %H:%M:%S', t.fcreate_time) from t_b_database_basic t

需要注意的是，假如转换为字符串的是代表当前时间的'now'，结果将会是utc时间，显示为当地时间需添加修饰符'localtime'，假如转换的是字段就不用担心这个问题，原因可能是因为内部处理的所有时间都默认为utc时间

4) to_date => datetime

Oracle

insert into t_timestamp_temp (create_time) values (to_date('2008-08-08 08:08:08' , 'yyyy-mm-dd hh24:mi:ss'));

SQLite

insert into t_timestamp_temp (create_time) values (datetime('2008-08-08 08:08:08'));
--insert into t_timestamp_temp (create_time) values (datetime('now', 'localtime'));

需要注意的是，转换为日期时默认处理的是utc时间，因此使用本地时间的固定字符串时不要添加修饰符'localtime'，否则时间会根据时区改变，只有在使用'now'时才需要添加'localtime'修饰符

5) listagg => group_concat

Oracle

--param_code排序并拼接为字符串
select listagg(d.fparam_code, ',') within group(order by d.fparam_code)
  from t_b_meter_detail d where d.fmeter_no = 1

SQLite

--param_code拼接为字符串，可分组，不可排序
select group_concat(d.fparam_code, ',')
  from t_b_meter_detail d
  group by d.fmeter_no

6) merge into => insert into (...) select ... from ...（merge into替代方法的其中一种）

Oracle

      merge into t_b_meter t using t_b_esubstation m
      on (upper(t.fparent_meter_no) = 'ROOT' and t.fmeter_no = m.fstation_code)
      when not matched then
      insert (fid, fparent_meter_no, fmeter_no, fmeter_mark, fguihao, fmeter_type_code, fmodule_code)
        values (seq_b_meter.nextval, 'ROOT', m.fstation_code, m.fstation_name, m.fstation_name, '0', '0')

SQLite
（when not matched部分）与Oracle的语法相比，需要将表名提到insert语句中，insert语句的字段部分保持不变，values部分直接替换为select，而select从句部分需要有where条件限制以验证using表的字段在merge into表中不存在

      insert into t_b_meter (fparent_meter_no, fmeter_no, fmeter_mark, fguihao, fmeter_type_code, fmodule_code)
      select 'ROOT', m.fstation_code, m.fstation_name, m.fstation_name, '0', '0' from t_b_esubstation m
        where m.fstation_code not in (select t.fmeter_no from t_b_meter t where upper(t.fparent_meter_no) = 'ROOT')

7) merge into => insert into (...) ; update ... (merge into 替代方法的另外一种)

Oracle

    merge into #{TableName} t using (select #{CabinetCode} fguihao from dual) s on (s.fguihao = t.fguihao)
      when matched then
        update set fmeter_no = #{MeterNo}, fcomm_error = #{CommError}
      when not matched then
        insert (fmeter_no, fguihao, fcomm_error) values (#{MeterNo}, #{CabinetCode}, #{CommError})

SQLite
当merge into中存在when not matched和when matched两部分，需拆分为insert和update语句两部分，其中insert对应when not matched部分，在上文小节6)中提到
（when matched部分）与Oracle的语法相比，需要将表名提到update语句中，update语句的字段部分保持不变，然后在已有基础上需要添加where从句限制以验证merge into表数据符合using表的条件

    insert into #{TableName} (fmeter_no, fguihao, fcomm_error)
    select #{MeterNo}, #{CabinetCode}, #{CommError} from dual
      where #{CabinetCode} not in (select t.fguihao from #{TableName} t);
    update #{TableName} set fmeter_no = #{MeterNo}, fcomm_error = #{CommError}
      where fguihao = #{CabinetCode,jdbcType=VARCHAR}

8) 查询所有表 user_tables => sqlite_master

Oracle

select table_name from user_tables
  order by table_name

SQLite

SELECT name table_name FROM sqlite_master
  WHERE type='table' --类型有table, trigger等
  ORDER BY name;

9) 查询表字段 user_tab_columns => pragma table_info(...)

Oracle

    select u.column_name, u.data_type, u.table_name from user_tab_columns u
      where u.table_name = '#{TableName}'
      order by u.column_name

SQLite

    pragma table_info('#{TableName}')

pragma table_info 结果

name对应column_name，type对应data_type

n) =>

Oracle

SQLite

25、SQLite小知识

1) 限制返回结果条数

select * from t_b_meter limit 20

26、Windows开机自动登录

1) 步骤

打开运行，运行命令：netplwiz

命令

取消下图中勾选框，点击应用并输入用户名密码，然后确定即可

界面
输入用户名密码

2) 假如没有勾选框

没有勾选框

运行命令regedit，打开注册表，在地址栏输入
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\PasswordLess\Device
然后回车，将DevicePasswordLessBuildVersion项的值改成0然后确定

修改值为0

修改完成后再重新进行步骤1)即可

27、BOOTP/DHCP配置工具一般配置步骤

1) 打开软件后，有些工具会自动搜索MAC地址，假如不支持就手动点击界面的搜索功能按钮

Ethernet IP Configuration
BootP DHCP Commissioning Tool
IP Setting Tool

2) 设置IP

2.1) Ethernet IP Configuration 工具

选中要设置的设备，点enable dhcp，再输入要设置的ip，然后点set ip configuration

Ethernet IP Configuration 操作步骤

2.2) BootP DHCP Commissioning Tool

点击“Add Relation”（或“Add to Relation List”），输入IP地址，点击“OK”

BootP DHCP Commissioning Tool 操作步骤

2.3) IP Setting Tool

选中设备，点击“IP地址设定”，在弹出窗口内输入IP地址，并点击“OK”

IP Setting Tool 操作步骤

3) 最好点disable bootp/dhcp，或者其它关闭BOOTP/DHCP功能的方法，以使设置的ip地址成为静态的

Ethernet IP Configuration 关闭DHCP
BootP DHCP Commissioning Tool 关闭DHCP

28、反正切函数相加减的公式

arctan A + arctan B=arctan[(A+B) / (1-AB)]
arctan A - arctan B=arctan[(A-B) / (1+AB)]