UCZYMY SIĘ JĘZYKA SQL.pdf

PROGRAMOWANIE

UCZYMY SIĘ JĘZYKA SQL

CD-ROM

Kompletne kody

źródłowe

nazwa pliku

– plik znajduje się

na krążku Eksperta

nazwa pliku

– plik w całości

znajduje się na krążku,

pokazany tu jest tylko

jego fragment

MySQL 5

Open Source

Pomocnik MySQL

demo

Dokument PDF

artykuł Kierownik bazy

z Eksperta 4/2004

W magazynie z danymi

K ażdy programista czy webma-

ster, prędzej czy później, spotka

się z koniecznością wyboru spo-

sobu przechowywania różnych

danych (na przykład informacji o produk-

tach w sklepie internetowym). Tak na-

prawdę wybór ten ogranicza się do trzech

możliwości: przechowywania danych

w plikach tekstowych, w plikach XML lub

w specjalnie do tego celu stworzonej bazie

danych. Pierwszą możliwość należy wybrać

tylko wtedy, gdy... nie możemy skorzystać

z żadnej innej. O dokumentach XML Eks-

pert pisał w numerze 6/2005. Czas więc

przyjrzeć się najlepszemu rozwiązaniu,

czyli bazom danych.

Baza danych znacznie ułatwia i przyspie-

sza projektowanie wszelkiego rodzaju apli-

kacji czy też witryn internetowych. Korzy-

stając z gotowej bazy, nie ma potrzeby mar-

twienia się o sposób przechowywania infor-

macji czy też ich bezpieczeństwo. Tym zaj-

muje się serwer bazy. Ten sam serwer ofe-

ruje również olbrzymie możliwości wyszu-

kiwania informacji, ich modyfikacji i usu-

wania, dokonując tych czynności niezwy-

kle szybko.

Ekspert pokaże, jak posługiwać się jedną

z najpopularniejszych baz danych – MySQL.

Poznamy podstawowe komendy języka SQL

pozwalające na tworzenie baz i tabel, a na-

stępnie na dodawanie, modyfikowanie,

usuwanie i wyszukiwanie informacji znaj-

dujących się w bazie. Ekspert dodatkowo

na stronie 78 przedstawia polecenia języka

PHP, za pomocą których będziemy mogli

połączyć się z bazą danych i ko-

rzystać z jej wszystkich dobro-

dziejstw na naszej stronie in-

ternetowej.

danych. O tym, jak z niego skorzystać, prze-

czytamy w artykule Kierownik bazy (Ekspert

4/2004, strony 58–61) – tekst ten znajdziemy

również w postaci pliku PDF na płycie dołą-

czonej do tego numeru Eksperta.

Jeśli jednak nie mamy konta na serwerze

WWW z dostępem do bazy danych, pod-

staw języka SQL najlepiej uczyć się na spe-

cjalnie do tego celu zainstalowanym na na-

szym domowym komputerze serwerze My-

