mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
120 lines
3.0 KiB
Plaintext
120 lines
3.0 KiB
Plaintext
|
|
CREATE TABLE t1(a TEXT, b, c);
|
|
CREATE INDEX i1 ON t1(b, c) WHERE a='abc';
|
|
INSERT INTO t1 VALUES('abc', 1, 2);
|
|
|
|
|
|
SELECT * FROM t1 WHERE a='abc'
|
|
|
|
|
|
CREATE INDEX i2 ON t1(b, c) WHERE a=5;
|
|
INSERT INTO t1 VALUES(5, 4, 3);
|
|
|
|
SELECT a, typeof(a), b, c FROM t1 WHERE a=5;
|
|
|
|
|
|
CREATE TABLE t2(x);
|
|
INSERT INTO t2 VALUES('v');
|
|
|
|
|
|
SELECT x, a, b, c FROM t2 LEFT JOIN t1 ON (a=5 AND b=x)
|
|
|
|
|
|
SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)
|
|
|
|
|
|
CREATE TABLE x1(a TEXT, b, c);
|
|
INSERT INTO x1 VALUES('2', 'two', 'ii');
|
|
INSERT INTO x1 VALUES('2.0', 'twopointoh', 'ii.0');
|
|
|
|
CREATE TABLE x2(a NUMERIC, b, c);
|
|
INSERT INTO x2 VALUES('2', 'two', 'ii');
|
|
INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0');
|
|
|
|
CREATE TABLE x3(a REAL, b, c);
|
|
INSERT INTO x3 VALUES('2', 'two', 'ii');
|
|
INSERT INTO x3 VALUES('2.0', 'twopointoh', 'ii.0');
|
|
|
|
|
|
CREATE TABLE x1(a TEXT, d PRIMARY KEY, b, c) WITHOUT ROWID;
|
|
INSERT INTO x1 VALUES('2', 1, 'two', 'ii');
|
|
INSERT INTO x1 VALUES('2.0', 2, 'twopointoh', 'ii.0');
|
|
|
|
CREATE TABLE x2(a NUMERIC, b, c, d PRIMARY KEY) WITHOUT ROWID;
|
|
INSERT INTO x2 VALUES('2', 'two', 'ii', 1);
|
|
INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0', 2);
|
|
|
|
CREATE TABLE x3(d PRIMARY KEY, a REAL, b, c) WITHOUT ROWID;
|
|
INSERT INTO x3 VALUES(34, '2', 'two', 'ii');
|
|
INSERT INTO x3 VALUES(35, '2.0', 'twopointoh', 'ii.0');
|
|
|
|
|
|
CREATE TABLE t2(a INTEGER, b TEXT);
|
|
INSERT INTO t2 VALUES(1, 'two');
|
|
INSERT INTO t2 VALUES(2, 'two');
|
|
INSERT INTO t2 VALUES(3, 'two');
|
|
INSERT INTO t2 VALUES(1, 'three');
|
|
INSERT INTO t2 VALUES(2, 'three');
|
|
INSERT INTO t2 VALUES(3, 'three');
|
|
|
|
CREATE INDEX t2a_two ON t2(a) WHERE b='two';
|
|
|
|
|
|
SELECT sum(a), b FROM t2 WHERE b='two'
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMQRY KEY, b, c);
|
|
|
|
|
|
CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE xyz;
|
|
|
|
|
|
SELECT * FROM t1
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
|
|
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER, z INTEGER);
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
|
INSERT INTO t1 VALUES(2, 1, 2);
|
|
INSERT INTO t2 VALUES(1, 5, 1);
|
|
INSERT INTO t2 VALUES(2, 5, 2);
|
|
|
|
CREATE INDEX t2z ON t2(z) WHERE y=5;
|
|
|
|
|
|
ANALYZE;
|
|
UPDATE sqlite_stat1 SET stat = '50 1' WHERE idx='t2z';
|
|
UPDATE sqlite_stat1 SET stat = '50' WHERE tbl='t2' AND idx IS NULL;
|
|
UPDATE sqlite_stat1 SET stat = '5000' WHERE tbl='t1' AND idx IS NULL;
|
|
ANALYZE sqlite_schema;
|
|
|
|
|
|
SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
|
|
|
|
|
|
CREATE INDEX t2yz ON t2(y, z) WHERE y=5;
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
|
|
|
|
|
|
CREATE TABLE t1(i INTEGER PRIMARY KEY, b TEXT, c TEXT);
|
|
CREATE INDEX i1 ON t1(c) WHERE b='abc' AND i=5;
|
|
INSERT INTO t1 VALUES(5, 'abc', 'xyz');
|
|
SELECT * FROM t1 INDEXED BY i1 WHERE b='abc' AND i=5 ORDER BY c;
|
|
|
|
|
|
CREATE TABLE t1(a, b, c);
|
|
CREATE INDEX ex2 ON t1(a, 4);
|
|
CREATE INDEX ex1 ON t1(a) WHERE 4=b;
|
|
INSERT INTO t1 VALUES(1, 4, 1);
|
|
INSERT INTO t1 VALUES(1, 5, 1);
|
|
INSERT INTO t1 VALUES(2, 4, 2);
|
|
|
|
|
|
SELECT * FROM t1 WHERE b=4;
|
|
|