表中的索引只是指向其数据的指针.这些用于加速从表中检索数据.
如果我们使用索引，INSERT和UPDATE语句将在较慢的阶段执行.而SELECT和WHERE在较短的时间内执行.
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索引后，它不允许重复值.
以下是创建a的语法唯一索引.
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'.您可以在两行上创建单个索引，它称为复合索引./p>
以下是复合索引的语法.
CREATE INDEX index_name on table_name (column_name1, column_name2);
以下索引在名称和位置列上创建复合索引.
ij> CREATE INDEX composite_index on Emp (Name, Location); 0 rows inserted/updated/deleted
SHOW INDEXES查询显示列表表上的索引.
以下是SHOW INDEXES语句的语法 :
SHOW INDEXES FROM table_name;
以下示例，我在表Employees上显示索引.
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程序演示了如何在表中的列上创建drop索引.
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 {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:MYDATABASE;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating the Emp table
String createQuery = "CREATE TABLE Emp( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "Phone_Number BIGINT )";
stmt.execute(createQuery);
System.out.println("Table created");
System.out.println(" ");
//Creating an Index on the column Salary
stmt.execute("CREATE INDEX example_index on Emp (Salary)");
System.out.println("Index example_index inserted");
System.out.println(" ");
//Creating an Unique index on the column Phone_Number
stmt.execute("CREATE UNIQUE INDEX unique_index on Emp (Phone_Number)");
System.out.println("Index unique_index inserted");
System.out.println(" ");
//Creating a Composite Index on the columns Name and Location
stmt.execute("CREATE INDEX composite_index on Emp (Name, Location)");
System.out.println("Index composite_index inserted");
System.out.println(" ");
//listing all the indexes
System.out.println("Listing all the columns with indexes");
//Dropping indexes
System.out.println("Dropping indexes unique_index and, composite_index ");
stmt.execute("Drop INDEX unique_index");
stmt.execute("DROP INDEX composite_index");
}
}执行时，会产生以下结果
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
