GBase 8a 学习笔记 010 —— GBase 8a MPP Cluster DML语句

DML 数据操作语言

DML（Data Manipulation Language）

用于更新指定的数据，包括插入新数据（INSERT 语句）、修改已有数据（UPDATE）和删除数据（DELETE）这三种操作。

• INSERT

-- 基于明确的值插入多行记录
insert into t0 values(1),(2),(3),(4),(5),(6); 

-- 从另一个或多个表中选取出值，并将其插入
insert into t0 select * from t1,t2 where t1.id=t2.id;

• UPDATE

update t0 set t0.id = t0.id+1 where t0.id > 1;

• DELETE

DELETE FROM t0 WHERE t0.id IN ( 1,2,3);

DML执行逻辑

执行过程： 发起节点会将DML命令下发给gnode计算节点，各计算节点执行DML命令、更新数据，然后返回结果给发起管理节点，DML操作会改变gnode节点的数据。

分布表主分片执行成功，然后链式转发同步到备份分片；

DML语法

INSERT

原理：从DC尾块新增数据，不影响已入库的数据。批量入库性能高于单条入库性能。
插入数据：INSERT语句向表中插入新的行，通常有两种形式：

• 插入元组的语法

INSERT [INTO] [database_name.]table_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),...

-- 插入学生成绩数据

gbase> CREATE TABLE sc ( sid int not null,
    -> score decimal(5,2),
    -> subject varchar(100) default '语文');
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

gbase> INSERT INTO sc (sid, score ) VALUES (1, 80);
Query OK, 1 row affected (Elapsed: 00:00:00.11)

gbase> select * from sc;
+-----+-------+---------+
| sid | score | subject |
+-----+-------+---------+
|   1 | 80.00 | 语文    |
+-----+-------+---------+
1 row in set (Elapsed: 00:00:00.01)

gbase> TRUNCATE TABLE sc;
Query OK, 1 row affected (Elapsed: 00:00:00.03)

gbase> INSERT sc (sid, score ) VALUES (1, 80), (2, 75.5), (3, 91), (4,86), (5,88.5) ;
Query OK, 5 rows affected (Elapsed: 00:00:00.05)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from sc;
+-----+-------+---------+
| sid | score | subject |
+-----+-------+---------+
|   1 | 80.00 | 语文    |
|   2 | 75.50 | 语文    |
|   3 | 91.00 | 语文    |
|   4 | 86.00 | 语文    |
|   5 | 88.50 | 语文    |
+-----+-------+---------+
5 rows in set (Elapsed: 00:00:00.01)

gbase>

• 插入查询结果操作语句

INSERT [INTO] [database_name.]table_name [(col_name,...)] SELECT ... FROM [database_name.]table_name ...;

-- 数据准备
gbase> CREATE TABLE Dept_age (Sdept varchar(20), Avg_age int) ;
Query OK, 0 rows affected (Elapsed: 00:00:00.09)

gbase> CREATE TABLE student (
    ->   Sno varchar(20) ,
    ->   Sname varchar(20) ,
    ->   Ssex varchar(20) ,
    ->   Sage int(11) ,
    ->   Sdept varchar(20)
    -> ) ;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> INSERT INTO student (Sno,Sname,Ssex,Sage,Sdept) VALUES
    -> ('200215125','张立','男',19,'IS')
    -> ,('200215123','王敏','女',18,'MA')
    -> ,('200215122','刘晨','女',19,'CS')
    -> ,('200215121','李勇','男',20,'CS');
Query OK, 4 rows affected (Elapsed: 00:00:00.41)
Records: 4  Duplicates: 0  Warnings: 0

gbase> select * from Dept_age;
Empty set (Elapsed: 00:00:00.01)

gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)

gbase>

-- 求每个系内学生的平均年龄，并把结果存入数据库

-- 对Student表按系分组求平均年龄，再把信息存入新表中。

gbase> INSERT INTO Dept_age (Sdept,Avg_age) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept ;
Query OK, 3 rows affected, 1 warning (Elapsed: 00:00:00.91)
Records: 3  Duplicates: 0  Warnings: 0

