Select Random Records :

example :
    SELECT * FROM tbl__table ORDER BY RAND() LIMIT 12;

    SELECT id,email FROM User ORDER BY RAND() LIMIT 5;

Substring :

Syntax SUBSTRING(str,pos,len);
example :
    SELECT substring('MySQL Substring',7); // Substring

    SELECT substring(email,7,3) from User LIMIT 1;

Stored Procedure :

* A Stored Procedure is a segment of SQL code which is stored in the database and can be invoked later by a program, stored procedure.
* Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database. It runs faster than uncompiled SQL commands which are sent from application
* Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.
Syntax Create :
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...]
routine_body
Call :
CALL sp_name([proc_parameter[,...]])
example :
Create Procedure : 
DELIMITER //
CREATE DEFINER='parwesh'@'%' PROCEDURE proc_sel()
BEGIN
       select * from Yii__proc;
END //
# (or)
DROP PROCEDURE /*IF EXISTS*/ `proc_insert`//
CREATE DEFINER=`parwesh`@`%` PROCEDURE `proc_insert`()
BEGIN
       insert into Yii__proc (id,name,adds)values('','manas','bls');
END//
Call Procedure : 
CALL proc_sel()//
CALL proc_insert()//
* Declaring variables :
Syntax DECLARE variable_name datatype([size]) [DEFAULT default_value];
example :
DECLARE  var2 INT DEFAULT 0;
SET var2 = 10;
SELECT COUNT(*) INTO var2 FROM Yii__User; # assign the variable using query
SELECT VAR2; # shows value 0f var2 variable
Stored Procedure Parameters :
Syntax MODE param_name param_type(param_size)

You can pass parameters to Stored Procedure, which make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.

* IN         : This is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside stored procedure does not change parameter.

* OUT     : It indicates that stored procedure can change this parameter and pass back to the calling program.

* INOUT  : This mode is combined of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program.
example 1 :
Create Procedure : 
DELIMITER //
DROP PROCEDURE IF EXISTS `PARAM_MODE`//
CREATE DEFINER=`parwesh`@`%` PROCEDURE `PARAM_MODE`(IN FRM INT, OUT COUNT INT, INOUT MSG VARCHAR(20))
BEGIN
     SELECT FRM, COUNT, MSG;
     SELECT COUNT(*) INTO COUNT FROM Yii__User WHERE ID BETWEEN FRM AND 60 AND email LIKE concat('%',MSG,'%');
     SET MSG=CONCAT("FROM :",FRM,' above');
     SELECT FRM, COUNT, MSG;
END//
DELIMITER ;
example 2 :
Call above procedure from MySql : 
SET @MSG='mail',@COUNT=100;
call PARAM_MODE(50,@COUNT,@MSG);
SELECT @COUNT,@MSG;
example 3 :
Call above procedure from PHP : 
<?php
$mysqli = new mysqli('host_name', 'user_name', 'password', 'database_name');
/* check connection */
if (mysqli_connect_errno()) {
    print("Connect failed: \n". mysqli_connect_error());
    exit();
}
else 
     echo 'Connected successfully';

$query="SELECT @COUNT,@MSG;
SET @MSG='mail',@COUNT=100;
call PARAM_MODE(50,@COUNT,@MSG);
SELECT @COUNT,@MSG;";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                echo '<pre>';
                print_r($row);
            }
            $result->free(); //Frees the memory associated with a result
        }
        /* print divider */
        if ($mysqli->more_results()) {
            print("-----------\n");
        }
    } while ($mysqli->next_result());
}
?>