SQL (plik z instalacją znajdziemy na płycie

CD, a opis instalacji i konfiguracji w ramce

dowolny komputer

lub aplikacja

na serwerze

serwer

bazy danych

baza danych

Klient MySQL

Do rozpoczęcia przygody

z MySQL niezbędny jest nam

serwer bazy danych . Jeśli

mamy płatne konto na serwe-

rze WWW, prawdopodobnie

mamy również dostęp do bazy

wynik

zapytania

dane

zapytanie

pobierz

dane

72 LIPIEC – SIERPIEŃ 2005

Przechowywanie informacji w bazie danych ma same zalety, z których najważniejszą jest

błyskawiczny dostęp do dowolnych danych. Poznajmy język SQL, który nam to umożliwi

Instalacja MySQL

I nstalacja serwera MySQL z płyty Eksperta nie powinna przysporzyć nikomu kło-

Wszystkie odpo-

wiedzi serwer zwraca

w postaci tabel, po-

nieważ właśnie w ta-

kiej postaci przecho-

wuje informacje.

Każda tabela ma za-

tytułowane kolumny

oraz wiersze, w któ-

rych znajdują się

przechowywane da-

ne. W przytoczonym

przed chwilą przy-

kładzie wydaliśmy

zapytanie show da-

tabases , które ozna-

cza „wyświetl listę

baz danych”. Serwer

odpowiedział na nie

tabelą składającą się

z jednej kolumny

(zatytułowanej Da-

tabase – baza da-

Typy danych

potów. Warto zwrócić jedynie uwagę na ostatnie okno procesu instalacji, w któ-

rym przed kliknięciem na przycisk należy zaznaczyć opcję . Dzięki temu z o-

stanie uruchomiony konfigurator serwera. Z Ekspertem z łatwością przebrniemy

przez proces konfiguracji.

1. Przechodzimy do drugiego okna kon-

figuratora i zaznaczamy opcję .

2. W kolejnym oknie za-

znaczamy opcję (dzię-

ki temu serwer MySQL

będzie uruchamiany jako

usługa przy starcie

naszego komputera).

3. Po przejściu do następ-

nego okna w pola wpi-

sujemy hasło, które będzie

chronić dostęp do ser-

wera.

4. Po przejściu do ko-

lejnego okna klikamy

na przycisk

i po chwili na przycisk . Serwer MySQL wraz z prostym klientem działającym

w trybie poleceń tekstowych został zainstalowany.

Typ Opis

TINYINT Liczby całkowite od -128 do 127

INT Liczby całkowite od -2147483648 do 2147483648

BIGINT Bardzo duże liczby całkowite

DOUBLE Liczby rzeczywiste

DATE Daty

DATETIME Daty i czas

TIME Czas

CHAR Znaki

VARCHAR (M) Ciąg znaków o maksymalnej długość M liter.

M nie może być większe niż 255

BLOB Ciągi znaków do 65 538 znaków

MEDIUMBLOB Ciągi znaków do 16 777 215 znaków

LONGBLOB

Ciągi znaków do 4 GB długości

. Bardzo uła-

twia to zapisywanie długich zapytań.

W ten sposób stworzyliśmy tabelę rezy-

serzy zawierającą cztery kolumny: imie ,

nazwisko , urodzony i idr .

Tworząc poszczególne kolum-

ny, obok nazwy podajemy typ

danych (w tabeli Typy danych

znajdziemy opis popularnych

typów). Określa on informacje,

które będą w danej kolumnie

przechowywane. W naszym

przykładzie kolumna imie jest

typu varchar , co oznacza, że

będą w niej przechowywane informacje

tekstowe (napisy). Liczba w nawiasie okre-

śla maksymalną długość takiego napisu

(w naszym przykładzie imię nie może być

5. Aby uruchomić klienta MySQL, z menu startowego wybieramy podmenu

, i ikonę . W oknie,

które się pojawia, wpisujemy ustalone podczas konfiguracji hasło i już możemy

korzystać z bazy danych MySQL.

nych) i trzema wier-

szami .

Utwórzmy teraz naszą pierwszą, własną

bazę danych. W tym celu posłużymy się po-

leceniem create database , po którym

umieszczamy nazwę bazy .

Widzimy, że nowa baza danych została

utworzona . Możemy ją usunąć za pomocą

polecenia drop database , po którym

umieszczamy nazwę bazy, którą chcemy ska-

sować . Należy być bardzo ostrożnym, gdyż

serwer MySQL nigdy nie prosi o potwierdze-

nie operacji, którą chcemy wykonać.

Utwórzmy jeszcze raz bazę danych , tym

razem nazwijmy ją filmy . Następnie wydajmy

polecenie

use ,po-

dając

po nim

nazwę

naszej

bazy .

W ten sposób poinformujemy serwer, że dal-

sze zapytania będą dotyczyły właśnie tej bazy.

Serwer pod Linuksem

nie serwer MySQL mamy już zainstalowany. Uru-

chomienie klienta MySQL polega na wydaniu komendy:

mysql -u root -p

Polecenia na płycie

W szystkie bardziej złożone polecenia SQL prezentowane

przez Eksperta znajdziemy na płycie w postaci plików

z rozszerzeniem .sql (ich nazwa podana została na obrazku z da-

nym poleceniem). W wielu narzędziach bazodanowych (na przy-

kład w PhpMyAdmin czy MySQL-Front) możemy je zaimporto-

wać i w ten sposób błyskawicznie wykonać znajdujące się w nich

komendy. W wypadku klienta MySQL, z którego korzysta Eks-

pert, zawartość pliku można skopiować i wkleić w oknie aplikacji.

Instalacja MySQL). Podczas instalacji ser-

wera MySQL instalowane są również dodat-

kowe narzędzia, wśród nich tak zwany

klient MySQL. Jest to program pozwalający

na uzyskanie dostępu do serwera bazy da-

nych, a tym samym na kierowanie zapytań

do bazy i uzyskiwanie od niej odpowiedzi.

Wszystkie przedstawione w tym artykule

przykłady zapytań wykorzystują właśnie

ten program. Jednak nie ma żadnych prze-

szkód, aby do komunikacji z bazą wykorzy-

stać na przykład phpMyAdmin (czyli witry-

nę WWW, z poziomu której można komu-

nikować się z serwerem MySQL) lub inne

aplikacje (Pomocnik MySQL czy MySQL-

-Front).

dłuższe niż 20 znaków). Podobnie zdefinio-

