SQL: guida completa dai fondamenti alle query avanzate

1
2
3
4
5
6
7

-- Esempio di tabella
CREATE TABLE users (
    id INT PRIMARY KEY,           -- Chiave primaria
    name VARCHAR(100) NOT NULL,   -- Campo obbligatorio
    email VARCHAR(100) UNIQUE,    -- Campo univoco
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1
2
3
4
5
6
7
8

sudo apt update
sudo apt install mysql-server mysql-client

# Configurazione sicurezza
sudo mysql_secure_installation

# Accesso a MySQL
sudo mysql -u root -p

1
2
3
4
5

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib

# Accesso
sudo -u postgres psql

1
2
3
4
5

# Installazione
sudo apt install sqlite3

# Creare/aprire database
sqlite3 test.db

1
2
3
4
5
6
7
8

-- Creare nuovo database
CREATE DATABASE ecommerce;

-- Selezionare database da usare
USE ecommerce;

-- Vedere tutti i database
SHOW DATABASES;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Tabella utenti
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabella prodotti
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Tabella categorie
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Tabella ordini
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Aggiungere colonna
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modificare colonna
ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 2);

-- Eliminare colonna
ALTER TABLE users DROP COLUMN phone;

-- Aggiungere indice
CREATE INDEX idx_users_email ON users(email);

-- Eliminare tabella
DROP TABLE IF EXISTS old_table;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- Inserimento singolo
INSERT INTO categories (name, description)
VALUES ('Electronics', 'Electronic devices and accessories');

-- Inserimento multiplo
INSERT INTO users (username, email, password_hash, first_name, last_name)
VALUES
    ('mario.rossi', 'mario@email.com', 'hash123', 'Mario', 'Rossi'),
    ('anna.verdi', 'anna@email.com', 'hash456', 'Anna', 'Verdi'),
    ('luca.bianchi', 'luca@email.com', 'hash789', 'Luca', 'Bianchi');

-- Inserimento con subquery
INSERT INTO products (name, price, category_id)
SELECT 'iPhone 15', 999.99, id
FROM categories
WHERE name = 'Electronics';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Selezionare tutti i campi
SELECT * FROM users;

-- Selezionare campi specifici
SELECT username, email, first_name FROM users;

-- Con condizioni
SELECT * FROM products WHERE price > 100;

-- Ordinamento
SELECT * FROM products ORDER BY price DESC;

-- Limitare risultati
SELECT * FROM products LIMIT 10;

-- Paginazione
SELECT * FROM products LIMIT 10 OFFSET 20;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Aggiornare singolo record
UPDATE users
SET first_name = 'Giuseppe', last_name = 'Verdi'
WHERE id = 1;

-- Aggiornare con condizioni
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 5 AND stock_quantity > 0;

-- Aggiornare con subquery
UPDATE products
SET category_id = (SELECT id FROM categories WHERE name = 'Books')
WHERE name LIKE '%Manual%';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Eliminare record specifico
DELETE FROM users WHERE id = 10;

-- Eliminare con condizioni
DELETE FROM products WHERE stock_quantity = 0;

-- Eliminare tutti i record (ATTENZIONE!)
DELETE FROM temp_table;

-- Truncate (piu veloce per svuotare tabella)
TRUNCATE TABLE temp_table;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- Operatori numerici
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE stock_quantity BETWEEN 10 AND 50;
SELECT * FROM products WHERE price IN (99.99, 199.99, 299.99);

-- Operatori testuali
SELECT * FROM users WHERE first_name LIKE 'M%';        -- Inizia con M
SELECT * FROM users WHERE email LIKE '%@gmail.com';    -- Finisce con @gmail.com
SELECT * FROM users WHERE first_name LIKE '_ario';     -- Esattamente 5 caratteri, finisce con ario

-- Operatori logici
SELECT * FROM products
WHERE price > 100 AND stock_quantity > 0;

SELECT * FROM products
WHERE category_id = 1 OR category_id = 2;

SELECT * FROM users
WHERE NOT (first_name = 'Mario' OR last_name = 'Rossi');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Ordinamento
SELECT * FROM products ORDER BY price ASC, name DESC;

-- Raggrupare dati
SELECT category_id, COUNT(*) as product_count
FROM products
GROUP BY category_id;

-- Filtrare gruppi
SELECT category_id, AVG(price) as avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 100;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Concatenazione
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

-- Conversione maiuscole/minuscole
SELECT UPPER(name), LOWER(email) FROM users;

-- Lunghezza stringa
SELECT name, LENGTH(name) as name_length FROM products;

-- Substring
SELECT LEFT(name, 10) as short_name FROM products;
SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) as username FROM users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- Data corrente
SELECT NOW(), CURDATE(), CURTIME();

-- Estrarre parti della data
SELECT
    YEAR(created_at) as year,
    MONTH(created_at) as month,
    DAY(created_at) as day
FROM users;

-- Calcoli con date
SELECT
    name,
    created_at,
    DATEDIFF(NOW(), created_at) as days_ago
