Starting from MySQL 5, you get Stored Procedure in Mysql

What is a stored procedure: A stored procedure is simply a procedure that is stored on the database server like MySQL. In programming languages, you write procedures to execute a function/logic. You can write similar procedure in SQL and store it in the database. From the front end application you can just call the procedure to get the functionality. Usually, you send series of sqls to the databases to execute a logic. A stored procedure is better as it needs one single call. But not every logic can/should be implemented as stored procedures.

Sample stored procedure

CREATE PROCEDURE sp_hello()
SELECT ‘Hello World’;

It just creates a procedure. To run the procedure, you have to type

call sp_hello;

You will see ‘Hello World’ as output.

Stored procedures can also take parameters and return values. Both needs to be mentioned as parameter. In the parameter list we can declare a variable as IN, OUT, or INOUT parameter. You can use SQLs[insert,select] inside procedure to perform database operations. Also, you can use to set/unset session variables.

Session variable example

SET @X=100;
CREATE PROCEDURE sp_in(p VARCHAR(10))
SET @x = P;

call sp_in(1000);

You see 1000 in screen

A more practical stored procedure with multiple statementsmysql> DELIMITER |mysql> CREATE PROCEDURE sp_declare (P INT) -> BEGIN -> DECLARE x INT; -> DECLARE y INT DEFAULT 10; -> SET x = P*y; -> INSERT INTO sp1(id,txt) VALUES(x,HEX(‘DEF’)); -> END|Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;mysql> CALL sp_declare(4);

please, note the use of | and DELIMETER. They provide support so that you can use ; in your procedures.

Some stored procedure related SQL commands

SHOW PROCEDURE STATUSSHOW CREATE PROCEDURE HelloSELECT * FROM INFORMATION_SCHEMA.ROUTINES [Ansi standard]ThanksSayed

From: http://sitestree.com/?p=4729
Categories:5
Tags:
Post Data:2012-06-17 13:50:57

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

Mysql Resources:

First you need the latest/stable version of MySQL. Right now 6.0 is the latest version. Mysql has two different releases like:
community server[free]
enterprise server[commercial]

Note: MAXDB is the current full package with many features that may not be fully tested.

Download MySQL:

You can download MySQL from

http://dev.mysql.com/downloads/mysql/5.0.html

Check the lower portion of the webpage.

Working with Mysql:

After installation you can interact with MySQL server through command line[Best to become guru]. But you may also want to use GUI IDE to interact with. GUI tools can be downloaded from:

http://dev.mysql.com/downloads/gui-tools/5.0.html

GUI are of two types: to administer MySQL server. Used for backup, restore, security

Development: For creating Database and interact with database using tables, Queries, Stored procedures, triggers

Application Development:

You can build appications that use MySQL databases at the backend. You can use languages like PHP, Perl, Java, .net to interact with MySQL databases. Usually, the package for these languages contain a driver to provide the functionalities. Otherwise, you can download drivers from:

http://dev.mysql.com/downloads/connector/

for many different programming languages like Java, PHP, Perl and similar.

From: http://sitestree.com/?p=4728
Categories:5
Tags:
Post Data:2009-01-22 07:30:25

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

Cross-Join: All row by all row, ex: SELECT FROM ,

The Equi-Join or Inner Join:Syntax:
SELECT
FROM ,
WHERE (Table1.column = Table2.column)

SELECT cds.artist, cds.title, genres.genre
-> FROM cds, genres
-> WHERE (cds.genreID = genres.genreID);

The Left Join:Syntax:

SELECT
FROM
LEFT JOIN
ON Table1.column = Table2.column

Without adding a WHERE clause the Left Join produces the same results as the equi-join example above.
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID;

The USING Clause: You can use this if the columns you are carrying out the join on have the same name.
Syntax:

SELECT
FROM
LEFT JOIN
USING ()

UPDATE JOIN:

mysql> UPDATE cds, artists
-> SET
-> cds.title = ‘The Funkship Odyssey’,
-> artists.Artist = ‘George Clinton’
-> WHERE (artists.artistID = cds.artistID)
-> AND (cds.cdID = ‘2’);

UPDATE cds->LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> SET
-> cds.title = ‘A Funk Odyssey’,
-> artists.name = ‘Jamiroquai’
-> WHERE (cds.cdID = ‘2’);

DELETE Joins:mysql> DELETE cds
-> FROM cds, artists
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = ‘3’);

From: http://sitestree.com/?p=4722
Categories:5
Tags:
Post Data:2010-05-28 12:14:38

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

After installing mysql, we will need to remove test database and associated users and their permissions. We will need to use mysql database to remove associated users and permission.

–DROP DATABASE test;

–SELECT db.Host, db.Db, db.User, db.Select_priv -> FROM db WHERE (db.DB = “vworksDB”);

—SELECT db.User, db.Host, db.Db -> FROM db -> WHERE (db.Db LIKE ‘test%’);

—DELETE FROM db

-> WHERE (db.Db LIKE ‘test%’);

—

mysql> DELETE FROM db

-> WHERE (db.Host = “%”);

mysql> DELETE FROM db

-> WHERE (db.User = “”);

—SELECT user.Host, user.User

-> FROM user

-> WHERE ((user.Host = “%”) OR (user.User = “”));

—

DELETE FROM user -> WHERE ((user.Host = “%”) OR (user.User = “”));—

FLUSH PRIVILEGES;—

details: http://www.keithjbrown.co.uk/vworks/mysql/mysql_pA.php#secure From: http://sitestree.com/?p=4721
Categories:5
Tags:
Post Data:2007-02-12 07:44:59

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

Using MySQL, AdministrationWorkshop RequirementsYou should have access to the MySQL command line client software.Various different PRIVILEGES on the MySQL ServerIntroductionIn the other MySQL Virtual Workshops we have used commands that are pretty much applicable to anyone. This part of the MySQL series is aimed at giving a rudimentary understanding of managing a MySQL database server. As such the task covered here are not really about manipulating data or database structures, but the actual databases themselves.Creating a DatabaseIn order to create a database you need to have the PRIVILEGES- this may be because you are the root user or you (or you systems administrator) has created an admin user that has ALL PRIVILEGES over all databases. In these examples a user called ‘admin’ has been created precisely for this purpose. Creating a database is fairly straightforward.Logging InA reminder of how to start the MySQL Client Software, and as we are not concerned with manipulating just one database we don’t have to specify a database as part of our startup command.$ mysql -u -pEnter password:Create database commandNext we are ready to enter the very simple command to create a database which is:mysql> CREATE DATABASE ; Let’s imagine that we are going to create a ‘vworks’ database (those wishing to create a database for use with the VWs should use this). We would enter the command:mysql> CREATE DATABASE vworks; We can now check for the presence of this database by typing:mysql> SHOW DATABASES;+———–+| Database |+———–+| mysql || vworks |+———–+2 rows in set (0.06 sec)The other database listed (‘mysql’) is the internal database which MySQL uses to manage users, permissions etc. NOTE: Deleting or DROPing a database is similar to the DROP TABLE command issued in Part 4. e.g.DROP DATABASE Granting Privileges on the new databaseNow that we have created a database, we need to decide who gets to use it. This is done by granting permissions for a user to use the database. This has a simplified syntax of:GRANT ON TO [IDENTIFIED BY ] [WITH GRANT OPTION]Where the items in square brackets are optional. The most common use is to give ALL PRIVILEGES on a database to a local user who has to use a password to access the database (in this case vworks).mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@localhost -> IDENTIFIED BY ‘newpassword’; If you are creating a database for use with the rest of the Virtual Workshops you should use this statement, substituting your username and password of choice. There are some other options we will look at. To restrict the user to manipulating data (rather than table or database structures) the statement would be altered to:mysql> GRANT SELECT,INSERT,UPDATE,DELETE -> ON vworks.* -> TO newuser@localhost -> IDENTIFIED BY ‘newpassword’; So that the user can only change the data using SELECT,INSERT,UPDATE or DELETE statements. If you wished to give a non-local user permissions on the database (for use with remote clients) then you could designate an IP or host address from which the user can connect:mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@192.168.0.2 -> IDENTIFIED BY ‘newpassword’; Now a user on the machine ‘192.168.0.2’ can connect to the database. To allow a user to connect from anywhere you would use a wildcard ‘%’mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@’%’ -> IDENTIFIED BY ‘newpassword’;You could even decide that a user doesn’t need a password if connecting from a certain machine. mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@192.168.0.2But I think it is sometimes good to provide a password anyway. Finally we’ll look at the WITH GRANT OPTION condition. This allows the user to give others privileges to that database:mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@localhost -> IDENTIFIED BY ‘newpassword’ -> WITH GRANT OPTION; This would allow the user ‘newuser’ to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.mysql> GRANT SELECT,INSERT,UPDATE,DELETE -> ON vworks.* -> TO friend@localhost -> IDENTIFIED BY ‘friendpass’; The WITH GRANT OPTION usually signifies ownership although it is worth noting that no user can GRANT more privileges that they themselves possess.Revoking privilegesRevoking privileges is almost identical to granting them as you simply substitute RE VOKE…. FROM for GRANT….TO and omit any passwords or other options.For example to REVOKE the privileges assigned to a user called ‘badvworks’:mysql> REVOKE ALL PRIVILEGES -> ON vworks.* -> FROM badvworks@localhost; Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.mysql> REVOKE INSERT,UPDATE,DELETE -> ON vworks.* -> FROM badvworks@localhost; Backing Up DataThere are several methods we can use to backup data. We are going to look at a couple of utilities that come with MySQL: mysqlhotcopy and mysqldump. mysqlhotcopymysqlhotcopy is a command line utility written in Perl that backs up (to a location you specify) the files which make up a database. You could do this manually, but mysqlhotcopy has the advantage of combining several different commands that lock the tables etc to prevent data corruption. The syntax (as ever) first.$ mysqlhotcopy -u -p /backup/location/Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory – the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory. mysqldumpThis is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is$ mysqldump -u -p [

] > file.sqlSo for example to back up a ‘vworks’ database which may have been created by completing the workshops:$ mysqldump -u admin -p vworks > vworks.sqlAfter entering the password a ‘vworks.sql’ file should be created. When you look at this file you can actually see that the data and structures are stored as a series of SQL statements. e.g.:– MySQL dump 8.22—- Host: localhost Database: vworks———————————————————– Server version 3.23.52—- Table structure for table ‘artist’–CREATE TABLE artist ( artistID int(3) NOT NULL auto _increment, name varchar(20) default NULL, PRIMARY KEY (artistID)) TYPE=MyISAM;—- Dumping data for table ‘artist’–INSERT INTO artist VALUES (1,’Jamiroquai’);INSERT INTO artist VALUES (2,’Various’);INSERT INTO artist VALUES (3,’westlife’);INSERT INTO artist VALUES (4,’Various’);INSERT INTO artist VALUES (5,’Abba’);And so on for the other tables. We could also have chosen to output just one table from the database, for example the artist table:$ mysqldump -u admin -p vworks artist > artist.sqlWe could even dump all the databases out (providing we have the permissions).$ mysqldump -u admin -p –all-databases > alldb.sqlRestoring a DumpRestoring a dump depends on what you have actually dumped. For example to restore a database to a blank database (perhaps having transferred the sql file to another machine) it is fairly simple. $ mysql -u admin -p vworks < vworks.sql…or to add a non-existent table to a database…$ mysql -u admin -p vworks vworks.sqlThen restore like normal:$ mysql -u admin -p vworks vworksDB.sqlThis will create additional SQL statements at the start of each database that CREATEs the dumped database (checking first to see if it does indeed exist) then USEing that database to import the table data into.– Current Database: vworks–CREATE DATABASE /*!32312 IF NOT EXISTS*/ vworks;USE vworks;Again we can resort like normal, but of course this time we can omit the database name.$ mysql -u admin -p vworks.sqlThe second option is ‘-a’ or ‘-all’ (either will do). Which also optimises the dump by creating mysql specific CREATE statements that speeds up the restore:$ mysqldump -u admin -p –all vworks > vworks.sqlUsing mysqldump to copy databases.It is possible to combine a dump and a restore on one line by using a pipe ‘|’ to pass the output of the dump directly to mysql basically bypassing the file. This may initially seem a bit redundant, but we can use this method to copy a database to another server or even create a duplicate copy.For example to copy the ‘vworks’ database to a mysql server called ‘remote.server.com’:$ mysqldump -u admin -p –databases vworks | > mysql -u backup -p MyPassword -h remote.server.com Note: the”” at the end of the first line means you wish to contine the command on another line before executing it. You may

From: http://sitestree.com/?p=4720
Categories:5
Tags:
Post Data:2008-06-02 09:17:27

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

“Do not give up when you meet the first obstacle. Many a time there will be heated discussions. As long as there is trust, even with differing opinions, you know that you can keep at it until you reach a solution.”

“Make sure that you are not so tied to your ideas that you cannot see a better solution. Be able to work toward compromise.”

“If you are not in the right place to grow, you will die”

Reference:Fifty Stories The Ugly Truth About Managing People: 50 Must-Get-Right Management Challenges…and How to Really Handle Them by Ruth King

From: http://sitestree.com/?p=5036
Categories:51
Tags:
Post Data:2013-04-15 22:37:39

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

• Read the book: Fifty StoriesThe Ugly Truth About Managing People: 50 Must-Get-Right Management Challenges…and How to Really Handle Them by Ruth King
• First lesson: Take the responsibility/blame for your team [deal with the trouble maker in private], acknowledge the strength of each of your team members, be willing to learn from your team members

From: http://sitestree.com/?p=5006
Categories:51
Tags:
Post Data:2006-11-26 13:45:26

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

• Positive Attitude: Develop a Positive Attitude for Stress Relief

From: http://sitestree.com/?p=4947
Categories:51
Tags:
Post Data:2011-07-04 00:14:23

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>

• How to deal with people

From: http://sitestree.com/?p=4907
Categories:51
Tags:
Post Data:2009-09-28 05:18:20

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>