GBase 8s 基本语法

2023-06-19 11:55| 来源: 网络整理| 查看: 265

GBase 8s 基本语法 DDL数据库表列视图索引存储过程函数同义词触发器 DMLinsertupdatedeletemerge DQL数据准备单表查询多表关联查询自连接内连接左连接右连接全连接子查询INEXISTS

DDL 数据库

新建数据库

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

重命名数据库

rename database to ;

删除数据库

drop database [if exists] ;

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. > 表

新建表

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

重命名表

rename table to ;

删除表

drop table [if exists] ;

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 add (new_column_name1 data_type1 [before old_column_name1] [, new_column_name2 data_type2 [before old_column_name2], ...]);

删除列

alter table drop (old_column_name1[, old_column_name2, ...]);

重命名列

rename column . to ;

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] as ;

删除视图

drop view if exists ;

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] on ( [asc | desc]);

重命名索引

rename index to ;

删除索引

drop index [if exists] ;

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] (param1 data_type1, param2 data_type2, ...) end procedure;

删除存储过程

drop procedure [if exists] [(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] (param1 data_type1, param2 data_type2, ...) returning data_type1 [as var1] [, data_type2 [as var2], ...] return var1[, var2, ...]; end function;

删除函数

drop function [if exists] [(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] for ;

删除同义词

drop synonym [if exists] ;

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. > 触发器

触发器三要素

事件(Event) 对数据库的插入、删除、修改操作。当声明的事件发生时，触发器开始工作。

条件(Condition) 当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果条件成立，则触发器执行相应的动作，否则触发器不做任何事情。

动作规则(Action Role)

新增触发器

create [or replace] tirgger on when

DEMO

[gbasedbt@devsvr train]$ cat trigger.sql drop table if exists t_log; create table t_log(f_logid serial, f_message varchar(50), f_operatedate date); drop table if exists t_sale; create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int); create or replace trigger trg_sale_insert insert on t_sale referencing new as new_item for each row ( insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today) ); create or replace trigger trg_sale_update update of f_qty on t_sale referencing old as old_item for each row ( insert into t_log(f_message, f_operatedate) values(concat('update: f_qty->', to_char(old_item.f_qty)), today) ); create or replace trigger trg_sale_delete delete on t_sale referencing old as old_item for each row ( insert into t_log(f_message, f_operatedate) values(concat('delete:f_saleid->', to_char(old_item.f_saleid)), today) ); create or replace trigger trg_sale_select select on t_sale referencing old as old_item for each row ( insert into t_log(f_message, f_operatedate) values(concat('select:', old_item.f_productname), today) ); !echo "insert action" insert into t_sale(f_productname, f_qty) values('tv', 10); insert into t_sale(f_productname, f_qty) values('a/c', 20); !echo "search log" select * from t_log; !echo "update action" update t_sale set f_qty = 15 where f_productname = 'tv'; !echo "search log" select * from t_log; !echo "select action" select * from t_sale; !echo "search log" select * from t_log; !echo "delete action" delete from t_sale where f_productname = 'tv'; !echo "search log" select * from t_log; !echo "select action" select * from t_sale; !echo "search log" select * from t_log; [gbasedbt@devsvr train]$ dbaccess mydb trigger.sql Your evaluation license will expire on 2022-06-18 00:00:00 Database selected. Table dropped. Table created. Table dropped. Table created. Trigger created. Trigger created. Trigger created. Trigger created. insert action 1 row(s) inserted. 1 row(s) inserted. search log f_logid f_message f_operatedate 1 insert:tv 07/05/2021 2 insert:a/c 07/05/2021 2 row(s) retrieved. update action 1 row(s) updated. search log f_logid f_message f_operatedate 1 insert:tv 07/05/2021 2 insert:a/c 07/05/2021 3 update: f_qty->10 07/05/2021 3 row(s) retrieved. select action f_saleid f_productname f_qty 1 tv 15 2 a/c 20 2 row(s) retrieved. search log f_logid f_message f_operatedate 1 insert:tv 07/05/2021 2 insert:a/c 07/05/2021 3 update: f_qty->10 07/05/2021 4 select:tv 07/05/2021 5 select:a/c 07/05/2021 5 row(s) retrieved. delete action 1 row(s) deleted. search log f_logid f_message f_operatedate 1 insert:tv 07/05/2021 2 insert:a/c 07/05/2021 3 update: f_qty->10 07/05/2021 4 select:tv 07/05/2021 5 select:a/c 07/05/2021 6 delete:f_saleid->1 07/05/2021 6 row(s) retrieved. select action f_saleid f_productname f_qty 2 a/c 20 1 row(s) retrieved. search log f_logid f_message f_operatedate 1 insert:tv 07/05/2021 2 insert:a/c 07/05/2021 3 update: f_qty->10 07/05/2021 4 select:tv 07/05/2021 5 select:a/c 07/05/2021 6 delete:f_saleid->1 07/05/2021 7 select:a/c 07/05/2021 7 row(s) retrieved. Database closed. [gbasedbt@devsvr train]$