gbase> select * from Dept_age;
+-------+---------+
| Sdept | Avg_age |
+-------+---------+
| MA    |      18 |
| CS    |      20 |
| IS    |      19 |
+-------+---------+
3 rows in set (Elapsed: 00:00:00.01)

gbase>

UPDATE

语法格式

UPDATE [database_name.]table_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)

-- 将sno号为200215121的学生年龄改为22岁。 

-- 更新单条数据
gbase> UPDATE Student SET Sage=22 WHERE Sno='200215121' ;
Query OK, 1 row affected (Elapsed: 00:00:00.05)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   19 | IS    |
| 200215123 | 王敏   | 女   |   18 | MA    |
| 200215122 | 刘晨   | 女   |   19 | CS    |
| 200215121 | 李勇   | 男   |   22 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.01)

-- 将所有学生的年龄增加1岁。 

-- 更新全部数据
gbase> UPDATE Student SET Sage=Sage+1 ;
Query OK, 4 rows affected (Elapsed: 00:00:00.05)
Rows matched: 4  Changed: 4  Warnings: 0

gbase> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 200215125 | 张立   | 男   |   20 | IS    |
| 200215123 | 王敏   | 女   |   19 | MA    |
| 200215122 | 刘晨   | 女   |   20 | CS    |
| 200215121 | 李勇   | 男   |   23 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (Elapsed: 00:00:00.02)


-- 将2020年前的商品价格设置为八折。 
-- 多表关联更新数据，只更新一张表数据
UPDATE product p, productPrice pp SET pp.price = pp.price * 0.8 WHERE p.productId = pp.productId AND p.dateCreated < '2020-01-01' ;

注意：不支持更新distributed by定义列的值；

快速UPDATE模式

UPDATE：默认全列更新，更新条数较少时，性能较差。

快速UPDATE模式

• 快速UPDATE模式，等价于DELETE+INSERT，即先删除符合更新条件的数据，然后再向表的末尾插入需要更新的新数据。
• 快速 UPDATE 模式，用以提高列存储数据更新操作效率问题。
• 更新少量数据时，建议使用快速 UPDATE 模式。
• 使用 SET gbase_fast_update =1; 命令打开快速 UPDATE 模式，默认是关闭快速UPDATE模式。

SHOW variables like 'gbase_fast_update';
SET gbase_fast_update = 1;
UPDATE student SET sage = sage+1 WHERE sage < 20;
SELETE * from student ;

DELETE

原理：对删除数据只标记删除标志，不实际删除数据。

语法格式

DELETE [FROM] [database_name.]table_name [tbl_alias] [WHERE where_definition];

--删除学号为200215128的学生记录。

-- 删除单条数据
DELETE FROM Student WHERE Sno=‘200215128’ ; 

-- 删除所有学生的选课记录。
DELETE FROM SC ; -- 删除全部数据

-- 删除计算机科学系所有学生的选课记录。
-- 删除多条数据
DELETE FROM SC WHERE ‘计算机科学系’=( SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno ) ; 

空间回收

释放被删除的数据文件所占的磁盘空间。

ALTER TABLE [vc_name.][database_names.]tbl_name SHRINK SPACE [FULL] | [FULL BLOCK_REUSE_RATIO= num；

参数说明

• 文件级回收：不指定 FULL 关键字时以文件为单位，只有当这个数据文件涉及的数据都被删除后，才能回收该文件占用的磁盘空间；
• 行级回收：指定 FULL 关键字，则按删除的行进行空间回收；有效行的顺序跟回收前保持一致，效率较低。
• 块级（ DC ）回收：指定 FULL BLOCK_REUSE_RATIO= num 表示当每个数据包（DataCell）中有效数据占比低于该设置值时，DC进行空间回收。DC重新整合成新的数据文件，无法保证有效行的顺序跟回收前一致。

ALTER TABLE t1 SHRINK SPACE FULL BLOCK_REUSE_RATIO=30；

DML语句使用说明