13th MySQL Advanced programming

1. Transactions: One or a series of queries;

2. Use the Transaction-safe table type (via InnoDB):

① off Auto-commit: Set autocommit=0;

If Autocommit is open, start a transaction using the following statement:

Start transaction; This sentence is not required if auto-commit is off

② completes the statement of the constituent transaction and submits it to the database: commit;

③ status before returning to the database: rollback;

④ Convert the table to a InnoDB table (previously the MyISAM table):

ALTER TABLE orders TYPE=INNODB;

ALTER TABLE Order_items TYPE=INNODB;

After you convert to a InnoDB table, you need to use commit again to complete the commit to the database.

3. (INNODB) Add foreign key:

To first create a table that uses a foreign key:

such as: CREATE TABLE order_items{

......

}type=innodb;

Then add the foreign key using the ALTER TABLE statement:

such as: ALTER TABLE Order_items TYPE=INNODB;

ALTER TABLE Order_items

Add foreign KEY (OrderID) references orders (OrderID);

The OrderID column is a foreign key that contains the OrderID column values in the Orders table

4. Storage:

① declares a stored procedure:

# basic_stored_procedure.sql# Basic storedprocedureExampledelimiter//# separators//to make use of a semicolon delimiter in a stored procedure.Create procedureTotal_orders (Out of totalfloat# total_orders is the stored procedure name # out indicates that the parameter will be outgoing or returned (corresponds to in) # Total is the parameter passed, and if more than one argument is separated by commas # float is the type of the parameterBEGIN    Select sum(amount) intoTotal fromorders;END//delimiter;# The process declaration is complete, set the delimiter back to a semicolon

After the procedure declaration is finished, use the call keyword:

such as: Call Total_orders (@t);

Call the Total_orders procedure and pass in a variable to hold the result @t

View results: Select @t;

② declares a storage function:

# basic_function.sql# Basic Syntax to CreateAfunctiondelimiter//Create functionAdd_tax (Pricefloat)returns floatbegin    DeclareTaxfloat default 0.10; # Declare for declaring local variables in Begin...endreturnPrice*(1+Tax );End//delimiter;

View results: Select Add_tax (100); 100 is the price value of passing the past

③ view defining stored procedures and stored functions:

Show CREATE PROCEDURE total_orders;

Show Create function Add_tax;

Delete:

drop procedure Total_orders;

Drop function Add_tax;

④ cursors, Control structures:

# control_structures_cursors.sql#Procedure  toFind the OrderID withThe largest amount# could is done with Max, but just toIllustrate storedprocedurePrinciplesdelimiter//Create procedureLargest_order (out largest_idint)begin    Declarethis_idint; #当前行的orderid值DeclareThis_amountfloat; #当前行的amount值DeclareL_amountfloat default 0.0; #最大的订单金额Declarel_idint; #最大订单金额对应的IDDeclareDoneint default 0; #循环标记 # declaration handle, similar to an exception in the stored procedure # (the handle will be in the SQLState'02000'called when the statement is executed)Declare ContinueHandler forSQLState'02000' SetDone=1; # cursor C1, similar to an array that obtains a result set from a queryDeclareC1cursor  for SelectOrderID, Amount fromorders; OpenC1; #open才是真正开始执行查询 RepeatFetchC1 intothis_id, This_amount; if  notDone Then            ifThis_amount>L_amount Then                SetL_amount=This_amount; Setl_id=this_id; End if; End if; Until doneEndrepeat; CloseC1; Setlargest_id=l_id;End//delimiter;

Calling process: Call Largest_order (@l);

View results: Select @l;

13th MySQL Advanced programming