Shulou(Shulou.com)06/01 Report--

The following mainly brings you a detailed introduction of the advanced application index, view and trigger of the mysql database. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit the advanced application index, view and trigger of the mysql database. All right, don't talk too much nonsense, let's just read the following.

Advantages and disadvantages of index

Advantages

Speed up access

Strengthen the uniqueness of rows

Shortcoming

Indexed tables need more storage space in the database

Commands that manipulate data take longer to process because they need to update the index

Select indexed columns according to the following criteria

Frequently searched columns

Columns that are often used as query selections

Frequently sorted, grouped columns

Columns that are often used as joins (primary / foreign keys)

Do not use the following columns to create an index

Columns that contain only a few different values

The table contains only a few rows

Reduce the use of * to return all columns when querying, do not return unwanted columns

Indexes should be as small as possible, and indexes should be built on columns with a small number of bytes.

When there is more than one conditional expression in the WHERE clause, the expression containing the index column should precede other conditional expressions

Avoid using expressions in ORDER BY clauses

Periodically rebuild or reorganize indexes and defragment according to the frequency of business data

Classification of indexes

General index

Unique index: the primary key itself is a special unique index.

Full-text index

Single column index

Multi-column index

Spatial index

Prepare the experimental environment

Bulk insert records: mysql > delimiter $$/ / define delimiters

Mysql > Create procedure autoinsert1 () / / create stored procedure

Begin

Declare i int default 1

