Stored Procedure: writes the corresponding SQL query to the server, and directly calls the Stored Procedure data during the query.
1. delimiter $ modify the execution command to $
Create a stored procedure:
// Procedure
// Parameter in | Out | inout
//
// Feature value
// {Contains SQL | no SQL | reads SQL data | modifies SQL data}: These feature values provide sub-ProgramUse the internal information of the data. These feature values are currently only provided to the server, and the actual usage of the data is not restricted based on these feature values. Contains SQL indicates that a subprogram does not contain statements that read or write data. No SQL indicates that the subroutine does not contain SQL statements. Reads SQL data indicates a subprogram that contains read data but does not contain a Write statement. Modifies SQL data indicates the subprogram contains the statement for writing data. If the features are not explicitly specified, the default value is contains SQL.
//
Create procedure findz (in startid int, in endid int, out gettotal INT)
Reads SQL data
Begin
Select username from user where ID> startid and ID <endid;
Select found_rows () into gettotal;
End $
// Call procedure
// Call findz (4,10, @ Love)
//
// Select @ Love
// Show create procedure findz
// Show procedure status {like 'patter '}
Define Variables
// Declare va_name type [default value]
// Variable value 1
// Set var_name = expr [, var_name = expr]
// Select col_name [,] into var_name
// Function parameters can only be in
Create Function updates (Val varchar (30), wid int)
Returns varchar (30)
Reads SQL data
Begin
Declare name varchar (30 );
Update user set username = 'val' where id = WID;
Select username into name from user where id = WID;
Return name;
End $
// Call a function
// Select updates ();
// Show create function findz
// Show function status
Trigger
It can help applications in the database to ensure data integrity.
Create triggger Chufa trigger time event on table for each row
Trigger time: Before after
Trigger event: insert update Delete
On Table: Old. old table new. New table
Create trigger yangsa before insert on user for each row
Insert into user_bk (ID, username) values (New. ID, new. username );
View
View is a table that seems to exist.
Create view shitu as select ID, username, price from user;
View:
select ID, username from shitu;