/ Gists / MySql

Gists - MySql

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

MySQL - Remove duplicate entries

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

•
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

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

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

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

Group Concat with count

MySql MySQL - ArtfulSoftware

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