Comments (2)
lujiashun
commented on July 20, 2024
https://sp.stonedb.io/zh/docs/developer-guide/DML-statements/
from mysql-server-mysql-8.0.30.
DandreChen
commented on July 20, 2024
DML
failed：× ； pass：✓
1. INSERT
1.1. insert to
mysql> create database cxddb;
Query OK, 1 row affected (0.01 sec)
mysql> use cxddb;
Database changed
mysql> CREATE TABLE t_test(
-> id INT NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(10) NOT NULL,
-> last_name VARCHAR(10) NOT NULL,
-> sex VARCHAR(5) NOT NULL,
-> score INT NOT NULL,
-> copy_id INT NOT NULL,
-> PRIMARY KEY (`id`)
-> ) engine=TIANMU;
Query OK, 0 rows affected (0.08 sec)
1.1.1 单条数据 [✓]
mysql> insert into t_test values(0,"abcde","zxycs","man",88,123);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_test;
+----+------------+-----------+-----+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+-----+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
+----+------------+-----------+-----+-------+---------+
1 row in set (0.00 sec)
1.1.2 多条数据 [✓]
mysql> insert into t_test values(0,"abcde","zxycs","man",88,123),(0,"3bcde","5xycs","man",99,456),(0,"sfdde","qwrqew","wo",10,789);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_test;
+----+------------+-----------+-----+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+-----+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
+----+------------+-----------+-----+-------+---------+
4 rows in set (0.00 sec)
1.1.3 选择性插入 [✓]
mysql> insert into t_test(first_name,last_name,sex,score,copy_id) values("first","last","man1",89,321),("f1","f2","man2",98,123);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
+----+------------+-----------+------+-------+---------+
6 rows in set (0.00 sec)
1.2. insert into select
1.2.1 全部插入 [✓]
mysql> create table t_test1 like t_test;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t_test1 select * from t_test;
Query OK, 6 rows affected, 1 warning (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 1
mysql> select * from t_test1;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
+----+------------+-----------+------+-------+---------+
6 rows in set (0.00 sec)
1.2.2选择性插入 [✓]
mysql> insert into t_test(first_name,last_name, sex, score, copy_id) select first_name,last_name, sex, score, copy_id from t_test1;
Query OK, 6 rows affected, 1 warning (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 1
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | abcde | zxycs | man | 88 | 123 |
| 8 | abcde | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | qwrqew | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
+----+------------+-----------+------+-------+---------+
12 rows in set (0.00 sec)
1.3. insert into on duplicate key update [✓]
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | abcde | zxycs | man | 88 | 123 |
| 8 | abcde | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
| 13 | 李 | 诚 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> insert into t_test values(13,'李','诚','1',47,10) on duplicate key update last_name='乘';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | abcde | zxycs | man | 88 | 123 |
| 8 | abcde | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> update t_test set first_name="hello" where last_name="zxycs";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
1.3. insert ignore into [X]
mysql> CREATE TABLE t (a INT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT IGNORE INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO t VALUES (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
2. UPDATE
2.1. 单表UPDATE [✓]
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | abcde | zxycs | man | 88 | 123 |
| 8 | abcde | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> update t_test set first_name="hello" where last_name="zxycs";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | hello | zxycs | man | 88 | 123 |
| 2 | hello | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | hello | zxycs | man | 88 | 123 |
| 8 | hello | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
2.2. UPDATE关联子查询 [✓]
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | hello | zxycs | man | 88 | 123 |
| 2 | hello | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | 周 | man2 | 98 | 123 |
| 7 | hello | zxycs | man | 88 | 123 |
| 8 | hello | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> select * from t_test1;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | abcde | zxycs | man | 88 | 123 |
| 2 | abcde | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 10 | 李 | f2 | 1 | 47 | 10 |
| 12 | 李 | 诚 | 1 | 47 | 10 |
| 13 | 李 | 诚 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
9 rows in set (0.00 sec)
mysql> UPDATE t_test SET first_name='陈' WHERE score IN (SELECT DISTINCT score FROM t_test1 WHERE score IN (88,99,89,98));
Query OK, 10 rows affected, 1 warning (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 1
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | 陈 | zxycs | man | 88 | 123 |
| 2 | 陈 | zxycs | man | 88 | 123 |
| 3 | 陈 | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | 陈 | last | man1 | 89 | 321 |
| 6 | 陈 | 周 | man2 | 98 | 123 |
| 7 | 陈 | zxycs | man | 88 | 123 |
| 8 | 陈 | zxycs | man | 88 | 123 |
| 9 | 陈 | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | 陈 | last | man1 | 89 | 321 |
| 12 | 陈 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
2.3 UPDATE多表关联 [✓]
mysql> create table t_test_update(first_name varchar(10)) engine=TIANMU;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t_test_update values("f1");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | hello | zxycs | man | 88 | 123 |
| 2 | hello | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | f2 | man2 | 98 | 123 |
| 7 | hello | zxycs | man | 88 | 123 |
| 8 | hello | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | f2 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> select * from t_test_update;
+------------+
| first_name |
+------------+
| f1 |
+------------+
1 row in set (0.00 sec)
UPDATE t_test t1 join t_test_update t2 SET t1.last_name = '周' WHERE t1.first_name = t2.first_name;
Query OK, 2 rows affected, 1 warning (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 1
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | hello | zxycs | man | 88 | 123 |
| 2 | hello | zxycs | man | 88 | 123 |
| 3 | 3bcde | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | first | last | man1 | 89 | 321 |
| 6 | f1 | 周 | man2 | 98 | 123 |
| 7 | hello | zxycs | man | 88 | 123 |
| 8 | hello | zxycs | man | 88 | 123 |
| 9 | 3bcde | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | first | last | man1 | 89 | 321 |
| 12 | f1 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
3. DELETE
3.1. 单表DELETE [X]
mysql> delete from t_test where id = 1;
ERROR 1031 (HY000): Table storage engine for 't_test' doesn't have this option
need to merge code from 5.7
3.2. 多表关联DELETE [X]
mysql> delete a from t_test a, t_test1 b where a.id = b.id;
ERROR 1031 (HY000): Table storage engine for 't_test' doesn't have this option
need to merge code from 5.7
4. REPLACE INTO [X]
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | 陈 | zxycs | man | 88 | 123 |
| 2 | 陈 | zxycs | man | 88 | 123 |
| 3 | 陈 | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | 陈 | last | man1 | 89 | 321 |
| 6 | 陈 | 周 | man2 | 98 | 123 |
| 7 | 陈 | zxycs | man | 88 | 123 |
| 8 | 陈 | zxycs | man | 88 | 123 |
| 9 | 陈 | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | 陈 | last | man1 | 89 | 321 |
| 12 | 陈 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> replace into t_test values(13,"赵","孙","man","89",123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | 陈 | zxycs | man | 88 | 123 |
| 2 | 陈 | zxycs | man | 88 | 123 |
| 3 | 陈 | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | 陈 | last | man1 | 89 | 321 |
| 6 | 陈 | 周 | man2 | 98 | 123 |
| 7 | 陈 | zxycs | man | 88 | 123 |
| 8 | 陈 | zxycs | man | 88 | 123 |
| 9 | 陈 | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | 陈 | last | man1 | 89 | 321 |
| 12 | 陈 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
5.TRUNCATE [X]
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | 陈 | zxycs | man | 88 | 123 |
| 2 | 陈 | zxycs | man | 88 | 123 |
| 3 | 陈 | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | 陈 | last | man1 | 89 | 321 |
| 6 | 陈 | 周 | man2 | 98 | 123 |
| 7 | 陈 | zxycs | man | 88 | 123 |
| 8 | 陈 | zxycs | man | 88 | 123 |
| 9 | 陈 | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | 陈 | last | man1 | 89 | 321 |
| 12 | 陈 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.00 sec)
mysql> truncate t_test;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t_test;
+----+------------+-----------+------+-------+---------+
| id | first_name | last_name | sex | score | copy_id |
+----+------------+-----------+------+-------+---------+
| 1 | 陈 | zxycs | man | 88 | 123 |
| 2 | 陈 | zxycs | man | 88 | 123 |
| 3 | 陈 | 5xycs | man | 99 | 456 |
| 4 | sfdde | qwrqew | wo | 10 | 789 |
| 5 | 陈 | last | man1 | 89 | 321 |
| 6 | 陈 | 周 | man2 | 98 | 123 |
| 7 | 陈 | zxycs | man | 88 | 123 |
| 8 | 陈 | zxycs | man | 88 | 123 |
| 9 | 陈 | 5xycs | man | 99 | 456 |
| 10 | sfdde | f2 | wo | 10 | 789 |
| 11 | 陈 | last | man1 | 89 | 321 |
| 12 | 陈 | 周 | man2 | 98 | 123 |
| 13 | 李 | 乘 | 1 | 47 | 10 |
+----+------------+-----------+------+-------+---------+
13 rows in set (0.01 sec)
from mysql-server-mysql-8.0.30.
Related Issues (20)
- TODO: Need to test supported/unsupported operations、data types for StoneDB 8.0
- TODO：InnoDB is limited to row-logging by MySQL 8.0 HOT 1
- TODO: Give suggested my.cnf about MySQL 8.0 HOT 2
- TODO: execute_sqlcom_select is deleted HOT 2
- TODO: handle_query is deleted HOT 2
- TODO: add build scripts
- TODO: R&D self-test DDL HOT 6
- TODO: mtr
- TODO: uncomment RCTable::binlog_load_query_log_event HOT 1
- TODO: uncomment export_file_name from `engine_execute.cpp` HOT 1
- TODO: check TODO on the comments HOT 9
- Evaluate: rr_sequential VS rr_quick
- TODO: R&D self-test DQL basically HOT 1
- TODO: R&D self-test TPCH HOT 1
- fix：mysqld...Segmentation fault HOT 1
- TODO: Add Engine::HandleSelect to MySQL 8.0 HOT 1
- lock_tables
- Crash: use db
- crash: query_compile.cpp
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from mysql-server-mysql-8.0.30.