MYSQL queries and stored procedures

MYSQL – REFERENCE

1.    Creating database

CREATE DATABASE `Aequor` ;

2.    Re naming the database

CREATE DATABASE `aequor_test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

DROP DATABASE `Aequor` ;

3.    Creating new table
            CREATE TABLE `aequor`.`table1` (

`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 50 ) NOT NULL ,
`created_date` DATE NOT NULL ,
`modified_date` DATE NOT NULL ,
`status` INT NOT NULL DEFAULT '0',
PRIMARY KEY ( `id` )

) ENGINE = MYISAM

4.    Renaming the table

RENAME TABLE `aequor`.`table1` TO `aequor`.`table` ;

5.    Adding one more columns to existing table

ALTER TABLE `table1` ADD `mobile_no` DOUBLE NOT NULL AFTER `name` ;

6.    Adding more than one column to existing table

ALTER TABLE `table1` ADD `test_column1` VARCHAR( 20 ) NOT NULL AFTER `status` ,
ADD `test_column2` VARCHAR( 10 ) NOT NULL AFTER `test_column1` ;

7.    Removing one column from existing table

ALTER TABLE `table1` DROP `mobile_no`;

8.    Removing more than one column from existing table

ALTER TABLE `table1`
  DROP `test_column1`,
  DROP `test_column2`;

9.    Renaming the column name

ALTER TABLE `table1` CHANGE `name` `user_name` VARCHAR( 50 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

10.  Alter the column data type

ALTER TABLE `table1` CHANGE `user_name` `user_name` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

ALTER TABLE `table1` CHANGE `user_name` `user_name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

11.  Removing PRIMARY KEY of a table

ALTER TABLE `table1` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`)

12.  Adding PRIMARY KEY for a table

ALTER TABLE `table1` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`)

13.  Adding UNIQUE key

ALTER TABLE `table1` ADD UNIQUE (`id`)

14.  Removing the UNIQUE key

ALTER TABLE `table1` DROP INDEX `id`

15.  Adding INDEX to the table

ALTER TABLE `table1` ADD INDEX ( `id` )

16.  Removing the INDEX

ALTER TABLE `table1` DROP INDEX `id`

17.  Mysql query optimization

Use "Explain" keyword to check the query runnning status

Ex: Explain select column_name from table_name where column_name = value

18.  Inserting values into the table

INSERT INTO `aequor`.`table1` (

`id` ,
`user_name` ,
`mobile_no` ,
`created_date` ,
`modified_date` ,
`status` ,
`test_column1` ,
`test_column2`

)
VALUES (NULL , 'samy', '9790741151', '2012-04-13', '2012-04-14', '1', 'test', 'test');

19.  Updating the values

UPDATE `aequor`.`table1` SET `user_name` = 'rathinasamy' WHERE `table1`.`id` =1  LIMIT 1 ;

20.  Updating the multi values in table

UPDATE `aequor`.`table1` SET `mobile_no` = '9283331451',
`created_date` = '2012-04-15',
`modified_date` = '2012-04-16' WHERE `table1`.`id` =1 LIMIT 1 ;

21.  Deleting the multi rows in table

DELETE FROM `table1` WHERE `table1`.`id` = 1 LIMIT 1;
DELETE FROM `table1` WHERE `table1`.`id` = 2 LIMIT 1;

22.  MYSQL Joins

-    Inner join

-    Outer join

o    Left outer join

o    Right outer join

-    Full join

-    Self join

-    Cross join

23.  Stored procedure

"Select" Stored Procedure using the PREPARE statement

create table team (team_id INT(10), sport_id INT(10),team_type_id INT(10),team_name VARCHAR(100

), team_description VARCHAR(250), insert_by VARCHAR(50),created_date DATETIME, modified_date DATETIME)

INSERT INTO team VALUES(1,1,1,'Chennai Super kings','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'Mumbai','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'Pune','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'cochin','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'Miyanmar','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'Tamilnadu','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'Resxter','test',1,'2012-4-13','2012-4-13') ;

INSERT INTO team VALUES(1,1,1,'deospen','test',1,'2012-4-13','2012-4-13') ;

SELECT * FROM teams_get

RENAME TABLE teams_get TO `team` ;

call teams_get ('team_name', 4, 1);

/*call teams_get ('team_name', NO OF RECORDS, OFFSET); */

Stored procedure

/*"Select" Stored Procedure using the PREPARE statement */

-----------------------------------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`teams_get`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `teams_get`(IN orderby TEXT, IN row_count INT, IN off_set INT)

BEGIN

                        SET @s = CONCAT('SELECT team_id,

                        team_name,

                        team_description

                        FROM team

                        ORDER BY ', orderby,

                        ' LIMIT ', row_count, ' OFFSET ', off_set);

                        PREPARE stmnt FROM @s;

                        EXECUTE stmnt;

                        DEALLOCATE PREPARE stmnt;

    END$$

DELIMITER ;

------------------------------------------------------------------------------------------------------

Usinf if else and calling stored procedure itself

DELIMITER $$

DROP PROCEDURE IF EXISTS `sportszone`.`load_team_member_wrapper` $$

CREATE PROCEDURE `sportszone`.`load_team_member_wrapper` (IN tm_nm VARCHAR(100))

BEGIN

            DECLARE tm_id INT;

            DECLARE usr_nm VARCHAR(100);

            DECLARE today_dt DATETIME;

            SET today_dt = now();

            SET usr_nm = (select substring(user(), 1, 4));

            SET tm_id = (Select team_id from team where team_name = tm_nm);

            IF (select s.sport_name

                        from sport s

                        join team t

                        on s.sport_id = t.sport_id

                        where t.team_id = tm_id) = 'Baseball'

            THEN

                                    CALL team_member_baseball_set (tm_id, usr_nm, today_dt);

            ELSEIF (select s.sport_name

                        from sport s

                        join team t

                        on s.sport_id = t.sport_id

                        where t.team_id = tm_id) = 'Football' THEN

                                    CALL team_member_football_set (tm_id, usr_nm, today_dt);

            END IF;

END $$

DELIMITER ;

-------------------------------------------------------------------------------------------

"Select" Stored Procedure using the PREPARE statement

DELIMITER $$
DROP PROCEDURE IF EXISTS `team_get` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_get`(IN tm_id INT)
BEGIN

Select
t.team_name,
t.team_abbreviation,
t.team_description,
tt.team_type_name,
s.sport_name
FROM team t
join team_type tt
on t.team_type_id = tt.team_type_id
join sport s
on t.sport_id = s.sport_id
WHERE t.team_id = tm_id;
END $$
DELIMITER ;

---------------------------------------------------------------------

"Select" Stored Procedure using two Input Variables 

DELIMITER $$
DROP PROCEDURE IF EXISTS `team_type_by_sport_count`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_type_by_sport_count`(IN tm_typ_id INT, IN sprt_id INT )
BEGIN

SELECT COUNT(*)
FROM team
where team_type_id = tm_typ_id
and sport_id = sprt_id;

END $$
DELIMITER ;

--------------------------------------------------------------------

Using MySQL Stored Procedures with PHP mysql/mysqli/pdo

Wondering how to use stored procedures with PHP and MySQL? So was I and here’s what I’ve learned. In this tutorial I’ll explain how to use PHP (I’m using 5.2.6) to call MySQL (I’m using 5.0.2) stored procedures using the following database extensions:

• MySQL - http://us.php.net/manual/en/book.mysql.php
• MySQLi - http://uk2.php.net/manual/en/class.mysqli.php
• PDO - http://us.php.net/manual/en/class.pdo.php

First we need to setup our enviroment which consists of a new database with one table and two stored procedures. In your db tool of choice (I’ll be using the MySQL Query Browser) create a new database named test. After you create the new database, make sure to add a user called example with password example to the database and give it read access.

CREATE DATABASE `test`;

Now create the table users:

DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE  `test`.`users` (
`users_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(100) NOT NULL,
`last_name` VARCHAR(100) NOT NULL,
PRIMARY KEY  (`users_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

Before we create the stored procedures, lets put some dummy data in the users table. To do that just run the following query:

INSERT INTO `test`.`users` VALUES (NULL, ‘Joey’, ‘Rivera’), (NULL, ‘John’, ‘Doe’);

Next create the first stored procedure get_user:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_user`$$
CREATE PROCEDURE  `test`.`get_user`
(
IN userId INT,
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100)
)
BEGIN
SELECT first_name, last_name
INTO firstName, lastName
FROM users
WHERE users_id = userId;
END $$
DELIMITER ;

Finally create the second and last stored procedure get_users:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users`$$
CREATE PROCEDURE  `test`.`get_users`()
BEGIN
SELECT *
FROM users;
END $$
DELIMITER ;

If you understand the sql above, skip this section. The first script we ran to create a database is pretty self explanitory. The second script will delete the table users if it’s already in your database then it will recreate it. The table will consist of three fields: users_id, first_name, and last_name. The insert script will create two users: ‘Joey Rivera’ and ‘John Doe’. 

If stored procedures are new to you, don’t worry. They aren’t that complicated once you start playing with them. When looking at the code for the first stored procedure, drop procedure works the same way as dropping a table. First you want to check if the stored procedure is there and deleted before you recreate it. Create does just that, create the stored procedure in the database. get_user has three parameters: userId, firstName, and lastName. IN means when this stored procedure is called, this variable should be passed with a value. OUT means after the stored procedure executes, it will set the OUT variables with a value that can then be retrieved. You can also have INOUT variables but we don’t need them for this example.

The blulk of the code for the stored procedure goes in the BEGIN to END block. get_user is selecting the first and last name fields from the table users where the user id is equal to the userId variable being passed in. The other thing happening here is the two OUT variables are getting the values retrieved from the select statement. Variable firstName is set to the field first_name and lastName is being set to last_name. That’s it for get_user. get_users doesn’t have any IN nor OUT variables. When that stored procedure is executed it will return a recordset instead of variables. 

Now that we have our environment set, we are ready to start our tests. Depending on what you are trying to achieve, you may be using mysql, mysqli, or PDO. I’m going to run the same tests with all three to show you the difference as well as the limitation of mysql compared to mysqli and PDO. One of the tests I’ll be running doesn’t work with mysql while all the tests work with mysqli and PDO. 

The three tests will be:

1.    A simple select statement

2.    Calling stored procedure passing IN variable and retrieve OUT variables – get_user

3.    Calling stored procedure with no parameters and returns a recordset – get_users

Below is the code to run all three tests with each of the database extensions:

<?php
// MYSQL
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
mysql_select_db(‘test’, $mysql);

print ‘<h3>MYSQL: simple select</h3>’;
$rs = mysql_query( ‘SELECT * FROM users;’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

print ‘<h3>MYSQL: calling sp with out variables</h3>’;
$rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
$rs = mysql_query( ‘SELECT @first, @last’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

print ‘<h3>MYSQL: calling sp returning a recordset – doesn\’t work</h3>’;
$rs = mysql_query( ‘CALL get_users()’ );
while($row = mysql_fetch_assoc($rs))
{
debug($row);
}

// MYSQLI
$mysqli = new mysqli(‘localhost’, ‘example’, ‘example’, ‘test’);

print ‘<h3>MYSQLI: simple select</h3>’;
$rs = $mysqli->query( ‘SELECT * FROM users;’ );
while($row = $rs->fetch_object())
{
debug($row);
}

print ‘<h3>MYSQLI: calling sp with out variables</h3>’;
$rs = $mysqli->query( ‘CALL get_user(1, @first, @last)’ );
$rs = $mysqli->query( ‘SELECT @first, @last’ );
while($row = $rs->fetch_object())
{
debug($row);
}

print ‘<h3>MYSQLI: calling sp returning a recordset</h3>’;
$rs = $mysqli->query( ‘CALL get_users()’ );
while($row = $rs->fetch_object())
{
debug($row);
}

// PDO
$pdo = new PDO(‘mysql:dbname=test;host=127.0.0.1′, ‘example’, ‘example’);

print ‘<h3>PDO: simple select</h3>’;
foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
{
debug($row);
}

print ‘<h3>PDO: calling sp with out variables</h3>’;
$pdo->query( ‘CALL get_user(1, @first, @last)’ );
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
{
debug($row);
}

print ‘<h3>PDO: calling sp returning a recordset</h3>’;
foreach($pdo->query( ‘CALL get_users()’ ) as $row)
{
debug($row);
}

function debug($o)
{
print ‘<pre>’;
print_r($o);
print ‘</pre>’;
}
?>

When you run this code you get the following results:

MYSQL: simple select
Array
(
    [users_id] => 1
    [first_name] => Joey
    [last_name] => Rivera
)
Array
(
    [users_id] => 2
    [first_name] => John
    [last_name] => Doe
)

MYSQL: calling sp with out variables
Array
(
    [@first] => Joey
    [@last] => Rivera
)

MYSQL: calling sp returning a recordset – doesn‘t work
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***test.php on line 24

MYSQLI: simple select
stdClass Object
(
    [users_id] => 1
    [first_name] => Joey
    [last_name] => Rivera
)
stdClass Object
(
    [users_id] => 2
    [first_name] => John
    [last_name] => Doe
)

MYSQLI: calling sp with out variables
stdClass Object
(
    [@first] => Joey
    [@last] => Rivera
)

MYSQLI: calling sp returning a recordset
stdClass Object
(
    [users_id] => 1
    [first_name] => Joey
    [last_name] => Rivera
)
stdClass Object
(
    [users_id] => 2
    [first_name] => John
    [last_name] => Doe
)

PDO: simple select
Array
(
    [users_id] => 1
    [0] => 1
    [first_name] => Joey
    [1] => Joey
    [last_name] => Rivera
    [2] => Rivera
)
Array
(
    [users_id] => 2
    [0] => 2
    [first_name] => John
    [1] => John
    [last_name] => Doe
    [2] => Doe
)

PDO: calling sp with out variables
Array
(
    [@first] => Joey
    [0] => Joey
    [@last] => Rivera
    [1] => Rivera
)

PDO: calling sp returning a recordset
Array
(
    [users_id] => 1
    [0] => 1
    [first_name] => Joey
    [1] => Joey
    [last_name] => Rivera
    [2] => Rivera
)
Array
(
    [users_id] => 2
    [0] => 2
    [first_name] => John
    [1] => John
    [last_name] => Doe
    [2] => Doe
)

------------------------------------------------------------------------------------------------------------------

24.  Function to calculate average for static number of values

DELIMITER $$
DROP FUNCTION IF EXISTS `sportszone`.`get_average` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_average`(stat1 INT, stat2 INT, stat3 INT, stat4 INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE stat_avg INT;
SET stat_avg = (stat1+stat2+stat3+stat4)/4;
RETURN stat_avg;
END $$
DELIMITER ;

25.Triggers

TRIGGERS

A Trigger is a named database object which defines some action that the database should take when some databases related event occurs. Triggers are executed when you issues a data manipulation command like INSERT, DELETE, UPDATE on a table for which the trigger has been created. They are automatically executed and also transparent to the user. But for creating the trigger the user must have the CREATE TRIGGER privilege. In this section we will describe you about the syntax to create and drop the triggers and describe you some examples of how to use them.

CREATE TRIGGER

The general syntax of CREATE TRIGGER is :
  CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement

By using above statement we can create the new trigger. The trigger can associate only with the table name and that must be refer to a permanent table. Trigger_time means trigger action time. It can be BEFORE or AFTER. It is used to define that the trigger fires before or after the statement that executed it. Trigger_event specifies the statement that executes the trigger. The trigger_event can be any of the DML Statement : INSERT, UPDATE, DELETE.

We can not have the two trigger for a given table, which have the same trigger action time and event. For Instance : we cannot have two BEFORE INSERT triggers for same table. But we can have a BEFORE INSERT and BEFORE UPDATE trigger for a same table.

Trigger_statement have the statement that executes when the trigger fires but if you want to execute multiple statement the you have to use the BEGIN?END compound statement.

We can refer the columns of the table that associated with trigger by using the OLD and NEW keyword. OLD.column_name is used to refer the column of an existing row before it is deleted or updated and NEW.column_name is used to refer the column of a new row that is inserted or after updated existing row.

In INSERT trigger we can use only NEW.column_name because there is no old row and in a DELETE trigger we can use only OLD.column_name because there is no new row. But in UPDATE trigger we can use both, OLD.column_name is used to refer the columns of a row before it is updated and NEW.Column_name is used to refer the column of the row after it is updated.

In the following example we are updating the Salary column of Employee table before inserting any record in Emp table. Example :

mysql> SELECT * FROM Employee; +-----+---------+----------+-------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+-------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10300 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 | | 3 | Chandan | Banglore | Team Leader | 15450 | 999 | | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 20000 | 658 | +-----+---------+----------+-------------------+--------+-------+ 7 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE TRIGGER ins_trig BEFORE INSERT ON Emp -> FOR EACH ROW -> BEGIN -> UPDATE Employee SET Salary=Salary-300 WHERE Perks>500; -> END; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,658); Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM Employee; +-----+---------+----------+-------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+-------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10000 | 853 | | 3 | Chandan | Banglore | Team Leader | 15150 | 999 | | 5 | Tapan | Pune | Developer | 20300 | 1111 | | 6 | Amar | Chennai | Developer | 15700 | 1124 | | 7 | Santosh | Delhi | Designer | 9700 | 865 | | 8 | Suman | Pune | Web Designer | 19700 | 658 | +-----+---------+----------+-------------------+--------+-------+ 7 rows in set (0.00 sec)

In the following example we are modifying the salary of Employee table before updating the record of the same table. Example :

mysql> delimiter // mysql> CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee -> FOR EACH ROW -> BEGIN -> IF NEW.Salary<=500 THEN -> SET NEW.Salary=10000; -> ELSEIF NEW.Salary>500 THEN -> SET NEW.Salary=15000; -> END IF; -> END -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> UPDATE Employee -> SET Salary=500; Query OK, 5 rows affected (0.04 sec) Rows matched: 7 Changed: 5 Warnings: 0 mysql> SELECT * FROM Employee; +-----+---------+----------+-------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+-------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10000 | 853 | | 3 | Chandan | Banglore | Team Leader | 10000 | 999 | | 5 | Tapan | Pune | Developer | 10000 | 1111 | | 6 | Amar | Chennai | Developer | 10000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 10000 | 658 | +-----+---------+----------+-------------------+--------+-------+ 7 rows in set (0.00 sec) mysql> UPDATE Employee -> SET Salary=1500; Query OK, 7 rows affected (0.03 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM Employee; +-----+---------+----------+-------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+-------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 15000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 15000 | 853 | | 3 | Chandan | Banglore | Team Leader | 15000 | 999 | | 5 | Tapan | Pune | Developer | 15000 | 1111 | | 6 | Amar | Chennai | Developer | 15000 | 1124 | | 7 | Santosh | Delhi | Designer | 15000 | 865 | | 8 | Suman | Pune | Web Designer | 15000 | 658 | +-----+---------+----------+-------------------+--------+-------+ 7 rows in set (0.01 sec)

DROP TRIGGER

The general syntax of DROP TRIGGER is :
  DROP TRIGGER trigger_name

This statement is used to drop a trigger. Example of Dropping the Trigger :

mysql> DROP TRIGGER updtrigger; Query OK, 0 rows affected (0.02 sec)