While (1delimiterskep / restore the delimiter back

Mysql > Call autoinsert1 (); / / call stored procedure

Note when creating an index (when creating a table): an index will be very slow at the beginning if you bulk import data because there is an index on a field.

Syntax: create table table name (

Field-data type [integrity constraint.]

Field-data type [integrity constraint.]

[unique | fulltext | spatial] index | key [index name] (field name [length] [asc | desc])

);

Create a normal index when creating a table:

Create table department (

Dept_id int primary key

Dept_name varchar (30)

Commnet varchar (50)

Index index_dept_name (dept_name)

);

View index: desc table name or show create table table name\ G

Create a unique index when creating a table:

Create table department (

Dept_id int primary key

Dept_name varchar (30)

Commnet varchar (50)

Unique Index index_dept_name (dept_name)

);

Create a full-text index when creating a table:

Create table department (

Dept_id int primary key

Dept_name varchar (30)

Commnet varchar (50)

Fulltext Index index_dept_name (dept_name)

) engin=myisam

Create a multi-column index when creating a table:

Create table department (

Dept_id int primary key

Dept_name varchar (30)

Commnet varchar (50)

Index index_dept_name (dept_name,commnet)

);

Simulated query analysis query syntax

Explain select * from dept 4 where commnet='sale'\ G

Create an index (existing table create)

The content and structure of the replication table copy all the data from the T2 table to the T4 table

Create table T4 select * from T2

Copy only the structure of the table to copy the structure of the T2 table to the T4 table

Create table T4 select * from T2 where 1: 2

Or create table T5 like T2

Syntax: create [unique | fullext | spatial] index index name on table name (field name [(length)] [asc | desc])

Create a normal index instance:

Create index index_dept_name on departmnet (dept_name)

Create a unique index instance:

Create unique index index_dept_name on departmnet (dept_name)

Create a full-text index instance:

Create fulltext index index_dept_name on departmnet (dept_name)

Create a multi-column index instance:

Create index index_dept_name on departmnet (dept_name,comment)

Create an index (existing table alter table)

Syntax: alter table table name add [unique | fullext | spatial] index index name (field name [(length)] [asc | desc])

Create a normal index instance:

Alter table department add index index_dept_name (dept_name)

Create a unique index instance:

Alter table department add unique index index_dept_name (dept_name)

Create a full-text index instance:

Alter table department add fulltext index index_dept_name (dept_name)

Create a multi-column index instance:

Alter table department add index index_dept_name (dept_name,comment)

Manage index

View Index

Show create table table name\ G

Test example

EXPLAIN select * from department where dept_name='hr'

Delete index

Drop index index name on table name

View introduction: security and simplification

The Mysql view is a virtual table whose content is defined by the query. Like the real table, the view consists of a series of named columns and rows, but the view does not exist in the database in the form of stored data values. The row and column data freely define the table referenced by the query of the view, and in the dynamic generation of the referenced view, the role of the mysql view is similar to filtering for the underlying tables referenced in it. Filters that define views can come from one or more tables in the current or other database, or other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them.

Views are sql query statements stored in the database, mainly for two reasons, security reasons, views can hide some data, such as some sensitive information, another reason is that it can make complex queries easy to understand and use.

Create a view

Grammar one

Create [algorithm= {undefined | merge | temptable}]

View view name [(field 1, field 2.)] As select statement

[with [caseaded | local] check option]

Grammar two

Create view View name as select statement

Example:

Create database view; / / create a database. It is recommended to create

Use view

Create view view_user

As select user,host,password from mysql.user

Select * from view_user

View view

Show tables View View name

Use view

Show tables

Show tables status

Example: view the view and all table details in the view database

Show table status from view\ G

Example: view view name view_user details in view database

Show table status from view like "view_user"\ G

View view definition information

Use view

Show create view view_user\ G

View view structur

Use view

Desc view_user

Modify the view

Method one is created after deletion

Use view

Drop view view_user

Create view view_user as select user,host from mysqk.user

Select * from view_user

Method 2: replace the original view

Syntax: create or replace view view name as select statement

Example: use view

Create or replace view view_user as select user,password from mysql.user

Method 3: alter modifies the view

Syntax: alter view view name as select statement

Example: use view

Alter view view_user as select user,password from mysql.user

Manipulate the base table through the view

Query data select is usually a query operation

Select * from view_user

Update data update

Delete data delect

Delete View

Drop view View name

Example:

Use view

Drop view view_user

Brief introduction of trigger

Triggers is a special stored procedure, its execution is not called by the program, nor started manually, but triggered by time, for example, when a table is insert,delect,update, it is activated to execute. Triggers are often used to strengthen data integrity constraints and business rules, etc.

For example, when you add a student's information to the student table, the total number of students should change at the same time, so you can create a trigger for the student table and perform a calculation of the total number of students each time you add a student record. in order to ensure the consistency of the total number of students and the number of records.

Create triggers: up to six triggers can be created in the same table

Syntax: create trigger trigger name before | after trigger event

On table name for each row

Begin

Trigger program body

End

Trigger name: / / 64 characters maximum

The execution interval of for each row / / triggers, which means for each row, or for the entire table if not added

Insert | update | events triggered by delete//

On table name / / on which table to create the trigger

Trigger program body: / / the sql statement triggered by the trigger, the statement can use sequence, judgment, loop and other statements to achieve the logic functions needed by the general program.

Example:

Create a tabl

Create table student (

Id int unsigned auto_incremnet primary key not null

Name varchar (50)

);

Unsigned / / unsigned modifier

Insert data into the table

Insert into student values (1)

Create Table 2 Total Table

Create table student_total (total int)

Insert data into the table

Insert into student_total values (1)

Create trigger instance-student _ insert_trigger

Mysql > Delimiter & & / / define Terminator

Mysql > Create trigger student_insert_trigger after insert

On student for each now

Begin

Upadte student_total set total=total+1

End&&

Mysql > Delimiter

Create trigger instance two

Mysql > Delimiter & & / / define Terminator

Mysql > Create trigger student_insert_trigger after delect

On student for each now

Begin

Upadte student_total set total=total-1

End&&

Mysql > Delimiter

Test results, insert data into the student table and view the results of the total table

Insert into student values (2 recording Alice')

Select * from student_total

View trigger

Method 1 check through the show triggers statement

Case: show triggers\ G

Method 2 check through the system table triggers

Case: use information_schema

Select * from triggers\ G

SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=' trigger name'\ G

Delete trigger

Drop trigger trigger name

Example: Drop trigger student_insert_trigger

The case of trigger

Case 1: after adding tab1 table records, records are automatically added to tab2, and the data types of fields that can be synchronized must be consistent.

Create tab1

Drop table if exists tab1

Create table tab1 (

Tab1_id varchar (11)

);

Create tab2

Drop table if exists tab2

Create table tab2 (

Tab2_id varchar (11)

);

Trigger: after_tab1_trigger

Function: automatically add records to tab2 after adding tab1 records

Mysql > Delimiter & & / / define Terminator

Mysql > drop trigger if exists tab1_after_trigger

Mysql > Create trigger tab1_after_trigger after insert

On tab1 for each now

Begin

Insert into tab2 (tab2_id) values (new tab1_id); the new keyword refers to the new value added after the tab1 is inserted, which should be deleted with the old keyword.

End&&

Mysql > Delimiter

Case 3:

When we update the student information in the student table, our student update table changes accordingly.

Create a tabl

Drop table if exists student

Create table student (

Student_id int auto_incremnet primary key not null

Student varchar (30) not null

Student_sex enum ('masking mast') default 'm'

);

Insert data:

Insert into student values

(1) the memory of the jackpot is similar to that of the other.

(2) 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 2, 2, 2, 4, 2, 4, 2, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6

(3 recordings, girls, etc.)

Create tab2

Drop table if exists update_student

Create table update_student (

Update_record int auto_incremnet primary key not null

Student_id int not null

Update_date date

);

Insert data:

Insert into update_student values

(1 ~ (1))

(2pr 2jinnow ())

(3pr 3Jing now ())

Create trigger ustudent_trigger

Mysql > Delimiter & & / / define Terminator

Mysql > drop trigger if exists student_update_trigger

Mysql > Create trigger student_update_trigger before update

On studnet for each now

Begin

If new.studentstudent student id then

Update update_student

Set student_id=new.student_id

Where student_id=old.student_id

End if

End&&

Mysql > Delimiter

The revised value is called the new value, and before the change is called the Old value, which means that after the student id is modified, the new value of the student id will also be assigned to the update_ student table.

Delete synchronization operation case

Mysql > Delimiter & & / / define Terminator

Mysql > drop trigger if exists student_delete_trigger

Mysql > Create trigger student_delete_trigger before delete

On studnet for each now

Begin

Delete from update_student

Where student_id=old.student_id

End&&

Mysql > Delimiter

For the above on the advanced application of mysql database index, view, trigger detailed introduction, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.