FROM products;

-- Formattazione date
SELECT DATE_FORMAT(created_at, '%d/%m/%Y %H:%i') as formatted_date FROM users;

1
2
3

SELECT *
FROM logs
ORDER BY STR_TO_DATE(data_str, '%Y%m%d:%H%i%s') DESC;

1
2
3

SELECT *
FROM logs
ORDER BY to_timestamp(data_str, 'YYYYMMDD:HH24MISS') DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT *
FROM logs
ORDER BY (
    substr(data_str, 1, 4) || '-' ||
    substr(data_str, 5, 2) || '-' ||
    substr(data_str, 7, 2) || ' ' ||
    substr(data_str, 10, 2) || ':' ||
    substr(data_str, 12, 2) || ':' ||
    substr(data_str, 14, 2)
) DESC;

1
2
3
4
5
6
7

-- Prodotti con nome categoria
SELECT
    p.name as product_name,
    p.price,
    c.name as category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

1
2
3
4
5
6

-- Tutti i prodotti, anche senza categoria
SELECT
    p.name as product_name,
    COALESCE(c.name, 'No Category') as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

1
2
3
4
5
6
7

-- Tutte le categorie, anche senza prodotti
SELECT
    c.name as category_name,
    COUNT(p.id) as product_count
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id
GROUP BY c.id, c.name;

1
2
3
4
5
6

-- MySQL non supporta FULL OUTER JOIN nativamente, si usa UNION
SELECT p.name as product_name, c.name as category_name
FROM products p LEFT JOIN categories c ON p.category_id = c.id
UNION
SELECT p.name as product_name, c.name as category_name
FROM products p RIGHT JOIN categories c ON p.category_id = c.id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Query complessa con piu join
SELECT
    u.username,
    o.id as order_id,
    o.total_amount,
    o.order_date,
    oi.quantity,
    p.name as product_name,
    p.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'confirmed'
ORDER BY o.order_date DESC;

1
2
3
4
5
6

-- Tabella employees con manager_id che referenzia id nella stessa tabella
SELECT
    e.name as employee_name,
    m.name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- Contare record
SELECT COUNT(*) as total_users FROM users;
SELECT COUNT(DISTINCT category_id) as unique_categories FROM products;

-- Somme e medie
SELECT
    SUM(price * stock_quantity) as inventory_value,
    AVG(price) as average_price,
    MIN(price) as cheapest_product,
    MAX(price) as most_expensive_product
FROM products;

-- Con raggruppamento
SELECT
    category_id,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(stock_quantity) as total_stock
FROM products
GROUP BY category_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Deviazione standard e varianza
SELECT
    STDDEV(price) as price_std_dev,
    VARIANCE(price) as price_variance
FROM products;

-- Concatenazione gruppi
SELECT
    category_id,
    GROUP_CONCAT(name ORDER BY price DESC SEPARATOR ', ') as products
FROM products
GROUP BY category_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Ranking
SELECT
    name,
    price,
    RANK() OVER (ORDER BY price DESC) as price_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) as row_num
FROM products;

-- Partizioni
SELECT
    name,
    category_id,
    price,
    AVG(price) OVER (PARTITION BY category_id) as category_avg_price,
    price - AVG(price) OVER (PARTITION BY category_id) as price_vs_avg
FROM products;

-- Running totals
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM orders
ORDER BY order_date;

1
2
3
4
5
6
7
8
9

-- Prodotti sopra la media
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Utenti con ordini
SELECT username
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

1
2
3
4
5
6
7
8

-- Prodotti piu costosi di ogni categoria
SELECT p1.name, p1.price, p1.category_id
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- CTE Semplice
WITH expensive_products AS (
    SELECT * FROM products WHERE price > 500
)
SELECT c.name, COUNT(ep.id) as expensive_count
FROM categories c
LEFT JOIN expensive_products ep ON c.id = ep.category_id
GROUP BY c.id, c.name;

-- CTE Ricorsiva (es. struttura gerarchica)
WITH RECURSIVE category_tree AS (
    -- Caso base: categorie radice
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Ricorsione: categorie figlie
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Categorizzazione con CASE
SELECT
    name,
    price,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-range'
        WHEN price > 200 THEN 'Premium'
        ELSE 'Unknown'
    END as price_category,

    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END as stock_status
FROM products;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Combinare risultati
SELECT 'user' as type, username as name FROM users
UNION ALL
SELECT 'category' as type, name FROM categories
ORDER BY type, name;

-- Intersezione (MySQL simulation)
SELECT DISTINCT p1.name
FROM products p1
INNER JOIN (
    SELECT name FROM products WHERE category_id = 1
) p2 ON p1.name = p2.name
WHERE p1.category_id = 2;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

DELIMITER //

-- Procedura per aggiungere utente
CREATE PROCEDURE AddUser(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_password_hash VARCHAR(255),
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50)
)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    -- Controllo se utente esiste già
    SELECT COUNT(*) INTO user_exists
    FROM users
    WHERE username = p_username OR email = p_email;

    IF user_exists > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User already exists';
    ELSE
        INSERT INTO users (username, email, password_hash, first_name, last_name)
        VALUES (p_username, p_email, p_password_hash, p_first_name, p_last_name);

        SELECT 'User created successfully' as result;
    END IF;
