数据库运维
记录DBA学习成长历程

第6章:GBase 8s 基本语法

文章目录

1 DDL

—1.1 库操作

新建数据库

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

—1.2 表

新建表

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

举例

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.

—1.3 列

新增列

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], ...]);

重命名列

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

举例

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.

—1.4 视图

新建视图

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

删除视图

drop view if exists <view_name>;

举例

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.

> 

—1.5 索引

新建索引

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

>

—1.6 存储过程

新建存储过程

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, ...)];

举例

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.

> 

—1.7 函数

新建函数

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, ...)];

举例

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.

> 

—1.8 同义词

新建同义词

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

删除同义词

drop synonym [if exists] <synonym_name>;

举例

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.

> 

—1.9 触发器

触发器三要素

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

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

3.动作规则(Action Role)

新增触发器

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>

举例

[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 <trigger_name>;

举例

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.

>

2 DML

—2.1 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, ...])>;

举例

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;

—2.2 update

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

举例

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.

> 

—2.3 delete

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

举例

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.

>

—2.4 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, ...);

举例

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.

> 

3 DQL

—3.1 数据准备

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);

—3.2 单表查询

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

>

—3.3 多表关联查询

——3.3.1 自连接

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

举例

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.

>

——3.3.2 内连接

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;

举例

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.

> 

——3.3.3 左连接

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;

举例

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.

> 

——3.3.4 右连接

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;

举例

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.

> 

——3.3.5 全连接

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;

举例

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.

> 

—3.4 子查询

——3.4.1 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, ...)>;

举例

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.

> 

——3.4.2 EXISTS

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

举例

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.

> 

 

 

赞(0)
MySQL学习笔记 » 第6章:GBase 8s 基本语法