Shulou(Shulou.com)06/01 Report--

Overview

An index is a table of correspondence between column values and record rows established by DBMS according to one or more columns in the table in a certain order, which is convenient for DBA management.

Indexes are stored in the form of files. DBMS saves everything of a table in the same index file, which takes up disk space. If there are a large number of indexes, it may reach the maximum file size faster than data files. While the index improves the query speed, it slows down the speed of updating the table. When you update data on an index column in a table, the index is automatically updated to ensure that the index tree is exactly the same as the contents of the table, so the more indexes, the longer the update.

Logically, indexes can be divided into three categories according to their purpose.

General index (INDEX): the most basic type of index with no restrictions. The keyword INDEX or KEY unique index (UNIQUE) is usually used: all values in the index can only appear once, and the keyword UNIQUE primary key (PRIMARY KEY) must be unique: the primary key is a unique index. When creating a primary key, the keyword PRIMARY KEY must be specified, and there can be no null values. It is generally specified when creating a table, or it can be added by modifying the table. Each table can only have one primary key.

Create an index

There are three ways to create an index

CREATE INDEX

CREATE [UNIQUE] INDEX index_nameNO tbl_name (index_col_name,...) UNIQUE: specifies that a unique index can be created for a table, and each index has a unique name in the table tabl_name: table name of the database index_col_name: description of the index. Format: col_name [(length)] [ASC | DESC]

Three grammatical elements of the description of the index

Col_namelenghtASC | DESCmysql > CREATE INDEX index_customers- > NO mysql_test.customers (cust_name (3) ASC) Query OK, 0 rows affected (0.20 sec) Records:0 Duplicates:0 Warning:0

CREATE TABLE

[CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...): create the table primary key while creating a new table {INDEX | KEY} [index_name] (index_col_name,...): create the table index at the same time [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] (index_col_name,...): used to create a unique index [CONSTRATIN [symbol] FOREIGN key [index _ name] (index_col_name) ): create the foreign key KEY: synonym for the keyword INDEX CONSTRAINT: define a name for the primary key, unique key, and foreign key while creating the table When using CREATE TABLE to define column options, you can add a primary key by adding PRIMARY KEY directly after a column definition, but this method cannot be used when the primary key is a multi-column index consisting of multiple columns. Mysql > USE mysql_testDatabase changedmysql > CREATE TABLE seller- > (- > seller_id int NOT NULL AUTO_INCREMENT- > seller_name char (50) NOT NLULL,- > seller_address char (50) null,- > product_type int (5) NULL- > sales int NULL- > PRIMARY KEY (seller_id,product_type)-> INDEX index_seller (salse)->) Query OK, 0 rows affected (0.20 sec)

ALTER TABLE

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.