DAY4 MySQL trigger view index and design optimization

Trigger

MySQL includes support for triggers. A trigger is a database object associated with a table operation that invokes the execution of a trigger on a table when a specified event occurs on the table on which the trigger is located.

Triggered by an event, cannot be sent to a parameter

Grammar

CREATE    [definer = {User | Current_User}]    TRIGGERtrigger_name trigger_time trigger_event onTbl_name forEach ROW[Trigger_order]trigger_bodytrigger_time: {before|After }trigger_event: {INSERT | UPDATE | DELETE}trigger_order: {follows|Precedes} other_trigger_name

which

Trigger_name: Identifies the trigger name, which is specified by the user;
Trigger_time: Identification trigger time, value is before or after;
Trigger_event: Identifies the triggering event, with a value of INSERT, UPDATE, or DELETE;
Tbl_name: Identifies the name of the table on which the trigger is established, that is, the table on which the trigger is established;
TRIGGER_STMT: A trigger program body, which can be an SQL statement, or multiple statements containing the BEGIN and END.

Thus, there are 6 types of triggers that can be created: before insert, before UPDATE, before DELETE, after INSERT, after UPDATE, and after DELETE.

Another limitation is that you cannot create 2 triggers of the same type on a table at the same time, so there are up to 6 triggers on a table.

trigger_event detailed

In addition to defining the INSERT, UPDATE, and DELETE basic operations, MySQL also defines the LOAD DATA and REPLACE statements, which can also trigger triggers of the above 6 types.

The load data statement is used to load a file into a data table rather than a series of INSERT operations.

The REPLACE statement is generally similar to the INSERT statement, except that when the table has a primary key or unique index, if the inserted data is consistent with the original primary key or unique index, the original data is deleted and then a new data is added, which means , a REPLACE statement is sometimes equivalent to a single clause.

Insert statements, sometimes equivalent to a DELETE statement plus an INSERT statement.

Insert trigger: Activates a trigger when inserting a row, which may be triggered by an insert, LOAD DATA, and REPLACE statement;
UPDATE trigger: Activates a trigger when a row is changed and may be triggered by an UPDATE statement;
Delete trigger: Activates a trigger when a row is deleted and may be triggered by a delete, REPLACE statement.

BEGIN ... END detailed

In MySQL, BEGIN ... The syntax for the END statement is:

BEGIN
[Statement_list]
END
Where statement_list represents a list of one or more statements, each statement in the list must end with a semicolon (;).
In MySQL, the semicolon is the end-of-statement identifier, and a semicolon indicates that the segment statement has ended and MySQL can start executing. Therefore, the interpreter encounters a semicolon in the statement_list and then begins execution, and then reports an error because no END is found that matches the BEGIN.

This will use the DELIMITER command (DELIMITER is the delimiter, the meaning of the delimiter), it is a command, do not need a statement to end the identity, the syntax is:
DELIMITER New_delemiter
New_delemiter can be set to 1 or more length symbols, the default is a semicolon (;), we can modify it to other symbols, such as $:
DELIMITER $
After that, the statement ends with a semicolon, and the interpreter does not react, only if it encounters $, it is considered to be the end of the statement. Note that after use, we should also remember to change it back.

Case One

--Create a trigger Cls_tri, when you delete a data from classroom, add this data to the Clsbak backup tableCreate TRIGGERCls_tri AfterDELETE  onClassroom forEach ROWBEGIN --add to Clsbak old New Insert  intoClsbakValues(old.cid,old.cname);END;Delete  fromClassroomwhereCid= 9;

Case Two

A complete example of creating a trigger
Suppose there are two tables in the system:
Class table Class (class number ClassID, number of students in the class Stucount)
Student Table Student (School number Stuid, class number ClassID)
To create a trigger to automatically update the number of students in the class table as the student adds

Elimiter $Create TriggerTri_stuinsert afterInsert onStudent forEach rowbeginDeclareCint;SetC=(SelectStucount fromClasswhereClassID=new.classid);UpdateClassSetStucount=C+ 1 whereClassID=New.classid;End$DELIMITER;

Variable explanation

MySQL uses DECLARE to define a local variable that can be used only at BEGIN ... END compound statement, and should be defined at the beginning of the compound statement,

That is, before the other statements, the syntax is as follows:

DECLARE var_name[,...] Type [DEFAULT value]
which
Var_name is the variable name, as with the SQL statement, the variable name is not case-sensitive; Type is any data type supported by MySQL; You can define multiple variables of the same type at the same time, separated by commas, the initial value of the variable is NULL, and if necessary, you can use the default clause to provide the defaults. The value can be specified as an expression.

A SET statement is used to assign values to variables, with the following syntax:

SET var_name = expr [, var_name = expr] ...

NEW detailed with old

The new keyword is used in the above example, similar to INSERTED and DELETED in MS SQL Server, where new and old are defined in MySQL to represent

