Method and introduction of table index definition in MySQL

Overview

An index is a table of correspondence between column values ​​and record rows created by the DBMS based on one or more columns in a table in a certain order, which facilitates DBA management.

• Indexes are stored in the form of files. The DBMS saves all the contents of a table in the same index file, which takes up disk space. If there are a large number of indexes, the maximum file size may be reached faster than the data file.
• While indexes increase query speed, they also decrease the speed of updating tables. When updating the data on the index column in the table, the index will be automatically updated to ensure that the index tree is completely consistent with the content in the table. Therefore, the more indexes there are, the longer the update time will be.

According to their usage, indexes are logically divided into three categories

1. Normal index (INDEX): The most basic index type, without any restrictions. Usually use the keyword INDEX or KEY
2. Unique index (UNIQUE): All values ​​in the index can only appear once and must be unique. The keyword UNIQUE is usually used.
3. PRIMARY KEY: A primary key is a unique index. When creating a primary key, you must specify the keyword PRIMARY KEY, and it cannot have a null value. It is usually specified when creating a table, and can also be added by modifying the table. Each table can only have one primary key.

Create Index

There are three ways to create indexes:

CREATE INDEX

CREATE [UNIQUE] INDEX index_name
NO tbl_name(index_col_name,...)

1. UNIQUE: Specifies to create a unique index. Multiple indexes can be created for a table, and each index has a unique name in the table.
2. tabl_name: database table name
3. index_col_name : Description of the index. The format is col_name[(length)][ASC|DESC]

The three grammatical elements of the index description

• col_name
• length
• ASC|DESC

mysql>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 a primary key for a new table while creating it
• {INDEX|KEY}[index_name](index_col_name,...) : Create an index for a table while creating the table.
• [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,...) : Used to create a unique index when creating a table
• [CONSTRATIN [symbol]] FOREIGN KEY[index_name] (index_col_name,...) : Create a foreign key while creating a table
• KEY : Synonym for the keyword INDEX
• CONSTRAINT: Defines a name for the primary key, UNIQUE key, and foreign key. When using CREATE TABLE to define column options, you can add a primary key by adding PRIMARY KEY directly after a column definition. This method cannot be used when the primary key consists of a multi-column index consisting of multiple columns.

mysql> USE mysql_test
Database changed
mysql> 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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.