数据库补充

 

?

1 2 3

//手动切换数据源 DynamicDataSourceContextHolder.setDataSourceKey("slaveDataSource"); //打印当前数据源       <br>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)进行分组,并且把分组字段日期的时分秒给截去后再去分组

?

1 2 3 4 5 6 7 8

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()函数。语法如下。 

?

1 2 3 4 5

SELECT MAX (column_name) / MIN (column_name) FROM  table_name   说明：列column_name中的数据可以是数值、字符串或是日期时间数

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

?

1	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.

?

1 2 3 4 5

-- 审核表添加字段 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';

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134

-- 更改【自提点表】联系人和地址 字段长度 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));<em id="__mceDel"><em id="__mceDel"><em id="__mceDel"><br></em></em></em>-- 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;