The row of data that triggers the trigger in the same table as the trigger.
In particular:
In an INSERT trigger, new is used to represent the before or already (after) inserted data;
In an UPDATE trigger, old is used to represent the original data that will or has been modified, and new is used to represent the data that will or has been modified;
In a delete type trigger, old is used to denote the original data that will or has been deleted;
How to use: New.columnname (ColumnName is a column name for the corresponding data table)
In addition, old is read-only, and NEW can use SET assignment in a trigger so that the trigger is not triggered again, causing a circular call (such as adding "2013" to the student's number before each insert).

View triggers

and view the database (show databases;) to view the table (show tables;), the syntax for viewing the trigger is as follows:

SHOW TRIGGERS [from schema_name];
Where Schema_name is the name of the schema, in MySQL, the schema and database are the same, that is, you can specify the name of the databases, so that

Without first "use database_name;" The

Delete Trigger

As with deleting a database, deleting a table, the syntax for deleting a trigger is as follows:

DROP TRIGGER [IF EXISTS] [schema_name.] Trigger_name

execution order of triggers

The database we build is generally a InnoDB database, and the tables created on it are transactional tables, which are transaction-safe. At this point, if the SQL statement or trigger execution fails, MySQL rolls back the transaction, with:

① if the before trigger execution fails, SQL does not execute correctly.
The after type trigger does not fire when the ②sql execution fails.
③after type of trigger execution fails and SQL is rolled back

View definition

Virtual tables. A virtual table, based on a base table, that dynamically queries the data that needs to be presented during use.

Grammar

Create or Replace View VIEW_EMP1  as Select *  from where deptno=ten;

With CHECK option

Ensure view security. The condition field after the WHERE clause cannot be modified.

Benefits of the View

A) Streamline queries

b) Security of data (hidden)

c) Implement field merge, Split

Advantages of indexed views

1. Simplify your query

2. Security of data (hidden)

3. Implement field merge, Split

Suitable for use in indexing situations

1. Columns that are frequently used as query criteria.

2. Add an index to the GAO Ji Shishou series.

3. Add an index to a table with large amounts of data.

Situations where indexing is not a good use

1. Frequent additions and deletions to the table (Maintenance required)

2. Small data volume, low base

3. Cannot add too many indexes

Case

// Create an index Create Index  on User (userid); // Delete Index Drop Index  on user;

Design paradigm of Database design database

Redundancy/Query Efficiency/expansion

A. First Paradigm 1NF

All domains are atomic. field values are indivisible.

B. Second Paradigm 2NF

Based on the first paradigm.

All non-primary key fields must be directly associated with the primary key field and cannot be associated with a part of the primary key (Federated primary key)

A table describes a single entity.

C. Third Paradigm 3NF

Ensure that each column is directly related to the primary key column, not indirectly

There cannot be a direct association between non-primary key fields.

Design step A of the database. Requirements Analysis Phase

Accurate understanding and analysis of user needs (including data and processing)

Is the basis of the entire design process, is the most difficult, the most time-consuming step

B. Conceptual structure design phase

is the key to the entire database design

Design the E-R model diagram of the database to confirm the correctness and completeness of the requirement information

C. Logic design Phase

Convert the E-r diagram to a logical model (the logical model is a relational model that manifests as multiple tables)

Review of the three paradigms of application database design

Design out-of-mode, build views

D. Physical design Phase

Determining access methods (e.g., indexing settings)

Identify the storage structure (where files are stored, etc.) and write code to implement the front-end application

E. Database implementation phase

Load data into and debug the database

F. Operational and maintenance phases

Using and Maintaining Databases

E-r Model Diagram

Provides methods for representing entity types, attributes, and connections to describe the conceptual model of the real world.

There are four components in the ER diagram:

Rectangle Box: Represents the entity, and the entity name is entered in the box.

Diamond Box: Indicates a contact, and the contact name is entered in the box.

Ellipse Box: A property that represents an entity or contact, and the property name is entered in the box. For the primary property name, underline it under its name.

Connections: Between entities and attributes, between entities and relationships, between contacts and attributes, and by a straight line, and by marking the type of contact on a line. (For one-to-one contact, write 1 in the two entity line direction; For a-to-many connection, write a 1 on a side, write N for many, and write n,m in two entity lines for many-to-many relationships.) )

Entity Relationship (table) A. Individual: (Personal information and archival information)

Foreign key field add UNIQUE constraint

Primary key do foreign key

B. One-to-many: (EMP and dept) C. Many-to-many: (Student and Corse)

Creates a relational table design federated primary key.

Optimization of the database

The less the number of query records, the better

Try to walk the index

Optimization principles

1) Minimize use *

2) Index invalidation:

A) index columns using is null/is NOT NULL, index invalidation

b) The index column cannot use the function (upper lower)

Inclusion also causes the index to become invalid.

c) The index column cannot be evaluated.

d) Index column is invalidated with not! = <> index.

e) Use the or index to fail and use union instead.

f) substituting exists with not exists instead of in

Backup and restore

Executing commands in cmd

-- Backup (Export) - - > D:/t.msql;    -- Import - - < C: \backup. sql   

DAY4 MySQL trigger view index and design optimization