Mysql common knowledge point _ MySQL

Mysql common knowledge point bitsCN.com

--Stored Procedure and common process control syntax

/* This code creates a stored procedure named "p4" and sets two int-type varchar parameters, s1, s2, and s3, or other data types, two internal variables x1 and x2 are created.

DELIMITER is used to modify the DELIMITER.

DELIMITER $ refers to changing the default DELIMITER ";" to "$", so that the stored procedure of multipart write can be executed as a whole, instead of being executed separately as multiple SQL statements.

After creation, change the separator ";"

Two ways to create variables and assign values

SET @ variable name = value;

SELECT value INTO @ variable name;

Run the variable assignment statement before using the variable.

You can also use "DECLARE variable name type (string type requires a range) DEFAULT value;" to create a variable, but the created variable can only be accessed within the process

There are only three types of stored procedures: IN, OUT, and INOUT.

During the call process, several parameters are passed, but IN-type parameters can be passed variables or constants, while OUT and INOUT parameters must be passed variables.

The value of the inout parameter changes externally as it changes during the process. the external value of the variable given to the in parameter is not affected by the change of the variable value in the process.

*/DELIMITER $

Drop procedure if exists 'p4 '$

Create procedure 'p4 '(IN s1 INT, OUT s2 INT, INOUT s3 VARCHAR (10))

BEGIN

DECLARE x1 VARCHAR (10) DEFAULT 'this is x1 ';

DECLARE x2 VARCHAR (10) DEFAULT 'this is x2 ';

SET s1 =11;

SET s2 =22;

SET s3 = 'iss3 ';

/* If syntax */

IF s1 =11AND s2 =12THEN

SELECT s1, s2;

End if;

IF s3 = 's3' OR s1 = s2 THEN

SELECT s3;

ELSE

SELECT s1, s2, s3;

End if;

/* Case syntax */

CASE s3

WHEN 'S1' THEN

SELECT 'this is S1 ';

WHEN 'S2' THEN

SELECT 'this is S2 ';

ELSE

SELECT 'this is s3 ';

End case;

/* While loop */

WHILE s1>1DO

SET s1 = s1-1;

End while;

SELECT s1;

/* Repeat statement

Unlike while, when the while condition is met, the repeat is always executed until the condition is met.

*/REPEAT

SET s1 = s1-1;

UNTIL s1 =1

End repeat;

SELECT s1;

/* LOOP

LOOP has no cyclic conditions and will keep repeating until "LEAVE ZiDingYi;" "ZiDingYi" is a custom LOOP mark */

ZiDingYi: LOOP

SET s1 = s1 +1;

IF s1 =5THENLEAVE ZiDingYi;

End if;

End loop;

SELECT s1;

END $

DELIMITER;

SET @ p_in =1;

SET @ p_out =2;

SET @ p_inout = 's3 ';

SELECT 'Hello world1' INTO @ p_4;

/* Call the stored procedure */

CALL p4 (@ p_in, @ p_out, @ p_inout );

SELECT @ p_in, @ p_out, @ p_inout, @ p_4;

/* If the stored procedure has only one statement, the statement does not need to be surrounded by begin... end.

External variables can be directly used in stored procedures.

The variables defined by set and select in the stored procedure are global. after the process is executed, the variables defined by set and select can be accessed externally. Other procedures can also be directly used */

Create procedure p1 () SET @ var = 'p1 ';

Create procedure p2 () select concat ('last procedure was', @ var );

CALL p1 ();

CALL p2 ();

SELECT @ var;

Create procedure p3 () select concat (@ p3_var, 'World ');

SET @ p3_var = 'hello ';

CALL p3 ();

/* Delete the stored procedure */

Drop procedure p4;

/* Or */

Drop procedure if exists 'p4'

/* Check which stored procedures test in the database is the database name */

Select name from mysql. proc WHERE db = 'test ';

/* Or */show procedure status where db = 'test ';

/* View the stored PROCEDURE details, including the creation statement */show create procedure p4;

--Create a trigger(If the trigger in the table triggers the table, an error is reported. if the trigger is used to trigger other tables, an error is returned)

Create trigger zl_tri after insert on zl_table

For each ROW

Insert INTO zl_table1 (Count) VALUES (2222 );

--Display trigger

Show triggers [from database name]

--Delete trigger

Drop trigger database name. TRIGGER name;

--Set

MySql only supports Union (Union) set operations. it seems that it is only available after 4.0;
However, the intersection Intersect and difference set Except T are not implemented.

--Connection(Same as SQL syntax)

--Basic syntax(Similar to SQL syntax, here is a difference)

Select * from emp order by emp_sal desc limit 2; -- limit is equivalent to top

View the information about employees with a salary ranking of 2nd to 3rd:

Mysql> select * from emp order by emp_sal desc limit 1, 2;

Using the rand () sampling survey, two employees are randomly selected to view their data.

Mysql> select * from emp order by rand () limit 2;

BitsCN.com