MySQLServer → 6 views and Indexes

MySQLServer → 6 views, index [6] views, and index views: A view contains a dataset composed of one or more table columns, the records in this table are composed of the query results obtained after a query statement is executed. A view is a virtual table that contains a series of columns and row data with names. 2. view is the SQL statement stored in the database for query.

My SQLServer → 6 views, index [6] views, and index views: A view contains a dataset composed of one or more table columns, the records in this table are composed of the query results obtained after a query statement is executed. A view is a virtual table that contains a series of columns and row data with names. 2. view is the SQL statement stored in the database for query.

My SQLServer → 6 views and Indexes

[6] views and Indexes

View concept: A view contains a dataset composed of one or more table columns. records in this table are composed of the query results obtained after a query statement is executed. A view is a virtual table that contains a series of columns and row data with names.

2. view is an SQL statement stored in the database for two reasons: ①. Some data can be hidden for security reasons. ② Makes complex query statements easy to understand and use

3. Why View

① Restrict users to access only specific columns in the table.

② You can access the desired data without having to create a new table.

③ Omit the complex SELECT command and table connection to generate data, and directly use the view to obtain the relevant data.

4. How to Create a view

L views can only be created in the current database. A view can only reference a maximum of 1024 columns. The number of records in a view is limited only by the number of records in its base table.

L view names must follow the identifier rules and must be unique to each user. In addition, this name cannot be the same as any table name owned by this user.

L if the base table or view referenced by the view is deleted, the view cannot be used until a new base table and view are created.

L if a column in the view is a function, a mathematical expression. If the constant or column names from multiple tables are the same, the column name must be defined.

L you cannot create indexes on the image. You cannot create indexes by default. Reference the view in the trigger definition.

When querying data through views, the Hong Kong Server is rented. the SQL Server should check to ensure the existence of all database objects involved in the statement. The context of each database object statements is valid, in addition, data modification statements cannot violate database integrity rules.

Syntax:

Create view view_name [(column_list)]

AS

Select_statement (SELECT statement)

* Note:

· View _ name: specify a name for the newly created View.

· Column _ list: name of the Column in the current table. If this option is ignored, the column name in the current base table is automatically used.

· Select _ statement: defines the SELECT statements used to retrieve travel and columns from one or more tables.

Sentence

· The select statement in the view cannot contain the order by or INTO clause, and the temporary table cannot be referenced in the query.

5. advantages of using views

L centralized Viewpoint

L simplified operations

L Custom Data

L merge and split data

L Security

Index: an index is a separate, physical database structure. It is a list of values contained in a table in a database, indicating the storage location of each value in the table.

Index types include: clustered index, non-clustered index, unique index, composite index, attempt index, full-text index, U.S. server, XML Index

* Note:

A unique index can be a clustered index or a non-clustered index. The column where the uniqueness constraint is located allows null values, but the primary key constraint does not allow null values.

8. Preparations before creating an index

L it is best to create an index for an empty table. We recommend that you set an index when creating the table.

L if you want to create a non-clustered index, you 'd better first create a clustered index and create a non-clustered index.

9. How to create an index

Use the create index command in the T-SQL to CREATE an INDEX

Syntax:

Create [unique] [clustered | nonclustered] INDEX index_name

On table_name (column_name [,…])

[

With

Fillfacor = fillfactor

]

Meanings of parameters:

L Unique: used to specify whether the index to be created is a Unique constraint. Duplicate data cannot exist in the fields for creating a Unique index.

L Clustered | nonclustered: Specify clustered to create a Clustered index, and use nonclustered to create a non-clustered index. The two cannot be used at the same time. Each table can only create one Clustered index and the Hong Kong Space, however, each table can create 249 non-clustered indexes.

L Index_name: specify a name for the newly created index. The index name must comply with the naming rules.

L Table_name: name of the index table to be created

L Column_name: name of the column contained in the index

L Fillfactor: Specify the index page filling rate, indicating the percentage of the index page filling space. Specify a value between 1 and.

* Note: When deleting an index, you must specify the table name and index name for the Drop index statement.

Posted on

Powered:
Blog
Copyright©^ O ^ a6l