📄 schemas.out
字号:
I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s where exists (select test.t.i from test.t);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- derived table in the from listselect 1 from test.s where exists (select * from (select * from test.t) x);1 -----------1 1 1 ij> select 1 from test.s where exists (select * from (select * from test.t) x (i, s, c, vc) );1 -----------1 1 1 ij> -- subquery in derived tableselect * from (select * from test.s where exists (select * from test.t) and i = 0) a;I |S |C |VC --------------------------------------------------------------------------------0 |0 |0 |0 ij> -- exists under an ORselect * from test.s where (1=2) or exists (select * from test.t);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s where (1=1) or exists (select * from test.t where (1=2));I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- expression subqueries-- non-correlatedselect * from test.s where i = (select i from test.t where i = 0);I |S |C |VC --------------------------------------------------------------------------------0 |0 |0 |0 ij> -- ? parameter on left hand side of expression subqueryprepare subq1 as 'select * from test.s where ? = (select i from test.t where i = 0)';ij> execute subq1 using 'values (0)';I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> remove subq1;ij> -- subquery = subqueryselect * from test.s where(select i from test.t where i = 0) = (select s from test.t where s = 0);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s t1 where(select i from test.t t2 where i = 0) = (select s from test.t t3 where s = 0);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- multiple subqueries at the same levelselect * from test.s where i = (select s from test.t where s = 0) and s = (select i from test.t where i = 2);I |S |C |VC --------------------------------------------------------------------------------ij> -- nested subqueriesselect * from test.s where i = (select i from test.t where s = (select i from test.t where s = 2));I |S |C |VC --------------------------------------------------------------------------------ij> select * from test.s t1where i = (select i from test.t t2 where s = (select i from test.t t3 where s = 2));I |S |C |VC --------------------------------------------------------------------------------ij> -- correlated subqueries-- negative tests-- exists disallowed in select clauseselect (exists (select * from test.ttt where iii = (select 11 from test.tt where ii = i and ii <> 1)) ) from test.s;ERROR 42X01: Syntax error: Encountered "exists" at line 6, column 9.ij> -- multiple matches at parent levelselect * from test.s, test.t where exists (select i from test.tt);ERROR 42X03: Column name 'I' is in more than one table in the FROM list.ij> -- match is against base table, but not derived column listselect * from test.s ss (c1, c2, c3, c4) where exists (select i from test.tt);ERROR 42X04: Column 'I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table.ij> select * from test.s ss (c1, c2, c3, c4) where exists (select ss.i from test.tt);ERROR 42X04: Column 'SS.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SS.I' is not a column in the target table.ij> -- correlation name exists at both levels, but only column match is at-- parent levelselect * from test.s where exists (select s.i from test.tt s);ERROR 42X04: Column 'S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table.ij> -- only match is at peer levelselect * from test.s where exists (select * from test.tt) and exists (select ii from test.t);ERROR 42X04: Column 'II' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'II' is not a column in the target table.ij> -- correlated column in a derived tableselect * from test.s, (select * from test.tt where test.s.i = ii) a;ERROR 42X04: Column 'TEST.S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TEST.S.I' is not a column in the target table.ij> -- positive tests-- skip levels to find matchselect * from test.s where exists (select * from test.ttt where iii = (select 11 from test.tt where ii = i and ii <> 1));I |S |C |VC --------------------------------------------------------------------------------0 |0 |0 |0 ij> -- join in subqueryselect * from test.s where i in (select i from test.t, test.tt where test.s.i <> i and i = ii);I |S |C |VC --------------------------------------------------------------------------------ij> select * from test.s t1 where i in (select i from test.t t2, test.tt t3 where t1.i <> i and i = ii);I |S |C |VC --------------------------------------------------------------------------------ij> -- joins in both query blocksselect test.s.i, test.t.i from test.s, test.t where test.t.i = (select ii from test.ttt, test.tt where test.s.i = test.t.i and test.t.i = test.tt.ii and iii = 22 and ii <> 1);I |I -----------------------0 |0 ij> select t1.i, t2.i from test.s t1, test.t t2 where t2.i = (select ii from test.ttt t3, test.tt t4 where t1.i = t2.i and t2.i = t4.ii and iii = 22 and ii <> 1);I |I -----------------------0 |0 ij> ------------------------------------ updatecreate table test.u (i int, s smallint, c char(30), vc char(30));0 rows inserted/updated/deletedij> insert into test.u select * from test.s;3 rows inserted/updated/deletedij> select * from test.u;I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> update test.u set i = 2where vc <> (select vc from test.s where vc = '1');1 row inserted/updated/deletedij> select * from test.u;I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 2 |0 |0 |0 1 |1 |1 |1 ij> delete from test.u;3 rows inserted/updated/deletedij> insert into test.u select * from test.s;3 rows inserted/updated/deletedij> -- deletedelete from test.u where c < (select c from test.t where c = '2');2 rows inserted/updated/deletedij> select * from test.u;I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL ij> -- reset autocommitautocommit on;ij> -- bug 5146 - drop schema did not invalidate plan for create table.-- now schemas are implictly created.create schema B5146;0 rows inserted/updated/deletedij> create table B5146.DT(i int);0 rows inserted/updated/deletedij> insert into B5146.DT values 5146, 6415;2 rows inserted/updated/deletedij> create schema A5146;0 rows inserted/updated/deletedij> prepare PS5146_TABLE as 'create table A5146.I(i int)';ij> drop schema A5146 restrict;0 rows inserted/updated/deletedij> execute PS5146_TABLE;0 rows inserted/updated/deletedij> insert into A5146.I values 3;1 row inserted/updated/deletedij> select * from A5146.I;I -----------3 ij> drop table A5146.I;0 rows inserted/updated/deletedij> prepare PS5146_VIEW as 'create view A5146.V AS SELECT * FROM B5146.DT';ij> drop schema A5146 restrict;0 rows inserted/updated/deletedij> execute PS5146_VIEW;0 rows inserted/updated/deletedij> select * from A5146.V;I -----------5146 6415 ij> drop view A5146.V;0 rows inserted/updated/deletedij> prepare PS5146_TRIGGER as 'create trigger A5146.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT MODE DB2SQL UPDATE B5146.DT SET I = I + 1';ij> drop schema A5146 restrict;0 rows inserted/updated/deletedij> execute PS5146_TRIGGER;0 rows inserted/updated/deletedij> drop trigger A5146.DT_TRIG;0 rows inserted/updated/deletedij> prepare PS5146_PROCEDURE as 'create procedure A5146.DUMMY() language java external name ''asdf.asdf'' parameter style java';ij> drop schema A5146 restrict;0 rows inserted/updated/deletedij> execute PS5146_PROCEDURE;0 rows inserted/updated/deletedij> drop procedure A5146.DUMMY;0 rows inserted/updated/deletedij> -- check implicit schema creation for all CREATE statements that create schema objects.-- TABLE,VIEW,PROCEDURE TRIGGER, STATEMENT-- Cloudscape requires that the INDEX schema matches the (existing) table schema so-- there is no implict schema creation for CREATE INDEX.prepare ISC_TABLE as 'create table ISC.I(i int)';ij> execute ISC_TABLE;0 rows inserted/updated/deletedij> insert into ISC.I values 3;1 row inserted/updated/deletedij> select * from ISC.I;I -----------3 ij> drop table ISC.I;0 rows inserted/updated/deletedij> drop schema ISC restrict;0 rows inserted/updated/deletedij> prepare ISC_VIEW as 'create view ISC.V AS SELECT * FROM B5146.DT';ij> execute ISC_VIEW;0 rows inserted/updated/deletedij> select * from ISC.V;I -----------5146 6415 ij> drop view ISC.V;0 rows inserted/updated/deletedij> drop schema ISC restrict;0 rows inserted/updated/deletedij> prepare ISC_TRIGGER as 'create trigger ISC.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT MODE DB2SQL UPDATE B5146.DT SET I = I + 1';ij> execute ISC_TRIGGER;0 rows inserted/updated/deletedij> insert into B5146.DT values 999;1 row inserted/updated/deletedij> drop trigger ISC.DT_TRIG;0 rows inserted/updated/deletedij> drop schema ISC restrict;0 rows inserted/updated/deletedij> select * from B5146.DT;I -----------5147 6416 1000 ij> prepare ISC_PROCEDURE as 'create procedure ISC.DUMMY() language java external name ''asdf.asdf'' parameter style java';ij> execute ISC_PROCEDURE;0 rows inserted/updated/deletedij> CALL ISC.DUMMY();ERROR 42X51: The class 'asdf' does not exist or is inaccessible. This can happen if the class is not public.ERROR XJ001: Java exception: 'asdf: java.lang.ClassNotFoundException'.ij> drop procedure ISC.DUMMY;0 rows inserted/updated/deletedij> drop schema ISC restrict;0 rows inserted/updated/deletedij> -- check declare of a temp table does not create a SESSION schema.DECLARE GLOBAL TEMPORARY TABLE SESSION.ISCT(c21 int) on commit delete rows not logged;0 rows inserted/updated/deletedij> select count(*) from SYS.SYSSCHEMAS WHERE SCHEMANAME = 'SESSION';1 -----------0 ij> drop table SESSION.ISCT;0 rows inserted/updated/deletedij> drop table B5146.DT;0 rows inserted/updated/deletedij> drop schema B5146 restrict;0 rows inserted/updated/deletedij> create schema SYSDJD;ERROR 42939: An object cannot be created with the schema name 'SYSDJD'.ij> drop schema SYSDJD restrict;ERROR 42Y07: Schema 'SYSDJD' does not existij> create schema "sys";0 rows inserted/updated/deletedij> drop schema "sys" restrict;0 rows inserted/updated/deletedij> set schema test;0 rows inserted/updated/deletedij> -- drop the tablesdrop table s;0 rows inserted/updated/deletedij> drop table t;0 rows inserted/updated/deletedij> drop table tt;0 rows inserted/updated/deletedij> drop table ttt;0 rows inserted/updated/deletedij> drop table u;0 rows inserted/updated/deletedij> set schema app;0 rows inserted/updated/deletedij> drop schema test restrict;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -