数据库补充

 

//手动切换数据源
DynamicDataSourceContextHolder.setDataSourceKey("slaveDataSource");
//打印当前数据源       
System.out.println(DynamicDataSourceContextHolder.getDataSourceId()+"========");

 

　

mongoDB补充：

1.在分页的同时去计算数据的总数

2.$ replaceRoot（聚合）等各种mongo管道技术

3.mongodb系列（二）使用复合索引中要注意字段的前后

4.mongodb复合索引有”黑科技“？

5.MongoDB 在SpringBoot中的使用

6.MongoDB更改数据库位置（Windows）

7.  1 如何在MongoDB中合并多个文档？              2.https://cloud.tencent.com/developer/ask/sof/490074       3.如何在MongoDB中合并多个文档？     

 

　

 

mysql补充：

Oracle补充：

1.oracle中to_date()函数的用法详解

2.按两个字段(每天日期和网点ID)进行分组,并且把分组字段日期的时分秒给截去后再去分组

SELECT
TRUNC(EWB_DATE,'DD')
,SEND_SITE_ID
,COUNT(SEND_SITE_ID)
,SUM(CALC_WEIGHT)
,SUM(PIECE)
FROM HS_OPT_EWB 
group by TRUNC(EWB_DATE,'DD'),SEND_SITE_ID

 3. 与Hive、MySQL、Oracle内建函数对照表(包含所有函数的讲解)

4.oracle   max()函数和min()函数 当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。语法如下。 

SELECT 
MAX (column_name) / MIN (column_name)
FROM  table_name

说明：列column_name中的数据可以是数值、字符串或是日期时间数

 5.Oracle 日期时间格式化不准的问题， hh24:mi:ss 才是正确的写法   -------------------  区别 HH24:mm:ss和HH24:mi:ss的区别

update HS_SITE_BUSINESS_LICENSE t set END_EFFECT_DATE = timestamp  '2999-12-31 23:56:59' where id = 19830;

6. oracle 如何对比某字段信息不同的数据？

两张表做union all, 而后group By 主键 having count（*）> 1

7. oracle 空值处理，排序过滤

按时间倒序会把空值排在最前面，那就给空值赋个最小值，让他排最后面
ORDER BY NVL(S.LATER_TIME,TO_DATE('1900-01-01', 'yyyy-mm-dd')) DESC

8.

-- 审核表添加字段
alter table HS_BASIC_EWB_LIMIT add site_id NUMBER(8);
comment on column HS_BASIC_EWB_LIMIT.site_id is '归属网点';
alter table HS_BASIC_EWB_LIMIT add polygon_clob_id NUMBER(16);
comment on column HS_BASIC_EWB_LIMIT.polygon_clob_id is '地图大字段id';

-- 更改【自提点表】联系人和地址 字段长度
alter table HS_SITE_PICK_UP modify (CONTACT_PERSON_NAME varchar2(100),PICK_UP_ADDRESS varchar2(400));        
-- 更改【自提点审核表】联系人和地址 字段长度
alter table HS_SITE_PICK_UP_AUDIT modify (CONTACT_PERSON_NAME varchar2(100),PICK_UP_ADDRESS varchar2(400));

-- Create table  创建表
create table HS_SITE_PICK_UP_AUDIT
(
  audit_id            NUMBER(8) not null,
  pick_up_id          NUMBER(8) not null,
  pick_up_name        VARCHAR2(50),
  pick_up_address     VARCHAR2(400),
  location            VARCHAR2(50),
  contact_phone       VARCHAR2(60),
  contact_person_name VARCHAR2(100),
  remark              VARCHAR2(800),
  site_id             NUMBER(8),
  district_id         NUMBER(20),
  district_level      NUMBER(1),
  audit_status        NUMBER(1),
  audit_time          DATE,
  audit_by            NUMBER(8),
  audit_remark        VARCHAR2(400),
  apply_by            NUMBER(8),
  apply_time          DATE,
  apply_remark        VARCHAR2(400),
  apply_type          NUMBER(1),
  rd_status           NUMBER(1),
  modify_before       NUMBER(16),
  modify_after        NUMBER(16)
)
tablespace ZTO_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column HS_SITE_PICK_UP_AUDIT.audit_id
  is '主键id';
comment on column HS_SITE_PICK_UP_AUDIT.pick_up_id
  is '自提点id';
comment on column HS_SITE_PICK_UP_AUDIT.pick_up_name
  is '自提点名称';
comment on column HS_SITE_PICK_UP_AUDIT.pick_up_address
  is '自提点地址';
comment on column HS_SITE_PICK_UP_AUDIT.location
  is '坐标';
comment on column HS_SITE_PICK_UP_AUDIT.contact_phone
  is '联系电话';
comment on column HS_SITE_PICK_UP_AUDIT.contact_person_name
  is '联系人';
comment on column HS_SITE_PICK_UP_AUDIT.remark
  is '备注';
comment on column HS_SITE_PICK_UP_AUDIT.site_id
  is '归属网点';
comment on column HS_SITE_PICK_UP_AUDIT.district_id
  is '行政区ID';
comment on column HS_SITE_PICK_UP_AUDIT.district_level
  is '行政区等级(镇/街道:5;村:6)';
comment on column HS_SITE_PICK_UP_AUDIT.audit_status
  is '审批状态（-1：不通过；0:审核；1：通过）';
comment on column HS_SITE_PICK_UP_AUDIT.audit_time
  is '审批时间';
comment on column HS_SITE_PICK_UP_AUDIT.audit_by
  is '审批人';
comment on column HS_SITE_PICK_UP_AUDIT.audit_remark
  is '审批说明';
comment on column HS_SITE_PICK_UP_AUDIT.apply_by
  is '申请人';
comment on column HS_SITE_PICK_UP_AUDIT.apply_time
  is '申请时间';
comment on column HS_SITE_PICK_UP_AUDIT.apply_remark
  is '申请原因';
comment on column HS_SITE_PICK_UP_AUDIT.apply_type
  is '申请类型（-1删除；1：新增；2：修改）';
comment on column HS_SITE_PICK_UP_AUDIT.rd_status
  is '数据状态 1：正常 0：删除';
comment on column HS_SITE_PICK_UP_AUDIT.modify_before
  is '变更前';
comment on column HS_SITE_PICK_UP_AUDIT.modify_after
  is '变更后';
-- Create/Recreate primary, unique and foreign key constraints 
alter table HS_SITE_PICK_UP_AUDIT
  add primary key (AUDIT_ID)
  using index 
  tablespace ZTO_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


--创建序列
create index I_SITE_PICK_UP_AUDIT_1 on HS_SITE_PICK_UP_AUDIT (SITE_ID)
  tablespace ZTO_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index I_SITE_PICK_UP_AUDIT_2 on HS_SITE_PICK_UP_AUDIT (PICK_UP_ID)
  tablespace ZTO_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create sequence 
create sequence SQE_SITE_PICK_UP_AUDIT
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20;