waliśmy pole nazwisko . Natomiast kolum-

na urodzony jest typu int , co znaczy, że

będą w niej przechowywane liczby całkowi-

te. Również tajemnicze pole idr jest typu

int . Przy tym jednak polu dopisaliśmy dwa

dodatkowe atrybuty. Atrybut not null

oznacza, że nie może w bazie wystąpić re-

kord (wiersz), w którym pole to byłoby pu-

ste (zawsze musi mieć jakąś wartość). Nato-

miast atrybut auto_increment oznacza, że

wartość tego pola ma być określana auto-

matycznie przez serwer MySQL i ma to być

wartość stale powiększana o jeden. Ozna-

cza to, że gdy dodamy pierwszy rekord

(wiersz), temu polu nadana zostanie auto-

matycznie wartość 1, dla kolejnego wiersza 2,

dla następnego 3.

Po co te zabiegi?

Po to, aby nasza ta-

bela miała jakieś

unikalne pole, czyli

takie, którego war-

tość nie powtarza się

Tabele

Jak już wiemy, wszystkie informacje

przechowywane przez serwer MySQL mają

postać tabel. Dlatego jeśli chcemy przecho-

wywać własne dane w bazie, najpierw

musimy zaprojektować odpowiednie tabe-

le, a później dodać do nich rekordy z dany-

mi (wiersze).

Tabele tworzymy za pomo-

cą zapytania create table ,

po którym umieszczamy na-

zwę tabeli, a następnie

w nawiasach okrągłych opis

kolejnych kolumn .

Tworzenie bazy

Mając urucho-

miony serwer

i klienta MySQL,

możemy wydać

polece nie (zapyta-

nie) . Na takie zapytanie serwer odpowie

tabelą z listą wszystkich baz danych .

01_rezyserzy.sql

Uwaga!

P amiętajmy, aby na końcu każdego polecenia SQL

➽

LIPIEC – SIERPIEŃ 2005 73

Ponieważ średnik określa koniec instrukcji,

w trakcie wprowadzania jednego zapytania

możemy wciskać klawisz

J eśli pracujemy w środowisku Linux, prawdopodob-

umieszczać średnik.

PROGRAMOWANIE

