testsimple.in.txt

非常棒的java数据库

CREATE TABLE TEST(ID IDENTITY);
ALTER TABLE TEST ALTER COLUMN ID RESTART WITH ? {1:10};
INSERT INTO TEST VALUES(NULL);
SELECT * FROM TEST;
> 10;
DROP TABLE TEST;
CREATE SEQUENCE TEST_SEQ;
ALTER SEQUENCE TEST_SEQ RESTART WITH ? INCREMENT BY ? {1:20, 2: 3};
SELECT NEXT VALUE FOR TEST_SEQ;
> 20;
SELECT NEXT VALUE FOR TEST_SEQ;
> 23;
DROP SEQUENCE TEST_SEQ;

create schema Contact;
CREATE TABLE Account (id BIGINT);
CREATE TABLE Person (id BIGINT, FOREIGN KEY (id) REFERENCES Account(id));
CREATE TABLE Contact.Contact (id BIGINT, FOREIGN KEY (id) REFERENCES public.Person(id));
drop schema contact;
drop table account, person;

create schema Contact;
CREATE TABLE Account (id BIGINT primary key);
CREATE TABLE Person (id BIGINT primary key, FOREIGN KEY (id) REFERENCES Account);
CREATE TABLE Contact.Contact (id BIGINT primary key, FOREIGN KEY (id) REFERENCES public.Person);
drop schema contact;
drop table account, person;

select extract(hour from timestamp '2001-02-03 14:15:16');
> 14;
select extract(hour from '2001-02-03 14:15:16');
> 14;
select hour('2001-02-03 14:15:16');
> 14;

CREATE TABLE TEST(A int NOT NULL, B int NOT NULL, C int) ;
ALTER TABLE TEST ADD CONSTRAINT CON UNIQUE(A,B);
ALTER TABLE TEST DROP C;
ALTER TABLE TEST DROP CONSTRAINT CON;
ALTER TABLE TEST DROP B;
DROP TABLE TEST;

select count(d.*) from dual d group by d.x;
> 1;

create table test(id int);
select count(*) from (select * from ((select * from test) union (select * from test)) a) b where id = 0;
> 0;
select count(*) from (select * from ((select * from test) union select * from test) a) b where id = 0;
> 0;
select count(*) from (select * from (select * from test union select * from test) a) b where id = 0;
> 0;
select 1 from ((test d1 inner join test d2 on d1.id = d2.id) inner join test d3 on d1.id = d3.id) inner join test d4 on d4.id = d1.id;
drop table test;

select lpad('string', 10, '+');
> ++++string;
select rpad('string', 10, '+');
> string++++;
select lpad('string', 10);
>     string;

select count(*) from (select * from dual union select * from dual) where x = 0;
> 0;
select count(*) from (select * from (select * from dual union select * from dual)) where x = 0;
> 0;

select instr('abcisj','s', -1) from dual;
> 5;
CREATE TABLE TEST(ID INT);
INSERT INTO TEST VALUES(1), (2), (3);
create index idx_desc on test(id desc);
select * from test where id between 0 and 1;
> 1;
select * from test where id between 3 and 4;
> 3;
drop table test;

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'HelloWorld'), (3, 'HelloWorldWorld');
SELECT COUNT(*) FROM TEST WHERE NAME REGEXP 'World';
> 2;
SELECT NAME FROM TEST WHERE NAME REGEXP 'WorldW';
> HelloWorldWorld;
drop table test;

select * from (select x from (select x from dual)) where 1=x;
> 1;
CREATE VIEW TEST_VIEW AS SELECT X FROM (SELECT X FROM DUAL);
SELECT * FROM TEST_VIEW;
> 1;
SELECT * FROM TEST_VIEW;
> 1;
DROP VIEW TEST_VIEW;

SELECT X FROM (SELECT X, X AS "XY" FROM DUAL) WHERE X=1;
> 1;
SELECT X FROM (SELECT X, X AS "X Y" FROM DUAL) WHERE X=1;
> 1;
SELECT X FROM (SELECT X, X AS "X Y" FROM DUAL AS "D Z") WHERE X=1;
> 1;

select * from (select x from dual union select convert(x, int) from dual) where x=0;
create table test(id int);
insert into scriptSimple.public.test(id) values(1), (2);
update test t set t.id=t.id+1;
update public.test set public.test.id=1;
select count(scriptSimple.public.test.id) from scriptSimple.public.test;
> 2;
update scriptSimple.public.test set scriptSimple.public.test.id=1;
drop table scriptSimple.public.test;

select year(timestamp '2007-07-26 18:44:26.109000 +02:00');
> 2007;

create table test(id int primary key);
begin;
insert into test values(1);
rollback;
insert into test values(2);
rollback;
begin;
insert into test values(3);
commit;
insert into test values(4);
rollback;
select group_concat(id order by id) from test;
> 2,3,4;
drop table test;

