MySQL's index, view, constraints simple concept

Index
An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet.
An index exists to increase the speed of query execution.

To create an index:
CREATE INDEX [unique| Fulltext| SPATIAL] index_name [Index_type] on Tbname (Index_col_name,...)

Index_name the name of the index, custom.

Index_col_name
colname [(LENGTH)] [asc| DESC]
LENGTH the number of characters to compare from left to right when indexing. If you are building on a text or char type, you must define a length. By default, all lengths are not specified.
ASC Ascending sort
Desc Descending sort


Index_type
USING {bterr| HASH}
Usually use btree, not hash

Index_option

To delete an index:
DROP INDEX index_name on Tbname

To display the index of a table:
SHOW INDEXES from Tbname


Views (view)
The stored select statement, based on the query results of the base table. A view is also called a virtual table. It is generally not recommended to insert or update the contents of a view because the view is constrained by the field of the base table. It's not very useful in MySQL.

Materialized view (MySQL not supported):
Saves the results of the generated view. The advantage is that you do not have to query every time, the disadvantage is that the base table update must be new materialized.

Create a View
CREATE VIEW VIEWNAME [column_list] as Select_statment

To delete a view:
DROP VIEWNAME

To modify a view:
Drop the Create button first.


Displays the specific statement that created the object:
SHOW CREATE tbname| VIEW,,,


Constraints
Domain constraints: Data type constraints
FOREIGN KEY constraints: referential integrity constraints
PRIMARY KEY constraint: A primary key is a field that uniquely identifies the entity to which this field belongs, and is not allowed to be empty. This condition is called the candidate primary key. A table can have only one primary key. 2 entities are not allowed to appear with the same value on the primary key.
Uniqueness constraint: A field in each row does not allow the same value to appear. But it can be empty. More than one can appear in a table.
CHECK constraint: Custom constraint. MySQL is weak in functionality.



Instance:
1. Add a unique key to a table
Mysql>desc Testcourses2;
Mysql>show INDEXES from Testcourses2;
Mysql>alter TABLE testcourses2 ADD UNIQUE KEY (courname);
Mysql>show INDEXES from Testcourses2;

2. Create a FOREIGN key:
Mysql>alter TABLE stud ADD froeign KEY (CID) Refencts courses (CID);
Mysql>insert into Stud (name,courname) VALUES (' Zhaoliu ', 4);

3. Create an index:
Mysql>show INDEXES from Stud;
Mysql>create INDEX Name_on_stud on Stud (name) USING BTREE;
Mysql>show INDEXES from Stud;

4. Rebuild the index after the index is deleted, with a limit of 5 characters in the first, ascending order:
Mysql>drop INDEX NAME_ON_STDU on Stud;
Mysql>create INDEX Name_on_stud on Stud (name (5) ASC);

5. Create a view:
mysql> CREATE VIEW SCT SELECT * from sutdents;
Mysql> SHOW TABLES;
mysql> SHOW TABLES STATUS;

6. Display the command to create the view:
Mysql>show CREATE sct\g;

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1679325

MySQL's index, view, constraints simple concept