Mysql----Shallow views, stored procedures, triggers

First, views view

A view is a virtual table that itself does not store any data. Only the runtime contains dynamically retrieved data.

Eg:select SID, Name, sex, s_num from student, school WHERE sid = 2 and student.sid = Scholl.sid;

This simple query involves two of tables. So anyone who needs this data must be familiar with the two tables and the relationships between them. If you want to retrieve additional student information, you must modify the WHERE clause. If you can wrap the entire query into a virtual table studentinfo, you can get the data in this way:

Eg:select SID, Name, sex, s_num from studentinfo WHERE sid = 2;

Use views to reuse SQL statements. For people who use it, you don't have to know the details. It also has a protective effect on the original data.

Views also have some limitations, such as the inability to index and the associated triggers. The name must be unique.

To create a view:

Eg:create VIEW Studentinfo as SELECT sid name, sex, s_num from student, school WHERE student.sid = School.sid;

Views can also be updated, but only in specific cases. If the view has these definitions, it cannot be updated: grouping, joins, subqueries, DISTINCT, aggregate functions, computed columns.

Second, the stored procedure

When the business logic that needs to be dealt with is complex, you can write SQL statements all the time and take into account all the details and data integrity that need to be handled. You can create stored procedures instead. It is like a batch that contains a predefined collection of one or more SQL statements. But its role is not limited to this.

Create:

Eg:creat PROCEDURE prostudent ()

BEGIN

SELECT Max (Score) as Scoremax from student;

END;

Delete:

Eg:drop PROCEDURE prostudent;

Use parameters:

Eg:create PROCEDURE prostudent (Out Scorehigh decimal (8, 2), Out Scorelow decimal (8, 2))

BEGIN

SELECT Max (Score) to scorehigh from student;

SELECT Min (Score) to scorelow from student;

END;

Perform:

Eg:call prostudent (@scorelow, @scorehigh);

SELECT @scorehigh, @scorelow;

A stored procedure named Prostudent was executed, and the highest and lowest scores were returned.

In addition, stored procedures can write comments, define temporary variables, in-pass parameters, and process Control statements.

SHOW CREATE PROCEDURE * * *; You can view the statements that create the stored procedure.

Show PROCEDURE STATUS; you can see when and by whom this stored procedure was created.

Third, cursors

The SELECT statement returns a result set that may be multiple flights that meet the criteria. What do we do when we want to do some processing on each row of this result set, or in the first row, the last row, the previous line, and so on? The cursor is used here. Cursors in MySQL can only be used for stored procedures, which are different from other databases.

Using cursors requires defining the DECLARE cursor for, then opening open, using, and closing close. The life cycle of a cursor is only in the stored procedure, that is, if you do not close it, it is automatically closed when the stored procedure end.

Once the cursor is open, a row can be fetched with a fetch, pointing internally to the next row, and the next row is taken when the fetch is fetched again.

For example: Now you want to add the scores of all the students with a SID greater than 3.

eg:DELIMITER //

CREATE PROCEDURE Sumofscore (out sum INT)

BEGIN

DECLARE done Booleean DEFAULT 0;

Delcare tmp INT;

DECLARE s INT DEFAULT 0;

DECLARE YB CURSOR for SELECT score from student WHERE SID > 3;

DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET done = 1;

OPEN YB;

REPEAT

FETCH YB into TMP;

SET s = s + tmp;

UNTIL done END REPEAT;

CLOSE YB;

SELECT s into sum;

END

//

DELIMITER;

DELIMITER to redefine the terminator for MySQL. 02000 is the error code that is not found by the data, and it is used to determine if all data is traversed.

Iv. triggers

In some cases, a trigger is used when you want certain statements to be executed automatically when a particular event occurs. MySQL triggers can only respond to delete, INSERT, and UPDATE statements.

To create a trigger:

Eg:creste TRIGGER newstudent after INSERT on student for each ROW SELECT new.sid into @s;

The trigger name is Newstudent,insert specifies that the response event is an insert operation. AFTER/BEFORE Specifies whether to fire before or after an event executes. The for every row specifies that each row is inserted, so each row is inserted, the SID of the line is passed to the variable s. Only one trigger is allowed per event per table, so there are up to 6 triggers per table. A trigger can also respond to only one event.

To delete a trigger:

Eg:drop TRIGGER newstudent;

Using triggers:

In Insertzhong, you can refer to a virtual table named new to access the inserted row. In Befroe Insert, you can also reference new and even update the data to change the contents of the inserted data.

Delete trigger, you can refer to a virtual table named old to access the deleted row.