create table test();
insert into test values();
ALTER TABLE TEST ADD ID INTEGER;
select count(*) from test;
> 1;
drop table test;

select * from dual where 'a_z' like '%=_%' escape '=';
> 1;

create table test as select 1 from dual union all select 2 from dual;
drop table test;

create table test_table(column_a integer);
insert into test_table values(1);
create view test_view AS SELECT * FROM (SELECT DISTINCT * FROM test_table) AS subquery;
select * FROM test_view;
> 1;
drop view test_view;
drop table test_table;

CREATE TABLE TEST(ID INT);
INSERT INTO TEST VALUES(1);
CREATE VIEW TEST_VIEW AS SELECT COUNT(ID) X FROM TEST;
explain SELECT * FROM TEST_VIEW WHERE X>1;
DROP VIEW TEST_VIEW;
DROP TABLE TEST;

create table test1(id int);
insert into test1 values(1), (1), (2), (3);
select sum(C0) from (select count(*) AS C0 from (select distinct * from test1) as temp);
> 3;
drop table test1;

create table test(id int primary key check id>1);
drop table test;
create table table1(f1 int not null primary key);
create table table2(f2 int not null references table1(f1) on delete cascade);
drop table table2;
drop table table1;
create table table1(f1 int not null primary key);
create table table2(f2 int not null primary key references table1(f1));
drop table table1;
drop table table2;

select case when 1=null then 1 else 2 end;
> 2;

select case (1) when 1 then 1 else 2 end;
> 1;

create table test(id int);
insert into test values(1);
select distinct id from test a order by a.id;
> 1;
drop table test;

create table FOO (ID int, A number(18, 2));
insert into FOO (ID, A) values (1, 10.0), (2, 20.0);
select SUM (CASE when ID=1 then 0 ELSE A END) col0 from Foo;
> 20.00;
drop table FOO;

select (SELECT true)+1 GROUP BY 1;
> 2;
create table FOO (ID int, A number(18, 2));
insert into FOO (ID, A) values (1, 10.0), (2, 20.0);
select SUM (CASE when ID=1 then A ELSE 0 END) col0 from Foo;
> 10.00;
drop table FOO;

create table A ( ID integer,  a1 varchar(20) );
create table B ( ID integer,  AID integer,  b1 varchar(20));
create table C ( ID integer,  BId integer,  c1 varchar(20));
insert into A (ID, a1) values (1, 'a1');
insert into A (ID, a1) values (2, 'a2');
select count(*) from A left outer join (B  inner join C on C.BID=B.ID )  on B.AID=A.ID where A.id=1;
> 1;
select count(*) from A left outer join (B  left join C on C.BID=B.ID )  on B.AID=A.ID where A.id=1;
> 1;
select count(*) from A left outer join B on B.AID=A.ID inner join C on C.BID=B.ID where A.id=1;
> 0;
select count(*) from (A left outer join B on B.AID=A.ID) inner join C on C.BID=B.ID where A.id=1;
> 0;
drop table a, b, c;

create schema a;
create table a.test(id int);
insert into a.test values(1);
create schema b;
create table b.test(id int);
insert into b.test values(2);
select a.test.id + b.test.id from a.test, b.test;
> 3;
drop schema a;
drop schema b;

select date '+0011-01-01';
> 0011-01-01;
select date'-0010-01-01';
> 0011-01-01;
select datediff('HOUR', timestamp '2007-01-06 10:00:00Z', '2007-01-06 10:00:00Z');
> 0;
select datediff('HOUR', timestamp '1234-05-06 10:00:00+01:00', '1234-05-06 10:00:00+02:00');
> -1;
select datediff('HOUR', timestamp '1234-05-06 10:00:00+01:00', '1234-05-06 10:00:00-02:00');
> 3;

create schema TEST_SCHEMA;
create table TEST_SCHEMA.test(id int);
create sequence TEST_SCHEMA.TEST_SEQ;
select TEST_SCHEMA.TEST_SEQ.CURRVAL;
> 0;
select TEST_SCHEMA.TEST_SEQ.nextval;
> 1;
drop schema TEST_SCHEMA;

create table test(id int);
create trigger TEST_TRIGGER before insert on test call "org.h2.test.db.TestTriggersConstraints";
comment on trigger TEST_TRIGGER is 'just testing';
select remarks from information_schema.triggers where trigger_name = 'TEST_TRIGGER';
> just testing;
@reconnect;
select remarks from information_schema.triggers where trigger_name = 'TEST_TRIGGER';
> just testing;
drop trigger TEST_TRIGGER;
@reconnect;

create alias parse_long for "java.lang.Long.parseLong";
comment on alias parse_long is 'Parse a long with base';
select remarks from information_schema.function_aliases where alias_name = 'PARSE_LONG';
> Parse a long with base;
@reconnect;
select remarks from information_schema.function_aliases where alias_name = 'PARSE_LONG';
> Parse a long with base;
drop alias parse_long;
@reconnect;

