📄 test.in.txt
字号:
> 1 2
> - -
> 1 2
> rows: 1
CREATE TABLE TEST(A VARCHAR, B VARCHAR, C VARCHAR AS LOWER(A));
> ok
ALTER TABLE TEST DROP COLUMN B;
> ok
DROP TABLE TEST;
> ok
create table t1(c1 int, c2 int);
> ok
create table t2(c1 int, c2 int);
> ok
insert into t1 values(1, null), (2, 2), (3, 3);
> update count: 3
insert into t2 values(1, 1), (1, 2), (2, null), (3, 3);
> update count: 4
select * from t2 where c1 not in(select c2 from t1);
> C1 C2
> -- --
> rows: 0
select * from t2 where c1 not in(null, 2, 3);
> C1 C2
> -- --
> rows: 0
select * from t1 where c2 not in(select c1 from t2);
> C1 C2
> -- --
> rows: 0
select * from t1 where not exists(select * from t2 where t1.c2=t2.c1);
> C1 C2
> -- ----
> 1 null
> rows: 1
drop table t1;
> ok
drop table t2;
> ok
create constant abc value 1;
> ok
call abc;
> 1
> -
> 1
> rows: 1
drop all objects;
> ok
call abc;
> exception
create table FOO(id integer primary key);
> ok
create table BAR(fooId integer);
> ok
alter table bar add foreign key (fooId) references foo (id);
> ok
truncate table bar;
> ok
truncate table foo;
> exception
drop table bar, foo;
> ok
CREATE TABLE TESTA(ID IDENTITY);
> ok
CREATE TABLE TESTB(ID IDENTITY);
> ok
explain SELECT TESTA.ID A, TESTB.ID B FROM TESTA, TESTB ORDER BY TESTA.ID, TESTB.ID;
> PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------
> SELECT TESTA.ID AS A, TESTB.ID AS B FROM PUBLIC.TESTA /* PUBLIC.TESTA_TABLE_SCAN */ INNER JOIN PUBLIC.TESTB /* PUBLIC.TESTB_TABLE_SCAN */ ORDER BY 1, 2
> rows (ordered): 1
DROP TABLE IF EXISTS TESTA, TESTB;
> ok
CREATE TABLE test (family_name VARCHAR_IGNORECASE(63) NOT NULL);
> ok
INSERT INTO test VALUES('Smith'), ('de Smith'), ('el Smith'), ('von Smith');
> update count: 4
SELECT * FROM test WHERE family_name IN ('de Smith', 'Smith');
> FAMILY_NAME
> -----------
> Smith
> de Smith
> rows: 2
SELECT * FROM test WHERE family_name BETWEEN 'D' AND 'T';
> FAMILY_NAME
> -----------
> Smith
> de Smith
> el Smith
> rows: 3
CREATE INDEX family_name ON test(family_name);
> ok
SELECT * FROM test WHERE family_name IN ('de Smith', 'Smith');
> FAMILY_NAME
> -----------
> Smith
> de Smith
> rows: 2
drop table test;
> ok
create memory table test(id int primary key, data clob);
> ok
insert into test values(1, 'abc' || space(20));
> update count: 1
script nopasswords nosettings blocksize 10;
> SCRIPT
> -------------------------------------------------------------------------------------------------------------------
> -- 1 = SELECT COUNT(*) FROM PUBLIC.TEST;
> ALTER TABLE PUBLIC.TEST ADD CONSTRAINT PUBLIC.CONSTRAINT_2 PRIMARY KEY(ID);
> CALL SYSTEM_COMBINE_BLOB(-1);
> CREATE ALIAS IF NOT EXISTS SYSTEM_COMBINE_BLOB FOR "org.h2.command.dml.ScriptCommand.combineBlob";
> CREATE ALIAS IF NOT EXISTS SYSTEM_COMBINE_CLOB FOR "org.h2.command.dml.ScriptCommand.combineClob";
> CREATE MEMORY TABLE PUBLIC.TEST( ID INT NOT NULL, DATA CLOB );
> CREATE TABLE IF NOT EXISTS SYSTEM_LOB_STREAM(ID INT, PART INT, CDATA VARCHAR, BDATA BINARY, PRIMARY KEY(ID, PART));
> CREATE USER IF NOT EXISTS SA PASSWORD '' ADMIN;
> DROP ALIAS IF EXISTS SYSTEM_COMBINE_BLOB;
> DROP ALIAS IF EXISTS SYSTEM_COMBINE_CLOB;
> DROP TABLE IF EXISTS SYSTEM_LOB_STREAM;
> INSERT INTO PUBLIC.TEST(ID, DATA) VALUES (1, SYSTEM_COMBINE_CLOB(0));
> INSERT INTO SYSTEM_LOB_STREAM VALUES(0, 0, 'abc ', NULL);
> INSERT INTO SYSTEM_LOB_STREAM VALUES(0, 1, ' ', NULL);
> INSERT INTO SYSTEM_LOB_STREAM VALUES(0, 2, ' ', NULL);
> rows: 15
drop table test;
> ok
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
> ok
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World');
> update count: 2
SELECT DISTINCT * FROM TEST ORDER BY ID;
> ID NAME
> -- -----
> 1 Hello
> 2 World
> rows (ordered): 2
DROP TABLE TEST;
> ok
create table Foo (A varchar(20), B integer);
> ok
insert into Foo (A, B) values ('abcd', 1), ('abcd', 2);
> update count: 2
select * from Foo where A like 'abc%' escape '\' AND B=1;
> A B
> ---- -
> abcd 1
> rows: 1
drop table Foo;
> ok
create memory table orders ( orderid varchar(10), name varchar(20), customer_id varchar(10), completed numeric(1) not null, verified numeric(1) );
> ok
select * from information_schema.columns where table_name = 'ORDERS';
> TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME TYPE_NAME NULLABLE IS_COMPUTED SELECTIVITY CHECK_CONSTRAINT REMARKS
> ------------- ------------ ---------- ----------- ---------------- -------------- ----------- --------- ------------------------ ---------------------- ----------------- ----------------------- ------------- ------------------ -------------- --------- -------- ----------- ----------- ---------------- -------
> SCRIPT PUBLIC ORDERS COMPLETED 4 null NO 3 1 1 1 10 0 Unicode OFF DECIMAL 0 FALSE 50
> SCRIPT PUBLIC ORDERS CUSTOMER_ID 3 null YES 12 10 10 10 10 0 Unicode OFF VARCHAR 1 FALSE 50
> SCRIPT PUBLIC ORDERS NAME 2 null YES 12 20 20 20 10 0 Unicode OFF VARCHAR 1 FALSE 50
> SCRIPT PUBLIC ORDERS ORDERID 1 null YES 12 10 10 10 10 0 Unicode OFF VARCHAR 1 FALSE 50
> SCRIPT PUBLIC ORDERS VERIFIED 5 null YES 3 1 1 1 10 0 Unicode OFF DECIMAL 1 FALSE 50
> rows: 5
drop table orders;
> ok
create table test(id int, d timestamp);
> ok
insert into test values(1, '2006-01-01 12:00:00.000');
> update count: 1
insert into test values(1, '1999-12-01 23:59:00.000');
> update count: 1
select * from test where d= '1999-12-01 23:59:00.000';
> ID D
> -- ---------------------
> 1 1999-12-01 23:59:00.0
> rows: 1
select * from test where d= timestamp '2006-01-01 12:00:00.000';
> ID D
> -- ---------------------
> 1 2006-01-01 12:00:00.0
> rows: 1
drop table test;
> ok
create table test(id int, b binary);
> ok
insert into test values(1, 'face');
> update count: 1
select * from test where b = 'FaCe';
> ID B
> -- ----
> 1 face
> rows: 1
drop table test;
> ok
create sequence main_seq;
> ok
create schema "TestSchema";
> ok
create sequence "TestSchema"."TestSeq";
> ok
create sequence "TestSchema"."ABC";
> ok
select currval('main_seq'), currval('TestSchema', 'TestSeq'), nextval('TestSchema', 'ABC');
> CURRVAL('main_seq') CURRVAL('TestSchema', 'TestSeq') NEXTVAL('TestSchema', 'ABC')
> ------------------- -------------------------------- ----------------------------
> 0 0 1
> rows: 1
set autocommit off;
> ok
set schema "TestSchema";
> ok
select nextval('abc'), currval('Abc'), nextval('TestSchema', 'ABC');
> NEXTVAL('abc') CURRVAL('Abc') NEXTVAL('TestSchema', 'ABC')
> -------------- -------------- ----------------------------
> 2 2 3
> rows: 1
set schema public;
> ok
drop schema "TestSchema";
> ok
drop sequence main_seq;
> ok
set autocommit on;
> ok
CREATE TABLE parent(id int PRIMARY KEY);
> ok
CREATE TABLE child(parentid int REFERENCES parent);
> ok
select * from INFORMATION_SCHEMA.CROSS_REFERENCES;
> PKTABLE_CATALOG PKTABLE_SCHEMA PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CATALOG FKTABLE_SCHEMA FKTABLE_NAME FKCOLUMN_NAME ORDINAL_POSITION UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
> --------------- -------------- ------------ ------------- --------------- -------------- ------------ ------------- ---------------- ----------- ----------- ------------ ------- -------------
> SCRIPT PUBLIC PARENT ID SCRIPT PUBLIC CHILD PARENTID 1 1 1 CONSTRAINT_3 null 7
> rows: 1
ALTER TABLE parent ADD COLUMN name varchar;
> ok
select * from INFORMATION_SCHEMA.CROSS_REFERENCES;
> PKTABLE_CATALOG PKTABLE_SCHEMA PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CATALOG FKTABLE_SCHEMA FKTABLE_NAME FKCOLUMN_NAME ORDINAL_POSITION UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
> --------------- -------------- ------------ ------------- --------------- -------------- ------------ ------------- ---------------- ----------- ----------- ------------ ------- -------------
> SCRIPT PUBLIC PARENT ID SCRIPT PUBLIC CHILD PARENTID 1 1 1 CONSTRAINT_3 null 7
> rows: 1
drop table parent, child;
> ok
create table test(id int);
> ok
create schema TEST_SCHEMA;
> ok
set autocommit false;
> ok
set schema TEST_SCHEMA;
> ok
create table test(id int, name varchar);
> ok
explain select * from test;
> PLAN
> ---------------------------------------------------------------------------------
> SELECT TEST.ID, TEST.NAME FROM TEST_SCHEMA.TEST /* TEST_SCHEMA.TEST_TABLE_SCAN */
> rows: 1
explain select * from public.test;
> PLAN
> ------------------------------------------------------------
> SELECT TEST.ID FROM PUBLIC.TEST /* PUBLIC.TEST_TABLE_SCAN */
> rows: 1
drop schema TEST_SCHEMA;
> ok
set autocommit true;
> ok
set schema public;
> ok
select * from test;
> ID
> --
> rows: 0
drop table test;
> ok
create table content(thread_id int, parent_id int);
> ok
alter table content add constraint content_parent_id check (parent_id = thread_id) or (parent_id is null) or ( parent_id in (select thread_id from content));
> ok
create index content_thread_id ON content(thread_id);
> ok
insert into content values(0, 0), (0, 0);
> update count: 2
insert into content values(0, 1);
> exception
insert into content values(1, 1), (2, 2);
> update count: 2
insert into content values(2, 1);
> update count: 1
insert into content values(2, 3);
> exception
drop table content;
> ok
select x/10 y from system_range(1, 100) group by x/10;
> Y
> --
> 0
> 1
> 10
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> rows: 11
select timestamp '2001-02-03T10:30:33';
> TIMESTAMP '2001-02-03 10:30:33.0'
> ---------------------------------
> 2001-02-03 10:30:33.0
> rows: 1
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
> ok
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World');
> update count: 2
select * from test where id in (select id from test);
> ID NAME
> -- -----
> 1 Hello
> 2 World
> rows: 2
select * from test where id in ((select id from test));
> ID NAME
> -- -----
> 1 Hello
> 2 World
> rows: 2
select * from test where id in (((select id from test)));
> ID NAME
> -- -----
> 1 Hello
> 2 World
> rows: 2
DROP TABLE TEST;
> ok
create table test(id int);
> ok
insert into test select x from system_range(1, 100);
> update count: 100
select id/1000 from test group by id/1000;
> ID / 1000
> ---------
> 0
> rows: 1
select id/(10*100) from test group by id/(10*100);
> ID / 1000
> ---------
> 0
> rows: 1
select id/1000 from test group by id/100;
> exception
drop table test;
> ok
select (x/10000) from system_range(10, 20) group by (x/10000);
> X / 10000
> ---------
> 0
> rows: 1
select sum(x), (x/10) from system_range(10, 100) group by (x/10);
> SUM(X) X / 10
> ------ ------
> 100 10
> 145 1
> 245 2
> 345 3
> 445 4
> 545 5
> 645 6
> 745 7
> 845 8
> 945 9
> rows: 10
CREATE FORCE VIEW ADDRESS_VIEW AS SELECT * FROM ADDRESS;
> ok
CREATE memory TABLE ADDRESS(ID INT);
> ok
alter view address_view recompile;
> ok
select * from ADDRESS_VIEW;
> ID
> --
> rows: 0
drop view address_view;
> ok
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -