4.2.4.3. Tdsql-Mysql
4.2.4.3.1. 插入
4.2.4.3.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);
-- 转换后TDSQL-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');
-- 转换后TDSQL-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);
-- 转换后TDSQL-MySQL：
INSERT INTO `unisql_special_orders` VALUES (3,1,1,'a1',1,1);
-- 查询表数据
SELECT * FROM unisql_special_orders;
4.2.4.3.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;
-- 转换后TDSQL-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.3.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;
-- 转换后TDSQL-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.3.1.4. 多表插入
暂不支持
4.2.4.3.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_;
-- 转换后TDSQL-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.3.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 |
-- 转换后TDSQL-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.3.2. 更新
4.2.4.3.2.1. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后TDSQL-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.3.2.2. 更新带子查询
暂不支持
4.2.4.3.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;
-- 转换后TDSQL-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.3.3. 删除
4.2.4.3.3.1. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后TDSQL-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.3.3.2. 条件子查询删除
暂不支持
4.2.4.3.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);
-- 转换后TDSQL-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;