mysql 与 oracle中语法差别_oraclelike语法与mysqllike语法-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_35815479/article/details/121395750

表结构

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
'真实姓名';

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 COMMENT '所属组织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_name,t.real_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 and pd.endTime != ''  ">
  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!='' and pd.endTime != null and pd.endTime != '' "> 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()

SELECT DATE_FORMAT(NOW(),'%Y-%m');

oracle

SYSDATE

select TO_CHAR(SYSDATE,'yyyy-mm-dd') from dual;

分页查询

mysql

select * FROM cost_bill_detailed t
LIMIT 2,2; -- 分页参数动态拼接第一页每页显示2条当前页(开始行：(当前页-1)*每页显示的条数 )，每页显示的条数

 StringBuffer pageSql = new StringBuffer();
  pageSql.append(sql);
                pageSql.append(" LIMIT " + page.getCurrentResult() + "," + page.getShowCount());

oracle

SELECT * FROM(SELECT ROWNUM ROW_ID,temp.* FROM(
SELECT * FROM "cost_bill_detailed" -- 动态sql
) temp WHERE ROWNUM <=2 -- 当前页 + 每页显示的条数例如 0页每页显示2条
) WHERE ROW_ID >0 -- 当前页

   StringBuffer pageSql = new StringBuffer();
 pageSql.append("SELECT * FROM (SELECT TMP_TB.*,ROWNUM ROW_ID FROM (");
                pageSql.append(sql);
                pageSql.append(") TMP_TB WHERE ROWNUM<=");
                pageSql.append(page.getCurrentResult() + page.getShowCount());
                pageSql.append(") WHERE ROW_ID>");
                pageSql.append(page.getCurrentResult());

行转列

listagg() WITHIN GROUP () 函数

1.使用条件查询查询部门为20的员工列表=

-- 查询部门为20的员工列表

SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;

2.使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)

SELECT
   t.DEPTNO,
   LISTAGG ( TO_CHAR( t.ENAME ), ',' ) within GROUP ( ORDER BY t.ENAME ) AS names
FROM
   SCOTT.EMP t
WHERE
   t.DEPTNO = '20'
GROUP BY
   t.DEPTNO;

某个字段存另一个表的多个id 商品表存颜色表多个id用逗号分割

颜色表

CREATE TABLE "color" (
"id" NVARCHAR2(255) NOT NULL,
"color" NVARCHAR2(255)
)

INSERT INTO "color" VALUES ('11111', '黑色');
INSERT INTO "color" VALUES ('2222', '白色');
INSERT INTO "color" VALUES ('3333', '蓝色');

商品表 color_ids 存的是color表的id用逗号分割

CREATE TABLE "goods" (
"id" NVARCHAR2(25) NOT NULL,
"name" NVARCHAR2(255),
"color_ids" NVARCHAR2(255)
)

INSERT INTO "goods" VALUES ('1', '铅笔', '1111,2222,3333');
INSERT INTO "goods" VALUES ('2', '钢笔', '1111,2222');

mysql中使用FIND_IN_SET 函数

SELECT
   g.id,
   g.name,
   g.color_ids,
   GROUP_CONCAT(c.color) as colorName
FROM
   goods as g
   INNER JOIN color as c on FIND_IN_SET(c.id, g.color_ids)
GROUP BY
    g.id;