Gists
On gists
reveal.js
•
JavaScript
reveal.js #
function reveal() {
var reveals = document.querySelectorAll(".reveal");
for (var i = 0; i < reveals.length; i++) {
var windowHeight = window.innerHeight;
var elementTop = reveals[i].getBoundingClientRect().top;
var elementVisible = 150;
if (elementTop < windowHeight - elementVisible) {
reveals[i].classList.add("active");
} else {
reveals[i].classList.remove("active");
}
}
}
window.addEventListener("scroll", reveal);
On gists
JavaScript
Simple reveal
reveal.js #
function reveal() {
var reveals = document.querySelectorAll(".reveal");
for (var i = 0; i < reveals.length; i++) {
var windowHeight = window.innerHeight;
var elementTop = reveals[i].getBoundingClientRect().top;
var elementVisible = 150;
if (elementTop < windowHeight - elementVisible) {
reveals[i].classList.add("active");
} else {
reveals[i].classList.remove("active");
}
}
}
window.addEventListener("scroll", reveal);
On gists
Top N per group - 4 ways
•
MySql
MySQL - ArtfulSoftware
MySql - advanced
0-structure.sql #
DROP TABLE IF EXISTS test;
CREATE TABLE test( id INT, entrydate DATE );
INSERT INTO test VALUES
( 1, '2007-5-01' ),( 1, '2007-5-02' ),
( 1, '2007-5-03' ),( 1, '2007-5-04' ),
( 1, '2007-5-05' ),( 1, '2007-5-06' ),
( 2, '2007-6-01' ),( 2, '2007-6-02' ),
( 2, '2007-6-03' ),( 2, '2007-6-04' ),
( 3, '2007-7-01' ),( 3, '2007-7-02' ),
( 3, '2007-7-03' );
-- expected result
+------+------------+------+
| id | entrydate | rank |
+------+------------+------+
| 1 | 2007-05-01 | 1 |
| 1 | 2007-05-02 | 2 |
| 2 | 2007-06-01 | 1 |
| 2 | 2007-06-02 | 2 |
| 3 | 2007-07-01 | 1 |
| 3 | 2007-07-02 | 2 |
+------+------------+------+
On gists
MySql
MySQL - ArtfulSoftware
Group Concat with count
q.sql #
drop table if exists t;
create table t (
type int(10) ,
instance int(10)
) ;
insert into t values
(1,4),(1,7),(1,9),(1,10),(2,2),
(2,3),(2,5),(2,6),(2,8),(3,1),(4,11);
select
group_concat(concat(type,'(',qty,')') separator ', ')
as list
from (
select type, count(*) qty
from t
group by type
) n
+------------------------+
| list |
+------------------------+
| 1(4), 2(5), 3(1), 4(1) |
+------------------------+
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
JavaScript
Scroll header - IntersectionObserver by KP
demo.html #
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<style>
.primary-header {
--_bg: hsl(0 0% 100%);
--_logo-color: hsl(0, 0%, 5%);
--_nav-link-color: hsl(37, 82%, 20%);
padding: 1rem;
background: var(--_bg);
position: sticky;
top: 0;
}
/* for demo, probably not the best idea */
.primary-header,
.primary-header * {
transition: 500ms;
}
.primary-header.sticking {
--_bg: #333;
--_logo-color: #fff;
--_nav-link-color: #f00;
}
.primary-header__inner {
display: flex;
justify-content: space-between;
}
.primary-header a {
color: var(--_nav-link-color);
}
.primary-header a:hover,
.primary-header a:focus {
opacity: 0.7;
}
.logo {
color: var(--_logo-color);
}
.nav-list {
list-style: none;
margin: 0;
padding: 0;
display: flex;
gap: clamp(0.5rem, 5vw, 2rem);
}
:root {
/* either --dark-mode or --no-dark-mode */
--color-scheme: var(--dark-mode);
--font-family: system-ui;
--fs-300: clamp(0.94rem, calc(0.92rem + 0.08vw), 0.98rem);
--fs-400: clamp(1.13rem, calc(1.06rem + 0.33vw), 1.31rem);
--fs-500: clamp(1.35rem, calc(1.21rem + 0.69vw), 1.75rem);
--fs-600: clamp(1.62rem, calc(1.37rem + 1.24vw), 2.33rem);
--fs-700: clamp(1.94rem, calc(1.54rem + 2.03vw), 3.11rem);
--fs-800: clamp(2.33rem, calc(1.7rem + 3.15vw), 4.14rem);
--fs-900: clamp(2.8rem, calc(1.85rem + 4.74vw), 5.52rem);
--clr-primary-300: hsl(219, 76%, 55%);
--clr-primary-400: hsl(219, 76%, 40%);
--clr-primary-500: hsl(219, 76%, 25%);
--clr-secondary-300: hsl(269, 75%, 55%);
--clr-secondary-400: hsl(269, 75%, 40%);
--clr-secondary-500: hsl(269, 75%, 25%);
--clr-accent-300: hsl(358, 72%, 65%);
--clr-accent-400: hsl(358, 72%, 50%);
--clr-accent-500: hsl(358, 72%, 35%);
/* settings */
--no-dark-mode: light;
--dark-mode: dark light;
}
*,
*::before,
*::after {
box-sizing: border-box;
}
body {
margin: 0;
font-family: var(--font-family);
font-size: var(--fs-400);
line-height: 1.6;
}
.wrapper {
width: min(65rem, 100% - 2rem);
margin-inline: auto;
}
.site-title {
font-size: var(--fs-900);
line-height: 1.05;
text-transform: uppercase;
}
.section-title {
font-size: var(--fs-800);
line-height: 1.1;
}
.visually-hidden {
clip: rect(0 0 0 0);
clip-path: inset(50%);
height: 1px;
overflow: hidden;
position: absolute;
white-space: nowrap;
width: 1px;
}
</style>
</head>
<body>
<header class="primary-header">
<div class="wrapper">
<div class="primary-header__inner">
<span class="logo">LOGO</span>
<ul class="nav-list">
<li><a href="#">Home</a></li>
<li><a href="#">Blog</a></li>
<li><a href="#">About</a></li>
<li><a href="#">Contact</a></li>
</ul>
</div>
</div>
</header>
<main>
<div class="wrapper">
<h1>Lorem ipsum dolor sit amet consectetuer adipiscing elit</h1>
<p>
Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa <strong>strong</strong>. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fringilla vel, aliquet nec, vulputate eget, arcu. In enim justo, rhoncus ut, imperdiet a, venenatis vitae, justo. Nullam dictum felis eu pede <a class="external ext" href="#">link</a>
mollis pretium. Integer tincidunt. Cras dapibus. Vivamus elementum semper nisi. Aenean vulputate eleifend tellus. Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim. Aliquam lorem ante, dapibus in, viverra quis, feugiat a, tellus. Phasellus viverra nulla ut metus varius laoreet. Quisque rutrum. Aenean imperdiet. Etiam ultricies nisi vel augue. Curabitur ullamcorper ultricies nisi.
</p>
<h1>Lorem ipsum dolor sit amet consectetuer adipiscing elit</h1>
<h2>Aenean commodo ligula eget dolor aenean massa</h2>
<img
src="/image-2.jpg"
alt="consectetuer adipiscing
elit"
/>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.</p>
<h2>Aenean commodo ligula eget dolor aenean massa</h2>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.</p>
<ul>
<li>Lorem ipsum dolor sit amet consectetuer.</li>
<li>Aenean commodo ligula eget dolor.</li>
<li>Aenean massa cum sociis natoque penatibus.</li>
</ul>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.</p>
<form action="#" method="post">
<fieldset>
<label for="name">Name:</label>
<input
type="text"
id="name"
placeholder="Enter your
full name"
/>
<label for="email">Email:</label>
<input
type="email"
id="email"
placeholder="Enter
your email address"
/>
<label for="message">Message:</label>
<textarea
id="message"
placeholder="What's on your
mind?"
></textarea>
<input type="submit" value="Send message" />
</fieldset>
</form>
<img
src="/image-3.jpg"
alt="consectetuer adipiscing
elit"
/>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.</p>
<table class="data">
<tr>
<th>Entry Header 1</th>
<th>Entry Header 2</th>
<th>Entry Header 3</th>
<th>Entry Header 4</th>
</tr>
<tr>
<td>Entry First Line 1</td>
<td>Entry First Line 2</td>
<td>Entry First Line 3</td>
<td>Entry First Line 4</td>
</tr>
<tr>
<td>Entry Line 1</td>
<td>Entry Line 2</td>
<td>Entry Line 3</td>
<td>Entry Line 4</td>
</tr>
<tr>
<td>Entry Last Line 1</td>
<td>Entry Last Line 2</td>
<td>Entry Last Line 3</td>
<td>Entry Last Line 4</td>
</tr>
</table>
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem.</p>
</div>
</main>
<script>
// For this tutorial on YT:
// https://youtu.be/V-CBdlfCPic
const primaryHeader = document.querySelector('.primary-header');
const scrollWatcher = document.createElement('div');
scrollWatcher.setAttribute('data-scroll-watcher', '');
primaryHeader.before(scrollWatcher);
const navObserver = new IntersectionObserver(
entries => {
primaryHeader.classList.toggle('sticking', !entries[0].isIntersecting);
},
{ rootMargin: '500px 0px 0px 0px' }
);
navObserver.observe(scrollWatcher);
</script>
</body>
</html>
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
SQL mix (all my SQL, huge library)
•
MySql tricks
MySql - advanced
mix.sql #
/*
ulozeno jako 1,3,999,765 ...
*/
/*stare spojeni*/
select z.* from
_zapasy z, _souteze s
where find_in_set(z.id,s.zapasy) and s.id=1
/* JOIN spojeni, 2 druhy */
select * from _zapasy z join _souteze s on s.id=1 where find_in_set(z.id,s.zapasy)
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) WHERE s.id=1
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) AND s.id=1
/* 20 latest unique records */
SELECT
FROM
(
SELECT
sparse,
MAX(ts) AS last_update
FROM
logfile
GROUP BY
sparse
) q
ORDER BY
last_update DESC
LIMIT
/* rank */
set @type := '', @num := 1;
select type, variety,
@num := if(@type = type, @num + 1, 1) as row_number,
@type := type as dummy
from fruits
order by type, variety;
+--------+------------+------------+--------+
| type | variety | row_number | dummy |
+--------+------------+------------+--------+
| apple | fuji | 1 | apple |
| apple | gala | 2 | apple |
| apple | limbertwig | 3 | apple |
| cherry | bing | 1 | cherry |
| cherry | chelan | 2 | cherry |
| orange | navel | 1 | orange |
| orange | valencia | 2 | orange |
| pear | bartlett | 1 | pear |
| pear | bradford | 2 | pear |
+--------+------------+------------+--------+
/* cislovani radku */
-- ve 2 SQL
set @radek=0;
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;
-- by KAJMAN v 1 sql
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t
/* Nejnovejsi komentare a clanky k nim s limitem */
SELECT clanky.nadpis, com.id, com.datum
FROM clanky,
(
SELECT id, id_cl, datum
FROM komentare
ORDER BY id DESC
)com
WHERE clanky.id = com.id_cl
GROUP BY clanky.id
ORDER BY com.datum
LIMIT 10 ;
/* concat subselect s limitem */
select group_concat(uzivatele order by registrace desc)
from (select uzivatele, registrace from uzivatele order by registrace desc limit 0,10) tmp
/* update case ..., update elt/field https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=143350 */
update `dostupnostKniha` join
(select 1 id, 10 k
union
select 2 id, 20 k
union
select 3 id, 30 k
union
select 4 id, 40 k) dbtmp on `dostupnostKniha`.id = dbtmp.id and `dostupnostKniha`.idDostupnosti = 2
set `dostupnostKniha`.pocetKusu = dbtmp.k, `dostupnostKniha`.datumUpdate = '$datum'
UPDATE dostupnostKniha
SET datumUpdate = '$datum', pocetKusu = Elt(Field(id, 1, 2), 10, 20)
WHERE id IN ( 1, 2 ) and idDostupnosti = 2
/* Náhrada za windows funkce */
SELECT id_student, id_class, grade,
@student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
@class:=id_class AS clset
FROM
(SELECT @student:= -1) s,
(SELECT @class:= -1) c,
(SELECT *
FROM mytable
ORDER BY id_class, id_student
) t
/* IN spojeni 2 a vice hodnot */
select ID_osoby
from co_si_pucil
where (ID_polozky,druh) in ((11043,'kniha'),(9443,'film'))
group by ID_osoby
having count(distinct ID_polozky, druh) = 2 -- pocet hledanych kombinaci
/* lepsi spojeni v subselectu nez normalne pres JOIN */
EXPLAIN SELECT gb . * , hraci.prezdivka
FROM hraci, (
SELECT *
FROM gb2
ORDER BY id DESC
LIMIT 3
)gb
WHERE hraci.id = gb.jmeno
/* limit v poddotaze */
SELECT * FROM (SELECT * FROM `tabulka` ORDER BY `poradi_vlozeni` DESC LIMIT 20) vybrane ORDER BY RAND() LIMIT 10
/* mazani duplicit, vic zpusobu */
delete from dupes
where id not in (select min(id) from dupes group by name)
-- Nezapoměňte si data zazálohovat. Pak jsou obvyklé dva způsoby.
-- 1) vytvořit si dočasný unikátní index, díky ingore se provede i na duplicitními sloupečky a další opakování vyhodí
ALTER IGNORE TABLE tabulka ADD UNIQUE INDEX docasny123 (sloupecek);
ALTER TABLE tabulka DROP INDEX docasny123;
-- 2) použít delete using s propojením tabulky se samo sebou - potřebujete ale nějaký jedinečný sloupeček,
-- tím si můžete i určit, zda chcete např. ponechat nejstarší nebo nejmladší hodnotu (nejvyšší id jako v příkladu)
DELETE FROM t1 USING tabulka t1, tabulka t2 WHERE t1.sloupecek=t2.sloupecek AND t1.id<t2.id
/* update join */
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
mysql> DELETE cds
-> FROM cds, artists
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');
To DELETE from both tables:
mysql> DELETE cds, artist
-> FROM cds, artist
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');
==========
mysql> UPDATE cds
-> LEFT JOIN artists
-> USING (artistID)
-> SET cds.artists = artists.Artist
-> WHERE (cds.cdID = '2');
UPDATE videogallery_komentare t1 JOIN videogallery t2 ON t2.code = t1.code
SET t1.videoID = t2.ID
/** tree without with recursive */
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
select l0.catID,
concat(
case when l5.catID is null then '' else concat(l5.category, '/') end
, case when l4.catID is null then '' else concat(l4.category, '/') end
, case when l3.catID is null then '' else concat(l3.category, '/') end
, case when l2.catID is null then '' else concat(l2.category, '/') end
, case when l1.catID is null then '' else concat(l1.category, '/') end
, l0.category)
from catcat l0
left join catcat l1 on l0.parentID=l1.catID
left join catcat l2 on l1.parentID=l2.catID
left join catcat l3 on l2.parentID=l3.catID
left join catcat l4 on l3.parentID=l4.catID
left join catcat l5 on l4.parentID=l5.catID
/* WEBFAQ, poradi, statistiky */
SET @poradi:=0,@poradib:=0;
SELECT @poradi:=@poradi+1, @poradi as poradi, IF(id=201, @poradib:=@poradi, 0), id, name FROM table ORDER BY `name` DESC;
SELECT @poradib;
-- kajman ;)
select count(*)+1 poradi from tabulka t where t.rok=2008 and t.golu>(select j.golu where j.rok=2008 and j.hrac=‚Jágr‘)
select count(*)+1 from (select sum(gol) golu group by hrac having golu>$goly_jagra)
/* CASE or ELT */
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
-- or
UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
ELSE `uid`
END
WHERE id in (1,2,3)
/* Nejvyssi clanky v kateg. */
select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)
SELECT
t1.id,
t1.nadpis,
t2.datum,
t1.datum,
t2.kategorie
FROM clanky t1
JOIN ( SELECT t2.kategorie, MAX(t2.datum) datum FROM clanky t2 GROUP BY t2.kategorie) t2
ON t1.kategorie = t2.kategorie
AND t1.datum = t2.datum
select * from clanky t1
where t1.id in (select max(t2.id) id from clanky t2 group by t2.kategorie)
/* rank, fotbalova tabulka */
http://diskuse.jakpsatweb.cz/index.php?action=vthread&topic=97602&forum=28&page=-1
1/ uzivatelske promenne
SET @minule := 0;
SET @poradi := 0;
SET @radek := 1;
UPDATE
poradi,
(
SELECT
tym,
body,
@poradi := IF(@minule = body, @poradi, @radek) AS poradi,
@radek := @radek + 1 AS radek,
@minule := body AS tmp
FROM poradi
ORDER BY body DESC
) AS vypocet
SET poradi.poradi = vypocet.poradi
WHERE poradi.tym = vypocet.tym;
2/ Kajman , nejdrive nastavit pozice klasicky 1,2,3, .. a pak vybrat ty co jsou vicekrat a znova update
update tabulka,
(select body1, body2, body3, min(poradi) poradi
from tabulka
group by body1, body2, body3
having count(*) > 1) as vypocet
set tabulka.poradi = vypocet.poradi
where tabulka.body1 = vypocet.body1
and tabulka.body2 = vypocet.body2
and tabulka.body3 = vypocet.body3
3/ za behu korelovany dotaz
SELECT SQL_NO_CACHE
(SELECT COUNT(*) + 1 FROM poradi as t1 WHERE t1.body > t2.body) AS rank, t2.tym, t2.body
FROM poradi as t2
4/ Moje procedura :)
CREATE PROCEDURE `SP_poradi`()
BEGIN
DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;
DECLARE position TINYINT DEFAULT 0;
DECLARE i INT;
DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET i = 0;
OPEN rank;
rank_loop: LOOP
FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;
SET i=i+1;
IF (temp_body <> s_body)
THEN SET position = i;
ELSE
SET position = position;
END IF;
UPDATE poradi SET poradi = position WHERE tym = s_tym;
SET temp_body = s_body;
END LOOP rank_loop;
CLOSE rank;
END
/* poradi nad a pod konkretnim poradim */
SELECT a . *
FROM tabulky a, tabulky b
WHERE b.tym =11 /* nahodny tym, ktery ma pozici 6 */
AND (
a.poradi +1 = b.poradi
OR a.poradi = b.poradi
OR a.poradi = b.poradi +1
)
ORDER BY poradi
/* rank with FIND_IN_SET */
Id Name Score
1 Ida 100
2 Boo 58
3 Lala 88
4 Bash 102
5 Assem 99
SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
id name score rank
1 Ida 100 2
2 Boo 58 5
3 Lala 88 4
4 Bash 102 1
5 Assem 99 3
Getting a single person score:
SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
WHERE name = 'Assem'
Gives this result:
id name score rank
5 Assem 99 3
/* GW ladder, rank similar values */
# za vcerejsek
SELECT
*
FROM
vysledky
WHERE
date_format(datum, '%Y-%m-%d') = (
CURDATE() - INTERVAL 1 DAY
) # za dnesek
SELECT
*
FROM
vysledky
WHERE
date_format(datum, '%Y-%m-%d') = CURDATE() # starsi nez 2 dny od dneska
SELECT
*
FROM
vysledky
WHERE
date_format(datum, '%Y-%m-%d') < (
CURDATE() - INTERVAL 1 DAY
)
order by
id_zapasu DESC
limit
10 # vytah
SELECT
count(*) + 1
FROM
`tabulka_tymu_ladder` l
LEFT JOIN `tabulka_tymu_ladder` a ON (
a.id_hrace = 1
AND a.id_souteze = 7
)
WHERE
l.id_souteze = 7
AND (
l.koeficient > a.koeficient
OR (
l.koeficient = a.koeficient
AND (
l.pts > a.pts
OR (
l.pts = a.pts
AND (
(l.gf - l.ga) > (a.gf - a.ga)
OR (
(l.gf - l.ga) = (a.gf - a.ga)
AND (
l.pz > a.pz
OR (
l.pz = a.pz
AND l.gf > a.gf
)
)
)
)
)
)
)
)
SELECT
count(*) + 1
FROM
`tabulka_tymu_ladder` l,
`tabulka_tymu_ladder` a
WHERE
a.id_hrace = 1
AND a.id_souteze = 8
AND l.id_souteze = 8
AND (
l.koeficient > a.koeficient
OR (
l.koeficient = a.koeficient
AND (
l.pts > a.pts
OR (
l.pts = a.pts
AND (
(l.gf - l.ga) > (a.gf - a.ga)
OR (
(l.gf - l.ga) = (a.gf - a.ga)
AND (
l.pz > a.pz
OR (
l.pz = a.pz
AND l.gf > a.gf
)
)
)
)
)
)
)
)
/* prioritni order by */
SELECT * FROM staty order by (nazev='Česká republika') DESC,
(nazev='Slovenská republika') DESC, nazev
/* vnoreny dotaz v insert select */
INSERT INTO tab1 (id,desription) VALUES((SELECT id FROM tab2 WHERE name='neco'),'blablalba')
/*
https://stackoverflow.com/questions/12992793/efficient-ways-to-calculate-ranks-for-over-350-000-users?noredirect=1&lq=1
*/
UPDATE `Members`
SET `RankNum` = (@newRank := @newRank + 1)
ORDER BY `Rank` DESC, `Id` ASC, @newRank := 0
/*
http://explainextended.com/2009/04/07/keeping-rows/
mazani s limitem
*/
DELETE d.*
FROM t_deleter d
LEFT JOIN
(
SELECT id
FROM t_deleter
LIMIT 10
) q
ON d.id = q.id
WHERE q.id IS NULL
/* ANY, ALLL */
Zkuste si udělat tabulku tabulka se sloupcem sloupec a v ní hodnoty 1 a 2.
select * from tabulka where sloupec >= any (select sloupec from tabulka)
vrátí oba řádky ale
select * from tabulka where sloupec >= all (select sloupec from tabulka)
vrátí jen řádek s dvojkou, protože 1 není větší než všechny hodnoty z poddotazu
/* Nalezení položek mající všechny hledané vlastnosti */
select id_polozky
from vazebni_tabulka
where id_vlastnosti in (1, 5, 7)
group by id_polozky
having count(distinct id_vlastnosti) = 3 -- počet jedinečných hodnot ze závorky
-- update when then
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
-- Multiple updates
-- with insert or duplicate key
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
-- or with union
UPDATE my_table m
JOIN (
SELECT 1 as id, 10 as _col1, 20 as _col2
UNION ALL
SELECT 2, 5, 10
UNION ALL
SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = __col2;
-- next solutions
UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
ELSE `uid`
END
WHERE id in (1,2,3)
UPDATE table SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
/* left join with correlated and dynamic cond */
-- https://explainextended.com/2010/10/20/correlated-left-join-in-a-subquery/
SELECT g.id, g.name, GROUP_CONCAT(p.name SEPARATOR ', ') AS players
FROM game g
LEFT JOIN
player_team pt
ON g.type = 'team'
AND pt.team = g.player1
JOIN player p
ON p.id = CASE g.type WHEN 'player' THEN g.player1 WHEN 'team' THEN pt.player END
GROUP BY
g.id
LIMIT 50
/* El dragon stats */
SELECT tmp.prezdivka, tmp.prijmeni, tmp.pocet_vysledky, tmp.pocet_clanky, tmp.pocet_forum, (
tmp.pocet_forum + tmp.pocet_vysledky + tmp.pocet_clanky
) AS CELKEM
FROM (
SELECT a.prezdivka, a.prijmeni, (
SELECT COUNT( * )
FROM forum_prispevky
WHERE registrovany_uzivatel = a.ID
) AS pocet_forum, (
SELECT COUNT( * )
FROM komentare_clanky
WHERE registrovany_uzivatel = a.ID
) AS pocet_clanky, (
SELECT COUNT( * )
FROM komentare_vysledky
WHERE registrovany_uzivatel = a.ID
) AS pocet_vysledky
FROM uzivatele AS a
) AS tmp
ORDER BY CELKEM DESC