Simple tutorial on SQL statements in linux --- CREATE INDEX, simple tutorial --- create

Simple tutorial on SQL statements in linux --- CREATE INDEX, simple tutorial --- create

Index helps us quickly find the required information from the table. For example, suppose we want to find a message in a gardening book about how to plant green peppers. If this book does not have an index, we have to read it from the beginning until we find something about the direct green peppers. If this book has an index, we can first index the page where the green peppers are planted and then read it directly on that page. Obviously, using indexes is an effective and time-saving method.

The same is true for searching data from database tables. If a table does not have an index, the database system needs to read data from the entire table (this process is called 'table scan '). If an appropriate index exists, the database system can first find out where the required data is in the table, and then directly capture the data from those places. This is faster.

Therefore, creating an index on a table is conducive to system efficiency. An index can contain one or more columns. The index creation syntax is as follows:

Create index "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME );

Now suppose we have the following table,

CustomerTable

Column name	Data Type
First_Name	Char (50)
Last_Name	Char (50)
Address	Char (50)
City	Char (50)
Country	Char (25)
Birth_Date	Datetime

If we want to create an index on the value of Last_Name, we will run the following command,

Create index IDX_CUSTOMER_LAST_NAME
ON Customer (Last_Name );

To create an index in the field Last_Name, run the following command,

Create index IDX_CUSTOMER_LOCATION
ON Customer (City, Country );

There is no fixed way to name indexes. Generally, you can add a header before the name, such as "IDX _", to avoid confusion with other objects in the database. In addition, it is also good to include the table name and column name in the index name.

Please note that each database has its ownCREATE INDEXThe syntax varies with the database. Therefore, check the correct syntax in the Database User Manual before executing the command.

Linux has been tested as follows:

Reprinted, please note: Xiao Liu