środa, 2024-02-14

SQL: CRUD – przykłady

CRUD jest fundamentalnym zestawem operacji, które są używane w większości aplikacji wchodzących w interakcję z danymi. Określenie to najczęściej stosuje się w kontekście baz danych. CRUD to akronim, który odnosi się do czterech podstawowych operacji, które można wykonać na danych.

Rozwinięcie akronimu

– C (create, tworzenie) – odnosi się do operacji tworzenia nowych rekordów w bazie danych. W SQL, jest to zazwyczaj realizowane za pomocą polecenia INSERT. – R (read, odczyt) – dotyczy operacji odczytywania lub pobierania danych z bazy. W SQL jest to realizowane za pomocą polecenia SELECT. – U (update, aktualizacja) – odnosi się do modyfikacji istniejących danych w bazie. W SQL odbywa się to za pomocą polecenia UPDATE. – D (delete, usuwanie) – dotyczy usuwania danych z bazy danych. W SQL jest to realizowane za pomocą polecenia DELETE.

Demonstracyjna baza danych

Do zaprezentowania operacji CRUD posłuży przykładowa tabela, która może być używana do przechowywania informacji o użytkownikach w systemie. Tabela ta będzie zawierać kilka podstawowych kolumn:

• id – unikalny identyfikator dla każdego użytkownika,
• name – nazwa użytkownika,
• email – adres e-mail użytkownika,
• created_at – data i czas utworzenia rekordu użytkownika.

Kod SQL

Poniższy kod SQL można użyć do utworzenia prostej tabeli użytkowników w większości systemów zarządzania bazami danych, takich jak MySQL, PostgreSQL, SQLite itp.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Objaśnienie działania kodu

1. CREATE TABLE users – to polecenie rozpoczyna tworzenie nowej tabeli o nazwie users.
2. id INT AUTO_INCREMENT PRIMARY KEY – ta linia definiuje kolumnę id jako całkowitoliczbową (INT), która będzie automatycznie zwiększana (AUTO_INCREMENT) dla każdego nowego rekordu. Jest to również klucz główny tabeli (PRIMARY KEY), co oznacza, że każda wartość w tej kolumnie musi być unikalna i nie może być wartością NULL.
3. name VARCHAR(100) NOT NULL – definiuje kolumnę name, która przechowuje ciągi znaków (tekst) o maksymalnej długości 100 znaków. NOT NULL oznacza, że ta kolumna nie może mieć wartości NULL.
4. email VARCHAR(100) NOT NULL UNIQUE – podobnie jak kolumna name, ale dodatkowo oznaczona jako UNIQUE, co oznacza, że każdy e-mail w tabeli musi być unikalny.
5. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP – ta kolumna zapisuje datę i czas utworzenia rekordu. DEFAULT CURRENT_TIMESTAMP ustawia domyślnie bieżącą datę i czas, gdy rekord jest tworzony.

(C)RUD – INSERT

W dalszej części znajdziesz kilka przykładowych zapytań INSERT, które służą do dodawania nowych rekordów do tabeli users. Pamiętaj, że przy dodawaniu danych należy zapewnić, aby wszystkie wymagane pola (zaznaczone jako NOT NULL) były wypełnione.

Dodaj nowego użytkownika z pełnymi informacjami

INSERT INTO users (name, email) VALUES ('Jan Kowalski', 'jan.kowalski@example.com');

To zapytanie doda nowego użytkownika o nazwie 'Jan Kowalski' i adresie e-mail 'jan.kowalski@example.com'. Kolumna id jest ustawiona na autoinkrementację, a created_at jest domyślnie ustawiona na bieżącą datę i czas. Każde z tych zapytań INSERT dodaje nowe rekordy do tabeli users, zgodnie z określonymi kryteriami i wartościami. Ważne jest, aby upewnić się, że wartości, które dodajesz, są zgodne z typami danych i ograniczeniami każdej kolumny w tabeli.

Dodaj kilku użytkowników jednocześnie

INSERT INTO users (name, email) VALUES 
('Anna Nowak', 'anna.nowak@example.com'),
('Piotr Wiśniewski', 'piotr.wisniewski@example.com'),
('Ewa Kozłowska', 'ewa.kozlowska@example.com');

To zapytanie dodaje trzech użytkowników jednocześnie z ich nazwami i adresami e-mail.

Dodaj użytkownika z określoną datą utworzenia

INSERT INTO users (name, email, created_at) VALUES ('Marek Marecki', 'marek.marecki@example.com', '2024-02-01 10:00:00');

W tym zapytaniu dodajesz użytkownika z określoną datą i czasem utworzenia (nie tą wstawianą automatycznie przez system).

C(R)UD – SELECT

Przykłady obejmują kilka zapytań SELECT, które mogą być używane z tabelą users utworzoną wcześniej, zakładając, że tabela już zawiera dane. Można je dostosować lub rozszerzyć w zależności od konkretnych potrzeb i struktury danych w tabeli users.

Wybierz wszystkie rekordy z tabeli

SELECT * FROM users;

To zapytanie zwróci wszystkie kolumny (*) dla wszystkich rekordów w tabeli users.

Wybierz tylko nazwę i e-mail użytkowników

SELECT name, email FROM users;

To zapytanie zwróci tylko kolumny name i email dla wszystkich użytkowników.

Wybierz użytkownika o określonym ID

SELECT * FROM users WHERE id = 1;

Zmieniając wartość po id =, możesz wyszukiwać różnych użytkowników. To zapytanie zwróci wszystkie informacje o użytkowniku, którego id jest równe 1.

Wybierz użytkowników, których nazwa zaczyna się na określoną literę

SELECT * FROM users WHERE name LIKE 'A%';

To zapytanie zwróci wszystkich użytkowników, których nazwa zaczyna się na literę 'A'. Symbol % oznacza dowolną ilość dowolnych znaków.

Użytkownicy utworzeni po określonej dacie

SELECT * FROM users WHERE created_at > '2024-01-01';

Zmieniając datę, możesz dostosować zakres czasowy. Powyższe zapytanie zwróci użytkowników, którzy zostali utworzeni po 1 stycznia 2024 roku.

Sortowanie według daty utworzenia

SELECT * FROM users ORDER BY created_at DESC;

To zapytanie zwróci wszystkich użytkowników, posortowanych od najnowszego do najstarszego (ze względu na użycie DESC - malejąco). Użyj ASC dla sortowania rosnącego.

CR(U)D – UPDATE

Kolejne przykłady to zapytania UPDATE, które mogą być używane do modyfikowania danych w tabeli users.

Przed wykonaniem zapytań UPDATE ważne jest, aby upewnić się, że dokładnie zrozumiano, które rekordy zostaną zmienione, ponieważ zapytania te mogą modyfikować istotne dane. W środowiskach produkcyjnych zaleca się wykonanie zapytań SELECT przed UPDATE, aby zweryfikować, które rekordy zostaną zmienione, oraz wykonanie kopii zapasowej bazy danych przed przeprowadzeniem znaczących aktualizacji.

Aktualizacja nazwy wybranego użytkownika

UPDATE users SET name = 'NowaNazwa' WHERE id = 1;

To zapytanie zmieni nazwę użytkownika, którego id wynosi 1, na 'NowaNazwa'.

Aktualizacja adresu e-mail wybranego użytkownika

UPDATE users SET email = 'nowyemail@example.com' WHERE id = 2;

Zmienia adres e-mail użytkownika o id 2 na 'nowyemail@example.com'.

Aktualizacja nazwy i adresu e-mail

UPDATE users SET name = 'NowaNazwa', email = 'nowyemail@example.com' WHERE id = 3;

Zmienia zarówno nazwę, jak i e-mail użytkownika o id 3.

Aktualizacja nazwy wszystkich użytkowników o określonym adresie e-mail

UPDATE users SET name = 'ZmienionaNazwa' WHERE email = 'jakisemail@example.com';

Zmienia nazwę wszystkich użytkowników, którzy mają adres e-mail 'jakisemail@example.com'.

Aktualizacja nazwy dla użytkowników, którzy dołączyli przed określoną datą

UPDATE users SET name = 'StaryUzytkownik' WHERE created_at < '2024-01-01';

To zapytanie zmieni nazwę wszystkich użytkowników, którzy zostali utworzeni przed 1 stycznia 2024 r., na 'StaryUzytkownik'.

CRU(D) – DELETE

Następne przykłady to zapytania DELETE, które służą do usuwania rekordów z tabeli users. Pamiętaj, że zapytania DELETE są trwałe i po usunięciu danych nie można ich łatwo odzyskać. w środowiskach produkcyjnych.

Kiedy wykonujesz zapytania DELETE, zawsze upewnij się, że Twoje klauzule WHERE są poprawne, aby uniknąć przypadkowego usunięcia niewłaściwych danych. W środowiskach produkcyjnych dobrą praktyką jest najpierw wykonać zapytanie SELECT z tą samą klauzulą WHERE, aby sprawdzić, które rekordy zostaną usunięte. Zawsze przed usuwaniem upewnij się, że masz odpowiednie kopie zapasowe lub procedury przywracania danych.

Usuń wybranego użytkownika

DELETE FROM users WHERE id = 1;

To zapytanie usunie użytkownika, którego id wynosi 1.

Usuń wszystkich użytkowników o określonym adresie e-mail

DELETE FROM users WHERE email = 'jakisemail@example.com';

Usuwa wszystkich użytkowników, którzy mają adres e-mail 'jakisemail@example.com'.

Usuń wszystkich użytkowników, którzy dołączyli przed określoną datą

DELETE FROM users WHERE created_at < '2024-01-01';

Usuwa wszystkich użytkowników, którzy zostali utworzeni przed 1 stycznia 2024 r.

Usuń wszystkich użytkowników z określoną nazwą

DELETE FROM users WHERE name = 'OkreślonaNazwa';

Usuwa wszystkich użytkowników, którzy mają nazwę 'OkreślonaNazwa'.

Usuń wszystkich użytkowników

DELETE FROM users;

To zapytanie usunie wszystkie rekordy z tabeli users. Używaj go z dużą rozwagą, ponieważ usuwa wszystkie dane w tabeli.