Primary Key, foreign key, view, Index

The primary key task is to help MySQL determine the location of a specific data record in the data table as quickly as possible. The primary key must meet the following two conditions:

1. The primary key must be unique. The primary key fields in any two data records cannot be the same.

2. The primary key should be compact. Because the primary key must have an index, the more compact the primary key, the more efficient the management of the primary index, so the primary key is preferably an integer type. In addition, primary keys are generally used as foreign keys for another table. The Foreign keys are compact and work efficiency increases.

Create   Table Publishers
(Publid Int   Not   Null Auto_increment,
Other columns,
  Primary   Key (Publid ))

 

A foreign key task references a record of another data table in a table.

As long as there is a change in the database, the so-called "foreign key constraints" (also called consistency constraints) will go back and check whether there is any cross-reference relationship between data tables affected. Based on the specific declaration of Foreign keys, there are two final results of the data record deletion operation: 1. This operation is not allowed; the second is to delete all data records related to other data tables. Which option you use depends on which option you use.

The following SQLCodeDemonstrate how to add Consistency Principles (foreign key constraints) to a foreign key field when declaring ):

Create   Table Titles
(Column1, column2 ,,
Publid Int ,
Foreign   Key (Publid) References Publishers (publisherid)
)

The above command explicitly defines the titles. publid field as a foreign key pointing to the primary key publishers. publisherid. On this basis, you can also use several options, such as on Delete, restrict, and on Delete cascade, to respond to the database system when its reference consistency is broken.

The following is the basic syntax for setting foreign key constraints for the foreign key field table1.column1:

Foreign   Key   [ Name ] (Column1) References Table2 (column2)
  [ On Delete {cascade | set null | no action | restrict} ]
  [ On update {cascade | set null | no action | restrict} ]

The foreign key constraint indicates what table 1 should do when table 2 changes.

Prerequisites for setting foreign key constraints

① Both table1.column1 and table2.column2 must have at least one common index. If not, use the alter table command. Table2.column2 is usually the primary key, but it is not required.

② The data columns table1.column1 and table2.column2 must be matched and can be directly compared without data type replacement. It is best to use either Int or bigint.

③ If the on delete/update set null option is selected, the value of table1.column1 must be null.

④ Foreign key constraints must be satisfied from the very beginning.

 

Index

An index is a special file that contains reference pointers to all records in a data table. Database indexes and book indexes have the same functions.

1. Common Index

The only task of a common index (index defined by the key or index keyword) is to speed up data access. Therefore, it should be the most frequently used query conditions (where column = ...) Or create an index for the data column in the order by column. If possible, you should select the most neat and compact data column to create an index.

2. Unique Index

Normal indexes allow indexed columns to contain duplicate values. If you can determine that a data column only contains different values, you can use the keyword unique to define it as a unique index when creating an index for it. There are two ways to do this: ① management is more efficient; ② when a new record is inserted into a data table, the system automatically checks whether the field of the new record already exists. If yes, the system rejects the insertion. In many cases, the unique index is not used to increase the access speed, but also to avoid data duplication.

3. Primary Index

The primary index is the index created for the primary key field. Use the keyword primary. Because the primary key value must be unique, the index primary index is also a unique index.
Note the difference between unique and primary keys. They all create a unique index. A table contains only one primary key constraint column. However, it may contain many unique constraints in other columns.

4. Composite Index

