/ Gists / MySql tricks

Gists - MySql tricks

On gists

Generated columns (Virtual/Stored)

•
MySql MySql tricks MySql - advanced

examples.sql #

-- Základní matematické operace
amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED

-- Práce s textem
full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

-- Použití CASE
status_description VARCHAR(50) GENERATED ALWAYS AS (
    CASE 
        WHEN status = 1 THEN 'Active'
        WHEN status = 0 THEN 'Inactive'
        ELSE 'Unknown'
    END
) VIRTUAL

-- Práce s datumy
age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL

-- Komplexnější výpočty
total_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE
        WHEN tax_rate IS NULL THEN amount
        ELSE amount * (1 + tax_rate/100)
    END
) STORED

-- Formátování a manipulace s textem
formatted_phone VARCHAR(20) GENERATED ALWAYS AS (
    CONCAT('+420 ', SUBSTR(phone, 1, 3), ' ', SUBSTR(phone, 4, 3), ' ', SUBSTR(phone, 7))
) VIRTUAL
On gists

Column check constraint

•
MySql MySql tricks MySql - advanced

examples.sql #

-- CONSTRAINT [constraint_name] CHECK (expression) [ENFORCED | NOT ENFORCED]

/*
ENFORCED - MySQL aktivně kontroluje podmínku při INSERT/UPDATE
NOT ENFORCED - MySQL podmínku nekontroluje, slouží jen jako dokumentace/metadata
*/

-- Kontrola formátu emailu pomocí REGEXP
CONSTRAINT valid_email CHECK (email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')

-- Kontrola, že koncová data je později než počáteční
CONSTRAINT valid_date_range CHECK (end_date > start_date)

-- Kontrola že sloupec obsahuje pouze velká písmena
CONSTRAINT uppercase_only CHECK (column_name = UPPER(column_name))

-- Kontrola že věk je v rozumném rozsahu
CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)

-- Kombinace více podmínek pro plat
CONSTRAINT salary_rules CHECK (
    (role = 'Junior' AND salary BETWEEN 30000 AND 50000) OR
    (role = 'Senior' AND salary BETWEEN 60000 AND 120000)
)

-- Kontrola že skóre je buď NULL nebo v rozsahu 0-100
CONSTRAINT valid_score CHECK (score IS NULL OR (score >= 0 AND score <= 100))

-- Kontrola že status je jedna z povolených hodnot
CONSTRAINT valid_status CHECK (
    status IN ('pending', 'approved', 'rejected', 'on_hold')
)

-- Komplexní validace telefonního čísla
CONSTRAINT valid_phone CHECK (
    phone_number REGEXP '^\\+?[1-9][0-9]{7,14}$'
)

-- Kontrola že discount je menší než cena
CONSTRAINT valid_discount CHECK (discount < price)

-- Kontrola že součet procentuálních hodnot nepřesáhne 100
CONSTRAINT percentage_sum CHECK (
    value1 + value2 + value3 <= 100
)


-- Komplexní kontrola hesla (minimální délka, musí obsahovat číslo a velké písmeno)
CONSTRAINT password_strength CHECK (
    LENGTH(password) >= 8 
    AND password REGEXP '[0-9]'
    AND password REGEXP '[A-Z]'
    AND password != username
)

-- Kontrola věku na základě data narození s aktuálním datem
CONSTRAINT valid_birth_date CHECK (
    YEAR(birth_date) >= 1900
    AND birth_date <= CURRENT_DATE
    AND TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) <= 120
)

-- Složitá business logika s CASE
CONSTRAINT complex_pricing CHECK (
    CASE 
        WHEN customer_type = 'VIP' THEN price >= 1000 AND discount <= price * 0.3
        WHEN customer_type = 'Regular' THEN price >= 500 AND discount <= price * 0.1
        WHEN customer_type = 'New' THEN price >= 100 AND discount = 0
        ELSE price >= 0
    END
)

-- Kombinace podmínek pro rezervační systém
CONSTRAINT valid_reservation CHECK (
    (status = 'confirmed' AND payment_id IS NOT NULL AND amount > 0)
    OR (status = 'pending' AND (payment_id IS NULL OR amount = 0))
    OR (status = 'cancelled' AND cancellation_reason IS NOT NULL)
)

-- Kontrola formátu a validity dat
CONSTRAINT valid_document CHECK (
    (doc_type = 'invoice' AND doc_number REGEXP '^INV-[0-9]{6}$')
    OR (doc_type = 'order' AND doc_number REGEXP '^ORD-[0-9]{6}$')
    AND issue_date <= due_date
    AND YEAR(issue_date) >= 2020
)

