Instantly share code, notes, and snippets.

View Functions.sql
Basic MySQL CREATE FUNCTION statement
A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:
DELIMITER $$
CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
View dense.sql
SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)
SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id
View ranks.sql
DROP TABLE IF EXISTS users;
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), start_date DATE, team_id INT);
INSERT INTO users (name, start_date, team_id) VALUES ('Matt', '2017-01-01', 1);
INSERT INTO users (name, start_date, team_id) VALUES ('John', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Sara', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Tim', '2017-01-02', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bob', '2017-01-03', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bill', '2017-01-04', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Kathy', '2017-01-04', 3);
View ranks.sql
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;
View example.sql
SELECT a.id AS nejblizsi_nizsi, b.id AS nejblizsi_vyssi
FROM `tabulka` a JOIN `tabulka` b ON (a.id < $id AND b.id>$id)
ORDER BY (b.id-a.id) ASC LIMIT 1
View max.sql
select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)
SELECT
t1.id,
t1.nadpis,
t2.datum,
View example.sql
id game_id game_rank score team_id
-------------------------------------------
5 1 1 15 1
4 1 2 25 2
1 1 3 40 3
3 1 3 40 4
2 1 4 55 5
7 2 0 0 1
6 2 0 0 2
View update.sql
#1
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)
View example.sql
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
View example.sql
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');