W Wykładzie 9 omówiony został język DML - kolejny podzbiór poleceń języka SQL, odpowiedzialny za operacje na danych – dopisywanie, zmianę wartości i usuwanie danych z tabel bazy.
Polecenia utworzenia, modyfikacji i usunięcia danych mają składnię zaczynającą się słów kluczowych:
Wstawianie nowych rekordów do tabeli może odbywać się przy użyciu instrukcji INSERT w uproszczonej, lub pełnej składni.
INSERT INTO Nazwa_tabeli VALUES (wyrażenie1, wyrażenie2,…);
Składnia uproszczona w klauzuli INSERT pomija nazwy kolumn. Kolejność, liczba i typ wyrażeń w klauzuli VALUES muszą dokładnie odpowiadać układowi kolumn w tabeli. Jeżeli na którejś z kolumn tabeli określone jest IDENTITY, takiej tabeli nie można zasilać danymi przy użyciu składni uproszczonej. Jeżeli któreś z pól tabeli ma pozostać niewypełnione, odpowiadające mu wyrażenie musi mieć explicite podaną wartość NULL.
Dopisz nowego pracownika do tabeli EMP.
INSERT INTO EMP VALUES (8111, 'BROWN', 'CLERK', 7876, '1982-07-01', 980, NULL, 10);
INSERT INTO Nazwa_tabeli (Kolumna1, Kolumna2, ….) VALUES (wyrażenie1, wyrażenie2,…);
W składni pełnej w klauzuli INSERT wymieniane są tylko te kolumny, które mają zostać wypełnione. Kolumny mające zdefiniowane więzy DEFAULT, mogą zostać pominięte. Kolumny mające zdefiniowane więzy NOT NULL nie mogą zostać pominięte. Kolumny mające zadeklarowane IDENTITY nie mogą być wymienione na liście klauzuli VALUES. Kolejność kolumn w klauzuli INSERT może być dowolna, różna od kolejności kolumn w tabeli. Musi natomiast być zgodna z liczbą i kolejnością wyrażeń, wymienionych w klauzuli VALUES.
Dopisz nowego pracownika do tabeli EMP, pomijając numer jego przełożonego, prowizję i numer departamentu.
INSERT INTO EMP (Empno, Ename, Job, Hiredate, Sal) VALUES (8112, 'GREEN', 'ANALYST', '1982-09-02', 2250);
Choć użycie składni uproszczonej wydaje się być atrakcyjne z uwagi na „oszczędność” pisania, jednak powinno być bardzo ostrożne, ograniczone do potrzeb doraźnych. Zwłaszcza użycie tej składni wewnątrz procedur, może okazać się „generatorem” trudnych do wykrycia błędów, w sytuacji, gdy zostanie zmieniony schemat tabeli.
MS SQL Server począwszy od wersji 2008 umożliwił wstawianie jednym poleceniem INSERT więcej niż jeden rekord.
Dopisz trzech nowych pracowników do tabeli EMP, pomijając numery ich przełożonych, prowizję i numery departamentów (MS SQL Server).
INSERT INTO EMP (Empno, Ename, Job, Hiredate, Sal) VALUES (8211, 'APPLE', 'CLERK', '1989-12-01', 1250), (8212, 'PEAR', 'ANALYST', '1982-12-01', 2500), (8213, 'PLUM', 'MANAGER', '1982-12-01', 3100);
Instrukcja INSERT może wpisywać do tabeli rekordy odczytane z bazy danych instrukcją SELECT, tworząc wraz z nią jedno polecenie.
Utwórz tabelę BUDZET {Deptno, Budzet, Data_aktualizacji}. Wstaw do niej numery departamentów, sumę miesięcznych płac w poszczególnych departamentach, oraz datę modyfikacji w każdym wierszu.
CREATE TABLE BUDZET ( Deptno Int, Budzet Money, Data_aktualizacji Date);
INSERT INTO BUDZET (Deptno, Budzet, Data_aktualizacji) SELECT DEPTNO, SUM(sal), Getdate() FROM EMP; GROUP BY DEPTNO;
Instrukcja SELECT … INTO umożliwia utworzenie nowej tabeli i wstawienie do niej wartości odczytanych przez instrukcję SELECT z danych zapisanych w bazie. Struktura nowo tworzonej tabeli (nazwy kolumn, ich kolejność, typy danych) zdeterminowane są przez instrukcję SELECT. Wyrażenia na liście SELECT, niebędące nazwami kolumn, muszą mieć nadane aliasy.
Napisz instrukcję, która utworzy tabelę BUDZET i wstawi do niej numery departamentów, sumę miesięcznych płac w poszczególnych departamentach, oraz datę modyfikacji w każdym wierszu.
SELECT Deptno, Sum(Sal) Budzet, Getdate() "Data aktualizacji" INTO BUDZET FROM EMP GROUP BY Deptno;
Modyfikowanie danych, czyli zmiana wartości danych w istniejących wierszach, realizowana jest instrukcją UPDATE o następującej składni:
UPDATE nazwa_tabeli SET nazwa_kolumny = wyrażenie1, ... [WHERE warunek];
Warunek definiowany w klauzuli WHERE jest opcjonalny. Jeżeli nie zostanie podany, zostaną zaktualizowane wszystkie rekordy będące w zasięgu instrukcji SELECT. Jeżeli warunek WHERE jest podany, zostaną zaktualizowane te rekordy, dla których przyjmuje on wartość TRUE, a pominięte zostaną te rekordy, dla których przyjmuje on wartość FALSE lub NULL. W warunku WHERE można odwoływać się do aktualnych (przed zmianą) wartości w kolumnach modyfikowanej tabeli.
Podnieś pensję Sal wszystkim pracownikom zatrudnionym na stanowisku MANAGER o 10%.
UPDATE EMP SET Sal = Sal*1.1 WHERE Job = 'MANAGER';
Istnieje możliwość aktualizacji danych w tabeli na podstawie danych pochodzących z innych tabel.
Zaktualizuj tabelę BUDZET.
UPDATE BUDZET SET Data_aktualizacji = Getdate(), BUDZET.Budzet = (SELECT SUM(sal) FROM EMP WHERE EMP.Deptno = BUDZET.Deptno);
Proszę zwrócić uwagę, na wykorzystanie korelacji podzapytania z zapytaniem nadrzędnym w konstrukcji tego polecenia.
Usuwanie danych (całych wierszy) z tabel bazy danych realizowane jest instrukcją
DELETE FROM nazwa_tabeli [WHERE warunek];
Podobnie jak w przypadku instrukcji UPDATE, usuwane są te rekordy, dla których warunek zdefiniowany w opcjonalnej klauzuli WHERE przyjmuje wartość TRUE. Rekordy, dla których warunek WHERE przyjmuje wartość FALSE lub NULL nie zostaną usunięte. Jeżeli klauzula WHERE nie pojawi się w instrukcji, wówczas zostaną usunięte wszystkie rekordy z tabeli wskazanej w klauzuli DELETE.
Należy zwrócić uwagę, że w klauzuli DELETE może pojawić się nazwa jednej tabeli (lub widoku). Niedopuszczalne jest odwoływanie się w klauzuli DELETE do związku tabel.
Należy także przypomnieć, że jeżeli do rekordów usuwanych z tabeli odwołują się rekordy z tabel podrzędnych, instrukcja DELETE będzie zrealizowana tylko wtedy, kiedy zadeklarowana została akcja referencyjna ON DELETE CASCADE lub ON DELETE SET NULL lub ON DELETE SET DEFAULT. Jeżeli taka deklaracja nie istnieje, polecenie nie zostanie wykonane (zakończy się błędem).
Istnieje jeszcze jedna instrukcja usuwająca dane z tabel.
TRUNCATE TABLE nazwa_tabeli;
W tym poleceniu nie występuje klauzula WHERE. Usuwane są wszystkie rekordy z tabeli. Jeżeli istnieją odwołania z innych tabel do tabeli, z której usuwane są rekordy poleceniem TRUNCATE i nie zostały zdefiniowane dla tych powiązań akcje referencyjne ON DELETE CASCADE lub ON DELETE SET NULL lub ON DELETE SET DEFAULT, polecenie nie zostanie zrealizowane. Nie dotyczy do powiązań rekurencyjnych.
Jeżeli tabela zawiera kolumnę, na której zdefiniowano IDENTITY, licznik zostanie
cofnięty do wartości SEED określonej w definicji tabeli.
Polecenie TRUNCATE jest na poziomie fizycznym bazy danych inaczej realizowane,
niż DELETE FROM, co przyspiesza jego działanie. Szczegóły wykraczają poza ramy
tego wykładu.
Jak już wspomniano, na perspektywach mogą być wykonywane operacje INSERT, UPDATE, DELETE tak, jak na tabelach.
Utwórz perspektywę PRACOWNICY_DEPT20, zwracającą dane (Empno, Ename, Sal) pracowników zatrudnionych na stanowisku w dziale 20. Używając tej perspektywy podnieś zarobki pracowników 0 5%.
CREATE VIEW PRACOWNICY_DEPT20 (Numer, Nazwisko, Placa) AS SELECT Empno, Ename, Sal FROM EMP WHERE Deptno = 20;
UPDATE PRACOWNICY_DEPT20 SET Placa = Placa * 1.05;
Jak wspomnieliśmy wcześniej, operacje DML wykonywane na perspektywach mają ograniczenia. Wynikają one z wymogu jednoznaczności tabel i wierszy, które biorą udział w operacji. Poniżej przedstawiamy wymogi wobec perspektywy, stanowiące warunek dostateczny, aby można było na niej przeprowadzać te operacje.
Punkty 1-5 stanowią warunek dostateczny na to, aby jednoznacznie był określony wiersz w tabeli, której dotyczy zmiana.
W definicji perspektywy może zostać użyta klauzula WITH CHECK OPTION, wymuszająca sprawdzenie przed wykonywaniem instrukcji INSERT i UPDATE, czy wstawiany bądź modyfikowany wiersz spełnia warunek określony w klauzuli WHERE.
CREATE VIEW nazwa_perspektywy [(nazwa_kolumny,...)] AS zapytanie WITH CHECK OPTION;
Jeśli warunek jest spełniony (przyjmuje wartość logiczną TRUE), operacja jest wykonywana. Jeśli nie (przyjmuje wartość logiczną FALSE lun NULL), operacja nie zostanie wykonana.
Utwórz perspektywę NA_BEZPLATNYM, zwracającą dane (Empno, Ename, Sal) pracowników na urlopie bezpłatnym, poprzez którą nie będzie można zmienić ich zerowej płacy.
CREATE VIEW NA_BEZPLATNYM (Numer, Nazwisko, Placa) AS SELECT Empno, Ename, Sal FROM EMP WHERE Sal = 0 OR Sal IS NULL WITH CHECK OPTION;
Na tej perspektywie można wykonać operacje zmiany numeru pracowniczego, zmiany nazwiska, można także usunąć rekordy będące w jej zasięgu. Natomiast próba wykonania polecenia:
UPDATE NA_BEZPLATNYM SET Placa = 1000;
zakończy się błędem (nie zostanie wykonana).