Podstawy SQL.docx

Podstawy SQL

Spis treści:

·         Podstawy SQL

·         Definiowanie schematu bazy danych

·         Manipulowanie danymi

o        INSERT

o        DELETE

o        UPDATE

o        SELECT (pojedyncza tabela)

o        SELECT (wiele tabel)

Uwaga: przedstawione tutaj polecenia zapisane są w dialekcie MySQL. W innych SZBD mogą one nie działać dokładnie tak, jak to przedstawiono poniżej. W razie wątpliwości należy sprawdzić dokumentację odpowiedniego SZBD (np. na gotAPI.com).

Definiowanie schematu bazy danych

Plecenie CREATE TABLE służy do tworzenia schematów table:

CREATE TABLE table_name (column1 column1_type, column2 column2_type, ...,

  PRIMARY KEY(key_col1, key_col2, ...) );

table_name to nazwa tworzonej tabeli. column1, column2, ... to nazwy kolumn występujących w tabeli. column1_type, column2_type, ... to domeny (zbiory wartości) poszczególnych kolumn (np. VARCHAR, INTEGER). Klauzula PRIMARY KEY określa zbiór kolumn, które stanowią klucz podstawowy tworzonej tabeli.

Przykład

Polecenie:

CREATE TABLE Osoby(Imie VARCHAR, Nazwisko VARCHAR, PESEL INTEGER,

  PRIMARY KEY(PESEL));

tworzy tablę Osoby, która zawiera kolumny:

1.      Imię typu VARCHAR (łańcuch o zmiennej liczbie znaków)

2.      Nazwisko typu VARCHAR

3.      PESEL typu INTEGER (liczba całkowita)

Kluczem tabeli jest kolumna PESEL.

Manipulowanie danymi

W każdej bazie danych wykonywane są 4 podstawowe typy operacji (CRUD – create, read, update, delete):

·         dodawanie nowych danych do bazy

·         wyświetlanie danych zgromadzonych w bazie

·         modyfikowanie danych występujących w bazie

·         usuwanie danych z bazy

Język SQL zawiera polecenia odpowiadające tym operacjom:

·         INSERT

·         SELECT

·         UPDATE

·         DELETE

INSERT

Polecenie INSERT pozwala dodawać nowe dane do instancji bazy danych. Polecenie to występuje w dwóch postaciach:

INSERT INTO table_name SET a = value1, b = value2, ...;

INSERT INTO table_name VALUES (value1, value2, ...);

Pierwsza postać polecenia pozwala określić wartości poszczególnych atrybutów poprzez ich nazwę. W drugiej postaci wykorzystywana jest domyślna kolejność atrybutów, zdefiniowana w schemacie określonej relacji.

Przykład

Dla relacji Osoby(Imie, Nazwisko, PESEL), polecenia:

INSERT INTO Osoby SET Imie = "Jan", Nazwisko = "Kowalski", PESEL = 123654;

INSERT INTO Osoby VALUES ("Jan", "Kowalski", 123654);

powodują dodanie do bazy nowej informacji o osobie, której imię to “Jan”, nazwisko “Kowalski”, a pesel 123654.

DELETE

Polecenie DELETE pozwala na usuwanie informacji z bazy danych. Zazwyczaj stosuje się je w postaci:

DELETE FROM table_name [WHERE condition] [LIMIT x]

Użycie polecenia bez klauzuli WHERE oraz LIMIT powoduje usunięci wszystkich wierszy znajdujących się w tabeli.
Klauzula WHERE pozwala określić warunek (np. saldo < 0), który muszą spełniać wiersze, które mają zostać usunięte z tabeli. Klauzula LIMIT ogranicza liczbę usuwanych wierszy do wartości x. Zaleca się stosowanie tej klauzuli, ze względu na nieodwracalność zmian wprowadzanych w bazie danych oraz możliwość pomyłki.

Przykład

Dla relacji Osoby(Imie, Nazwisko, PESEL), która zawiera krotki:

polecenie:

DELETE FROM Osoby WHERE Imie = 'Jan' LIMIT 1;

spowoduje usunięcie wyłącznie pierwszej krotki.

UPDATE

Polecenie UPDATE pozwala na aktualizację danych zawartych w instancji bazy danych. Polecenie to przyjmuje zazwyczaj postać:

UPDATE table_name SET a = value1, b = value2 [WHERE condition] [LIMIT x]

gdzie znaczenie klauzul WHERE i LIMIT jest takie samo jak w przypadku polecenie DELETE.

Przykład

Dla relacji Osoby(Imie, Nazwisko, PESEL), która zawiera krotki:

polecenie:

UPDATE Osoby SET Imie = "Wojciech" WHERE Nazwisko = "Kowalski" LIMIT 1;

spowoduje zamianę imienia “Jan” na “Wojciech” wyłącznie w pierwszej krotce.

SELECT (pojedyncza tabela)

Polecenie SELECT wyświetla dane zgromadzone w jednej lub większej liczbie tabel znajdujących się w bazie danych.

Jego omówienie zaczniemy od przypadku wyświetlenia danych znajdujących się w jednej tabeli. W tym prostym przypadku składnia polecenia jest następująca:

SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY o_column] [LIMIT x,y];

gdzie:

1.      column1, column2,... to nazwy kolumn, których zawartość ma być wyświetlona jako wynik zapytania.

2.      table_name to nazwa tabeli, z której pobierane są dane

3.      condition to warunek jaki muszą spełniać wiersze tabeli, aby zostać wyświetlone

4.      o_column to kolumna, wg. której mają być posortowane wyniki zapytania

5.      x  to pozycja wiersza, od którego mają być wyświetlane wyniki

6.      y  to maksymalna liczba krotek, które mają pojawić się w wyniku

Ad. 1
Jeżeli zamiast nazw kolumn wpiszemy znak ‘*‘ to zostaną wyświetlone dane z wszystkich kolumn.

Ad. 3
W warunkach:

·         można stosować operatory porównania =, >=, >, etc.

·         można stosować operatory logiczna AND i OR

·         do porównywania wartości pustych (NULL) należy stosować składnię IS NULL oraz IS NOT NULL

·         do porównywania łańcuchów można stosować polecenie LIKE akceptujące wzorce napisów, w których znak ’_’ zastępuje dowolną literę, zaś znak ’%’ zastępuje dowolny ciąg znaków

Ad. 4
Sortowanie wyników:

·         może odbywać się według kilku kolumn, których nazwy oddzielane są przecinkiem

·         jest dla danej kolumny domyślnie zgodne z naturalnym porządkiem jej wartości, a odwrotne po dodaniu modyfikatora DESC

Przykład

Niech dana będzie tabela Osoby(Imie, Nazwisko, PESEL):

(pomijamy fakt, że PESEL nie może powtarzać się dla dwóch osób).

Polecenie:

SELECT Imie, Nazwisko FROM Osoby WHERE PESEL = 123;

da w wyniku:

Polecenie:

SELECT * FROM Osoby WHERE Nazwisko LIKE '%j%';

da w wyniku:

Polecenie:

SELECT * FROM Osoby ORDER BY PESEL, Nazwisko DESC;

da w wyniku:

SELECT (wiele tabel)

W przypadku wyświetlania danych z kilku tabel może wystąpić sytuacja, w której kolumny kilku tabel, a nawet same tabele (w przypadku związków rekurencyjnych) będą się powtarzać. W przypadku kolumn problem ten rozwiązywany jest poprzez poprzedzenie ich nazw zakończonej kropką nazwą tabeli, do której należą. (np. Osoby.Imie). W przypadku tabel możemy zastosować klauzulę AS, które zamienia nazwę danej tabeli na inną (np. Osoby AS Rodzice).

Jeśli chcemy wyświetlić dane z wielu tabel konieczne jest określenie sposobu łączenia krotek występujących w tych tabelach. W domyślnej postaci tworzony jest bowiem iloczyn kartezjański wszystkich krotek, co zazwyczaj nie jest pożądane.

Rozwiązaniem tego problemu może być określenie warunku, który będzie wymagał aby wartości kolumn w dwóch tabelach były identyczne (np. Osoby.PESEL = Adresy.PESEL). To rozwiązanie nie jest jednak doskonałe, gdyż w wyniku zostaną pominięte krotki, których wartość (lub zbiór wartości) dla łączonych atrybutów nie występuje w łączonej tabeli.

W języku SQL występuje specjalna klauzula JOIN, która pozwala rozwiązać problem tego rodzaju. Składnia polecenia SELECT z klauzulą JOIN jest następująca:

SELECT * FROM table1 JOIN table2 ON condition ...

condition określa sposób łączenia krotek. Zazwyczaj w warunku będzie wymagało się, żeby wartość kolumny w jednej tabeli była taka sama jak wartość kolumny w innej tabeli (table1.columnA = table2.columnB). Występują trzy wersje klauzuli JOIN:

·         INNER

·         LEFT [OUTER]

·         RIGHT [OUTER]

...