END //

DELIMITER ;

-- Chiamare la procedura
CALL AddUser('new_user', 'new@email.com', 'hash123', 'New', 'User');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

DELIMITER //

-- Funzione per calcolare sconto
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_pct INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN price * (100 - discount_pct) / 100;
END //

-- Funzione per contare ordini utente
CREATE FUNCTION GetUserOrderCount(user_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE order_count INT;
    SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = user_id;
    RETURN order_count;
END //

DELIMITER ;

-- Utilizzo delle funzioni
SELECT
    name,
    price,
    CalculateDiscount(price, 10) as discounted_price
FROM products;

SELECT
    username,
    GetUserOrderCount(id) as total_orders
FROM users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Trigger per aggiornare timestamp
DELIMITER //

CREATE TRIGGER update_product_timestamp
    BEFORE UPDATE ON products
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END //

-- Trigger per log delle modifiche
CREATE TRIGGER product_audit_log
    AFTER UPDATE ON products
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, old_values, new_values, timestamp)
    VALUES (
        'products',
        'UPDATE',
        CONCAT('price:', OLD.price, ',stock:', OLD.stock_quantity),
        CONCAT('price:', NEW.price, ',stock:', NEW.stock_quantity),
        NOW()
    );
END //

DELIMITER ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Indice semplice
CREATE INDEX idx_products_name ON products(name);

-- Indice composto
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Indice univoco
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Indice parziale (dove supportato)
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Vedere gli indici
SHOW INDEX FROM products;

-- Eliminare indice
DROP INDEX idx_products_name ON products;

1
2
3
4
5
6
7
8

-- EXPLAIN per analizzare piano di esecuzione
EXPLAIN SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

-- EXPLAIN ANALYZE (PostgreSQL/MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'iPhone%';

1
2
3
4
5

-- Inefficiente
SELECT * FROM products WHERE price > 100;

-- Efficiente
SELECT id, name, price FROM products WHERE price > 100;

1
2

-- Limitare risultati quando non serve tutto
SELECT name, price FROM products ORDER BY price DESC LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Inefficiente - WHERE dopo JOIN
SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

-- Piu efficiente - filtrare prima del JOIN
SELECT p.name, c.name
FROM (SELECT * FROM products WHERE price > 100) p
JOIN categories c ON p.category_id = c.id;

1
2
3
4
5
6
7

-- Puo essere lento con grandi dataset
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Spesso piu efficiente
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Vedere configurazioni correnti
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';

-- Statistiche prestazioni
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';

-- Abilitare slow query log
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; -- Query piu lente di 2 secondi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Nomi chiari e consistenti
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    profile_picture_url VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_profiles_user_id (user_id),
    FOREIGN KEY fk_user_profiles_user_id (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

1
2
3
4
5
6
7

-- Vulnerabile a SQL injection
query = "SELECT * FROM users WHERE username = '" + username + "'"

-- Usare prepared statements
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
SET @username = 'mario.rossi';
EXECUTE stmt USING @username;

1
2
3
4
5
6
7
8
9

-- Creare utente con privilegi limitati
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Dare solo i privilegi necessari
GRANT SELECT, INSERT, UPDATE ON ecommerce.users TO 'app_user'@'localhost';
GRANT SELECT ON ecommerce.products TO 'app_user'@'localhost';

-- Non dare mai privilegi globali se non necessario
-- GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'localhost'; -- Evitare

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- Transazione esplicita
START TRANSACTION;

INSERT INTO orders (user_id, total_amount) VALUES (1, 99.99);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 5, 2, 49.99);

UPDATE products SET stock_quantity = stock_quantity - 2 WHERE id = 5;

-- Se tutto è andato bene
COMMIT;

-- In caso di errore
-- ROLLBACK;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- Backup database
mysqldump -u root -p ecommerce > backup_ecommerce.sql

-- Ripristino
mysql -u root -p ecommerce < backup_ecommerce.sql

-- Ottimizzare tabelle
OPTIMIZE TABLE products;

-- Analizzare tabelle per statistiche
ANALYZE TABLE products;

-- Controllare e riparare tabelle
CHECK TABLE products;
REPAIR TABLE products;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Denormalizzato (duplicazione dati)
CREATE TABLE orders_bad (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2)
);

-- Normalizzato (terza forma normale)
-- Tabelle separate con relazioni tramite chiavi esterne

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,

    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- Creare dati di test
INSERT INTO users (username, email, password_hash, first_name, last_name)
SELECT
    CONCAT('user', i) as username,
    CONCAT('user', i, '@test.com') as email,
    'test_hash' as password_hash,
    CONCAT('User', i) as first_name,
    'Test' as last_name
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + 1 as i
    FROM
        (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
        (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
        (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
) numbers
LIMIT 1000;