1, View (keyword: view)

1. View creation

-- Basic format for creating views: create view <View name> as <Query statement>; 
-- The essence of view is to use<View name>replace<Query statement>，This is equivalent to aliasing the query statement.
select * from class; create view v_c as select * from class; 
-- Created a view named v_c，Used in place of query statements"select * from class;"

2. Use of view

-- Basic format used by the view: select <Attribute name> from <View name>;
select * from v_c; 
-- Query view v_c，Its query effect is similar to the query statement it replaces"select * from class;"Consistent effect

Views can be used to replace complex query statements, such as the following multi table join queries:

select S.s_name Student name ,C.c_name Class name,T.t_name Teacher name 
	from student S left join class C on S.s_cid = C.c_id 
	left join teacher T on C.c_tid = T.t_id;

Views can be used instead of:

create view v_sctname as select S.s_name Student name ,C.c_name Class name,T.t_name Teacher name
	from student S left join class C on S.s_cid = C.c_id 
	left join teacher T on C.c_tid = T.t_id;

Query this view:

select * from v_sctname;

At this point, the simple view query statement "select * from V"_ sctname;" The effect is consistent with the above multi table join query statement, but the format is much simpler.

You can also query only some attributes in the view, such as

select Student name,Class name from v_sctname;

The view also supports conditional queries, such as

select Student name,Class name from v_sctname limit 3; select Student name,Class name from v_sctname where Teacher name = 'petty thief';

3. View modification

Modify the basic format of the view:
Alter view < view name > as < query statement >;
– let the view name replace another query statement
If there are the following views:
create view v_c as select * from class;
Modify the query statement replaced by the view name:
alter view v_c as select c_id,c_name from class where c_tid is not null;
Query view: select * from v_c;

4. Delete view

-- Delete the basic format of the view: drop view <View name>; as
drop view v_c; -- The object deletion command is used here drop Delete database view object v_c

2, Index (keyword: index)

Index is used to improve the efficiency of query. For the fields that create index, the query efficiency is higher than that of ordinary fields.
Index stores data in a data structure that can efficiently obtain data. Different storage engines use different data structures. Generally, BTREE, hash table, red black tree and other data structures that can efficiently read data are used.

1. General index

Create normal index

-- Basic format:
-- create index Index name on Table name<Field 1[,Field 2,...,field n]>;
create index Ind_Sage on student(s_age); 
-- yes student Tabular s_age Property creates a normal index named Ind_Sage.  
select s_age from student; 

For querying s in the student table_ Age is a single field, and the query efficiency will be improved; Due to the small amount of data in the data table, we can't see the obvious effect of improving query efficiency; The more data in the data table, the larger the amount of data, and the more obvious the improvement of index effect will be

Add indexes in a modified manner

-- Basic format: alter table <Table name> add index <Index name(Attribute name)>;
alter table student add index Ind_Ssex(s_sex); 
-- by student In table s_sex Property to add a Ind_Ssex General index of

View all indexes in the table

-- Basic format: show index from <Table name>;
show index from student; -- see student All indexes in the table

So, does a table without an index exist?

show index from class; 
show index from teacher; 
-- Tables that are not indexed may also have indexes. If there are primary keys and foreign keys, there will be primary key indexes

Delete index

-- Basic format: drop index <Index name> on <Table name>; 
-- delete<Table name>Table and<Index name>Corresponding index
drop index Ind_Ssex on student;

2. Unique constraint (keyword unique index)

The query efficiency will be improved by querying the fields with unique indexes. Unique indexes are related to unique constraints. After adding a unique index to an attribute, duplicate values of the attribute are not allowed.

Create unique index

-- Basic format: create unique index <Index name> on <Table name(Attribute name)>; 
-- The table name is<Table name>The attribute name in the table is<Attribute name>The property creation index for is named<Index name>Unique index of
create unique index Ind_Sage on student(s_name); 
-- The table name is student Attributes in the table s_name Create index named Ind_Sage Unique index of

You can create a unique index automatically when you add a unique constraint to a property without manually creating a unique index.

Adds a unique index in a modified manner

-- Basic format: alter teble <Table name> add unique(<Attribute name>); 
-- The table name is<Table name>The property name in the data table is<Attribute name>Add a unique index to the properties of
alter table student add unique(s_name); 
-- The table name is student The property name in the data table is s_name Add a unique index to the properties of

3. Primary key index / foreign key index

Basic concepts of primary key index
Primary key / foreign key index keyword: primary key
The query efficiency of primary keys and foreign keys is relatively high, because primary keys and foreign keys have primary key indexes.

Creation of primary key index

A primary key index is automatically created when a primary key or foreign key constraint is established for an attribute, or when a table is created. For example:

create table Table name{ 
	id int; 
	name varchar(20); 
	primary key(id) 
	-- As attribute id Create a primary key index 
}

Addition of primary key index

-- Basic format: ALTER TABLE <Table name> ADD PRIMARY KEY (<Listing>); 
-- Add the primary key index in a modified way
alter table Table name add primary key(id);

4. Full text index (keyword: fulltext index)

Query the attributes of string or text type added with full-text index, and the query efficiency will be improved.

Creation of full-text index

-- Basic format: create fulltest index <Index name> <Table name(Attribute name)>; 
-- The table name is<Table name>The property name in the data table is<Attribute name>Create a full-text index using the properties of<Index name>
create fulltext index fullind_tname teacher(t_name); 
-- The table name is teacher The property name in the data table is t_name Add a full-text index to the properties of<Index name>
alter table teacher add fulltext index fullind_tname(t_name); 
-- The table name is teacher The property name in the data table is t_name Add a full-text index to the properties of fullind_tname

Full text indexing is relatively less used, which is limited to adding data types such as text and string.

5. Spatial index

Spatial index is an index established for spatial data types (point, line, surface and three-dimensional graphics). Because the application scope of spatial index is not extensive, it is generally only used in the database storing map, model and other related data.

6. Advantages and disadvantages of index

Advantages: using index can greatly improve the efficiency of query.
Disadvantages: compared with ordinary fields, the index needs to occupy additional disk space. Due to the differences in the stored data structure, when adding, deleting and modifying data, the indexed fields need to be dynamically maintained, which increases the workload of the DBMS and reduces the maintainability of the database.