OceanBase基本语法（MySQL租户）

架构

OceanBase 数据库支持数据跨地域（Region）部署，一个 Region 可以包含一个或者多个 Zone。Zone 是可用区（Availability Zone）的简写。

Zone 本身是一个逻辑概念，是对物理机进行管理的容器，一般是同一机房的一组机器的组合。物理层面来讲一个 Zone 通常等价于一个机房、一个数据中心或一个 IDC。

一个 OceanBase 集群通常会分布在同城的 3 个机房中，同一份数据的三个副本分别分布在 3 个机房中（即三个 Zone 中）。

 OceanBase 数据库是支持多租户的，这里租户的概念类似于传统数据库的数据库实例。租户下可以建立数据库，在租户的数据库下可以建立表。

 目前支持两种不同的租户类型：MySQL 租户和 Oracle 租户。使用不同类型的租户对应不同类型的语法和功能。

OceanBase 数据库支持租户隔离。每个租户可以被赋于一定的资源（比如 CPU，内存，IOPS 和磁盘空间）。通过资源配置和设定资源池可以实现对租户资源的控制。

查看数据库版本

obclient> select version();

创建数据库

使用 CREATE DATABASE 语句创建数据库。

示例如下：

• 创建数据库 test 2，并指定字符集为 UTF8。

  obclient> CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8;

查看数据库

使用 SHOW DATABASES 语句查看数据库。

示例如下：

obclient> SHOW DATABASES;

删除数据库

使用 DROP DATABASE 语句删除数据库。

示例如下：

obclient> DROP DATABASE my_db;    

创建表

使用 CREATE TABLE 语句在数据库中创建新表。

示例如下：

obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

创建了一个自增列,列的类型可以定义为 AUTO_INCREMENT,示例如下：

obclient> CREATE TABLE t1(id bigint not null auto_increment primary key
, name varchar(50)
, gmt_create timestamp not null default current_timestamp
);

创建联合主键：CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT pk_c1_c2 PRIMARY KEY(c1, c2));

注意：OceanBase 数据库只支持在建表时通过 CREATE TABLE 创建主键约束，暂不支持通过 ALTER TABLE 追加、删除、修改主键约束。

变相修改主键方法：

复制表结构和数据

不支持select * into from t语句。

使用 CREATE TABLE LIKE 语句复制表结构。

obclient>CREATE TABLE t1 like t;

使用INSERT INTO … SELECT …语句复制表数据。

obclient>INSERT INTO t1 SELECT * FROM t;

查看表

使用 SHOW CREATE TABLE 语句查看建表语句。

示例如下：

obclient> SHOW CREATE TABLE test;

使用 SHOW TABLES 语句查看指定数据库中的所有表。

示例如下：

obclient> SHOW TABLES FROM my_db;

使用 DESCRIBE TABLE 语句查看表结构。

示例如下：

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1    | int(11)    | NO   | PRI | NULL    |       |
| c2     | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

使用 ALTER TABLE 语句修改表结构。

示例如下：

obclient> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);
Query OK, 0 rows affected (0.08 sec)
obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3     | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

执行以下命令，增加 c3 列。

obclient> ALTER TABLE test ADD c3 int;

执行以下命令，删除 c3 列。

obclient> SHOW TABLES FROM my_db;

修改表

新增列，支持在表中新增列，但不支持增加主键列。

obclient> ALTER TABLE test ADD c3 int;

修改列类型。修改列c1为bigint。

obclient>ALTER TABLE test MODIFY c1 BIGINT(50);

修改列名称。修改列c2为c。

obclient>ALTER TABLE test RENAME COLUMN c2 TO c;

同时修改列名称和列类型。修改列c2为c，字段为char(60)

obclient>ALTER TABLE test CHANGE COLUMN c2 c CHAR(60);

删除列，支持删除表中的列，但不允许删除主键列或包含索引的列。

obclient> ALTER TABLE test DROP c;

重命名表

obclient> ALTER TABLE test RENAME TO t1;

修改列的默认值

ALTER TABLE test CHANGE COLUMN c2 c2 varchar(50) DEFAULT 2;