-- Kontrola závislostí mezi více sloupci
CONSTRAINT project_status_rules CHECK (
    (status = 'completed' AND end_date IS NOT NULL AND progress = 100)
    OR (status = 'in_progress' AND end_date IS NULL AND progress BETWEEN 1 AND 99)
    OR (status = 'planned' AND end_date IS NULL AND progress = 0)
)

-- Sezónní ceník s různými pravidly
CONSTRAINT seasonal_pricing CHECK (
    CASE 
        WHEN MONTH(booking_date) IN (6,7,8) THEN price >= 1000  -- Letní sezóna
        WHEN MONTH(booking_date) IN (12,1,2) THEN price >= 800  -- Zimní sezóna
        WHEN DAYOFWEEK(booking_date) IN (1,7) THEN price >= 700 -- Víkendy
        ELSE price >= 500  -- Mimo sezónu
    END
)

-- Validace adresy
CONSTRAINT valid_address CHECK (
    LENGTH(street) >= 3
    AND LENGTH(city) >= 2
    AND postal_code REGEXP '^[0-9]{5}$'
    AND (
        country = 'CZ' AND phone REGEXP '^\\+420[0-9]{9}$'
        OR 
        country = 'SK' AND phone REGEXP '^\\+421[0-9]{9}$'
    )
)

-- Komplexní kontrola produktu
CONSTRAINT product_rules CHECK (
    CASE product_type
        WHEN 'physical' THEN 
            weight > 0 
            AND dimensions IS NOT NULL 
            AND digital_url IS NULL
        WHEN 'digital' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND digital_url IS NOT NULL
        WHEN 'service' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND duration > 0
    END
    AND (
        price > 0
        OR (price = 0 AND is_free_sample = TRUE)
    )
)
On gists

Insert only when @var is true

•
MySql MySql tricks MySql - advanced

insert.sql #

SET @cnt = (SELECT COUNT(1) FROM image_size WHERE folder = '840x1190');

INSERT INTO `image_size` 
    (`id`, `folder`, `quality`, `xsize`, `ysize`, 
    `image_resize_id`, `diagonal`, `watermark__image_id`, 
    `watermark_xsize`, `watermark_ysize`, 
    `watermark_xpos`, `watermark_ypos`, 
    `crop_left`, `crop_top`, `trim`, `grayscale`, 
    `filter_color`, `filter_opacity`)
    
SELECT NULL, '840x1190', 95, 840, 1190, 
       0, NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 0, 0, 
       NULL, NULL
WHERE @cnt = 0;
On gists

sql table from select

MySql MySql tricks MySql - advanced

howto.sql #

-- 1

  SELECT 'Mon' 
    UNION 
    SELECT 'Tue'
    UNION 
    SELECT 'Web'
    UNION 
    SELECT 'Thu'
    UNION 
    SELECT 'Fri'
    UNION 
    SELECT 'Sat'
    UNION 
    SELECT 'Sun';



-- 2
WITH RECURSIVE days_cte AS (
  SELECT 'Mon' AS day_name
  UNION ALL
  SELECT
    CASE day_name
      WHEN 'Mon' THEN 'Tue'
      WHEN 'Tue' THEN 'Wed'
      WHEN 'Wed' THEN 'Thu'
      WHEN 'Thu' THEN 'Fri'
      WHEN 'Fri' THEN 'Sat'
      WHEN 'Sat' THEN 'Sun'
      ELSE 'Mon' -- Pro zajištění cyklického opakování
    END
  FROM days_cte
  WHERE day_name != 'Sun'
)
SELECT day_name FROM days_cte;
On gists

Check column in table if exists

MySql MySql tricks

check-column.sql #

SELECT COUNT(*) INTO @cnt FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'transport_type' AND column_name = 'measure_distance';

SET @q = IF(@cnt <= 0, 
"ALTER TABLE transport_type ADD COLUMN measure_distance TINYINT(1) NULL DEFAULT NULL COMMENT 'Měřit vzdálenost' AFTER delivery_holiday",
'select \'Column exists\' status');

 PREPARE stmt from @q;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
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
On gists

Advanced CTE - Tree

MySql MySql tricks MySql - advanced

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 can't specify target table for update in FROM clause

MySql MySql tricks MySql - advanced

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

Query with variable in table name

MySql MySql tricks MySql - advanced

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