新建数据库
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 db1
rename database db1 to db2;
drop database db2;
[informix@vm84145 ~]$ dbaccess - -
> create database db1;
Database created.
> rename database db1 to db2;
359: Cannot drop or rename the current database or any open database.
Error in line 1
Near character position 25
> database testdb;
Database closed.
Database selected.
> rename database db1 to db2;
Database renamed.
> drop database 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 t1(c_id int,c_name varchar(30));
rename table t1 to t2;
drop table t2;
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> create table t1(c_id int,c_name varchar(30));
Table created.
> rename table t1 to t2;
Table renamed.
> drop table t2;
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 tuser;
create table tuser(c_userid int, c_username varchar(20));
insert into tuser values(1, 'sinodb');
select * from tuser;
alter table tuser add (c_adress varchar(20));
select * from tuser;
rename column tuser.c_adress to c_home;
select * from tuser;
alter table tuser drop (c_home);
select * from tuser;
> drop table if exists tuser;
Table dropped.
> create table tuser(c_userid int, c_username varchar(20));
Table created.
> insert into tuser values(1, 'sinodb');
1 row(s) inserted.
> select * from tuser;
c_userid c_username
1 sinodb
1 row(s) retrieved.
> alter table tuser add (c_adress varchar(20));
Table altered.
> select * from tuser;
c_userid c_username c_adress
1 sinodb
1 row(s) retrieved.
> rename column tuser.c_adress to c_home;
Column renamed.
> select * from tuser;
c_userid c_username c_home
1 sinodb
1 row(s) retrieved.
> alter table tuser drop (c_home);
Table altered.
> select * from tuser;
c_userid c_username
1 sinodb
1 row(s) retrieved.
>
新建视图
create view [if not exists] <view_name> as <query_define>;
删除视图
drop view if exists <view_name>;
DEMO
create view v_tuser as select * from tuser;
select * from v_tuser;
drop view v_tuser;
> create view v_tuser as select * from tuser;
View created.
> select * from v_tuser;
c_userid c_username
1 sinodb
1 row(s) retrieved.
> drop view v_tuser;
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 tuser (c_userid asc);
rename index idx_user to idx_user01;
drop index idx_user01;
> create unique index idx_user on tuser (c_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 tarea;
create table tarea(c_areaid int, c_areaname varchar(20));
create procedure pro_area_add(areaid int, areaname varchar(20))
insert into tarea(c_areaid, c_areaname) values(areaid, areaname);
end procedure;
call pro_area_add(1, 'Beijing');
call pro_area_add(2, 'Shanghai');
call pro_area_add(3, 'Tianjin');
call pro_area_add(4, 'Chongqing');
select * from tarea;
delete from tarea;
execute procedure pro_area_add(1, 'Beijing');
execute procedure pro_area_add(2, 'Shanghai');
execute procedure pro_area_add(3, 'Tianjin');
execute procedure pro_area_add(4, 'Chongqing');
select * from tarea;
> drop table if exists tarea;
Table dropped.
> create table tarea(c_areaid int, c_areaname varchar(20));
Table created.
> create procedure pro_area_add(areaid int, areaname varchar(20))
> insert into tarea(c_areaid, c_areaname) values(areaid, areaname);
> end procedure;
Routine created.
> call pro_area_add(1, 'Beijing');
Routine executed.
> call pro_area_add(2, 'Shanghai');
Routine executed.
> call pro_area_add(3, 'Tianjin');
Routine executed.
> call pro_area_add(4, 'Chongqing');
Routine executed.
> select * from tarea;
c_areaid c_areaname
1 Beijing
2 Shanghai
3 Tianjin
4 Chongqing
4 row(s) retrieved.
> delete from tarea;
4 row(s) deleted.
> execute procedure pro_area_add(1, 'Beijing');
Routine executed.
> execute procedure pro_area_add(2, 'Shanghai');
Routine executed.
> execute procedure pro_area_add(3, 'Tianjin');
Routine executed.
> execute procedure pro_area_add(4, 'Chongqing');
Routine executed.
>
> select * from tarea;
c_areaid c_areaname
1 Beijing
2 Shanghai
3 Tianjin
4 Chongqing
4 row(s) retrieved.
>
新建函数
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
2022 2 17
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,'sinodb');
select * from t_user;
select * from syn_user;
drop synonym syn_user;
> drop synonym if exists syn_user;
Synonym dropped.
> create synonym syn_user for t_user;
Synonym created.
> insert into syn_user values(1,'sinodb');
1 row(s) inserted.
> select * from tuser;
c_userid c_username
1 sinodb
1 row(s) retrieved.
> select * from syn_user;
f_userid f_username
1 sinodb
1 row(s) retrieved.
> drop synonym syn_user;
Synonym dropped.
>
触发器三要素
-事件(Event)
对数据库的插入、删除、修改操作。
当声明的事件发生时，触发器开始工作。
-条件(Condition)
当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果条件成立，则触发器执行相应的动作，否则触发器不做任何事情。
-动作规则(Action Rule)
新增触发器
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>
DEMO
tirgger.sql
[informix@vm84145 ~]$ vim 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 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 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 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 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;
[informix@vm84145 ~]$ dbaccess testdb trigger.sql
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 1
f_message insert:tv
f_operatedate 2022 02月 17日
f_logid 2
f_message insert:a/c
f_operatedate 2022 02月 17日
2 row(s) retrieved.
update action
1 row(s) updated.
search log
f_logid 1
f_message insert:tv
f_operatedate 2022 02月 17日
f_logid 2
f_message insert:a/c
f_operatedate 2022 02月 17日
f_logid 3
f_message update: f_qty->10
f_operatedate 2022 02月 17日
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 1
f_message insert:tv
f_operatedate 2022 02月 17日
f_logid 2
f_message insert:a/c
f_operatedate 2022 02月 17日
f_logid 3
f_message update: f_qty->10
f_operatedate 2022 02月 17日
f_logid 4
f_message select:tv
f_operatedate 2022 02月 17日
f_logid 5
f_message select:a/c
f_operatedate 2022 02月 17日
5 row(s) retrieved.
delete action
1 row(s) deleted.
search log
f_logid 1
f_message insert:tv
f_operatedate 2022 02月 17日
f_logid 2
f_message insert:a/c
f_operatedate 2022 02月 17日
f_logid 3
f_message update: f_qty->10
f_operatedate 2022 02月 17日
f_logid 4
f_message select:tv
f_operatedate 2022 02月 17日
f_logid 5
f_message select:a/c
f_operatedate 2022 02月 17日
f_logid 6
f_message delete:f_saleid->1
f_operatedate 2022 02月 17日
6 row(s) retrieved.
select action
f_saleid f_productname f_qty
2 a/c 20
1 row(s) retrieved.
search log
f_logid 1
f_message insert:tv
f_operatedate 2022 02月 17日
f_logid 2
f_message insert:a/c
f_operatedate 2022 02月 17日
f_logid 3
f_message update: f_qty->10
f_operatedate 2022 02月 17日
f_logid 4
f_message select:tv
f_operatedate 2022 02月 17日
f_logid 5
f_message select:a/c
f_operatedate 2022 02月 17日
f_logid 6
f_message delete:f_saleid->1
f_operatedate 2022 02月 17日
f_logid 7
f_message select:a/c
f_operatedate 2022 02月 17日
7 row(s) retrieved.
Database closed.
[informix@vm84145 ~]$ dbaccess testdb trigger.sql
删除触发器
drop trigger <trigger_name>;
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;
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> 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.
>
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, ...])>;
DEMO
drop table if exists tuser1;
create table tuser1(c_userid int, c_username varchar(20));
insert into tuser1 values(1, 'SINODB');
select * from tuser1;
drop table if exists tuser2;
create table tuser2(c_userid int, c_username varchar(20));
insert into tuser2 select * from tuser1;
select * from tuser2;
drop table if exists tuser3;
create table tuser3(c_userid int, c_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 tuser3 execute function fn_user_add(10);
select * from tuser3;
> drop table if exists tuser1;
Table dropped.
> create table tuser1(c_userid int, c_username varchar(20));
Table created.
> insert into tuser1 values(1, 'SINODB');
1 row(s) inserted.
> select * from tuser1;
c_userid c_username
1 SINODB
1 row(s) retrieved.
> drop table if exists tuser2;
Table dropped.
> create table tuser2(c_userid int, c_username varchar(20));
Table created.
> insert into tuser2 select * from tuser1;
1 row(s) inserted.
> select * from tuser2;
c_userid c_username
1 SINODB
1 row(s) retrieved.
> drop table if exists tuser3;
Table dropped.
> create table tuser3(c_userid int, c_username varchar(20));
Table created.
> drop function if exists fn_user_add;
Routine dropped.
> 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;
Routine created.
> insert into tuser3 execute function fn_user_add(10);
10 row(s) inserted.
> select * from tuser3;
c_userid c_username
1 user_1
2 user_2
3 user_3
4 user_4
5 user_5
6 user_6
7 user_7
8 user_8
9 user_9
10 user_10
10 row(s) retrieved.
>
update <table_name | view_name | synonym_name> 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 from <table_name | view_name | synonym_name> [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.
> 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 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, ...);
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;
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> 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.
>
数据准备
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] <* | 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], ...]];
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 RMB 6000.00
2 1 Jack RMB 8000.00
3 1 Mary RMB 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 RMB 9000.00
1 RMB 8000.00
2 RMB 7500.00
5 RMB 6500.00
4 row(s) retrieved.
>
自连接
select * from <table_name> a, <table_name> 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 <table_name1> a
inner join <table_name2> 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 <table_name1> a
left outer join <table_name2> 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 <table_name1> a
right outer join <table_name2> b
on a.column_name1 = b.column_name2;
DMEO
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;
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> 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 <table_name1> a
full outer join <table_name2> 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 <table_name1>
where column_name1 in
(select column_name2 from <table_name2>;
select *
from <table_name1>
where column_name1 in <(val1, val2, ...)>;
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 RMB 6000.00
2 1 Jack RMB 8000.00
3 1 Mary RMB 6600.00
4 2 Henry RMB 5000.00
5 2 Rose RMB 7500.00
7 3 Kate RMB 5000.00
8 3 Bob RMB 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 RMB 6500.00
7 3 Kate RMB 5000.00
8 3 Bob RMB 9000.00
3 row(s) retrieved.
>
select *
from <table_name1>
where exists (select 1 from <table_name2> 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 RMB 6000.00
2 1 Jack RMB 8000.00
3 1 Mary RMB 6600.00
4 2 Henry RMB 5000.00
5 2 Rose RMB 7500.00
7 3 Kate RMB 5000.00
8 3 Bob RMB 9000.00
7 row(s) retrieved.
>