MySQL common knowledge

--The stored procedure and common procedure control grammar

/*The code is to create a named "P4" stored procedure and set the S1, S2, S3 two int a varchar parameter, can also be other data types, internal created x1, X2 two variables

DELIMITER is to modify the delimiter

DELIMITER $$Mean is the default delimiter ";" into "$$", can be stored procedure that write the segmentation is performed, rather than being a multiple SQL statements executed

Create process then changed back into a separator";"

The two variable is created and assignment way

SET @Variable name = value;

SELECT INTO @ variable name;

You must first run the variable assignment statements use variables

Internal process can also through the "DECLARE variable name type (string type and range of DEFAULT value);" to create variables, but so created variables only in the process of the internal access

Stored procedures are only three types of parameter IN,OUT,INOUT

When the procedure is called process has several parameters of several parameters, only the parameters of IN can be transmitted is variable, can be a constant, and the parameters of OUT and INOUT type must be a variable.

To out, inout parameter value will change with the change in the external in the process, and to the in parameter value is not affected by the impact of external variables within the process variable values change

*/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 grammar*/

IF s1 = 11 AND s2 = 12 THEN

SELECT s1,s2;

END IF;

IF s3 = 's3' OR s1 = s2 THEN

SELECT s3;

ELSE

SELECT s1,s2,s3;

END IF;

/*Case grammar*/

CASE s3

WHEN 's1' THEN

SELECT 'this is s1';

WHEN 's2' THEN

SELECT 'this is s2';

ELSE

SELECT 'this is s3';

END CASE;

/*While cycle*/

WHILE s1>1 DO

SET s1=s1-1;

END WHILE;

SELECT s1;

/*The repeat loop

Unlike the while while satisfies the condition is the executive, repeat always execute until it meets the termination conditions

*/REPEAT

SET s1 = s1-1;

UNTIL s1=1

END REPEAT;

SELECT s1;

/*LOOP cycle

LOOP no cycle condition, the loop until will keep encountering the "LEAVE ZiDingYi"; "ZiDingYi" is a custom LOOP tag*/

ZiDingYi:LOOP

SET s1 = s1+1;

IF s1 = 5 THENLEAVE 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;

/*Calling a stored procedure*/

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

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

/*The stored procedure if only one sentence statement is not surrounded by begin... End

Stored procedures can be used directly to external variables defined

The stored procedure with set and select defined variable is global, the execution of the process inside set and select variables defined outside access, other processes also can be used directly*/

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 a stored procedure*/

DROP PROCEDURE p4;

/*Or*/

DROP PROCEDURE IF EXISTS `p4`

/*View the database which the stored procedure test to the database name*/

SELECT NAME FROM mysql.proc WHERE db='test';

/*Or*/SHOW PROCEDURE STATUS WHERE db='test';

/*The stored procedure detailed information, including the creation of statement*/SHOW CREATE PROCEDURE p4;

--Create trigger(inside and outside the trigger is triggered if the table, will error; triggering other table can)

create trigger zl_tri after insert on zl_table

for each ROW

insert INTO zl_table1(Count) VALUES(2222);

--Display trigger

Show triggers [from library.

--The delete trigger

The DROP TRIGGER library. Trigger name;

--Set

MySql only supports Union (Union) set operation, it seems only after some 4,
But for the intersection of Intersect, Except difference sets, no realization of the.

--Connect(like the SQL syntax)

--Basic grammar(basic and SQL syntax, it is not the same place)

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

View the salary rank second to third personnel records.:

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

Using rand () sampling survey, 2 employees were randomly selected from the data, see

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

Posted by Archer at December 19, 2013 - 7:39 PM