MySQL drill-down view and index

Copyright NOTICE: Welcome to Exchange!

Directory (?) [+]

Note: Job hunting season, consolidate under MySQL knowledge!

1. View

    A view is also called a virtual table . As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced. The view has the following functions : 1, security, permission control. Some data sheets have important information. Some fields are confidential and cannot be viewed directly by the user. You can then create a view that retains only a subset of the fields in this view. This allows the user to query the fields they need and not see the fields that are confidential. 2, performance, fast. Data from relational databases are often stored in tables, using foreign keys to establish relationships between these tables. At this point, the database query typically uses a connection (join). This is not only troublesome, but also relatively low efficiency. If you create a view that combines related tables and fields, you can avoid using join to query data. 3, flexible, extraction of the imminent waste of the table, generate useful value. If there is an old table in the system, this table will be discarded due to the design problem. However, many applications are based on this table and are not easy to modify. You can then create a view in which the data in the view maps directly to the newly created table. In this way, you can make a lot less changes, and also achieve the purpose of upgrading the data table. For learning about views, I recommend you refer to the MySQL manual. Find some examples on the internet and get started soon.

2. Classification

Views can be divided into three categories in SQL

1 Normal Views (Regular view)

SQL templates

[SQL]View PlainCopy  

2. CREATE VIEW [schema_name.] view_name [(column [,... n])]
4. [With <view_attribute> [,... N]]
6. As Select_statement
8. [ with CHECK OPTION] [;]
10. <view_attribute>:: =
12. {
14. [Encryption]
16. [SCHEMABINDING]
18. [View_metadata]}

Explain:

parameter is still relatively small, now explain the above parameters:

encryption: The view is encrypted , and if you choose this option, you cannot modify it. You need to save the script when creating the view , or you can no longer modify the

SCHEMABINDING: defines the binding to the table to which the underlying reference is referenced. With this option selected, the table referenced by the view cannot alter the schema arbitrarily (such as the data type of the column), and if the underlying table schema needs to be changed, drop or alter the view that is bound on the underlying table.

View_metadata: This is a very interesting option. As indicated by the name of this option, if not selected, the METADATA returned to the client is the METADATA of the table referenced by the VIEW, if this option is selected, The metadata of the view is returned. In plain explanation, View_metadata can make the view look like a table. The definition of each column of the view tells the client directly, not the definition of the underlying table column referenced.

With CHECK option: This option is used to update data to make restrictions

Restriction conditions

• In view, the ORDER BY clause cannot be used unless there is a top keyword
• View name must be unique in each schema
• View do not nest nesting as far as possible
• Compute,compute by,into keyword is not allowed in view
• View cannot be built on a temporary table
• View cannot query a full-text index

Instance

Build a View

[SQL]View PlainCopy 

1. CREATE VIEW v_test
2. As
3. SELECT TOP Ten * from table1

Query view

[SQL]View PlainCopy 

1. SELECT * from v_test

2 indexed view (Indexed view)----automatically synchronized (good and bad)

An indexed view can be thought of as a table-equivalent object! , is real in the physical data.

Strict requirements: (partial)

• The base table involved in the indexed view must be ANSI_NULLS set to ON
• Indexed views can reference only basic tables

[SQL]View PlainCopy 

2. CREATE VIEW V_test_index
4. With SCHEMABINDING
6. As
8. SELECT Name,id
10. From CUSTOMER join name="PAUL"
12. ADN id>5
14. GO
16. --Index on the view
18. CREATE UNIQUE CLUSTERED index index
20. On V_test_index

Bottom-level direct clustered index scan----through hash matching, index scanning, good performance

But MySQL didn't

Note: Operations on indexes are similar to CML can use Alter,update,delete

Appendix:

MySQL Index

1. What is an index

An index is a separate database structure stored on disk that contains reference pointers to all records in the database table.

