GBASE南大通用技术分享: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>;
DEMO
create database demo_db1;
rename database demo_db1 to demo_db2;
drop database demo_db2;
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-06-18 00:00:00
> create database demo_db1;
Database created.
> rename database demo_db1 to demo_db2;
359: Cannot drop or rename the current database or any open database.
Error in line 1
Near character position 35
> database gbasedb;
Database closed.
Database selected.
> rename database demo_db1 to demo_db2;
Database renamed.
> drop database demo_db2;
Database dropped.
>
GBASE南大通用技术分享:GBase 8s 表
新建表
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>;
DEMO
create table t_user1(f_userid int, f_username varchar(20));
rename table t_user1 to t_user2;
drop table t_user2;
> database gbasedb;
Database closed.
Database selected.
> create table t_user1(f_userid int, f_username varchar(20));
Table created.
> rename table t_user1 to t_user2;
Table renamed.
> drop table t_user2;
Table dropped.
>
列
新增列
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>;
DEMO
drop table if exists t_user;
create table t_user(f_userid int, f_username varchar(20));
insert into t_user values(1, 'gbasedbt');
select * from t_user;
alter table t_user add (f_nickname varchar(20));
select * from t_user;
rename column t_user.f_nickname to f_showname;
select * from t_user;
alter table t_user drop (f_showname);
select * from t_user;
[gbasedbt@devsvr train]$ dbaccess gbasedb -
Your evaluation license will expire on 2022-06-18 00:00:00
Database selected.
> drop table if exists t_user;
Table dropped.
> create table t_user(f_userid int, f_username varchar(20));
Table created.
> insert into t_user values(1, 'gbasedbt');
1 row(s) inserted.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> alter table t_user add (f_nickname varchar(20));
Table altered.
> select * from t_user;
f_userid f_username f_nickname
1 gbasedbt
1 row(s) retrieved.
> rename column t_user.f_nickname to f_showname;
Column renamed.
> select * from t_user;
f_userid f_username f_showname
1 gbasedbt
1 row(s) retrieved.
> alter table t_user drop (f_showname);
Table altered.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
>
视图
新建视图
create view [if not exists] <view_name> as <query_define>;
删除视图
drop view if exists <view_name>;
DEMO
create view vw_user as select * from t_user;
select * from vw_user;
drop view vw_user;
> create view vw_user as select * from t_user;
View created.
> select * from vw_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> drop view vw_user;
View dropped.
>
索引
新建索引
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>;
DEMO
create unique index idx_user on t_user (f_userid asc);
rename index idx_user to idx_user01;
drop index idx_user01;
> create unique index idx_user on t_user (f_userid asc);
Index created.
> rename index idx_user to idx_user01;
Index renamed.
> drop index idx_user01;
Index dropped.
>
存储过程
新建存储过程
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, ...)];
DEMO
drop table if exists t_city;
create table t_city(f_cityid int, f_cityname varchar(20));
create procedure up_city_add(cityid int, cityname varchar(20))
insert into t_city(f_cityid, f_cityname) values(cityid, cityname);
end procedure;
call up_city_add(1, 'Beijing');
call up_city_add(2, 'Shanghai');
call up_city_add(3, 'Tianjin');
call up_city_add(4, 'Chongqing');
select * from t_city;
delete from t_city;
execute procedure up_city_add(1, 'Beijing');
execute procedure up_city_add(2, 'Shanghai');
execute procedure up_city_add(3, 'Tianjin');
execute procedure up_city_add(4, 'Chongqing');
select * from t_city;
> drop table if exists t_city;
Table dropped.
> create table t_city(f_cityid int, f_cityname varchar(20));
Table created.
> create procedure up_city_add(cityid int, cityname varchar(20))
>
> insert into t_city(f_cityid, f_cityname) values(cityid, cityname);
> end procedure;
Routine created.
> call up_city_add(1, 'Beijing');
Routine executed.
> call up_city_add(2, 'Shanghai');
Routine executed.
> call up_city_add(3, 'Tianjin');
Routine executed.
> call up_city_add(4, 'Chongqing');
Routine executed.
> select * from t_city;
f_cityid f_cityname
1 Beijing
2 Shanghai
3 Tianjin
4 Chongqing
4 row(s) retrieved.
> delete from t_city;
4 row(s) deleted.
> execute procedure up_city_add(1, 'Beijing');
Routine executed.
> execute procedure up_city_add(2, 'Shanghai');
Routine executed.
> execute procedure up_city_add(3, 'Tianjin');
Routine executed.
> execute procedure up_city_add(4, 'Chongqing');
Routine executed.
> select * from t_city;
f_cityid f_cityname
1 Beijing
2 Shanghai
3 Tianjin
4 Chongqing
4 row(s) retrieved.
> drop procedure up_city_add;
Routine dropped.
>
函数
新建函数
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, ...)];
DEMO
drop function if exists fn_get_ymd;
create function fn_get_ymd(dt date)
returning int as year, int as month, int as day
define y,m,d int;
let y = year(dt);
let m = month(dt);
let d = day(dt);
return y,m,d;
end function;
execute function fn_get_ymd(today);
drop function fn_get_ymd;
> drop function if exists fn_get_ymd;
Routine dropped.
> create function fn_get_ymd(dt date)
> returning int as year, int as month, int as day
>
> define y,m,d int;
>
> let y = year(dt);
> let m = month(dt);
> let d = day(dt);
>
> return y,m,d;
> end function;
Routine created.
> execute function fn_get_ymd(today);
year month day
2021 7 6
1 row(s) retrieved.
> drop function fn_get_ymd;
Routine dropped.
>
同义词
新建同义词
create [public | private] synonym [if not exists] <synonym_name> for <table_name | view_name | sequence_name>;
删除同义词
drop synonym [if exists] <synonym_name>;
DEMO
drop table if exists t_user;
create table t_user(f_userid int, f_username varchar(20));
drop synonym if exists syn_user;
create synonym syn_user for t_user;
insert into syn_user values(1, 'gbasedbt');
select * from t_user;
select * from syn_user;
drop synonym syn_user;
> drop table if exists t_user;
Table dropped.
> create table t_user(f_userid int, f_username varchar(20));
Table created.
> drop synonym if exists syn_user;
Synonym dropped.
> create synonym syn_user for t_user;
Synonym created.
> insert into syn_user values(1, 'gbasedbt');
1 row(s) inserted.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> select * from syn_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> drop synonym syn_user;
Synonym dropped.
>