Indexes can overwrite multiple data columns, just like index (Columna, columnb ). Index (a, B, c) can be used as an index of A or (a, B), but cannot be used as an index of B, C, (B, c. Index compliance mainly aims to improve index efficiency. composite indexes require less overhead during database operations and can replace multiple single indexes.
When the number of rows in a table is much greater than the number of index keys, this method can significantly speed up the table query.

Another type of index: clustered index and non-clustered index.

In a clustered index, the leaf page of the index tree contains actual data: The index order of the records is the same as that of the physical order. In a non-clustered index, the leaf-level page points to the records in the table: the physical sequence of records is not necessarily related to the logical sequence.

The cluster index is very similar to a directory table. The order of the Directory table is the same as that of the actual page number. Non-clustered indexes are more like the standard index table of books. The order of the index table is usually different from that of the actual page number. A book may have multiple indexes. For example, it may have both the subject index and the author index. Similarly, a table can have multiple non-clustered indexes.

primary key and clustered index comparison

	Primary Key	Clustered Index
Purpose	Forced table entity integrity	Sorts data rows for easy Query
Number of tables in a table	A table has at most one primary key.	A table can have at most one clustered index.
Can multiple fields be defined?	One primary key can be defined by multiple fields	One index can be defined by multiple fields.
Whether NULL data rows are allowed	If the data in the data column to be created is null, a primary key cannot be created. The specified primary key constraint column during table creation is implicitly converted to not null.	Columns that do not limit the creation of clustered indexes must not be null. That is, the column data can be null. See the last comparison.
Must the data be unique?	Data must be unique	Data can be unique or not unique. See the unique settings you have defined for this index. (In this case, we need to look at the following comparison. Although your data columns may not be unique, the system will generate a unique column that you cannot see for you)
Created Logic	When the database creates a primary key, it automatically creates a unique index. If no clustered index exists in the table and the non-clustered index is not specified when the primary key is created, a unique clustered index is created when the primary key is created.	If the unique attribute is not used to create a clustered index, the database engine automatically adds a four-byte uniqueifier column to the table. If necessary, the database engine automatically adds a uniqueifier value to the row to make each key unique. This column and column value are used internally and cannot be viewed or accessed by users.

Index defects:

① Although they can accelerate data retrieval operations, this slows down data modification operations. Each time a data record is modified, the index must be refreshed once. If there are many records that need to be modified, you can use the delay_key_write option to temporarily disable the update, and update the index after all the records are modified or inserted.

② The index will occupy a considerable amount of space on the hard disk. Therefore, you should create an index for the data columns that are most frequently queried and sorted. Note: If a data column contains many duplicate content, creating an index for it does not make much sense.

 

View

A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced.

For the referenced basic table, the view function is similar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. There are no restrictions on query through views, and there are few restrictions on data modification through them.

A view is an SQL statement stored in a database. It is mainly for two reasons: security reasons. A view can hide some data, such as the Social Insurance Fund table, you can use the view to display only the name and address, but not the social insurance number and wage number. Another reason is that complex queries are easy to understand and use.

Therefore, a view is not a basic table that actually exists, but a virtual table. The data of the view is not actually stored in the database in the view structure, but in the table referenced by the view.

Create View

Create   [ Or replace ]   [ Algorithm = {undefined | merge | temptable} ]
View   [ Db_name. ] View_name [ (Column_list) ]
As Select_statement
[ With [cascaded | Local ]   Check   Option ]

 

Code
In this example, create a product table and a purchase record table (purchase), and then query the details of the purchase through the view purchase_detail.

Create   Table Product

(

Product_id Int   Not   Null ,

Name Varchar ( 50 ) Not   Null ,

Price Double   Not   Null

);

Insert   Into Product Values ( 1 , ' Apple ' , 5.5 );

Create   Table Purchase

(

ID Int   Not   Null ,

Product_id Int   Not   Null ,

Qty Int   Not   Null   Default   0 ,

Gen_time Datetime   Not   Null

);

Insert   Into Purchase Values ( 1 , 1 , 10 , Now ());

Create   View Purchase_detail As   Select Product. Name As Name, product. Price As Price, purchase. Qty As Qty, product. Price * Purchase. Qty As Total_value From Product, purchase Where Product. product_id = Purchase. product_id;

After the creation is successful, enter: Select   *   From Purchase_detail;

The running effect is as follows:

+ -- ----- + ------- + ----- + ----------- +

| Name | Price | Qty | Total_value |

+ -- ----- + ------- + ----- + ----------- +

| Apple | 5.5   |   10   | 55   |

+ -- ----- + ------- + ----- + ----------- +

1 Row In   Set ( 0.01 Sec)