mysql与oracle中语法差别表结构
oracle 建表语句
/*==============================================================*/ /* Table: "cost_bill_detailed"                                  */
/*==============================================================*/ create table "cost_bill_detailed"
(
"id"                NVARCHAR2(255)      not null,
"con_id"            NVARCHAR2(255),
"user_id"            NVARCHAR2(255),
"user_name"          NVARCHAR2(255),
"real_name"          NVARCHAR2(255),
"org_top_ip"        NVARCHAR2(255),
"org_top_name"      NVARCHAR2(255),
"platform_id"        NVARCHAR2(255),
"org_id"            NVARCHAR2(255),
"org_name"          NVARCHAR2(255),
"open_time"          DATE,
"close_time"        DATE,
"hardware_type_id"  NVARCHAR2(255),
"con_type"          NVARCHAR2(1),
"work_hours"        NUMBER(11,2),
"work_price"        NUMBER(11,2),
"content"            NVARCHAR2(500),
"app_id"            NVARCHAR2(255),
"software_name"      NVARCHAR2(255),
"software_version"  NVARCHAR2(255),
"cpu_use"            NUMBER(10,2),
"gpu_use"            NUMBER(10,2),
"memory_use"        NUMBER(10,2),
"storage_use"        NUMBER(10,2),
"memor_price"        NUMBER(11,2),
"app_price"          NUMBER(11,2),
"creation_date"      DATE,
"last_update_date"  DATE,
"batch_id"          NVARCHAR2(255),
constraint PK_COST_BILL_DETAILED primary key ("id")
);
comment on column "cost_bill_detailed"."con_id" is
'连接id';
comment on column "cost_bill_detailed"."user_id" is
'⽤户id';
comment on column "cost_bill_detailed"."user_name" is
'⽤户名';
comment on column "cost_bill_detailed"."real_name" is
'真实姓名';
oracle decimal类型comment on column "cost_bill_detailed"."org_top_ip" is
'⽤户所在⽤户单位';
comment on column "cost_bill_detailed"."org_top_name" is
'⽤户所在⽤户单位名称';
comment on column "cost_bill_detailed"."platform_id" is
'平台id';
comment on column "cost_bill_detailed"."org_id" is
'所属组织id';
comment on column "cost_bill_detailed"."org_name" is
'所属组织名称';
comment on column "cost_bill_detailed"."open_time" is
'开始使⽤时间';
comment on column "cost_bill_detailed"."close_time" is
'结束使⽤时间';
comment on column "cost_bill_detailed"."hardware_type_id" is '连接类型  虚机 物理机';
comment on column "cost_bill_detailed"."con_type" is
'连接类型  N 虚机 Y物理机';
comment on column "cost_bill_detailed"."work_hours" is
'⼯作时长 h⼩时';
comment on column "cost_bill_detailed"."work_price" is
'总费⽤';
comment on column "cost_bill_detailed"."content" is
'扣费内容';
comment on column "cost_bill_detailed"."app_id" is
'软件id';
comment on column "cost_bill_detailed"."software_name" is '软件名称';
comment on column "cost_bill_detailed"."software_version" is '软件版本';
comment on column "cost_bill_detailed"."cpu_use" is
'cpu使⽤';
comment on column "cost_bill_detailed"."gpu_use" is
'GPU使⽤';
comment on column "cost_bill_detailed"."memory_use" is
'内存使⽤';
comment on column "cost_bill_detailed"."storage_use" is
'存储容量使⽤';
comment on column "cost_bill_detailed"."memor_price" is
'存储资源费⽤';
comment on column "cost_bill_detailed"."app_price" is
'软件费⽤';
comment on column "cost_bill_detailed"."creation_date" is '创建时间';
comment on column "cost_bill_detailed"."last_update_date" is '更新时间';
comment on column "cost_bill_detailed"."batch_id" is
'批次id';
mysql建表语句
/*==============================================================*/
/* Table: "cost_bill_detailed"                                  */
/*==============================================================*/
CREATE TABLE `cost_bill_detailed`  (
`id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`con_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '连接id',  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户id',  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户名',
`real_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
`org_top_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户所在⽤户单位',
`org_top_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户所在⽤户单位名称',
`platform_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平台id',
`org_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL CO
MMENT '所属组织id',
`org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属组织名称',
`open_time` datetime NULL DEFAULT NULL COMMENT '开始使⽤时间',
`close_time` datetime NULL DEFAULT NULL COMMENT '结束使⽤时间',
`hardware_type_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
COMMENT '连接类型  dict 虚机 物理机',
`con_type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '连接类型 N 虚机 Y物理机',
`work_hours` decimal(11,2) NULL DEFAULT NULL COMMENT '⼯作时长 h⼩时',
`work_price` decimal(11,2) NULL DEFAULT NULL COMMENT '总费⽤',
`content` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扣费内容',
`app_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '软件id',  `software_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '软件名称',
`software_version` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '软件版本',
`cpu_use` decimal(10,2) NULL DEFAULT NULL COMMENT 'cpu使⽤',
`gpu_use` decimal(10,2) NULL DEFAULT NULL COMMENT 'GPU使⽤',
`memory_use` decimal(10,2) NULL DEFAULT NULL COMMENT '内存使⽤',
`storage_use` decimal(10,2) NULL DEFAULT NULL COMMENT '存储容量使⽤',
`memor_price` decimal(11,2) NULL DEFAULT NULL COMMENT '存储资源费⽤',
`app_price` decimal(11,2) NULL DEFAULT NULL COMMENT '软件费⽤',
`creation_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
`last_update_date` datetime NULL DEFAULT NULL COMMENT '更新时间',
`batch_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '批次id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
1.模糊查询
-- mysql  模糊查询俩中⽅式
SELECT * FROM cost_bill_detailed t where (t.user_name like CONCAT('%','张','%')
OR  t.real_name like CONCAT('%','张','%') );
SELECT * FROM cost_bill_detailed t where CONCAT_WS(',',t.user_al_name)
like CONCAT('%','张','%');
--  oracle  模糊查询 忽略⼤⼩写
SELECT * FROM "cost_bill_detailed" t where (
regexp_like (t."user_name",'⽯','i')
OR regexp_like(t."real_name",'⽯','i')
);
对应mabatis
mysql
<if test="pd.searchName != null and pd.searchName != ''">
AND (
t3.name LIKE CONCAT('%', #{pd.searchName}), '%')
OR t2.name LIKE CONCAT('%', #{pd.searchName}), '%')
)
</if>
<if test="textSearch !=null and textSearch !='' " >
and CONCAT_WS(',',ccms_params.param_code,ccms_params.param_name)
like CONCAT("%",#{textSearch},"%")
</if>
oracle
<if test="pd.searchName != null and pd.searchName != ''">
AND (
REGEXP_LIKE(t."real_name", #{pd.searchName}, 'i')
OR  REGEXP_LIKE(t."org_top_name", #{pd.searchName}, 'i')
)
</if>
时间范围搜索
mysql
<if test="pd.startTime != null and pd.startTime!='' and  pd.endTime != null dTime != ''  ">
AND DATE_FORMAT(t.open_time,'%Y-%m-%d')  BETWEEN #{pd.startTime}  AND  #{pd.endTime}
</if>
DATE_FORMAT(时间，格式化)      '%Y-%m-%d' 2021-10-12
oracle
<if test="pd.startTime != null and pd.startTime!='' dTime != null dTime != '' "> AND TO_CHAR(t.open_time,'yyyy-mm-dd') BETWEEN #{pd.startTime} AND #{pd.endTime} </if>
时间格式化
TO_CHAR(时间，格式)  格式 ,'yyyy-mm-dd'  2021-10-12
当前时间
mysql
NOW()

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。