我正在研究 SQLite3 索引。
这是一个表 COMPANY:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
=================================================== =====
SELECT * FROM COMPANY;
Results:
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0
6|Kim|22|South-Hall|45000.0
7|James|24|Houston|10000.0
让我们创建一个索引salary_index,
CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY);
它有什么作用以及如何使用它?
这次我在删除旧索引后创建了这样的索引:
CREATE INDEX IF NOT EXISTS salary_index on COMPANY (SALARY)
WHERE SALARY > 50000;
添加索引后,我做了:
SELECT * FROM COMPANY;
原以为我只会看到工资高于50000的人,但我看到了低于50000的人。
我也尝试这样做:
SELECT * FROM COMPANY INDEXED BY salary_index;
然后我得到错误:没有查询解决方案
显然我必须这样做:
SELECT * FROM COMPANY INDEXED BY salary_index WHERE SALARY > 50000;
其中条件必须与索引中的条件相同。
那么……我该如何使用索引?