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

1. Transaction management

* * (1) concept: a transaction refers to a logical set of operations that make up the units of this set of operations, either successful or unsuccessful.

(2) MySQL comes with its own transaction by default, but the transaction that comes with MySQL is a statement that monopolizes a transaction.

(3) you can also control your own affairs: * *

Star transcation;-start the transaction, and the sql after this statement will be in the same transaction

.

. # statement

Commit; # commits the transaction so that the impact of the sql in the transaction on the database occurs immediately

Rollback; # rollback the transaction and test back

Create table account (

Id int primary key auto_increment

Name varchar (40)

Money double

);

Insert into account values (null,' Laize ammonium', 2000), (null,' Hou Wenze', 1000)

* * (4)

Atomicity: a set of indivisible units in a transaction that either succeed or fail at the same time.

Consistency: data integrity should be consistent before and after the transaction

Isolation: when multiple users access the database concurrently, one user's transaction cannot be interfered by other user transactions.

Persistence: once submitted, changes to the data will be permanent

Isolation: in essence, it is a multi-thread concurrency security problem caused by multiple threads operating on the same resource. Locking can ensure isolation, but result in a decline in database performance.

If two transactions are modified concurrently: must be isolated

If two transactions query concurrently: no isolation is required

If a transaction modifies a query: dirty read # undo halfway, not repeatable # modify halfway, false read # add content * *

Four major isolations:

Read uncommitted # does not isolate

Read committed # can prevent dirty reading

Repeatable read # can not prevent false reading, but can only read the data of the start time transaction. If you want to view the data of the later time, you can only see it when the transaction terminates.

Serializable # database running in serialization is not implemented, low performance, directly locked, the other party can not be modified, until the end of the transaction.

Default Repeatable read

Set the statement:

SET SESSION TRANSCATION ISOLATION LEVEL isolation level

Query statement:

Select @ @ tx_isolation; # after the above statement is executed successfully

two。 Creation of stored procedure

Reuse of a function to reduce workload

(1) grammar:

CREATE PROCEDURE sp_name ([proc_parameter])

[characteristics...] Routine_body

~ proc_parameter# parameter list

Form: [IN | OUT | INOUT] param_name# parameter name type# parameter type

~ characteristics# storage characteristics

LANGUAGE SQL: indicates that the routine_body part consists of SQL statements

[NOT] DETERMINISTIC: indicates whether the result of the stored procedure execution is determined. Default NOT

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: contains SQL statements but does not contain read and write data statements, does not include SQL statements, read and write data statements, write data statements.

Default CONTAINS SQL

SQL SECURITY {DEFINER | INVOKER}: indicates who has the permission to execute, DEFINER means that only the definer can execute, and INVOKER means that the caller with permission can execute.

Default DEFINER

COMMENT'string': comment information, which can be used to describe stored procedures

~ routine_body:SQL statement

DELIMITER / / # defines the Terminator as / /

BEGIN

.

.

END

Delimiter / /

Create procedure nbaf ()

Begin

Select from team left join star on team.id = star.team_id

Union

Select from team right join star on team.id = star.team_id

End//

Delimiter

Call nbaf ()

(2) define a variable in the stored procedure

The declaration of the variable must be between BEGIN and END of the stored procedure, and the scope is the current storage scope

DECLARE var_name [, varname]... data_type [DEFAULT value]

Modify the variable value 1:

SET var_name = expr [, var_name = expr] # expression assigned to.

Modify the variable value 2:

SELECT col_name [...] # data into# replication var_name [..] # variable table_expr# query condition

Delimiter / /

Create procedure pf (in p_id int)

Begin

Select from team left join star on team.id = star.team_id where team.id = p_id

Union

Select from team right join star on team.id = star.team_id where team.id = p_id

End//

Delimiter

Call pf ()

3. Define conditions and handlers

(1) define conditions: define the problems encountered in the execution of the program in advance, the handler defines the way to deal with these problems, and ensure that the stored procedure can continue to execute when there is a warning or error in the stored procedure.

DECLARE condition_name CONDITION FOR [condition_type]

There are two forms of condition_type:

[condition_type]:

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

Sqlstate_value: is a string type error code of length 5

Mysql_error_code: error code for numeric type

For example: ERROR1142 (42000), sqlstate_value:42000,mysql_error_code:1142

(2) define the handler

DECLARE handler_type HANDER FOR condition_value [,...] Sp_statement

Handler_type:CONTINUE | EXIT | UNDO# encountered an error to withdraw the previous operation, but MySQL does not support it

Condition_value:

SQLSTATE [VALUE] sqlstate_value: string error value containing 5 characters

Condition_name: error condition name

SQLWARNING: matches all SQLSTATE error codes that start with 01

NOT FOUND: matches all SQLSTATE error codes that start with 02

SQLEXCEPTION: matches all SQLSTATE error codes except the beginning of 01pc02

Mysql_error_code: error code that matches the numeric type

Several ways to define handlers

Declare continue handler for SQLSTATE '42S02' set @ info=' NO_SUCH_TABLE'; # info output

Declare continue handler for 1146 set @ info= 'NO_SUCH_TABLE'; # 1146, capture mysql_error_code

Declare no_such_table condition for 1146

Declare continue handler for NO_SUCH_TABLE set @ info= 'ERROR'; # define the condition first, and then call

Declare exit handler for SQLWARNING set @ info= 'ERROR'

Declare exit handler for NOT FOUND set @ info= 'NO_SUCH_TABLE'

Declare exit handler for SQLEXCEPTION set @ info= 'ERROR'

4. Use of the cursor: when the amount of data is very large, use the cursor to query one by one

(1) cursor declaration: after the declaration of variables and conditions, and after the declaration of the handler

DECLARE cursor_name CURSOR FOR select_statement

(2) use of the cursor

Open the cursor:

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name]....

Close the cursor:

CLOSE curse_name

5. Control the use of processes: in writing stored procedures

(1) IF statement:

IF expr_condition THEN statement_list

[ELSEIF expr_contidion THEN statement_list]

[ELSE statement_list]

END IF

/ / expr_condition judgment statement statement_list SQL statement

(2) CASE statement:

CASE case_expr

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]

.

[ELSE statement_list]

END CASE

(3) LOOP statement:

[loop_table:] LOOP

Statement_list

END LOOP [loop_tabel]

/ / loop_table indicates the name of the dimension You can omit- -

Delimiter / /

Create procedure east () begindeclare ep1 int default 0 star.team_id where team.id ep2 int default 7 star.team_id where team.id ep1 = ep1 + 1 world if ep1 < 4 then select * from team left join star on team.id = star.team_id where team.id = ep1 union select * from team right join star on team.id = star.team_id where team.id = ep1;else leave loop;end if;end LOOP esat_p;end//delimiter; call east () / / cannot use-(4) LEAVELEAVE label # to exit the loop (5) ITERATEITERATE label # to loop again Back to the beginning (6) repeat [repeat _ lable:] REPEAT statement_listUNTIL expr_condition # until it is determined that the statement is true to exit END repeat [repeat _ lable]

(7) WHILE

[while_lable:] WHEIL expr_condition DO

Statement_list

END WHILE [while_lable]

6. Call stored procedure

(1) execute stored procedures

CALL sp_name ([parameter [.]])

7. View stored procedures

(1) SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

Show procedure status

Show procedure status like 'nbaf'

Show procedure status like'% f'\ G # View stored procedures ending in f

8. Modify stored procedure

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic....]

# characteristic indicates which part of the stored procedure to be modified. The values are as follows

~ CONTAINS SQL

~ NO SQL

~ READS SQL DATA # read data

~ MODIFIES SQL DATA # write data

~ SQL SECURITY {DEFINER | INVOKER}

~ COMMENT'string' # comment

At present, MySQL does not provide modifications to existing stored procedure codes. Delete them if you want to modify them.

9. Delete stored procedure

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

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.