删除触发器

drop trigger ;

DEMO

drop trigger if exists trg_sale_insert; drop trigger if exists trg_sale_update; drop trigger if exists trg_sale_delete; drop trigger if exists trg_sale_select; > drop trigger if exists trg_sale_insert; Trigger dropped. > drop trigger if exists trg_sale_update; Trigger dropped. > drop trigger if exists trg_sale_delete; Trigger dropped. > drop trigger if exists trg_sale_select; Trigger dropped. > DML insert insert into [column_name1, column_name2, ...] values(value1, value2, ...); insert into [column_name1, column_name2, ...] select col_name1, col_name2, ... ; insert into [column_name1, column_name2, ...] execute function ;

DEMO

drop table if exists t_user1; create table t_user1(f_userid int, f_username varchar(20)); insert into t_user1 values(1, 'gbasedbt'); select * from t_user1; drop table if exists t_user2; create table t_user2(f_userid int, f_username varchar(20)); insert into t_user2 select * from t_user1; select * from t_user2; drop table if exists t_user3; create table t_user3(f_userid int, f_username varchar(20)); drop function if exists fn_user_add; create function fn_user_add(user_num int) returning int as userid, varchar(20) as username define i int; define userid int; define username varchar(20); for i = 1 to user_num let userid = i; let username = concat('user_', to_char(i)); return userid, username with resume; end for; end function; insert into t_user3 execute function fn_user_add(10); select * from t_user3; update update set column_name1 = value1[, column_name2 = value2, ... ] [where condition];

DEMO

drop table if exists t_user; create table t_user(f_userid int, f_username varchar(50), f_age int); insert into t_user values(1, 'Mary', 18); insert into t_user values(2, 'Jack', 21); select * from t_user; update t_user set f_age = 20 where f_userid = 1; select * from t_user; > drop table if exists t_user; Table dropped. > create table t_user(f_userid int, f_username varchar(50), f_age int); Table created. > insert into t_user values(1, 'Mary', 18); 1 row(s) inserted. > insert into t_user values(2, 'Jack', 21); 1 row(s) inserted. > select * from t_user; f_userid f_username f_age 1 Mary 18 2 Jack 21 2 row(s) retrieved. > update t_user set f_age = 20 where f_userid = 1; 1 row(s) updated. > select * from t_user; f_userid f_username f_age 1 Mary 20 2 Jack 21 2 row(s) retrieved. > delete delete from [where condition];

DEMO

select * from t_user; delete from t_user where f_userid = 1; select * from t_user; > select * from t_user; f_userid f_username f_age 1 Mary 20 2 Jack 21 2 row(s) retrieved. > > > > select * from t_user; f_userid f_username f_age 1 Mary 20 2 Jack 21 2 row(s) retrieved. > delete from t_user where f_userid = 1; 1 row(s) deleted. > select * from t_user; f_userid f_username f_age 2 Jack 21 1 row(s) retrieved. > merge merge into as t using 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, ...);

DEMO

drop table if exists t_user1; create table t_user1(f_userid int, f_username varchar(20), f_age int); insert into t_user1 values(1, 'Tom', 28); insert into t_user1 values(2, 'Jack', 26); insert into t_user1 values(4, 'Rose', 18); select * from t_user1; drop table if exists t_user2; create table t_user2(f_userid int, f_username varchar(20), f_age int); insert into t_user2 values(3, 'Jim', 25); insert into t_user2 values(4, 'Rose', 23); insert into t_user2 values(5, 'Mike', 21); insert into t_user2 values(6, 'Bill', 19); select * from t_user2; merge into t_user1 a using t_user2 b on a.f_userid = b.f_userid when matched then update set a.f_age = b.f_age when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age); select * from t_user1; > drop table if exists t_user1; Table dropped. > create table t_user1(f_userid int, f_username varchar(20), f_age int); Table created. > insert into t_user1 values(1, 'Tom', 28); 1 row(s) inserted. > insert into t_user1 values(2, 'Jack', 26); 1 row(s) inserted. > insert into t_user1 values(4, 'Rose', 18); 1 row(s) inserted. > select * from t_user1; f_userid f_username f_age 1 Tom 28 2 Jack 26 4 Rose 18 3 row(s) retrieved. > drop table if exists t_user2; Table dropped. > create table t_user2(f_userid int, f_username varchar(20), f_age int); Table created. > insert into t_user2 values(3, 'Jim', 25); 1 row(s) inserted. > insert into t_user2 values(4, 'Rose', 23); 1 row(s) inserted. > insert into t_user2 values(5, 'Mike', 21); 1 row(s) inserted. > insert into t_user2 values(6, 'Bill', 19); 1 row(s) inserted. > select * from t_user2; f_userid f_username f_age 3 Jim 25 4 Rose 23 5 Mike 21 6 Bill 19 4 row(s) retrieved. > merge into t_user1 a > using t_user2 b > on a.f_userid = b.f_userid > when matched then update set a.f_age = b.f_age > when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age); 4 row(s) merged. > select * from t_user1; f_userid f_username f_age 1 Tom 28 2 Jack 26 4 Rose 23 3 Jim 25 5 Mike 21 6 Bill 19 6 row(s) retrieved. > DQL 数据准备 drop table if exists t_dept; create table t_dept(f_deptid int, f_deptname varchar(10), f_parentid int); insert into t_dept values(0, 'MS', -1); insert into t_dept values(1, 'Dev', 0); insert into t_dept values(2, 'Test', 1); insert into t_dept values(3, 'Market', 0); insert into t_dept values(4, 'HR', 0); drop table if exists t_employee; create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(10), f_salary money); insert into t_employee values(1, 1, 'Tom', 6000.00); insert into t_employee values(2, 1, 'Jack', 8000.00); insert into t_employee values(3, 1, 'Mary', 6600.00); insert into t_employee values(4, 2, 'Henry', 5000.00); insert into t_employee values(5, 2, 'Rose', 7500.00); insert into t_employee values(6, 5, 'Bill', 6500.00); insert into t_employee values(7, 3, 'Kate', 5000.00); insert into t_employee values(8, 3, 'Bob', 9000.00); 单表查询 select [first n] from ; select from group by column_name_n1[, column_name_n2, ...] [order by column_name_m1 [asc | desc][, column_name_m2 [asc | desc], ...]];

DEMO

select first 3 * from t_employee; select f_deptid, max(f_salary) as f_salary from t_employee group by f_deptid order by f_salary desc; > select first 3 * from t_employee; f_employeeid f_deptid f_employeename f_salary 1 1 Tom $6000.00 2 1 Jack $8000.00 3 1 Mary $6600.00 3 row(s) retrieved. > > select f_deptid, max(f_salary) as f_salary from t_employee group by f_deptid order by f_salary desc; f_deptid f_salary 3 $9000.00 1 $8000.00 2 $7500.00 5 $6500.00 4 row(s) retrieved. > 多表关联查询自连接 select * from a, b where a.f_column_name1 = b.f_column_name2;

DEMO

