Unit 10 Indexes and Views

Unit 10 Indexes and Views

1. Common data structures

1. Stack

Features: first in, last out, last in, first out

2. Queue

Features: first in, first out

3. Array

1. Fast query speed: data can be quickly located through address values ​​and indexes

2. Low deletion efficiency: after deleting data, each data must be moved forward

3. The addition efficiency is extremely low: after adding the position, each data is moved back and then the data is added.

4. Linked list

1. The data in the link is stored free, and each element node contains the element value and the address of the next element

2. The query speed is slow because each query must be queried sequentially through the head pointer

3. Addition and deletion are relatively efficient, because you only need to re-point the pointer to the newly added element and the location of other elements No need to move.

5. Binary tree

Binary tree, the full name is binary search tree. The stored data is based on the first piece of data. If it is smaller, it will be placed on the left, if it is greater, it will be placed on the right.

1. There can only be one root node, and each node supports up to two direct child nodes

2. Node degree: the number of children the node has. The degree of a node is not greater than 2. If the degree is 0, it is called a leaf node or terminal node.

3. The depth of a binary tree node: refers to the number of longest simple path edges from the root node to the node.

4. The height of a binary tree node: refers to the number of the longest simple path edges from the node to the leaf node.

Disadvantages:

Although binary trees can improve some efficiency, they still face problems when there are many nodes or the depth of the tree is very high. The search speed is slow, and it is easy to degenerate linked list (the data is stored in order when).

6. Balanced binary tree

Data structure online address: Data Structure Visualization (usfca.edu)

<sThe �� field cannot be indexed

5. Using index

Grammar format

create [unique] [clustered / nonclustered]
 index index_name
 on table_name(column_name1, column_name2, …)

unique: unique index

clustered: clustered index

nonclustered：non-clustered index

index_name: index name

-- Create a clustered index
 create clustered index idx_userinfo_Id on UserInfo(Id);

 -- Create a non-clustered index (nonclustered can be omitted)
 create nonclustered index idx_userinfo_Account on UserInfo(Account);

 -- Create a unique non-clustered index
 create unique nonclustered index idx_userinfo_pwd on UserInfo(Pwd);

Unique feature: the index field must be unique, but can have a value of NULL

View Index

exec sp_helpindex 'dbo.  UserInfo'

Rename index

-- Rename index
 -- exec sp_rename 'Table name.Old index name','New index name','index';

 exec sp_rename 'userinfo  .idx_userinfo_pwd',  'idx_userinfo_password','index  span>';

Delete index

drop index idx_userinfo_Id on UserInfo

Rebuild index

alter index index name on data table name rebuild

3. View

The role of view:

1. Improve security

2. Simplify the query process

What is a view

View is a database used to simplify the query process and improve database securityVirtual tableObject. The essence of a view is actually a bunch of encapsulated SQL.

How to use views

Create View

Grammar format:

create view view name

 as select  span> column_name from table_name [where Condition]
 --Create view

 create view v_studentscore
 as
 select a.*,b.Degree,c.Cno,Cname,d.* fromStudent a

 inner join Score b on a.Sno=b.Sno

 inner join Course c on b.Cno=c.Cno

 inner join Teacher d on c.Tno=d.Tno

 
 --Use view

 select * from v_studentscore where nickname='  Zhang Xu'
  

Modify view

Be sure to remember to save the code that creates the view, so as not to have to rewrite the code next time you modify the view. , especially the encrypted view code.

alter view v_studentscore
 --with encryption -- encryption
 as
 select a.*,b.Degree,c.Cno,Cname,d.* fromStudent a
 inner join Score b on a.Sno=b.Sno
 inner join Course c on b.Cno=c.Cno
 inner join Teacher d on c.Tno=d.Tno

Delete View

--Delete view
​

drop view v_studentscore

Video supporting link: SQL Server Advanced Programming – NetEase Cloud Classroom (163.com)

The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.style=”color: rgba(0, 0, 0, 1)”>Create view

create view v_studentscore
as
select a.*,b.Degree,c.Cno,Cname,d.* fromStudent a

inner join Score b on a.Sno=b.Sno

inner join Course c on b.Cno=c.Cno

inner join Teacher d on c.Tno=d.Tno

—Use view

select * from v_studentscore where nickname=‘ Zhang Xu‘

Modify view

Be sure to remember to save the code that creates the view, so as not to have to rewrite the code next time you modify the view. , especially the encrypted view code.

alter view v_studentscore
 --with encryption -- encryption
 as
 select a.*,b.Degree,c.Cno,Cname,d.* fromStudent a
 inner join Score b on a.Sno=b.Sno
 inner join Course c on b.Cno=c.Cno
 inner join Teacher d on c.Tno=d.Tno

Delete View

--Delete view
​

drop view v_studentscore

Video supporting link: SQL Server Advanced Programming – NetEase Cloud Classroom (163.com)

The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.