MySQL (9), mysql

MySQL (9), mysql
1. Code Execution Structure

• There are three types of code execution structures: sequential structure, branch structure, and cyclic structure.

 

1.1 Branch Structure

• Branch Structure: Prepare multiple code blocks and execute a code segment selectively according to the conditions.

 

• In MySQL, only if branches exist.

 

• Basic syntax

If condition judgment then -- the code that meets the condition else if -- the code that does not meet the condition else -- the code that does not meet the condition end if;

 

• Trigger combined with if branch: judge whether the product stock is sufficient, not enough to generate orders.

-- Trigger combined with the if branch to determine whether the commodity inventory is sufficient, insufficient to generate order delimiter $ create trigger insert_before before insert on 'order' for each rowbegin -- determine whether the commodity inventory is sufficient -- Obtain the commodity inventory: in the table, SELECT inv FROM goods WHERE id = new. g_id INTO @ inv; -- Compare kuc IF @ inv <new. g_number THEN -- insufficient inventory -- the trigger does not provide a capability to prevent an event. Only the Force Error insert into xxx VALUES (xxx); end if; END $ DELIMITER;

 

1.2 Loop Structure

• Loop Structure: A code is executed under a specified condition.

 

• While Loop

While condition judgment do -- the code that meets the condition to be executed -- change the loop condition end while;

Loop Control: cyclically judge and control the internal loop. MySQL does not have the corresponding continue and break; iterate: similar to continueleave: similar to break

How to Use iterate and leave: iterate/leave loop name;

-- Define loop name: while condition do -- loop body -- loop control end while;

 

2 Function

• Function: encapsulate a code block into a structure. When you need to execute a code block, you can call the structure for execution (code reuse ).

 

• There are two types of functions: system functions and user-defined functions.

 

2.1 system functions

• The function defined by the system can be called directly.
• Any function has a return value, so the function is called Through select.

 

• Substring (str, offset, len): Starting from offset (The subscripts in MySQL are from 1), and the truncation length is len.

SET @ cn = 'Hello world'; SET @ en = "hello world"; -- string truncation select substring (@ cn, 1, 2), SUBSTRING (@ en, 1, 2 );

• Char_length: character Length
• Length: the length of a byte.

SET @ cn = 'Hello world'; SET @ en = "hello world"; -- String length select CHAR_LENGTH (@ cn), CHAR_LENGTH (@ en), LENGTH (@ cn ), LENGTH (@ en );

• Instr: determines whether a string exists in a string. If yes, the position is returned. If no, 0 is returned.

SET @ cn = 'Hello world'; SET @ en = "hello world"; select instr (@ cn, 'you ');

Select instr (@ cn, 'haha ');

• Lpad: Left fill, fill the string to the specified length according to a specified filling method.

SET @ cn = 'Hello world '; SET @ en = "hello world"; select lpad (@ cn, 20, 'hha ');

• Strcmp: compare. If the string is small,-1 is returned. If it is equal, 0 is returned. If it is large, 1 is returned.

SET @a = 'hello';SET @b = 'hei';SET @c = 'HEI';SELECT STRCMP(@a,@b),STRCMP(@b,@c),STRCMP(@c,@a);

 

2.2 User-Defined Functions

• Function elements: function name, parameter list, return value, function body (scope ).

 

• Create a function

-- Create function name ([parameter list]) RETURNS data type BEGIN -- FUNCTION body -- RETURN value (specified data type); END;

DELIMITER $$CREATE  FUNCTION dis() RETURNS INTBEGIN    RETURN 100;END$$ DELIMITER ;

 

• User-Defined Functions are called in the same way as system functions.

SELECT dis();

 

2.3 view Functions

• View All functions:

show function status [like 'pattern'];

• View function creation statements

 

2.4 modify and delete Functions

• The function cannot be modified. You can only delete the function and then add a new function.

 

• Basic statement for deleting a function

Drop function name;

 

2.5 function parameters

• There are two types of functions: the defined parameters are named form parameters, and the called parameters are called real parameters.

 

• Parameter: the data type must be specified.

DELIMITER $ create function name (Name Data Type) RETURNS data type BEGIN -- FUNCTION body -- RETURN value; END; $ DELIMITER;

DELIMITER $$CREATE FUNCTION res (num INT) RETURNS INTBEGIN    SET @i = 1;    SET @sum = 0;    WHILE @i <= num DO    SET @sum = @sum + @i;    SET @i = @i+1;    END WHILE;        RETURN @sum;    END;$$DELIMITER ;

SELECT res(100);

 

• @ Variables defined inside the function can also be used outside the function.

 

2.6 function Scope

• The scopes in MySQL are exactly the same as those in js.
  • Global variables can be used anywhere; local variables can only be used within the function.

 

• Global variables: use the set keyword and @ symbol to define them.
• Local variables: Use the declare keyword to declare. There is no @ symbol. The declaration of all local variables must be prior to the start of the function body.

DELIMITER $$CREATE FUNCTION res (num INT) RETURNS INTBEGIN    DECLARE i INT DEFAULT 1;    DECLARE SUM INT DEFAULT 0;    WHILE i <= num DO    SET SUM = SUM +i;    SET i = i+1;    END WHILE;        RETURN SUM;    END;$$DELIMITER ;

 

3. Stored Procedure

• Stored procedure: procedure is a method used to process data.
• A stored procedure is a function without return values.

 

3.1 create a stored procedure

• Basic syntax

DELIMITER $ create procedure process name ([parameter list]) BEGIN -- Process body END $ DELIMITER;

DELIMITER $ create procedure proe () BEGIN -- SELECT * FROM my_account; END $ DELIMITER;

 

3.2 view stored procedures

• The function is fully used and stored.

 

• View all stored procedures.
  • Show procedure status [like 'pattern'];

SHOW PROCEDURE STATUS;

• View creation process statements
  • Show create procedure stored procedure name;

 

3.3 call Process

• Because stored procedures do not return values, you cannot use the select keyword.

 

• Call the stored procedure and use the call keyword.

The name of the call stored procedure;

CALL proe();

3.4 modification and deletion Processes

• The stored procedure cannot be modified. You can only delete the stored procedure before adding the stored procedure.

 

• Delete stored procedure

The name of the drop stored procedure;

 

3.5 stored procedure parameter types

• Function parameters need to be specified as data types, and the process is stricter than the function.

 

• The process also has its own type limitation: Three Types
  • ① In: data is transmitted from the outside for internal use (value transfer): it can be a value or a variable
  • ② Out: Only internal use of the process (external data is not required) is allowed for external use (reference transfer, external data is cleared before entering the internal), and only variables are allowed.
  • ③ Inout: external objects can be used internally, internal modifications can also be used externally, and typical references can be passed, but only variables can be passed.

 

• Basic syntax

DELIMITER $ create procedure process name (IN parameter name data type, OUT parameter name data type, INOUT Parameter Name Data Type) BEGIN -- Process body END; $ DELIMITER;

DELIMITER $ create procedure pro1 (IN int_1 INT, OUT int_2 INT, INOUT int_3 INT) BEGIN -- SELECT INT_1, INT_2, INT_3; END; $ DELIMITER;

• Call: variables must be input for parameters of the out And inout types, instead of numeric values.

SET @int_2 = 1;SET @int_3 = 1;SET @int_1 = 1 ;SELECT @int_1,@int_2,@int_3;CALL pro1(@int_1,@int_2,@int_3);SELECT @int_1,@int_2,@int_3;