Gists - MySql - advanced

On gists

Generated columns (Virtual/Stored)

17.1.2025 • 7.2.2025 (aktualizováno)

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

17.1.2025 • 7.2.2025 (aktualizováno)

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

29.11.2024 • 7.2.2025 (aktualizováno)

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

MySQL - Remove duplicate entries

27.5.2024

MySql MySql - advanced

remove.sql #

DELETE FROM your_table
WHERE rowid NOT IN (
    SELECT MAX(rowid)
    FROM your_table
    GROUP BY column1, column2, ...
);

On gists

Advanced usage of CASE WHEN THEN

12.5.2024 • 2.6.2024 (aktualizováno)

MySql MySql - advanced

when-then.sql #

-- https://towardsdev.com/sql-case-statement-with-code-examples-04f77dab8d5a

-- 1. Simple case
SELECT
    order_id,
    CASE customer_id
        WHEN 1 THEN 'Premium'
        WHEN 2 THEN 'Gold'
        WHEN 3 THEN 'Silver'
        ELSE 'Regular'
    END AS customer_type
FROM orders;


-- 2. Searched CASE Statement
SELECT
    order_id,
    CASE
        WHEN order_amount > 1000 THEN 'High'
        WHEN order_amount > 500 THEN 'Medium'
        ELSE 'Low'
    END AS order_priority
FROM orders;

-- 3. Using CASE in WHERE Clause
SELECT *
FROM customers
WHERE CASE
    WHEN country = 'USA' THEN sales_region = 'North America'
    WHEN country = 'UK' THEN sales_region = 'Europe'
    ELSE FALSE
END;

-- 4. Using CASE with Aggregate Functions
SELECT
    department,
    COUNT(CASE WHEN salary > 50000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary <= 50000 THEN 1 END) AS low_salary_count
FROM employees
GROUP BY department;


-- 5. Nesting CASE Statements
SELECT
    order_id,
    CASE
        WHEN payment_status = 'paid' THEN
            CASE
                WHEN shipping_status = 'shipped' THEN 'Delivered'
                ELSE 'Processing'
            END
        ELSE 'Pending'
    END AS order_status
FROM orders;

-- 6. Using CASE in JOIN Conditions
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM orders o
JOIN customers c
ON CASE
    WHEN o.customer_id = 1 THEN c.customer_id = o.customer_id
    WHEN o.customer_id = 2 THEN c.country = 'USA'
    ELSE c.country = 'UK'
END;


-- INSERT
INSERT INTO employees (employee_id, name, salary)
VALUES
 (101, 'John Doe', 
 CASE
 WHEN department = 'Engineering' THEN 80000
 WHEN department = 'Marketing' THEN 70000
 ELSE 60000
 END);

On gists

LEFT JOIN -> JOIN ON, WHERE

16.3.2024

MySql MySql - advanced

join.sql #

/*
https://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
*/


+--------+---------------+
| DeptId | DeptName      |
+--------+---------------+
|      1 | HR            |
|      2 | Payroll       |
|      3 | Admin         |
|      4 | Marketing     |
|      5 | HR & Accounts |
+--------+---------------+

+-------+----------+--------+-----------+
| EmpId | EmpName  | DeptId | EmpSalary |
+-------+----------+--------+-----------+
|     1 | John     |      1 |   5000.00 |
|     2 | Albert   |      1 |   4500.00 |
|     3 | Crain    |      2 |   6000.00 |
|     4 | Micheal  |      2 |   5000.00 |
|     5 | David    |   NULL |     34.00 |
|     6 | Kelly    |   NULL |    457.00 |
|     7 | Rudy     |      1 |    879.00 |
|     8 | Smith    |      2 |   7878.00 |
|     9 | Karsen   |      5 |    878.00 |
|    10 | Stringer |      5 |    345.00 |
|    11 | Cheryl   |   NULL |      0.00 |
+-------+----------+--------+-----------+


-- 1
select * from employees e
left join departments d on e.deptid = d.deptid;
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |      2 | Payroll       |
|     4 | Micheal  |      2 |   5000.00 |      2 | Payroll       |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |      2 | Payroll       |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 2
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |   NULL | NULL          |
|     4 | Micheal  |      2 |   5000.00 |   NULL | NULL          |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |   NULL | NULL          |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 3
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
+-------+----------+--------+-----------+--------+---------------+


-- to same ale z druheho pohledu, slo by resit pres RIGHT JOIN

-- 1
select * from departments d
left join employees e on e.deptId = d.deptId;
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |     3 | Crain    |      2 |   6000.00 |
|      2 | Payroll       |     4 | Micheal  |      2 |   5000.00 |
|      2 | Payroll       |     8 | Smith    |      2 |   7878.00 |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+


-- 2
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |  NULL | NULL     |   NULL |      NULL |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+

-- 3
SELECT * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+

On gists

Pivot table

16.3.2024 • 17.3.2024 (aktualizováno)

MySql - advanced

pivot.sql #

-- static
SELECT
  product_id,
  SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
  SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;

-- dynamic
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN month = ''',
      month,
      ''' THEN sales_amount ELSE 0 END) AS ',
      CONCAT('`', month, '`')
    )
  ) INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



-- another table
SELECT
  `Sales Date`,
  MAX(CASE WHEN `Product Name` = 'Product AthanEN `Sales Amount` END) AS `Product A`,
  MAX(CASE WHEN `Product Name` = 'Product B' then `Sales Amount` END) AS `Product B`,
  MAX(CASE WHEN `Product Name` = 'Product C' than `Sales Amount` END) AS `Product C`
FROM
  `sales_data`
GROUP BY
  `Sales Date`;
  
  
  SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN `Product Name` = ''', `Product Name`, ''' THEN `Sales Amount` END) AS `', `Product Name`, '`')
  ) INTO @sql
FROM
  `sales_data`;

SET @sql = CONCAT('SELECT `Sales Date`, ', @sql, ' FROM `sales_data` GROUP BY `Sales Date`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



/* month-year invoices */
select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok

On gists

window fn - standings rank with previous rank

15.3.2024

MySql - advanced

rank.sql #

CREATE TABLE `standing` (
	`team` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`pts` INT(11) NULL DEFAULT NULL,
	`season` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=MyISAM
;
  
  
  SELECT
    team,
    season,
    pts,
    RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rnk,
    LAG(rank_prev_season) OVER (PARTITION BY team ORDER BY season) AS prev_rnk
FROM
    (
        SELECT
            team,
            season,
            pts,
            RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rank_prev_season
        FROM
            standing
    ) AS subquery
ORDER BY
    season, rnk;



+--------+--------+------+-----+----------+
| team   | season | pts  | rnk | prev_rnk |
+--------+--------+------+-----+----------+
| Plzen  |   2021 |   60 |   1 |     NULL |
| Sparta |   2021 |   55 |   2 |     NULL |
| Slavia |   2021 |   40 |   3 |     NULL |
| Sparta |   2022 |   54 |   1 |        2 |
| Slavia |   2022 |   50 |   2 |        3 |
| Plzen  |   2022 |   48 |   3 |        1 |
| Slavia |   2023 |   55 |   1 |        2 |
| Plzen  |   2023 |   49 |   2 |        3 |
| Sparta |   2023 |   38 |   3 |        1 |
+--------+--------+------+-----+----------+

On gists

sql table from select

11.3.2024

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

Top N per group - 4 ways

29.5.2023 • 30.5.2023 (aktualizováno)

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 |
+------+------------+------+