create role hr;
comment on role hr is 'Human Resources';
select remarks from information_schema.roles where name = 'HR';
> Human Resources;
@reconnect;
select remarks from information_schema.roles where name = 'HR';
> Human Resources;
create user abc password 'x';
grant hr to abc;
drop role hr;
@reconnect;
drop user abc;

create domain email as varchar(100) check instr(value, '@') > 0;
comment on domain email is 'must contain @';
select remarks from information_schema.domains where domain_name = 'EMAIL';
> must contain @;
@reconnect;
select remarks from information_schema.domains where domain_name = 'EMAIL';
> must contain @;
drop domain email;
@reconnect;

create schema tests;
set schema tests;
create sequence walk;
comment on schema tests is 'Test Schema';
comment on sequence walk is 'Walker';
select remarks from information_schema.schemata where schema_name = 'TESTS';
> Test Schema;
select remarks from information_schema.sequences where sequence_name = 'WALK';
> Walker;
@reconnect;
select remarks from information_schema.schemata where schema_name = 'TESTS';
> Test Schema;
select remarks from information_schema.sequences where sequence_name = 'WALK';
> Walker;
drop schema tests;
@reconnect;

create constant abc value 1;
comment on constant abc is 'One';
select remarks from information_schema.constants where constant_name = 'ABC';
> One;
@reconnect;
select remarks from information_schema.constants where constant_name = 'ABC';
> One;
drop constant abc;
drop table test;
@reconnect;

create table test(id int);
alter table test add constraint const1 unique(id);
create index IDX_ID on test(id);
comment on constraint const1 is 'unique id';
comment on index IDX_ID is 'id_index';
select remarks from information_schema.constraints where constraint_name = 'CONST1';
> unique id;
select remarks from information_schema.indexes where index_name = 'IDX_ID';
> id_index;
@reconnect;
select remarks from information_schema.constraints where constraint_name = 'CONST1';
> unique id;
select remarks from information_schema.indexes where index_name = 'IDX_ID';
> id_index;
drop table test;
@reconnect;

create user sales password '1';
comment on user sales is 'mr. money';
select remarks from information_schema.users where name = 'SALES';
> mr. money;
@reconnect;
select remarks from information_schema.users where name = 'SALES';
> mr. money;
alter user sales rename to SALES_USER;
select remarks from information_schema.users where name = 'SALES_USER';
> mr. money;
@reconnect;
select remarks from information_schema.users where name = 'SALES_USER';
> mr. money;

create table test(id int);
create linked table test_link('org.h2.Driver', 'jdbc:h2:mem:', 'sa', 'sa', 'DUAL');
comment on table test_link is '123';
select remarks from information_schema.tables where table_name = 'TEST_LINK';
> 123;
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST_LINK';
> 123;
comment on table test_link is 'xyz';
select remarks from information_schema.tables where table_name = 'TEST_LINK';
> xyz;
alter table test_link rename to test_l;
select remarks from information_schema.tables where table_name = 'TEST_L';
> xyz;
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST_L';
> xyz;
drop table test;
@reconnect;

create table test(id int);
create view test_v as select * from test;
comment on table test_v is 'abc';
select remarks from information_schema.tables where table_name = 'TEST_V';
> abc;
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST_V';
> abc;
alter table test_v rename to TEST_VIEW;
select remarks from information_schema.tables where table_name = 'TEST_VIEW';
> abc;
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST_VIEW';
> abc;
drop table test;
@reconnect;

create table test(a int);
comment on table test is 'hi';
select remarks from information_schema.tables where table_name = 'TEST';
> hi;
alter table test add column b int;
select remarks from information_schema.tables where table_name = 'TEST';
> hi;
alter table test rename to test1;
select remarks from information_schema.tables where table_name = 'TEST1';
> hi;
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST1';
> hi;
comment on table test1 is 'ho';
@reconnect;
select remarks from information_schema.tables where table_name = 'TEST1';
> ho;
drop table test1;

create table test(a int, b int);
comment on column test.b is 'test';
select remarks from information_schema.columns where table_name = 'TEST' and column_name = 'B';
> test;
@reconnect;
select remarks from information_schema.columns where table_name = 'TEST' and column_name = 'B';
> test;
alter table test drop column b;
@reconnect;
comment on column test.a is 'ho';
select remarks from information_schema.columns where table_name = 'TEST' and column_name = 'A';
> ho;
@reconnect;
select remarks from information_schema.columns where table_name = 'TEST' and column_name = 'A';
> ho;
drop table test;
@reconnect;

create table test(a int);
comment on column test.a is 'test';
alter table test rename to test2;
@reconnect;
select remarks from information_schema.columns where table_name = 'TEST2';
> test;
@reconnect;
select remarks from information_schema.columns where table_name = 'TEST2';
> test;
drop table test2;
@reconnect;