There are two types of storage for indexes in MySQL: BTREE (tree) and hash (hash), which are related to the storage engine of the table. The MyISAM and InnoDB storage engines only support Btree Index。

2. Benefits of indexing

Proper use of indexes can improve database query speed!

3. Example:

Create an index when creating a table

Grammar: [SQL]View PlainCopy  

1. CREATE table table name [column name data type]
2. [ UNIQUE |   FULLTEXT] [ INDEX |    KEY]   [Index name]  (column name [length]) [ ASC | DESC]

Description: UNIQUE and fulltext are optional parameters, respectively, indicating Unique index, full-text index; index is synonymous with key, and the function is the same, which is used to specify the index;

(1), normal index: Normal index is the basic index type of MySQL, allows to insert duplicate values and Null values in columns that define the index

Cases:
[SQL]View PlainCopy 

2. CREATE TABLE Book
4. (
6. BookID INT not NULL,
8. BookName VARCHAR (+) not NULL,
10. Authors VARCHAR (+) not NULL,
12. Info VARCHAR (+) NULL,
14. Year_publication Year is not NULL,
16. INDEX (year_publication)
18. );

(2), unique index (unique): The value of a unique indexed column must be unique , but a null value is allowed . A primary key index is a special unique index that does not allow null values.

Cases:

[SQL]View PlainCopy 

1. CREATE TABLE Book
2. (
3. ID INT not NULL,
4. name CHAR (a) is not NULL,
5. UNIQUE INDEX uniqueidx (ID)
6. );

(3), Federated Index: A composite index creates an index on multiple columns . When querying, the index is only used when the leftmost field of these fields is used in the query criteria (which columns are specified when the composite index is created).

[SQL]View PlainCopy 

2. CREATE TABLE Student
4. (
6. ID INT not NULL,
8. name CHAR (a) is not NULL,
10. The Age INT is not NULL,
12. Info VARCHAR (+),
16. INDEX multiidx (ID,name,age)
18. );

(4), full-text index: MySQL only the MyISAM storage engine supports fulltext indexes, and the class type is char, text, VARCHAR. And you need to specify the table's storage engine as MyISAM.

Cases:

[SQL]View PlainCopy  

2. CREATE TABLE T4
4. (
6. ID INT not NULL,
8. name CHAR (a) is not NULL,
10. The Age INT is not NULL,
12. Info VARCHAR (+),
14. Fulltext INDEX Fullindexname (info)
16. ) ENGINE = MyISAM;

To create an index on a table that already exists:

Grammar:

[SQL]View PlainCopy 

1. ALTER TABLE table_name ADD [ UNIQUE |   FULLTEXT] [ INDEX | KEY]
2. [Inex_name]  (col_name [length],...) [ASC | DESC]

[SQL]View PlainCopy  

2. (1), General index:ALTER TABLE book ADD Index IndexName (bookname (30));
4. (2), unique index:ALTER TABLE book ADD unique index UNIQUEIDX (bookid);
6. (3), combined index:ALTER TABLE book ADD Index Bkandinfoidx (authors (), info (50));
8. (4), full-text index:ALTER table T6 ADD fulltext index INFIIDX (info) (if the storage engine for this table is MyISAM)

To create an index using creation index:

Syntax:

[SQL]View PlainCopy  

2. CREATE [ UNIQUE |   FULLTEXT] [ INDEX |  KEY] INDEX index_name
4. On table_name (col_name[length], ...) [ASC | DESC]
6. Example: An index named BKNAMEIDX is established on the BookName field of the table book.
8. CREATE INDEX bknameidx on book (BookName);
10. Example: Create a unique index on the BookID field of the Book table.
12. CREATE UNIQUE INDEX uniqueidx on book (BOOKID);

4. Delete index: [Java]View PlainCopy 

1. (1), ALTER TABLE table_name DROP INDEX index_name;
2. (2), DROP INDEX index_name on table_name;

MySQL drill-down view and index