SinoDB数据库基本操作

运维指南 SQL开发
1

1. 数据库操作

1.1 创建数据库

  • 创建数据库为无日志模式数据库

create database db in db_dbs;      —db_dbs为数据库所在数据空间

  • 创建UNBUFFERED日志模式数据库

create database db in db_dbs with log;

  • 创建BUFFERED日志模式数据库

create database db in db_dbs with buffered log;

  • 创建ANSI日志模式数据库

create database db in db_dbs with log mode ansi；

1.2 重命名数据库

  • 将数据库stores6重命名为stores7

rename database stores6 to stores7;

1.3 删除数据库

  • 删除名为testdb的数据库

drop database testdb;

关于创建数据库的更多说明参考以下链接：

SinoDB创建数据库注意事项 - 知识库 / 运维管理 - Sinoregal Tech Forum

2. 表操作

2.1 创建普通表

  • 创建名为orders的表

create table orders(
 customer_num integer,
 order_date date
)
in dbspace1       —表的存放位置
extent size 64      —表创建时分配的extent大小
next size 32       —extent空间使用完后下一个分配的extent大小
lock mode row;      —锁级别

2.2 创建简单大对象表

  • 创建名为evaluation含有简单大对象字段表,并将简单大对象字段部分存放于独立blobspace上

create table evaluation(
 employee_num serial,
 manager_num integer,
 emp_eval_form text in blobspace1,   —列字段类型与存放位置
 emp_picture byte in blobspace2    —列字段类型与存放位置
)
in dbspace2;

2.3 创建智能大对象表

  • 创建表名为movie的含有智能大对象字段表, 并将智能大对象字段部分存放于独立smartblobspace上

create table movie(
 movie_num integer,
 movie_title char(50),
 video blob,
 audio blob,
 description clob  —指定列字段类型为clob
)
put video in (sbdbs1),   —指定字段video的存放位置
audio in (sbdbs2),     —指定audio的存放位置
description in (sbdbs3);   —指定description的存放位置

2.4 创建临时表

  • 创建名为temp_order的临时表，不记日志

create temp table temp_order(
 order_num integer
)
with no log;     —指定无日志模式

  • 将customer中两个字段插入临时表cust_temp，不记日志

select customer_num, company from customer
into temp cust_temp
with no log;     —指定无日志模式

2.5 重命名表

  • 将表名为stock表更名为inventory

rename table stock to inventory;

2.6 获取建表语句

  • 导出数据库stores_demo中orders表的所有表结构

dbschema -d stores_demo -t orders –ss
-d 指定数据库名
-t 指定表名
-ss 同时打印出存放位置、锁模式以及extent信息

2.7 更改锁模式

  • 将orders表锁模式改为行锁

ALTER TABLE orders LOCK MODE (ROW);

2.8 更改表下一个extent大小

  • 将customer表下一个extent设置为20K

ALTER TABLE customer NEXT SIZE 20;

关于建表的更多说明参考以下链接：

SinoDB建表注意事项 - 知识库 / 运维管理 - Sinoregal Tech Forum

2.9 增加字段

  • 为customer表增加一个birthday字段，数据类型为date

ALTER TABLE customer ADD birthday DATE;

2.10 删除字段

  • 删除customer表中birthday字段

ALTER TABLE customer DROP birthday;

2.11 更改字段数据类型

  • 更改customer表birthday字段时间类型为年至分

ALTER TABLE customer MODIFY birthday DATETIME YEAR TO MINUTE;

2.12 字段重命名

  • 将表invoice表中paid_date字段名更改为date_paid

RENAME COLUMN invoice.paid_date TO date_paid;

2.13 转化普通表为智能大对象表

  • 将booklist表content字段变更为CLOB类型并存放在sbsp1中

ALTER TABLE booklist MODIFY content CLOB, PUT content IN (sbsp1) (log);

2.14 删除表

  • 删除customer表

DROP TABLE customer;

3. 索引操作

3.1 创建唯一索引

  • 创建名为ix_orders的唯一索引并存放在idx_dbs中

CREATE UNIQUE INDEX ix_orders ON orders(orders_num) IN idx_dbs;

3.2 创建复合索引

  • 创建名为ix_items的复合索引

CREATE INDEX ix_items ON items(manu_code, stock_num);

3.3 创建簇索引

  • 创建名为ix_manufact的簇索引

CREATE UNIQUE CLUSTER INDEX ix_manufact ON manufact(manu_code);

3.4 创建可重复索引

  • 创建名为ix_man_stk的可重复索引

CREATE INDEX ix_man_stk ON items(manu_code desc, stock_num);

3.5 创建带索引页填充度的索引

  • 创建名为state_code_idx的可重复索引，设置填充度为80%

CREATE INDEX state_code_idx ON state(code) FILLFACTOR 80;

3.6 更改、重命名、删除索引

  • 更改索引idx_man_cd为CLUSTER

ALTER INDEX idx_man_cd TO CLUSTER;

  • 更改索引名ix_cust为new_idx_cust

RENAME INDEX ix_cust TO new_idx_cust;

  • 删除idx_stock索引

DROP INDEX idx_stock;

3.7 禁用索引

  • 禁用表上索引

SET INDEXES FOR tablename DISABLED;

3.8 启用索引

  • 启用表上所有索引

SET INDEXES FOR tablename ENABLED;

4. 表分片操作

4.1 创建ROUND ROBIN分片表

  • 创建名为table1的分片表，数据轮流存放至dbsapce1,dbspace2中

CREATE TABLE table1(
col_1 SERIAL,
col_2 CHAR(20))
FRAGMENT BY ROUND ROBIN   -----指定存放模式为顺序存放
IN dbspace1,dbspace2   -----指定顺序存放的位置
EXTENT SIZE 10000 NEXT SIZE 3000;

4.2 创建大对象ROUND ROBIN分片表

  • 创建表名为movie表，video数据轮流存放sbsp3，sbsp6，sbsp7中；audio数据轮流存放sbsp1，sbsp2，sbsp4中

CREATE TABLE movie(
movie_num INTEGER,movie_title CHAR(50),
video BLOB,audio BLOB,description CLOB),    ----指定各列名与数据类型
PUT video IN (sbsp3, sbsp6, sbsp7),    -----指定vidoe顺序存放位置
audio IN (sbsp1, sbsp2, sbsp4),    -----指定audio顺序存放位置
description IN (sbsp5);

4.3 创建基于表达式的分片表

  • 创建分片表table1并将：
    col_1<= 10000 AND col_1 >= 1放在dbspace1中，
    col_1 <= 20000 AND col_1 > 10000 放在dbspace2中；
    不满足上面条件全部存放在dbspace3中

CREATE TABLE table1(
col_1 SERIAL,
col_2 CHAR(20),
…)
FRAGMENT BY EXPRESSION
col_1 <= 10000 AND col_1 >= 1 IN dbspace1,   ----指定表达式与存放位置
col_1<=20000 AND col_1>10000 IN dbspace2   ----指定表达式与存放位置
REMAINDER IN dbspace3;    -----指定不满足条件存放位置

4.4 创建基于HASH Function的分片表

CREATE TABLE table1(
customer_num SERIAL
lname CHAR(20)
…)
FRAGMENT BY EXPRESSION
MOD(customer_num, 3) = 0 IN dbspace1,  -----指定条件与存放位置
MOD(customer_num, 3) = 1 IN dbspace2,  -----指定条件与存放位置
MOD(customer_num, 3) = 2 IN dbspace3;  -----指定条件与存放位置

4.5 创建/修改成带ROWID的分片表

  • 创建带ROWID分片表

CREATE TABLE orders(
order_num SERIAL,
customer_num INTEGER,
part_num CHAR(20))
WITH ROWIDS -----带ROWIDS
FRAGMENT BY ROUND ROBIN IN dbs1,dbs2;

  • 在表中增加ROWIS

ALTER TABLE items ADD ROWIDS;

  • 在表中删除ROWIS

ALTER TABLE items DROP ROWIDS

4.6 创建分片表索引

  • 通过表达式创建分片索引

CREATE INDEX idx1 ON table1(col_1)
FRAGMENT BY EXPRESSION
col_1 < 10000 IN dbspace1,
col_1 >= 10000 IN dbspace2;

  • 创建不分片索引

CREATE INDEX idx1 ON table1(col_1) IN dbspace1;

4.7 普通表与分片表转换

  • 从分片表修改为普通表

ALTER FRAGMENT ON TABLE table1 INIT IN dbspace2;

  • 从普通表切换为分片表

ALTER FRAGMENT ON TABLE table1 INIT  -----init初始化成
FRAGMENT BY ROUND ROBIN
IN dbspace1, dbspace2;

  • 从普通表切换为基于表达式的分片表

ALTER FRAGMENT ON TABLE table1 INIT  -----init初始化成
FRAGMENT BY EXPRESSION
col_1 <= 10000 AND col_1 >= 1 IN dbspace1,
col_1 <= 20000 AND col_1 > 10000 IN dbspace;

4.8 增加分片

  • 在原条件最后添加语句

ALTER FRAGMENT ON TABLE orders ADD -----增加条件
note_code > 3000 IN dbspace4;

  • 在原dbspace4条件前添加语句

ALTER FRAGMENT ON TABLE orders ADD -----增加条件
note_code <= 3000 OR note_code = 3500 IN dbspace3
BEFORE dbspace4; -----指定条件增加的位置

  • 给Round robin表添加dbspace

ALTER FRAGMENT ON TABLE customer ADD dbspace3;

4.9 删除分片

  • 删除表table1中dbspace1分片

ALTER FRAGMENT ON TABLE table1 DROP dbspace1;

  • 删除分片索引table1_idx1中dbspace4分片

ALTER FRAGMENT ON INDEX table1_idx1 DROP dbspace4;

4.10 更改分片规则

  • 将原有放在dbspace1上的表达式更改为col_1 > 30000

ALTER FRAGMENT ON TABLE table1 MODIFY dbspace1 TO col_1 > 30000 IN dbspace1;

  • 将原存放在dbspace3上的数据库更改为存放在dbspace5上

ALTER FRAGMENT ON TABLE table1 MODIFY dbspace3 TO REMAINDER IN dbspace5;

4.11 摘除/连接分片

  • 将table1,table2表结合成一个table1的分片表

ALTER FRAGMENT ON TABLE table1 ATTACH table1, table2;

  • 将一个分片表，分裂成两个普通表

ALTER FRAGMENT ON TABLE table1 DETACH dbspace2 table2;

4.12 分片跳过

数据库允许忽略不可用的分片，可以通过DATASKIP参数来控制

  • 打开

SET DATASKIP ON

  • 关闭

SET DATASKIP OFF

  • SKIP指定分片

SET DATASKIP dbspace1

5. 约束操作

5.1 增加约束

  • 增加主键约束

ALTER TABLE orders
ADD CONSTRAINT PRIMARY KEY (order_num);

  • 增加外键约束

ALTER TABLE items
ADD CONSTRAINT FOREIGN KEY(order_num)
REFERENCES orders; -----指定外键关联表

5.2 约束延迟检查

BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED; -----设置约束延迟检查
UPDATE orders SET order_num = 1006
WHERE order_num = 1001;
UPDATE items SET order_num = 1006
WHERE order_num = 1001;
COMMIT WORK;

5.3 删除约束

  • 删除在pk_orders列上所有约束

ALTER TABLE orders DROP CONSTRAINT pk_orders;

6. 视图/存储过程操作

当视图中包括Join语句，运算语句时：
视图将不能插入、更改、删除；
当视图引用虚拟列时不能更改、删除、插入。

6.1 创建普通视图

样例：

CREATE VIEW ordsummary AS
SELECT order_num, customer_num, ship_date
FROM orders;

CREATE VIEW they_owe (ordno, orddate, cnum) AS
SELECT
order_num, order_date, customer_num
FROM orders
WHERE paid_date IS NULL; -----视图语句

6.2 创建筛选行的视图

CREATE VIEW baseball AS
SELECT * FROM stock WHERE -----视图语句
description MATCHES “baseball”; -----筛选条件

6.3 删除视图

DROP VIEW ordsummary;

6.4 创建多表联合视图

CREATE VIEW stock_info AS SELECT stock.*, manu_name
FROM stock, manufact -----指定多张表
WHERE stock.manu_code =manufact.manu_code ----指定关联条件

7.存储过程/触发器创建

7.1 创建存储过程

CREATE PROCEDURE credit_order(p_order_num INT)
UPDATE orders
SET paid_date = TODAY
WHERE order_num = p_order_num;
END PROCEDURE

  • 删除存储过程

drop procedure credit_order;

7.2 创建TRIGGER

  • 语法

CREATE TRIGGER trigger_name …
Trigger event --什么事件将触发此TRIGGER
INSERT ON table_name
DELETE ON table_name
UPDATE ON table_name
UPDATE OF column_name ON table_name
SELECT ON table_name
SELECT OF column_name ON table_name
REFERENCING clause–触发器将引起什么动作

  • 示例

CREATE TRIGGER items_upd
UPDATE OF total_price ON items
REFERENCING NEW AS post OLD AS pre
FOR EACH ROW
(UPDATE ORDERS
SET order_price = order_price +
post.total_price - pre.total_price
WHERE order_num = post.order_num);

  • WHEN clause–制约

CREATE TRIGGER ins_cust_calls INSERT ON cust_calls
{Flag problems for billing dept. review} REFERENCING NEW AS post
FOR EACH ROW WHEN(post.call_code = ‘B’)
(INSERT INTO warn_billing
VALUES(post.customer_num));
Trigger action–trigger将做什么
BEFORE (define action here)；
FOR EACH ROW (define action here)；
AFTER (define action here)；

  • 删除TRIGGER

DROP TRIGGER trig_name;

2 
create table t1(
c1 int,
c2 int ,
primary key (c1),
unique(c1,c2) constraint pk_t59
);

有用户使用以上方式，在 CREATE TABLE 语句中使用约束键字来创建主键、外键、惟一性和其他约束，看似比较简单且方便，但从数据库的性能和管理而言，并不建议这样做。