博客

mysql 与 oracle中语法差别

  • Post author:
  • Post category:mysql


表结构

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的员工列表

=


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


  2. 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;

oracle中不支持 FIND_IN_SET 函数    注意因我id项目中是uuid使用此方式没问题

若是 是id包含关系就会有问题  比如 一个id是 1111 另一id是11 就会有问题

SELECT

g.”name”,

(select wm_concat(c.”color”) from “color” c where instr(g.”color_ids”, c.”id”    ) > 0)  as colorNames

FROM “goods” g

参考


Oracle字符串分割Split（超简单一条sql解决）_冷夜轩的博客-CSDN博客_oracle 字符串分割


版权声明：本文为weixin_35815479原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: