/
Gists
/
MySql
Gists - MySql
On gists
table.sql
•
MySql
table.sql #
League table
from the Artful Common Queries page
Here is a simple soccer league table setup that was developed in the MySQL Forum by J Williams and a contributor named "Laptop Alias". The teams table tracks team ID and name, the games table tracks home and away team IDs and goal totals for each game. The query for standings is built by aggregating a UNION of home team and away team game results:
DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(
id int primary key auto_increment,
tname char(32)
);
CREATE TABLE games(
id int primary key auto_increment,
date datetime,
hteam int,
ateam int,
hscore tinyint,
ascore tinyint
);
INSERT INTO teams VALUES
(1,'Wanderers'),(2,'Spurs'),
(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),
(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),
(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname |
+----+-----------+
| 1 | Wanderers |
| 2 | Spurs |
| 3 | Celtics |
| 4 | Saxons |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
| 1 | 2008-01-01 20:00:00 | 1 | 2 | 1 | 0 |
| 2 | 2008-01-01 20:00:00 | 3 | 4 | 0 | 2 |
| 3 | 2008-01-08 20:00:00 | 1 | 3 | 1 | 1 |
| 4 | 2008-01-08 20:00:00 | 2 | 4 | 2 | 1 |
+----+---------------------+-------+-------+--------+--------+
-- Standings query:
SELECT
tname AS Team, Sum(P) AS P,
Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,
SUM(Pts) AS Pts
FROM(
SELECT
hteam Team,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE
WHEN hscore > ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END PTS
FROM games
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE
WHEN hscore < ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team | P | W | D | L | F | A | GD | Pts |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers | 2 | 1 | 1 | 0 | 2 | 1 | 1 | 4 |
| Spurs | 2 | 1 | 0 | 1 | 2 | 2 | 0 | 3 |
| Saxons | 2 | 1 | 0 | 1 | 3 | 2 | 1 | 3 |
| Celtics | 2 | 0 | 1 | 1 | 1 | 3 | -2 | 1 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2009
On gists
MySql
MySql - advanced
League standings
table.sql #
DROP TABLE IF EXISTS teams, games;
CREATE TABLE teams(
id int primary key auto_increment,
tname char(32)
);
CREATE TABLE games(
id int primary key auto_increment,
date datetime,
hteam int,
ateam int,
hscore tinyint,
ascore tinyint
);
INSERT INTO teams VALUES
(1,'Wanderers'),(2,'Spurs'),
(3,'Celtics'),(4,'Saxons');
INSERT INTO games VALUES
(1,'2008-1-1 20:00:00',1,2,1,0),
(2,'2008-1-1 20:00:00',3,4,0,2),
(3,'2008-1-8 20:00:00',1,3,1,1),
(4,'2008-1-8 20:00:00',2,4,2,1);
SELECT * FROM teams;
+----+-----------+
| id | tname |
+----+-----------+
| 1 | Wanderers |
| 2 | Spurs |
| 3 | Celtics |
| 4 | Saxons |
+----+-----------+
SELECT * FROM games;
+----+---------------------+-------+-------+--------+--------+
| id | date | hteam | ateam | hscore | ascore |
+----+---------------------+-------+-------+--------+--------+
| 1 | 2008-01-01 20:00:00 | 1 | 2 | 1 | 0 |
| 2 | 2008-01-01 20:00:00 | 3 | 4 | 0 | 2 |
| 3 | 2008-01-08 20:00:00 | 1 | 3 | 1 | 1 |
| 4 | 2008-01-08 20:00:00 | 2 | 4 | 2 | 1 |
+----+---------------------+-------+-------+--------+--------+
-- Standings query:
SELECT
tname AS Team, Sum(P) AS P,
Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,
SUM(Pts) AS Pts
FROM(
SELECT
hteam Team,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE
WHEN hscore > ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END PTS
FROM games
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE
WHEN hscore < ascore THEN 3
WHEN hscore = ascore THEN 1
ELSE 0
END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;
+-----------+------+------+------+------+------+------+------+------+
| Team | P | W | D | L | F | A | GD | Pts |
+-----------+------+------+------+------+------+------+------+------+
| Wanderers | 2 | 1 | 1 | 0 | 2 | 1 | 1 | 4 |
| Spurs | 2 | 1 | 0 | 1 | 2 | 2 | 0 | 3 |
| Saxons | 2 | 1 | 0 | 1 | 3 | 2 | 1 | 3 |
| Celtics | 2 | 0 | 1 | 1 | 1 | 3 | -2 | 1 |
+-----------+------+------+------+------+------+------+------+------+
Last updated 22 May 2009
-- OR
select
team,
count(*) played,
count(case when goalsfor > goalsagainst then 1 end) wins,
count(case when goalsagainst> goalsfor then 1 end) lost,
count(case when goalsfor = goalsagainst then 1 end) draws,
sum(goalsfor) goalsfor,
sum(goalsagainst) goalsagainst,
sum(goalsfor) - sum(goalsagainst) goal_diff,
sum(
case when goalsfor > goalsagainst then 3 else 0 end
+ case when goalsfor = goalsagainst then 1 else 0 end
) score
from (
select hometeam team, goalsfor, goalsagainst from scores
union all
select awayteam, goalsagainst, goalsfor from scores
) a
group by team
order by score desc, goal_diff desc;
On gists
Pivot table (2 zpusoby)
•
MySql
MySql - advanced
AW
pivot-table.sql #
-- https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=174158
-- Pivotni tabulka
-- Ja
SELECT user_id, (
SUM(IF (action_type = 'login', 1, 0))
) AS 'login', (
SUM(IF (action_type = 'publish', 1, 0))
) AS 'publish', (
SUM(IF (action_type = 'download', 1, 0))
) AS 'download', (
SUM(IF (action_type = 'upload', 1, 0))
) AS 'upload', (
SUM(IF (action_type = 'comment', 1, 0))
) AS 'comment',(
SUM(IF (action_type = 'share', 1, 0))
) AS 'share'
FROM ss_stats
GROUP BY user_id
HAVING user_id IS NOT NULL
-- Kajman
SELECT user_id,
( Sum(IF (action_type = 'login', pocet, 0)) ) AS 'login',
( Sum(IF (action_type = 'publish', pocet, 0)) ) AS 'publish',
( Sum(IF (action_type = 'download', pocet, 0)) ) AS 'download',
( Sum(IF (action_type = 'upload', pocet, 0)) ) AS 'upload',
( Sum(IF (action_type = 'comment', pocet, 0)) ) AS 'comment',
( Sum(IF (action_type = 'share', pocet, 0)) ) AS 'share'
FROM (SELECT user_id,
action_type,
Count(*) pocet
FROM ss_stats
GROUP BY action_type,
user_id) pocty
WHERE user_id IS NOT NULL
GROUP BY user_id
--- tttt; nejlepsi a nejrychlejsi
SELECT
user_id,
logins.count AS 'login',
publishes.count AS 'publish',
downloads.count AS 'download',
uploads.count AS 'upload',
"comments".count AS 'comment',
shares.count AS 'shares'
FROM
users
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'login' GROUP BY user_id) logins USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'publish' GROUP BY user_id) publishes USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'download' GROUP BY user_id) downloads USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'upload' GROUP BY user_id) uploads USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'comment' GROUP BY user_id) "comments" USING(user_id)
LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'share' GROUP BY user_id) shares USING(user_id)
On gists
tutorial-Store-Procedures-Mysql.sql
•
MySql
MySql - advanced
tutorial-Store-Procedures-Mysql. #
------Creating Stored Procedures in MySQL------
--Make sure you have version 5 of MySQL:
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.00 sec)
--First pick a database to use (a procedure, like a table, is associated with
--a single database.) For these examples, I will use a database that is populated
--with the tables from HW 2:
USE ozaidan_hw2;
--Next, change the delimiter, because we will use the semicolon WITHIN the
--procedure declarations, and therefore it cannot be the delimiter anymore:
DELIMITER //
--OK, let's get started. Creating procedures is straightforward:
CREATE PROCEDURE myFirstProc()
SELECT 'Hello World!' AS Output;
//
Query OK, 0 rows affected (0.00 sec)
--Whenever you create a procedure (successfully) you should get a 'Query OK' message.
--Calling a procedure is also straightforward:
CALL myFirstProc() //
+--------------+
| Output |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
--By the way, procedure names are NOT case sensitive:
CALL myfirstproc() //
+--------------+
| Output |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
--Another example:
CREATE PROCEDURE ListStudents()
SELECT *
FROM Student;
//
CALL ListStudents() //
+-------+----------+---------+------+------+-------+---------+-----------+
| StuID | LName | Fname | Age | Sex | Major | Advisor | city_code |
+-------+----------+---------+------+------+-------+---------+-----------+
| 1001 | Smith | Linda | 18 | F | 600 | 1121 | BAL |
| 1002 | Kim | Tracy | 19 | F | 600 | 7712 | HKG |
.
.
.
| 1034 | Epp | Eric | 18 | M | 50 | 5718 | BOS |
| 1035 | Schmidt | Sarah | 26 | F | 50 | 5718 | WAS |
+-------+----------+---------+------+------+-------+---------+-----------+
34 rows in set (0.00 sec)
--Say we only want student ID's and names. To update a procedure, we must
--first DROP it:
DROP PROCEDURE IF EXISTS ListStudents //
Query OK, 0 rows affected (0.00 sec)
--Again, whenever you drop a procedure, you should get a 'Query OK' message.
--From now on, we will always use "DROP PROCEDURE IF EXISTS procName" as
--a standard practice before declaring procedures:
DROP PROCEDURE IF EXISTS ListStudents //
CREATE PROCEDURE ListStudents()
SELECT StuID, LName, FName
FROM Student;
//
CALL ListStudents() //
+-------+----------+---------+
| StuID | LName | FName |
+-------+----------+---------+
| 1001 | Smith | Linda |
| 1002 | Kim | Tracy |
.
.
.
| 1034 | Epp | Eric |
| 1035 | Schmidt | Sarah |
+-------+----------+---------+
34 rows in set (0.00 sec)
--OK, let's use some parameters:
DROP PROCEDURE IF EXISTS sayHello //
CREATE PROCEDURE sayHello(IN name VARCHAR(20))
SELECT CONCAT('Hello ', name, '!') AS Greeting;
//
--The 'IN' keyword tells MySQL that is should be expecting an input value for
--the parameter......hunh? Why would a parameter NOT have an input value? You will
--see in a little bit. First, let's see if sayHello works:
CALL sayHello('Omar') //
+-------------+
| Greeting |
+-------------+
| Hello Omar! |
+-------------+
1 row in set (0.00 sec)
--Another example:
DROP PROCEDURE IF EXISTS saySomething //
CREATE PROCEDURE saySomething(IN phrase VARCHAR(20), IN name VARCHAR(20))
SELECT CONCAT(phrase, ' ', name, '!') AS Output;
//
CALL saySomething('Go','Blue Jays') //
CALL saySomething('Do','my homework') //
+---------------+
| Output |
+---------------+
| Go Blue Jays! |
+---------------+
1 row in set (0.00 sec)
+-----------------+
| Output |
+-----------------+
| Do my homework! |
+-----------------+
1 row in set (0.00 sec)
--and another one:
DROP PROCEDURE IF EXISTS FindStudent //
CREATE PROCEDURE FindStudent(IN id INT)
SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
FROM Student
WHERE StuID = id;
//
CALL FindStudent(1001) //
+-------+--------------+
| StuID | Student Name |
+-------+--------------+
| 1001 | Linda Smith |
+-------+--------------+
1 row in set (0.00 sec)
--and yet another:
DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
SET sum = x + y;
SET product = x * y;
//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ...
--Well, that wasn't good. The reason is, we must use BEGIN/END if we have
--a compound statement:
DROP PROCEDURE IF EXISTS calculate //
CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)
BEGIN
SET sum = x + y;
SET product = x * y;
END;
//
--Did you notice the 'OUT' keyword for sum and product? This tells MySQL that those
--two parameters are not 'input' parameters but are 'output' parameters instead.
--Now, when calling the procedure, we need to provide four parameters: two input
--values, and two MySQL *variables* where the results will be stored:
CALL calculate(4,5,@s,@p) //
Query OK, 0 rows affected (0.00 sec)
--Here, @s and @p are MySQL variables. Notice that they start with @, although
--procedure *parameters* do not start with @
SELECT @s //
SELECT @p //
+------+
| @s |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
+------+
| @p |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
--Note: you can also have INOUT parameters, which serve as both input and output
--parameters.
--OK, let's do some interesting stuff. First off, flow control:
DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN
IF x > 0 THEN
SELECT x AS Number, '+' AS Sign;
ELSEIF x < 0 THEN
SELECT x AS Number, '-' AS Sign;
ELSE
SELECT x AS Number, 'Zero' AS Sign;
END IF;
END;
//
CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //
+--------+------+
| Number | Sign |
+--------+------+
| 2 | + |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| -5 | - |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| 0 | Zero |
+--------+------+
1 row in set (0.00 sec)
--Before we get any further, let's introduce variables:
DROP PROCEDURE IF EXISTS mySign //
CREATE PROCEDURE mySign(IN x INT)
BEGIN
DECLARE result VARCHAR(20);
IF x > 0 THEN
SET result = '+';
ELSEIF x < 0 THEN
SET result = '-';
ELSE
SET result = 'Zero';
END IF;
SELECT x AS Number, result AS Sign;
END;
//
CALL mySign(2) //
CALL mySign(-5) //
CALL mySign(0) //
+--------+------+
| Number | Sign |
+--------+------+
| 2 | + |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| -5 | - |
+--------+------+
1 row in set (0.00 sec)
+--------+------+
| Number | Sign |
+--------+------+
| 0 | Zero |
+--------+------+
1 row in set (0.00 sec)
--Using CASE:
DROP PROCEDURE IF EXISTS digitName //
CREATE PROCEDURE digitName(IN x INT)
BEGIN
DECLARE result VARCHAR(20);
CASE x
WHEN 0 THEN SET result = 'Zero';
WHEN 1 THEN SET result = 'One';
WHEN 2 THEN SET result = 'Two';
WHEN 3 THEN SET result = 'Three';
WHEN 4 THEN SET result = 'Four';
WHEN 5 THEN SET result = 'Five';
WHEN 6 THEN SET result = 'Six';
WHEN 7 THEN SET result = 'Seven';
WHEN 8 THEN SET result = 'Eight';
WHEN 9 THEN SET result = 'Nine';
ELSE SET result = 'Not a digit';
END CASE;
SELECT x AS Digit, result AS Name;
END;
//
CALL digitName(0) //
CALL digitName(4) //
CALL digitName(100) //
+-------+------+
| Digit | Name |
+-------+------+
| 0 | Zero |
+-------+------+
1 row in set (0.00 sec)
+-------+------+
| Digit | Name |
+-------+------+
| 4 | Four |
+-------+------+
1 row in set (0.00 sec)
+-------+-------------+
| Digit | Name |
+-------+-------------+
| 100 | Not a digit |
+-------+-------------+
1 row in set (0.00 sec)
--As you'd expect, we have loops. For example, WHILE loops:
DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN
DECLARE result INT;
DECLARE i INT;
SET result = 1;
SET i = 1;
WHILE i <= x DO
SET result = result * i;
SET i = i + 1;
END WHILE;
SELECT x AS Number, result as Factorial;
END;
//
CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 1 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 2 | 2 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 4 | 24 |
+--------+-----------+
1 row in set (0.01 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 0 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
--There is also REPEAT/UNTIL loops:
DROP PROCEDURE IF EXISTS fact //
CREATE PROCEDURE fact(IN x INT)
BEGIN
DECLARE result INT DEFAULT 1; /* notice you can declare a variable*/
DECLARE i INT DEFAULT 1; /* and give it a value in one line */
REPEAT
SET result = result * i;
SET i = i + 1;
UNTIL i > x
END REPEAT;
SELECT x AS Number, result as Factorial;
END;
//
CALL fact(1) //
CALL fact(2) //
CALL fact(4) //
CALL fact(0) //
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 1 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 2 | 2 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 4 | 24 |
+--------+-----------+
1 row in set (0.00 sec)
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 0 | 1 |
+--------+-----------+
1 row in set (0.00 sec)
--OK, do you remember this?
/*
CREATE PROCEDURE FindStudent(IN id INT)
SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'
FROM Student
WHERE StuID = id;
//
*/
--What if we only want to extract the name without printing it out?
--Obviously, we need some OUT parameters. Still, how do you extract
--information into those OUT parameters?
--
--Answer: something called a CURSOR:
DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
DECLARE cur CURSOR FOR
SELECT FName, LName
FROM Student
WHERE StuID = id;
OPEN cur;
FETCH cur INTO fn, ln;
CLOSE cur;
END;
//
CALL FindName(1001,@f,@l) //
Query OK, 0 rows affected (0.00 sec)
--Remember that @f and @l are MySQL variables:
SELECT @f //
SELECT @l //
+-------+
| @f |
+-------+
| Linda |
+-------+
1 row in set (0.00 sec)
+-------+
| @l |
+-------+
| Smith |
+-------+
1 row in set (0.00 sec)
--What if we give an invalid student ID?
CALL FindName(0000,@f,@l) //
ERROR 1329 (02000): No data to FETCH
--MySQL complains, as expected. It would be nice to handle this more elegantly, however.
--We need an error HANDLER. Let's modify FindName:
DROP PROCEDURE IF EXISTS FindName //
CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))
BEGIN
DECLARE cur CURSOR FOR
SELECT FName, LName
FROM Student
WHERE StuID = id;
DECLARE EXIT HANDLER FOR NOT FOUND
SELECT 'Sorry; this ID was not found' AS 'Error Message';
OPEN cur;
FETCH cur INTO fn, ln;
CLOSE cur;
END;
//
CALL FindName(0000,@f,@l) //
+------------------------------+
| Error Message |
+------------------------------+
| Sorry; this ID was not found |
+------------------------------+
1 row in set (0.00 sec)
--Another use for handlers: multiple FETCH calls using a CONTINUE handler.
--
--In this case, we use a CONTINUE handler that, instead of exiting the procedure
--upon encountering a NOT FOUND error, simply sets a variable done = 1.
--
--Why would we do that? And how does that help us carry out multiple FETCH calls?
--
--Take a look at this procedure, which traverses all the entries of a table to
--find the maximum and minimum age:
DROP PROCEDURE IF EXISTS MaxMinAge //
CREATE PROCEDURE MaxMinAge(OUT maxAge INT, OUT minAge INT)
BEGIN
DECLARE currAge,maxSoFar,minSoFar,done INT;
DECLARE cur CURSOR FOR
SELECT Age
FROM Student;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
SET maxSoFar = 0;
SET minSoFar = 1000;
SET done = 0;
OPEN cur;
WHILE done = 0 DO
FETCH cur INTO currAge;
IF currAge > maxSoFar THEN
SET maxSoFar = currAge;
END IF;
IF currAge < minSoFar THEN
SET minSoFar = currAge;
END IF;
END WHILE;
CLOSE cur;
SET maxAge = maxSoFar;
SET minAge = minSoFar;
END;
//
CALL MaxMinAge(@max,@min) //
Query OK, 0 rows affected (0.00 sec)
SELECT @max //
SELECT @min //
+------+
| @max |
+------+
| 27 |
+------+
1 row in set (0.00 sec)
+------+
| @min |
+------+
| 16 |
+------+
1 row in set (0.00 sec)
--In summary, stored procedures in MySQL look like this:
DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(parameter list)
BEGIN
/* variable declarations */
/* CURSOR definitions */
/* declaring handlers */
/* procedure body...whatever you want it to do */
END;
//
--In more detail:
DROP PROCEDURE IF EXISTS procName //
CREATE PROCEDURE procName(IN/OUT/INOUT parName parType, ...)
BEGIN
/* variable declarations */
DECLARE varName,... varType;
/* e.g. DECLARE myName VARCHAR(20); DECLARE x,y,z INT; */
DECLARE varName varType DEFAULT value;
/* e.g. DECLARE x INT DEFAULT 0; */
/* CURSOR definitions */
DECLARE curName CURSOR FOR
SELECT ...
/* e.g. DECLARE cur1 CURSOR FOR
SELECT FName, LName
FROM Student; */
/* declaring handlers */
DECLARE EXIT/CONTINUE HANDLER FOR errorType/errorNumber
... action ...
/* e.g. DECLARE EXIT HANDLER FOR NOT FOUND
SELECT 'Sorry; this ID was not found' AS 'Error Message'; */
/* e.g. DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1; */
/* procedure body...whatever you want it to do */
/* IF statement */
IF cond1 THEN
action1
ELSEIF cond2 THEN
action2
ELSEIF cond3 THEN
action3
ELSE
elseaction
END IF;
/* e.g. IF x > 0 THEN
SET result = '+';
ELSEIF x < 0 THEN
SET result = '-';
ELSE
SET result = 'Zero';
END IF; */
/* CASE statement */
CASE varName
WHEN val1 THEN action1
WHEN val2 THEN action2
ELSE elseaction
END CASE;
/* e.g. CASE position
WHEN 1 THEN SET result = 'Gold Medal';
WHEN 2 THEN SET result = 'Silver Medal';
WHEN 3 THEN SET result = 'Bronze Medal';
ELSE SET result = 'No Medal!';
END CASE; */
/* WHILE loop */
WHILE cond DO
action1
action2
...
END WHILE
/* e.g. WHILE i < 5 DO
SET result = result + i;
SET i = i + 1;
END WHILE; */
/* REPEAT/UNTIL loop */
REPEAT
action1
action2
...
UNTIL cond
END REPEAT;
/* e.g. REPEAT
SET result = result + i;
SET i = i + 1;
UNTIL i >= 5
END REPEAT; */
/* using a CURSOR */
OPEN curName;
.
.
FETCH curName INTO var1, var2, ...;
.
.
CLOSE curName;
/* e.g. Assume cur1 has id's, first names, and last names
let's find the name of the student whose StuID is x:
OPEN cur1;
SET found = 0;
WHILE found = 0 DO
FETCH cur1 INTO nextID, nextFName, nextLName;
IF nextID = x THEN
SET result = CONCAT(nextFName, ' ', nextLName);
SET found = 1;
END IF;
END WHILE;
CLOSE cur1; */
END;
//
On gists
MySql
MySql tricks
MySql - advanced
Advanced CTE - Tree
tree.sql #
https://akki.ca/blog/mysql-adjacency-list-model-for-hierarchical-data-using-cte/
CREATE TABLE category (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
parent_id int(10) UNSIGNED DEFAULT NULL,
is_active int(1) UNSIGNED DEFAULT 1,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO category (name, parent_id) VALUES ("Electronics", null);
INSERT INTO category (name, parent_id) VALUES ("TV", 1);
INSERT INTO category (name, parent_id) VALUES ("Smart", 2);
INSERT INTO category (name, parent_id) VALUES ("4K Ultra HD", 2);
INSERT INTO category (name, parent_id) VALUES ("Curved", 2);
INSERT INTO category (name, parent_id) VALUES ("Camera", 1);
INSERT INTO category (name, parent_id) VALUES ("Computer", 1);
INSERT INTO category (name, parent_id) VALUES ("Desktop", 7);
INSERT INTO category (name, parent_id) VALUES ("Laptops", 7);
INSERT INTO category (name, parent_id) VALUES ("Work", 9);
INSERT INTO category (name, parent_id) VALUES ("Travel", 9);
INSERT INTO category (name, parent_id) VALUES ("All Around", 9);
INSERT INTO category (name, parent_id) VALUES ("Gaming", 9);
INSERT INTO category (name, parent_id) VALUES ("Tablet", 7);
/* Tree */
WITH RECURSIVE shoppingCategories AS
(
SELECT id, name, parent_id, 1 AS depth, name AS path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, sc.depth + 1, CONCAT(sc.path, ' > ', c.name)
FROM shoppingCategories AS sc
JOIN category AS c ON sc.id = c.parent_id
)
SELECT * FROM shoppingCategories;
+------+-------------+-----------+-------+-----------------------------------------------+
| id | name | parent_id | depth | path |
+------+-------------+-----------+-------+-----------------------------------------------+
| 1 | Electronics | NULL | 1 | Electronics |
| 2 | TV | 1 | 2 | Electronics > TV |
| 6 | Camera | 1 | 2 | Electronics > Camera |
| 7 | Computer | 1 | 2 | Electronics > Computer |
| 3 | Smart | 2 | 3 | Electronics > TV > Smart |
| 4 | 4K Ultra HD | 2 | 3 | Electronics > TV > 4K Ultra HD |
| 5 | Curved | 2 | 3 | Electronics > TV > Curved |
| 8 | Desktop | 7 | 3 | Electronics > Computer > Desktop |
| 9 | Laptops | 7 | 3 | Electronics > Computer > Laptops |
| 14 | Tablet | 7 | 3 | Electronics > Computer > Tablet |
| 10 | Work | 9 | 4 | Electronics > Computer > Laptops > Work |
| 11 | Travel | 9 | 4 | Electronics > Computer > Laptops > Travel |
| 12 | All Around | 9 | 4 | Electronics > Computer > Laptops > All Around |
| 13 | Gaming | 9 | 4 | Electronics > Computer > Laptops > Gaming |
+------+-------------+-----------+-------+-----------------------------------------------+
/* ancestors, tree */
SET @catID = 9;
WITH RECURSIVE
ancestorCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE id = @catID
UNION ALL
SELECT c.id, c.name, c.parent_id, ac.depth - 1
FROM ancestorCategories AS ac
JOIN category AS c ON ac.parent_id = c.id
),
ancestorDeptOneCategories AS
(
SELECT id, name, parent_id, 1 AS depth
FROM category
WHERE parent_id IS null
UNION ALL
SELECT c.id, c.name, c.parent_id, adoc.depth + 1
FROM ancestorDeptOneCategories AS adoc
JOIN ancestorCategories ac ON ac.id = adoc.id
JOIN category AS c ON adoc.id = c.parent_id
)
SELECT * FROM ancestorDeptOneCategories
ORDER BY depth;
+------+-------------+-----------+-------+
| id | name | parent_id | depth |
+------+-------------+-----------+-------+
| 1 | Electronics | NULL | 1 |
| 2 | TV | 1 | 2 |
| 6 | Camera | 1 | 2 |
| 7 | Computer | 1 | 2 |
| 8 | Desktop | 7 | 3 |
| 9 | Laptops | 7 | 3 |
| 14 | Tablet | 7 | 3 |
| 10 | Work | 9 | 4 |
| 11 | Travel | 9 | 4 |
| 12 | All Around | 9 | 4 |
| 13 | Gaming | 9 | 4 |
+------+-------------+-----------+-------+
On gists
With recursive - CTE
•
MySql
MySql tricks
MySql - advanced
cte.sql #
WITH RecursiveSequence AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM RecursiveSequence
WHERE number < 10
)
SELECT * FROM RecursiveSequence;
+--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| ... |
| 10 |
+--------+
/*
numbers, days interval
*/
WITH RECURSIVE cte(n, dt) AS (
SELECT 10, '2023-01-01'
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
/*
nebo takto kdyz nechci uvadet za cte(parametry):
WITH RECURSIVE cte AS (
SELECT 10 AS n, '2023-01-01' AS dt
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)
*/
SELECT * FROM cte;
/* tree, path, level */
# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL),
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name | role | manager_id |
+----+----------+--------------+------------+
| 1 | Matthew | CEO | NULL |
| 2 | Caroline | CFO | 1 |
| 3 | Tom | CTO | 1 |
| 4 | Sam | Treasurer | 2 |
| 5 | Ann | Controller | 2 |
| 6 | Anthony | Dev Director | 3 |
| 7 | Lousie | Sys Admin | 3 |
| 8 | Travis | Senior DBA | 3 |
| 9 | John | Developer | 6 |
| 10 | Jennifer | Developer | 6 |
| 11 | Maria | Junior DBA | 8 |
+----+----------+--------------+------------+
WITH RECURSIVE reporting_chain(id, name, PATH, PATH2, level) AS
(
SELECT
id, name,
CAST(name AS CHAR(100)),
CAST(name AS CHAR(100)),
1
FROM orgchart
WHERE manager_id IS NULL
UNION ALL
SELECT
oc.id,
oc.name,
CONCAT(rc.path,' -> ',oc.name),
CONCAT(oc.name,' -> ', rc.path2),
rc.level+1
FROM reporting_chain rc
JOIN orgchart oc ON rc.id=oc.manager_id
)
SELECT * FROM reporting_chain ORDER BY level;
+------+----------+---------------------------------------+-------+
| id | name | path | level |
+------+----------+---------------------------------------+-------+
| 1 | Matthew | Matthew | 1 |
| 2 | Caroline | Matthew -> Caroline | 2 |
| 3 | Tom | Matthew -> Tom | 2 |
| 4 | Sam | Matthew -> Caroline -> Sam | 3 |
| 5 | Ann | Matthew -> Caroline -> Ann | 3 |
| 6 | Anthony | Matthew -> Tom -> Anthony | 3 |
| 7 | Lousie | Matthew -> Tom -> Lousie | 3 |
| 8 | Travis | Matthew -> Tom -> Travis | 3 |
| 9 | John | Matthew -> Tom -> Anthony -> John | 4 |
| 10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer | 4 |
| 11 | Maria | Matthew -> Tom -> Travis -> Maria | 4 |
+------+----------+---------------------------------------+-------+
/* node paths */
WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;
/* next tree */
CREATE TABLE
categories (
id int,
cat_name varchar(100),
parent_category_id int DEFAULT NULL
);
INSERT INTO
categories
VALUES
(1, 'Mens', NULL),
(2, 'Tops', 1),
(3, 'Jerseys', 2),
(4, 'England', 3);
WITH RECURSIVE
category_tree AS (
SELECT
id,
cat_name,
parent_category_id,
cat_name AS full_name
FROM
categories
WHERE
parent_category_id IS NULL
UNION ALL
SELECT
c.id,
c.cat_name,
c.parent_category_id,
CONCAT (ct.full_name, ' > ', c.cat_name)
FROM
categories c
JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT
full_name
FROM
category_tree;
On gists
MySql
MySql tricks
MySql - advanced
MySQL can't specify target table for update in FROM clause
super-workaround.sql #
/*
does not work!
*/
update Users set Valid = 0
where Id in (
select Id from Users where Id < 100
)
/*
workaround
+ one subselect more like a wrapper ;-)
*/
update Users set Valid = 0
where Id in (
select Id from (
select Id from Users where Id < 100
) as t
)
On gists
MySql
MySql tricks
MySql - advanced
Query with variable in table name
example.sql #
SET @newDomainTable := CONCAT('domains_', newDomain);
SET @SQL = CONCAT(
'CREATE TABLE ',@newDomainTable,' (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
PREPARE sqlcmd from @SQL;
EXECUTE sqlcmd;
DEALLOCATE PREPARE sqlcmd;
END
On gists
Pivot table via prepared statements
•
MySql
MySql tricks
MySql - advanced
pivot.sql #
create table tbl_values (
id int unsigned not null primary key,
`Name` varchar(10),
`Group` varchar(10),
`Value` int
);
insert into tbl_values values
(1, 'Pete', 'A', 10),
(2, 'Pete', 'B', 20),
(3, 'John', 'A', 10);
-- 1. Create an expression that builds the columns
set @sql = (
select group_concat(distinct
concat(
"sum(case when `Group`='", `Group`, "' then `Value` end) as `", `Group`, "`"
)
)
from tbl_values
);
-- 2. Complete the SQL instruction
set @sql = concat("select Name, ", @sql, " from tbl_values group by `Name`");
-- 3. Create a prepared statement
prepare stmt from @sql;
-- 4. Execute the prepared statement
execute stmt;