jezyki zapytan dla relacyjnych baz danych Flashcards
Polecenia SQL dotyczą:
tworzenia i usuwania baz danych, tabel, kluczy
wprowadzania, uaktualniania i usuwania danych
wyszukiwania danych
ustawiania praw dostępu do danych
administracji bazą danych
zarządzania transakcjami
Sposób wprowadzania do bazy poleceń SQL:
w programie działającym z linii poleceń
(np. mySQL monitor)
w programie z graficznym interfejsem użytkownika
(np. mySQL Navigator)
w skryptach i programach komunikujących się z bazą
danych (np. skrypty PHP)
pośrednio, przy użyciu graficznego interfejsu
użytkownika (np. Access
utworzenie bazy:
CREATE DATABASE nazwa;
przełączenie się do bazy:
USE nazwa;
Typy danych mySQL – liczby całkowite:
TINYINT (1 bajt)
SMALLINT (2 bajty)
MEDIUMINT (3 bajty)
INT (4 bajty)
BIGINT (8 bajtów)
Dodatkowe atrybuty:
UNSIGNED – liczba bez znaku
ZEROFILL – dopełnienie zerami
(M) – wyświetlenie M cyfr
Typy danych mySQL – liczby zmiennoprzecinkowe:
FLOAT (n) – pojedyncza precyzja, n liczb
DOUBLE (M,D) – podwójna precyzja
DECIMAL (M,D) – liczba zapisywana jako tekst
Dodatkowe atrybuty:
UNSIGNED – liczba bez znaku
ZEROFILL – dopełnienie zerami
(M) – wyświetlenie M cyfr
(M,D) – wyświetlenie M cyfr, D cyfr po przecinku
Typy danych mySQL – data i czas:
DATETIME – data + czas (2003-10-15 15:00:12)
DATE – data (2003-10-15)
TIME – czas (15:00:12)
YEAR – rok (2003 lub 03)
TIMESTAMP (n) – znacznik czasu (n – liczba znaków)
Typy danych mySQL – łańcuchy tekstowe:
CHAR (n) – stała długość n (max. 255)
VARCHAR (n) – zmienna długość, max. n (do 255)
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
– dane tekstowe ASCII
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
– dane binarne
ENUM – typ wyliczeniowy
SET – zbiór wartości
Utworzenie tabeli wymaga podania nazw pól
(kolumn) oraz ich typów.
CREATE TABLE albumy (
id INT NOT NULL,
wykonawca VARCHAR(30),
tytuł VARCHAR(30),
rok YEAR,
liczba-utw SMALLINT,
opis TEXT
);
Wstawianie danych z podaniem wszystkich kolumn
tabeli (należy zachować kolejność!)
INSERT INTO albumy
VALUES (1, ‘Pink Floyd’, ‘The Division Bell’,
1994, 11, ‘Ostatni studyjny album’);
Wyszukiwanie danych w tabeli – instrukcja SELECT
SELECT które_kolumny FROM z_której_tabeli
WHERE które_rekordy;
Eliminacja powtórzeń wyników:
SELECT DISTINCT wykonawca FROM albumy;
Wyszukiwanie rekordów spełniających zadany
warunek
– instrukcja WHERE
SELECT tytuł FROM albumy
WHERE wykonawca = ‘Pink Floyd’;
Operatory używane w instrukcji SELECT …
WHERE:
porównania: = <> < > <= >= <=>
logiczne: NOT ! AND && OR || XOR
IS NULL, IS NOT NULL
expr BETWEEN min AND max (NOT BETWEEN)
expr IN (lista) (NOT IN)
Symbole wieloznaczne używane w instrukcji WHERE:
% zastępuje dowolny ciąg znaków
_ zastępuje jeden znak
Operator symboli wieloznacznych:
LIKE, NOT LIKE
Sortowanie wyników wg zadanej kolumny:
ORDER BY pole – w porządku rosnącym
ORDER BY pole ASC – jw.
ORDER BY pole DESC – w porządku malejącym
Grupowanie wyników
użycie funkcji, np. COUNT, SUM, MAX, MIN, AVG
nazwanie kolumny z wynikami (opcjonalnie) – AS
zgrupowanie wyników – GROUP BY
Ograniczenie rekordów uzyskanych w wyniku
grupowania
operator HAVING
Ograniczenie liczby wyników
LIMIT
Uwzględnienie relacji między tabelami:
SELECT * FROM albumy, utwory
WHERE albumy.IDA = utwory.IDA;
Wstawianie do tabeli danych uzyskanych w wyniku
zapytania:
INSERT INTO nowa (autor, dzielo)
SELECT DISTINCT wykonawca, album
FROM albumy;
Wstawianie danych z pliku na komputerze klienta
(pola rozdzielone tabulatorami, rekordy – znakiem
nowej linii)
– komenda mySQL (nie standard):
LOAD DATA LOCAL INFILE ‘dane.txt’
INTO nowa_tabela;
Usunięcie rekordów spełniających zadane kryteria
– instrukcja DELETE. Warunki takie same jak w SELECT.
Zmiana danych rekordów już istniejących w tabeli
UPDATE albumy
SET wykonawca = ‘Pink Floyd’
WHERE wykonawca = ‘Fink Ployd’;
Usuwanie całej bazy:
DROP DATABASE baza;
Wyświetlenie istniejących baz danych:
SHOW DATABASES;
Przełączenie się na inną bazę danych:
USE baza;
Wyświetlenie struktury tabeli:
DESCRIBE tabela;
Zmiana nazwy:
RENAME TABLE tabela TO nowa_tabela;
Zmiana struktury tabeli – ALTER TABLE
ALTER TABLE tabela ADD (opis TEXT);
ALTER TABLE tabela DROP opis;
Modyfikacja typu kolumny
ALTER TABLE tabela MODIFY opis VARCHAR(50);
Zmiana nazwy kolumny:
ALTER TABLE tabela CHANGE opis info VARCHAR(50);
Atrybuty pól tabeli
Przy tworzeniu lub zmianie tabeli można podać
opcjonalne atrybuty pól (kolumn) tabeli:
CREATE TABLE (pole typ atrybuty, …);
Dostępne atrybuty:
NULL – można nie podawać wartości (domyślnie)
NOT NULL – wartość musi być podana
DEFAULT wartość – gdy nie podamy wartości
AUTO_INCREMENT – automatycznie zwiększany licznik
COMMENT ‘opis’ – komentarz
PRIMARY KEY, KEY – indeksy główne
AUTO_INCREMENT
nie wpisujemy danych, baza
wpisuje aktualny stan licznika i zwiększa go o 1.
DEFAULT
jeżeli nie wprowadzimy danych,
zostanie wpisana wartość domyślna
TIMESTAMP
Wartością domyślną dla kolumny o typie
TIMESTAMP
jest aktualny znacznik czasu (data i czas).
Kolumna tego typu umożliwia zachowanie czasu
wprowadzenia lub ostatniej modyfikacji rekordu.
Jeżeli zostanie podana wartość – jest ona
wpisywana.
Jeżeli nie zostanie podana wartość (NULL) –
wpisywany jest znacznik czasu.
Sprawdzanie poprawności danych
NOT NULL – w tej kolumnie muszą być wpisane dane
CHECK (wyrażenie) – dane muszą spełniać podany
warunek
Na wybrane kolumny tabeli mogą być nakładane
indeksy (klucze) w celu:
przyspieszenia wyszukiwania
zdefiniowania relacji pomiędzy tabelami
Typy indeksów w MySQL:
KEY, INDEX
UNIQUE
PRIMARY KEY
FULLTEXT
UNIQUE
żadne dwa rekordy w tabeli nie mogą mieć
jednakowych danych w indeksowanej kolumnie. Jest
to jednocześnie INDEX.
Indeks główny – PRIMARY KEY
identyfikuje jednoznacznie każdy rekord w tabeli
może istnieć tylko jeden w tabeli
jest typu UNIQUE
indeksowana kolumna otrzymuje automatycznie
atrybut NOT NULL
ma nazwę PRIMARY (nie można podać własnej)
bierze domyślnie udział w relacjach z innymi tabelami
Tworzenie indeksu głównego podczas definiowania
tabeli
CREATE TABLE dane {
nazwisko VARCHAR(30) NOT NULL,
pesel CHAR(11) PRIMARY KEY,
};
MySQL posiada specjalny typ klucza FULLTEXT,
umożliwiający wyszukiwanie informacji w polach
tekstowych. Indeks ten nie wchodzi w skład
standardu SQL.
CREATE TABLE albumy {
tytul VARCHAR(30) PRIMARY KEY,
wykonawca VARCHAR(30),
recenzja TEXT,
FULLTEXT indeks (tytul, recenzja)
};
Wyszukiwanie dosłowne:
MATCH (kolumny) AGAINST (napis)
Wyszukiwanie w trybie logicznym
– operator IN BOOLEAN MODE
Operatory wyszukiwania:
+słowo – słowo musi wystąpić
–słowo – słowo nie może wystąpić
“całe zdanie” – musi wystąpić podana fraza
słow* – słowo zaczynające się od podanych liter
() – grupowanie operatorów
~ – zaprzeczenie
Przykład wyszukiwania w trybie logicznym:
SELECT * FROM albumy
WHERE MATCH (tytul, recenzja)
AGAINST (‘+rock +”debut album” -awful’
IN BOOLEAN MODE);
Język SQL udostępnia szereg funkcji umożliwiających
wykonywanie operacji na danych w zapytaniach.
Funkcje:
matematyczne
tekstowe
daty i czasu
Użycie w warunku wyboru kolumn – zamienia
litery na wielkie w zwracanych danych:
SELECT UPPER(wykonawca) FROM albumy;
Tryb transakcji –
wprowadzane operacje zostaną
wykonane dopiero po podaniu odpowiedniej komendy.
START TRANSACTION
rozpoczęcie transakcji
Kolejne operacje są zapamiętywane, ale nie są
wykonywane.
COMMIT
wykonanie operacji z całej transakcji
ROLLBACK
cofnięcie do początku transakcji
Możliwe jest ustawienie w trakcie transakcji punktów
zapisu za pomocą komendy
SAVEPOINT nazwa
ROLLBACK TO SAVEPOINT nazwa
Blokowanie:
LOCK TABLES tabela1 typ, tabela2 typ, … ;
Typ blokady:
READ – blokada do odczytu
WRITE – blokada do zapisu
Odblokowanie tabel:
UNLOCK TABLES;
MySQL obsługuje różne standardy zapisywania tabel.
Typy nie udostępniające mechanizmu transakcji:
MyISAM (domyślny), ISAM (stary), HEAP, MERGE
Typy udostępniające mechanizm transakcji i blokowania:
InnoDB, BDB
Aby utworzyć tabelę innego typu, należy podać
żądany typ na końcu instrukcji:
CREATE TABLE nazwa (definicja) TYPE=InnoDB;
Zmiana typu tabeli (w praktyce utworzenie nowej
tabeli, przepisanie danych i usunięcie starej
tabeli):
ALTER TABLE nazwa TYPE=InnoDB;
System zarządzania bazą danych obsługuje tzw.
system przywilejów (privilege system):
autoryzacja użytkownika łączącego się z bazą z określonego
komputera (login, hasło),
określenie praw do wykonywania poszczególnych operacji na
bazie danych (przywileje)
Przywileje są ustalane na podstawie:
nazwy użytkownika
nazwy sieciowej komputera klienta
operacji, którą chce wykonać użytkownik
Sprawdzanie przywilejów odbywa się na dwóch
poziomach
Poziom 1 – połączenie z bazą
Poziom 2 – wykonanie operacji
Nadanie praw wykonywania określonych operacji na
danych w bazie:
GRANT przywilej (kolumny_tabeli)
ON baza_danych.tabela
TO użytkownik@host IDENTIFIED BY ‘hasło’;
Odbieranie praw dostępu:
REVOKE przywilej (kolumny_tabeli)
ON baza_danych.tabela
FROM użytkownik@host;
Przywileje dostępu do danych (ON) mogą
dotyczyć:
wszystkich baz danych na serwerze (poziom globalny):
GRANT … ON .
wszystkich tabel w określonej bazie:
GRANT … ON baza_danych.*
określonej tabeli:
GRANT … ON baza_danych.tabela
pojedynczych kolumn w określonej tabeli
Przywileje dla użytkowników
użytkownik user z dowolnego komputera:
user@’%’
użytkownik user z komputera w domenie:
user@’%.eti.pg.gda.pl’
użytkownik z określonego komputera:
user@sound.eti.pg.gda.pl
user@localhost
user@153.19.49.25
Ustalenie hasła przy nadawaniu praw:
GRANT ALL ON . TO user IDENTIFIED BY ‘hasło’;
Nadanie wybranych praw do tabeli dane użytkownikowi
joe łączącemu się z podanej domeny
GRANT SELECT,INSERT,UPDATE ON forum.dane
TO joe@’%.eti.pg.gda.pl’
IDENTIFIED BY ‘alamakota’;
Po połączeniu się z bazą systemową należy ustawić hasło:
mysql -u root mysql;
SET PASSWORD FOR root = PASSWORD(‘tajnehaslo’);
Dodanie użytkownika z jednoczesnym nadaniem
praw dostępu do danych:
GRANT ALL ON baza.* TO user IDENTIFIED BY ‘haslo1’;
