GBase 8s SQL语言分类及基本语法

GBase 8s SQL语言分类及基本语法

DDL

DDL语句：用来创建数据库以及定义其表结构、视图、索引等。
关键词有CREATE、DROP、ALTER、RENAME等。

数据库

新建数据库

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;

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.

>

表

新建表

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