UCZYMY SIĘ JĘZYKA SQL

w całej tabeli. Dzięki temu każdy wiersz

możemy za pomocą takiego pola łatwo zi-

dentyfikować. Zapis primary key (idr)

określa właśnie, która z naszych kolumn

ma być tak zwanym kluczem, czyli ma

identyfikować jednoznacznie każdy wiersz.

W naszym przykładzie poinformowaliśmy

serwer, że tą kolumną ma być idr .

Poleceniem show

tables możemy

sprawdzić, jakie ta-

bele znajdują się

w wybranej bazie da-

nych, natomiast po-

leceniem drop table

możemy usuwać wybrane tabele (ich nazwy

podajemy jako dodatkowy parametr).

Spróbuj-

my utwo-

rzyć jeszcze

jedną tabe-

lę, w której

będziemy

przecho-

wywać in-

formacje

o naszych

ulubio-

nych fil -

mach .

ślana przez

bazę MySQL

automatycz-

nie (ma atry-

but auto_in-

crement ),

nadajemy

wartość NULL

(pustą).

W ten spo-

sób dodaliśmy

opisy ośmiu

reżyserów

do naszej ta-

beli rezyse-

rzy . Dodajmy

teraz kilka

opisów fil-

mów . Nale-

ży zwrócić

uwagę, że

w tabeli filmy

kolumna re-

zyser jest ty-

pu int , a za-

tem powinni-

śmy w niej przechowywać liczby. W jaki więc

sposób zaznaczyć, która osoba była reżyserem

danego filmu? Otóż jako numer reżysera po-

dajemy wartość pola idr danego reżysera .

Nasza tabela filmy zawiera teraz 14 rekor-

dów. Możemy się o tym przekonać, wydając

zapytanie .

05_filmy_dane.sql

02_filmy.sql

gwiazdki – zwrócone zostaną wszystkie ko-

lumny. Po słowie from wpisujemy nazwę

tabeli, która ma być przeszukiwana. Przed-

stawione przez nas zapytanie wyświetliło

wszystkie kolumny i wszystkie rekordy z ta-

beli rezyserzy .

Modyfikacja tabeli

Czasami zachodzi potrzeba zmodyfikowa-

nia tabeli, która już jest gotowa. Usunięcie

jej i ponowne utworzenie jest dobrym po-

mysłem, ale tylko wtedy, kiedy nie prze-

chowujemy w niej żadnych danych. W in-

nym wypadku możemy posłużyć się polece-

niem alter table . Spójrzmy na przykład ta-

kiego polecenia . Za jego pomocą zmieni-

liśmy typ kolumny gatunek .

Określanie warunków

Polecenie select przewiduje użycie dodat-

kowego słowa where , po którym możemy

umieścić warunki. Przy takim użyciu zapyta-

nia serwer MySQL zwraca tylko te wiersze,

dla których ów warunek będzie spełniony

(prawdziwy). Na przykład, jeśli chcemy wy-

świetlić tylko nazwiska tych reżyserów, któ-

rzy urodzili się po 1950 roku, możemy wydać

zapytanie .

Wyszukiwanie informacji

Serwer MySQL ma bardzo rozbudowane

polecenie select . Służy ono do wyszukiwa-

nia wszelkiego rodzaju informacji w tabe-

lach. Pozwala na określenie kolumn, które

nas interesują, czy zdefiniowanie warun-

03_alter.sql

Wwa-

runkach

możemy

posługi-

wać się

nazwami

kolumn

oraz do-

datkowymi operatorami logicznymi: or (lub),

and (i) oraz not (nie). Spójrzmy na przy-

kład . Przytoczone zapytanie zwróciło tytu-

ły tych filmów, których czas trwania przekra-

cza 90 i jest mniejszy niż 100 minut.

W warunku możemy posłużyć się rów-

nież słowem kluczowym like . Pozwala ono

na porównywanie ciągów znaków (napi-

sów) z uwzględnieniem pro-

stych wyrażeń specjalnych,

jak chociażby symbolu pro-

centa „%”, który oznacza do-

06_select1.sql

Gdybyśmy chcieli dodać nową kolumnę,

moglibyśmy wywołać zapytanie w postaci .

Takie zapytanie dodaje nową kolumnę

w tabeli nazwa_tabeli za kolumną o na-

zwie kolumna .

alter table nazwa_tabeli add nazwa_nowej_kolumny typ_kolumny after kolumna;

ków, jakie

mają być

spełnione. Spójrzmy

na najprostsze użycie

zapytania select .

Pomiędzy słowami select i from wy-

mieniamy nazwy kolumn, które chcemy wy-

świetlić. Jeśli wpiszemy w to miejsce znak

alter table nazwa_tabeli rename column stara_nazwa to nowa_nazwa ;

alter table nazwa_tabeli drop column nazwa_kolumny;

Aby z kolei usunąć kolumnę nazwa_ko-

lumny , wydajemy polecenie .

Zmianę nazwy kolumny o s iągniemy na-

tomiast, wydając komendę .

04_rezyserzy_dane.sql

Dodawanie rekordów

Mając już utworzone odpowiednie tabele,

można dodać do nich rekordy z danymi.

Dokonuje się tego za pomocą polecenia in-

sert into , po którym umieszczamy nazwę

tabeli, do której chcemy wpisać dane, a na-

stępnie słowo values i w nawiasach warto-

ści kolejnych kolumn . Kolejność kolumn

powinna być taka sama, jak przy tworzeniu

tabeli. Dla pola, którego wartość jest okre-

07_select2.sql

74 LIPIEC – SIERPIEŃ 2005

wolny ciąg

znaków.

Spójrzmy

na przy-

kład zapy-

tania ,

które

zwraca rekordy z ciągiem znaków ne wko-

lumnie tytul .

Kiedy po słowie kluczowym from wymie-

nimy kilka tabel, oddzielając je od siebie

przecinkiem, serwer dokona iloczynu karte-

zjańskiego tych tabel. Oznacza to, że ser-

wer przejrzy każdy wiersz jednej z tabel, łą-

cząc go z każdym wierszem z drugiej tabeli.

Bardzo często przy tego typu zapytaniach

konieczny jest warunek where określający,

które z tych połączeń mają być wyświetlo-

ne. W naszym przy-

kładzie wyświetlane

są te porównania (te

pary wierszy z obu

tabel), dla których

pole idr z tabeli re-

zyserzy jest takie sa-

mo jako pole rezyser

z tabeli filmy . W ten

sposób uzyskaliśmy

tabelę z tytułami fil-

mów i odpowiadającymi im imionami i na-

zwiskami reżyserów.

MySQL pozwala na korzystanie ze słowa

kluczowego as , którym możemy zmieniać lo-

kalnie (na potrzeby danego zapytania) nazwy

kolumn lub tabel. Prezentuje to przykład .

14_delete.sql

Sortowanie wyników

Jeśli chcemy, by wyni-

ki, które zwraca serwer

MySQL, były posortowa-

ne, dodajemy do zapyta-

nia słowo order by ,

po którym umieszczamy

nazwę kolumny, we-

dług której ma się odbyć

sortowanie. Możemy

określić kilka kolumn, oddzielając je od siebie

przecinkami – wtedy wyniki będą sortowane

z uwzględnieniem kilku kolumn. Na przy-

kład, jeśli chcemy wyświetlić nazwiska

i imiona reżyserów w posortowanej kolejno-

ści (według alfabetu, zwracając w pierwszej

kolejności uwagę na nazwisko, a w drugiej

na imię), wydajemy zapytanie .

09_select4.sql

my zmodyfikować. Na przykładzie widzimy

wyraźnie, jak ogromne zastosowanie ma

