GDCA学习实践--GBase 8s基本语法

DDL

数据库

• 新建数据库

create database [if not exists] <db_name> [in dbs_name] [with [buffered] log | with log mode ansi] [nlscasesensitive | nlscase insensitive];

• 重命名数据库

rename database <old_db_name> to <new_db_name>;

• 删除数据库

drop database [if exists] <db_name>;

表

• 新建表

create [standard | raw] table [if not exists] <table_name> (colname1 data_type1, colname2 data_type2, ...);

• 重命名表

rename table <old_table_name> to <new_table_name>;

• 删除表

drop table [if exists] <table_name>;

列

• 新增列

alter table <table_name | synonym_name> 
add (new_column_name1 data_type1 [before old_column_name1] [, new_column_name2 data_type2 [before old_column_name2], ...]);

• 删除列

alter table <table_name | synonym_name> drop (old_column_name1[, old_column_name2, ...]);

• 重命名列

rename column <table_name>.<old_column_name> to <new_column_name>;

视图

• 新建视图

create view [if not exists] <view_name> as <query_define>;

• 删除视图

drop view if exists <view_name>;

索引

• 新建索引

create [unique | distinct | cluster] index [if not exists] <idx_name> 
on <table_name | synonym_name> (<column_name | func_name(column_name)> [asc | desc]);

• 重命名索引

rename index <old_index_name> to <new_index_name>;

• 删除索引

drop index [if exists] <idx_name>;

存储过程

• 新建存储过程

create procedure [if not exists] <procedure_name>(param1 data_type1, param2 data_type2, ...)
<spl code>
end procedure;

• 删除存储过程

drop procedure [if exists] <procedure_name> [(data_type1, data_type2, ...)];

函数

• 新建函数

create function [if not exists] <function_name>(param1 data_type1, param2 data_type2, ...)
returning data_type1 [as var1] [, data_type2 [as var2], ...]
<spl code>
return var1[, var2, ...];
end function;

• 删除函数

drop function [if exists] <function_name> [(data_type1, data_type2, ...)];

同义词

• 新建同义词

create [public | private] synonym [if not exists] <synonym_name> for <table_name | view_name | sequence_name>;

• 删除同义词

drop synonym [if exists] <synonym_name>;

触发器

• 新增触发器

create [or replace] tirgger <trigger_name> <insert | update [of column_name] | delete | select> on <target_table_name>
<before | after | for each row>
when <condition>
<action>

• 删除触发器

drop trigger <trigger_name>;

DML

insert

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] values(value1, value2, ...);

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] select col_name1, col_name2, ... ;

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] execute function <fn_name([param1, param2, ...])>;

update

update <table_name | view_name | synonym_name> set column_name1 = value1[, column_name2 = value2, ... ] [where condition];

delete

delete from <table_name | view_name | synonym_name> [where condition];

merge

merge into <target_table_name> as t
using <source_table_name | source_query> as s
on t.column_name1 = s.column_name2
when matched then update set t.col_1 = s.col_1, t.col_2 = s.col_2, ...
when not matched then insert (t.col_1, t.col_2, ...) values(s.col_1, s.col_2, ...);

DQL

单表查询

select [first n] <* | column_name1[, column_name2, ...]> from <table_name>;

select <column_name1[, column_name2, ...], aggr_func1(column_name_n1)[, aggr_func2(column_name_n2), ...]>
from <table_name>
group by column_name_n1[, column_name_n2, ...]
[order by column_name_m1 [asc | desc][, column_name_m2  [asc | desc], ...]];

多表关联查询

• 自连接

select * from <table_name> a, <table_name> b where a.f_column_name1 = b.f_column_name2;

• 内连接

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
inner join <table_name2> b
on a.column_name1 = b.column_name2;

• 左连接

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
left outer join <table_name2> b
on a.column_name1 = b.column_name2;

• 右连接

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
right outer join <table_name2> b
on a.column_name1 = b.column_name2;

• 全连接

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
full outer join <table_name2> b
on a.column_name1 = b.column_name2;

子查询

• IN

select *
from <table_name1>
where column_name1 in
(select column_name2 from <table_name2>;

select *
from <table_name1>
where column_name1 in <(val1, val2, ...)>;

• EXISTS

select *
from <table_name1>
where exists (select 1 from <table_name2> where table_name1.column_name1 = table_name2.column_name2);