4.2.4.5. OceanBase-Mysql

4.2.4.5.1. 插入

4.2.4.5.1.1. 插入单条记录

指定所有字段

-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL：
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);

-- 转换后oceanbase-MySQL：
INSERT INTO `unisql_special_orders` (`order_`,`customer_`,`order_total`,`sales_rep_id`,`credit_limit`,`cust_email`) VALUES (1,1,1,'a1',1,1);

-- 查询表数据
SELECT * FROM unisql_special_orders;

指定某些字段，不指的有默认值的插入时带上默认值。

-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');

-- 转换后oceanbase-MySQL：
INSERT INTO `unisql_special_orders` (`sales_rep_id`) VALUES ('a1');

-- 查询表数据
SELECT * FROM unisql_special_orders;

不指定字段，则默认为所有字段与建表时一致。

-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);

-- 转换后oceanbase-MySQL：
INSERT INTO `unisql_special_orders` VALUES (3,1,1,'a1',1,1);

-- 查询表数据
SELECT * FROM unisql_special_orders;

4.2.4.5.1.2. 从另外一个表取数据进行批量插入

-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);

-- 从另外一个表读取数据进行数据插入
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders;

-- 转换后oceanbase-MySQL：
INSERT INTO `unisql_special_orders` SELECT `order_`,`customer_`,`order_total`,`sales_rep_id`,`credit_limit`,`cust_email` FROM `unisql_special_orders`;

-- 查询表数据
SELECT * FROM unisql_special_orders;

4.2.4.5.1.3. 批量插入

-- 创建表
DROP table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 转换前Oracle SQL:
INSERT ALL
INTO unisql_special_orders VALUES(1,1,1,'a1',1,1)
INTO unisql_special_orders VALUES(2,2,2,'a2',2,2)
INTO unisql_special_orders VALUES(3,3,3,'a3',3,3)
INTO unisql_special_orders VALUES(4,4,4,'a4',4,4)
INTO unisql_special_orders VALUES(5,5,5,'a5',5,5)
SELECT * FROM dual;

-- 转换后oceanbase-MySQL：
INSERT INTO `unisql_special_orders` VALUES (1,1,1,'a1',1,1),(2,2,2,'a2',2,2),(3,3,3,'a3',3,3),(4,4,4,'a4',4,4),(5,5,5,'a5',5,5);

-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
    1|        1|          1|a1          |           1|         1|
    2|        2|          2|a2          |           2|         2|
    3|        3|          3|a3          |           3|         3|
    4|        4|          4|a4          |           4|         4|
    5|        5|          5|a5          |           5|         5|

4.2.4.5.1.4. 多表插入

暂不支持

4.2.4.5.1.5. WITH临时表查询插入

-- 创建表
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);

-- 插入数据
INSERT INTO unisql_special_orders VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders VALUES(2,2,2,'a2',2,2);
INSERT INTO unisql_special_orders VALUES(3,3,3,'a3',3,3);

-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders t LEFT JOIN tmp y ON t.order_ = y.order_;

-- 转换后oceanbase-MySQL：
INSERT
    INTO
    `unisql_special_orders` (`order_`,
    `customer_`,
    `order_total`,
    `sales_rep_id`)
SELECT
    `t`.`order_`,
    `t`.`customer_`,
    `y`.`order_total`,
    `y`.`sales_rep_id`
FROM
    `unisql_special_orders` AS `t`
LEFT JOIN (
    SELECT
        `order_`,
        `customer_`,
        `order_total`,
        `sales_rep_id`
    FROM
        `unisql_special_orders`) AS `y` ON
    `t`.`order_`=`y`.`order_`;

SELECT * FROM unisql_special_orders;

4.2.4.5.1.6. 合并插入

# ON 关联条件必须是主键或者唯一键
-- 创建表
CREATE TABLE unisql_people_source (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

CREATE TABLE unisql_people_target (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name  VARCHAR2(20) NOT NULL,
title      VARCHAR2(10) NOT NULL
);

-- 插入数据
INSERT INTO unisql_people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO unisql_people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO unisql_people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO unisql_people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO unisql_people_source VALUES (4, 'Dave', 'Brown', 'Mr');

-- 转换前Oracle SQL：
MERGE INTO unisql_people_target pt
USING unisql_people_source ps
ON    (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
    pt.last_name = ps.last_name,
    pt.title = ps.title
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

SELECT * FROM unisql_people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

-- 转换后oceanbase-MySQL：
INSERT
    INTO
    `unisql_people_target` (`person_id`,
    `first_name`,
    `last_name`,
    `title`)
SELECT
    `ps`.`person_id`,
    `ps`.`first_name`,
    `ps`.`last_name`,
    `ps`.`title`
FROM
    `unisql_people_source` AS `ps` ON
    DUPLICATE KEY
UPDATE
    `first_name`=`ps`.`first_name`,
    `last_name`=`ps`.`last_name`,
    `title`=`ps`.`title`;

person_id|first_name|last_name|title|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

4.2.4.5.2. 更新

4.2.4.5.2.1. 更新带表别名

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;

-- 转换后oceanbase-MySQL：
UPDATE `unisql_people_target` AS `u` SET `u`.`FIRST_NAME`='Ashley', `u`.`LAST_NAME`='Jessica' WHERE `u`.`person_id`=2;

-- 查询表数据
select * from unisql_people_target;

4.2.4.5.2.2. 更新带子查询

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = (SELECT b.FIRST_NAME FROM unisql_people_target b WHERE b.person_id = 2), u.LAST_NAME = 'Nicholas' WHERE u.person_id=3;

-- 转换后LightDB-Oracle SQL:
UPDATE `unisql_people_target` AS `u` SET `u`.`FIRST_NAME`=(SELECT `b`.`FIRST_NAME` FROM `unisql_people_target` AS `b` WHERE `b`.`person_id`=2), `u`.`LAST_NAME`='Nicholas' WHERE `u`.`person_id`=3;

-- 查询表数据
select * from unisql_people_target;

4.2.4.5.2.3. 更新带函数

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = UPPER(u.FIRST_NAME),u.LAST_NAME = LOWER(u.LAST_NAME)  WHERE u.person_id=3;

-- 转换后oceanbase-MySQL：
UPDATE `unisql_people_target` AS `u` SET `u`.`FIRST_NAME`=UPPER(`u`.`FIRST_NAME`), `u`.`LAST_NAME`=LOWER(`u`.`LAST_NAME`) WHERE `u`.`person_id`=3

4.2.4.5.3. 删除

4.2.4.5.3.1. 条件删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';

-- 转换后oceanbase-MySQL：
DELETE `u` FROM `unisql_people_target` AS `u` WHERE `u`.`PERSON_ID`=1 AND `FIRST_NAME`='Bob';

-- 查询表数据
select * from unisql_people_target;

4.2.4.5.3.2. 条件子查询删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID > (SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME = 'ASHLEY');

-- 转换后LightDB-Oracle SQL:
DELETE `u` FROM `unisql_people_target` AS `u` WHERE `u`.`PERSON_ID`>(SELECT `PERSON_ID` FROM `unisql_people_target` WHERE `FIRST_NAME`='ASHLEY');

-- 查询表数据
select * from unisql_people_target;

4.2.4.5.3.3. exists函数删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_source
WHERE EXISTS(SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID = unisql_people_source.PERSON_ID);

-- 转换后oceanbase-MySQL：
DELETE FROM `unisql_people_source` WHERE EXISTS (SELECT 1 FROM `unisql_people_target` WHERE `unisql_people_target`.`PERSON_ID`=`unisql_people_source`.`PERSON_ID`);

    -- 查询表数据
select * from unisql_people_source;