删除表

使用 DROP TABLE 语句删除表。

obclient> DROP TABLE IF EXISTS test;

创建索引

使用 CREATE INDEX 语句创建表的索引。

obclient> CREATE INDEX test_index ON test (c1, c2);

查看索引

使用 SHOW INDEX 语句查看表的索引。

obclient> SHOW INDEX FROM test;

删除索引

使用 DROP INDEX 语句删除表的索引。

obclient> DROP INDEX test_index ON test;

插入数据

向表 t1 中插入一行数据。


obclient> INSERT INTO t1 VALUES(1,1);

向表 t1 中插入多行数据。

obclient> INSERT t1 VALUES(1,1),(2,default),(2+2,3*4);

支持INSERT INTO … SELECT …语句。

删除数据

删除表 t1 中按照 c2 列排序之后的第一行数据。

obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1;

多表删除，删除 t1、t2 表中 t1.c1 = t2.c1 的数据。

obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;

更新数据

 将表 t1 中 t1.c1=1 对应的那一行数据的 c2 列值修改为 100。

obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;

 将表 t2 中 p2 分区的数据中 t2.c1 > 2 的对应行数据的 c2 列值修改为 100。

obclient> UPDATE t2 partition(p2) SET t2.c2 = 100 WHERE t2.c1 > 2;

 多表联合更新。

obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
datetime不能插入''，要插入null，多行插入时结尾要带;

提交事务

如果使用 BEGIN 开启一个事务，执行 DML 语句后需要使用 COMMIT 提交事务；

obclient> BEGIN;
Query OK, 0 rows affected 

obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected 

obclient> COMMIT;
Query OK, 0 rows affected 

如果不显示地使用 BEGIN 开启事务，则一条 SQL 就是一个事务，不再需要提交事务。SQL 执行后您的修改即持久化成功，不可以用 ROLLBACK 语句回滚这些修改。

在提交事务（COMMIT）之前：

• 您的修改只对当前会话可见，对其他数据库会话均不可见。

• 您的修改没有持久化，您可以通过 ROLLBACK 语句撤销修改。

在提交事务（COMMIT）之后：

• 您的修改对所有数据库会话可见。

• 您的修改持久化成功，不能通过 ROLLBACK 语句回滚修改。

回滚事务

使用 ROLLBACK 语句回滚事务。

obclient> rollback;
Query OK, 0 rows affected (0.00 sec)

回滚一个事务指将事务的修改全部撤销。可以回滚当前整个未提交的事务，也可以回滚到事务中任意一个保存点。

创建视图

视图用来展示表的查询结果。大部分能使用表的地方，都可以使用视图。如果经常访问的数据分布在多个表里时，使用视图是最好的方法。

创建视图 stock_item。该视图内容取自 stock 和 item 两张表，两张表做表连接即可得到该视图。

obclient> CREATE  VIEW stock_item
     AS
     SELECT i_price, i_name, s_i_id
     FROM stok s, item i
     WHERE s.s_i_id = i.i_id;

修改视图

修改视图 stock_item。

obclient> CREATE OR REPLACE VIEW stock_item
     AS
     SELECT i_price, i_name, i_data, s_i_id, s_w_id, s_order_cnt
     FROM stok s, item i
     WHERE s.s_i_id = i.i_id;

删除视图

使用 DROP VIEW 语句删除一个或多个视图，删除视图并不会删除视图引用的表。

obclient> DROP VIEW V1;

创建同义词

 同义词是一个租户数据库对象的别名。使用同义词通常是为了管理权限方便，因为同义词可以隐藏另外一个数据对象的所有者权限。

使用 CREATE SYNONYM 语句来创建私有同义词s1，非当前数据库dqms的表test。

obclient> CREATE SYNONYM s1 for dqms.test;

使用 CREATE PUBLIC SYNONYM 语句来创建公共同义词。

删除一个私有同义词。

obclient> DROP SYNONYM test.s1;

删除一个私有同义词。

obclient> DROP PUBLIC SYNONYM syn_pub;