select a.*, b.f_deptname as f_parentname from t_dept a, t_dept b where a.f_parentid = b.f_deptid; > select a.*, b.f_deptname as f_parentname from t_dept a, t_dept b where a.f_parentid = b.f_deptid; f_deptid f_deptname f_parentid f_parentname 1 Dev 0 MS 2 Test 1 Dev 3 Market 0 MS 4 HR 0 MS 4 row(s) retrieved. > 内连接 selct table_name1.column_name1, table_name1.column_name2, ... , table_name2.column_name1, table_name2.column_name2, ... from a inner join b on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a inner join t_dept b on a.f_deptid = b.f_deptid; > select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a inner join t_dept b on a.f_deptid = b.f_deptid;> > > f_employeeid f_employeename f_deptname 1 Tom Dev 2 Jack Dev 3 Mary Dev 4 Henry Test 5 Rose Test 7 Kate Market 8 Bob Market 7 row(s) retrieved. > 左连接 selct table_name1.column_name1, table_name1.column_name2, ... , table_name2.column_name1, table_name2.column_name2, ... from a left outer join b on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a left outer join t_dept b on a.f_deptid = b.f_deptid; > select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a left outer join t_dept b on a.f_deptid = b.f_deptid;> > > f_employeeid f_employeename f_deptname 1 Tom Dev 2 Jack Dev 3 Mary Dev 4 Henry Test 5 Rose Test 6 Bill 7 Kate Market 8 Bob Market 8 row(s) retrieved. > 右连接 selct table_name1.column_name1, table_name1.column_name2, ... , table_name2.column_name1, table_name2.column_name2, ... from a right outer join b on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a right outer join t_dept b on a.f_deptid = b.f_deptid; > select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a right outer join t_dept b on a.f_deptid = b.f_deptid;> > > f_employeeid f_employeename f_deptname MS 1 Tom Dev 2 Jack Dev 3 Mary Dev 4 Henry Test 5 Rose Test 7 Kate Market 8 Bob Market HR 9 row(s) retrieved. > 全连接 selct table_name1.column_name1, table_name1.column_name2, ... , table_name2.column_name1, table_name2.column_name2, ... from a full outer join b on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a full outer join t_dept b on a.f_deptid = b.f_deptid; > select a.f_employeeid, a.f_employeename, b.f_deptname from t_employee a full outer join t_dept b on a.f_deptid = b.f_deptid;> > > f_employeeid f_employeename f_deptname 1 Tom Dev 2 Jack Dev 3 Mary Dev 4 Henry Test 5 Rose Test 6 Bill 7 Kate Market 8 Bob Market MS HR 10 row(s) retrieved. > 子查询 IN select * from where column_name1 in (select column_name2 from ; select * from where column_name1 in ;

DEMO

select * from t_employee where f_deptid in (select f_deptid from t_dept); select * from t_employee where f_deptid in (3, 5); > select * from t_employee where f_deptid in (select f_deptid from t_dept); f_employeeid f_deptid f_employeename f_salary 1 1 Tom $6000.00 2 1 Jack $8000.00 3 1 Mary $6600.00 4 2 Henry $5000.00 5 2 Rose $7500.00 7 3 Kate $5000.00 8 3 Bob $9000.00 7 row(s) retrieved. > select * from t_employee where f_deptid in (3, 5); f_employeeid f_deptid f_employeename f_salary 6 5 Bill $6500.00 7 3 Kate $5000.00 8 3 Bob $9000.00 3 row(s) retrieved. > EXISTS select * from where exists (select 1 from where table_name1.column_name1 = table_name2.column_name2);

DEMO

select * from t_employee a where exists (select 1 from t_dept b where b.f_deptid = a.f_deptid); > select * > from t_employee a > where exists (select 1 from t_dept b where b.f_deptid = a.f_deptid); f_employeeid f_deptid f_employeename f_salary 1 1 Tom $6000.00 2 1 Jack $8000.00 3 1 Mary $6600.00 4 2 Henry $5000.00 5 2 Rose $7500.00 7 3 Kate $5000.00 8 3 Bob $9000.00 7 row(s) retrieved. >

【本文地址】

公司简介

联系我们