无涯教程-Derby - 索引(Index) 表中的索引不过是指向其数据的指针，这些用于加快从表中检索数据的速度，如果增

表中的索引不过是指向其数据的指针，这些用于加快从表中检索数据的速度，如果增加索引将对写入与更新速度产生影响所以如果写多则尽少使用索引。

创建索引

CREATE INDEX语句用于在Derby数据库的表中创建新索引。

以下是CREATE INDEX语句的语法-

CTREATE INDEX index_name on table_name (column_name);

假设无涯教程在Apache Derby中创建了一个名为Employees的表，如下所示。

CREATE TABLE Emp ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   Phone_Number BIGINT
);

以下SQL语句在表Employees中名为Salary的列上创建索引。

ij> CREATE INDEX example_index on Emp (Salary);
0 rows inserted/updated/deleted

创建唯一索引

在Apache Derby中，UNIQUE索引用于数据集成，在表的列上创建UNIQUE索引后，该索引将不允许重复值。

以下是创建唯一索引的语法。

CREATE UNIQUE INDEX index_name on table_name (column_name);

下面的示例在表Employee的列ID上创建UNIQUE索引。

ij> CREATE UNIQUE INDEX unique_index on Emp (Phone_Number);
0 rows inserted/updated/deleted

在列上创建唯一索引后，就无法在另一行中为该列输入相同的值。简而言之，具有UNIQE索引的列将不允许重复值。

在Emp表中插入一行，如下所示

ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES (Amit,
45000, Hyderabad, 9848022338);
1 row inserted/updated/deleted

由于无涯教程在Phone_No列上创建了唯一索引，因此，如果您要输入与上一条记录相同的值，则会显示错误。

ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES (Sumit,
35000, Chennai, 9848022338);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
UNIQUE_INDEX defined on EMP.

创建复合索引

您可以在两行上创建一个索引，这称为复合索引。

以下是复合索引的语法。

CREATE INDEX index_name on table_name (column_name1, column_name2);

以下索引在"Name"和"Location"列上创建一个复合索引。

ij> CREATE INDEX composite_index on Emp (Name, Location);
0 rows inserted/updated/deleted

显示索引

SHOW INDEXES查询显示表上的索引列表。

以下是SHOW INDEXES语句的语法-

SHOW INDEXES FROM table_name;

在下面的示例中，无涯教程在雇员表上显示索引。

ij> SHOW INDEXES FROM Emp;

这将产生以下输出。

ij> SHOW INDEXES FROM Emp;
TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
EMP |PHONE_NUMBER |false |3 |A |NULL |NULL
EMP |NAME |true |3 |A |NULL |NULL
EMP |LOCATION |true |3 |A |NULL |NULL
EMP |SALARY |true |3 |A |NULL |NULL
4 rows selected

删除索引

Drop Index语句删除/删除给定索引在列上。

以下是DROP INDEX语句的语法。

DROP INDEX index_name;

下面的示例删除上面创建的名为Composite_index和unique_index的索引。

ij> DROP INDEX composite_index;
0 rows inserted/updated/deleted
ij>Drop INDEX unique_index;
0 rows inserted/updated/deleted

现在，如果您验证了索引列表，则由于无涯教程已删除了其余的索引，因此您可以在一列上看到索引。

ij> SHOW INDEXES FROM Emp;
TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
EMP |SALARY |true |3 |A |NULL |NULL
1 row selected

JDBC示例

以下JDBC程序演示了如何在表中的列上创建放置索引。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class IndexesExample {
   public static void main(String args[]) throws Exception {
  </span><span class="com">//注册驱动</span><span class="pln">
  </span><span class="typ">Class</span><span class="pun">.</span><span class="pln">forName</span><span class="pun">(</span><span class="str">"org.apache.derby.jdbc.EmbeddedDriver"</span><span class="pun">);</span><span class="pln">
  </span><span class="com">//创建连接</span><span class="pln">
  </span><span class="typ">String</span><span class="pln"> URL</span><span class="pun">=</span><span class="str">"jdbc:derby:MYDATABASE;create=true"</span><span class="pun">;</span><span class="pln">
  </span><span class="typ">Connection</span><span class="pln"> conn</span><span class="pun">=</span><span class="typ">DriverManager</span><span class="pun">.</span><span class="pln">getConnection</span><span class="pun">(</span><span class="pln">URL</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//获取Statement对象</span><span class="pln">
  </span><span class="typ">Statement</span><span class="pln"> stmt</span><span class="pun">=</span><span class="pln">conn</span><span class="pun">.</span><span class="pln">createStatement</span><span class="pun">();</span><span class="pln">

  </span><span class="com">//创建表结构</span><span class="pln">
  </span><span class="typ">String</span><span class="pln"> createQuery</span><span class="pun">=</span><span class="str">"CREATE TABLE Emp( "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Name VARCHAR(255), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Salary INT NOT NULL, "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Location VARCHAR(255), "</span><span class="pln">
     </span><span class="pun">+</span><span class="pln"> </span><span class="str">"Phone_Number BIGINT )"</span><span class="pun">;</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="pln">createQuery</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Table created"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//创建索引</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"CREATE INDEX example_index on Emp (Salary)"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Index example_index inserted"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//创建唯一索引</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"CREATE UNIQUE INDEX unique_index on Emp (Phone_Number)"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Index unique_index inserted"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//创建联合索引</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"CREATE INDEX composite_index on Emp (Name, Location)"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Index composite_index inserted"</span><span class="pun">);</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">" "</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//查看所有索引</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Listing all the columns with indexes"</span><span class="pun">);</span><span class="pln">

  </span><span class="com">//删除索引</span><span class="pln">
  </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="str">"Dropping indexes unique_index and, composite_index "</span><span class="pun">);</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"Drop INDEX unique_index"</span><span class="pun">);</span><span class="pln">
  stmt</span><span class="pun">.</span><span class="pln">execute</span><span class="pun">(</span><span class="str">"DROP INDEX composite_index"</span><span class="pun">);</span><span class="pln">

}
}

执行时，将产生以下输出

Table created
Index example_index inserted
Index unique_index inserted
Index composite_index inserted
Listing all the columns with indexes
Dropping indexes unique_index and, composite_index

无涯教程-Derby - 索引(Index)