kolumna klucza.

Poprzez zapytanie update możemy mo-

dyfikować więcej niż jeden wiersz jedno-

cześnie. Zależy to tylko od warunku, jaki

umieścimy w zapytaniu. Należy więc być

bardzo ostrożnym, wydanie bowiem zapy-

tania: update filmy set tytul=”Super”; -

bez określenia warunku – spowoduje zmia-

nę wszystkich tytułów w naszej tabeli fil-

my na wyraz

Super .

12_select7.sql

Odwrotna kolejność

Usuwanie

rekordów

Oczywiście

podczas pracy

z bazami da-

nych zachodzi

często sytuacja,

że chcemy usunąć pewne dane z naszej ta-

beli. Posługujemy się wówczas poleceniem

delete from , po którym umieszczamy na-

zwę tabeli, z której chcemy usunąć dane,

oraz warunek where określający, które

z danych mają być usunięte. Spójrzmy na

przykład .

Podobnie jak w przypadku zapytania

update , przy zapytaniu delete należy być

bardzo ostrożnym. Złe sformułowanie wa-

runku lub pominięcie go może spowodować

usunięcie wszystkich lub nie tych, o które

nam chodzi, danych z tabeli. KWi

A by posortować wyniki w odwrotnej kolejności,

Porcjowanie wyników

Polecenie select pozwala na użycie jesz-

cze jednej klauzuli – limit . Jest ona szcze-

gólnie przydatna przy dużej ilości danych,

bo pozwala na „porcjowanie” wyników. Je-

śli po słowie limit umieścimy jedną liczb ę

W przytoczonym przykładzie skorzystali-

śmy dodatkowo z polecenie concat , które

służy do łączenia ciągów znaków. W ten spo-

sób połączyliśmy nazwiska z imionami, od-

dzielając je od siebie spacją . Nazwę takiej

kolumny zmieniliśmy

słowem as na Reżyser.

10_select5.sql

całkowitą, określać ona będzie liczbę pierw-

szych wierszy, które mają zostać zwrócone

(na przykład ). Natomiast podanie dwóch

liczb spowoduje wyświetlenie wierszy

z określonego przez te liczby przedziału.

Modyfikacja

rekordów

Mając tabele z dany-

mi, może czasami zajść

potrzeba dokonania pewnych modyfikacji

istniejących danych. W takich sy-

tuacjach możemy posłużyć się pole-

ceniem update . Jeśli na przykład

chcemy zmienić gatunek filmu

Haker z komedii na dramat, wyda-

jemy zapytanie . Gatunek zosta-

nie zmieniony .

Pomiędzy słowami kluczowymi

update i set wpisujemy nazwę ko-

lumny, w której planu-

jemy dokonać zmian.

Następnie po słowie set

wymieniamy korekty,

które chcemy zrobić

(oddzielamy je od siebie

przecinkami). W na-

szym przykładzie jest

jedna zmiana – modyfi-

kacja wartości kolumny

gatunek . Ostatnia

część zapytania to wa-

runek where określają-

cy, które wiersze chce-

13_update.sql

Przeszukiwanie kilku tabel

Poleceniem select można przeszukiwać

więcej niż jedną tabelę jednocześnie.

Spójrzmy na przykład .

11_select6.sql

Warto zajrzeć...

Książki:

MySQL Samouczek HELP – Karol

Wierzchołowski, BestHelp, Michałowice 2004,

cena 15,00 zł

Core MySQL Przewodnik zaawansowanego

programisty – Leon Atkinson, Wydawnictwo

Helion, Gliwice 2003, cena 65,00 zł

Adresy WWW:

www.mysql.com

www.binboy.org

www.sqlcourse.com

LIPIEC – SIERPIEŃ 2005 75

08_select3.sql

po nazwie kolumny, według której sortujemy,

należy dopisać słowo kluczowe desc .

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: