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